MySQL Errors & High Load - Help Request

[Jennifer2010]

Hi Everyone,

My load in WHM seems to spike up between 5.00 - 35.00 lately and I see this as one of the top processes:

/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/my.hostname.err --pid-file=/var/lib/mysql/my.hostname.com.pid

In WHM's "Process Manager" it shows the process at 20.7 CPU% and 5.1% Memory Percentage (both the highest CPU % and memory %)

I'm pasting the output of some commands below. Any help is GREATLY appreciated!

- Jennifer

[B]mysql_config --version[/B]
5.1.66[/QUOTE]

[B]cat /etc/my.cnf:[/B]
[QUOTE][mysqld]
innodb_file_per_table=1

[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
skip-locking
#skip-innodb
query_cache_limit=4M
query_cache_size=128M
query_cache_type=1
max_user_connections=500
max_connections=1000
interactive_timeout=28000
wait_timeout=28800
connect_timeout=28800
thread_cache_size=256
key_buffer=512M
join_buffer=16M
max_allowed_packet=32M
table_cache=5096
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
thread_concurrency=8
myisam_sort_buffer_size=64M
server-id=1
table_open_cache=5096
local-infile=0
join_buffer_size=8M


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

#[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

[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

/usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl:

 >>  MySQLTuner 1.2.0_1 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.66-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 802)
[--] Data in InnoDB tables: 9M (Tables: 15)
[--] Data in MEMORY tables: 2M (Tables: 11)
[!!] Total fragmented tables: 16

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 17h 37m 11s (104M q [697.256 qps], 5M conn, TX: 2501B, RX: 19B)
[--] Reads / Writes: 82% / 18%
[--] Total buffers: 666.0M global + 12.5M per thread (1000 max threads)
[OK] Maximum possible memory usage: 12.9G (82% of installed RAM)
[OK] Slow queries: 0% (38/104M)
[OK] Highest usage of available connections: 33% (338/1000)
[OK] Key buffer size / total MyISAM indexes: 512.0M/2.0G
[OK] Key buffer hit rate: 99.5% (1B cached / 6M reads)
[OK] Query cache efficiency: 57.4% (39M cached / 69M selects)
[!!] Query cache prunes per day: 3256506
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 2M sorts)
[!!] Joins performed without indexes: 13028
[OK] Temporary tables created on disk: 13% (71K on disk / 546K total)
[OK] Thread cache hit rate: 99% (470 created / 5M connections)
[!!] Table cache hit rate: 8% (664 open / 8K opened)
[OK] Open file limit used: 8% (982/11K)
[OK] Table locks acquired immediately: 99% (57M immediate / 58M locks)
[!!] InnoDB data size / buffer pool: 9.7M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 128M)
    join_buffer_size (> 8.0M, or always use indexes with joins)
    table_cache (> 5096)
    innodb_buffer_pool_size (>= 9M)

 

[Greenhost]

You should install mytop, that will help you to monitor an analytic your data base, what query is run, who is connected to your DB and etc.
Here the mytop documentation .

I recomend you to set "wait_timeout" and "max_connections" value as lower as possible. And set "key_buffer" value high, but Don't set it higher than the sum of all of your MYI files and your RAM.

 

[Jennifer2010]

You should install mytop, that will help you to monitor an analytic your data base, what query is run, who is connected to your DB and etc.
Here the mytop documentation .

I recomend you to set "wait_timeout" and "max_connections" value as lower as possible. And set "key_buffer" value high, but Don't set it higher than the sum of all of your MYI files and your RAM.

Thank you for the response!

I tried installing MyTop and got this error:

Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package mytop.noarch 0:1.4-2.el5.rf set to be updated
--> Processing Dependency: perl(Term::ReadKey) for package: mytop
--> Processing Dependency: perl(DBI) for package: mytop
--> Finished Dependency Resolution
mytop-1.4-2.el5.rf.noarch from rpmforge has depsolving problems
  --> Missing Dependency: perl(DBI) is needed by package mytop-1.4-2.el5.rf.noar                              ch (rpmforge)
mytop-1.4-2.el5.rf.noarch from rpmforge has depsolving problems
  --> Missing Dependency: perl(Term::ReadKey) is needed by package mytop-1.4-2.e                              l5.rf.noarch (rpmforge)
Error: Missing Dependency: perl(DBI) is needed by package mytop-1.4-2.el5.rf.noa                              rch (rpmforge)
Error: Missing Dependency: perl(Term::ReadKey) is needed by package mytop-1.4-2.                              el5.rf.noarch (rpmforge)
 You could try using --skip-broken to work around the problem
 You could try running: package-cleanup --problems
                        package-cleanup --dupes
                        rpm -Va --nofiles --nodigest
The program package-cleanup is found in the yum-utils package.

Any ideas?

 

[Archmactrix]

Any ideas?

You seem to be missing these two perl modules as mytop dependencies:

DBI
Term::ReadKey