I have a large database running on a machine with 4GB of RAM, so I thought I’d take advantage of this fact, and allocate 1.4GB to the key file and 1GB to the query cache.
For some reason, when I try to set the query_cache_size in my.cnf or use a SET GLOBAL query_cache_size = 1073741824, the value reported by SHOW VARIABLES like ‘%cache%’; goes to 0.
Is there an upper limit determined by MySQL somewhere? I’ve even tried upping the limit gradually, from it’s current setting of 104857600 (100MB) by a few bytes and I get the same result.
Anyone else experienced this or know how this can be circumvented?
The reason for you are getting 0 when you are trying to set the value is very strange.
Are you using MyISAM tables?
Then you generally shouldn’t use more than 25% of the RAM locked up in cache (key and query) the rest should be free to be used for the operating system file cache.
And also do you actually have so many identical queries with so large result sets that you can utilize a 1GB query cache?
But as always it depends on your applications query pattern.
I am using MyIsam tables as I’m using FULLTEXT indices, and I do have a large number of regularly run queries which return 2-300,000 rows.
I was allowing around 1GB for the operating system, as this should be fine on a freeBSD platform, and thought why not allocate the rest.
I’ll try playing around with it some more over the weekend when it’s not in use so much. Can’t play too much during the week for fear of disrupting the service.
OK then it sounds like you are starting to have a good reason for using a pretty large query cache.
But how often do you actually run the exact same queries that that return 200-300,000 rows.
The reason I’m opposing your thoughts about 1GB query cache is that the main usage and what it is designed for are smaller queries that are queried a lot of times.
Because on these queries the overhead for parsing the query and deciding on execution plan etc takes a lot of the total query time.
When the amount of data increases you have a lot of other bottlenecks that are more protruding.
But you should remember that unless you can fit most of your queries into the query_cache you will pay a pretty high price for not leaving enough memory for the OS file cache.
That is why the recommendation is to only let MySQL use 1/4 of the available RAM.
But as always play around with the settings and try them out on your application.