Skip to main content

MYSQL high RAM usage

abdelhost77
Hello, i would like please your suggestions to decrease Mysql RAM consumption, actually it take more than 35% and still increasing day after day . Intel(R) Core(TM) i5 CPU 760 @ 2.80GHz 4 CPU 8G RAM CENTOS 5.* Around 200 small sites with CMS ( WP, Joomla ... ) Less than 5000 visitors per day . Here is my /etc/my.cnf
[mysqld] skip-name-resolve myisam_use_mmap=1 max_connections=100 max_user_connections = 50 log-slow-queries=mysql-slow.log long_query_time = 1 safe-show-database query_cache_type = 1 query_cache_size = 75M query_cache_limit = 2M join_buffer_size=1M read_rnd_buffer_size=1M max_allowed_packet=268435456 tmp_table_size=25M max_heap_table_size=25M table_open_cache = 7500 thread_cache_size = 25 open_files_limit=50000 key_buffer_size = 500M myisam_sort_buffer_size = 256M innodb_file_per_table=1 innodb_buffer_pool_size = 1G innodb_log_file_size = 64M innodb_log_buffer_size = 8M

Comments

4 comments

Date Votes
  • cPanelMichael
    Hello :) Please see the following thread for information on how to use a MySQL tuner script: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you.
    0
  • abdelhost77
    Please find below mysqltuner.sh result, kindly advice what is best tunning for etc/my.cnf
    >> MySQLTuner 1.3.0 - 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 [OK] Currently running supported MySQL version 5.1.73-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 796M (Tables: 18419) [--] Data in InnoDB tables: 474M (Tables: 12934) [--] Data in MEMORY tables: 0B (Tables: 289) [!!] Total fragmented tables: 129 -------- Security Recommendations ------------------------------------------- [!!] User 'xxxx@ip' has no password set. [!!] User 'yyyy@ip' has no password set. -------- Performance Metrics ------------------------------------------------- [--] Up for: 15m 37s (152K q [162.334 qps], 831 conn, TX: 108M, RX: 11M) [--] Reads / Writes: 84% / 16% [--] Total buffers: 1.6G global + 4.4M per thread (100 max threads) [OK] Maximum possible memory usage: 2.0G (26% of installed RAM) [OK] Slow queries: 0% (0/152K) [OK] Highest usage of available connections: 7% (7/100) [OK] Key buffer size / total MyISAM indexes: 500.0M/249.4M [OK] Key buffer hit rate: 99.6% (997K cached / 3K reads) [OK] Query cache efficiency: 78.0% (51K cached / 65K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3K sorts) [!!] Joins performed without indexes: 560 [!!] Temporary tables created on disk: 28% (858 on disk / 2K total) [OK] Thread cache hit rate: 99% (7 created / 831 connections) [OK] Table cache hit rate: 96% (901 open / 936 opened) [OK] Open file limit used: 2% (1K/50K) [OK] Table locks acquired immediately: 99% (21K immediate / 21K locks) [OK] InnoDB buffer pool / data size: 1.0G/474.8M [OK] InnoDB log waits: 0 -------- 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 When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: join_buffer_size (> 1.0M, or always use indexes with joins) tmp_table_size (> 25M) max_heap_table_size (> 25M)
    0
  • cPanelMichael
    Please ensure you let MySQL run for at least 24 hours to ensure you receive the most accurate results. Thank you.
    0
  • abdelhost77
    Hello, here after mysqltuner.sh result after 24 hours Mysql Runing, kindly suggest how to modify /etc/my.cnf, in order to reduce Mysql memory consumption ( actually Mysql is eating more than 40% memory ) .
    >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> Run with '--help' for additional options and output filtering [OK] Currently running supported MySQL version 5.1.73-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 806M (Tables: 18518) [--] Data in InnoDB tables: 475M (Tables: 12974) [--] Data in MEMORY tables: 248K (Tables: 289) [!!] Total fragmented tables: 174 -------- Security Recommendations ------------------------------------------- [!!] User 'xxxx@ip' has no password set. [!!] User 'yyyy@ip' has no password set. -------- Performance Metrics ------------------------------------------------- [--] Up for: 23h 21m 10s (9M q [113.480 qps], 88K conn, TX: 13B, RX: 950M) [--] Reads / Writes: 81% / 19% [--] Total buffers: 1.6G global + 4.4M per thread (100 max threads) [OK] Maximum possible memory usage: 2.0G (26% of installed RAM) [OK] Slow queries: 0% (142/9M) [OK] Highest usage of available connections: 27% (27/100) [OK] Key buffer size / total MyISAM indexes: 500.0M/257.0M [OK] Key buffer hit rate: 99.8% (44M cached / 71K reads) [OK] Query cache efficiency: 79.5% (3M cached / 4M selects) [!!] Query cache prunes per day: 283137 [OK] Sorts requiring temporary tables: 0% (28 temp sorts / 215K sorts) [!!] Joins performed without indexes: 32087 [!!] Temporary tables created on disk: 33% (168K on disk / 504K total) [OK] Thread cache hit rate: 99% (27 created / 88K connections) [!!] Table cache hit rate: 3% (7K open / 241K opened) [OK] Open file limit used: 8% (4K/50K) [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks) [OK] InnoDB buffer pool / data size: 1.0G/475.6M [OK] InnoDB log waits: 0 -------- 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 When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: [url=http://bit.ly/1mi7c4C]table_cache negative scalability - MySQL Performance Blog Variables to adjust: query_cache_size (> 75M) join_buffer_size (> 1.0M, or always use indexes with joins) tmp_table_size (> 25M) max_heap_table_size (> 25M) table_open_cache (> 7500)
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post