For years I have been running dedicated servers with more or less limited ressources, resulting in unsatisfying performance of the web application (Drupal). Tools like Matthew Montgomery's MySQL Performance Tuning Primer and Major Hayden's MySQLTuner always suggested parameters that required more RAM than those servers had. Almost every article about database performance repeated the same mantra: "rDBMS require as much memory as possible to work fast", "you can never have enough RAM". When I set up my latest dedicated database server a couple of months ago, I learned that this isn't the whole truth (most of you probably will know this already).
The current database server (Intel Xeon E5-1620v2 @3.7/3.9 GHz, 4 cores/8 threads, 64 GB RAM) isn't even excessively well equipped, however I have been unable to make full use of it's available memory because MySQL 5.5.37-0+wheezy1 (Debian) won't become faster if the relevant tunable parameters get more ressources. In fact, above a certain "sweet spot", MySQL's performance becomes worse than it is with less RAM. This was an surprising finding I hadn't expected. In the past weeks I did some research and ran lots of tests; my results are consistent, and others experienced similar limitations of MySQL as well and documented it on the web. Some examples:
query_cache_size - defaults on Debian to 16M; in my case, the "sweet spot" appears to be between 256M and 512M. With 1G or even 2G, performs significantly slower than with the default 16M (cf. stackoverflow.com/questions/2095614/mysql-query-caching-limited-to-a-maximum-cache-size-of-128-mb, blogs.oracle.com/dlutz/entry/mysql_query_cache_sizing).
join_buffer_size - started tuning with "256" and increased in small steps to "15M"; with more memory, MySQL gets slower (cf. dba.stackexchange.com/questions/53201/mysql-creates-temporary-tables-on-disk-how-do-i-stop-it).
tmp_table_size and max_heap_table_size - those default on Debian to "32M"; I increased those values in small steps to "12G" each; with more memory, MySQL becomes significantly slower and - even worse - the number of temporary tables created on disk does not decrease. It's always around 36%-38%, no matter if tmp_table_size and max_heap_table_size is "25G" each or "10G" each. Actually I'm currently working my way back down again to find the "sweet spot" (probably below "10G"; cf. dba.stackexchange.com/questions/53201/mysql-creates-temporary-tables-on-disk-how-do-i-stop-it).
innodb_buffer_pool_size - I started with "1G" and increased to "24G". More memory does not result in better database performance (cf. www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/; dba.stackexchange.com/questions/19164/what-to-set-innodb-buffer-pool-and-why/19181; dba.stackexchange.com/questions/39467/mysql-performance-impact-of-increasing-innodb-buffer-pool-size; dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html)
Bottomline after three months of trial & error with the MySQL configuration: Even on heavy load, MySQL plus OS do not require significantly more than 25 GB of RAM. If I force significantly more RAM upon MySQL, the web application becomes slower than when running with an MySQL with Debian's very conservative default settings. The most plausible explanation for this behaviour I could find is, that MySQL's caching algorithms are buggy at some point and/or not fully optimized.
Currently I'm in the bizarre situation to have 1/2 - 1/3 of the database server's memory vacant. For the time being I added some of it to a memcache cluster (currently using 26G of the server's memory accordung to 'top'). Still the server has ~20G RAM vacant. Questions:
a) Is there a more beneficial way to make use of this memory with MySQL, and b) which tools are recommended to get hints when MySQL Performance Tuning Primer and MySQLTuner can not suggest anything useful anymore?
Excerpt from my.cnf
… pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP max_connections = 150 table_cache = 15000 table_definition_cache = 5000 thread_concurrency = 8 query_cache_limit = 2M query_cache_size = 512M join_buffer_size = 15M tmp_table_size = 10G max_heap_table_size = 10G sort_buffer_size = 1M expire_logs_days = 10 max_binlog_size = 100M innodb_buffer_pool_size = 24G innodb_flush_method=O_DIRECT innodb_io_capacity = 2000 innodb_read_io_threads = 8 innodb_thread_concurrency = 0 innodb_write_io_threads = 8 innodb_commit_concurrency = 16 …
Size of the databases:
# du -h /var/lib/mysql 31G /var/lib/mysql
Number of tables:
mysql> SELECT IFNULL(table_schema,'Total') "Database",TableCount -> FROM (SELECT COUNT(1) TableCount,table_schema -> FROM information_schema.tables -> WHERE table_schema NOT IN ('information_schema','mysql') -> GROUP BY table_schema WITH ROLLUP) A; +-----------------------+------------+ | Database | TableCount | +-----------------------+------------+ | Total | 3506 | +-----------------------+------------+
Size of InnoDB data and indexes:
mysql> SELECT SUM(data_length+index_length)/1024/1024 AS -> total_InnoDB_size_in_MB -> FROM INFORMATION_SCHEMA.TABLES -> WHERE engine = 'InnoDB'; +-------------------------+ | total_InnoDB_size_in_MB | +-------------------------+ | 20355.12500000 | +-------------------------+
Key figures from MySQLTuner:
>> MySQLTuner 1.1.1 - Major Hayden <email@example.com> -------- Storage Engine Statistics -------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 278M (Tables: 50) [--] Data in InnoDB tables: 10G (Tables: 3439) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) -------- Performance Metrics -------------------------------------- [--] Reads / Writes: 76% / 24% [--] Total buffers: 34.5G global + 16.6M per thread (150 max threads) [OK] Maximum possible memory usage: 37.0G (58% of installed RAM) [OK] Slow queries: 0% (1K/265M) [OK] Key buffer size / total MyISAM indexes: 16.0M/160.4M [OK] Key buffer hit rate: 100.0% (2B cached / 36K reads) [OK] Query cache efficiency: 73.9% (179M cached / 243M selects) [!!] Query cache prunes per day: 6750338 [OK] Sorts requiring temporary tables: 0% (15K temp sorts / 11M sorts) [!!] Joins performed without indexes: 501667 [!!] Temporary tables created on disk: 39% (7M on disk / 19M total) [OK] Thread cache hit rate: 89% (123K created / 1M connections) [OK] Table cache hit rate: 35% (5K open / 16K opened) [OK] Open file limit used: 0% (163/30K) [OK] Table locks acquired immediately: 99% (126M immediate / 126M locks) [OK] InnoDB data size / buffer pool: 10.3G/24.0G
Key figures from MySQL Tuning Primer:
INNODB STATUS Current InnoDB index space = 9.59 G Current InnoDB data space = 10.27 G Current InnoDB buffer pool free = 74 % Current innodb_buffer_pool_size = 24.00 G Depending on how much space your innodb indexes take up it may be safe to increase this value to up to 2 / 3 of total system memory MEMORY USAGE Max Memory Ever Allocated : 26.97 G Configured Max Per-thread Buffers : 2.42 G Configured Max Global Buffers : 24.53 G Configured Max Memory Limit : 26.95 G Physical Memory : 62.94 G Max memory limit seem to be within acceptable norms … Current query_cache_size = 512 M Current query_cache_used = 429 M Current query_cache_limit = 2 M Current Query cache Memory fill ratio = 83.79 % Current query_cache_min_res_unit = 4 K However, 39248536 queries have been removed from the query cache due to lack of memory Perhaps you should raise query_cache_size JOINS Current join_buffer_size = 15.00 M You have had 502190 queries where a join could not use an index properly join_buffer_size >= 4 M This is not advised OPEN FILES LIMIT Current open_files_limit = 30160 files The open_files_limit should typically be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_open_cache = 15000 tables Current table_definition_cache = 5000 tables You have a total of 3530 tables You have 5784 open tables. The table_cache value seems to be fine TEMP TABLES Current max_heap_table_size = 10.00 G Current tmp_table_size = 10.00 G Of 11854537 temp tables, 39% were created on disk Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables