[nycbug-talk] Re: MySQL

[Bob Ippolito]

On Aug 21, 2005, at 7:43 AM, Francisco Reyes wrote:

> On Sat, 20 Aug 2005, Bob Ippolito wrote:
>
>
>> Uh yeah.  I've had more bad experiences this month with MySQL than  
>> anyone should ever have with a RDBMS.  These things are supposed  
>> to be robust and reliable, MySQL is neither.  Granted, we're doing  
>> ~150 queries/sec and have > 4GB data in there, but still.  Stay  
>> away if you can!
>>
>
>
> I have 20 to 60 queries per second and MySQL is using up 50% to 70%  
> of CPU. What optimizations have you done that you could recommend?
>
> I am just starting to read up on MySQL and looking into optimizations.
>
> Our queries are primarily  against a single table using index  
> access. We use mySQl primarily in conjunction with Postfix so the  
> queries are just lookups of email addresses.

Try adding a column with a MD5 hash of the email address, and an  
index that covers the hash instead.  Don't bother indexing the column  
also, and don't bother indexing both, because the hash has such high  
selectivity that another index would simply take up space.

-- the second part is only necessary if you care about MD5 collisions :)
SELECT * FROM addresses WHERE email_hash=MD5(email) AND email=email;

For whatever reason, this made a measurable difference for us.. but  
this was a TEXT column, VARCHAR is probably not so bad.  Other than  
that, our queries use indexed integers to find rows.

Also, normalizing things helps considerably.  Normalized data takes  
up less space, which means less I/O bottleneck.

Jeremy Zawodny's MySQL Optimization book, his blog, and the various  
presentations he's done are all pretty good.

However, make sure to backup regularly and don't trust replication  
unless you're going to babysit it.  Even if it doesn't report any  
errors in slave status, some of your tables might not be replicating!

Try not to get too tied down to MySQL, you might wanna switch too :)

-bob