How to Improve MySQL Server Performance with Proper Optimization

Setting Up Cache for MyISAM and InnoDB

MyISAM Cache

MyISAM is an older MySQL storage engine and uses key_buffer_size as the main parameter to set the size of its index cache. This parameter is very important because cached indexes can be accessed much faster than indexes that need to be read from disk.

key_buffer_size: Specifies the cache size for the MyISAM index. Setting this size too small can lead to frequent slow disk access while setting it too large can leave a bit of memory for other needs. As a general rule, key_buffer_size should be set between 25-30% of the total physical memory on a server that only uses MyISAM.

Example settings in the MySQL configuration file (my.cnf):

[mysqld]

key_buffer_size = 4G

InnoDB Cache

InnoDB is the default and most widely used storage engine in MySQL. InnoDB uses innodb_buffer_pool_size to set up the main cache that stores the data and indexes of InnoDB tables.

innodb_buffer_pool_size: Specifies the size of the buffer pool used to store data and indexes. This setting is critical to InnoDB’s performance because frequently accessed data is stored in a buffer pool, reducing disk access. As a general rule, innodb_buffer_pool_size should be set between 60-80% of the total physical memory on a server that only uses InnoDB.

Example settings in the MySQL configuration file (my.cnf):

[mysqld]

innodb_buffer_pool_size = 12G

innodb_buffer_pool_instances: If the buffer pool is large (more than 1GB), you can split it into multiple instances to increase the concurrency. For example, if innodb_buffer_pool_size is set to 12GB, you can split it into 4 instances of 3GB each:

[mysqld]

innodb_buffer_pool_size = 12G

innodb_buffer_pool_instances = 4

Query Cache (General)

MySQL also has a query cache that stores the results of frequently repeated queries to speed up the execution time of subsequent queries. However, in the latest version, the query cache is considered obsolete and has been removed in MySQL 8.0 due to performance issues in environments with a lot of writes.

query_cache_size: If you are using an older version of MySQL, you can set the cache query size with this parameter. However, we recommend using app-level caching or cache proxies for better management.

[mysqld]

query_cache_size = 256M

7. Setting Up Cache for MyISAM

Setting the Buffer Key Size for MyISAM

The buffer key (key_buffer_size) is the primary cache used by the MyISAM storage engine to store indexes. Proper setting of the key_buffer_size is essential to ensure optimal performance because frequently accessed indexes are stored in a buffer, reducing the need for slower disk access.

key_buffer_size: The size of the buffer key determines how much memory is allocated to store the MyISAM index. This setting should be adjusted based on the amount of physical memory available and the amount of data accessed periodically. In general, if your server uses MyISAM exclusively, you can allocate between 25-30% of the total physical memory to key_buffer_size.

Example settings in the MySQL configuration file (my.cnf):

[mysqld]

key_buffer_size = 4G

This customization allows MySQL to store more indexes in memory, which can speed up frequently executed searches and queries.