Drupal scaling and performance tuning - Part 2
07 Oct 2011Drupal is highly relational. When it comes to performance, MySQL has a
big role to play in Drupal world. As I explained in Drupal scaling and
performance tuning - Part 1, we were able tune Apache to handle much load during high
traffic hours. But MySQL didn't give a chance to rest.
As the first step, we decided to look at the MySQL slow queries to
identify bad queries. Server logged all queries that took more than 2 seconds to process and most
of them were node permissions, user sessions, access log, cache, comments, watchdog and node contents.
The server load was gone up and it was almost at the frozen state most of the
time. As a solution, I knew that we would have to end up with a modified MySQL configuration file.
But, What are the Parameters I need to change and what are the values for
them, like i did for Apache?
Then I came across with a tool called MySQLTuner-perl which is a script
written in Perl that allows us to review a MySQL installation quickly and make adjustments to increase
performance and stability. The current configuration variables and status data are retrieved and
presented in a brief format along with some basic performance suggestions
Tools used
- MySQLTuner-perl
- Maatkit ( Power tools for open-source databases)
Mysql Optimization - I will not list all configurations here as MySQL
Tuner gives you a good guide
- MySQL Tuner
- Download the Mysql Tuner (wget https://github.com/rackerhacker/MySQLTuner-perl/blob/master/mysqltuner.pl)
- Make it executable (chmod +x mysqltuner.pl)
- Run MySQL Turner - You need your MySQL root password in order to execute this (./mysqltuner.pl)
- You should carefully read the output, especially the recommendations at the end. It shows exactly which variables you should adjust in the [mysqld] section of your my.cnf (on Debian and Ubuntu the full path is /etc/mysql/my.cnf). Whenever you change your my.cnf, make sure that you restart MySQL. You can then run MySQLTuner again to see if it has further recommendations to improve the MySQL performance. This way, you can optimize MySQL step by step.
- Maatkit
- mk-duplicate-key-checker - helped me to find duplicate indexes and foreign keys on MySQL tables.
- Removed all duplicate indexes and foreign keys - This helped MySQL to process SQLs smoothly.
- mk-query-digest and mk-query-profiler helped to profile and test new configurations/ Modifications to the database.
- mk-variable-advisor to double check changes and recommendations made by MySQL Tuner.
- Converted comments, node, users tables from MyISAM to InnoDB
- mysqlcheck -o -A -p command optimized other tables in the Drupal Database
Other Optimizations used
- Disable watchdog, Statistic modules from Drupal to reduce the read/write load to MySQL
- Uninstalled all unused modules.
Finally I was able to get Our Oxygentank Database to a state where
it can breathe freely without running on full power all the time.
But I was not happy with this setting since the second MySQL master server was always running silently
without helping the Primary Master server to handle its load.
I used NGINX to solve this
problem.
I will discuss how I used NGINX to share MySQL load between Databases / Drupal nodes with the help of Memcached, in part 3.