Generic Optimization Request Please

[patrick_m]

Hi guys,

I've had several servers optimized by eva2000, and all of them are SMOKIN! Thank you truly for all your hard work - it is much appreciated!

I'm looking to understand the MySQL optimizations a little more, and am looking to come up with a generic optimized my.cnf that will work with most of our machines. Our machines are all Intel P4's with at least 1gb of RAM.

Here are some settings I took from one of the servers eva2000 optimized for me. For a moderately optimized server (for a "generic" configuration), what should I change?

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-name-resolve
skip-locking
safe-show-database
skip-innodb
max_connections = 650
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size=384
wait_timeout = 180
connect_timeout = 10
tmp_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 9999999999
thread_concurrency = 2
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

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

[myisamchk]
key_buffer = 64M
sort_buffer_size = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open_files_limit = 8192




Thanks everyone!

[eva2000]

what version of mysql

for 4.1.x series you can use something like below for 1GB ram configs


[mysqld]
safe-show-database
back_log = 50
skip-innodb
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 180
connect_timeout = 10
tmp_table_size = 32M
max_allowed_packet = 64M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 16384
query_alloc_block_size = 16384
default-storage-engine = MyISAM

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout


but really it depends on your mysql usage stats and other apps and services that are running on your box - it's truly trial and error once past the basics

[patrick_m]

What about for versions 4.0?

[eva2000]

basically same just maybe minus

default-storage-engine = MyISAM