Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1152
  • Last Modified:

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

0
xserverx
Asked:
xserverx
  • 2
2 Solutions
 
xserverxAuthor Commented:
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

0
 
K VDatabase ConsultantCommented:
Refer:
http://www.mysqlperformanceblog.com/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/

for fragmented tables issue you may use following script:
http://kedar.nitty-witty.com/miscpages/mysql-repair-analyze-optimize-script.html

******** Query cache efficiency: 79.7%  - you may go for incremental increase in Query cache.
post output: show variables like '%qcache%';

Execute: ulimit -n  on your linux shell.

*******    MySQL started within last 24 hours - recommendations may be inaccurate
You better reobserve and repost the results from mysqltuner.pl
0
 
xserverxAuthor Commented:
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

0
 
cjl7Commented:
Hi,

You have slow query-time set to 5 seconds. And you have a lot of slow querys...

You probably need to either:

1. look at the database tables and create indexes
2. or look at the code that uses the database and optimize there

Bad SQL will put any database under high load, no matter what!

You could try to enable log-queries-not-using-indexes for a while to get a list of queries that are the worst performance killers...

//jonas
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now