How to Enable Query Cache in MariaDB for Performance in CWP/hestiacp/Centos/Ubuntu

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';

example :

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 query_cache_type is 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 [mysqld] section.

For CWP/centos file is located in : /etc/my.cnf or /etc/my.cnf.d/server.cnf

For ubuntu/Debian file is located in : /etc/mysql/my.cnf

Open the file in a text editor and add the following lines:

query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M

The 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.