Not the answer you need?
Register and ask your own question!

IOWait so high

baph0m3tbaph0m3t Current User Role Contributor
edited July 22 in Percona XtraBackup
Hi again community.
I'm getting Disk I/O overload alerts from zabbix because in our xtrabackup process taking more time and we want to know if our settings are ok or exist some tuning we can do to avoid this issue.

How it's possible get this all time to calcolate LSN ?

we do backup every 30 mins to get 1 full at midnight and every 30min the differences backups to recovery more actual data.

this action create Read disk over 40-60mb/s with IOWait 90-99% in 3 minutes approx.

Here is what we got
1 Percona server - 4 cpu, 6 gb ram disk in Raid5 on SSD over SAN
1 Percona slave -  8 cpu, 10 gb ram disk in Raid5 on SSD  over SAN

our slave ram right now is
              total        used        free      shared  buff/cache   available
Mem:           9866        5481        2604         392        1780        3831
Swap:          3967         109        3858

- Our application run only versus percona master server.
- The slave is of course for replication and create backup.
percona slave configuration is
-------
# Percona Custom server configuration
#
# Ansible managed

[client]
socket=/mysql/mysql.sock

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

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

[mysqldump]
max_allowed_packet=1G

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

Our script process run in this way with this values. 80% for parallel, compress and crypt process using 7 cpu., take the files and send by xbstream over ssh into repository on other server.

logs from script using xtrabackup

----

xtrabackup: recognized server arguments: --log_bin=/mysql/binlogs/binlog_node2 --server-id=2 --innodb_undo_directory=/mysql/innodb/ibundologs --innodb_log_files_in_group=5 --innodb_log_group_home_dir=/mysql/innodb/ibredologs --innodb_write_io_threads=8 --innodb_buffer_pool_size=3G --innodb_flush_method=O_DIRECT --innodb_data_home_dir=/mysql/innodb/ibdata --innodb_log_file_size=250M --innodb_autoextend_increment=50 --innodb_flush_log_at_trx_commit=1 --innodb_data_file_path=data1:100M:autoextend --innodb_read_io_threads=8 --innodb_file_per_table=1 --datadir=/mysql/data --parallel=4

xtrabackup: recognized client arguments: --socket=/mysql/mysql.sock --backup=1 --user=xbuser --password=* --stream=xbstream --lock-ddl=1 --slave-info=1 --compress --compress-threads=4 --encrypt=AES256 --encrypt-key=* --encrypt-threads=4

----

Any idea ? 

thanks

Best Answer

Answers

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.