Mysql – 16 Cores 12 GB RAM Dedicated MySql Configuration

configurationinnodbMySQL

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.

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

Related Topic