Backup Crash Recovery

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hi @community

We have percona server using this versions:

Percona-Server-shared-57-5.7.39-42.1.el8.x86_64
percona-release-1.0-27.noarch
Percona-Server-server-57-5.7.39-42.1.el8.x86_64
Percona-Server-shared-compat-57-5.7.39-42.1.el8.x86_64

Right now we are expecting 1h 30min to recovery, we are using AWS:
We need to know if is possible get a backup crash consistency more faster in minor time. maybe tuning more the configuration or limits on linux. ???

the backup we use is creating snapshots and when we test disaster recovery using hat snapshot mysql start using that time. we not use script for stop mysql for create a backup to avoid stop services from our customers so we use snapshost for now.

ec2: m5.xlarge 4 cpu and 16 Gb ram optimized for db according to aws instances type

total db: 113
we got more biggers between 40-80gb db , the rest are more smaller and between 1000-3000 tables by db more less

our config

#
# Percona Custom server configuration
#
# Ansible managed

[mysqldump]
max_allowed_packet=1G

[mysql]
default-character-set=utf8mb4
no-auto-rehash
socket=/mysql/mysql.sock
max_allowed_packet=1G

[client]
socket=/mysql/mysql.sock

[mysqld]
explicit_defaults_for_timestamp=OFF
log_timestamps=SYSTEM
wait_timeout=300
character_set_server=utf8mb4
collation-server=utf8mb4_unicode_520_ci
log_error_verbosity=3
max_allowed_packet=1G
lower_case_table_names=1
sql_mode=ANSI,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES
max_connections=1500
general-log=0
general-log-file=/mysql/log/general_query_log.txt
slow-query-log=0
slow_query_log_file=/mysql/log/slow_query_log.txt
long_query_time=10
slow_query_log_always_write_time=10
innodb_fast_shutdown=0
innodb_file_per_table=ON
innodb_data_home_dir=/mysql/innodb/ibdata
innodb_data_file_path=data1:100M:autoextend
innodb_autoextend_increment=50
innodb_log_group_home_dir=/mysql/innodb/ibredologs
innodb_log_files_in_group=5
innodb_log_file_size=250M
innodb_flush_log_at_trx_commit=1
innodb_buffer_pool_size=1G
innodb_buffer_pool_instances=8
innodb_flush_method=O_DIRECT
innodb_undo_directory=/mysql/innodb/ibundologs
innodb_read_io_threads=8
innodb_write_io_threads=8

# Percona 5x specific config
innodb_temp_data_file_path=../ibgtemp/gtemp:50M:autoextend:max:500M

the log on test DR from taking start from snapshot until mysql open connection can see differences in time

2023-04-20T09:22:51.480503Z 0 [Warning] Changed limits: max_open_files: 5000 (requested 7505)
2023-04-20T09:22:51.481009Z 0 [Warning] Changed limits: table_open_cache: 1745 (requested 2000)
2023-04-20T11:22:51.731753+01:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-04-20T11:22:51.732510+01:00 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2023-04-20T11:22:52.072066+01:00 0 [Note] /usr/sbin/mysqld (mysqld 5.7.39-42-log) starting as process 1215 ...
2023-04-20T11:22:53.728957+01:00 0 [Note] InnoDB: PUNCH HOLE support available
2023-04-20T11:22:53.728995+01:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2023-04-20T11:22:53.728999+01:00 0 [Note] InnoDB: Uses event mutexes
2023-04-20T11:22:53.729002+01:00 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2023-04-20T11:22:53.729005+01:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2023-04-20T11:22:53.729008+01:00 0 [Note] InnoDB: Using Linux native AIO
2023-04-20T11:22:53.730771+01:00 0 [Note] InnoDB: Number of pools: 1
2023-04-20T11:22:53.732756+01:00 0 [Note] InnoDB: Using CPU crc32 instructions
2023-04-20T11:22:53.833642+01:00 0 [Note] InnoDB: Initializing buffer pool, total size = 1G, instances = 8, chunk size = 128M
2023-04-20T11:22:53.865671+01:00 0 [Note] InnoDB: Completed initialization of buffer pool
2023-04-20T11:22:53.875751+01:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2023-04-20T11:22:54.216984+01:00 0 [Note] InnoDB: Recovering partial pages from the parallel doublewrite buffer at /mysql/innodb/ibdata/xb_doublewrite
2023-04-20T11:22:54.660806+01:00 0 [Note] InnoDB: Highest supported file format is Barracuda.
2023-04-20T11:22:54.760604+01:00 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 1890470010454
2023-04-20T11:22:54.760630+01:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 1890470013389
2023-04-20T11:22:55.086921+01:00 0 [Note] InnoDB: Database was not shutdown normally!
2023-04-20T11:22:55.086943+01:00 0 [Note] InnoDB: Starting crash recovery.
2023-04-20T11:23:03.906363+01:00 0 [Note] InnoDB: Created parallel doublewrite buffer at /mysql/innodb/ibdata/xb_doublewrite, size 31457280 bytes
2023-04-20T11:23:11.607541+01:00 0 [Note] InnoDB: Transaction 1085783904 was in the XA prepared state.
2023-04-20T11:23:12.436455+01:00 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 0 row operations to undo
2023-04-20T11:23:12.436484+01:00 0 [Note] InnoDB: Trx id counter is 1085784320
2023-04-20T11:23:12.438389+01:00 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
2023-04-20T11:23:12.971116+01:00 0 [Note] InnoDB: Apply batch completed
2023-04-20T11:23:12.971139+01:00 0 [Note] InnoDB: Last MySQL binlog file position 0 693360865, file name binlog_node1.000555
2023-04-20T13:01:42.163794+01:00 0 [Note] InnoDB: Starting in background the rollback of uncommitted transactions
2023-04-20T13:01:42.165857+01:00 0 [Note] InnoDB: Rollback of non-prepared transactions completed
2023-04-20T13:01:42.170553+01:00 0 [Note] InnoDB: Removed temporary tablespace data file: "../ibgtemp/gtemp"
2023-04-20T13:01:42.170568+01:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2023-04-20T13:01:42.170634+01:00 0 [Note] InnoDB: Setting file '/mysql/innodb/ibdata/../ibgtemp/gtemp' size to 50 MB. Physically writing the file full; Please wait ...
2023-04-20T13:01:47.791917+01:00 0 [Note] InnoDB: File '/mysql/innodb/ibdata/../ibgtemp/gtemp' size is now 50 MB.
2023-04-20T13:01:47.812003+01:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2023-04-20T13:01:47.812029+01:00 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2023-04-20T13:01:47.812223+01:00 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5933936ms. The settings might not be optimal. (flushed=0, during the time.)
2023-04-20T13:01:47.812720+01:00 0 [Note] InnoDB: Waiting for purge to start
2023-04-20T13:01:47.863292+01:00 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.7.39-42 started; log sequence number 1890470013389
2023-04-20T13:01:47.864287+01:00 0 [Note] InnoDB: Loading buffer pool(s) from /mysql/innodb/ibdata/ib_buffer_pool
2023-04-20T13:01:48.834875+01:00 0 [Note] Plugin 'FEDERATED' is disabled.
2023-04-20T13:01:48.858385+01:00 0 [Note] InnoDB: Starting recovery for XA transactions...
2023-04-20T13:01:48.858412+01:00 0 [Note] InnoDB: Transaction 1085783904 in prepared state after recovery
2023-04-20T13:01:48.858418+01:00 0 [Note] InnoDB: Transaction contains changes to 25 rows
2023-04-20T13:01:48.858423+01:00 0 [Note] InnoDB: 1 transactions in prepared state after recovery
2023-04-20T13:01:48.858427+01:00 0 [Note] Found 1 prepared transaction(s) in InnoDB
2023-04-20T13:01:49.332048+01:00 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2023-04-20T13:01:49.332857+01:00 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2023-04-20T13:01:49.332866+01:00 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2023-04-20T13:01:49.332869+01:00 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-04-20T13:01:49.338643+01:00 0 [Warning] CA certificate ca.pem is self signed.
2023-04-20T13:01:49.338682+01:00 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2023-04-20T13:01:49.340393+01:00 0 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2023-04-20T13:01:49.340415+01:00 0 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2023-04-20T13:01:49.340444+01:00 0 [Note] Server socket created on IP: '0.0.0.0'.
2023-04-20T13:01:49.570841+01:00 0 [Note] Failed to start slave threads for channel ''
2023-04-20T13:01:49.696318+01:00 0 [Warning] Optional native table 'performance_schema'.'processlist' has the wrong structure or is missing.
2023-04-20T13:01:49.698021+01:00 0 [Note] Event Scheduler: Loaded 0 events
2023-04-20T13:01:49.698237+01:00 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.39-42-log'  socket: '/mysql/mysql.sock'  port: 3306  Percona Server (GPL), Release 42, Revision b0a7dc2da2e
2023-04-20T13:07:09.311899+01:00 0 [Note] InnoDB: Buffer pool(s) load completed at 230420 13:07:09
2023-04-20T13:08:11.879178+01:00 2 [Note] Aborted connection 2 to db: 'hypersicalessandria' user: 'alessandriauser' host: 'ip-10-25-0-149.eu-west-1.compute.internal' (Got timeout reading communication packets)
2

With 16GB of RAM on the instance, your config needs adjusting:

innodb_buffer_pool_size=8G

See if that makes a difference. The other option is to use Percona Xtrabackup (PXB) directly on the EC2 instance instead of AWS snapshots. PXB will perform a hot, online backup. After the backup finishes, prepare it, then copy it somewhere safe. Now you can restore this prepared backup to a new MySQL in a matter of minutes.

3

Hi @matthewb

Thanks for reply I’ll try with that option and test again… our scenario is master-slave and in fact we do also a backup using xtrabackup scripts from slave and send it to another repofile just to keep it in case someone need db or specific table to restore it in other database to not touch original master, maybe we have to change our logic to how restore more easy using that data we already got than snapshot in someway re-writing our xtrabackup scripting logic for our architecture.

we think about it in any case thanks for the tips again.

4

Hi @matthewb

I have another question that option could be help to improve the performance even on original server not from recovery DR ?

reading this site: InnoDB Performance Optimization Basics

i see this settings is set dinamically without restart server is correct right?

how can i do exactly that?
just set into my /etc/percona.conf file? or that settings is dinamically using mysql console?

5

All the dynamic changes can be applied using SET GLOBAL command while the static changes will need the config file change followed by restarting MySQL.

SET GLOBAL variable_name = VALUE;

Though this change will not persist the mysql restart and will needed to be manually added to MySQL configuration file for versions < MySQL 8. But in MySQL 8 you can persist these changes using SET PERSIST command:
SET PERSIST variable_name = VALUE;