Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite
1

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..

share|improve this question
3  
What version of MySQL? What storage engine(s) are you using (InnoDB, MyISAM, or a mixture of both)? – RolandoMySQLDBA May 21 '12 at 15:27
Just assuming from his lack of innodb_* variables it's MyISAM :) – Derek Downey May 21 '12 at 21:30
In addition to @Rolando's questions, when your load is >200, what type of memory is being used. Are the full 2000 connections being used? Have you tried analyzing the slow queries and proper indexing? – Derek Downey May 21 '12 at 21:38
Let mysql manage threads # thread_concurrency = 24 Change your table engines to InnoDB so they don't implicit read lock the whole table, then innodb_buffer_pool = 32G *Test that configuration and you'll see less CPU queuing (load) and less disk activity. – randy melder Jun 21 '12 at 19:30

1 Answer

up vote 1 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.