Hey Guys, How are you?
I am running a starting-to-get-really-popular community website and, although my server specs are pretty high, starting to have mysql problems. At times when I get around 500 people on the site, mysql process jumps to 170% and the site becomes unresponsive. The Database Engine is InnoDB. I have tried numerous configurations but still the problem remains the same. I think i am simply overlooking something and i hope you can point me in the right direction.
Server:
PowerEdge 2950
2 x Quad Core Intel® Xeon® E5310, 2x4MB Cache, 1.60GHz
16 Gb RAM
OS & MySQL:
Running FreeBSD 6.2 with mysql community server 5.1.22-rc
Here are some of the important specs/config variables:
Total buffers: 3.4G global + 58.2M per thread (512 max threads)
Reads / Writes: 89% / 11%
InnoDB data size / buffer pool: 1.9G/3.0G
query_cache_min_res_unit = 1024
thread_cache = 64
query_cache_size = 128M
wait_timeout = 60
interactive_timeout = 120
connect_timeout = 10
max_connections = 512
max_allowed_packet = 64M
max_heap_table_size = 256M
tmp_table_size = 256M
max_tmp_tables = 256
thread_cache_size = 64
thread_concurrency = 16
innodb_buffer_pool_size = 3G
innodb_data_file_path = ibdata1:200M:autoextend
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 15
innodb_thread_concurrency = 16
Anyone can suggest improvements without going master-slave direction?
PS: There is already a layer of caching implemented.