MYSQL high RAM usage

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

 

Please find below mysqltuner.sh result , kindly advice what is best tunning for etc/my.cnf

Code:

>>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  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 '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)

 

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 '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[/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)