Results 1 to 6 of 6

Thread: MySQL connection overload - what is causing it?

  1. #1
    Registered Generalissimo's Avatar
    Join Date
    Mar 2005
    Location
    Huddersfield, UK
    Posts
    409

    MySQL connection overload - what is causing it?

    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?
    Sean Spurr. Incka Media.
    49 Link Building Service

  2. #2
    Not that blue at all Blue Cat Buxton's Avatar
    Join Date
    May 2004
    Location
    UK
    Posts
    932
    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?

  3. #3
    Administrator Chris's Avatar
    Join Date
    Feb 2003
    Location
    East Lansing, MI USA
    Posts
    7,048
    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)
    Chris Beasley - My Guide to Building a Successful Website[size=1]
    Content Sites: ABCDFGHIJKLMNOP|Forums: ABCD EF|Ecommerce: Swords Knives

  4. #4
    Registered Generalissimo's Avatar
    Join Date
    Mar 2005
    Location
    Huddersfield, UK
    Posts
    409
    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.
    Sean Spurr. Incka Media.
    49 Link Building Service

  5. #5
    Registered Generalissimo's Avatar
    Join Date
    Mar 2005
    Location
    Huddersfield, UK
    Posts
    409
    Oh, this is my MySQL process list atm (while problem is happening)

    Code:
    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
    Sean Spurr. Incka Media.
    49 Link Building Service

  6. #6
    Administrator Chris's Avatar
    Join Date
    Feb 2003
    Location
    East Lansing, MI USA
    Posts
    7,048
    Quote Originally Posted by Generalissimo View Post
    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.
    Chris Beasley - My Guide to Building a Successful Website[size=1]
    Content Sites: ABCDFGHIJKLMNOP|Forums: ABCD EF|Ecommerce: Swords Knives

Similar Threads

  1. Replies: 0
    Last Post: 01-04-2006, 10:40 PM
  2. Replies: 0
    Last Post: 11-23-2005, 04:49 AM
  3. Clear my misunderstanding: 1 MySQL database for many programs?
    By iKwak in forum Website Programming & Databases
    Replies: 4
    Last Post: 08-21-2005, 10:40 AM
  4. Replies: 0
    Last Post: 07-13-2005, 08:01 AM
  5. Replies: 0
    Last Post: 02-27-2005, 10:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •