Comment 4 for bug 1703321

Revision history for this message
luojia (luojia) wrote :

the commands: innobackupex --apply-log --use-memory=20G ${backup_file_dir} >${backup_file_dir}/apply.log 2>&1
innobackupex version 2.4.7 Linux (x86_64) (revision id: 05f1fcf)
 my.cnf :
[mysql]
port=*
prompt=\\u@\\d\\r:\\m:\\s>
no-auto-rehash

[client]
port=*
socket=*/mysql.sock

[mysqld]
#dir
basedir=/usr
datadir=*
tmpdir=*
log-error=mysql.err
slow_query_log_file=*/mysql.slow
general_log_file=*/mysql.genlog
socket=*/mysql.sock
pid-file=*/mysql.pid

#innodb
innodb_file_format=Barracuda
innodb_data_home_dir=*
innodb_log_group_home_dir=*
innodb_data_file_path=ibdata1:100M:autoextend
innodb_buffer_pool_size=100G
innodb_buffer_pool_instances=4
innodb_log_files_in_group=3
innodb_log_file_size=1G
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2
innodb_max_dirty_pages_pct=70
innodb_io_capacity=400
innodb_thread_concurrency=0
innodb_read_io_threads=4
innodb_write_io_threads=16
innodb_open_files=60000
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_change_buffering=all
innodb_adaptive_flushing=1
innodb_old_blocks_time=1000
innodb_use_native_aio=1
innodb_lock_wait_timeout=120
lock_wait_timeout=60
innodb_rollback_on_timeout=0
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON
max_heap_table_size=128M

#myisam
key_buffer_size=32M
myisam_sort_buffer_size=128M
concurrent_insert=ALWAYS
delayed_insert_timeout=300
read_rnd_buffer_size=128K
myisam-recover-options=default

#replication
skip-slave-start
relay-log=relay
slave_load_tmpdir=*

#binlog
log-bin=mysql-bin
server_id=32120007
report_host=10.32.120.7
report_port=5002
binlog_cache_size=1M
max_binlog_cache_size=2G
max_binlog_size=1024M
binlog-format=row
#sync_binlog=1
log-slave-updates
read_only=ON
expire_logs_days=7
max_binlog_files=0
binlog_rows_query_log_events=ON

#server
default-storage-engine=INNODB
character-set-server=utf8mb4
collation_server=utf8mb4_unicode_ci
character_set_client=utf8mb4
open_files_limit=140000
log_slow_admin_statements=1
log_slow_verbosity=microtime,query_plan,innodb
log_warnings=2
#log_queries_not_using_indexes=1
long_query_time=0.100000
slow_query_log=1
log_slow_slave_statements=1
query_cache_type=0
query_cache_size=256M
query_cache_limit=1M
query_cache_min_res_unit=1K
table_definition_cache=65536
table_open_cache=65536
thread_stack=512K
thread_cache_size=256
sort_buffer_size=8M
join_buffer_size=8M
read_buffer_size=128K
port=5002
skip-name-resolve
skip-ssl
max_connections=2000
max_connect_errors=65536
max_allowed_packet=16M
connect_timeout=8
net_read_timeout=30
net_write_timeout=60
slave_net_timeout=90
back_log=1024

#When this variable is enabled, InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics during these operations. Leaving this setting disabled can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.
innodb_stats_on_metadata=OFF

#because bug https://bugs.launchpad.net/percona-server/+bug/1199213,default is 1
innodb_adaptive_hash_index_partitions=1

#suppress warning
log_warnings_suppress=1592

#on have performance problem.
performance_schema=OFF
#gtid
#gtid_mode=ON
#gtid_deployment_step=OFF
#enforce-gtid-consistency=ON

#replicate
relay_log_recovery=1
relay_log_info_repository=TABLE
master_info_repository=TABLE
slave_compressed_protocol=ON

#userstat
userstat=ON

#audit log setting
#audit_log_policy=LOGINS