Today we’ll learn how you can enable query cache in MariaDB server, it has several caching mechanisms to improve performance the query cache stores results of SELECT queries so that if the identical query is received in future, the results can be quickly returned.
This is extremely useful in high-read, low-write environments (such as most websites). It does not scale well in environments with high throughput on multi-core machines, so it is disabled by default.
Here are the steps to enable query cache in MariaDB:
Check if query cache is enabled:
Before enabling query cache, you should check if it’s already enabled or not. You can do this by logging into your MariaDB server and executing the following command:
SHOW VARIABLES LIKE 'query_cache_type';
type mysql in command line and hit enter it will bring the mysql console and then execute show variable command
[root@server ]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 10.11.2-MariaDB-log MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_type'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | query_cache_type | OFF | +------------------+-------+ 1 row in set (0.001 sec)
If the value of
OFF, it means that query cache is not enabled.
Enable query cache:
To enable query cache, you need to modify the mariadb configuration file and add the below configs under
For CWP/centos file is located in :
For ubuntu/Debian file is located in :
Open the file in a text editor and add the following lines:
query_cache_type = 1 query_cache_size = 128M query_cache_limit = 4M
query_cache_type variable is set to 1 to enable query cache,
query_cache_size specifies the size of the cache in megabytes, and
query_cache_limit specifies the maximum size of a single query that can be cached.
Restart MariaDB server:
After modifying the
my.cnf configuration file, you need to restart the MariaDB server to apply the changes. You can do this by running the following command:
systemctl restart mariadb
Verify query cache is enabled:
To verify that query cache is enabled, you can log into the MariaDB server and execute the
SHOW VARIABLES LIKE 'query_cache_type'; command again. If the value of
query_cache_type is now
ON, it means that query cache has been enabled.
By following these steps, you can enable query cache in MariaDB and improve the performance of your database queries.