Web Hosting Talk : Web Hosting Main Forums : Hosting Security and Technology : trace mysql

[Zion Ahead]

Can someone give me several various ways to trace server load? It seems to be a mysql issue with me as I ran top -c and suspended exim to check, mysql seems to be using a lot of cpu resources

What I've done:
suspend exim
top -c
mysqladmin processlist (constantly shows eximstats), else is normal
/usr/sbin/lsof -p (which lists just about every account)
ps aux | grep mysql
lsof -p PROCESS-ID |more
Using mysql 5, centos 4 / cpanel stable version

I have about 400 accounts, mostly reseller sub accounts and about 30% disk usage.

[david510]

If mysql is taking lot of cpu resourses, you have to find out which database is eating up the resourses. You can use mysqladmin processlist for this
May be use mysqladmin processlist -i4
This will refresh in every 4 seconds. Then you will be able to see if it is any particular database which is causing the issue.
Also, what is your my.cnf file? Have you optimized it?

[Zion Ahead]

my.cnf


#DO NOT MODIFY THE FOLLOWING COMMENTED LINES!
#Created with ELS from www.servermonkeys.com
#els-build=5.0
[mysqld]
datadir=/var/lib/mysql
skip-locking
skip-innodb
#skip-networking
safe-show-database
query_cache_limit=1M
query_cache_size=128M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=225
max_connections=550
interactive_timeout=10
wait_timeout=20
connect_timeout=20
thread_cache_size=128
key_buffer=512M ## 128MB for every 1GB of RAM
join_buffer=1M
max_connect_errors=20
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=4M ## 1MB for every 1GB of RAM
read_buffer_size=4M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=4M ## 1MB for every 1GB of RAM
thread_concurrency=8 ## Number of CPUs x 2
myisam_sort_buffer_size=64M
server-id=1
log_slow_queries=/var/log/mysql-slow-queries.log
long_query_time=2
collation-server=latin1_general_ci

[mysql.server]
user=mysql
old-passwords = 1

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

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[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

[mysqlhotcopy]
interactive-timeout


Before I increased the below from 200 and 500 to what is shown now

max_user_connections=225
max_connections=550

[Zion Ahead]

eximstats is constantly showing up when I run
mysqladmin processlist -i4