Enable the query cache in MySQL to improve performance

If you want to get optimized and speedy response from your MySQL server then you need to add following two configurations directive to your MySQL server:

ADVERTISEMENTS

query_cache_size=SIZE
The amount of memory (SIZE) allocated for caching query results. The default value is 0, which disables the query cache.

query_cache_type=OPTION
Set the query cache type. Possible options are as follows:
0 : Don’t cache results in or retrieve results from the query cache.
1 : Cache all query results except for those that begin with SELECT S_NO_CACHE.
2 : Cache results only for queries that begin with SELECT SQL_CACHE

Howto enable caching in MySQL

You can setup caching as follows:
$ mysql -u root –p
Output:

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Now setup cache size 16Mb:

mysql> SET GLOBAL query_cache_size = 16777216;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size'; 

Output:

 +------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 16777216 |
+------------------+----------+

You can setup them in /etc/my.cnf (Red Hat) or /etc/mysql/my.cnf (Debian) file:
# vi /etc/my.cnf
Append config directives as follows:

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

In above example the maximum size of individual query results that can be cached set to 1048576 using query_cache_limit system variable. Memory size in Kb.

🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source & DevOps topics via:
CategoryList of Unix and Linux commands
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
27 comments… add one
  • vijay prakash Feb 7, 2007 @ 6:53

    Sir,
    I wanted to run a website on port 80 through apache webserver but it is showing that port is already in use. When i try to find out which application is running on port 80 by this command

    netstat -tulpn | grep :80

    it is showing following result

    tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN –

    Please help me how can i kill this so that port 80 can be utilised for apache web server.
    Thankyou

    Vijay

  • 🐧 nixCraft Feb 7, 2007 @ 12:47

    Stop Apache webserver using
    /etc/init.d/httpd stop
    OR
    killall -9 httpd
    Also you need to run netstat -tulpn | grep :80 as root user so you will get program name or PID

  • vijay prakash Feb 7, 2007 @ 13:13

    Sir,

    I have already used above commands but still the problem persist. When i use netstat then no pid/program name displays as i have already mentioned the output i am getting in earlier query. Please help me in this situation what should i do.

    thankyou.

    vijay

  • 🐧 nixCraft Feb 7, 2007 @ 15:18

    Restart Linux system. Also note that you must run netstat command as root user. BTW what Linux distro you are using?

  • Shelon Padmore Jan 17, 2008 @ 14:06

    Yes a reboot may help. Also try configuring httpd to listen on another port, just to see if all is well with the httpd installation.

    Shelon Padmore

  • Siddique Jan 21, 2008 @ 5:21

    Hi, Nice good information

  • Me Apr 5, 2008 @ 13:04

    In one sentence you show a 16MB cache size as 16777216. In another, you explain that the variable values (ie, 16777216) are in KB. You should correct this, as it might confuse folks who don’t know which it is (that is, if you express it as 16777216 it is NOT in KB, it’s in bytes).

  • dog Jul 11, 2008 @ 1:49

    You might be using skype. Skype is configured to use port 80 and 443 when it can’t use another port for incoming connections. Check your advanced options to turn this off, and Apache will run properly.

  • mmorpg Sep 27, 2008 @ 2:33

    Thanks, that is always a big concern with multiple databases. Good tips for the MySQL

  • Nathan Oct 15, 2008 @ 20:45

    I did some benchmarking with and without this setting enabled and noticed virtually no difference, I performed the exact same SELECT query about 900.000 times and marked the microseconds it took.. so in theory the cache should jump in right away..

  • rapidshare library Jan 22, 2009 @ 11:26

    here

    size are in bytes not in KB
    mysql> SET GLOBAL query_cache_size = 16777216;

    16777216 Bytes

  • Mr.Urs Feb 19, 2009 @ 5:37

    Good information

  • 111110100 Dec 17, 2009 @ 8:42

    You can also explicitly set to my.cnf

    query_cache_size=256M
    query_cache_type=1
    query_cache_limit=1M

  • Purab Kharat Oct 18, 2010 @ 7:22

    I am having one doubt..When this mysql query cache will be destroyed. Is there any setting available for destroying the cache time.

    • Rob Chant Aug 18, 2011 @ 15:08

      It uses a least recently used system for pruning the cache, so there’s no explicit timeout for cached queries.

  • Deepak Agrawal Jan 3, 2011 @ 0:27

    hey thx a lot very much for this information you have my a lot of time and money thx if you have any other information important in my sql plz inform me

  • Milos Popovic Mar 1, 2011 @ 12:25

    Tnx very much, it’s works 😀

  • khupcom Sep 5, 2011 @ 1:05

    [mysqld_safe]
    query_cache_type=1
    query_cache_size = 64M
    query_cache_limit= 64M

    Not working with mysql 5.5

  • V Jan 27, 2012 @ 9:43

    How one can determine the need to increase or decrease the query_cache_limit size?

  • MetaKafe May 15, 2012 @ 21:02

    Thank you verry much..

  • ali Aug 8, 2013 @ 12:49

    what is limit of the query cache good for?
    how to know best value?

    thanks for the information

  • Sohail Ahmed Aug 12, 2014 @ 7:37

    @Ali, checkout mysqltuner.com.

  • Andrew Jan 21, 2015 @ 10:34

    The one thing I’m curious about is why would someone NOT use cache? Why is no cache the default setting? Is there a downside to caching?

  • Dave Jan 30, 2015 @ 11:19

    if you set the cache size to 16Mb by command line in mysql then to set it in the /etc/my.cnf the config should be:

    query_cache_size = 16777216
    query_cache_type=1
    query_cache_limit=1048576

    setting query_cache_size = 268435456 in my.cnf sets the cache to 256M

  • Anh Tran Aug 27, 2015 @ 2:07

    I read the MySQL manual and it says

    If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution.

    Does that mean if we disable query cache, we reduce overhead in query execution and thus increase performance?

  • Yogesh Gupta Jun 27, 2016 @ 18:32

    Hi All,
    I am working on MySQL.. I deployed a new server using cadet and the below query takes only 2 sec on my server but on newly deployed server using application in which this query is written, takes about 6-7 sec.. Please give me some solution for it ..
    Should I set cache variable as above mention or is there any other solution to have the same speed on newly deployed server?

    Insert into table —(which has only 1 column)
    select max(column) from table1 group by columnname

    Thanks

    • Darren Mar 1, 2017 @ 1:36

      Copy your my.cnf config?

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.