… few people link to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what… have to filter down to for an index to be useful (that is, eliminate 70-80% of rows). It’s not quite correct to…, MySQL flipped from tablescan to index at about 34%. How am I supposed to find unused indexes then? You really have to run queries against your…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
… time ago about how to find unused indexes with single query. I was working on the system today and found hundreds of unused indexes on dozens of tables so just dropping indexes manually did not look fun. So I extended Vadim’s query to…. However it is very helpful for testing allowing to drop all potentially not needed indexes so you can perform proper QA and…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…STATUS output and how to use this info to improve MySQL Performance. To start with …unusable. To be honest I do not really like averages Innodb provides as it is hard to get average for interval you want to… investigations you might need to look at the log files to find truly conflicting statements….
Post: Estimating Undo Space needed for LVM Snapshot
June 9, 2008 By 5 Comments
… no free space left to be used as snapshot undo space, which means LVM is pretty much unusable for backups or required… simple, however how do you find that ? One simple though not very reliable way is to look at VMSTAT or IOSTAT. Find out number of… advice I would give on snapshot space estimation would be not to try to save too much on it. Hard drive space is…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
…to Information Schema but I still wanted to post little query I use for the purpose so I can easily find…how much does the index take compared to… or simply unused indexes among …To make things easier I’ve added INFORMATION_SCHEMA to the query so it works whatever database you have active. It does not…
Comment: Unused indexes by single query
September 14, 2008 By 14 Comments
…you shouldn’t simply drop the unused indexes. This patch, like any patch, should probably only…. A non-production server, no matter how much you try, will NOT have the same queries and data … leave it. If you want to drop an index, do some performance testing to find out how much benefit (disc space, …
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
… to get skewed to handle the write workload from replication, and not the read workload it will have to…In reality, it can be completely unusable for a long time. To measure how much this really matters, I did… a query against these three sets of stats to find the top tables on the active server and…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…to help you determine how indexes are used in more flexible ways. The default report just prints out ALTER statements for removing unused… over the SSH tunnel to query EXPLAIN and find out the index usage on… comment_post_id = ? and comment_type not regexp ? and comment_approved = ? sample: SELECT …
Post: Economics of Performance Optimization
March 13, 2008 By 9 Comments
…have to decide on couple few questions on how to tackle …not be available and keeping unused spare box can be expensive. Because fewer users use these boxes you also likely to…find them in the situation when they have hard time allocating resources to get application optimized properly – they need to…
Post: Give me 8 hours, and I'll help you build a better application
August 29, 2009 By 4 Comments
… wants to know how to deal with an application that is growing. The simplest way is to prevent… server really needs to keep in memory to function well. That’s because unused rows can bloat…not be telling you when the service is down. Be the first to know, and have enough information to help you find…
