We have a 150 GB database running MySQL 5.0.45 using MyIsam tables, there are large tables larger than 5 GB and 2 GB indexes. Server Configuration: 8GB Dual Core 3.2 GHz - Advanced Streaming One 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 valuable for improving query performance (80% read / 20% write) Thank you,
David
source
share