?
Solved

server load high

Posted on 2009-04-17
6
Medium Priority
?
715 Views
Last Modified: 2012-05-06
hello there,
I would like to know what I can I do make the mysql server load stay low..
sometimes the server load its above 8.00 sometimes stays at 1.00..
I have this settings in my my.cnf what can I do to make it stable?
[mysqld]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
datadir=/var/lib/mysql
skip-locking
skip-innodb
old-passwords = 1
key_buffer = 256M
key_buffer_size = 64M
table_cache = 2048
sort_buffer_size = 2M
read_buffer_size=2M
read_rnd_buffer_size = 4M
join_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 128M
query_cache_limit = 2M
thread_concurrency = 8
thread_cache_size = 8
max_connections = 500
max_connect_errors=1000 
log-slow-queries=/var/log/mysqld/slow.log
max_heap_table_size=800M
wait_timeout = 1800
connect_timeout = 10
interactive_timeout = 120
 
[mysql.server] 
user=mysql
basedir=/var/lib 
 
[safe_mysqld] 
err-log=/var/log/mysqld/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 = 256M
sort_buffer_size = 32M
read_buffer = 2M
write_buffer = 2M
 
[myisamchk]
key_buffer = 256M
sort_buffer_size = 32M
read_buffer = 2M
write_buffer = 2M
 
[mysqlhotcopy] 
interactive-timeout
 
[client]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock

Open in new window

0
Comment
Question by:XK8ER
  • 3
  • 3
6 Comments
 
LVL 21

Expert Comment

by:K V
ID: 24173712
Server Information 'll be useful, pls provide.

Also provide output of following commands:-
- status;
- show status like '%qcache%'
- show status like 'key_read%';
- show variables like '%buffer_size';

Checkout following:
** http://hackmysql.com/mysqlreport
** http://wiki.mysqltuner.com/MySQLTuner
0
 
LVL 1

Author Comment

by:XK8ER
ID: 24174315
I ran the command lines and mysqltuner script and this is what I got
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.45-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: 2G (Tables: 916)
[--] Data in MEMORY tables: 5M (Tables: 6)
[!!] Total fragmented tables: 66
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 426d 10h 12m 49s (1M q [0.043 qps], 29K conn, TX: 4B, RX: 342M)
[--] Reads / Writes: 96% / 4%
[--] Total buffers: 234.0M global + 9.2M per thread (500 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 4.7G (135% of installed RAM)
[OK] Slow queries: 0% (152/1M)
[OK] Highest usage of available connections: 3% (18/500)
[OK] Key buffer size / total MyISAM indexes: 64.0M/1.3G
[OK] Key buffer hit rate: 100.0% (201M cached / 84K reads)
[!!] Query cache efficiency: 9.5% (140K cached / 1M selects)
[!!] Query cache prunes per day: 2627
[OK] Sorts requiring temporary tables: 1% (24K temp sorts / 1M sorts)
[!!] Temporary tables created on disk: 46% (11K on disk / 23K total)
[OK] Thread cache hit rate: 99% (63 created / 29K connections)
[OK] Table cache hit rate: 69% (469 open / 676 opened)
[OK] Open file limit used: 18% (838/4K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
 
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_limit (> 2M, or use smaller result sets)
    query_cache_size (> 128M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 800M)
 
 
mysql> status;
--------------
mysql  Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (i686) using readline 5.0
 
Connection id:          300
Current database:       db_forum
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.45-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
Uptime:                 5 min 12 sec
 
Threads: 2  Questions: 5852  Slow queries: 17  Opens: 993  Flush tables: 1  Open tables: 984  Queries per second avg: 18.756
 
 
 
 
mysql> show status like '%qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 119       |
| Qcache_free_memory      | 832432312 |
| Qcache_hits             | 2226      |
| Qcache_inserts          | 1691      |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 981       |
| Qcache_queries_in_cache | 817       |
| Qcache_total_blocks     | 2041      |
+-------------------------+-----------+
8 rows in set (0.00 sec)
 
 
mysql> show status like 'key_read%';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Key_read_requests | 3314810 |
| Key_reads         | 17993   |
+-------------------+---------+
2 rows in set (0.00 sec)
 
 
mysql> show variables like '%buffer_size';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| bdb_log_buffer_size     | 0        |
| bulk_insert_buffer_size | 8388608  |
| innodb_log_buffer_size  | 1048576  |
| join_buffer_size        | 1044480  |
| key_buffer_size         | 67108864 |
| myisam_sort_buffer_size | 67108864 |
| preload_buffer_size     | 32768    |
| read_buffer_size        | 2093056  |
| read_rnd_buffer_size    | 4190208  |
| sort_buffer_size        | 2097144  |
+-------------------------+----------+
10 rows in set (0.00 sec)

Open in new window

0
 
LVL 1

Author Comment

by:XK8ER
ID: 24174340
these are the mysql results using the script mysqlreport
MySQL 5.0.45-log         uptime 0 0:16:2        Sat Apr 18 02:57:01 2009
 
__ Key _________________________________________________________________
Buffer used    21.74M of  64.00M  %Used:  33.96
  Current      29.11M            %Usage:  45.48
Write hit      55.00%
Read hit       99.74%
 
__ Questions ___________________________________________________________
Total          15.37k    16.0/s
  DMS           6.28k     6.5/s  %Total:  40.88
  QC Hits       5.25k     5.5/s           34.17
  Com_          1.96k     2.0/s           12.72
  COM_QUIT      1.41k     1.5/s            9.17
  +Unknown        471     0.5/s            3.06
Slow 10 s          69     0.1/s            0.45  %DMS:   1.10  Log:  ON
DMS             6.28k     6.5/s           40.88
  SELECT        4.78k     5.0/s           31.11         76.10
  UPDATE          957     1.0/s            6.23         15.23
  INSERT          490     0.5/s            3.19          7.80
  DELETE           31     0.0/s            0.20          0.49
  REPLACE          24     0.0/s            0.16          0.38
Com_            1.96k     2.0/s           12.72
  change_db     1.73k     1.8/s           11.28
  show_tables      77     0.1/s            0.50
  set_option       45     0.0/s            0.29
 
__ SELECT and Sort _____________________________________________________
Scan            2.02k     2.1/s %SELECT:  42.14
Range             555     0.6/s           11.61
Full join           0       0/s            0.00
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan         854     0.9/s
Sort range      1.08k     1.1/s
Sort mrg pass   1.30k     1.4/s
 
__ Query Cache _________________________________________________________
Memory usage    6.31M of 800.00M  %Used:   0.79
Block Fragmnt  10.99%
Hits            5.25k     5.5/s
Inserts         3.40k     3.5/s
Insrt:Prune   3.40k:1     3.5/s
Hit:Insert     1.55:1                 
 
__ Table Locks _________________________________________________________
Waited             42     0.0/s  %Total:   0.36
Immediate      11.77k    12.2/s
 
__ Tables ______________________________________________________________
Open             1040 of 2048    %Cache:  50.78
Opened          1.05k     1.1/s
 
__ Connections _________________________________________________________
Max used           41 of  500      %Max:   8.20
Total           1.41k     1.5/s
 
__ Created Temp ________________________________________________________
Disk table        727     0.8/s
Table           1.47k     1.5/s    Size: 800.0M
File              876     0.9/s
 
__ Threads _____________________________________________________________
Running             2 of    2
Cached              8 of    8      %Hit:  92.35
Created           108     0.1/s
Slow                0       0/s
 
__ Aborted _____________________________________________________________
Clients             2     0.0/s
Connects            2     0.0/s
 
__ Bytes _______________________________________________________________
Sent          310.18M  322.4k/s
Received        3.50M    3.6k/s
 
__ InnoDB Buffer Pool __________________________________________________
Usage               0 of       0  %Used:   0.00
Read hit        0.00%
Pages
  Free              0            %Total:   0.00
  Data              0                      0.00 %Drty:   0.00
  Misc              0                      0.00
  Latched           0                      0.00
Reads               0       0/s
  From file         0       0/s            0.00
  Ahead Rnd         0       0/s
  Ahead Sql         0       0/s
Writes              0       0/s
Flushes             0       0/s
Wait Free           0       0/s 
 
__ InnoDB Lock _________________________________________________________
Waits               0       0/s
Current             0
Time acquiring
  Total             0 ms
  Average           0 ms
  Max               0 ms
 
__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads             0       0/s
  Writes            0       0/s
  fsync             0       0/s
  Pending
    Reads           0
    Writes          0
    fsync           0
 
Pages
  Created           0       0/s
  Read              0       0/s
  Written           0       0/s
 
Rows
  Deleted           0       0/s
  Inserted          0       0/s
  Read              0       0/s
  Updated           0       0/s            

Open in new window

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 1

Author Comment

by:XK8ER
ID: 24174422
I have updated my.cnf and im getting different results now


-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.45-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: 2G (Tables: 916)
[--] Data in MEMORY tables: 3M (Tables: 6)
[!!] Total fragmented tables: 66
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 13m 14s (14K q [17.822 qps], 1K conn, TX: 243M, RX: 2M)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 1.6G global + 67.2M per thread (500 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 34.4G (994% of installed RAM)
[OK] Slow queries: 0% (2/14K)
[OK] Highest usage of available connections: 6% (32/500)
[OK] Key buffer size / total MyISAM indexes: 64.0M/1.3G
[OK] Key buffer hit rate: 99.7% (5M cached / 18K reads)
[OK] Query cache efficiency: 43.5% (3K cached / 8K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 5% (117 temp sorts / 2K sorts)
[!!] Temporary tables created on disk: 46% (1K on disk / 2K total)
[OK] Thread cache hit rate: 95% (80 created / 1K connections)
[OK] Table cache hit rate: 98% (987 open / 997 opened)
[OK] Open file limit used: 41% (1K/4K)
[OK] Table locks acquired immediately: 99% (10K immediate / 10K locks)
 
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
 
 
[mysqld]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
datadir=/var/lib/mysql
skip-locking
skip-innodb
old-passwords = 1
key_buffer = 256M
key_buffer_size = 64M
table_cache = 2048
sort_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 32M
join_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 800M
query_cache_limit = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
thread_cache_size = 8
# log-bin
# server-id=1
max_connections = 500
#Increase max_connect_errors from 10 to 1000
max_connect_errors=1000
 
#this is to log and find out slow query that slow down the server
#log-queries-not-using-indexes
#long_query_time = 5
log-slow-queries=/var/log/mysqld/slow.log
 
#start added on Jan 30, 2009
#record_buffer=1M
#net_buffer_length=16K
#bulk_insert_buffer_size=1M
#thread_cache=20
max_heap_table_size=800M
tmp_table_size=800M
wait_timeout = 1800
#ended added on Jan 30, 2009
 
connect_timeout = 10
interactive_timeout = 120
 
[mysql.server]
user=mysql
basedir=/var/lib
 
[safe_mysqld]
err-log=/var/log/mysqld/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 = 256M
sort_buffer_size = 32M
read_buffer = 2M
write_buffer = 2M
 
[myisamchk]
key_buffer = 256M
sort_buffer_size = 32M
read_buffer = 2M
write_buffer = 2M
 
[mysqlhotcopy]
interactive-timeout
 
[client]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock

Open in new window

0
 
LVL 21

Expert Comment

by:K V
ID: 24174677
Hi, tuner suggest you to better improvements.
Consider increasing:
thread_cache_size
Efficiency = 100 - ((Threads_create / Connections) * 100))

Keep variables under observation and how they perform when server is having highest usage.
As suggested in report itself:
"    MySQL started within last 24 hours - recommendations may be inaccurate"
0
 
LVL 21

Accepted Solution

by:
K V earned 2000 total points
ID: 24182004
Observe following output:
shell> ulimit -n # will give you filesystem limit of number of open file descriptors
mysql> show variables like 'open_files_limit'

Now read:-
Keeping most of the database tables open can be expensive. The optimum value for the table_cache parameter is directly related to the number of tables that need to be open simultaneously in order to perform multiple-table joins. The table_cache value should be equal to no less than the number of concurrent connections times the largest number of tables involved in any one join. Typically, 1,024 is good value for applications with a couple of hundred tables. Each connection has its own entry. Check the Open_tables status variable to see if it is large compared to the table_cache setting.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question