Mysqltuner suggestions and changes to my.cnf

Question

Had this question on Serverfault for a few days with no luck.

I've run mysqltuner.pl on a VPS and have a bunch of questions as to the suggestions on variables to change. I'm sure these are general questions with complex answers.

I'm not knowledgable enough to write queries and test them against the server, but am just trying to get a bit more performance out of the server that runs five WordPress sites with >200,000 page views/month.

I've optimized the database via phpmyadmin (and do that regularly), but the tuner still says there are fragmented tables. And because this is WordPress, I can't change queries in core code.

But how much should I increase the variables like query_cache_size and innodb_buffer_pool_size? What about the other innodb variables?

Some of the variables suggested don't exist in my.cnf, like table_cache, and are flagged in tuner report, etc. Can I add them to my.cnf?

(And why is this block duplicated in my.cnf? Can I delete the duplicate?)

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

Below is the my.cnf and the output of mysqltuner:

Contents of my.cnf:

query-cache-type = 1
query-cache-size = 8M

set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

old_passwords=1

skip-bdb

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-bdb

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

Output of mysqltuner:

------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.45
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 133M (Tables: 637)
[--] Data in InnoDB tables: 10M (Tables: 344)
[--] Data in MEMORY tables: 126K (Tables: 2)
[!!] Total fragmented tables: 69

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 6h 24m 13s (2M q [22.135 qps], 116K conn, TX: 4B, RX: 530M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 35.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 303.7M (8% of installed RAM)
[OK] Slow queries: 0% (4/2M)
[OK] Highest usage of available connections: 53% (53/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/46.1M
[OK] Key buffer hit rate: 99.6% (749M cached / 2M reads)
[OK] Query cache efficiency: 32.2% (685K cached / 2M selects)
[!!] Query cache prunes per day: 948863
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 660K sorts)
[!!] Temporary tables created on disk: 46% (400K on disk / 869K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 24K opened)
[OK] Open file limit used: 10% (109/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[!!] InnoDB data size / buffer pool: 10.6M/2.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 8M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 10M)

Answer 1

My recomendations for tunning MySQL for WP:

Database tables should be periodically optimized (and repaired if necessary) for optimum performance.

I recommend using WP-DBManager plugin which provides this functionality as well as database backup, all crucial for any blog installation.

WP-DBManager allows you to schedule and forget, and it will take care of all the work automatically.

Other alternative is manually optimizing and repairing your table through a tool like phpmyadmin.

The MySQL Query Cache saves results of queries in case the query comes by again. However, it only knows how to save the byte-text of queries, not their compiled versions, so small changes to the query will create different cache entries. Turn this on if you don’t have unique ids in every query. You can enable it by adding the following to /etc/my.cnf:

query_cache_type = 1
query_cache_size = 26214400

Answer 2

I'll to my best to help here. The MysqlTuner report implies that you have 4GB of RAM in this VPS, so my suggestions are based on that.

query_cache_size - This is the amount of RAM MySQL can use to cache the results of database queries. Results stored in the query cache are returned much faster than normal selects, so this variable can significantly speed things up (more so than any of the other suggested changes).

Exactly what the correct value is for you will take some experimentation. You currently have this set to 8M. If you have 4GB of RAM in this box I'd start at 64M, increasing to 128M and then 256M if required. After each change, leave things for a few days and then run MysqlTuner again and compare the percentage for 'Query cache efficiency' to what it was before. For a server mainly hosting 5 Wordpress blogs I doubt you'd see any improvement beyond 256M, and I wouldn't recommend going beyond an eighth of your total RAM.

Personally I find Munin (a free server monitoring tool) quite handy for keeping an eye on this sort of thing. E.g. here's the MySQL graph from one of my boxes:

The light green area shows the normal MySQL selects hitting the database. The pink area shows the selects served by the cache, so pink = good.

tmp_table_size - for some complex queries (particularly those using GROUP BY or complex sorting), MySQL needs to first create a temporary table containing the data and then run some operations on it in order to create the result set. It will try and create these temp tables in memory, as this is much faster than creating them on disk; but for large result sets this isn't always possible. tmp_table_size controls this threshold.

I can't imagine Wordpress is doing any hugely complex queries so I wouldn't go overboard with this one. MysqlTuner is suggesting a value greater than 32MB, so start with 64M and see how this affects the 'Temporary tables created on disk' value after a few days. Set max_heap_table_size while you're at it as it suggests.

thread_cache_size - Wordpress doesn't use persistent connections by default (which is good), so each request is making a new connection to your database and then closing this once the page has been generated. This overhead is not significant, but using thread_cache_size allows MySQL to reuse these connection threads which will help a little.

I'd go with the suggested value of 4 which I'd imagine will be fine unless you get a high number of concurrent users.

table_cache - I'm a little hazy on this one, it seems to relate to MySQL's cache of table structure. I'd go with 128 for this.

innodb_buffer_pool_size - this is the amount of memory MySQL can use to cache indexes and data for InnoDB tables. This one puzzles me a bit as I don't think Wordpress uses InnoDB at all - do you have some other sites on this server as well?

To answer your other questions, the configuration after [mysqld_safe] only apply to the MySQL daemon in safe mode, rather than MySQL overall, so that's why some of the variables are duplicated. If you do change innodb_buffer_pool_size, you'll want to change the first one. The variables not in the file you can add, yes, but add them above the [mysqld_safe] block for the same reason.

Lastly, since you're in the mood for optimising, if you are not already using a PHP bytecode cache such as APC then this is worth exploring. APC can give some significant speed improvements to PHP apps without any negative effects.

Answer 3

This is not a direct answer to your question but in my experience wordpress can be very well optimized using caching on the frontend servers. Also mostly wordpress seems to be CPU-bound on the frontend machines not on the database. (you might want to double check that indeed you are optimizing the bottleneck).

Have a look at "w3 total cache" for example. Using it with APC should be the most simple approach. Make sure you have a look at the size of apc.shm_size (in php.ini) and cache-hit ratio (some apc info utility should be supplied with w3tc).

I have seen wordpress instances running smoothly on a single server with that setup and way more than just 200.000 page views per month.

Answer 4

There are more tools out there to tune your mysql database: http://www.day32.com/MySQL/ and http://www.maatkit.org/doc/ and http://hackmysql.com/mysqlsla

In most cases you don't no need to write queries and test them against the server. Just enable the slow query log to identify your slow queries aggregate them with mysqlsla and explain them with maatkit:

You could paste the slowest queries from the mysqla results to a text file and execute them with maatkit.

mk-visual-explain –host hostname –user username –password passwort –database databasename -c query1.sql >> query1_data.txt

mk-query-profiler –host hostname –user username –password passwort –database databasename query1_data.txt >> query1_data.txt

Often coosing a newer mysql version is critical to performance. I experienced that the execution plans for complex queries are very different when you compare for example mysql 5.0.23 to 5.1.4. They are executed in our environment much faster with 5.1.4.

Lot's of useful information about mysql can be found at http://www.mysqlperformanceblog.com/ and in the book "High Performance MySQL".

Tabe Cache: According to the book "the table cache stores objects that represent tables. Each object in the cache contains the associated table's parsed .frm file plus other data, depending on the table's storage engine.

The table cache's design is a little MyISAM centric - this is one of the areas where the seperation between the server and the storage engine is not completely clean, for historical reasons. The table cache is a little less important for InnoDB, because InnoDB doesn't rely on it for as many purposes(such as holding file descriptors; it has its own version of a table cache for this purpose). However, even InnoDB benefits from caching the parsed .frm files.".

If you raise the table cache, there might be errors with the open files limit. You also need to increase the open_files_limit variable on the server and perhaps the operating system open files limit: http://www.cyberciti.biz/faq/linux-increase-the-maximum-number-of-open-files/.

Thread Cache:

The thread cache holds the threads that aren't currently associated with a connection but are ready to serve new connections. As long as MySQL has a free thread in the cache, it can respond very rapidly to connect requests, because it doesn't have to create a new thread for each connection.

[!!] Temporary tables created on disk: 46% (400K on disk / 869K total) If tmp_table_size and max_heap_table_size are not set yet, increase them. Disk operations are very slow compared to RAM-operations. Does wordpress use lots of blob/text columns? then you won't see much benefits, because BLOB and Text columns are not allowed in memory tables.

[OK] Highest usage of available connections: 53% (53/100) To save RAM you could decrease the allowed max connections. On the other hand you might run out of connections in peak times.

Using an opcode cache for PHP is a very good idea!