Myrocks has abnormally increased memory

MySQL & MariaDB Percona Server for MySQL 8.0
,
1

My mysql server shows signs of increasing ram abnormally after a period of time.

In my opinion, this happens when RocksDB flushes data from memory to disk but does not release it in memory

image
image822×735 46.4 KB

My mysql server has the following configuration:
Hardware:
16 Cores
16G Ram

Mysql version : mysql Ver 8.0.32-24 for Linux on x86_64 (Percona Server (GPL), Release ‘24’, Revision ‘e5c6e9d2’)

libjemalloc: 5.2.1-1ubuntu1

mysqld.cnf

[mysqld]
default_authentication_plugin=mysql_native_password
skip-name-resolve = ON
server-id = 101159655
pid-file  = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir   = /data/mysql
log-error  = /var/log/mysql/error.log
secure-file-priv = ""
lc-messages-dir  = /usr/share/mysql/english
lc-messages = en_US
explicit_defaults_for_timestamp
symbolic-links=0

################## Replication configuration ##################

replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=mysql
replicate-ignore-db=sys

################## Logs ########################
log-bin = /data/log/mysql-bin.log
binlog-ignore-db = information_schema
binlog-ignore-db = mysql
gtid_mode = ON
enforce_gtid_consistency = ON

max_connections = 8092
#innodb_flush_neighbors = 0
table_open_cache = 1024
table_open_cache_instances=16
back_log = 1024
open_files_limit=65535
tmp_table_size=32M
max_heap_table_size=32M
thread_handling=pool-of-threads
thread_pool_size=128
max_allowed_packet=1G
rocksdb_block_cache_size=4G
rocksdb_db_write_buffer_size=128M
sql_mode=NO_ENGINE_SUBSTITUTION
default-time-zone = '+07:00'
log_timestamps = SYSTEM
################## Slow queries ##################
slow-query-log
slow_query_log_file = /data/log/slow_queries.log
long_query_time = 5

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

@Thinh_Nguyen_Quang

I can see the gradual increase of the memory usage. To further dig down this can you please get the below details with respect to RocksDB engine.

mysql> SHOW STATUS like 'Rocksdb%';
mysql> SHOW ENGINE ROCKSDB STATUS\G;

Also, please attach the RocksDB log file “/var/lib/mysql/.rocksdb/LOG” OR you can check the location with select @@rocksdb_datadir; command.

We also like to review the OS/Database insight. You can get the same via executing below snippet.

PTDEST=/tmp/pt/collected/`hostname`/
mkdir -p $PTDEST;
cd /tmp/pt;

wget http://per.co.na/pt-summary http://per.co.na/pt-mysql-summary
chmod +x pt*;
sudo ./pt-summary > $PTDEST/pt-summary.out;
sudo ./pt-mysql-summary --save-samples=$PTDEST/samples --user=root --password=mysql-root-password  > $PTDEST/pt-mysql-summary.out;
Please note that the username and password have to be changed to reflect the correct values of your instance.

Then compress the files and send them as an attachment:

tar czvf $(dirname ${PTDEST})/$(hostname).tar.gz $PTDEST;

Lastly, if enabled PS instrument then you check the global memory usage as per the events happen.

E.g,

select event_name, sum(current_number_of_bytes_used)/1024/1024 mb from performance_schema.memory_summary_global_by_event_name group by event_name order by 2 desc ;

Also sharing a few references in order to troubleshoot memory spike issues.

https://www.percona.com/blog/troubleshooting-mysql-memory-usage/
https://www.percona.com/blog/what-to-do-when-mysql-runs-out-of-memory-troubleshooting-guide/