[Cialug] MySQL Optimization

[David Champion]

Usually what I've seen with mysql bottlenecks comes down to what Nate 
says - improper indexes and poorly written queries.

I usually make sure I have indexes on any fields used in join 
conditions, where conditions, and order by or group by expressions. But 
as he says, too many indexes can slow things down. Mysql has an 
"explain" function to show what indexes are being used by a query.

Check to see it they're using explicit joins with "LEFT OUTER JOIN...", 
or implicit joins in the WHERE. My experience has been that implicit 
joins don't optimize as well. I've also seen cases of putting filter 
conditions in the join will cause unpredictable results - make sure to 
just do join conditions in the join, and filter conditions in the where.

To see where your bottlenecks are happening, I've used "mysqladmin -u 
<user> --password=<password> processlist". Run that several times and 
see if you can narrow it down to certain queries that are executing slowly.

Mysql will also write results to a temp table if it needs to. This can 
make a query that should execute in a second slow down to 10 or 20 
seconds because it's waiting for the IO. You might be able to make it do 
this less by optimizing the query or increasing the resources available 
in the .ini.

Search around in the documentation on mysql.com for optimization tips - 
you might see some info there that will help you out.

-dc

Tony Bibbs wrote:
> Dave has already recommended pgsql off list...like a million times ;-)
> 
> Nate, I'll give a Dave a crack at it since it's hosted in his rack...if 
> we're both stumped then I may pull you in as a fresh set of eyes.
> 
> It's a site running Geeklog, http://www.geeklog.net.  Yes, quite a few 
> indexes, mostly small numbers of joines (2 or 3 tables).  The problems 
> are specifically with one plugin (forum)...which isn't code I wrote.
> 
> --Tony
> 
> Nathan C. Smith wrote:
> 
>> Tell us more about how it is slow.  What kind of application is it?
>>
>> Are the queries big (do you have a sample) do you have a ton of 
>> indexes on
>> your tables (they can slow down inserts) do you have any indexes on the
>> tables?  How many tables are you using in a query?  A lot of times 
>> rewriting
>> a query alone can make the DB faster.
>>
>> Are you accessing it with PHP - through common libraries?
>>
>> Dave- I'm surprised you didn't just say fix it with PGSQL.
>>
>> -Nate
>>
>> -----Original Message-----
>> From: Tony Bibbs [mailto:tony at tonybibbs.com] Sent: Tuesday, January 
>> 10, 2006 9:32 AM
>> To: Central Iowa Linux Users Group
>> Subject: [Cialug] MySQL Optimization
>>
>>
>> Ok, I'm not a complete n00b to MySQL but despite my best efforts I'm 
>> in dire need of optimizing an installation I have.  I've got a site 
>> that is running terribly slow despite having a dedicated database 
>> server.  The load on the database server loves to stay at 2 or better.
>>
>> I'm looking for someone with experience optimizing these bad-boys 
>> who'd, preferrably, be willing to work for beer (bourbon and whisky 
>> available, too).  Of course...I was drinking the Turkey when I made my 
>> last stab at optimizations which may explain the current state ;-)
>>
>> Anyway, if any of you have experience and would be willing to help 
>> please email me off list.  Oh, and for the benefit of the 
>> group...maybe when we get this all figured out we'll have enough for a 
>> presentation at one of the upcoming meetings.
>>
>> --Tony
>> _______________________________________________
>> Cialug mailing list
>> Cialug at cialug.org
>> http://cialug.org/mailman/listinfo/cialug
>> _______________________________________________
>> Cialug mailing list
>> Cialug at cialug.org
>> http://cialug.org/mailman/listinfo/cialug
> 
> _______________________________________________
> Cialug mailing list
> Cialug at cialug.org
> http://cialug.org/mailman/listinfo/cialug
>