What to do with unused RAM on a MySQL database server?

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