Tuning MySQL for eZ Publish

Before you tune your MySQL installation, it is useful to know the current settings. Use the show variables command in the MySQL command line interface to display the current settings.

mysql> show variables
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| back_log                | 50      |
| basedir                 | /usr/   |
| binlog_cache_size       | 32768   |
| bulk_insert_buffer_size | 8388608 |
....
....

You can view specific variables by using the like operator in the query. For example:

mysql> show variables like "innodb%";
+---------------------------------+------------------------+
| Variable_name                   | Value                  | 
+---------------------------------+------------------------+
| innodb_additional_mem_pool_size | 104857600              |
| innodb_autoextend_increment     | 8                      |
| innodb_buffer_pool_awe_mem_mb   | 0                      |
| innodb_buffer_pool_size         | 734003200              |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_fast_shutdown            | ON                     |
| innodb_file_io_threads          | 4                      |
| innodb_file_per_table           | OFF                    |
| innodb_flush_log_at_trx_commit  | 0                      |
| innodb_flush_method             |                        |
| innodb_force_recovery           | 0                      |
| innodb_lock_wait_timeout        | 40                     |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_arch_dir             |                        | 
| innodb_log_archive              | OFF                    |
| innodb_log_buffer_size          | 8388608                |
| innodb_log_file_size            | 183500800              |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 90                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_open_files               | 300                    | 
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 8                      |
+---------------------------------+------------------------+
26 rows in set (0.00 sec)

To monitor the health of your MySQL server and test the effect of configuration changes, you can use the show status command. This lists information about caches, threads, queries, cached queries and more. The like operator can also be used to limit the results. For example:

mysql> show status like "qcache%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 34       |
| Qcache_free_memory      | 16466312 |
| Qcache_hits             | 1313227  |
| Qcache_inserts          | 78096    |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 3328     |
| Qcache_queries_in_cache | 140      |
| Qcache_total_blocks     | 346      |
+-------------------------+----------+
8 rows in set (0.00 sec)

The MySQL Administrator GUI application can also be used to view the settings and monitor the status of your MySQL server. The MySQL Administrator is distributed under the GPL license.

The screenshot below shows the connection health screen in the MySQL Administrator application. It shows the number of connections, the traffic and the number of SQL queries at any given time. You can also add you own custom graphs to monitor specific values of your MySQL server.

MySQL Administrator - Connection Health

The screenshot below shows the MySQL status variables. It is equivalent to the show status command in the command-line interface described above.

MySQL Administrator - Status Variables

Changing MySQL configuration

To change MySQL settings via the command line, edit the my.ini configuration file. MySQL must be restarted after changing the configuration.

To change MySQL settings via the MySQL Administrator GUI application, edit the settings on the Options page.

MySQL Administrator - Options