Overloaded mySQL database

A

aldog

Guppy
Simple mySQL queries have been taking wayyy too long. A day ago I saw the mySQL server was handling 65 queries per second (average). Currently, its at 57 queries per second (average). 2 months ago the query would only take 200ms for me (I'm on the west coast so i believe that includes travel time). Now, if things are really really good, it'll be in the 500ms to 600ms range. That would still be fine for me if that were the norm, however, often times it ends up taking 12-15 seconds! And, about 10-15% of the time it just times out (20 seconds+), or it'll say I have an error "connecting to the database" or I get the message "is there a mysql server at this location" debug message.

The complete mySQL server stats I'm seeing (as of writing this) are:
Flush tables: 1
Open tables: 121
Opens: 268760
Queries per second avg: 57.008
Questions: 11032690
Slow queries: 3613
Threads: 4
Uptime: 2 days 5 hours 45min 30 sec

Could you guys consider setting up a #2 mySQL server?
 
More recently, I've seen it up to 582 queries per second. Theres tons of databases on the mySQL server now...
 
Im surprised its running those many queries per second, are you sure that is right? I'm checking this out with our linux admin

This a very powerful dual processor scsi based system. CPU usage is less than 10% at all times so you should be getting very good speeds. The server doesnt have too many databases on it like you believe.

Are you experiencing any slowness? This forum is loading without any delay and I checked a few other MySQL sites. Loading very fast. Execution time is not more than 5 or 6ms
 
411 mySQL databases by my count. I'd say thats a ton of databases, for a single db machine that sees regular activity.

I get slowdowns on my site when it runs queries all the time. However, on the forum I've never noticed a slowdown, although i hardly use it compared to development with my own apps. Looking at the query execution times, its apparent the slowdown is caused by the mySQL server, and not the application server itself.

Anyway, I think it probably has something to do with the location of the sites. Pinging one of my domains, hosted by jodohost, I always get 20ms higher pings than when I ping jodohost itself. Also, the tracert is drastically different. So, maybe i'm wrong, but I think the jodohost site gets a higher priority to the database than my own site. Maybe its a matter of bandwith running between the mySQL server and the server which my domain is hosted on. Or, maybe the jodohost page is allocated a higher priority than my own site. That I could understand, because its your business and its really important for you to be able to market and support your product. But, the thing is, it doesn't accurately let you monitor the performance of the database to clients. Is this only happening on win2? I thought you guys said awhile ago that you migrated your sql servers offsite from the application servers. Could it be that the server that runs the jodohost forums is within the same intranet as the mySQL server, but seperate from the client application servers(atleast win2)? I'm like a curious monkey.
 
Tracing route to www.jodohost.com [64.156.220.241]
over a maximum of 30 hops:

1 15 ms 1 ms 1 ms 192.168.0.128
2 18 ms 14 ms 12 ms 10.8.20.1
3 13 ms 11 ms 19 ms 68.6.10.114
4 14 ms 11 ms 11 ms fed1dsrc02-gex0703.sd.sd.cox.net [68.6.8.150]
5 11 ms 11 ms 11 ms fed1bbrc01-pos0203.rd.sd.cox.net [68.1.0.200]
6 15 ms 14 ms 12 ms so-2-0.hsa2.SanDiego1.Level3.net [63.214.191.45]
7 11 ms 19 ms 14 ms so-4-0-0.mp2.SanDiego1.Level3.net [4.68.112.137]
8 73 ms 74 ms 75 ms so-3-0-0.mp1.Miami1.Level3.net [64.159.0.1]
9 75 ms 75 ms 72 ms ge-10-0.hsa1.Miami1.Level3.net [64.159.1.90]
10 75 ms 75 ms 75 ms reverse241.acceleratebiz.com [64.156.220.241]

Trace complete.
--------------------------------------------------------------------------

Tracing route to mysql.jodoshared.com [64.156.223.231]
over a maximum of 30 hops:

1 12 ms 1 ms 1 ms 192.168.0.128
2 13 ms 18 ms 11 ms 10.8.20.1
3 12 ms 11 ms 11 ms 68.6.10.114
4 23 ms 11 ms 12 ms fed1dsrc01-gex0703.sd.sd.cox.net [68.6.8.146]
5 12 ms 19 ms 20 ms fed1bbrc01-pos0101.rd.sd.cox.net [68.1.0.204]
6 14 ms 11 ms 12 ms so-2-0.hsa2.SanDiego1.Level3.net [63.214.191.45]
7 14 ms 11 ms 11 ms so-4-0-0.mp2.SanDiego1.Level3.net [4.68.112.137]
8 82 ms 71 ms 75 ms so-3-0-0.mp1.Miami1.Level3.net [64.159.0.1]
9 76 ms 73 ms 71 ms ge-10-0.hsa1.Miami1.Level3.net [64.159.1.90]
10 74 ms 75 ms 75 ms 64.156.223.231

Trace complete.
--------------------------------------------------------------------------
Tracing route to elysian.jodoshared.com [66.36.229.91]
over a maximum of 30 hops:

1 11 ms 1 ms 1 ms 192.168.0.128
2 14 ms 20 ms 12 ms 10.8.20.1
3 14 ms 11 ms 11 ms 68.6.10.114
4 11 ms 11 ms 19 ms fed1dsrc01-gex0703.sd.sd.cox.net [68.6.8.146]
5 10 ms 11 ms 12 ms fed1bbrc01-pos0101.rd.sd.cox.net [68.1.0.204]
6 25 ms 23 ms 23 ms fed1bbrc01-pos0200.rd.sd.cox.net [68.1.0.193]
7 23 ms 23 ms 25 ms ge4-0-6.core1.pao1.hopone.net [66.36.224.221]
8 93 ms 91 ms 99 ms ge4-0-1.core1.iad1.hopone.net [66.36.224.13]
9 97 ms 91 ms 92 ms ge8-0.core1.dca2.hopone.net [66.36.224.17]
10 92 ms 92 ms 91 ms vl2.msfc1.distb1.dca2.hopone.net [66.36.224.228]
11 95 ms 96 ms 96 ms sls-ce8p10.dca2.superb.net [66.36.229.91]

Trace complete.
--------------------------------------------------------------------------

Looking above you can see that mysql.jodoshared.com and jodoshared.com are on the same server, or lan atleast. Whereas my site, elysian.jodoshared.com, is in a completely different place and 20ms ping higher. Maybe your servers at acceleratebiz dont communicate all that quickly with the ones you got at superb.net? If you want to see the difference, all you have to do is move support.jodohost.com to your client application servers. I gurantee this forum would slow to a crawl at times. Don't do that really though, I'm just kidding, cause then it'll take me lots longer to sort through things at the forum when Im here.
 
Ah, this is issue is not because of database load. MySQL and win2 are on different networks. We plan to migrate win2 to a server on the same network shortly
 
411 databases on mysql with unix is not much at all, you can easily run 1500 on the caliber machines we are running.
 
You must log in or register to reply here.
Back
Top