mysql tuning help needed

[tribal]

First of all - thank you very much Chris for your help last time. Now it is me again. we upgraded our server again from 384 MB Ram to now 512 MB Ram. So we could probably tune the mysql-settings again.

The server: Pentium III 550 MhZ, UltraSCSI-harddrive an 512 MB Ram now

Top


3:36pm up 4:01, 1 user, load average: 1.19, 2.02, 3.50
105 processes: 101 sleeping, 2 running, 2 zombie, 0 stopped
CPU states: 0.7% user, 18.2% system, 16.2% nice, 64.7% idle
Mem: 515876K av, 169916K used, 345960K free, 69464K shrd, 22292K buff
Swap: 136544K av, 0K used, 136544K free 101956K cached


status

ptime: 34229017 Threads: 27 Questions: 308847 Slow queries: 20 Opens: 102
Flush tables: 1 Open tables: 98


Variable_name | Value |
+----------------------------+---------------------------------------+
| back_log | 5 |
| connect_timeout | 5 |
| basedir | /usr/ |
| datadir | /var/mysql/ |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| join_buffer | 6287360 |
| flush_time | 0 |
| key_buffer | 67104768 |
| language | /usr/share/mysql/english/ |
| log | OFF |
| log_update | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| max_allowed_packet | 1047552 |
| max_connections | 250 |
| max_connect_errors | 10 |
| max_delayed_insert_threads | 20 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_write_lock_count | 4294967295 |
| net_buffer_length | 16384 |
| pid_file | /var/mysql/server28.plusserver.de.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 6287360 |
| skip_locking | ON |
| skip_networking | OFF |
| socket | /tmp/mysql.sock |
| sort_buffer | 6291448 |
| table_cache | 250 |
| thread_stack | 131072 |
| tmp_table_size | 1048576 |
| tmpdir | /tmp/ |
| version | 3.22.32 |
| wait_timeout | 28800 |
+----------------------------+---------------------------------------+

my.cnf:

# Example mysql config file.
# You can copy this to one of:
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/mysql) or
# ~/.my.cnf to set user-specific options.
#
# One can use all long options that the program supports.
# Run the program with --help to get a list of available options

# This will be passed to all mysql clients
[client]
#password = my_password
port = 3306
socket = /tmp/mysql.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
user = mysql
skip-locking
set-variable = max_connections=250
set-variable = join_buffer=6M
set-variable = key_buffer=64M
set-variable = record_buffer=6M
set-variable = sort_buffer=6M
set-variable = table_cache=250
set-variable = max_allowed_packet=1M
set-variable = thread_stack=128K
# Start logging
# log

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=16M

bye Tom

[necrominator]

post that inbeetween the code tags, because its impossible to see it like this.

[Chris Schreiber]

Hi there,

I would make the following changes to your my.cnf file:


# Example mysql config file.
# You can copy this to one of:
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/mysql) or
# ~/.my.cnf to set user-specific options.
#
# One can use all long options that the program supports.
# Run the program with --help to get a list of available options

# This will be passed to all mysql clients
[client]
#password = my_password
port = 3306
socket = /tmp/mysql.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
user = mysql
skip-locking
set-variable = max_connections=250
set-variable = join_buffer=8M
set-variable = key_buffer=96M
set-variable = record_buffer=8M
set-variable = sort_buffer=8M
set-variable = max_allowed_packet=1M
set-variable = thread_stack=128K
set-variable = table_cache=250
# Start logging
# log

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=16M


-Chris


[Edited by Chris Schreiber on 11-07-2000 at 09:13 AM]

[tribal]

thank you chris - you are the best !!!!

Tom

[eva2000]

thought i'd point out you have a duplicate table_cache=250 setting in the new my.cnf file there chris

[Chris Schreiber]

Originally posted by eva2000
thought i'd point out you have a duplicate table_cache=250 setting in the new my.cnf file there

Oppps yes I do... won't hurt anything, but I took it out anyway... thanks :)

-Chris