[Chugalug] Percona vs. MySQL

Mike Harrison cluon at geeklabs.com
Sun Nov 18 10:43:51 UTC 2012


> The application in question has one really big table with a bunch of ENUMs and one joined table (with a 4K text
> field). All reads, minimal writes. Queries on the table work great in MySQL (MyISAM engine) with the DB is on an SSD
> (on a the new bad-ass High I/O Quadruple Extra Large EC2 instances) but performance sucks when the DB is moved back
> to EBS (and gets worse with the production m1.large instance).

I don't have any experience in Percona, but I've got a bit of experience 
with tuning up MySQL and especially queries. That the performance 
seemingly takes a hit when the hard drive/io changes is a big clue.

1st: Check your my.cnf files, let it use some ram. Most default MySQL 
my.cnf files are for minimal uses on basic hardware.

2nd: Make sure you have indexes on the columns you are using for selects 
and joins.

3rd: Joins, especially complex ones or > 2 tables sucketh. I have had 
dramatic (10x+) speedups limiting a set of queries to 1 join each,
creating temporary tables (I love it when you can use 'memory' as a table 
type for these.. if they fit). and then joining it again to the next 
table. I've also seen slight changes in join syntax make big changes.

4th: Enums are way kewl, I remember playing with them.. but something 
stuck in my head said they caused more issues than they solved. No real 
info left, I slept since then. Might have been my strange way of looking 
at things. Others say Enums are faster: 
http://stackoverflow.com/questions/7879540/mysql-query-performance-dilema-enum-vs-tables

5th: MyISAM is fast, on current versions of MySQL, InnoDB is supposed to 
be almost as fast for simple reads, and my "seat of the pants" feeling
is that complex queries using InnoDB seem to be faster.

"SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" aka Dirty Reads
works on InnoDB tables (all tables involved must be InnoDB) makes a big 
difference if you are just reading static data.

6th: VM's suck for serious DB work.

7th: I was at an Oracle MySQL thingy a couple of weeks ago, MySQL 5.6 is
supposed to have some multi-threading (ala PostgreSQL) that is supposed
to make a big difference, especially in DB restores.

8th: I debug MySQL queries using 'show processlist' in a loop in a term,
while running the queries so I can see where they hang, and try to tune 
them up one line at a time. What hangs? "copying data" might be a bad 
query or IO bound..















More information about the Chugalug mailing list