Question

how to fix mysql high memory usage?

Hi,

I used mysqltuner to check the problem related to frequent crashes of my database. I received this warning

*** MySQL’s maximum memory usage is dangerously high ***

Here’s the result of mysqltuner:

code

`>> MySQLTuner 1.4.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 Please enter your MySQL administrative login: Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.5.42-log [OK] Operating on 64-bit architecture

-------- Storage Engine Statistics ------------------------------------------- [–] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [–] Data in MyISAM tables: 51M (Tables: 3) [–] Data in InnoDB tables: 653M (Tables: 233) [–] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [–] Data in MEMORY tables: 372K (Tables: 5) [!!] Total fragmented tables: 34

-------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned

-------- Performance Metrics ------------------------------------------------- [–] Up for: 34m 50s (46K q [22.025 qps], 3K conn, TX: 3B, RX: 18M) [–] Reads / Writes: 86% / 14% [–] Total buffers: 672.0M global + 2.8M per thread (500 max threads) [!!] Maximum possible memory usage: 2.0G (205% of installed RAM) [OK] Slow queries: 4% (1K/46K) [OK] Highest usage of available connections: 1% (6/500) [OK] Key buffer size / total MyISAM indexes: 32.0M/51.3M [OK] Key buffer hit rate: 98.8% (55K cached / 675 reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts) [!!] Joins performed without indexes: 8 [OK] Temporary tables created on disk: 22% (103 on disk / 455 total) [OK] Thread cache hit rate: 99% (6 created / 3K connections) [OK] Table cache hit rate: 33% (142 open / 420 opened) [OK] Open file limit used: 0% (56/65K) [OK] Table locks acquired immediately: 99% (70K immediate / 70K locks) [!!] InnoDB buffer pool / data size: 592.0M/653.4M [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 Reduce your overall MySQL memory footprint for system stability Adjust your join queries to always utilize indexes Variables to adjust: *** MySQL’s maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (>= 8M) join_buffer_size (> 128.0K, or always use indexes with joins) innodb_buffer_pool_size (>= 653M)


And here's my my.cnf config.

Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208

Configuration name server generated for …

[mysql]

CLIENT

port = 3306 socket = /var/lib/mysql/mysql.sock

[mysqld]

GENERAL

user = mysql default-storage-engine = InnoDB socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid performance_schema = off

MyISAM

key-buffer-size = 10M myisam-recover = FORCE,BACKUP

SAFETY

max-allowed-packet = 10M max-connect-errors = 1000000 skip-name-resolve sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY sysdate-is-now = 1 innodb = FORCE

DATA STORAGE

datadir = /var/lib/mysql/

BINARY LOGGING

log-bin = /var/lib/mysql/mysql-bin expire-logs-days = 7 sync-binlog = 1

CACHES AND LIMITS

tmp-table-size = 20M max-heap-table-size = 32M query-cache-type = 1 query-cache-size = 32M max-connections = 50 thread-cache-size = 50 open-files-limit = 1500 table-definition-cache = 4096 table-open-cache = 4096

INNODB

innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 80M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 700M

LOGGING

log-error = /var/lib/mysql/mysql-error.log log-queries-not-using-indexes = 1 slow-query-log = 1 slow-query-log-file = /var/lib/mysql/mysql-slow.log


Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in Q&A, subscribe to topics of interest, and get courses and tools that will help you grow as a developer and scale your project or business.

Use this tool to create a reliable config file, be honest with the answers to get better results, if you don’t know, research each question.

https://tools.percona.com/wizard