Description:
Mysql with only 33 connections consuming 53GB of RAM.
innodb_buffer_pool_size configured with 34 GB
Server with 72 GB of RAM
Ubuntu 24.04.1 LTS
/usr/sbin/mysqld Ver 8.4.3 for Linux on x86_64 (MySQL Community Server - GPL)
mysql> status
--------------
mysql Ver 8.4.3 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 52024
Current database:
Current user: user@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.4.3 MySQL Community Server - GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/run/mysqld/mysqld.sock
Binary data as: Hexadecimal
Uptime: 5 days 16 hours 30 min 46 sec
Threads: 32 Questions: 955098704 Slow queries: 170 Opens: 674859 Flush tables: 3 Open tables: 992 Queries per second avg: 1943.445
--------------
MY.INI
[mysqld]
# === Required Settings ===
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
bind_address = 0.0.0.0
datadir = /dados/mysql-data
mysql_native_password = ON
max_allowed_packet = 256M
max_connect_errors = 1000000
pid_file = /var/run/mysqld/mysqld.pid
port = 3307
skip_external_locking
skip_name_resolve
socket = /var/run/mysqld/mysqld.sock
tmpdir = /dados/mysql-data/tmp
# === SQL Compatibility Mode ===
sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'
# === InnoDB Settings ===
innodb_buffer_pool_instances = 8
innodb_buffer_pool_size = 34G
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 64M
innodb_sort_buffer_size = 1M
innodb_stats_on_metadata = 0
innodb_use_fdatasync = 1
transaction_isolation = READ-COMMITTED
innodb_redo_log_capacity = 536870912
#innodb_thread_concurrency = 4
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
key_buffer_size = 4M # UPD
# === Connection Settings ===
max_connections = 200
back_log = 512
thread_cache_size = 100
interactive_timeout = 3600
wait_timeout = 3600
# === Buffer Settings ===
join_buffer_size = 8M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
sort_buffer_size = 4M
# === Table Settings ===
table_definition_cache = 1000
table_open_cache = 1000
open_files_limit = 8000
max_heap_table_size = 16M
tmp_table_size = 32M
# === Binary Logging ===
server_id = 6
log_bin = binlog
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_expire_logs_seconds = 345600
replica_parallel_workers = 10
log_replica_updates = 0
relay_log = relaybinlog
# === Error & Slow Query Logging ===
log_error = /var/log/mysql/error.log
log_queries_not_using_indexes = 0
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /dados/mysql-data/mysql_slow.log
general_log = 0
general_log_file = /dados/mysql-data/mysql_general.log
log_timestamps = SYSTEM
lower_case_table_names=1
local_infile=1
log_error_verbosity = 3
slave-skip-errors=1062,1032,1735,1452
[mysqldump]
quick
quote_names
max_allowed_packet = 256M
+---------------------------+---------------+
| code_area | current_alloc |
+---------------------------+---------------+
| memory/innodb | 34.92 GiB |
| memory/sql | 2.43 GiB |
| memory/performance_schema | 267.69 MiB |
| memory/temptable | 13.00 MiB |
| memory/mysys | 6.56 MiB |
| memory/mysqld_openssl | 756.57 KiB |
| memory/refcache | 21.46 KiB |
| memory/mysqlx | 3.45 KiB |
| memory/vio | 3.34 KiB |
| memory/myisam | 728 bytes |
| memory/csv | 120 bytes |
| memory/blackhole | 120 bytes |
+---------------------------+---------------+
12 rows in set (0.01 sec)
It has already increased by 4GB from yesterday to today. MySQL is consuming 57.7 GB
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2140222 mysql 20 0 62.0g **57.7g** 41856 S 47.8 81.6 34,43 mysqld
This did not occur in MySQL 5.7.
How to repeat:
I don't know how to reproduce.
Description: Mysql with only 33 connections consuming 53GB of RAM. innodb_buffer_pool_size configured with 34 GB Server with 72 GB of RAM Ubuntu 24.04.1 LTS /usr/sbin/mysqld Ver 8.4.3 for Linux on x86_64 (MySQL Community Server - GPL) mysql> status -------------- mysql Ver 8.4.3 for Linux on x86_64 (MySQL Community Server - GPL) Connection id: 52024 Current database: Current user: user@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.4.3 MySQL Community Server - GPL Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/run/mysqld/mysqld.sock Binary data as: Hexadecimal Uptime: 5 days 16 hours 30 min 46 sec Threads: 32 Questions: 955098704 Slow queries: 170 Opens: 674859 Flush tables: 3 Open tables: 992 Queries per second avg: 1943.445 -------------- MY.INI [mysqld] # === Required Settings === user = mysql pid-file = /var/run/mysqld/mysqld.pid bind_address = 0.0.0.0 datadir = /dados/mysql-data mysql_native_password = ON max_allowed_packet = 256M max_connect_errors = 1000000 pid_file = /var/run/mysqld/mysqld.pid port = 3307 skip_external_locking skip_name_resolve socket = /var/run/mysqld/mysqld.sock tmpdir = /dados/mysql-data/tmp # === SQL Compatibility Mode === sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' # === InnoDB Settings === innodb_buffer_pool_instances = 8 innodb_buffer_pool_size = 34G innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 64M innodb_sort_buffer_size = 1M innodb_stats_on_metadata = 0 innodb_use_fdatasync = 1 transaction_isolation = READ-COMMITTED innodb_redo_log_capacity = 536870912 #innodb_thread_concurrency = 4 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_io_capacity = 10000 innodb_io_capacity_max = 20000 key_buffer_size = 4M # UPD # === Connection Settings === max_connections = 200 back_log = 512 thread_cache_size = 100 interactive_timeout = 3600 wait_timeout = 3600 # === Buffer Settings === join_buffer_size = 8M read_buffer_size = 1M read_rnd_buffer_size = 1M sort_buffer_size = 4M # === Table Settings === table_definition_cache = 1000 table_open_cache = 1000 open_files_limit = 8000 max_heap_table_size = 16M tmp_table_size = 32M # === Binary Logging === server_id = 6 log_bin = binlog binlog_format = ROW gtid_mode = ON enforce_gtid_consistency = ON binlog_expire_logs_seconds = 345600 replica_parallel_workers = 10 log_replica_updates = 0 relay_log = relaybinlog # === Error & Slow Query Logging === log_error = /var/log/mysql/error.log log_queries_not_using_indexes = 0 long_query_time = 1 slow_query_log = 1 slow_query_log_file = /dados/mysql-data/mysql_slow.log general_log = 0 general_log_file = /dados/mysql-data/mysql_general.log log_timestamps = SYSTEM lower_case_table_names=1 local_infile=1 log_error_verbosity = 3 slave-skip-errors=1062,1032,1735,1452 [mysqldump] quick quote_names max_allowed_packet = 256M +---------------------------+---------------+ | code_area | current_alloc | +---------------------------+---------------+ | memory/innodb | 34.92 GiB | | memory/sql | 2.43 GiB | | memory/performance_schema | 267.69 MiB | | memory/temptable | 13.00 MiB | | memory/mysys | 6.56 MiB | | memory/mysqld_openssl | 756.57 KiB | | memory/refcache | 21.46 KiB | | memory/mysqlx | 3.45 KiB | | memory/vio | 3.34 KiB | | memory/myisam | 728 bytes | | memory/csv | 120 bytes | | memory/blackhole | 120 bytes | +---------------------------+---------------+ 12 rows in set (0.01 sec) It has already increased by 4GB from yesterday to today. MySQL is consuming 57.7 GB PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2140222 mysql 20 0 62.0g **57.7g** 41856 S 47.8 81.6 34,43 mysqld This did not occur in MySQL 5.7. How to repeat: I don't know how to reproduce.