MySQL Optimization – Maximum Possible Memory Usage Over 100%

memoryMySQLoptimization

During these days I experienced several problems (delay and failed restart) on my database MySQL, so I tried with some help here around (and using Percona service too) to optimize my /etc/mysql/my.cnf file.

I'm running an Amazon t2.micro instance with 1 CPU and 1 GB of RAM with OS Ubuntu 14.04

# ./mysqltuner.pl

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.47-0ubuntu0.14.04.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 269M (Tables: 1078)
[--] Data in MyISAM tables: 403M (Tables: 218)
[!!] Total fragmented tables: 428

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 605 basic passwords in the list.

-------- CVE Security Recommendations  ---------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 17m 57s (23K q [5.012 qps], 2K conn, TX: 60M, RX: 1M)
[--] Reads / Writes: 85% / 15%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 1.1G global + 3.5M per thread (70 max threads)
[!!] Maximum reached memory usage: 1.1G (117.33% of installed RAM)
[!!] Maximum possible memory usage: 1.4G (140.96% of installed RAM)
[OK] Slow queries: 0% (2/23K)
[OK] Highest usage of available connections: 4% (3/70)
[OK] Aborted connections: 0.04%  (1/2280)
[OK] Query cache efficiency: 65.5% (10K cached / 15K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 273 sorts)
[OK] Temporary tables created on disk: 3% (61 on disk / 2K total)
[OK] Thread cache hit rate: 99% (3 created / 2K connections)
[OK] Table cache hit rate: 53% (1K open / 1K opened)
[OK] Open file limit used: 35% (733/2K)
[OK] Table locks acquired immediately: 100% (7K immediate / 7K locks)
[OK] Binlog cache memory access: 100.00% ( 107 Memory / 107 Total)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 20.2% (52M used / 262M cache)
[OK] Key buffer size / total MyISAM indexes: 250.0M/73.4M
[!!] Read Key buffer hit rate: 86.1% (36K cached / 5K reads)
[!!] Write Key buffer hit rate: 0.8% (2K cached / 2K writes)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 592.0M/269.2M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 21.69% (8219 used/ 37887 total)
[OK] InnoDB Read buffer efficiency: 99.99% (96339383 hits/ 96346396 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 219 writes)

-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.

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

This is my /etc/mysql/my.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
# GENERAL #
user            = mysql
default_storage_engine         = InnoDB
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

# MyISAM #
key_buffer_size                = 250M
myisam_recover                 = FORCE,BACKUP

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log_bin                        = /var/lib/mysql/mysql-bin
expire_logs_days               = 14

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 100M
query_cache_type               = 1
query_cache_size               = 16M
query_cache_limit = 10M
thread_cache_size              = 50
open_files_limit               = 1535
table_definition_cache         = 4096
table_open_cache               = 1000
max_allowed_packet      = 16M
thread_cache_size       = 50

sort_buffer_size = 1M
join_buffer_size = 1M
read_buffer_size = 1M
max_connections = 70

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 64M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 592M
innodb_buffer_pool_instances = 1
innodb_log_buffer_size = 256M
innodb_read_io_threads = 16
innodb_write_io_threads = 16

# LOGGING #
log_error = /var/log/mysql/error.log
log_slow_queries        = /var/log/mysql/mysql-slow.log
log_warnings = 0
long_query_time = 10

expire_logs_days        = 10
max_binlog_size         = 100M
binlog_format = MIXED

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/

This is the screenshot running command top (the process mysqld uses 60% of RAM)

And buffer related settings

mysql> SHOW VARIABLES LIKE '%buffer%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| bulk_insert_buffer_size      | 8388608   |
| innodb_buffer_pool_instances | 1         |
| innodb_buffer_pool_size      | 620756992 |
| innodb_change_buffering      | all       |
| innodb_log_buffer_size       | 268435456 |
| join_buffer_size             | 1048576   |
| key_buffer_size              | 262144000 |
| myisam_sort_buffer_size      | 8388608   |
| net_buffer_length            | 16384     |
| preload_buffer_size          | 32768     |
| read_buffer_size             | 1048576   |
| read_rnd_buffer_size         | 262144    |
| sort_buffer_size             | 1048576   |
| sql_buffer_result            | OFF       |
+------------------------------+-----------+
14 rows in set (0.00 sec)

I'm not a MySQL expert user, what's wrong with my configuration?

Best Answer

Your key_buffer, innodb_buffer_pool and innodb_log_buffer together already exhausts the available memory in a a t2 instance.

If you also consider the per thread allocations (>3M) * 70 (max connections) the possible usage is growing by another 210M minimum.

You have 270MB InnoDb data. If you don't expect it to grow significantly over time almost 600MB buffer pool is overkill. Make it somewhere around 300MB or if you expect it to grow then adjust with care.

The 256MB innodb_log_buffer is completely superfluous. It's in the same order of magnitude as your data. You're never going to write that much that needs to be buffered. Increase your innodb_log_file_size rather if you are maintaining a write heavy operation.

Key_cache_size is also way overprovisioned. You don't need more than what the size of your myisam indexes.

Drop your per thread buffers and improve as it is necessary.

Without seeing the usage hard to say exact numbers but I would start with something like this:

key_buffer_size = 64M
innodb_buffer_pool_size = 384M
innodb_log_buffer_size = 4M

join_buffer_size = 128k
# Increase if you have a lot of joins without indexes
sort_buffer_size = 256k
# increase if you have a lot of sorts without indexes 
read_buffer_size = 128k
read_rnd_buffer_size = 128k

Always test the config parameters and see what works best for your workload. Most of them are dynamic so easy too change in runtime, test, analyze and repeat until you find the best.

Related Solutions

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

...even surpassing it's theorically maximum possible allocation.

[OK] Maximum possible memory usage: 7.3G (46% of installed RAM)

There is not actually a way to calculate maximum possible memory usage for MySQL, because there is no cap on the memory it can request from the system.

The calculation done by mysqltuner.pl is only an estimate, based on a formula that doesn't take into account all possible variables, because if all possible variables were taken into account, the answer would always be "infinite." It's unfortunate that it's labeled this way.

Here is my theory on what's contributing to your excessive memory usage:

thread_cache_size       = 128

Given that your max_connections is set to 200, the value of 128 for thread_cache_size seems far too high. Here's what makes me think this might be contributing to your problem:

When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.

^{http://dev.mysql.com/doc/refman/5.6/en/memory-use.html}

If your workload causes even an occasional client thread to require a large amount of memory, those threads may be holding onto that memory, then going back to the pool and sitting around, continuing to hold on to memory they don't technically "need" any more, on the premise that holding on to the memory is less costly than releasing it if you're likely to need it again.

I think it's worth a try to do the following, after first making a note of how much memory MySQL is using at the moment.

Note how many threads are currently cached:

mysql> show status like 'Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 9     |
+----------------+-------+
1 row in set (0.00 sec)

Next, disable the thread cache.

mysql> SET GLOBAL thread_cache_size = 0;

This disables the thread cache, but the cached threads will stay in the pool until they're used one more time. Disconnect from the server, then reconnect and repeat.

mysql> show status like 'Threads_cached';

Continue disconnecting, reconnecting, and checking until the counter reaches 0.

Then, see how much memory MySQL is holding.

You may see a decrease, possibly significant, and then again you may not. I tested this on one of my systems, which had 9 threads in the cache. Once those threads had all been cleared out of the cache, the total memory held by MySQL did decrease... not by much, but it does illustrate that threads in the cache do release at least some memory when they are destroyed.

If you see a significant decrease, you may have found your problem. If you don't, then there's one more thing that needs to happen, and how quickly it can happen depends on your environment.

If the theory holds that the other threads -- the ones currently servicing active client connections -- have significant memory allocated to them, either because of recent work in their current client session or because of work requiring a lot of memory that was done by another connection prior to them languishing in the pool, then you won't see all of the potential reduction in memory consumption until those threads are allowed to die and be destroyed. Presumably your application doesn't hold them forever, but how long it will take to know for sure whether there's a difference will depend on whether you have the option of cycling your application (dropping and reconnecting the client threads) or if you'll have to just wait for them to be dropped and reconnected over time on their own.

But... it seems like a worthwhile test. You should not see a substantial performance penalty by setting thread_cache_size to 0. Fortunately, thread_cache_size is a dynamic variable, so you can freely change it with the server running.

Mysql – The total number of locks exceeds the lock table size, even after increasing buffer pool size

Try this in my.cnf file,

[mysqld] innodb_buffer_pool_size = 80M

And you need to restart the Workbench as well as the Sql service.

Best Answer

Related Solutions

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

Mysql – The total number of locks exceeds the lock table size, even after increasing buffer pool size

Related Question