Mysql slowing my server down??

[ChrisFrez]

I'm trying to figure out what is slowing my server down in which I have Vbulletin 3.5.4 loaded on. The last couple of months many of my users are reporting that the board would stall. I'm in need of some help in troubleshooting this.

Here is a screen shot of a top command in unix that I did when I experienced some slowness. Normally there is between 450-500 users online, it can rise to 800+ in the evening. Please note the mysql process.

Tasks: 145 total, 3 running, 142 sleeping, 0 stopped, 0 zombie
Cpu(s): 14.3% us, 1.2% sy, 0.0% ni, 83.9% id, 0.5% wa, 0.0% hi, 0.0% si
Mem: 2074844k total, 1606536k used, 468308k free, 26656k buffers
Swap: 2040244k total, 380136k used, 1660108k free, 1106344k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
27807 apache 15 0 27728 10m 4396 S 9.6 0.5 0:05.98 httpd
27856 apache 15 0 30232 12m 4444 S 7.0 0.6 0:07.12 httpd
27723 apache 15 0 27524 9.8m 4396 S 6.7 0.5 0:02.85 httpd
23720 apache 15 0 33668 13m 5096 S 6.0 0.7 0:20.69 httpd
27757 apache 16 0 30240 12m 4420 S 5.3 0.6 0:05.95 httpd
27953 apache 15 0 28980 11m 4644 S 5.3 0.6 0:06.42 httpd
28902 apache 15 0 28900 11m 4416 S 5.0 0.6 0:04.37 httpd
28907 apache 15 0 27840 10m 4420 S 4.7 0.5 0:03.32 httpd
29211 apache 15 0 27568 9.8m 4388 S 4.7 0.5 0:01.84 httpd
2187 mysql 16 0 404m 198m 3600 S 4.3 9.8 661:35.76 mysqld
28768 apache 15 0 28356 10m 4392 S 1.3 0.5 0:03.37 httpd
29202 apache 16 0 29048 11m 4420 S 1.3 0.6 0:01.90 httpd
28893 apache 16 0 27772 10m 4400 R 1.0 0.5 0:03.45 httpd
27739 apache 16 0 30548 13m 4652 R 0.7 0.6 0:07.33 httpd
27759 apache 15 0 30216 12m 4428 S 0.3 0.6 0:05.57 httpd
29673 root 16 0 2384 976 744 R 0.3 0.0 0:00.08 top

I've also been receiving probably around 2-6 e-mails per date with the mysql error of receiving a packet too large and a huge message that looks like somebody was doing a generic search on my site.

A little info on my server -

1 - Dedicated Server
2 - DUAL XEON 3.0GHZ PROC / 2GB of RAM / 5 X 73GB drives in RAID5 / Red Hat Enterprise 4
PHP Version 4.3.9
Apache/2.0.52 (Red Hat)
mysql 4.1.12

Any other questions to help figure this out, i'll be happy to answer. Thanks in advance!

[Marco van Herwaarden]

The package too large, could come from attachments. If they are stored in your database, you could consider moving them to the filesystem.

If you want to find out about the high server loads, i suggest your post the information as requested [Server Optimisation]: Read This Before Posting (required by eva2000) [Feb 26, 2006] (http://www.vbulletin.com/forum/showthread.php?t=70117) and post an optimisation request in the Server Configuration forum.

[ChrisFrez]

We do not allow file attachments on our system.

Thank you, i'll follow the instructions.

[Zachery]

Can you give us the exact error messages you were getting?

[ChrisFrez]

Here is the exact error message:

Database error in vBulletin 3.5.4:

Invalid SQL:

SELECT
thread.threadid
FROM thread AS thread
INNER JOIN post AS post ON(thread.threadid = post.threadid )
WHERE post.postid
IN (7741 - a whole other bunch of numbers)
MySQL Error : Got a packet bigger than 'max_allowed_packet' bytesError Number : 1153Date : Friday, June 16th 2006 @ 02:42:48 PMScript : http://web.camaross.com/forums/search.php (http://web.camaross.com/forums/search.php)Referrer : http://web.camaross.com/forums/search.php?searchid=1092315 (http://web.camaross.com/forums/search.php?searchid=1092315)IP Address : 216.0.247.2Username : ChrisFrezClassname : vb_database

[Marco van Herwaarden]

What is the current value of the 'max_allowed_packet' parameter in your my.cnf? You might need to ask your host this. If i remember correct, that should be set to 14400 minimum to avoid problems.

But i still suggest you follow my suggestion in previous post and post in the Server Configuration forum.

[ChrisFrez]

There is no max_allowed_packet value in my /etc/my.cnf file - so whatever the default is, that is what it probably is set at.

I will do the server configuration forum as well.

[Zachery]

By default its 1mb which isn't much.