asked on

mysql cause high load

Hello ,

I have a high load on mysql I hope some one help to adjust this high load
this my server specs :

1 X quad core Intel(R) Xeon(R) CPU X3230 @ 2.66GHz
RAM 4Gb
HDD 2 X 250 Gb

my top stat :

top - 06:16:22 up 1 day, 18:05, 1 user, load average: 6.78, 7.16, 6.58
Tasks: 286 total, 1 running, 283 sleeping, 1 stopped, 1 zombie
Cpu(s): 41.9%us, 18.4%sy, 0.0%ni, 39.2%id, 0.3%wa, 0.0%hi, 0.2%si, 0.0%st
Mem: 4151076k total, 3543080k used, 607996k free, 233564k buffers
Swap: 2096440k total, 7092k used, 2089348k free, 1536072k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4219 mysql 15 0 639m 570m 3500 S 178.1 14.1 1877:36 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=my
10017 nobody 16 0 163m 26m 10m S 20.3 0.6 0:02.24 /usr/local/apache/bin/httpd -k start -DSSL
10386 nobody 15 0 158m 17m 7120 S 10.0 0.4 0:00.32 /usr/local/apache/bin/httpd -k start -DSSL
8419 nobody 15 0 159m 23m 12m S 9.6 0.6 0:01.89 /usr/local/apache/bin/httpd -k start -DSSL
9837 nobody 15 0 158m 21m 9.9m S 9.6 0.5 0:01.66 /usr/local/apache/bin/httpd -k start -DSSL
8837 nobody 16 0 161m 24m 11m S 2.0 0.6 0:06.47 /usr/local/apache/bin/httpd -k start -DSSL
10044 nobody 15 0 159m 21m 10m S 2.0 0.5 0:00.21 /usr/local/apache/bin/httpd -k start -DSSL
10051 nobody 15 0 159m 20m 9740 S 1.7 0.5 0:00.84 /usr/local/apache/bin/httpd -k start -DSSL
10047 nobody 17 0 160m 23m 10m S 1.0 0.6 0:00.66 /usr/local/apache/bin/httpd -k start -DSSL
9796 nobody 16 0 161m 22m 9.9m S 0.7 0.6 0:01.21 /usr/local/apache/bin/httpd -k start -DSSL
10009 nobody 18 0 160m 22m 9880 S 0.7 0.6 0:01.10 /usr/local/apache/bin/httpd -k start -DSSL
10052 nobody 15 0 159m 19m 8180 S 0.7 0.5 0:00.20 /usr/local/apache/bin/httpd -k start -DSSL
8842 nobody 16 0 159m 22m 11m S 0.3 0.6 0:01.97 /usr/local/apache/bin/httpd -k start -DSSL
9171 nobody 16 0 159m 22m 10m S 0.3 0.6 0:04.56 /usr/local/apache/bin/httpd -k start -DSSL
9765 nobody 15 0 161m 22m 9020 S 0.3 0.6 0:02.17 /usr/local/apache/bin/httpd -k start -DSSL
9805 nobody 22 0 158m 21m 10m S 0.3 0.5 0:00.49 /usr/local/apache/bin/httpd -k start -DSSL
9843 nobody 15 0 159m 21m 10m S 0.3 0.5 0:01.40 /usr/local/apache/bin/httpd -k start -DSSL
10005 nobody 18 0 159m 19m 8596 S 0.3 0.5 0:00.98 /usr/local/apache/bin/httpd -k start -DSSL
10010 nobody 20 0 160m 20m 8648 S 0.3 0.5 0:01.48 /usr/local/apache/bin/httpd -k start -DSSL
10019 nobody 15 0 159m 21m 9876 S 0.3 0.5 0:00.61 /usr/local/apache/bin/httpd -k start -DSSL
10024 nobody 16 0 159m 22m 10m S 0.3 0.5 0:00.32 /usr/local/apache/bin/httpd -k start -DSSL
10026 nobody 20 0 159m 18m 7340 S 0.3 0.4 0:00.70 /usr/local/apache/bin/httpd -k start -DSSL
10030 nobody 15 0 159m 19m 8516 S 0.3 0.5 0:00.51 /usr/local/apache/bin/httpd -k start -DSSL
10043 nobody 21 0 0 0 0 Z 0.3 0.0 0:01.02 [httpd] <defunct>
10048 nobody 15 0 159m 19m 8312 S 0.3 0.5 0:00.46 /usr/local/apache/bin/httpd -k start -DSSL
10058 nobody 15 0 159m 23m 12m S 0.3 0.6 0:00.50 /usr/local/apache/bin/httpd -k start -DSSL
10376 nobody 15 0 158m 18m 7228 S 0.3 0.4 0:00.31 /usr/local/apache/bin/httpd -k start -DSSL
10378 nobody 15 0 158m 16m 5768 S 0.3 0.4 0:00.03 /usr/local/apache/bin/httpd -k start -DSSL
10379 nobody 15 0 157m 12m 3312 S 0.3 0.3 0:00.02 /usr/local/apache/bin/httpd -k start -DSSL
10391 nobody 15 0 158m 16m 5232 S 0.3 0.4 0:00.13 /usr/local/apache/bin/httpd -k start -DSSL
10403 nobody 20 0 159m 19m 8676 S 0.3 0.5 0:00.25 /usr/local/apache/bin/httpd -k start -DSSL
10456 root 15 0 2328 1132 796 R 0.3 0.0 0:00.03 top c
1 root 15 0 2064 628 536 S 0.0 0.0 0:02.07 init [3]
2 root RT -5 0 0 0 S 0.0 0.0 0:00.03 [migration/0]
3 root 34 19 0 0 0 S 0.0 0.0 0:00.01 [ksoftirqd/0]

mysqladmin proc stat

+--------+--------------+-----------+---------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+--------------+-----------+---------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 491439 | eximstats | localhost | eximstats | Sleep | 1918 | | |
| 509149 | asssso_awal | localhost | asssso_vbnew | Query | 1 | Sorting result | SELECT userid, usergroupid, displaygroupid, username, posts
FROM user AS user
WHERE po |
| 509150 | asssso_awal | localhost | asssso_vbnew | Query | 1 | Sorting result | SELECT userid, usergroupid, displaygroupid, username, posts
FROM user AS user
WHERE po |
| 509153 | asssso_awal | localhost | asssso_vbnew | Query | 1 | Sorting result | SELECT thread.threadid, thread.title, thread.lastpost, thread.forumid, thread.replycount, thread.las |
| 509160 | asssso_awal | localhost | asssso_vbnew | Query | 3 | Sorting result | SELECT userid, usergroupid, displaygroupid, username, joindate, posts
FROM user AS user
|
| 509162 | asssso_awal | localhost | asssso_vbnew | Query | 2 | Locked | UPDATE user
SET pmtotal = pmtotal + 1, pmunread = pmunread + 1,
pmpopup =
CASE
|
| 509165 | asssso_awal | localhost | asssso_vbnew | Query | 2 | Sorting result | SELECT userid, usergroupid, displaygroupid, username, joindate, posts
FROM user AS user
|
| 509167 | asssso_awal | localhost | asssso_vbnew | Query | 2 | Sorting result | SELECT userid, usergroupid, displaygroupid, username, joindate, posts
FROM user AS user
|
| 509175 | asssso_awal | localhost | asssso_vbnew | Query | 1 | Locked | UPDATE user SET
profilevisits = profilevisits + 1
WHERE userid = 169224 |
| 509179 | root | localhost | | Query | 0 | | show processlist |
+--------+--------------+-----------+---------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
Uptime: 68780 Threads: 10 Questions: 13058483 Slow queries: 8788 Opens: 4603 Flush tables: 1 Open tables: 3015 Queries per second avg: 189.859

my.cnf 
 
[mysqld]
skip-innodb
skip-locking
local-infile=0
long_query_time=5
log-slow-queries
connect_timeout=10
#wait_timeout=5
interactive_timeout=30
max_connections = 300
key_buffer_size = 384M
max_allowed_packet = 16M
table_cache = 4048
max_heap_table_size = 192M
sort_buffer_size = 8M
read_buffer_size = 1M
thread_cache_size= 8
join_buffer_size = 8M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
bulk_insert_buffer_size = 8M
query_cache_type=1
query_cache_size = 100M
query_cache_limit = 2M
thread_concurrency = 8
low_priority_updates=1
tmp_table_size = 96M
 
[mysql.server]
 
user=mysql
 
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
 
[mysqld_safe]
open_files_limit = 10522
 
[mysqldump]
quick
max_allowed_packet=16M
 
[mysql]
no-auto-rehash
#safe-updates
connect_timeout= 5
 
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 16M
write_buffer = 16M
 
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 16M
write_buffer = 16M
 
[mysqlhotcopy]
interactive-timeout

Open in new window

xserverx

ASKER

this is mysqltuner results :

 >>  MySQLTuner 1.0.0 - Major Hayden <major@mhtx.net>
 >>  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.67-community-log
[!!] 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: 3G (Tables: 1037)
[--] Data in MEMORY tables: 3M (Tables: 9)
[!!] Total fragmented tables: 32
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 19h 56m 9s (13M q [193.427 qps], 537K conn, TX: 4B, RX: 2B)
[--] Reads / Writes: 82% / 18%
[--] Total buffers: 590.0M global + 21.2M per thread (300 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 6.8G (171% of installed RAM)
[OK] Slow queries: 0% (9K/13M)
[OK] Highest usage of available connections: 75% (227/300)
[OK] Key buffer size / total MyISAM indexes: 384.0M/855.5M
[OK] Key buffer hit rate: 99.9% (1B cached / 1M reads)
[OK] Query cache efficiency: 79.7% (9M cached / 12M selects)
[OK] Query cache prunes per day: 0
[!!] Sorts requiring temporary tables: 18% (121K temp sorts / 662K sorts)
[!!] Joins performed without indexes: 1672
[OK] Temporary tables created on disk: 2% (8K on disk / 288K total)
[OK] Thread cache hit rate: 94% (28K created / 537K connections)
[OK] Table cache hit rate: 65% (3K open / 4K opened)
[OK] Open file limit used: 48% (4K/8K)
[OK] Table locks acquired immediately: 98% (4M immediate / 4M locks)
 
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    sort_buffer_size (> 8M)
    read_rnd_buffer_size (> 4M)
    join_buffer_size (> 8.0M, or always use indexes with joins)

Open in new window

ASKER CERTIFIED SOLUTION

theGhost_k8

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xserverx

ASKER

th output of ulimit -n :
4096

show variables like '%cache%';
is :

mysql> show variables like '%cache%';
+------------------------------+------------+
| Variable_name                | Value      |
+------------------------------+------------+
| binlog_cache_size            | 32768      |
| have_query_cache             | YES        |
| key_cache_age_threshold      | 300        |
| key_cache_block_size         | 1024       |
| key_cache_division_limit     | 100        |
| max_binlog_cache_size        | 4294963200 |
| ndb_cache_check_time         | 0          |
| query_cache_limit            | 2097152    |
| query_cache_min_res_unit     | 4096       |
| query_cache_size             | 104857600  |
| query_cache_type             | ON         |
| query_cache_wlock_invalidate | OFF        |
| table_cache                  | 4048       |
| thread_cache_size            | 8          |
+------------------------------+------------+
14 rows in set (0.00 sec)
 
mysql>

Open in new window

SOLUTION

cjl7

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial