[Chugalug] Percona vs. MySQL

Eric Wolf ebwolf at gmail.com
Sun Nov 18 16:47:47 UTC 2012


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





On Sun, Nov 18, 2012 at 3:43 AM, Mike Harrison <cluon at geeklabs.com> wrote:

> 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<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..
>
>
>
>
>
>
>
>
>
>
>
>
>
> ______________________________**_________________
> Chugalug mailing list
> Chugalug at chugalug.org
> http://chugalug.org/cgi-bin/**mailman/listinfo/chugalug<http://chugalug.org/cgi-bin/mailman/listinfo/chugalug>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://chugalug.org/pipermail/chugalug/attachments/20121118/65432111/attachment.html>


More information about the Chugalug mailing list