Optimize MySQL Query Cache?

Philippe Gaucher

Well-known member
Collaborate
Registered
Joined
Jul 27, 2016
Messages
184
Points
18
I heard that there is a way to optimize/enable MySQL Query cache to boost website loading speed.

It is possible and how to do that?
 

VirtuBox

Well-known member
Registered
Joined
May 3, 2016
Messages
1,622
Points
83
Be careful with the cache size. The query_cache_size define the size of a unique query. Not the size of all the queries.

And the best way to boost a website loading speed is to use a caching system. Because it will reduce the number of queries. MySQL system will never be fast enough even with an huge queries cache.
 

MooseLucifer

Well-known member
Registered
Joined
May 20, 2016
Messages
149
Points
28
Be careful with the cache size. The query_cache_size define the size of a unique query. Not the size of all the queries.

And the best way to boost a website loading speed is to use a caching system. Because it will reduce the number of queries. MySQL system will never be fast enough even with an huge queries cache.
What caching solution would you recommend for the OP?

I heard that there is a way to optimize/enable MySQL Query cache to boost website loading speed.

It is possible and how to do that?
If you want to Optimize MySQL Query Cache then applying these steps here and its simple.

Login into your MySQL

Code:
# mysql -u root -p
You will be prompted this, enter your password to continue

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 333
Server version: 5.5.50-38.0 Percona Server (GPL), Release 38.0, Revision b05b24c

Copyright (c) 2009-2016 Percona LLC and/or its affiliates
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Check current status of Mysql cache

Code:
show variables like 'query_cache_%';
My result is

mysql> show variables like 'query_cache_%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)

mysql> Ctrl-C -- exit!
How to edit MySql cache

query_cache_size: the size cache (bytes), should not give too big.

query_cache_type: ON or OFF

query_cache_limit: The limitation of a cache query

To configure MySQL Query cache, you need to edit in file /etc/my.cnf (Red Hat) or /etc/mysql/my.cnf (Debian)

Code:
nano /etc/mysql/my.cnf
Edit according to your sizes

Code:
query_cache_limit = 3M
query_cache_size = 128M
Restart mysql

Code:
service mysql restart
Check current status of Mysql cache after restarted

Code:
mysql> show status like 'Qc%';

Hope it helps!
 

VirtuBox

Well-known member
Registered
Joined
May 3, 2016
Messages
1,622
Points
83
VirtuBox
There are several caching system, but depending on what software you are using, it could not work.

For WordPress

you can use memcached (require to have php-memcached extension and a memcached-server running) and the plugin W3TC with the following settings :

Page Cache: Disk Enhanced
Database Cache: Memcached
Object Cache: Memcached
Browser Cache: Disable

memcached.jpg


Or Redis-cache (require to have a redis-server running and the php-redis extension) with the plugin Redis Object Cache

For redis, if you are running several wordpress with it, you will have to add


Code:
define( 'WP_CACHE_KEY_SALT', 'virtubox:' ); --> replace virtubox with any name for you site, but 2 websites shouldn't have the same key
define( 'WP_REDIS_PORT', '6379' ); -> add the redis server port, needed only if you have a different port than 6379. But for security reason, you should use another port than default.
After for Prestashop, there is no way at the moment to have redis, but it support memcached natively.

And if you are looking for a true challenge for redis-cache you should try magento. It's really an heavy software to run, and you will understand it just by reading the default configuration for php (2GB memory / 1800 sec time for scripts).*
 

EpicGlobalWeb

Well-known member
Registered
Joined
Jan 24, 2016
Messages
180
Points
0
I heard that there is a way to optimize/enable MySQL Query cache to boost website loading speed.

It is possible and how to do that?
Not sure if you've done this already but it's a common thing that comes up in web development: change your queries to run on InnoDB instead of MyISAM. This will also give you a performance boost.
 
Older Threads
Replies
6
Views
2,901
Replies
1
Views
1,947
Replies
11
Views
3,587
fwh
Latest Threads
Replies
0
Views
30
Replies
0
Views
72
Replies
1
Views
226
Similar Threads
Replies
0
Views
2,773
Replies
6
Views
2,400
Replies
2
Views
3,444

Latest Hosting OffersNew Reviews

Sponsors

Tag Cloud

You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.

Top