My master database is where all of my inserts/updates go, along with a small subset of my selects. Two slave servers handle the bulk of the selects.
I have a query that joins 4 tables. On my master database, the query planner picks a poor index for the query, and the query takes about 15 minutes to run. On the slaves, a better index is picked, and the query runs in about 2 seconds.
Running analyze table on the master did not change the behavior, even with innodb_stats_sample_pages turned up to a high value (512). However, I did find a fix, which was to go onto the master, and do some selects which read in the whole table. These sorts of full table scans never happen on the master under ordinary circumstances. After that, the query optimizer started to pick the right indexes!
What I’m wondering is why that would be? What all goes into the heuristic used by the query planner to pick the best index for a select? Just the cardinality of each index, or is there more to it, such as how often each index is used? Is there anything other than ANALYZE TABLE that I can do to keep the statistics in good shape in my setup?
I’m running Percona Server 5.5.27-rel28.1-296 for Ubuntu on EC2. I’ve noticed that when I build a test server using an EBS snapshot of my master database, this problem continues to manifest itself. Are table statistics persisted to disk in Percona Server 5.5?
Director of Systems Engineering, Get Satisfaction.