[Cialug] MySQL Optimization

[Tony Bibbs]

Thanks, Dave.  Yeah, did some of the optimization stuff suggested on 
mysql.com.  Unfortunately they don't define every tidbit of information 
I see in the .ini and when it does it assumes a certain degree of 
knowledge as the to the inner-workings of MySQL...something a true DBA 
would know but a software guy like me might not.

Again, when/if this gets resolved I'll report back as I think it makes 
for an interesting discussion since it is a real world problem.

--Tony

David Champion wrote:
> 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
>>
> 
> 
> _______________________________________________
> Cialug mailing list
> Cialug at cialug.org
> http://cialug.org/mailman/listinfo/cialug