Example My.cnf

[jeff.showers]

For those struggling with MySQL performance tuning, here's an example of my 'my.cnf' file on a Dual-Core Opteron 240 server with 8GB of RAM. This particular server runs both the MySQL DB as well as all the zabbix monitoring and zabbix frontend.

My average load with this setup is about 0.35. Using a stock MySQL Huge RAM conf file... my average load was about 1.90. In addition to that, housekeeping? OMG HUGE difference! Housekeeping process on stock config was about 3 hours long, with tweaked config? 10 seconds.

So here ya go and happy tuning!

# The following options will be passed to all MySQL clients
[client]
port = 3306
socket = /tmp/mysql.sock

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
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_size = 384
query_cache_size = 32M
max_connections=400
default-storage-engine=innodb
thread_concurrency = 4
server-id = 1
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 250M
innodb_flush_log_at_trx_commit = 0

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[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

[mysqlhotcopy]
interactive-timeout

[Alexei]

I would also suggest to increase key_buffer to at least 2GB if ZABBIX database is created as MyISAM. This would make difference!

[jeff.showers]

Yeah, I considered that too, but wouldn't see any performance gain since they're set as InnoDB =)