Need Advice on MySQL Server Tuning

[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...

[Stoic Joker]

@Renegade - Just for the record, I designed and wrote the entire system...From the db, to the UI ... So the shooting option is out...

But to be fair:
I seldom use joins.
all the critical stuff is indexed.
and I haven't a clue what denormalizing is/does. ...And I suspect it's rather unlikely that I've already done it by accident.

------------------

Anyhow, here's the thing... While I could, with agonizing care, try restructuring the report queries. I'm not entirely convinced that that will truly give me net effect I'm looking. I say this because of the following reasons:

• as I mentioned before 99% of the time everything runs just fine
• Sometimes a large query really is just that: 5,000+ clients, Several thousand items (seasoned with pricing/quantity info that gets tallied & totaled on multiple levels), data set is for the last many years... (Yada yada)
• It is only the large reports (that should be CPU hogging, yet are not) that are taking time to run
• The server the queries are run against never shows more that 20% (per core) usage during the query.
• There are no hardware based reasons (tons of memory available, no disk contention, etc.) for it to take that long.
• I know that I used the conservative resource settings back when I originally deployed the rig.

So... I'm looking for more of a "Let Loose the Reins!" type of solution so the MySQL server service can have have a better crack at the CPU's "time". I just don't want to over do it because there are a few other things the box does/needs to be handling also.

[patthecat]

You have to determine where most of the report work is being done... the client PC or the server.  Maybe put the SQL as a stored procedure on the server and have the client PC call that rather than doing the SQL reporting stuff on the client PC.

[Stoic Joker]

You have to determine where most of the report work is being done... the client PC or the server.  Maybe put the SQL as a stored procedure on the server and have the client PC call that rather than doing the SQL reporting stuff on the client PC.

-patthecat (February 02, 2011, 12:15 PM)

The only thing done client side is Click OK and view. Client connects with a TCP/IP session, spits in query, gets results, and displays. Queries are all structured so that the returned results set can be dropped straight into a ListView control for application use (or are stuffed into a table via either .asp or .php). The only "processing" done client side is input validation ... Which takes about 4ms, and is done prior to query being sent (No used borked queries aloud...).

[Renegade]

@Renegade - Just for the record, I designed and wrote the entire system...From the db, to the UI ... So the shooting option is out...

But to be fair:
I seldom use joins.
all the critical stuff is indexed.
and I haven't a clue what denormalizing is/does. ...And I suspect it's rather unlikely that I've already done it by accident.

-Stoic Joker (February 02, 2011, 10:45 AM)

"Not normalized" is my #1 database pet peeve that sets me off into a irrational, frothy-mouthed, insane frenzy.

(I just had another episode last night dealing with WordPress and GUIDs in its database...)

"Denormalizing" is a way of tuning a database to perform better under specific business logic restrictions that effectively pollute the absolute integrity of the data, but not enough to make it useless -- only enough to sacrifice integrity that your business logic will never require. e.g. You should always have family names in their own field, but if you will never ever use only part of a full name, then you can collapse all name fields into 1 field. Now you can never know the family name, but you can know the full name faster and easier.

For indexing in MySQL, you might be surprised at just how much extra performance you can get by indexing fields that you would not normally index. I was working on a largish database and started experimenting with performance there -- additional indexing helped a fair bit.

Anyhow, here's the thing... While I could, with agonizing care, try restructuring the report queries. I'm not entirely convinced that that will truly give me net effect I'm looking. I say this because of the following reasons:

• as I mentioned before 99% of the time everything runs just fine
• Sometimes a large query really is just that: 5,000+ clients, Several thousand items (seasoned with pricing/quantity info that gets tallied & totaled on multiple levels), data set is for the last many years... (Yada yada)
• It is only the large reports (that should be CPU hogging, yet are not) that are taking time to run
• The server the queries are run against never shows more that 20% (per core) usage during the query.
• There are no hardware based reasons (tons of memory available, no disk contention, etc.) for it to take that long.
• I know that I used the conservative resource settings back when I originally deployed the rig.

So... I'm looking for more of a "Let Loose the Reins!" type of solution so the MySQL server service can have have a better crack at the CPU's "time". I just don't want to over do it because there are a few other things the box does/needs to be handling also.

-Stoic Joker (February 02, 2011, 10:45 AM)

Sounds like a tough nut to crack.

Is it just that the query itself takes a long time? With no resource issues... Bottleneck in there?

Have you tried a query profiler?

[Stoic Joker]

For indexing in MySQL, you might be surprised at just how much extra performance you can get by indexing fields that you would not normally index. I was working on a largish database and started experimenting with performance there -- additional indexing helped a fair bit.

-Renegade (February 02, 2011, 07:49 PM)

Actually no, I manage to figure that one out a few years back ... Now I index everything, as it really does make a big difference.

Sounds like a tough nut to crack.

Is it just that the query itself takes a long time? With no resource issues... Bottleneck in there?

Have you tried a query profiler?

-Renegade (February 02, 2011, 07:49 PM)

Given the scale of what it's being asked to do, sure... It's bound to take a while. It does not however (IMO) need to take that long...

Here's the thing... I run one of the problem child queries that I selected for testing purposes while watching Task Manager to see what impact it has on the server's resource usage. While the query is running the server is just chuffing along showing absolutely no sign of a strain.

I know I originally set the thing up conservatively (Instance Configuration Wizard using Developer Machine settings), because I hadn't decided if I could trust it back then. But... Now I gotta give it a bit more freedom to gobble resources because it occasionally needs them. ...Badly...

And that's where the "fun" starts, because there are a ton of different memory caches/buffers/settings which I really need adjust...but  not screw-up.

[Renegade]

I forget where, but there's a guy that does MySQL tuning pretty much exclusively. He's got a blog called something like "mysql performance" or optimization or something. Damned if I can find it now... If I remember, it was orange? Orange and black? Very good stuff and really low level too.

You can also try here:

http://www.mysqlperformanceblog.com/

They might have some tips.



Maybe it was this one:

http://mysqlha.blogspot.com/

Here's a list of some I found:

    * http://blogs.sun.com/realneel/
    * http://developer.cybozu.co.jp/kazuho/
    * http://www.chriscalender.com/
    * http://www.facebook.....php?id=102841356695
    * http://mysqlha.blogspot.com/
    * http://torum.net/
    * http://yoshinorimatsunobu.blogspot.com/
    * http://explainextended.com/
    * http://mtocker.livejournal.com/
    * http://blogs.sun.com/LinuxJedi/
    * http://rpbouman.blogspot.com/
    * http://jan.kneschke.de/
    * http://www.xarg.org/
    * http://rackerhacker.com/
    * http://ronaldbradford.com/
    * http://code.openark.org/blog/
    * http://datacharmer.blogspot.com
    * http://sql-error.mic...al.com/en/index.html
    * http://kristiannielsen.livejournal.com/


Sorry I can't be of much help.

[f0dder]

Here's the thing... I run one of the problem child queries that I selected for testing purposes while watching Task Manager to see what impact it has on the server's resource usage. While the query is running the server is just chuffing along showing absolutely no sign of a strain.

-Stoic Joker (February 03, 2011, 06:48 AM)

Does "Task Manager" imply MySQL is running on a Windows server?

If that's the case, how old is the MySQL version?

Iirc, there was recently something about a new version of MySQL that fixed some Windows performance issues - I was shocked to read the announcement, since it revealed that the Win version had essentially used a "BigLock" and only in 2010 been fixed to use more fine-grained locking. In other words, for years and years and years, whoever were responsible for the Windows port should really have been shot, or at least have had a great big flogging.

Don't have a link to the article (wouldn't be surprised if it was on osdev, or perhaps slashdot), but here's a graph showing the consequences of the 5.5 release.

[Stoic Joker]

Here's the thing... I run one of the problem child queries that I selected for testing purposes while watching Task Manager to see what impact it has on the server's resource usage. While the query is running the server is just chuffing along showing absolutely no sign of a strain.

-Stoic Joker (February 03, 2011, 06:48 AM)

Does "Task Manager" imply MySQL is running on a Windows server?

-f0dder (February 03, 2011, 09:42 AM)

Yes.

If that's the case, how old is the MySQL version?

-f0dder (February 03, 2011, 09:42 AM)

v5.1

Iirc, there was recently something about a new version of MySQL that fixed some Windows performance issues - I was shocked to read the announcement, since it revealed that the Win version had essentially used a "BigLock" and only in 2010 been fixed to use more fine-grained locking. In other words, for years and years and years, whoever were responsible for the Windows port should really have been shot, or at least have had a great big flogging.

Don't have a link to the article (wouldn't be surprised if it was on osdev, or perhaps slashdot), but here's a graph showing the consequences of the 5.5 release.

-f0dder (February 03, 2011, 09:42 AM)

Holy Shit!

The charts shown are only for the transactional InnoDB (These are basic MyISAM stuff), but either way its got to be an option worth exploring. Maybe I'll give that a shot this weekend.

[Gothi[c]]

Besides what was already mentioned...

* If EXPLAIN shows it's using filesort, you might want to increase your sort buffer sizes.
* If you have some queries that repeat alot, enable query cacheing... it will turn those queries into lightning

[Stoic Joker]

Besides what was already mentioned...

* If EXPLAIN shows it's using filesort, you might want to increase your sort buffer sizes.
* If you have some queries that repeat alot, enable query cacheing... it will turn those queries into lightning

-Gothi[c] (February 03, 2011, 02:41 PM)

Now that is the type of answer I was looking for.

Query caching probably won't net much as there are only about 20 (sporatic use) users... But the sort buffer strikes me as the sort of thing that could have an impact on a large report query. Are there any rules of thumb (keep in multiples of X kind of stuff) for buffer sizes?

[Gothi[c]]

I would say that the major rule of thumb is to try to not allocate more buffer space than your machine has memory, though that can be hard to predict.