Hi All,
I have been using Drupal for quite some time and recently we went live. The performance scalability has been some issue of late. I have been looking into monitoring my MySQL parameters for quite sometime. Currently I'm using mysql tuner perl script to monitor what variables need change.
Some observations that need help:
1> Total fragmented tables 60: The database size is quite big around 250 tables, but even then I am using "mysqlcheck -ao -auto-repair -u[username] -p[password] [database-name] with all my databases on my site to optimize and repair all my tables. Am I doing something wrong? Or do I need to optimize all my tables often? Currently I run the above command as cron for every 15 mins. How often should I repair and optimize all my tables?
2> Query cache: 66% - Is this efficient?? How to improve on this?? I already have a 32M query_cache_size
3> Temporary tables created on disk: This is somewhere I definitely need a help. Its a high 37% How to decrease the temporary tables on disk and have most of my temporary tables created in the memory?
4> Table cache hit: Not pretty good. 45%, I have set the table_cache=2048, Should I increase the value?
Any insights on any of the above would be a great help.
Thanks!!!
Comments
Enough memory
Hi, my first question is how much memory do you have?
If you are getting fragmented tables is that because you are are using MyISAM, instead of InnoDB engine? Are you using InnoDB because you don't have memory allocated.
Which temp tables are being written, what version of Drupal are you using. D5?
Kieran
Enough Memory
Hi,
Currently using D5. I have around 10gb of memory that can be used by my mysql. All my tables are MyISAM tables, although i'm sometime later planning to change my search tables engine to innodb [probably i will use google cse instead]. Temp tables are related to sorts, joins, etc i guess. I'm not pretty sure its a concern but something like 30% of temp tables getting stored in a disk looks too much.
Mukesh
Cheers,
Mukesh Agarwal
www.innoraft.com
temp tables on disk required for text/blob columns
Remember when considering your % of temporary tables on the disk that any such table with a text or blob column will always be written to disk (http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html). Many of the Drupal tables use such columns to store data, cache pages, etc.
I am not 100% certain of my own assertion here, but I have personally ignored higher numbers of temp tables on disk because I rationalized that they were probably tables with text or blob columns. I never did any testing to confirm this, but it makes sense to me so I didn't spend so much time on it myself.
Thanks
Thanks!! I guess its most of cache then. Also, I do have a lot of queries which use group by and distinct. May be I should stop paying heed to this for now.
Mukesh
Cheers,
Mukesh Agarwal
www.innoraft.com
1) Optimizing tables once a
1)
Optimizing tables once a day should be sufficient unless a particular table has a lot of deletes.
2)
What are the 66% you are referring to? 32M query_cache_size doesn't appear a lot. Look at the hit ratio and see how it changes when you increase the cache.
Some queries aren't easily cachable due to how Drupal works.
3)
I guess many of these tables are created by the search module. I suggest to create a temporary file partition in RAM and tell mysql to use this for storing such tables.
4)
I currently do not have access to an install using myisam, so can't compare.
Thanks
Thanks!! I guess a temporary file partition is a good idea. Will try to implement that
Mukesh
Cheers,
Mukesh Agarwal
www.innoraft.com
4 or 5 GB for InnoDB buffer pool
If you have 10GB RAM here, then I would try using InnoDB engine for most of the tables, if not all. Use 50% of RAM for innodb buffer pool.
This is in addition to the above mentioned suggestions.
I'm not sure if this is a
I'm not sure if this is a good idea. From what I have read about InnoDB and MyISAM, and related to my site's content, I would prefer MyISAM as it offers better speed for reads. Correct me if I'm wrong. Also, I have read a lot of articles on 2bits for the same.
Mukesh
Cheers,
Mukesh Agarwal
www.innoraft.com
With InnoDB, reads can be served from memory
...if you have enough RAM, and I guess 10GB is enough.
We run a 2 million page views a day site (not all is Drupal, though, yet) and we use InnoDB for everything. All reads are served from memory. We also have /tmp mounted in memory for temporary tables. Works great.
Edit: if you don't do it already, you would need a tool to monitor cpu, memory, diskio, etc. From there you could see how much memory is being used, etc. Then, I would suggest comparing MyISAM -vs- InnoDB in your case.
Query cache prunes per day
Small issue: Query cache prunes per day is around 90K. Any insights on this?
Mukesh
Cheers,
Mukesh Agarwal
www.innoraft.com
run experiment to build static home page
Hi Mukesh - tuning the DB is an important ingredient for any high-volume site, but it is an order of magnitude or more less effective at freeing up server CPU than building static cache for pages. Are you able to consider building static cache for any of your most content rich pages? If you haven't looked closely at the quick-and-dirty proxy technique I outlined below (or looked at boost.module to cache anonymous user pages, or Squid to implement more complex static caching), it might be well worth you time trying it as an experiment. I would try caching just your home page - you might be amazed at the impact that one change can make on server response across your entire site.
http://groups.drupal.org/node/18963
Memca
This doesn't directly pertain to your mysql stats, but from an experiential standpoint I've seen significant increases in speed for logged in users after implementing memcached for the internal (non page) caches within drupal. This makes intuitive sense for a couple reasons:
1) These application data caches (cache, cache_views, cache_content, cache_locale, etc) all need to be loaded for every logged-in pageview. Memcached responds more quickly than mysql, and doesn't force php to unserialize its response, speeding pageloads.
2) By not loading large volumes of data from mysql every time (e.g. the above can often be 1MB+ in total size), the database can focus on its own work, which makes it more efficient.
All that said, you might also want to look into your slow query log, etc, and figure out where the specific pain points are with regards to your database. Especially in MyISAM-land, one pokey query can lock up a central table like {node} or {users} for a few seconds, causing a cascade of slowdown allover the site. Going to InnoDB is good, as is examining your slow query log for optimization and cache candidates.
http://www.chapterthree.com | http://www.outlandishjosh.com
https://pantheon.io | http://www.chapterthree.com | https://www.outlandishjosh.com
I do use memcache
Hi,
I do use memcache (i guess none of the high content websites built in drupal can live without either memcache or boost). I looked into the slow query log as well. I do have a lot of organic groups (around 100000) which means for a user who has joined 100 groups or more, drupal runs a query at user_load hook to check for the subscriptions of any of these groups. This query is very hefty and at times takes a lot of time (i have seen it grow to more than 10 secs). I would love to see OG giving me an option to look for subscriptions on 1. everytime an authenticated user loads a page OR 2. every cron. I guess even drupal.org can increase its performance of every page load by that.
Mukesh
Cheers,
Mukesh Agarwal
www.innoraft.com
I think I see the problem.
I think I see the problem. Can you add an index on the uid column of og_uid table. Hopefully that speeds the query. Please post your results back here or in the OG issue queue as a bug.
This may go without saying,
This may go without saying, but have you turned off the Database watchdog and statistics modules? These cause writes to the database on every page view.
Also, it is a silly question, but have you turned on full page caching in Drupal in /admin/settings/performance?
Slantview Media http://www.slantviewmedia.com/ | Blog http://www.slantview.com/
I need watchdog
Thanks!! I need both watchdog and statistics, which means i actually want to optimize mysql such that these writes happen but the performance on the whole is not disturbed a lot.. I have moved to D6 and all caching is ON for the production site...
Mukesh
Cheers,
Mukesh Agarwal
www.innoraft.com
Query cache prunes
Hi Guys,
I have moved to Drupal 6 and was monitoring my performance again. So raising the same issue again. These are some of MySQL Stats for my site which has mysql running for more than 4 days and in initial stage, i.e. number of users are relatively low:
Join buffer size: 2M
Key buffer size: 64M
Key cache age threshold: 5 min
Max allowed packet: 1M
Max connections: 150
Max heap table size: 128M
Max binlog size: 1G
Max binlog cache size: 4G
Max Join size: 4G
MyISAM max sort file size: 2G
MyISAM sort buffer size: 64M
Query cache size: 64M
Query cache limit: 1M
Read buffer size: 1M
Sort buffer size: 64M
Tmp table size: 128M
Query Cache Stats (Its a little disappointing to have only 53% queries being cached : Is it an issue with drupal or is it my configuration?)
Query Cache Size:64M
Cache hits: 33037708
Cache Inserts: 27647101
Cache Lowmen Prunes: 18715848
MySQL Status Variables (please bear in mind that the site is in initial stage)
Aborted Clients: 4
Aborted Connections: 18
Binlog Cache Disk Usage: 0
Binlog Cache Usage: 0 (does this mean that i should set the binlog cache size variables to the minimum - the question whirls around the point that is the memory allotted to these variables reserved in RAM even if its not being used - as there are no transactions we dont need any binlog cache memory... right?)
Handler Read Rnd Next: 697 (I am using MyISAM which means their are a lot of table scans - can i somehow identify which of these queries or requests need this)
Key Blocks Not Flushed: 29
Key Reads: 29489909
Key Read Requests: 9288807892 (implies cache miss rate comes to around 0.0031 which is very cool, I guess)
Select Scan: 35 (Can I somehow identify what joins do full scan of the first table? If not I think we need to submit this as a feature request for next mysql version)
The issues that I am looking to take care via mysqltuner script are:
1. high value of query cache prunes per day - 4496872 (is increasing the query cache size the only way? or is it something i dont need to worry about)
2. joins performed without indexes - 1758847 (there are a lot of views which might be doing this - can i identify the queries? is slow query log the only way? I dont want to experiment by just increasing the join buffer size coz i wont be able to identify the problem in that case)
3. temporary tables created on disk - 45% (1M on disk / 4M total) (do i need to increase tmp tables size? is there any other way to troubleshoot? )
Any insight on any of the above would be very helpful. Thanks!!
Mukesh
Cheers,
Mukesh Agarwal
www.innoraft.com
total RAM
Btw, i have a total of 1GB RAM with me, which I need to distribute to MySQL and Apache. I guess its best to have 50% given to both.
Mukesh
Cheers,
Mukesh Agarwal
www.innoraft.com
Insert/Hit Ratio, and Low Mem Prunes
It's been a while since I've calculated the the Query Cache Insert/Hit Ratio but you numbers jumped out at me.
You've got a Hit/Insert ratio of 1.2 and it should be 10. A query cache of 64MB is really small? Are you using the default My.cnf which is designed for running MySQL on a Laptop? Your lowmemprunes is very high, which confirms your query cache is too high.
Are you running the slow query log with the no index flag on? You need to add indexes to help avoid slow queries and creation of temp tables. Once you have the necessary indices in place, then see if your tmp table creation is still high.
If someone else wants to double check these recommendations, that would be appreciated.
Cheers,
Kieran
Drupal community adventure guide, Acquia Inc.
Drupal events, Drupal.org redesign
Query cache
Old, but still indexed by Google ..
Re: query cache, you would want to increase the query cache limit. Cache table queries can easily contain more than 1M of data in a large site, and you want as much of the cache in memory as possible -- so a few suggestions:
Make sure all large queries are handled by memcache, then tune query cache limit for the 'smaller' intensive queries. Redirect memory savings to memcache.
Increase query cache limit after inspecting your slow log, then repeat. It is likely that you will see a few HUGE dumps in there. That is what you are trying find a place for or clean up. Probably variable, menu_router, og_ancestry, node_access.. and then your custom views.
Reduce larger query sizes by investigating the nature of the tables and the data. i.e., maybe something in the query itself will catch your eye -- "why is that node loading on this page?" or "why is this cache refreshing on every page load?" .. ;)
http://drupal.org/project/dbt
http://drupal.org/project/dbtuner
****Me and Drupal :)****
Clickbank IPN - Sell online or create a membership site with the largest affiliate network!
Review Critical - One of my sites