mysql 131.92 65.45 0.0

mitul

Well-Known Member
Feb 8, 2003
291
0
166
Hello All,

DUAL => Pentium(R) III CPU family 1133MHz
RAM => 1 GB.

10 sites only .

Only one Site which is Mysql intensive load damn slow.

This is Constant on my server.......

mysql 131.92 65.45 0.0
Top Process %CPU 8.5 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.myservers.pid --skip-locking
Top Process %CPU 7.8 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.myservers.pid --skip-locking
Top Process %CPU 7.6 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.myservers.pid --skip-locking

cat /etc/my.cnf
=============
[mysqld]
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 1M
table_cache = 1500
thread_cache_size = 32
wait_timeout = 14400
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
thread_concurrency=4
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
skip-innodb

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

ps auxwwwf
===========
root 4581 0.0 0.1 2212 1056 pts/0 S 13:42 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/server.servers.com.pid
mysql 4604 0.0 0.4 43684 4544 pts/0 S 13:42 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4606 0.0 0.4 43684 4544 pts/0 S 13:42 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4607 0.0 0.4 43684 4544 pts/0 S 13:42 0:00 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4608 4.3 0.4 43684 4544 pts/0 S 13:42 1:07 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4609 0.2 0.4 43684 4544 pts/0 S 13:42 0:03 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4610 4.7 0.4 43684 4544 pts/0 S 13:42 1:14 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4611 4.0 0.4 43684 4544 pts/0 S 13:42 1:03 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4612 4.0 0.4 43684 4544 pts/0 S 13:42 1:03 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4613 3.7 0.4 43684 4544 pts/0 S 13:42 0:58 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4614 4.7 0.4 43684 4544 pts/0 S 13:42 1:14 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4615 4.8 0.4 43684 4544 pts/0 S 13:42 1:16 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4616 4.6 0.4 43684 4544 pts/0 S 13:42 1:12 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4617 4.5 0.4 43684 4544 pts/0 R 13:42 1:10 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4697 4.5 0.4 43684 4544 pts/0 S 13:44 1:06 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4698 4.9 0.4 43684 4544 pts/0 S 13:44 1:12 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4699 4.3 0.4 43684 4544 pts/0 S 13:44 1:03 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4700 4.3 0.4 43684 4544 pts/0 S 13:44 1:03 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4701 4.9 0.4 43684 4544 pts/0 S 13:44 1:12 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 4702 4.8 0.4 43684 4544 pts/0 S 13:44 1:10 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 5014 4.5 0.4 43684 4544 pts/0 R 13:50 0:49 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 5015 3.8 0.4 43684 4544 pts/0 S 13:50 0:41 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 5016 4.8 0.4 43684 4544 pts/0 S 13:50 0:52 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 5090 5.1 0.4 43684 4544 pts/0 S 13:52 0:51 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking
mysql 5172 3.0 0.4 43684 4544 pts/0 S 13:55 0:24 \_ /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.servers.com.pid --skip-locking

The Mysql Load is always around 135.x and never goes down.

Any comments will be appreciated.

Thank you,
:confused:
 

mitul

Well-Known Member
Feb 8, 2003
291
0
166
Originally posted by MattDr2
I'm having a similar problem across a few servers... No luck yet on a fix.

Regards,
Matt
I put a ticket to cpanel stating the above, They say that if you have a Busy MySQL intensive site then that very Normal, You dont need to worry about that.

But I am still looking for some solution to this ... :)

Thank you,
 

Finkinstein

Well-Known Member
Mar 21, 2003
131
0
166
I am having the same issue, but mysql is slow for everyone. One mysql intensive site (and I'm not talking very intensive) one a 2.8 dual processor with 1 gig of ram with a few other sites and everything goes haywhire!

Not rite in my opinion..
 

mickalo

Well-Known Member
Apr 16, 2002
782
5
318
N.W. Iowa
this may not pretain to all the problems noted in this thread, but many of the servers I've worked on, setting MySQL and various programming projects related to MySQL high loads, is usually due to lack of optimization and proper indexing. I always recommend when this issue are becoming a problem to enable the slow query log in the my.cnf main option file, under the mysqld group using the option setting:log-slow-queries = /path/to/mysql/slow_query.log, you'd be suriprized what you might find :)

if you find alot of SQL queries are load intensive, try using the EXPLAIN function to check the the query is properly index, using the indices, and it optimized properly.

The MySQL website has alot of good info about optimization and proper indexing. :)

Hope this helps ;)
Mickalo
 

anand

Well-Known Member
Nov 11, 2002
1,435
1
168
India
cPanel Access Level
DataCenter Provider
don't forget to touch and chown the file as its not auto created.

touch /path/to/mysql/slow_query.log
chown mysql.root /path/to/mysql/slow_query.log

Restart mysql and you should be ready to roll.
 

mickalo

Well-Known Member
Apr 16, 2002
782
5
318
N.W. Iowa
Originally posted by anand
don't forget to touch and chown the file as its not auto created.

touch /path/to/mysql/slow_query.log
chown mysql.root /path/to/mysql/slow_query.log

Restart mysql and you should be ready to roll.
If you set it in the main option file, under the mysqld option group setting, it will be automatically created after you restart MySQL... so you don't need to manually create it.

Mickalo
 

anand

Well-Known Member
Nov 11, 2002
1,435
1
168
India
cPanel Access Level
DataCenter Provider
Originally posted by mickalo
If you set it in the main option file, under the mysqld option group setting, it will be automatically created after you restart MySQL... so you don't need to manually create it.

Mickalo
Till date whenever i have done that i don't remember seeing the file created automatically, thats the reason when i saw this post i replied. If its auto created for you, thats nice.