[nycbug-talk] Re: MySQL
bob at redivi.com
Sun Aug 21 14:07:28 EDT 2005
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 :)
More information about the talk