Enable the query cache in MySQL to improve performance

by on March 24, 2006 · 22 comments· LAST UPDATED July 31, 2007

in , ,

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:

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.

TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 22 comments… read them below or add one }

1 vijay prakash February 7, 2007 at 6:53 am

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

Reply

2 nixCraft February 7, 2007 at 12:47 pm

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

Reply

3 vijay prakash February 7, 2007 at 1:13 pm

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

Reply

4 nixCraft February 7, 2007 at 3:18 pm

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

Reply

5 Shelon Padmore January 17, 2008 at 2:06 pm

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

Reply

6 Siddique January 21, 2008 at 5:21 am

Hi, Nice good information

Reply

7 Me April 5, 2008 at 1:04 pm

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

Reply

8 dog July 11, 2008 at 1:49 am

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.

Reply

9 mmorpg September 27, 2008 at 2:33 am

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

Reply

10 Nathan October 15, 2008 at 8:45 pm

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

Reply

11 rapidshare library January 22, 2009 at 11:26 am

here

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

16777216 Bytes

Reply

12 Mr.Urs February 19, 2009 at 5:37 am

Good information

Reply

13 111110100 December 17, 2009 at 8:42 am

You can also explicitly set to my.cnf

query_cache_size=256M
query_cache_type=1
query_cache_limit=1M

Reply

14 Purab Kharat October 18, 2010 at 7:22 am

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

Reply

15 Rob Chant August 18, 2011 at 3:08 pm

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

Reply

16 Deepak Agrawal January 3, 2011 at 12:27 am

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

Reply

17 Milos Popovic March 1, 2011 at 12:25 pm

Tnx very much, it’s works :D

Reply

18 khupcom September 5, 2011 at 1:05 am

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

Not working with mysql 5.5

Reply

19 V January 27, 2012 at 9:43 am

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

Reply

20 MetaKafe May 15, 2012 at 9:02 pm

Thank you verry much..

Reply

21 ali August 8, 2013 at 12:49 pm

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

thanks for the information

Reply

22 Sohail Ahmed August 12, 2014 at 7:37 am

@Ali, checkout mysqltuner.com.

Reply

Leave a Comment

Tagged as: , , ,

Previous post:

Next post: