Unix and Linux network configuration. Multiple network interfaces. Bridged NICs. High-availability network configurations.


Reviews of latest Unix and Linux software. Helpful tips for application support admins. Automating application support.


Disk partitioning, filesystems, directories, and files. Volume management, logical volumes, HA filesystems. Backups and disaster recovery.


Distributed server monitoring. Server performance and capacity planning. Monitoring applications, network status and user activity.

Commands & Shells

Cool Unix shell commands and options. Command-line tools and application. Things every Unix sysadmin needs to know.

Home » MySQL

MySQL query cache

Submitted by on August 17, 2007 – 11:15 am 4 Comments

Query cache can significantly speed up database operations for identical search queries. This is particularly useful for Web server applications, where multiple requests for the same data are a regular occurrence.

To enable query caching on startup:

vi /etc/my.cnf (or my.ini under Windows) and set variable query_cache_type to 1

To view the current status of query cache:

mysql> SET GLOBAL query_cache_size = 20971520 ;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE '%query_cache%';
| Variable_name | Value |
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_size | 20971520 |
| query_cache_type | ON |
4 rows in set (0.00 sec)

If query_cache_size is set to 0, then caching is not allowed, even if it’s enabled. To change this value:

mysql> SET GLOBAL query_cache_size = 20971520 ;

This sets query cache size to 20Mb.

Print Friendly, PDF & Email


  • Gage says:

    I’ve lost count of the number of times I have closed my client browser because I requested a search I didn’t need. When I do this, do popular webservers (IIS, Apache) tell popular attached database systems (like MsSQL, MySQL, Oracle) to abort the search process or does it carry on running until completion/timeout?
    Is this standard functionality or does it rely on the programmer writing it in to the script?

  • Squall Leonhart says:

    i want people who register to my site to
    upload an avatar photo.

    and code to retrieve the pic would be appreciated

  • Salam says:

    I am currently trying to build a blog which goes on top of the forum I’m using. Lets say all topics from forum 2 will go on the blog page. I also need it to call the first post from each of these topics. Would a subquery work in this event, or should I just use multiple queries and cache it somehow?

    But there are also going to be other posts that share the same topic, in which case, I don’t want these displayed on the blog page.

    Sorry if thats not very clear ;)

  • Malcolm Hudson says:

    Our server some time gone stuck mysql using heavy ram and load we need to reduce Mysql server memory requirements
    For Reducing Mysql server memory requirements I got this info from this link

    Our dedicated server is
    •Intel N2800 DC 4×1.86 GHz
    •RAM 4GB
    •HDD 2 x 1TB
    •Speed 100Mbps/1Gbps port
    •Linux ready
    •Software RAID
    And we are using Centos 5 64-bit and kloxo
    1.If I added in file /etc/my.cnf following under and after [mysqld]

    port = 3306
    key_buffer = 16K
    max_allowed_packet = 1M
    table_cache = 4
    sort_buffer_size = 64K
    read_buffer_size = 256K
    read_rnd_buffer_size = 256K
    net_buffer_length = 2K
    thread_stack = 64K

    2.And at end of file /etc/my.cnf

    max_allowed_packet = 16M


    And try to restart the mysqld then mysqld don’t turn off but start again

    And when we try to access any web then this error comes as follows:
    Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)

    And if we remove above then all come back on old position and heavy use of mysqld ram and server load

    How we can solve this problem and how we can reduce server memory requirements?

Leave a Reply

%d bloggers like this: