I am managing a server for a very busy news website with lots of select queries (read-heavy), concurrent about 4000 users online at peak time. Around 1.4Mil pages views / day
The server has 42G RAM, running dual intel CPU 6 core 2.6G ( HT tech) = 2x6x2.6Ghz ( x2 in HT ) I have issues of the server overloading randomly up to +300 server load
Most of that time I see that SQL has taken all resources.. I'm trying to tweak it, but I'm out of my knowledge .
There are 3 databases, two just small 200mb but one main database for news is +4G in volume. ( web runs fast at load under 200 but it got sstuck, or error 502/504 if load increase )
Here is my.cnf
socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 768M max_allowed_packet = 16M max_connections = 2000 wait_timeout = 20 connect_timeout = 10 tmpdir = /dev/shm/mysqlcache tmp_table_size = 512M table_cache = 2948 sort_buffer_size = 256M read_buffer_size = 16M read_rnd_buffer_size = 256M myisam_sort_buffer_size = 256M thread_cache_size = 768 query_cache_type = 1 query_cache_size = 2048M query_cache_limit = 16M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 24
Any suggestions very well come..