Percona vs. MySQL

From: Eric Wolf 
------------------------------------------------------
Does anyone have experience with Percona versus MySQL?

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).

Think Percona will help? Should I bite the bullet and try Postgres?

-Eric


-=--=---=----=----=---=--=-=--=---=----=---=--=-=-
Eric B. Wolf                           720-334-7734

=============================================================== From: Mike Harrison ------------------------------------------------------ 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..

=============================================================== From: Eric Wolf ------------------------------------------------------ Mike, Thanks for the list. I'll try them out. Using ENUMs, I've gotten the table down to < 3GB. I have 7GB+ RAM to work with. The table is a little too big to put in memory (but it's close). It's seems really hard to get MySQL to use more RAM other by specifying MEMORY for the engine. I may try InnoDB again with transactions turned off. But I might as well be doing MyISAM at that point. I'm a little annoyed that AWS has provisioned IO on the order of 1000-2000 IOPS. The SSDs I'm using are rated at 100,000+ IOPS. Paying extra still yields 100X less performance. Rackspace provides SSD block storage at 70 cents/GB/month. I wish I had gone that direction but the client went and bought an AWS reserved instance (without asking me first). -Eric -=--=---=----=----=---=--=-=--=---=----=---=--=-=- Eric B. Wolf 720-334-7734

=============================================================== From: Ed King ------------------------------------------------------ if time is money and you value your hair... ditch mysql and go to postgresql

=============================================================== From: Eric Wolf ------------------------------------------------------ Managed to get decent performance by RAIDing the two instance storage devices and using that for the database. I did some benchmarks and found RAIDed instance storage to be two orders of magnitude faster than EBS. Not as fast as the SSD-backed instances but fast enough. The database app is simple enough not to need too much tweaking. But I will push to change to Postgres ASAP. -Eric -=--=---=----=----=---=--=-=--=---=----=---=--=-=- Eric B. Wolf 720-334-7734