Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a dedicated 16 Cores 12 GB RAM Mysql server which is performing quite badly since I configure it for a 16 GB server and I guess that explains why in the world the site now takes more than 500 seconds to render a single page. Im using memcached for caching the quires and using InnoDB as database engine.

#8 core/16GB config stolen from http://groups.drupal.org/node/28830

back_log                        = 50
max_connect_errors              = 9999999
table_cache                     = 5000
binlog_cache_size               = 1M
max_heap_table_size             = 128M
sort_buffer_size                = 500K
join_buffer_size                = 500K
thread_cache_size               = 100
thread_concurrency              = 16
query_cache_size                = 512M
query_cache_limit               = 8M
query_cache_min_res_unit        = 2K
thread_stack                    = 192K
tmp_table_size                  = 384M
long_query_time                 = 2

# *** INNODB Specific options ***

innodb_additional_mem_pool_size = 80M
innodb_buffer_pool_size         = 12G
innodb_file_io_threads          = 4
innodb_thread_concurrency       = 16
innodb_log_buffer_size          = 20M
innodb_log_file_size            = 800M
innodb_log_files_in_group       = 3
innodb_max_dirty_pages_pct      = 90
innodb_lock_wait_timeout        = 120
innodb_file_per_table           = 1

Any help on optimizing the my.cnf config, or even purposing a new one would be really appreciated.

share|improve this question
    
Have you narrowed it down to MySQL, or are you just guessing at the cause? –  ceejayoz Aug 14 '11 at 0:54
    
You mean optimizing the queries? –  kornesh Aug 14 '11 at 1:05
4  
No, I mean things like failing disks, a runaway or malware process on the server, etc. Are you certain MySQL is what's being slow? –  ceejayoz Aug 14 '11 at 1:07
1  
And, for that matter, have you determined whether there are any bottlenecks, such as excessive swap usage, that would point to which aspect of the configuration might be at fault? –  womble Aug 14 '11 at 1:10
1  
Cough, cough. Mines are about 63 Millions rows on a way smaller (non dedicated) machine. And pages render under less than a second. So first I would look at the database design and the real bottleneck. –  mailq Aug 14 '11 at 8:45
show 8 more comments

1 Answer

The only way that a config for 16GB on a 12GB box would be an issue is if MySQL is trying to use more memory resources than the system has. Most MySQL servers are configured to use too much memory, but that doesn't cause them to run poorly from day 1. If the box isn't swapping and MySQL getting OOM killed, it's not the fact that it's a config for a 16GB box on a 12GB box.

If the issue is on the MySQL side, you can start by checking the process list with SHOW PROCESSLIST;. If it's taking 500 seconds, you'll definitely see some hanging queries. If the queries being executed are being truncated, you can run SHOW FULL PROCESSLIST;.

I would also suggest turning on slow query logging. Once you identify any problem queries, you can use the EXPLAIN statement to see if your queries are using the indexes you have.

If you isolate the issue to the database, there are still a lot of variables. The first step is finding out what queries are going slowly and go from there. You will probably find that improving the queries will give the biggest improvement over any server-side config changes.

share|improve this answer
add comment

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.