MySQL settings for low-end server

MySQL & MariaDB Other MySQL® Questions
1

Hello,

Can I please get some assistance to determine best settings for a Debian linux server that is dual-core and has 2GB RAM? It is your typical all-around web server running Apache2 (mpm worker), PHP5 (fcgid), MySQL5, PureFTPd.

MySQL is serving around a dozen databases, both MyISAM and InnoDB tables. Biggest databases are 2GB, biggest tables 1.5GB.

I need to tune MySQL so that it doesn’t take all RAM even under heavy load but leaves plenty to Apache and other services running on the server. Before applying values below on live server, I’d like to hear expert opinions on them. )

[B]Quote:[/B]

key_buffer = 128M
thread_cache_size = 64
table_cache = 1024
thread_concurrency = 4
key_buffer_size = 256M
thread_cache = 4

query_cache_limit = 1M
query_cache_size = 32M

innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_buffer_size = 8M
innodb_log_file_size = 32M
innodb_flush_log_at_trx_commit = 2

Thanks in advance!

2

key_buffer is deprecated and replaced by key_buffer_size. You specify both. Just remove ‘key_buffer = 128M’.

Aside from that it is hard to say anything without knowing more about your data, distribution among MyISAM/InnoDB, and about index sizes.