MySQL load problems (15 CPU load..)

AresXP

Member
May 26, 2004
16
0
151
Hello,
Whenever I start MySQL, load climbs to 9-25!! I've tried many things. MySQL version is 4.1.10a.
My.cnf:
[mysqld]
set-variable = wait_timeout=30
set-variable = query_cache_type=1
set-variable = query_cache_limit=10M
set-variable = query_cache_size=150M
set-variable = connect_timeout=30
set-variable = interactive_timeout=30
set-variable = wait_timeout=20
set-variable = thread_cache=128
set-variable = thread_stack=100000
set-variable = thread_concurrency=10
set-variable = max_connections=500
safe-show-database
 

chirpy

Well-Known Member
Verifed Vendor
Jun 15, 2002
13,437
31
473
Go on, have a guess
Really need the information from top posted within CODE tags to tell, but at a guess I'd say they you're running out of memory and swapping to disk which is causing the high load. especially with query_cache_size=150M if you d not have that much RAM free for it.
 

AresXP

Member
May 26, 2004
16
0
151
Swap isnt used and memory usage is around 40%

Pid Owner Priority Cpu % Mem % Command
5477 mysql 0 25.6 3.2 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.technikahosting.net.pid --skip-locking.
3-4 of those processes
 

Sinewy

Well-Known Member
May 15, 2004
364
1
168
Sydney, Australia
cPanel Access Level
DataCenter Provider
Some of those settings are rather high, which is probably why you're getting high loads.

The following is more optimal:

[mysqld]
skip-locking
max_connections=300
connect_timeout=15
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
key_buffer=16M
join_buffer=1M
record_buffer=1M
sort_buffer=2M
table_cache=1028
thread_cache_size=286
max_allowed_packet=5M
wait_timeout=15
thread_concurrency=2
 

gr8ex

Member
Sep 30, 2004
23
0
151
Guten Tag

Mysql persistant connections can also cause load. Just set mysql persistant connection to off in php.ini

Also reduce the query cache size to 50MB..I think this can help you. :)
 

thehostinghut

Well-Known Member
Jan 5, 2005
232
0
166
DigitalN said:
Try installing MyTop http://jeremy.zawodny.com/mysql/mytop/ - that may shed some light on what is using up the resources with MySQL.

Check the disk performance using

# hdparm -t -T /dev/hda

Enable dma if it isn't enabled on your drives.
I did this and nothing out of the ordanary shows..... It does not show those processes.

Tracy
 

DigitalN

Well-Known Member
Sep 23, 2004
419
1
168
You could try commenting out the limits set in /etc/my.cnf and stop/start mysql, to see if that helps.

There may be bugs in that version of MySQL, you could upgrade to the latest 4.1.11

http://dev.mysql.com/downloads/mysql/4.1.html

or downgrade back to 4.0.x

Downgrading may require backups and recompiling apache/php if you do that.
I'd try the limits one by one in my.cnf first then upgrade to the latest version.
 
S

Secret Agent

Guest
I'm having the same problem

/etc/my.cnf

Code:
[mysqld]
skip-locking
max_connections=300
connect_timeout=15
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
key_buffer=16M
join_buffer=1M
record_buffer=1M
sort_buffer=2M
table_cache=1028
thread_cache_size=286
max_allowed_packet=5M
wait_timeout=15
thread_concurrency=2

[mysqld_safe]
open_files_limit = 8192
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer_size = 64M
sort_buffer_size = 64M
read_buffer_size = 4M
write_buffer_size = 4M

[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 64M
read_buffer_size = 4M
write_buffer_size = 4M

[mysqlhotcopy]
interactive-timeout
TOP shows mysql using the load generally, but can't trace it.

6.5% cpu usage while these below happened

Code:
19139 mysql     18   0 33296  22M  1944 R    26.6  4.6   0:12   0 mysqld
18979 nobody    15   0 10772  10M  3608 S     1.4  2.0   0:00   0 httpd
18753 nobody    16   0 10688  10M  3752 S     0.9  2.0   0:01   0 httpd
    7 root      15   0     0    0     0 SW    0.8  0.0   0:08   1 kswapd
14650 nobody    15   0 20660 8784  2020 D     0.6  1.7   0:02   1 spamd
18447 nobody    15   0  9180 9024  3568 S     0.6  1.7   0:00   1 httpd
    8 root      15   0     0    0     0 SW    0.3  0.0   0:13   1 kscand
15309 nobody    15   0 10508  10M  3720 S     0.2  2.0   0:02   0 httpd
19136 mysql     15   0 33296  22M  1944 D     0.2  4.6   0:09   0 mysqld
18441 nobody    15   0  7360 7204  3520 S     0.1  1.4   0:00   0 httpd
18940 root      15   0  1240 1240   896 R     0.1  0.2   0:00   1 top
ps auxf shows:

Code:
mysql    19055  2.1  4.2 100084 21696 ?      S    16:30   0:00 /usr/sbin/mysqld
as the only lines with any thing around 2.0 cpu usage
 

WeMasterz5

Well-Known Member
Feb 24, 2003
360
0
166
Miami
Michael from cPanel helped me out with something like this

we were seeing something like this jam up the load on the server

%CPU 99.9 usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/pub1.*****.com.pid --skip-locking

%CPU 99.6 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/pub1.********.com.pid --skip-locking

%CPU 97.8 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/pub1.*********.com.pid --skip-locking

we done this and it seems to be helping so far

alter table `YOURDB_statz`.`mnmstats_info` add index `ip`(`ip`);

The cpu-intensive queries seem to have been coming from the YOURBD_statz database. I've added an index on the "ip" field of the "mnmstats_info" so the queries don't have to scan the whole table every time, and the mysql load seems to have gone down
 

WeMasterz5

Well-Known Member
Feb 24, 2003
360
0
166
Miami
you will need to find which database it is that is overloading
 

AndyReed

Well-Known Member
PartnerNOC
May 29, 2004
2,217
4
193
Minneapolis, MN
We have seen this problem happening with few of our clients. The main reason was an insecure Php and/or cgi script that was not coded properly. I am referring to scripts written by individuals, or freedly distributed programs with the CPanel including PhpBB, PhpNume, and osCommerce. You'll have to upgrade these programs to the latest release, and/or ask the client to fix their own scripts.
 

SteveD3

Member
Jul 3, 2003
23
0
151
WeMasterz5 said:
Michael from cPanel helped me out with something like this

we were seeing something like this jam up the load on the server

%CPU 99.9 usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/pub1.*****.com.pid --skip-locking

we done this and it seems to be helping so far

alter table `YOURDB_statz`.`mnmstats_info` add index `ip`(`ip`);

The cpu-intensive queries seem to have been coming from the YOURBD_statz database. I've added an index on the "ip" field of the "mnmstats_info" so the queries don't have to scan the whole table every time, and the mysql load seems to have gone down
I'm not seeing where I need to index anything. the DB that is causing the issues has no such record for mnmstats, nor is one names statz

Can you shed a little more information on this resolution

Thanks

Steve