Administrators/Optimize performance/MySQL

From Aimeos documentation

Administrators | Optimize performance
Other languages:
English 100% • ‎русский 100%


Analyze and optimize cache tables regularly

MySQL uses the index that fits best to perform the query and the decision is made on the cardinality of the index. Indices with a higher cardinality are preferred over indices with lower ones. Problems arise if the cardinality is incorrect and especially the mshop_index_text MyISAM table containing the fulltext index is known for being vulnerable to this. Therefore, it's a good idea to check the cardinality values in the mshop_index_* tables once in a while and especially if you think the product search is slower than in the past while the number of products is not much higher than before.

To get rid of the problem you have to execute the MySQL

  1. ANALYZE TABLE "table name"

statement and if this doesn't help, run the MySQL

  1. OPTIMIZE TABLE "table name"

statement afterwards. The statements are also executed after the index rebuild has been finished.

Please be careful with "OPTIMIZE TABLE" if your shop has a large number of records in these index tables as it hurts performance of the front-end during its execution quite drastically.

Optimize MySQL server settings if necessary

Depending of the size of the database and the number of rows in the different tables, it might be necessary to adapt some MySQL server settings to get the maximum speed out of Arcavias.

The easiest way to find out if there are any problems caused by e.g. buffers that are too small is the "Status" tab (or "Show MySQL runtime information" link in older versions) on the home screen of phpMyAdmin. It contains the different server variables that can be changed, their value and a description sometimes including hints what to do. The cool thing about this view is that phpMyAdmin highlights the values in red that may be a source of problems.


Especially watch out for these keys:

Created_tmp_disk_tables 
The number of temporary tables that had been written to the hard disk because the buffer was too small. See tmp_table_size if this happened more than a few times
Innodb_buffer_pool_reads 
The number of times index data have to be read from the hard disk because it was not cached in memory. High values may indicate the need to increase the value for innodb_buffer_pool_size
Qcache_lowmem_prunes 
The number of queries that had been removed from the query cache because there was no more space left. If this value is high, try to increase query_cache_size
Slow_queries 
The number of queries that need longer than the configured number of seconds. If this value is high, your server is either to slow for the amount of simultaneous users that are hitting your site or there are some very slow queries executed very often. In the later case check if you've installed extensions for Arcavias that doesn't scale well and can cause performance problems
Sort_merge_passes 
The number of merge passes the sort algorithm has been forced to do. Higher values can be a hint to increase the sort_buffer_size


These MySQL variables are worth looking for:

innodb_buffer_pool_size 
The higher this value, the more data and indexes are available in memory and this is much faster than reading them from the hard disk
query_cache_size 
The higher the value, the more queries can be answered directly without accessing the hard disk
sort_buffer_size 
The buffer size that is available for sorting result sets if no index can be used
tmp_table_size 
The size of the in-memory buffer for temporary tables before they are written to the hard disk

Keep all indexes in memory

If you have enough RAM, you should try to keep all indexes in memory to reduce the need to access the hard disc to a minimum. Nevertheless, make sure that key_buffer_size + innodb_buffer_pool_size does not exceed 75% of the available RAM and that there's enough RAM for the database connections and the operating system.

To find out the size (in MB) of all indexes, use this statements:

InnoDB indexes

  1. SELECT CEIL( SUM( index_length + data_length ) / POWER( 1024, 2 ) ) innodb_buffer_pool_size_MB
  2. FROM information_schema.TABLES WHERE engine = 'InnoDB';

MyISAM indexes

  1. SELECT CEIL( SUM( index_length ) / POWER( 1024, 2 ) ) key_buffer_size_MB
  2. FROM information_schema.TABLES WHERE engine = 'MyISAM'
  3. AND table_schema NOT IN ( 'information_schema', 'performance_schema', 'mysql' );

Load the most important indexes into the RAM

To get the maximum performance that is possible it's necessary to reduce the need to access the hard disc to a minimum. For this, you must have enough RAM to store the indexes of the mshop_index_* tables into the MySQL cache.

For InnoDB tables, the OPTIMIZE TABLE statement makes sure that the complete indexes (and data as InnoDB stores both in one data structure) of the tables are in the cache if it's big enough:

  1. OPTIMIZE TABLE mshop_index_attribute
  2. OPTIMIZE TABLE mshop_index_catalog
  3. OPTIMIZE TABLE mshop_index_price

There's another statement required the MyISAM table for the texts, that contain the fulltext index:

  1. LOAD INDEX INTO CACHE mshop_index_text

If you have plenty of RAM in your database server, you can also load the mshop_product and mshop_product_list tables:

  1. OPTIMIZE TABLE mshop_product
  2. OPTIMIZE TABLE mshop_product_list