MySQL connection overload - what is causing it?

[Generalissimo]

A few sites on my server rely on each page request connecting to MySQL. I know it's not perfect but for things like vbulletin there is no alternative.

Recently there were lots of too many connection issues, so I increased the number of simulatenous connections from 500 to 1500 (which is way to much imho). There is no way legitimate traffic is causing anywhere like this amount. It must be robots or scripts going wrong.

How can I find out which it is?

[Blue Cat Buxton]

Presumably they are not vbulletin problems?

It sounds like a trial and error process, how long has it been at this level, and what have you changed in the same timeframe that may have caused the problem etc etc?

[Chris]

check your log files for repeated connections by the same IPs.

You may have to build an HTML cache of some of your php queries (non-vbulletin)

[Generalissimo]

I checked the log files before and there was no evidence of many IPs or any overzealous IPs. Is there anything you can do in robots.txt to slow robots down? Is there anyway to check for script failures? I'm using WHM/CPanel on an Apache server if that helps.

[Generalissimo]

Oh, this is my MySQL process list atm (while problem is happening)



Mysql Process List
Id User Host db Command Time State Info
916 litforum_litforu localhost litforum_vbforum Sleep 4775 NULL
1051 litforum_litforu localhost litforum_vbforum Sleep 1409 NULL
1813 litforum_litforu localhost litforum_vbforum Sleep 1499 NULL
1978 litforum_litforu localhost litforum_vbforum Sleep 3286 NULL
3060 litforum_litforu localhost litforum_vbforum Sleep 3639 NULL
3875 litforum_litforu localhost litforum_vbforum Sleep 1213 NULL
6328 litforum_litforu localhost litforum_vbforum Sleep 4551 NULL
8761 litforum_litforu localhost litforum_vbforum Sleep 4117 NULL
9702 litforum_litforu localhost litforum_vbforum Sleep 2976 NULL
9807 litforum_litforu localhost litforum_vbforum Sleep 229 NULL
68302 lyricsbz_user localhost lyricsbz_lyrics Sleep 1187 NULL
88732 lyricsbz_user localhost lyricsbz_lyrics Sleep 7244 NULL
93643 litforum_litforu localhost litforum_vbforum Sleep 4092 NULL
93706 litforum_litforu localhost litforum_vbforum Sleep 1498 NULL
94014 lyricsbz_user localhost lyricsbz_lyrics Sleep 16505 NULL
94020 litforum_litforu localhost litforum_vbforum Sleep 1250 NULL
94289 litforum_litforu localhost litforum_vbforum Sleep 1166 NULL
142449 lyricsbz_user localhost lyricsbz_lyrics Sleep 683 NULL
152356 litforum_litforu localhost litforum_vbforum Sleep 2627 NULL
153849 litforum_litforu localhost litforum_vbforum Sleep 1875 NULL
154258 litforum_litforu localhost litforum_vbforum Sleep 2279 NULL
261109 lyricsbz_user localhost lyricsbz_lyrics Sleep 5135 NULL
264752 lyricsbz_user localhost lyricsbz_lyrics Sleep 5372 NULL
266069 lyricsbz_user localhost lyricsbz_lyrics Sleep 3325 NULL
267048 lyricsbz_user localhost lyricsbz_lyrics Sleep 9110 NULL
321414 lyricsbz_user localhost lyricsbz_lyrics Sleep 2084 NULL
332908 lyricsbz_user localhost lyricsbz_lyrics Sleep 3365 NULL
397699 lyricsbz_user localhost lyricsbz_lyrics Sleep 5563 NULL
401821 lyricsbz_user localhost lyricsbz_lyrics Sleep 1509 NULL
402035 lyricsbz_user localhost lyricsbz_lyrics Sleep 9953 NULL
403458 lyricsbz_user localhost lyricsbz_lyrics Sleep 3174 NULL
426513 litforum_litforu localhost litforum_vbforum Sleep 3184 NULL
427121 lyricsbz_user localhost lyricsbz_lyrics Sleep 2839 NULL
434051 litforum_litforu localhost litforum_vbforum Sleep 1051 NULL
435318 lyricsbz_user localhost lyricsbz_lyrics Sleep 16672 NULL
436070 litforum_litforu localhost litforum_vbforum Sleep 1660 NULL
436076 litforum_litforu localhost litforum_vbforum Sleep 1245 NULL
436450 lyricsbz_user localhost lyricsbz_lyrics Sleep 1544 NULL
437131 litforum_litforu localhost litforum_vbforum Sleep 5774 NULL
437523 litforum_litforu localhost litforum_vbforum Sleep 641 NULL
439268 litforum_litforu localhost litforum_vbforum Sleep 1275 NULL
439628 litforum_litforu localhost litforum_vbforum Sleep 1492 NULL
439704 litforum_litforu localhost litforum_vbforum Sleep 1832 NULL
439715 litforum_litforu localhost litforum_vbforum Sleep 109 NULL
440517 litforum_litforu localhost litforum_vbforum Sleep 2883 NULL
440538 litforum_litforu localhost litforum_vbforum Sleep 1359 NULL
441173 litforum_litforu localhost litforum_vbforum Sleep 1420 NULL
441286 litforum_litforu localhost litforum_vbforum Sleep 2946 NULL
441379 litforum_litforu localhost litforum_vbforum Sleep 2572 NULL
441534 litforum_litforu localhost litforum_vbforum Sleep 1949 NULL
441665 litforum_litforu localhost litforum_vbforum Sleep 3033 NULL
442436 litforum_litforu localhost litforum_vbforum Sleep 1291 NULL
442438 litforum_litforu localhost litforum_vbforum Sleep 2604 NULL
442441 litforum_litforu localhost litforum_vbforum Sleep 41 NULL
442574 litforum_litforu localhost litforum_vbforum Sleep 1778 NULL
443029 litforum_litforu localhost litforum_vbforum Sleep 2284 NULL
443037 lyricsbz_user localhost lyricsbz_lyrics Sleep 23006 NULL
443462 litforum_litforu localhost litforum_vbforum Sleep 5204 NULL
443535 litforum_litforu localhost litforum_vbforum Sleep 1411 NULL
443836 litforum_litforu localhost litforum_vbforum Sleep 144 NULL
445521 litforum_litforu localhost litforum_vbforum Sleep 2717 NULL
445588 lyricsbz_user localhost lyricsbz_lyrics Sleep 18590 NULL
446273 lyricsbz_user localhost lyricsbz_lyrics Sleep 5270 NULL
446672 litforum_litforu localhost litforum_vbforum Sleep 1394 NULL
446721 litforum_litforu localhost litforum_vbforum Sleep 2675 NULL
446723 litforum_litforu localhost litforum_vbforum Sleep 3613 NULL
446877 litforum_litforu localhost litforum_vbforum Sleep 616 NULL
447190 litforum_litforu localhost litforum_vbforum Sleep 6468 NULL
447535 litforum_litforu localhost litforum_vbforum Sleep 2398 NULL
447893 litforum_litforu localhost litforum_vbforum Sleep 2532 NULL
448240 litforum_litforu localhost litforum_vbforum Sleep 1501 NULL
450818 lyricsbz_user localhost lyricsbz_lyrics Sleep 683 NULL
451246 lyricsbz_user localhost lyricsbz_lyrics Sleep 5383 NULL
451604 litforum_litforu localhost litforum_vbforum Sleep 3294 NULL
452477 litforum_litforu localhost litforum_vbforum Sleep 1141 NULL
453185 lyricsbz_user localhost lyricsbz_lyrics Sleep 1170 NULL
453399 lyricsbz_user localhost lyricsbz_lyrics Sleep 9365 NULL
453560 lyricsbz_user localhost lyricsbz_lyrics Sleep 3496 NULL
455884 lyricsbz_user localhost lyricsbz_lyrics Sleep 1633 NULL
455981 lyricsbz_user localhost lyricsbz_lyrics Sleep 3111 NULL
456066 litforum_litforu localhost litforum_vbforum Sleep 4078 NULL
456108 litforum_litforu localhost litforum_vbforum Sleep 2284 NULL
456187 lyricsbz_user localhost lyricsbz_lyrics Sleep 7175 NULL
456265 lyricsbz_user localhost lyricsbz_lyrics Sleep 1379 NULL
456797 lyricsbz_user localhost lyricsbz_lyrics Sleep 3082 NULL
457985 litforum_litforu localhost litforum_vbforum Sleep 2623 NULL
458234 lyricsbz_user localhost lyricsbz_lyrics Sleep 1541 NULL
458279 litforum_litforu localhost litforum_vbforum Sleep 4740 NULL
458309 litforum_litforu localhost litforum_vbforum Sleep 220 NULL
458353 lyricsbz_user localhost lyricsbz_lyrics Sleep 1029 NULL
458413 litforum_litforu localhost litforum_vbforum Sleep 3345 NULL
461891 litforum_litforu localhost litforum_vbforum Sleep 3014 NULL
463161 litforum_litforu localhost litforum_vbforum Sleep 2243 NULL
464250 lyricsbz_user localhost lyricsbz_lyrics Sleep 1602 NULL
464283 lyricsbz_user localhost lyricsbz_lyrics Sleep 1571 NULL
464351 lyricsbz_user localhost lyricsbz_lyrics Sleep 1509 NULL
464437 lyricsbz_user localhost lyricsbz_lyrics Sleep 1446 NULL
464474 litforum_litforu localhost litforum_vbforum Sleep 1411 NULL
464809 lyricsbz_user localhost lyricsbz_lyrics Sleep 1206 NULL
466263 litforum_litforu localhost litforum_vbforum Sleep 143 NULL
466503 root localhost NULL Query 0 NULL SHOW PROCESSLIST

[Chris]

I checked the log files before and there was no evidence of many IPs or any overzealous IPs. Is there anything you can do in robots.txt to slow robots down? Is there anyway to check for script failures? I'm using WHM/CPanel on an Apache server if that helps.
There is no standard for robots.txt, but various search engines do provide facilities to ask them to crawl you slower.

If you think it is vbulletin specific, go to their forum, they have a subforum for server optimization requests. You post your files (my.cnf, httpd.conf, etc) vbulletin settings, and they tell you what to change to make it run faster.