my server get high load from mysql server

selvamurali

Active Member
Jan 15, 2009
31
0
56
India
cPanel Access Level
Root Administrator
hi all i get mysql server load very high
this is showing process list


Pid Owner Priority CPU % Memory % Command
26781 (Trace) (Kill) mytitbit 0 18.0 1.0 /usr/bin/php /disk1/mytitbit/public_html/user.php
25381 (Trace) (Kill) mysql 0 17.9 2.5 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/vmclouds.vmclouds.net.pid --skip-external-locking
26764 (Trace) (Kill) mytitbit 0 16.2 0.0
PHP:
 <defunct>
26802 (Trace) (Kill)	mytitbit	0	  14.0	     0.6	/usr/bin/php /disk1/mytitbit/public_html/404error.php
26754 (Trace) (Kill)	mytitbit	0	  12.1	     1.4	/usr/bin/php /disk1/mytitbit/public_html/search.php

what to do now
i am running mysql tunner it will given the followning result

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

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.92-community
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 383M (Tables: 5179)
[--] Data in InnoDB tables: 13M (Tables: 804)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 34

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3h 50m 57s (748K q [54.021 qps], 10K conn, TX: 860M, RX: 76M)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 34.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 302.7M (14% of installed RAM)
[OK] Slow queries: 0% (8/748K)
[OK] Highest usage of available connections: 11% (11/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/185.0M
[OK] Key buffer hit rate: 99.7% (261M cached / 776K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 8% (3K temp sorts / 39K sorts)
[!!] Joins performed without indexes: 2978
[!!] Temporary tables created on disk: 39% (14K on disk / 35K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 42K opened)
[OK] Open file limit used: 10% (107/1K)
[OK] Table locks acquired immediately: 99% (770K immediate / 770K locks)
[!!] InnoDB data size / buffer pool: 13.1M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 13M)[/QUOTE]

kindle help
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
Hello,

If /etc/my.cnf only has that line in it, then put the following into that file:

Code:
[mysqld]
innodb_buffer_pool_size = 13M
max_heap_table_size = 48M
query_cache_limit = 1M
query_cache_size = 8M
query_cache_type = 1
table_cache = 80
thread_cache_size = 4
tmp_table_size = 48M
After setting the above variables, you could then restart MySQL:

Code:
/etc/init.d/mysql restart
Thanks!
 

huggys

Registered
Nov 6, 2009
2
0
51
I have the same issue and I was wondering if you could take the time to look at mine as well?

Code:
[mysqld]
old_passwords=1
datadir=/var/lib/mysql
skip-locking
safe-show-database
tmp_table_size = 64M
max_heap_table_size = 64M
query_cache_limit=2M
query_cache_size=32M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=25
max_connections=100

collation_server=utf8_unicode_ci
character_set_server=utf8

delayed_insert_timeout=40

interactive_timeout=10
wait_timeout=3600
connect_timeout=20
thread_cache_size=128
key_buffer=32M ## 32MB 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=1M ## 1MB for every 1GB of RAM
read_buffer_size=1M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=1M  ## 1MB for every 1GB of RAM
thread_concurrency=2 ## Number of CPUs x 2
myisam_sort_buffer_size=32M
server-id=1

[mysql.server]
user=mysql

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

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
log-slow-queries=/var/log/mysql_slow_queries.log
Thank you!
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
Hello huggys,

If you post it into your own thread, we could look at it. It isn't normally advisable to post your issue into another thread when it's a discussion on configuring specific server options.

If you'd like, I would be happy to move your post into your own thread.

Thanks!