1

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 <major@mhtx.net>
-------- 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
  • Is your application performing as expected? Do you need to improve performance in some way? – Phil Sumner Jul 2 '14 at 9:20
  • As you have discovered, diminishing returns are normal when tuning MySQL memory usage. The only thing I see likely to increase performance is to investigate those joins not using indexes; maybe you need some composite (multi-column) indexes. As for the spare memory, run some virtual machines to host other tasks for the Drupal site or host another, smaller site. – dartonw Jul 2 '14 at 15:03
  • @dartonw: I am aware of the joins not using indexes, but as far as I know, I have very limited possibilities to change this. Drupal has it's static data model, and queries are built either with contributed 3rd party modules in PHP, or with an query generator (Views). Virtual machines do IMHO make not much sense on a dedicated database server (the websites and the webservers are on another dedicated machine). – user153878 Jul 2 '14 at 15:11
  • Ah right, forgot about all the dynamic query construction. You can of course still add indexes, but it would be difficult to find where they're needed, and 500K/243M is well under 1% anyway. – dartonw Jul 2 '14 at 15:18
  • @Phil Sumner: The web application (Drupal) already uses a number of means to deliver an acceptable performance (e.g. internal query caching, JS/CSS aggregation, APC opcode caching, memcached, Varnish, sharded subdomains for static content etc.). Web pages/elements, that are delivered through Varnish, are performing OK (<1 sec.). Elements that are not in the cache(s), are performing very poorly (6-20 secs first byte time). Since not everything can be kept in and delivered through a reverse proxy like Varnish, I need to figure out a way to make the backend faster - if possible ;) – user153878 Jul 2 '14 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.