We are having a problem with one of the database server of one application, possibly caused by some code that is creating a problem in the way Mysql manage it's memory.
Until the second week of April, our db server has a stable consumption of memory of about 5 gigs (with a maximum of 7 gigs). But then, it started to increase limitless, even surpassing it's theorically maximum possible allocation.
This is our yearly munin graph showing the increase in the last 2 months:
This is another view from the last seven days after a restart in mysql:
This is the report created by mysqltuner.pl:
-------- Performance Metrics ------------------------------------------------- [--] Up for: 4d 1h 56m 28s (152M q [431.585 qps], 383K conn, TX: 593B, RX: 29B) [--] Reads / Writes: 90% / 10% [--] Total buffers: 5.3G global + 10.2M per thread (200 max threads) [OK] Maximum possible memory usage: 7.3G (46% of installed RAM) [OK] Slow queries: 0% (2K/152M) [OK] Highest usage of available connections: 13% (26/200) [OK] Key buffer size / total MyISAM indexes: 16.0M/300.0K [OK] Key buffer hit rate: 100.0% (61M cached / 9 reads) [OK] Query cache efficiency: 70.8% (103M cached / 146M selects) [!!] Query cache prunes per day: 501819 [OK] Sorts requiring temporary tables: 0% (926 temp sorts / 3M sorts) [!!] Joins performed without indexes: 39128 [OK] Temporary tables created on disk: 16% (821K on disk / 5M total) [OK] Thread cache hit rate: 99% (26 created / 383K connections) [!!] Table cache hit rate: 10% (845 open / 7K opened) [OK] Open file limit used: 3% (148/4K) [OK] Table locks acquired immediately: 99% (65M immediate / 65M locks) [!!] InnoDB data size / buffer pool: 5.5G/5.0G
We are in unknown territory here. Any help will be appreciated!
Edit: Adding my.cnf
# The MySQL database server configuration file. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] character_set_server = utf8 collation_server = utf8_general_ci user = mysql socket = /var/run/mysqld/mysqld.sock pid-file = /var/run/mysqld/mysqld.pid port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking bind-address = 0.0.0.0 # Fine Tuning max_connections = 200 key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K join_buffer_size = 2M sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 4M thread_cache_size = 128 thread_concurrency = 24 table_cache = 2K table_open_cache = 2K table_definition_cache = 4K # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP # innodb innodb_buffer_pool_size = 5G innodb_flush_log_at_trx_commit = 1 innodb_support_xa = 1 innodb_additional_mem_pool_size = 32M innodb_log_buffer_size = 8M innodb_flush_method = O_DIRECT # Query Cache Configuration query_cache_limit = 32M query_cache_size = 256M query_cache_min_res_unit = 256 # Logging and Replication log_error = /var/log/mysql/error.log log-slow-queries = /var/log/mysql/slow.log long_query_time = 1 # REPLICATION CONFIGURATION log_bin = /var/log/mysql/mysql-bin.log log-bin = mysql-bin expire_logs_days = 15 sync_binlog = 1 server-id = 1 ssl-ca =/etc/ssl/private/repl/cacert.pem ssl-cert =/etc/ssl/private/repl/master-cert.pem ssl-key =/etc/ssl/private/repl/master-key.pem [mysqldump] quick quote-names max_allowed_packet = 16M [isamchk] key_buffer = 16M