I've been tweaking our MySQL configuration, and I'm down to addressing query cache prunes. In my research there seems to conflicting recommendations about how much memory to allocate to query_cache_size, and reducing prunes. Some threads say that one shouldn't allocate too much memory to query_cache_size, it can have negative impact, and others say keep giving it memory until the number of prunes stops going down. I reduced query_cache_size = 128M to 96M, we were getting about 1.5 million prunes a day. Now we are getting about 3 million prunes a day. My inclination is to up the query_cache_size to say 256M, but I keep reading that there may be some other underlying problems. Here are my mysqltuner.pl results:
[OK] Maximum possible memory usage: 18.2G (77% of installed RAM)
[OK] Slow queries: 0% (0/37M)
[OK] Highest usage of available connections: 6% (11/175)
[OK] Key buffer size / total MyISAM indexes: 8.0M/70.0K
[OK] Key buffer hit rate: 99.6% (20M cached / 90K reads)
[OK] Query cache efficiency: 90.4% (32M cached / 35M selects)
[!!] Query cache prunes per day: 3134501
[OK] Sorts requiring temporary tables: 0% (31 temp sorts / 347K sorts)
[!!] Temporary tables created on disk: 36% (172K on disk / 471K total)
[OK] Thread cache hit rate: 99% (11 created / 135K connections)
[OK] Table cache hit rate: 98% (485 open / 491 opened)
[OK] Open file limit used: 1% (28/1K)
[OK] Table locks acquired immediately: 100% (6M immediate / 6M locks)
[OK] InnoDB data size / buffer pool: 723.7M/16.1G
My understanding about the temp tables being so high is that Drupal cache tables with blog or text fields will always be written to disk. We will be installing memcache, and I assume that should address that issue. What I'm lost about is, exactly what is the relationship between query_cache_size and prunes? Should I just keep giving it memory and see what happens, or is there other things that could be causing this?
Thank you.
Comments
I would install memcache
I would install memcache first because everything will change after you do that. But with 3M prunes per day you are probably better off turning query cache off.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Graduate to the next two mysql tuning tools
Sounds like you are ready to run the next, better, tools. I posted on this some time ago, early last year, I think. In GDO/la most likely. Now use:
MYSQL PERFORMANCE TUNING PRIMER - By: Matthew Montgomery
Use the above tool in conjunction with the tuning script you have now. Yes, sometimes their recommendations will conflict.
And then the much more complex tool, where you need to understand all the relationships you have implied are there, the tool is:
mysqlreport
My post last year, or so, had the actual download URLs. Or google to find them.
BTW, at 3M pruned (is that per day, or since you last ran the tool, many weeks ago?), I have to wonder if your site is primary authenticated users, using the session table for unique SELECTs, or just very, very high traffic. Do consider looking into the types of prunes, and see if any custom code might remove the session table, and still work.
Peter
LA's Open Source User Group Advocate - Volunteer at DrupalCamp LA and SCALE
Thank you for the replies.
Thank you for the replies. Everything helps.
The server has been running for about 37 hours since I changed the parameters, and the prunes per day have dropped to about 2.6 Million. Upping the query_cache_size I think will help, since with 32M more ram, it was at 1.5 Million. The site receives around 10K visits/day, and we have not implemented caching in the form of varnish. Our only caching is the built in Drupal cache at this point, which is why our disk writes are so high, drupal cache tables have blobs/text fields, forcing them to be written to disk. We will be getting memcache installed very soon, so that should take care of that.
My goal is to optimize as best I can, and learn a thing or two in the process, before enabling memcache and varnish. I don't want to mask a bad implementation with caching. These servers will be getting more sites in the future, and I would like to have them running at peak efficiency.
Thank you,
Sean
I would increase the query
I would increase the query cache size back to at least 128M, possibly 164M but not much larger. It is OK to have some query cache prunes as MySQL users an LRU algorithm for that. If you have a large number of authenticated users, you may reduce the number of prunes by implementing sessions in memcache.
I would be more worried about the temporary tables that are being created on disk (almost 0.5% of your queries require writing the results to disk!). You likely have either a text/blob column or your result sets are very large in a query which uses ORDER BY, DISTINCT or GROUP BY, requiring that the temporary table be written to disk.
Slow query log will probably be a good place to start looking for further MySQL performance improvements.
I've spent time examining one
I've spent time examining one of the DBs, and there are many cck fields with no specified maximum length, which means they default to longtext. Where possible, I've set these to 255 or less to have them converted to varchars. With MySQL 5.0.3, varchar in utf8 can be a combined 21844 give or take a little. There are cck fields we have that have max of anywhere from 2000 to 10000. Setting that leaves them as longtext. I could programmatically convert them to varchar(4096) for instance, and was wondering if this seems like a good or bad idea.
I've written a class that examines every field of every table to test for blobs and text fields. I examine the length, and the largest string in each field. There are many fields that fall well within the 21844 varchar limit, seemingly safely within, and why wouldn't I just convert those?
It would seem to me that this would go a long way to dealing with my table cache issues.
Thank you,
Sean
Similar module
http://drupal.org/project/dbtuner
Haven't worked on it for some time but it does similar things
Thank you, I had checked it
Thank you, I had checked it out, but I know more now than a week ago, so I will peruse again. Also, is it safe to assume that I've set my query_cache too high when it starts to fragment? I'm at about 23% fragmentation, which is new.
Sean
tmpfs
I have a ton of complex database actions on my site and was seeing significant use of temp tables. I moved to a memory-backed tmpfs partition, and then I point mysql to that partition for its temporary tables. This essentially works around the write-to-disk issue since it keeps everything in memory. Google for "mysql tmpfs" and you should find tutorials if you need them. Helped me quite a bit, and it's a lot quicker than trying to manually fix all of your queries.