MySQL load seems very high

shufil · Mar 19, 2014

Hello All ,

Am Shufil , working as a Linux system admin . we are using WHM servers , i regularly watching all forms for finding a solution .
Currently i have a issue . our server running with 4 core cpu and 4 GB Ram , our MySQL DB size 5.6 GB , problem is our MySQL load seems very high , i need to check our my.cnf configure correctly or not . also you can see mysql tuning result .

MySQL tuning scan Result .

Code:

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Increasing the query_cache size over 128M may reduce performance
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 256M) [see warning above]
    join_buffer_size (> 1.0M, or always use indexes with joins)
    table_cache (> 4096)
    innodb_buffer_pool_size (>= 12G)

Code:

[mysqld]
#innodb_force_recovery = 4
old_passwords=1
tmpdir=/home/mysqltmp
datadir=/var/lib/mysql
skip-locking
#skip-networking
#tmp_table_size = 1024M
#max_heap_table_size = 1024M
tmp_table_size = 256M
max_heap_table_size = 256M
query_cache_limit=20M
query_cache_size=128M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=100
max_connections=150
innodb_file_per_table=1

innodb_buffer_pool_size = 1073741824
collation_server=utf8_unicode_ci
character_set_server=utf8

delayed_insert_timeout=40

interactive_timeout=10
wait_timeout=500
connect_timeout=20
thread_cache_size=128
key_buffer=256M ## 32MB for every 1GB of RAM
join_buffer=1M
max_connect_errors=20
max_allowed_packet=64M
table_cache=4096
table_definition_cache=1024
record_buffer=2M
sort_buffer_size=4M ## 1MB for every 1GB of RAM
read_buffer_size=4M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=4M  ## 1MB for every 1GB of RAM
thread_concurrency=4 ## Number of CPUs x 2
myisam_sort_buffer_size=64M
server-id=1

log-slow-queries=/home/mysqltmp/mysql_slow_queries.log 

open_files_limit=36116
[mysql.server]
user=mysql

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

[mysqldump]
quick
max_allowed_packet=32M

[mysql]
no-auto-rehash

Regards,
Shufil
______________________________________________________

cPanelMichael · Mar 19, 2014

You may want to post the full output of the tuner, as opposed to just the recommendations. It might help users to better offer you advice on settings to change.

Thank you.

Archmactrix · Mar 19, 2014

It would be more helpful for other users if you posted the whole output of mysqltuner that is needed so users can assist you in better way.

The output has usually General Statistics, Storage Engine Statistics and Performance Metrics.

edit:

sorry for the reply, didn't know that Michael had replied.

shufil · Mar 23, 2014

cPanelMichael said:
You may want to post the full output of the tuner, as opposed to just the recommendations. It might help users to better offer you advice on settings to change.

Thank you.

Heloo ,

Sorry for delay , below the full scan result .

Code:

 ./mysqltuner.pl

 >>  MySQLTuner 1.0.1 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major[/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.73-cll
[!!] 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: 15G (Tables: 4752)
[--] Data in InnoDB tables: 12G (Tables: 1841)
[--] Data in MEMORY tables: 0B (Tables: 4)
[!!] Total fragmented tables: 138

-------- Performance Metrics -------------------------------------------------
[--] Up for: 17d 6h 14m 35s (98M q [65.728 qps], 1M conn, TX: 246B, RX: 15B)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 1.6G global + 13.2M per thread (150 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 3.6G (89% of installed RAM)
[OK] Slow queries: 0% (8K/98M)
[OK] Highest usage of available connections: 28% (43/150)
[OK] Key buffer size / total MyISAM indexes: 256.0M/620.0M
[OK] Key buffer hit rate: 99.8% (636M cached / 1M reads)
[OK] Query cache efficiency: 55.6% (51M cached / 92M selects)
[!!] Query cache prunes per day: 134269
[OK] Sorts requiring temporary tables: 0% (200 temp sorts / 8M sorts)
[!!] Joins performed without indexes: 29849
[!!] Temporary tables created on disk: 49% (3M on disk / 7M total)
[OK] Thread cache hit rate: 99% (43 created / 1M connections)
[!!] Table cache hit rate: 0% (4K open / 1M opened)
[OK] Open file limit used: 16% (5K/36K)
[OK] Table locks acquired immediately: 99% (58M immediate / 58M locks)
[!!] InnoDB data size / buffer pool: 12.8G/1.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 128M)
    join_buffer_size (> 1.0M, or always use indexes with joins)
    table_cache (> 4096)
    innodb_buffer_pool_size (>= 12G)

Regards,
Shufil

thinkbot · Mar 24, 2014

You got 15GB in MyISAM and 12GB in InnoDB
For MyISAM, key_buffer_size must be higher than 620M if you use all contents indexes, so
[OK] Key buffer size / total MyISAM indexes: 256.0M/620.0M

set key_buffer=650M

For innodb you need to set as much as you can in your case, innodb_buffer_pool_size
but since you don't have RAM, you got options, or you get more RAM, or you compress some of your data, or convert it to MyISAM, and increase key_buffer

and delete those vars below:
record_buffer=2M
sort_buffer_size=4M ## 1MB for every 1GB of RAM
read_buffer_size=4M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=4M ## 1MB for every 1GB of RAM
thread_concurrency=4 ## Number of CPUs x 2

no need to increase them at all

shufil · Mar 24, 2014

thinkbot said:
You got 15GB in MyISAM and 12GB in InnoDB
For MyISAM, key_buffer_size must be higher than 620M if you use all contents indexes, so
[OK] Key buffer size / total MyISAM indexes: 256.0M/620.0M

set key_buffer=650M

For innodb you need to set as much as you can in your case, innodb_buffer_pool_size
but since you don't have RAM, you got options, or you get more RAM, or you compress some of your data, or convert it to MyISAM, and increase key_buffer

and delete those vars below:
record_buffer=2M
sort_buffer_size=4M ## 1MB for every 1GB of RAM
read_buffer_size=4M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=4M ## 1MB for every 1GB of RAM
thread_concurrency=4 ## Number of CPUs x 2

no need to increase them at all

Hello,

Thanks for the reply and support .

Ok, i Will increase key_buffer , but can you advice me how can i compress database , can i get this option in phpmyadmin ?.

Regards,
Shufil

thinkbot · Mar 24, 2014

The thing is,
for fast query execution it's important to have indexes (MyISAM) and data+indexes (Innodb) in memory, RAM

MyISAM places only indexes in RAM, InnoDB both, indexes and data
so if you have only 4GB, it would be better to convert InnoDB databases to MyISAM

when you do that, please rerun mysqltuner.pl and I will suggest adjusted settings

shufil · Mar 25, 2014

thinkbot said:
The thing is,
for fast query execution it's important to have indexes (MyISAM) and data+indexes (Innodb) in memory, RAM

MyISAM places only indexes in RAM, InnoDB both, indexes and data
so if you have only 4GB, it would be better to convert InnoDB databases to MyISAM

when you do that, please rerun mysqltuner.pl and I will suggest adjusted settings

Hello,

Before its running with MyISAM so our site regularly running very slow so we are changed from MyISAM to InnoDB because MyISAM Lock table base, any way after the change site running fine . may the problem is running site without indexes .
If we add 2 GB memory extra , can we expect it will run without any slow

Regards,
Shufil

thinkbot · Mar 25, 2014

Yes, but in this case you should convert to InnoDB only the table which had problems with locking
And previously you might not had properly configured myisam key buffer size, so that might be the problem too

Anyways, since you have too less RAM, your option is to get your data/indexes smaller to fit in RAM, I suggest converting back to MyISAM, increase key buffer size to fit indexes to database
and then do optimize of queries/indexes when necessary
or if needed convert only tables that got locking problems to InnoDB

shufil · Apr 1, 2014

thinkbot said:
Yes, but in this case you should convert to InnoDB only the table which had problems with locking
And previously you might not had properly configured myisam key buffer size, so that might be the problem too

Anyways, since you have too less RAM, your option is to get your data/indexes smaller to fit in RAM, I suggest converting back to MyISAM, increase key buffer size to fit indexes to database
and then do optimize of queries/indexes when necessary
or if needed convert only tables that got locking problems to InnoDB

Hello,

Sorry for late ,
Do you know why the site slow with InnoDB , becouse InnoDB is 5.5 defualt engine in mysql 5.5 right ?.

Regards,
Shufil

thinkbot · Apr 2, 2014

Becouse you got too small buffers to fit InnoDB data/indexes to RAM
[!!] InnoDB data size / buffer pool: 12.8G/1.0G

and you are using 32-bit system
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability

shufil · Apr 4, 2014

thinkbot said:
Becouse you got too small buffers to fit InnoDB data/indexes to RAM
[!!] InnoDB data size / buffer pool: 12.8G/1.0G

and you are using 32-bit system
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability

Hello,

Currently our innodb_buffer_pool_size = 1073741824 , so we need to increase to 1573741824 , is this get any result .

Regards,
Shufil

shufil · Apr 19, 2014

shufil said:
Hello,

Currently our innodb_buffer_pool_size = 1073741824 , so we need to increase to 1573741824 , is this get any result .

Regards,
Shufil

Hello,

Can this increase without increase our primary ram . show process list will give current mysql activity , but how can we know each query how many memory consuming or how many load taken , can we know this any command or using any external tool .

Regards,
Shufil

shufil · Apr 23, 2014

shufil said:
Hello,

Can this increase without increase our primary ram . show process list will give current mysql activity , but how can we know each query how many memory consuming or how many load taken , can we know this any command or using any external tool .

Regards,
Shufil

Hello,

Value changed to innodb_buffer_pool_size = 1573741824 , before mysql running with - Uptime: 4083648 Threads: 5 Questions: 268046639 Slow queries: 26915 Opens: 2908061 Flush tables: 1 Open tables: 4096 Queries per second avg: 65.639

after the change
Uptime: 2585 Threads: 2 Questions: 82706 Slow queries: 2 Opens: 231 Flush tables: 1 Open tables: 224 Queries per second avg: 31.994
This time mysql show process list seems only eximstat .but load seems 31.94 .

Regards,
Shufil

Thread starter	Similar threads	Forum	Replies	Date
M	CPU Load + 200 on mysql	Workarounds and Optimization	1	Apr 8, 2020
S	MySQL CPU load	Workarounds and Optimization	4	Nov 22, 2019
W	Problem with MYSQL Load CPU	Workarounds and Optimization	1	Nov 5, 2019
J	MySQL 200% CPU Load	Workarounds and Optimization	4	Jan 6, 2019
	Mysql overload problem	Workarounds and Optimization	6	Nov 20, 2016

Search

Search

MySQL load seems very high

shufil

Well-Known Member

cPanelMichael

Administrator

Archmactrix

Well-Known Member

shufil

Well-Known Member

thinkbot

Well-Known Member

shufil

Well-Known Member

thinkbot

Well-Known Member

shufil

Well-Known Member

thinkbot

Well-Known Member

shufil

Well-Known Member

thinkbot

Well-Known Member

shufil

Well-Known Member

shufil

Well-Known Member

shufil

Well-Known Member