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:
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:
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 🙂
