MYSQL high RAM usage

A

abdelhost77

Well-Known Member
Apr 25, 2012
116
2
68
Morocco
cPanel Access Level
Root Administrator
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





Code: 
[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
 
A

abdelhost77

Well-Known Member
Apr 25, 2012
116
2
68
Morocco
cPanel Access Level
Root Administrator
Please find below mysqltuner.sh result , kindly advice what is best tunning for etc/my.cnf



Code: 
>>  MySQLTuner 1.3.0 - 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
[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 '[email protected]' has no password set.
[!!] User '[email protected]' 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)
 
cPanelMichael

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,883
2,256
463
Please ensure you let MySQL run for at least 24 hours to ensure you receive the most accurate results.

Thank you.
 
A

abdelhost77

Well-Known Member
Apr 25, 2012
116
2
68
Morocco
cPanel Access Level
Root Administrator
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 ) .



Code: 
>>  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
[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 '[email protected]' has no password set.
[!!] User '[email protected]' 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[/url]
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)
 
You must log in or register to reply here.
Thread starter Similar threads Forum Replies Date
R Mysqld - high CPU usage Workarounds and Optimization 4
T High MySQL usage - how to troubleshoot? Workarounds and Optimization 2
M MYSQL high cpu usage issue Workarounds and Optimization 1
B MySQL Very High CPU Usage issue Workarounds and Optimization 1
C Higher MySQL RAM usage, need to optimize Workarounds and Optimization 32
Similar threads
Mysqld - high CPU usage
High MySQL usage - how to troubleshoot?
MYSQL high cpu usage issue
MySQL Very High CPU Usage issue
Higher MySQL RAM usage, need to optimize
Top Bottom