MySql using 309G, sys.memory_global_by_current_bytes reports 193G. Possible memory leak?

MySQL & MariaDB
,
1

Top command gives
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2680 mysql 20 0 309.1g 240.0g 9556 S 167.4 95.3 166954:03 mysqld

Version is:
[fdelisle@prod-tc-db-224 ~]$ rpm -qa|grep mysql
php-mysqlnd-7.3.33-8.el7.remi.x86_64
[fdelisle@prod-tc-db-224 ~]$ rpm -qa|grep percona
percona-toolkit-3.5.2-2.el7.x86_64
percona-server-client-8.0.31-23.1.el7.x86_64
percona-release-1.0-27.noarch
percona-server-shared-8.0.31-23.1.el7.x86_64
percona-icu-data-files-8.0.31-23.1.el7.x86_64
percona-server-shared-compat-8.0.31-23.1.el7.x86_64
percona-server-server-8.0.31-23.1.el7.x86_64

kernel is:
3.10.0-1160.88.1.el7.x86_64
Centos 7.9.2009

mysql> SELECT event_name, current_alloc FROM sys.memory_global_by_current_bytes;
±-------------------------------------------------------------------------------±--------------+
| event_name | current_alloc |
±-------------------------------------------------------------------------------±--------------+
| memory/innodb/buf_buf_pool | 98.16 GiB |
| memory/innodb/memory | 61.82 GiB |
| memory/innodb/os0event | 6.90 GiB |
| memory/performance_schema/table_io_waits_summary_by_index_usage | 5.15 GiB |
| memory/performance_schema/table_shares | 4.81 GiB |
| memory/sql/TABLE_SHARE::mem_root | 3.87 GiB |
| memory/performance_schema/file_instances | 3.29 GiB |
| memory/innodb/dict0dict | 1.78 GiB |
| memory/performance_schema/table_lock_waits_summary_by_table | 1.61 GiB |
| memory/innodb/std | 1.55 GiB |
| memory/innodb/buf0buf | 945.94 MiB |
| memory/mysys/lf_node | 703.37 MiB |
| memory/mysys/lf_slist | 702.31 MiB |
| memory/innodb/ut_lock_free_hash_t | 512.25 MiB |
| memory/innodb/mem0mem | 410.75 MiB |
| memory/mysys/lf_dynarray | 131.77 MiB |
| memory/innodb/log_buffer_memory | 128.00 MiB |
| memory/sql/TABLE | 123.18 MiB |
| memory/performance_schema/events_errors_summary_by_account_by_error | 90.43 MiB |
| memory/performance_schema/events_errors_summary_by_host_by_error | 90.43 MiB |
| memory/performance_schema/rwlock_instances | 88.00 MiB |
| memory/performance_schema/events_statements_summary_by_account_by_event_name | 83.79 MiB |
| memory/performance_schema/events_statements_summary_by_host_by_event_name | 83.79 MiB |
| memory/performance_schema/table_handles | 81.56 MiB |
| memory/performance_schema/mutex_instances | 80.55 MiB |
| memory/performance_schema/memory_summary_by_account_by_event_name | 65.21 MiB |
| memory/performance_schema/memory_summary_by_host_by_event_name | 65.21 MiB |
| memory/sql/dd::String_type | 56.45 MiB |
| memory/performance_schema/events_errors_summary_by_thread_by_error | 51.67 MiB |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name | 47.88 MiB |
| memory/performance_schema/events_statements_summary_by_digest | 40.28 MiB |
| memory/performance_schema/memory_summary_by_thread_by_event_name | 37.27 MiB |
| memory/performance_schema/events_waits_summary_by_account_by_event_name | 36.31 MiB |
| memory/performance_schema/events_waits_summary_by_host_by_event_name | 36.31 MiB |
| memory/sql/dd::objects | 34.66 MiB |
| memory/sql/THD::main_mem_root | 26.15 MiB |
| memory/innodb/ut0link_buf | 24.00 MiB |
| memory/innodb/lock0lock | 21.40 MiB |
| memory/performance_schema/events_waits_summary_by_thread_by_event_name | 20.75 MiB |
| memory/innodb/ha_innodb | 18.43 MiB |
| memory/sql/NET::buff | 17.39 MiB |
| memory/temptable/physical_ram | 15.00 MiB |
| memory/performance_schema/events_statements_history | 14.69 MiB |
| memory/performance_schema/events_statements_current | 14.69 MiB |
| memory/performance_schema/events_statements_history_long | 14.34 MiB |
| memory/performance_schema/metadata_locks | 12.50 MiB |
| memory/innodb/fil0fil | 10.79 MiB |
| memory/performance_schema/events_statements_history.digest_text | 10.00 MiB |
| memory/performance_schema/events_statements_history.sql_text | 10.00 MiB |
| memory/performance_schema/events_statements_current.digest_text | 10.00 MiB |
| memory/performance_schema/events_statements_current.sql_text | 10.00 MiB |
| memory/performance_schema/events_statements_summary_by_digest.digest_text | 9.77 MiB |
| memory/performance_schema/events_statements_history_long.digest_text | 9.77 MiB |
| memory/performance_schema/events_statements_history_long.sql_text | 9.77 MiB |
| memory/performance_schema/events_stages_summary_by_account_by_event_name | 9.57 MiB |
| memory/performance_schema/events_stages_summary_by_host_by_event_name | 9.57 MiB |
| memory/mysys/KEY_CACHE | 8.00 MiB |
| memory/innodb/sync0arr | 7.03 MiB |
| memory/performance_schema/events_errors_summary_by_user_by_error | 6.46 MiB |
| memory/sql/dd::infrastructure | 6.38 MiB |
| memory/performance_schema/events_statements_summary_by_user_by_event_name | 5.99 MiB |
| memory/innodb/row0sel | 5.76 MiB |
| memory/performance_schema/events_stages_summary_by_thread_by_event_name | 5.47 MiB |
| memory/sql/log_sink_pfs | 5.00 MiB |
| memory/mysys/IO_CACHE | 4.81 MiB |
| memory/performance_schema/memory_summary_by_user_by_event_name | 4.66 MiB |
| memory/innodb/read0read | 4.48 MiB |
| memory/performance_schema/threads | 4.31 MiB |
| memory/innodb/ut0pool | 4.00 MiB |
| memory/performance_schema/events_statements_summary_by_program | 3.44 MiB |
| memory/performance_schema/events_transactions_history | 3.36 MiB |
| memory/performance_schema/events_transactions_history_long | 3.28 MiB |
| memory/innodb/os0file | 2.62 MiB |
| memory/performance_schema/events_waits_summary_by_user_by_event_name | 2.59 MiB |
| memory/innodb/trx0trx | 2.23 MiB |
| memory/performance_schema/prepared_statements_instances | 1.94 MiB |
| memory/performance_schema/events_waits_history | 1.72 MiB |
| memory/performance_schema/events_waits_history_long | 1.68 MiB |
| memory/performance_schema/accounts | 1.64 MiB |
| memory/performance_schema/hosts | 1.42 MiB |
| memory/sql/acl_cache | 1.22 MiB |
| memory/performance_schema/events_stages_history | 1.02 MiB |
| memory/performance_schema/events_stages_history_long | 1015.62 KiB |
| memory/sql/String::value | 916.30 KiB |
| memory/performance_schema/scalable_buffer | 728.42 KiB |
| memory/performance_schema/events_stages_summary_by_user_by_event_name | 700.00 KiB |
| memory/performance_schema/session_connect_attrs | 512.00 KiB |
| memory/performance_schema/socket_instances | 312.00 KiB |
| memory/sql/plugin_mem_root | 304.75 KiB |
| memory/performance_schema/file_handle | 256.00 KiB |
| memory/innodb/trx0i_s | 192.18 KiB |
| memory/innodb/trx0undo | 188.12 KiB |
| memory/innodb/rem0rec | 185.28 KiB |
| memory/performance_schema/cond_instances | 180.00 KiB |
| memory/innodb/buf0dblwr | 170.09 KiB |
| memory/performance_schema/events_errors_summary_global_by_error | 164.62 KiB |
| memory/performance_schema/events_transactions_summary_by_account_by_event_name | 154.00 KiB |
| memory/performance_schema/events_transactions_summary_by_host_by_event_name | 154.00 KiB |
| memory/mysqld_openssl/openssl_malloc | 132.34 KiB |
| memory/innodb/btr0pcur | 122.69 KiB |
| memory/performance_schema/memory_class | 99.38 KiB |
| memory/innodb/trx0rseg | 93.59 KiB |
| memory/performance_schema/events_transactions_summary_by_thread_by_event_name | 88.00 KiB |
| memory/performance_schema/mutex_class | 87.50 KiB |
| memory/innodb/dict0mem | 85.20 KiB |
| memory/performance_schema/users | 80.00 KiB |
| memory/performance_schema/setup_actors | 64.00 KiB |
| memory/innodb/trx0sys | 61.10 KiB |
| memory/performance_schema/setup_objects | 56.00 KiB |
| memory/performance_schema/events_statements_summary_global_by_event_name | 47.88 KiB |
| memory/innodb/log0log | 47.23 KiB |
| memory/sql/errmsgs::server | 45.35 KiB |
| memory/performance_schema/stage_class | 43.75 KiB |
| memory/performance_schema/statement_class | 42.56 KiB |
| memory/mysys/TREE | 38.14 KiB |
| memory/performance_schema/cond_class | 37.50 KiB |
| memory/performance_schema/memory_summary_global_by_event_name | 37.27 KiB |
| memory/innodb/dict_stats_bg_recalc_pool_t | 32.09 KiB |
| memory/sql/tz_storage | 32.05 KiB |
| memory/sql/Filesort_buffer::sort_keys | 32.03 KiB |
| memory/performance_schema/thread_class | 25.00 KiB |
| memory/performance_schema/file_class | 25.00 KiB |
| memory/sql/binlog_cache_mngr | 24.97 KiB |
| memory/performance_schema/rwlock_class | 17.50 KiB |
| memory/innodb/lexyy | 16.17 KiB |
| memory/innodb/sync0debug | 15.90 KiB |
| memory/innodb/sync0sharded_rw | 11.53 KiB |
| memory/sql/THD::Session_tracker | 11.26 KiB |
| memory/performance_schema/events_transactions_summary_by_user_by_event_name | 11.00 KiB |
| memory/sql/handlerton::objects | 9.06 KiB |
| memory/sql/LOG_POS_COORD | 9.05 KiB |
| memory/csv/Transparent_file | 8.06 KiB |
| memory/innodb/trx_sys_t::rw_trx_ids | 8.03 KiB |
| memory/innodb/srv0tmp | 7.72 KiB |
| memory/sql/sql_acl_mem | 7.12 KiB |
| memory/performance_schema/events_stages_summary_global_by_event_name | 5.47 KiB |
| memory/sql/THD::variables | 5.03 KiB |
| memory/sql/udf_mem | 3.80 KiB |
| memory/sql/THD::Session_sysvar_resource_manager | 3.48 KiB |
| memory/performance_schema/socket_class | 3.12 KiB |
| memory/mysqlx/objects | 3.11 KiB |
| memory/sql/plugin_bookmark | 3.02 KiB |
| memory/sql/Prepared_statement::infrastructure | 2.81 KiB |
| memory/mysys/MY_BITMAP::bitmap | 2.11 KiB |
| memory/sql/ST_SCHEMA_TABLE | 2.03 KiB |
| memory/sql/sql_acl_memex | 1.76 KiB |
| memory/innodb/srv0srv | 1.54 KiB |
| memory/innodb/trx0purge | 1.50 KiB |
| memory/innodb/adaptive hash index | 1.41 KiB |
| memory/sql/user_var_entry | 1.41 KiB |
| memory/sql/User_level_lock | 1.41 KiB |
| memory/sql/THD::handler_tables_hash | 1.41 KiB |
| memory/innodb/buf0flu | 1.20 KiB |
| memory/innodb/undo::Tablespaces | 1.16 KiB |
| memory/vio/vio | 1.09 KiB |
| memory/sql/THD::db | 1.07 KiB |
| memory/csv/TINA_SHARE | 1.03 KiB |
| memory/sql/errmsgs::handler | 760 bytes |
| memory/myisam/ft_stopwords | 728 bytes |
| memory/innodb/log and page archiver | 680 bytes |
| memory/sql/user_conn | 666 bytes |
| memory/sql/db_worker_hash_entry | 600 bytes |
| memory/sql/mysql_plugin | 544 bytes |
| memory/mysys/my_err_head | 504 bytes |
| memory/mysys/my_file_info | 480 bytes |
| memory/sql/global_system_variables | 448 bytes |
| memory/innodb/log0recv | 424 bytes |
| memory/sql/log_error::stack | 374 bytes |
| memory/sql/Sid_map::Node | 364 bytes |
| memory/sql/TABLE::sort_io_cache | 336 bytes |
| memory/sql/Rpl_info_table | 294 bytes |
| memory/sql/Event_scheduler::scheduler_param | 272 bytes |
| memory/sql/Mutex_cond_array::Mutex_cond | 272 bytes |
| memory/innodb/clone | 256 bytes |
| memory/sql/MYSQL_LOCK | 208 bytes |
| memory/sql/File_query_log::name | 181 bytes |
| memory/sql/root | 176 bytes |
| memory/innodb/buf_stat_per_index_t | 152 bytes |
| memory/innodb/ibuf0ibuf | 144 bytes |
| memory/sql/host_cache::hostname | 120 bytes |
| memory/sql/Owned_gtids::sidno_to_hash | 120 bytes |
| memory/sql/userstat_table_stats | 120 bytes |
| memory/sql/userstat_index_stats | 120 bytes |
| memory/sql/userstat_user_stats | 120 bytes |
| memory/sql/userstat_thread_stats | 120 bytes |
| memory/sql/userstat_client_stats | 120 bytes |
| memory/blackhole/blackhole_share | 120 bytes |
| memory/sql/servers_cache | 120 bytes |
| memory/mysys/MY_TMPDIR::full_list | 111 bytes |
| memory/innodb/ut0wqueue | 104 bytes |
| memory/sql/MYSQL_RELAY_LOG::index | 100 bytes |
| memory/innodb/usr0sess | 96 bytes |
| memory/sql/MYSQL_RELAY_LOG::basename | 94 bytes |
| memory/sql/LOG::file_name | 93 bytes |
| memory/sql/MYSQL_BIN_LOG::index | 87 bytes |
| memory/sql/MYSQL_LOG::name | 81 bytes |
| memory/sql/MYSQL_BIN_LOG::basename | 81 bytes |
| memory/sql/opt_bin_logname | 81 bytes |
| memory/vio/ssl_fd | 80 bytes |
| memory/innodb/sync0types | 48 bytes |
| memory/innodb/ut0list | 48 bytes |
±-------------------------------------------------------------------------------±--------------+
201 rows in set (0.06 sec)

2

[fdelisle@prod-tc-db-224 ~]$ cat /etc/my.cnf
[mysql]
default-character-set=utf8mb4
socket = /mnt/disks/tapclicks-data/mysql-data/mysqld.sock
[client]
socket=/mnt/disks/tapclicks-data/mysql-data/mysqld.sock
[mysqld_safe]
socket = /mnt/disks/tapclicks-data/mysql-data/mysqld.sock
malloc-lib=/usr/lib64/libjemalloc.so.1
[mysqld]

* Basic Settings

Master

server-id = 224
pid-file = /mnt/disks/tapclicks-data/mysql-data/mysqld.pid
socket = /mnt/disks/tapclicks-data/mysql-data/mysqld.sock
port = 3306
basedir = /usr/
datadir = /mnt/disks/tapclicks-data/mysql-data
tmpdir = /mnt/disks/tapclicks-data/mysql-tmpdir
log_error = /mnt/disks/tapclicks-data/mysql-logs/stg-tapclicks-app-db.err
lc_messages_dir = /usr/share/mysql-8.0/
bind-address = 0.0.0.0
log_error_verbosity = 3
skip-name-resolve
default_authentication_plugin=mysql_native_password
#skip-grant-tables

* Fine Tuning

wait_timeout = 86400

REPLICATION

relay-log-recovery = 1
master-info-repository = TABLE
relay-log-info-repository = TABLE
expire_logs_days = 3
#gtid_mode = OFF_PERMISSIVE
binlog_cache_size = 262144
binlog_row_image = MINIMAL
log_statements_unsafe_for_binlog = 0
log_bin = /mnt/disks/tapclicks-data/mysql-binlog/mysql-bin
log_bin_index = /mnt/disks/tapclicks-data/mysql-binlog/mysql-bin.index
sync_binlog = 1000
max_binlog_size = 100M
binlog_format = MIXED
log_bin_trust_function_creators = 1
skip_replica_start
replica_skip_errors = 1062,1032

GENERAL

default-storage-engine = InnoDB
default-time-zone = ‘+00:00’
sql_mode = NO_ENGINE_SUBSTITUTION
connect_timeout = 86400
interactive_timeout = 86400
collation-server = utf8mb4_0900_ai_ci
character-set-server = utf8mb4
group_concat_max_len = 65000
lock_wait_timeout = 600

SAFETY

max-connect-errors = 32000
#skip-name-resolve ## Cause problem with PMM Client
innodb = FORCE
max_allowed_packet = 512M

CACHES AND LIMITS

open-files-limit = 1000000
table-definition-cache = 150000
table-open-cache = 150000
table_open_cache_instances = 32
tablespace_definition_cache = 100000
schema_definition_cache = 1000
read_rnd_buffer_size = 64M
range_optimizer_max_mem_size = 32M
regexp_time_limit = 1024

* InnoDB

default_storage_engine = InnoDB
innodb-flush-method = fsync
innodb_redo_log_capacity=32G
innodb-file-per-table = 1
innodb_strict_mode = 0
innodb_monitor_enable = all
innodb_io_capacity = 1000
innodb_io_capacity_max = 5000
innodb_log_buffer_size = 128M
innodb_log_write_ahead_size = 4096
innodb_stats_persistent_sample_pages = 1000
innodb_sync_array_size = 16
innodb_open_files = 400000
innodb_log_compressed_pages = OFF
transaction-isolation = READ-COMMITTED
innodb_online_alter_log_max_size = 2G
innodb_print_lock_wait_timeout_info = ON

LOGGING

long_query_time = 20
slow-query-log = 1
log_output = TABLE
performance_schema = 1

innodb_force_recovery

DYNAMIC

innodb_buffer_pool_instances = 16 # 8 or 16 if 16+ cpu
max_heap_table_size = 256M #1/1000 memory
sort_buffer_size = 256M #1/1000 memory
tmp_table_size = 2560M # 1% memory
temptable_max_ram = 25600M # 10% memory
max-connections = 1600 # nb cpu * 100 min 800
innodb-buffer-pool-size = 96G # 40-60% pool size

max_user_connections=1550

3

select from sys.memory_global_by_current_bytes. gives 193 Gig total used

Pmap gives:
pmap 2680
2680: /usr/sbin/mysqld
…
total 324069700K

Well over the 193 Gig sys.memory_global_by_current_bytes reports .