SQL - where's my bottleneck?

B

Brian_B

2[H]4U
Joined
Mar 23, 2012
Messages
3,354
I've been running a mysql server for a web back end for a while now. It's been running fine for years actually, but I figured I should stop being lazy and actually try to optimize it somewhat. This is a small user count development platform - the software is for modeling industrial data - nothing really to do with programming, other than we are using php/mysql/javascript as a tool to compliment a lot of other tools.

This is an older machine, running older hardware and software - some of it could just be that. Core i7 920, 6GB RAM, SQL tables on an older SSD, RAID10 for /home accounts. I think it's on Fedora 10 (pretty dated, but it's difficult to migrate at the moment)

Querys often take minutes to perform. Some of the tables are long, but not terribly wide - the largest are several GB where we store historical data. If more than one person is using the web page, the database just slows to a crawl and you may as well get lunch while you wait on results. Tables are a mix of InnoDB and MyISAM - both run slow, I can't tell if one is appreciably better than the other from a performance standpoint.

Here are some top outputs when a query is running. I can't quite figure out what my bottleneck is. mysqld shows a high CPU%, but at the same time CPU shows high idle percentage (if I'm understanding that correctly). When mysqld goes idle, the load average drops to 0.25 or less. There isn't much free memory, but swap isn't really getting touched, so I'm assuming most of that memory load is caching, and wait average is very low.

Apart from this development stuff, the server also runs a very small amount of email.

Is this just table/query optimization issue? Indexing issue? Is it a mysqld parameter optimzation issue? User error? Old hardware? Insufficient hardware?

I know just enough about databases to make them work for me, but not nearly enough to make them work well.


Top.jpeg


Code: 
top - 12:24:46 up 26 days,  2:02,  6 users,  load average: 1.52, 1.53, 1.23

Tasks: 237 total,   1 running, 236 sleeping,   0 stopped,   0 zombie

Cpu(s): 15.2%us,  0.4%sy,  0.0%ni, 84.4%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st

Mem:   6121748k total,  6077116k used,    44632k free,    19664k buffers

Swap: 23993068k total,      124k used, 23992944k free,  4834632k cached


  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND           

10053 mysql     20   0  587m 227m 5608 S 127.5  3.8  14012:06 mysqld            

28685 brian     20   0 15012 1312  872 S  0.7  0.0  97:39.39 top
 
When was it last reindexed? I would try there and see what that happens.

From there I would check the keys are appropriate and ordered from most used to least used.. left to right.

Both of those should see some budge on numbers.. the former more than the latter.

The queries would be another area to eyeball. Shit queries (lots of 'INs', bad joins, no WHEREs, etc) are hellish.

Is it kept up on versions?

I woud try this and see what it tells you.
https://github.com/major/MySQLTuner-perl
 
Good advice - thanks all. Will start digging through these suggestions and see what moves the needle.
 
For slow SQL-based applications, I find a reindex helps a lot...could see improvement of reporting performance in a matter of minutes depending on how large of a data set.
 
I was also thinking that if table sizes have grown too much it could be that the query plans have bad or stale query plans. Index rebuilds should also rebuild the plans not sure if that’s so on MySQL.
 
You must log in or register to reply here.
Back
Top