Unknown system variable ‘query_cache_size’

You should be aware that the message while using MySQL RDS instance “Unknown system variable ‘query_cache_size‘” is not a result of an error but rather is typical behavior.

According to MySQL, setting the query cache typically results in performance reduction. Because of its many issues, the query cache was deprecated in MySQL 5.7.20 and removed from MySQL 8.0. As a result, Amazon RDS advises against using it.

The query cache does not work in an environment where you have multiple mysqld servers updating the same MyISAM tables.

If you are using the following query to determine how much Memory your database will ever consume, you need to modify it right away. This will throw the above-specified error message.

SELECT ( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * (
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;

Now your calculation must be with the following variables.

Maximum MySQL Memory Usage =
+ key_buffer_size
+ ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size)
X max_connections)

Hope you find this article useful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s