Other Software > Developer's Corner

Need Advice on MySQL Server Tuning

(1/4) > >>

Stoic Joker:
Greetings
  A few years back I setup a MySQL server back-end for our company with an Intranet website so everybody could have free access to the same correct up-to-date information. This resolved issues we had been having with people squirreling away copies of stuff and then arguing over who's info was the most(est) current/correct. *Sigh*...

  So... This was good, for awhile. But then another db was added for something else, and another, and another... Now 99% of the time all is still just fine, but there are the odd occasions where large (All of the X, who got X, in the past X years...) reports need to be compiled ... And some of these are taking up-wards of 3-5 minutes to complete.

  Initially, I setup the server config to use resources sparingly; used just the basic many things happening on box (MySQL Instance Configuration Wizard) configuration. But now I'm faced with having to re-think that configuration to speed-up report generation...And the settings available on the advanced (MySQL Administrator) options are considerably more complex...

  Mind you I have considered the possibility that I may just be creating a larger problem instead of solving one ... So thought it best to ask if there were any (succinct) best practice tutorials available for tweaking this puppy up a notch so that the larger report queries don't take quite so close to forever to run...

Thank you,
Stoic Joker

Renegade:
For a production database, back it all up to a testing server first, obviously.

I'd put indexes on everything that seemed logical first. That should speed things up by a good amount.

Next, denormalize. It sucks ass, but it's a good performance tuner on large databases. If it is already denormalized, shoot the fucknut that designed the database as he's too stupid to have children and further pollute the genepool.

Indexing though is the best way that I've seen for "junkie" style performance improvements.

Next, or before denormalize, tweak SQL into multiple statements. JOINs are killer. 2 SELECTs should way outperform a JOIN or 2.

Tweak inside those SELECTs to find the performance killing JOINs. Capitalize there.

It's a LOOOOONNNNNGGGG process, but it works for a lot of things.

I'd be interested to find out what you do and how things work out for you.

Renegade:
Oh, and make sure to use a tool that measures SQL speeds for you. That's crucially important.

Renegade:
That's kind of stupid to say as it is obvious... But I think it will benefit some people other than you that may read later on.

housetier:
You could turn on slow query logging. Then EXPLAIN those slow queries to see if there are missing indices. If they are missing, add them when there is little or no load on the DB.

Then it might help to set up replication, so the reporting tools can select and aggregate whatever they want, without imposing a burden on the so-called live system. However, now you have an even more complex system to maintain...

Navigation

[0] Message Index

[#] Next page