Best MySQL server configuration for performance?

Question

We have a database with 150 GB in size running MySQL 5.0.45 using MyIsam tables, there are big tables with over 5 GB in sizes, and 2GB in indices. The server config is: 8GB Dual Core 3.2GHz – hyper threading enabled Single raid 5 - SCSI 1gb nic 64 bit OS

Here is our my.cnf file:

[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

[mysqld]
log=/var/log/mysqld.log
log-slow-queries=/var/log/mysqld-slow.log
set-variable=long_query_time=2
set-variable=sql_mode=ANSI_QUOTES
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
max_connections = 100
key_buffer = 384M
max_allowed_packet = 16M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
thread_concurrency = 3


[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M



[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

Suggestions are really appreciate to improve query performance (80% read/20% write) Thanks,

David

Solution

There's not really enough here to answer this well. It depends on the application - both in terms of which tables/indexes are hot (and how hot) and whether or not the queries can effectively use the index, whether or not the index is present in memory, whether or not the OS is able to cache the pages that represent the data, so on and so forth.

The MySQL manual has a bunch of tips, but if you want a shortcut that works for a lot of people, do SHOW STATUS and SHOW VARIABLES and look at hit-rates for various caches, and expand the cache sizes accordingly. But not all cache hits are the same...

Database administration is a complex field and it's not a generally solvable problem.

Comments

[David Zhao]

thx for your answer, and I understand there wouldn't be a straight answer one way or the other, I just want to let the guys to see if I have some settings are completely out of whack.