XtraBackup vs. OOM killer(usage of mprof)

This small article is about nicely depicting memory usage prior to OOM killers show.

I have started MySQL with:

/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/bin/mysqld --no-defaults \
--innodb_buffer_pool_size=1G --innodb_log_file_size=1G --innodb_page_size=64K --early-plugin-load=keyring_file.so \ --keyring_file_data=/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/mysql-keyring/keyring \
--log-bin=mysql-bin --log-slave-updates --server-id=1 --gtid-mode=ON --enforce-gtid-consistency --binlog-format=row --core-file --basedir=/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64 \
--tmpdir=/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/data \
--datadir=/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/data \
--plugin-load-add=tokudb=ha_tokudb.so --tokudb-check-jemalloc=0 --plugin-load-add=rocksdb=ha_rocksdb.so 
--socket=/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/socket.sock --port=17057 \
--log-error=/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/log/master.err

So basically it is MySQL 5.7.19 with 64K page size.

Trying to take full + incremental backups:

Full is OK:

/home/shahriyar.rzaev/XB_TEST/server_dir/target/percona-xtrabackup-2.4.x-debug/bin/xtrabackup --defaults-file= \
--user=root --password=''  --target-dir=/home/shahriyar.rzaev/XB_TEST/backup_dir/ps_5_7_x_2_4/full/2017-11-14_11-31-20 \
--backup --socket=/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/socket.sock \
--compress=quicklz --compress-chunk-size=65536 --compress-threads=4 --encrypt=AES256 --encrypt-key=VVTBwgM4UhwkTTV98fhuj+D1zyWoA89K \
--encrypt-threads=4 --encrypt-chunk-size=65536 --no-version-check --core-file --parallel=10 --throttle=40 \
--keyring-file-data=/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/mysql-keyring/keyring --stream="xbstream" > \
/home/shahriyar.rzaev/XB_TEST/backup_dir/ps_5_7_x_2_4/full/2017-11-14_11-31-20/full_backup.stream

Incremental Failed:

/home/shahriyar.rzaev/XB_TEST/server_dir/target/percona-xtrabackup-2.4.x-debug/bin/xtrabackup --defaults-file= \
--user=root --password='' --target-dir=/home/shahriyar.rzaev/XB_TEST/backup_dir/ps_5_7_x_2_4/inc/2017-11-14_11-33-51 \
--incremental-basedir=/home/shahriyar.rzaev/XB_TEST/backup_dir/ps_5_7_x_2_4/full/2017-11-14_11-31-20 --backup \
--socket=/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/socket.sock \
--compress=quicklz --compress-chunk-size=65536 --compress-threads=4 --encrypt=AES256 \
--encrypt-key=VVTBwgM4UhwkTTV98fhuj+D1zyWoA89K --encrypt-threads=4 --encrypt-chunk-size=65536 \
--no-version-check --core-file --parallel=10 --throttle=40 \
--keyring-file-data=/home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/mysql-keyring/keyring --stream="xbstream" > \
 /home/shahriyar.rzaev/XB_TEST/backup_dir/ps_5_7_x_2_4/inc/2017-11-14_11-33-51/inc_backup.stream

171114 11:35:05 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/socket.sock
Using server version 5.7.19-17-debug-log
/home/shahriyar.rzaev/XB_TEST/server_dir/target/percona-xtrabackup-2.4.x-debug/bin/xtrabackup version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: )
incremental backup from 70156215 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/shahriyar.rzaev/XB_TEST/server_dir/PS131117-percona-server-5.7.19-17-linux-x86_64/data/
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 1073741824
InnoDB: Number of pools: 1
171114 11:35:06 >> log scanned up to (70156224)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
xtrabackup: Starting 10 threads for parallel data files transfer
171114 11:35:07 >> log scanned up to (70156224)
Killed

I forgot about already reported bug here -> #1582456

So according to this report and this code line:

write_filt.cc#L78

The buffer calculation for incremental backup is:

/* allocate buffer for incremental backup (4096 pages) */
	buf_size = (cursor->page_size / 4 + 1) * cursor->page_size +
UNIV_PAGE_SIZE_MAX;

So with 64K MySQL it is going to allocate:

(64*1024/4+1)*64*1024+64*1024 == 1073872896 == 1024 MB == 1G roughly.

And if you are going to use –parallel=10 as me, it is going to something like 10*1G = 10G. Due to this it is quite fair action by OOM killer to terminate my process.

After that, I was searching for nice simple solution to depict memory usage of executable and found this Python module:

memory_profiler

To install it:

sudo pip3.5 install memory_profiler
sudo pip3.5 install psutil
sudo pip3.5 install matplotlib

You need tkinter as well:

sudo yum -y install python35u-tkinter

You need xauth as well, for plotting:

sudo yum -y install xauth

After that connecting to my Vagrant VM with -X option:

ssh -X vagrant@127.0.0.1 -i /home/shako/REPOS/MySQL-AutoXtraBackup/.vagrant/machines/default/virtualbox/private_key -p 2222

And running following command:

mprof run append_here_the_command_to_run

It will run the given command and create some file mprofile_20171114122250.dat.

Now run following:

[vagrant@dhcppc3 ~]$ mprof plot mprofile_20171114122250.dat

And it will create:

BTW I have reported a bug to mprof as well: #171
Thanks for reading 🙂