Skip to main content

MySQL load seems very high

shufil
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 . -------- 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)
[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 ______________________________________________________

Comments

13 comments

Date Votes
  • cPanelMichael
    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.
    0
  • Archmactrix
    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.
    0
  • shufil
    [quote="cPanelMichael, post: 1599742">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 . ./mysqltuner.pl >> MySQLTuner 1.0.1 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> 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
    0
  • thinkbot
    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
    0
  • shufil
    [quote="thinkbot, post: 1603492">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
    0
  • thinkbot
    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
    0
  • shufil
    [quote="thinkbot, post: 1603631">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
    0
  • thinkbot
    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
    0
  • shufil
    [quote="thinkbot, post: 1604392">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
    0
  • thinkbot
    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
    0
  • shufil
    [quote="thinkbot, post: 1610301">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
    0
  • shufil
    [quote="shufil, post: 1612422">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
    0
  • shufil
    [quote="shufil, post: 1625111">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
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post