monitoring db health, finding problems and fixing them?

[schilly]

Our db is running into max connections every now and then and we're trying to figure out why.

 

I've been running the mysql administrator but don't totally understand what all the info means.

 

Can someone point in the right direction of some good resources to read up on?

 

Also some suggestions on what to do to figure out exactly what the problem is.

 

Current max connections is 150.

 

I'm not sure if it's better to increase the max connections or the different buffers.

 

Thanks.

 

 

[schilly]

Anyone?

 

Connection usage doesn't look to be more than 10%.

 

Traffic Ave: 110000

 

Number of SQL Queries: Ave: 19, Max: 166

 

Query Cache Hitrate: Ave. 54%

 

Key Buffer usually sits around 84M of 115M and average hitrate is 77%

 

 

Query Cache Hitrate seems low. Should I increase the cache?

[schilly]

lot of views. anyone?

[dreamwest]

I recently had the same problem, it was a combination of google hitting bad queries i had made ages ago like 150,000 times a day.

 

I removed order by rand() with the altenate FLOOR(), indexed all query joins , removed live count(*)  and just had totals coming from an updated table.

 

First place to look is bots hitting your database driven pages through awstats

[schilly]

thanks dreamwest.

 

so you didn't tweak any of the database config?

 

I'm guessing we probably have some old queries lying around but most of the frontend code has been revamped in the last 6 months. I have looked at the slow-queries log but most of from backend statistics queries that don't get run very often.

 

[Mchl]

Do you by any chance use persistent connections in your application?

[schilly]

Do you by any chance use persistent connections in your application?

 

nope.