| View previous topic :: View next topic |
| Author |
Message |
Paul
CastleCops Founder
Joined: Feb 22, 2002
Posts: 27351
|
 Posted: Wed Feb 20, 2008 3:04 pm Post subject: MySQL query_cache_size improvements |
|
|
[OK] Query cache efficiency: 74.9%
[!!] Query cache prunes per day: 337315
With an uptime of about eleven hours, so I know its not much. But, I've never truly had much success with those low_mem_prunes.
| Code: | mysql> show global variables like 'query%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| query_alloc_block_size | 16384 |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 1024 |
| query_cache_size | 104857600 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+-----------+
7 rows in set (0.00 sec)
|
Suggestions appreciated.
8 GB RAM.
_________________
Paul Laudanski - http://www.laudanski.com
http://www.linkedin.com/pub/1/49a/17b
|
|
| Back to top |
|
 |
Trpm
Security Expert
Premium Member
Joined: Jan 16, 2004
Posts: 1663
|
| Posted: Wed Feb 20, 2008 10:35 pm Post subject: |
|
|
Have you tried increasing your allocation block size?
Rex
_________________
I'm a reasonable person just ask Eeyore or Christopher R.
|
|
| Back to top |
|
|
Paul
CastleCops Founder
Joined: Feb 22, 2002
Posts: 27351
|
| Posted: Thu Feb 21, 2008 12:09 am Post subject: |
|
|
If there has been one thing which caused me confusion with mysqld, its query cache. I've attempted various values from 4k up to 32k and seem to get those darn lowmem prunes.
_________________
Paul Laudanski - http://www.laudanski.com
http://www.linkedin.com/pub/1/49a/17b
|
|
| Back to top |
|
|
Bill_Bright
General
Premium Member
Joined: Jan 16, 2004
Posts: 8963
Location: Nebraska, USA
|
|
| Back to top |
|
|
Paul
CastleCops Founder
Joined: Feb 22, 2002
Posts: 27351
|
|
| Back to top |
|
|
Paul
CastleCops Founder
Joined: Feb 22, 2002
Posts: 27351
|
| Posted: Sat Feb 23, 2008 8:22 pm Post subject: |
|
|
After about 20 hours uptime on the mysql 5.0.x install on the other system, here is what mysqltuner.pl reports:
[OK] Query cache efficiency: 97.4%
[!!] Query cache prunes per day: 190560
How to bring that down is the question?
Taking the values of
Qcache_lowmem_prunes / Uptime =
2.2285
Not a good value.
_________________
Paul Laudanski - http://www.laudanski.com
http://www.linkedin.com/pub/1/49a/17b
|
|
| Back to top |
|
|
Gena01
Trooper
Joined: Jun 07, 2007
Posts: 14
Location: USA
|
| Posted: Sat Feb 23, 2008 8:56 pm Post subject: |
|
|
From this article: http://www.sqlmanager.net/en/articles/704
Another indicator of poor query reuse is an increasing Qcache_lowmem_prunes value. This indicates how often MySQL had to remove queries from the cache to make use for incoming statements. Other reasons for an increasing number of Qcache_lowmem_prunes are an undersized cache, which can't hold the needed amount of SQL statements and result sets, and memory fragmentation in the cache which may be alleviated by issuing a FLUSH QUERY CACHE statement. You can remove all queries from the cache with the RESET QUERY CACHE command.
Another article that explains some of the parameters: http://www.quest-pipelines.com/newsletter-v6/1205_B.htm
Good luck,
Gena01
|
|
| Back to top |
|
|
|
|
Forum FAQ
Search
Usergroups
Profile
Login to check your private messages
Login
