MySQL Ram Usage & Improve it with mysqltuner

Question

how can i improve my server ram usage: Server Ram = 1GB after install the mysqltuner , I have this result :

 [root@server ~]# perl mysqltuner.pl
 >>  MySQLTuner 1.5.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.44
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 18M (Tables: 185)
[--] Data in InnoDB tables: 19M (Tables: 271)
[--] Data in MEMORY tables: 0B (Tables: 5)
[!!] Total fragmented tables: 22

-------- Security Recommendations  -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[!!] User 'admin_jmu@%' hasn't specific host restriction.
[!!] User 'admin_mcstd@%' hasn't specific host restriction.
[!!] User 'admin_melody@%' hasn't specific host restriction.
[!!] There is not basic password file list !

-------- Performance Metrics -------------------------------------------------
[--] Up for: 9h 21m 32s (322K q [9.576 qps], 68K conn, TX: 663M, RX: 16M)
[--] Reads / Writes: 87% / 13%
[--] Binary logging is disabled
[--] Total buffers: 208.0M global + 4.5M per thread (400 max threads)
[OK] Maximum reached memory usage: 244.0M (24.49% of installed RAM)
[!!] Maximum possible memory usage: 2.0G (201.52% of installed RAM)
[OK] Slow queries: 0% (1/322K)
[OK] Highest usage of available connections: 2% (8/400)
[OK] Aborted connections: 0.00%  (3/68352)
[!!] Key buffer used: 19.1% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/959.0K
[OK] Read Key buffer hit rate: 99.8% (166K cached / 273 reads)
[OK] Write Key buffer hit rate: 99.2% (237 cached / 2 writes)
[OK] Query cache efficiency: 91.1% (96K cached / 105K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Temporary tables created on disk: 60% (765 on disk / 1K total)
[OK] Thread cache hit rate: 99% (8 created / 68K connections)
[OK] Table cache hit rate: 98% (351 open / 358 opened)
[OK] Open file limit used: 3% (320/8K)
[OK] Table locks acquired immediately: 99% (10K immediate / 10K locks)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/19.2M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 20.19% (1654 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 99.96% (4013636 hits/ 4015282 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 356 writes)

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)

and my.cnf file have this content :

[mysqld]
max_connections = 400
key_buffer = 32M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M


[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

local-infile=0

How Can I change my.cnf ? Thanks

First, as the script itself even says, don't try to run it if MySQL hasn't been running for at least 24 hours. Second, why not follow its own advice? For example, MySQL's max memory is "dangerously high" - you need to lower the amount of memory you're using. Third, tell us your hardware & server specs (how much memory does your server have, is it a dedicated server for MySQL or is it running other services, etc...) — David W, Aug 10 '15 at 08:43
Dear David, I'm begginer and i don't know how to action to this advice :*** MySQL's maximum memory usage is dangerously high *** — Ashina, Aug 10 '15 at 09:00
Server 1GB Ram , 1GB Swap, 1Core CPU 3.2, CentOS 6.5 , anddddddd. mysql usage 985mb!!! — Ashina, Aug 10 '15 at 09:07

score 1 · Accepted Answer · answered Aug 10 '15 at 09:12

You can see your main problem here:

[--] Total buffers: 208.0M global + 4.5M per thread (400 max threads)

As per your configuration, every thread can take up to 4.5 MB RAM and there's a maximum of 400 threads:

max_connections = 400

Put your max_connections down to 100 or below to lower your maximum memory usage.

score 0 · Answer 2 · answered Aug 10 '15 at 08:43

What are you trying to achieve?

Where SQL is concerned you should give it as much RAM as you can, it will use it for caching indexes, and in the case of InnoDB, the data pages as well which means better performance all round and less read from disk for repeat queries.

I am not 100% sure on this but I think the MyISAM engine only caches indexes to memory, in which case InnoDB is usually the better choice if you want to get a fast MySQL database going.

MySQL Ram Usage & Improve it with mysqltuner

2 Answers2