MySQL is crashing my computer

[traian20031]

Hi,
I have a server that currently runs WHM 8.6.0, cPanel 8.7.0-R26, and RH9.

The problem is that the server is always overloaded (Server Load 2.21 (1 cpu) ) and I found it to be due to MySQL. Now I've update MySQL to the newest version (4.0.17) and it still overloads. I used it on two different servers and both seem to crash everyday or so...

Both servers were/is P4 2.4GHz/1GB RAM. I have a huge forum that loads music and pictures constantly but it is not a reason to crash the server that often. This is what CPU Usage shows:
789 root 0 99.9 0.1 /usr/local/apache/bin/httpd-DSSL

913 mysql 0 99.9 1.5 /usr/sbin/mysqld--basedir=/--datadir=/var/lib/mysql--user=mysql--pid-file=/var/lib/mysql/server.XXXXXXXXXX.net.pid--skip-locking--socket=/var/lib/mysql/mysql.sock


What I was thinking is that I should allow less resources to MySQL but I don't know how to do that.
When I check my.cnf it shows the follwing:
________________________________
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
_________________________________

Anyhelp will be greatly appreciated...

[twhiting9275]

Here's a sample my.cf, works pretty good for me.

[mysqld]
set-variable = max_connections=250
set-variable = interactive_timeout=60
set-variable = max_user_connections=20
set-variable = wait_timeout=60
set-variable = connect_timeout=10
set-variable = thread_cache_size=8
set-variable = key_buffer=384M
set-variable = max_allowed_packet=32M
set-variable = table_cache=1024
set-variable = sort_buffer=4M
set-variable = record_buffer=4M
set-variable = thread_cache=8
set-variable = thread_concurrency=2
set-variable = myisam_sort_buffer_size=64M


[safe_mysqld]
err-log=/var/log/mysqld.log
open_files_limit=8192

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash
[isamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

That does a lot of the "optimization" for you, but if you don't have a server with decent stats, and run constant queries, then you're going to have problems.

[maverick]

Thanks for posting that up. To give it some context though, could you let us know what the specs are of your server (RAM/CPU)?

Mav.

[traian20031]

thanks, I'm going to try this for a while see how it works out for me.