[nycbug-talk] Re: MySQL

[Hans Zaunere]

talk-bounces at lists.nycbug.org wrote on Sunday, August 21, 2005 3:01 PM:
> On Aug 21, 2005, at 8:55 AM, Francisco Reyes wrote:
> 
> > On Sun, 21 Aug 2005, Bob Ippolito wrote:
> > 
> > 
> > > Try adding a column with a MD5 hash of the email address, and an
> > > index that covers the hash instead.
> > > 
> > 
> > The queries are coming from Postfix, not sure we can change how
> > postfix does it queries. 

An index is an index - it doesn't matter if it's across an MD5 or an email
address.

> > > Also, normalizing things helps considerably.  Normalized data
> > > takes up less space, which means less I/O bottleneck.
> > > 
> > 
> > Again, our use is primarily with Postfix. Nothing fancy. We do use
> > it for some apps, but the utilization problems are with the Postfix
> > usage.

Try turning on query caching.

> It might be trying to do too much concurrency, which would explain
> this kinda problem.  See if postfix has any facility to do MySQL
> connection pooling, and take a look at how many connections you have
> with mytop. 

Postfix generally generates a lot of the same queries - many small fast
queries.  Try enabling query cache.  For simple SELECT statements, you
should have no problem handling thousands of concurrent connections.

But before any of that, get the right binary.  The number one problem with
MySQL on FreeBSD is that people use the wrong binary.

H