MySQL takes huge memory by ignoring buffer pool size

Question

I am facing a very strange problem, looks like on mysql startup, mysql is taking so much memory than 4x of buffer pool size.

I am using Ubuntu VM (4 Core 8 GB) with MySQL 5.6.33. /etc/mysql/my.cnf is as below:

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /datafiles/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 0.0.0.0
key_buffer = 128M
max_allowed_packet = 128M
thread_stack = 192K
thread_cache_size = 8
wait_timeout = 300
interactive_timeout = 600
max_connect_errors = 1000000
open-files-limit = 1024000
transaction-isolation = READ-COMMITTED
myisam-recover-options = BACKUP
max_connections = 25000
query_cache_limit = 1M
query_cache_size = 4M
general_log_file = /dblogs/audit/general_log.log
general_log = OFF
log_error = /dblogs/error.log
slow_query_log = ON
slow_query_log_file = /dblogs/SLOW.log
long_query_time =2 
min_examined_row_limit = 5000
server-id = 1
log_bin = /dblogs/binarylogs/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 10M
binlog_format = MIXED
innodb_strict_mode = OFF
sql_mode = NO_ENGINE_SUBSTITUTION
innodb_file_format = barracuda
innodb_file_format_max = barracuda
innodb_file_per_table = 1
innodb_data_home_dir = /datafiles/mysql
innodb_buffer_pool_size = 300M
innodb_buffer_pool_instances = 1
innodb_open_files = 6000
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_lock_wait_timeout = 600
innodb_io_capacity = 400
innodb_flush_method = O_DSYNC
innodb_flush_log_at_trx_commit = 2
innodb_write_io_threads = 2
innodb_read_io_threads = 2
innodb_log_files_in_group = 2
innodb_monitor_enable = all
join_buffer_size=256K
sort_buffer_size=256K
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/

As, per config file innodb_buffer_pool_size is set 300M and innodb_buffer_pool_instances is set to 1 (no need, as buffer pool is < 1G). error log file also confirms that buffer pool is set to 300.0M

ERROR.log

2020年09月24日 01:50:06 4886 [Note] Plugin 'FEDERATED' is disabled.
2020年09月24日 01:50:06 4886 [Note] InnoDB: Using atomics to ref count buffer pool pages
2020年09月24日 01:50:06 4886 [Note] InnoDB: The InnoDB memory heap is disabled
2020年09月24日 01:50:06 4886 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020年09月24日 01:50:06 4886 [Note] InnoDB: Memory barrier is not used
2020年09月24日 01:50:06 4886 [Note] InnoDB: Compressed tables use zlib 1.2.8
2020年09月24日 01:50:06 4886 [Note] InnoDB: Using Linux native AIO
2020年09月24日 01:50:06 4886 [Note] InnoDB: Using CPU crc32 instructions
2020年09月24日 01:50:06 4886 [Note] InnoDB: Initializing buffer pool, size = 300.0M
2020年09月24日 01:50:06 4886 [Note] InnoDB: Completed initialization of buffer pool
2020年09月24日 01:50:06 4886 [Note] InnoDB: Highest supported file format is Barracuda.
2020年09月24日 01:50:06 4886 [Note] InnoDB: 128 rollback segment(s) are active.
2020年09月24日 01:50:06 4886 [Note] InnoDB: Waiting for purge to start
2020年09月24日 01:50:06 4886 [Note] InnoDB: 5.6.33 started; log sequence number 4529735
2020年09月24日 01:50:06 4886 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2020年09月24日 01:50:06 4886 [Note] - '0.0.0.0' resolves to '0.0.0.0';
2020年09月24日 01:50:06 4886 [Note] Server socket created on IP: '0.0.0.0'.
2020年09月24日 01:50:06 4886 [Note] Event Scheduler: Loaded 0 events
2020年09月24日 01:50:06 4886 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.33-0ubuntu0.14.04.1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)

even more show global variables like '%pool%'; also show that buffer pool is set properly:

+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_additional_mem_pool_size | 8388608 |
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 314572800 |
+-------------------------------------+----------------+

But after service start if I check using free -h or in top mysql is taking more than 75% of RAM

 total used free shared buffers cached
Mem: 7.5G 5.3G 2.1G 28K 2.8M 77M
-/+ buffers/cache: 5.3G 2.2G
Swap: 9G 1.2G 8.8G

top command output:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
 8071 mysql 20 0 6982340 5.741g 9260 S 0.3 77.0 0:02.19 mysqld

I checked by changing innodb_buffer_pool_size to 1G,2G,3G and innodb_buffer_pool_instances to 1,8,10, but irrespective of all these when I start MySQL service 5-6 GB of total RAM (7GB) is taken by Mysql and as soon as I stop the service RAM usages reduced to 224M.

What is this behaviour? Did I misconfigured the MySQL? Why Mysql service is taking more than (4x or 8x) of buffer pool size?

• Wilson Hauck: Additional information request. Any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions.
• Amogh: @WilsonHauck thanks for the reply. Today while working on the same issue I observe that if I reduce the max_connections then memory taken by MySQL on startup gets reduced. Based on this I did some Google and I found a bug reported here bugs.mysql.com/bug.php?id=68514
• Wilson Hauck: Welcome to stackexchange. That is a great start. There are more opportunities to improve your configuration, if you could provide the additional information requested.

Answer 1

These are much too high:

open-files-limit = 1024000 -- 10K is probably plenty big
max_connections = 25000 -- 151 is probably plenty big
innodb_log_buffer_size = 64M -- Most DBA find the 8M default OK.
innodb_write_io_threads = 2 -- leave at 4
innodb_read_io_threads = 2 -- leave at 4

Don't raise settings (except for innodb_buffer_pool_size) before you have some concrete reason for doing so. In general, MySQL and MariaDB are adequately well-tuned "out of the box".

I glanced at the other items you changed; I did not see any that are likely to cause trouble.