Re: Questions regarding Query cache usage
On Tue, Jun 8, 2010 at 10:57 PM, Machiel Richards machi...@rdc.co.za wrote: Good morning all I would like to try and find out how you can see what is using the query cache. My reason for asking is the following: On one of our client databases, the query cache is set to 128Mb and the usage always varied between 5% and 53% and basically never went above that. However, this morning I noticed that the query cache usage is at 99.98% which is very odd for the database. How are you determining the cache usage? I don't think 99.98% utilitzation is a bad thing. It would be preferable to wasting memory on a cache that is under-utilized. Does anybody have an idea on how to determine why this usage is suddenly this high and if we should look at increasing the query cache size or not? Has a new workload been introduced to the server? The cache utilization may be indicative of a lot of small repeated queries being introduced. You can monitor the Qcache_lowmem_prunes and Qcache_free_blocks to determine if you can benefit from increased query cache size. http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/ I also have a second question relating to a previous post I sent through but never really received a definitive answer. The client database is setup with a master slave replication, the master Innodb buffer pool usage is at 4Gb at present (no more system memory available to increase this) We are starting to receive errors on the slave server however relating to the innodb buffer pool size being used up and there is no place to add more locks. This was found to be related to the slave server's innodb buffer pool size that is currently still set to 8mb. I would like to know whether it will be worth changing the value on the slave server to match that of the master server or will this cause more problems? If the memory is available, why not use it? It seems like the default buffer pool size out of the box was just never changed. Kyong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Questions regarding Query cache usage
On Wed, Jun 9, 2010 at 8:04 PM, Kyong Kim kykim...@gmail.com wrote: If the memory is available, why not use it? It seems like the default buffer pool size out of the box was just never changed. Agreed, of course, but if something happens on a system that is out of the ordinary, it's very good practice to hunt the cause down before it makes more undesireable things happen. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Questions regarding Query cache usage
Absolutely. You don't want to obscure the cause by just throwing more hardware at things. That approach just buys you time until a bigger pile hits the fan if the underlying issue remains unresolved. At the same time, though, 8 MB production innodb buffer pool allocation should be fairly high on the list of things to scrutinize. Kyong On Wed, Jun 9, 2010 at 12:12 PM, Johan De Meersman vegiv...@tuxera.be wrote: On Wed, Jun 9, 2010 at 8:04 PM, Kyong Kim kykim...@gmail.com wrote: If the memory is available, why not use it? It seems like the default buffer pool size out of the box was just never changed. Agreed, of course, but if something happens on a system that is out of the ordinary, it's very good practice to hunt the cause down before it makes more undesireable things happen. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Questions regarding Query cache usage
Good morning all I would like to try and find out how you can see what is using the query cache. My reason for asking is the following: On one of our client databases, the query cache is set to 128Mb and the usage always varied between 5% and 53% and basically never went above that. However, this morning I noticed that the query cache usage is at 99.98% which is very odd for the database. Does anybody have an idea on how to determine why this usage is suddenly this high and if we should look at increasing the query cache size or not? I also have a second question relating to a previous post I sent through but never really received a definitive answer. The client database is setup with a master slave replication, the master Innodb buffer pool usage is at 4Gb at present (no more system memory available to increase this) We are starting to receive errors on the slave server however relating to the innodb buffer pool size being used up and there is no place to add more locks. This was found to be related to the slave server's innodb buffer pool size that is currently still set to 8mb. I would like to know whether it will be worth changing the value on the slave server to match that of the master server or will this cause more problems? Really hoping someone can assist here and all help is appreciated. Machiel
