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:
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.
E-mail this to a Friend
Printable Version
Is your site working? Monitor Your Web Site 24/7. Get SMS alerts on server downtime. Free 30-day trial including 20 SMS!
You may also be interested in other helpful articles:
- How does query caching in MySQL works and how to find find out my MySQL query cache is working or not?
- How to improve Apache httpd performance with mod_cache - caching dynamic content
- MySQL Performance Tuning tips and techniques
- How to optimize a web page for faster and better experience
- How to tune MySQL server for performance
Discussion on This Article:
Leave a Reply
We encourage your comments, and suggestions. But please stay on topic, be polite, and avoid spam. Thank you very much for stopping by our site!
Tags: mysql cache size, mysql command, mysql query cache, mysql server
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
Stop Apache webserver using
/etc/init.d/httpd stopOR
killall -9 httpdAlso you need to run netstat -tulpn | grep :80 as root user so you will get program name or PID
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
Restart Linux system. Also note that you must run netstat command as root user. BTW what Linux distro you are using?
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
Hi, Nice good information
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).
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.
Thanks, that is always a big concern with multiple databases. Good tips for the MySQL
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..