Everyone who ever used MySQL under heavy load faced the performance problem. There numerous manuals and advises, tricks and “how to ?” examples. But you will definitely not find the solution which suits 100% for your project and will fix all your issues. You have to learn it. You have to spend your time.
The biggest misunderstanding nowadays is performance advertised by various cloud serviced where dedicated MySQL solution is possible for renting. Usually they advertise the performance in QPS (queries per seconds). And looks that this is hassle free solution. The problem is that these figures are based on synthetic tests and have nothing with your real world demands. Once your DB will behave “unusual” CloudDB will suggest you separate solution where some tuning will be required. And either you will do it or you have to hire to do it for you.
So leaning is the key to gain the proper performance. So today I will share one of the tool which can give some performance boost to you DB. I’m sure you heard about memcahce. And I’m sure that once you were confused about the difference about memcahe and memcahed. Don’t you ? On the top of it I can guess that you also ever been confused about php-memecahe and php-memcached and the way to install all this stuff.
To explain all these difference is not the purpose of this article, but I will try to make it shot way. Memcache appeared before memcached. Previously the were different by it’s nature, memcache was suitable for singe server solution memcached for distributed cache on multiple servers. Now days you can easily use memcached on the single server. Same for php-memcahce and php-memcached. To make it possible to work for instance on single server for CMS like WordPress memcahed+php-memcached will be enough.
However if you wish to use the benefit of memcached but do no wish to dig into tunign of this tool, there is slightly less affected but totally hassle free solution directly from MySQL starting from version 5.6.
This is InnoDB memcached plugin. All you need to do is to activate in on you working database.
to activate plugin you need to login MySQL console as root and enter:
mysql> install plugin daemon_memcached soname "libmemcached.so";
to disable it:
mysql> uninstall plugin daemon_memcached;
Keep in mind that this solution will be 25-50% slower (at least for heavy loaded wordpress/buddypress), will take you almoast no time to implement it.
So the conclusion:
This is hassle free solution directly from MySQL starting from version 5.6 or later, also available in MariaDB (since version 5.3) and Percona (since version 5.5).
InnoDB memcached plugin works perfectly on single server and suites WordPress.