クエリキャッシュのパフォーマンスを最大限に引き出す：query_cache_sizeとquery_cache_limitの最適化ガイド

MySQLとMariaDBにおけるquery_cache_sizeとquery_cache_limitの違い

query_cache_sizeとquery_cache_limitは、MySQLとMariaDBで利用可能なクエリキャッシュに関するシステム変数です。どちらもクエリキャッシュの動作に影響を与えますが、異なる役割を果たします。

query_cache_size

• クエリキャッシュに割り当てられる総メモリ量を決定します。
• デフォルト値は1MBですが、設定ファイルで変更できます。
• メモリ使用量が多すぎると、パフォーマンスが低下する可能性があります。

• クエリキャッシュに保存できる個々のクエリ結果の最大サイズを決定します。
• この値を超えるクエリ結果はキャッシュされません。

例

• query_cache_sizeを10MBに設定し、query_cache_limitを2MBに設定した場合、2MB以下のクエリ結果はキャッシュされ、それ以上のクエリ結果はキャッシュされません。
• query_cache_sizeを0に設定すると、クエリキャッシュが無効化されます。

どちらを設定すべきか？

• クエリキャッシュの使用頻度が高く、メモリに余裕がある場合は、query_cache_sizeとquery_cache_limitを適切な値に設定することで、パフォーマンスを向上させることができます。
• クエリキャッシュの使用頻度が低い場合や、メモリが少ない場合は、クエリキャッシュを無効化することを検討する必要があります。

MariaDB 10.2以降

• MariaDB 10.2以降では、query_cache_sizeとquery_cache_limitは非推奨となり、将来のバージョンで削除される予定です。
• 代わりに、performance_schemaテーブルを使用して、クエリキャッシュの使用状況を監視することを推奨しています。

• クエリキャッシュは、MySQLとMariaDBの古いバージョンでのみ使用できます。
• MySQL 8.0以降では、クエリキャッシュは廃止され、代わりにPREPAREDステートメントが推奨されています。
• クエリキャッシュは、パフォーマンスを向上させる可能性がありますが、すべての状況で最適とは限りません。
• クエリキャッシュを使用する前に、その影響をベンチマークで評価することをお勧めします。

-- Set the query cache size to 10MB
SET GLOBAL query_cache_size = 10485760;

-- Set the query cache limit to 2MB
SET GLOBAL query_cache_limit = 2097152;

-- Check the current values of the query cache variables
SHOW VARIABLES LIKE 'query_cache%';

-- Run a SELECT query to generate some cacheable results
SELECT * FROM customers;

-- Check the query cache hit rate
SHOW STATUS LIKE 'Qcache%';

This code will first set the query_cache_size variable to 10MB, which means that the query cache can store up to 10MB of data. It will then set the query_cache_limit variable to 2MB, which means that only queries that result in less than 2MB of data will be cached.

Next, it will run a SELECT query to generate some cacheable results. Finally, it will check the query cache hit rate to see how often the cached results are being used.

Additional notes:

• The SET GLOBAL statement is used to set the variables for all subsequent connections to the MySQL server.
• The SHOW VARIABLES statement is used to display the current values of the variables.
• The SHOW STATUS statement is used to display statistics about the query cache.

The MySQL configuration file (my.cnf) can be used to set the query_cache_size and query_cache_limit variables. The following lines can be added to the my.cnf file:

query_cache_size = 10MB
query_cache_limit = 2MB

These settings will take effect the next time the MySQL server is restarted.

Using the SET statement in a script

The SET statement can be used to set the query_cache_size and query_cache_limit variables in a script. The following script will set the variables to the same values as the previous examples:

SET GLOBAL query_cache_size = 10485760;
SET GLOBAL query_cache_limit = 2097152;

This script can be saved as a file and then executed using the following command:

mysql -u root -p < set_query_cache_variables.sql

Using the MySQL client API

The MySQL client API can be used to set the query_cache_size and query_cache_limit variables programmatically. The following code snippet shows how to do this using the PHP MySQLi extension:

<?php

$mysqli = new mysqli('localhost', 'root', '', 'mydatabase');

if ($mysqli->connect_error) {
    die('Connection failed: ' . $mysqli->connect_error);
}

$mysqli->query('SET GLOBAL query_cache_size = 10485760');
$mysqli->query('SET GLOBAL query_cache_limit = 2097152');

$mysqli->close();

?>

php set_query_cache_variables.php

Considerations

• As mentioned earlier, the query_cache_size and query_cache_limit variables are non-deprecated since MySQL 8.0 and MariaDB 10.2. It is recommended to use the performance_schema tables to monitor query cache usage instead.
• The query cache can be a performance optimization tool, but it is important to use it judiciously. If the cache is too small, it will not be able to store many queries, and there will be little benefit. If the cache is too large, it will consume too much memory and could actually slow down performance.
• In general, it is a good idea to start with small values for the query_cache_size and query_cache_limit variables and then increase them as needed.
• It is also important to monitor the query cache hit rate to see how often the cached results are being used. If the hit rate is low, it may be a sign that the cache is too small or that the queries being executed are not a good fit for caching.