[Chugalug] Percona vs. MySQL
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
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:
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