Need help optimizing mysql on a busy server 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..
If you have frequent updates, then you should be using InnoDB as with MyISAM the whole table gets locked.

If you have so much RAM, you could set up large buffers to keep your tables completely in memory, which should give you a big improvement in performance.

Try turning on the slow query log to capture slow queries and queries not using indexes. You could use Percona's pt-query-digest tool to analyze the slow query log. Try tuning the slowest queries first and adding any indexes if necessary.