0

Mysql Dump Taking longer time

I have 188 databases, which I am individually dumping in a loop using a shell script as below

 echo "$(date  +%b-%d-%y) Dumping Started at $(date +%r)" | tee -a $DEST/general.logs $DEST/details.logs
for db in $databases; do
        echo "START : Dumping $db start at $(date +%r)" >> $DEST/details.logs
        if [[ "$db" != "information_schema" && "$db" != "performance_schema" ]]; then
                if ( mysqldump -u $USER -p$PASSWORD --databases $db > $DEST/$DATE.$db.sql) then
                        gzip $DEST/$DATE.$db.sql
                        echo "SUCCESS : Dumping $db" >> $DEST/details.logs
                        echo "FINISH : Dumping $db finished at $(date +%r)" >> $DEST/details.logs
                else
                        echo "FAILED : Dumping $db" | tee -a $DEST/general.logs $DEST/details.logs
                fi
        fi
done
echo "$(date  +%b-%d-%y) Dumping Finished at $(date +%r)" | tee -a $DEST/general.logs $DEST/details.logs

if [ -d "$oldBackup" ]; then
        rm -rf $oldBackup
        echo "Backup for $fiveDaysAgo removed successfully at $(date +%r)" | tee -a $DEST/general.logs $DEST/details.logs
else
        echo "Backup for $fiveDaysAgo not found." | tee -a $DEST/general.logs $DEST/details.logs
fi
echo "Backup Size" $(du -ch $DEST | grep total) >> $DEST/general.logs

The total size of all the databases is nearly 150GB When I restart mysql it takes nearly 2-3 hours to complete all the backups for a couple of days and then it keeps on increasing. Over a period of a week it reaches 7-8 hours and then 15 -20 Hours.

I have made sure that there is no process running while the backup is going for the first 4 hours.

Below is my sys config : OS : Centos 5.6 Mysql : 5.6 RAM : 64GB(8 cores)

Below my SQL config(my.cnf)

[mysqld]
#innodb_force_recovery=6
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/var/lib/mysql/tmp
user=mysql

interactive_timeout=15
wait_timeout=40

symbolic-links=0
local-infile=0
port=33060
skip-name-resolve

sql_mode=NO_ENGINE_SUBSTITUTION
explicit_defaults_for_timestamp=1

innodb_buffer_pool_size=32G
innodb_log_file_size=10G
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_checksum_algorithm=crc32

innodb_buffer_pool_instances=8
innodb_thread_concurrency=16
innodb_lock_wait_timeout=180

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_io_capacity=2000
innodb_io_capacity_max=3000
innodb_lru_scan_depth=2000

#Binary log/replication
#log-bin
#sync_binlog=1
#sync_relay_log=1
#relay-log-info-repository=TABLE
#master-info-repository=TABLE
#expire_logs_days=6
#binlog_format=ROW
#transaction-isolation=READ-COMMITTED
#innodb_autoinc_lock_mode = 2
join_buffer_size=6M
sort_buffer_size=6M
read_buffer_size=6M
read_rnd_buffer_size=1M

key_buffer_size=512M
query_cache_type=0
query_cache_size=0
#query_cache_limit=1M

tmp_table_size=256M
max_heap_table_size=256M
table_open_cache_instances=16
table_open_cache=200000
table_definition_cache=100000
open_files_limit=1024000

thread_cache_size=200
max_connections=400
max_user_connections=350
max_connect_errors=99

connect_timeout=20
max_allowed_packet=128M

slow_query_log=1
long_query_time=5
slow_query_log_file=/var/log/mysql-slow.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
max_allowed_packet=1G

Thank you in advance!!

6
  • What's your question? – mustaccio May 5 '18 at 13:02
  • @mustaccio I would like to stop this incremental increase of the backup time. Whenever I restart my server it comes down to the base time, but then again it starts increasing. – Nirbhay Shah May 7 '18 at 1:53
  • what is the output of the following two quires SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty'; and SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; – Ahmad Abuhasna May 7 '18 at 11:05
  • Output of SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty' is Innodb_buffer_pool_pages_dirty - 1627 And of Innodb_page_size is Innodb_page_size - 16384 – Nirbhay Shah May 19 '18 at 3:04
  • Are all of your table InnoDB? – Rick James May 22 '18 at 17:31
1

Not a solution, but an improvement:

mysqldump ... | gzip >$DEST/$DATE.$db.sql.gz

This would avoid the I/O due to the write and reread of the .sql file.

You could also consider having N connections -- N-1 doing the biggest databases, 1 doing all the rest. N=4 might be reasonable; there is no use making it too high -- once the I/O is saturated, no further speedup can be had.

A minor note: Since you are invoking date more than once, there is a chance of getting a different value somewhere in the middle of the script. Recommend capturing it in a variable.

Does the du show a significant difference from one time to the next?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.