如何加快800万条记录的mysql表的复制速度



我通过Bash Shell脚本在Ubuntu服务器中的CRON作业,使用mysqldump命令备份我的Live服务器,并且同一脚本也将备份上传到我的备份服务器。早些时候,这很好,但现在我面临着速度慢的问题(在备份服务器上备份和上传需要1个小时),因为其中一个数据库表的大小已经增长到5GB,由1000万条记录组成。我在一个线程上看到,我们可以通过SQL的批量/组执行来加快SQL插入——mysql如何更快地插入数百万条记录?

但在我的情况下,我不确定如何创建一个Shell脚本来执行同样的操作。

要求是我想以最大10k的组导出所有SQL数据库表,以便在服务器上导入时执行速度更快。

我在我的服务器bash脚本上写了以下代码:

#!/bin/bash
cd /tmp
file=$(date +%F-%T).sql
mysqldump 
--host ${MYSQL_HOST} 
--port ${MYSQL_PORT} 
-u ${MYSQL_USER} 
--password="${MYSQL_PASS}" 
${MYSQL_DB} > ${file}
if [ "${?}" -eq 0 ]; then
mysql -umyuser -pmypassword -h 198.168.1.3  -e "show databases"
mysql -umyuser -pmypassword -h 198.168.1.3 -D backup_db -e "drop database backup_db"
mysql -umyuser -pmypassword -h 198.168.1.3  -e "create database backup_db" 
mysql -umyuser -pmypassword -h 198.168.1.3 backup_db < ${file}
gzip ${file}
aws s3 cp ${file}.gz s3://${S3_BUCKET}/live_db/
rm ${file}.gz
else
echo "Error backing up mysql"
exit 255
fi

备份服务器和实时服务器共享相同的AWS硬件配置:16GB RAM、4 CPU、100GB SSD。

这些是屏幕截图和数据:

在Live服务器上调试的屏幕截图和查询:信息模式表:

https://i.imgur.com/RnjQwbP.png

显示全局状态:

https://pastebin.com/raw/MuJYwnsm

显示全局变量:

https://pastebin.com/raw/wdvn97XP 

在备份服务器上调试的屏幕截图和查询:

https://i.imgur.com/rB7qcYU.png
https://pastebin.com/raw/K7vHXqWi
https://pastebin.com/raw/PR2gWpqe

服务器工作负载几乎可以忽略不计。一直没有负载,我也通过AWS监控面板进行了监控,这是占用超过所需资源服务器的唯一原因,这样它就永远不会耗尽。我用了16GB的RAM和4个CPU,这是绰绰有余的。AWS监控面板显示,最大使用率很少为6%,而最大使用率约为1%。

Analysis of GLOBAL STATUS and VARIABLES:

观察结果:

  • 版本:10.3.32-MariaDB-0ubuntu0.20.04.1-log
  • 16 GB RAM
  • 正常运行时间=3d 05:41:50
  • 42.1 QPS

更重要的问题:

一些提高内存利用率的设置建议:

key_buffer_size = 20M
innodb_buffer_pool_size = 8G
table_open_cache = 300
innodb_open_files = 1000
query_cache_type = OFF
query_cache_size = 0

其他原因的一些设置建议:

eq_range_index_dive_limit = 20
log_queries_not_using_indexes = OFF

建议使用slowlog(long_query_time=1)来定位恶意查询。然后我们可以讨论如何改进它们。http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

详细信息和其他观察结果:

( (key_buffer_size - 1.2 * Key_blocks_used * 1024) ) = ((512M - 1.2 * 8 * 1024)) / 16384M = 3.1%—在key_buffer中浪费的RAM百分比。--减小key_buffer_size(现在为536870912)。

( Key_blocks_used * 1024 / key_buffer_size ) = 8 * 1024 / 512M = 0.00%—使用的key_buffer的百分比。高水位线。--降低key_buffer_size(现在为536870912)以避免不必要的内存使用。

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((512M / 0.20 + 128M / 0.70)) / 16384M = 16.7%——应该使大部分可用的ram可用于缓存。--http://mysql.rjweb.org/doc.php/memory

( table_open_cache ) = 16,293—要缓存的表描述符数--几百通常是好的。

( innodb_buffer_pool_size ) = 128M——InnoDB数据+索引缓存--1.28亿美元(一个旧的默认值)少得可怜。

( innodb_buffer_pool_size ) = 128 / 16384M = 0.78%——用于InnoDB缓冲池的RAM百分比--设置为可用RAM的70%左右。(越低效率越低;交换风险越高。)

( innodb_lru_scan_depth ) = 1,024--";InnoDB:page_cleaner:1000ms预期循环占用"可以通过降低lru_scan_depth 来修复

( innodb_io_capacity ) = 200——冲洗时,使用这么多IOP。--阅读可能缓慢或尖锐。

( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10——容量:最大/普通--建议2。最大值应该大约等于I/O子系统可以处理的IOP。(如果驱动器类型未知,2000/200可能是一对合理的驱动器。)

( Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests ) = 3,565,561,783 / 162903322931 = 2.2%—必须命中磁盘的读取请求--如果您有足够的RAM,请增加innodb_buffer_pool_size(现在为134217728)。

( Innodb_pages_read / Innodb_buffer_pool_read_requests ) = 3,602,479,499 / 162903322931 = 2.2%—必须命中磁盘的读取请求--如果您有足够的RAM,请增加innodb_buffer_pool_size(现在为134217728)。

( Innodb_buffer_pool_reads ) = 3,565,561,783 / 279710 = 12747 /sec—缓冲池中的缓存未命中。--增加innodb_buffer_pool_size(现在是134217728)?(HDD的限制为~100,SSD的限制为-1000。)

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((3565561783 + 1105583) ) / 279710 = 12751 /sec—InnoDB I/O--增加innodb_buffer_pool_size(现在是134217728)?

( Innodb_buffer_pool_read_ahead_evicted ) = 5,386,209 / 279710 = 19 /sec

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 1,564,913,152 / (279710 / 3600) / 2 / 48M = 0.2——比值--(见会议记录)

( innodb_flush_method ) = innodb_flush_method = fsync——InnoDB应该如何请求操作系统写入块。建议使用O_DIRECT或O_ALL_DIRECT(Percona)以避免双重缓冲。(至少适用于Unix。)有关O_ALL_DIRECT 的警告,请参阅chrischandler

( default_tmp_storage_engine ) = default_tmp_storage_engine =

( innodb_flush_neighbors ) = 1——将块写入磁盘时的一个小优化。--对SSD驱动器使用0;1用于HDD。

CCD_ 19——如果>100%,需要更多的io_capacity。--如果驱动器能够处理,则增加innodb_io_容量(现在为200)。

( innodb_io_capacity ) = 200--磁盘上每秒的I/O操作数。100表示慢速驱动器;200,用于旋转驱动器;1000-2000适用于固态硬盘;乘以RAID因子。

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON——是否使用自适应散列(AHI)。--ON表示大部分只读;DDL重关闭

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON-通常应为ON。--在某些情况下OFF更好。另请参见innodb_adaptive_hash_index_parts(现在为8)(5.7.9之后)和innodb_daptive_hash_index_partions(MariaDB和Percona)。ON被卷入了罕见的崩溃(错误73890)。10.5.0决定默认关闭。

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF——是否记录所有死锁。--如果您受到死锁的困扰,请打开它。注意:如果您有很多死锁,这可能会向磁盘写入很多内容。

( innodb_ft_result_cache_limit ) = 2,000,000,000 / 16384M = 11.6%—FULLTEXT结果集的字节限制。(可能未预先分配,但正在增长?)--降低设置。

( local_infile ) = local_infile = ON--local_infile(现在为ON)=ON是潜在的安全问题

( Qcache_lowmem_prunes ) = 6,329,393 / 279710 = 23 /sec——QC空间不足--增加query_cache_size(现在为16777216)

( Qcache_lowmem_prunes/Qcache_inserts ) = 6,329,393/7792821 = 81.2%——去除率(内存不足需要修剪的频率)

( Qcache_hits / Qcache_inserts ) = 1,619,341 / 7792821 = 0.208—命中插入比—高即好--请考虑关闭查询缓存。

( Qcache_hits / (Qcache_hits + Com_select) ) = 1,619,341 / (1619341 + 9691638) = 14.3%—命中率—使用QC的SELECT--请考虑关闭查询缓存。

( Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) ) = 1,619,341 / (1619341 + 7792821 + 278272) = 16.7%—查询缓存命中率--可能最好关闭QC。

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (16M - 1272984) / 3058 / 16384 = 0.309--query_alloc_block_size与公式--调整query_alloc_block_size(现在为16384)

CCD_ 32——创建磁盘的频率";temp";表作为复杂SELECT的一部分--增加tmp_table_size(现在为16777216)和max_heap_table_size(下面为1677722016)。当使用MEMORY而不是MyISAM时,请检查临时表的规则。也许轻微的架构或查询更改可以避免MyISAM。更好的索引和重新表述查询更有可能有所帮助。

( Created_tmp_disk_tables / Questions ) = 1,667,989 / 11788712 = 14.1%—磁盘tmp表上需要的查询数。--更好的索引/无斑点等

( Created_tmp_disk_tables / Created_tmp_tables ) = 1,667,989 / 4165525 = 40.0%—溢出到磁盘的临时表的百分比--可能会增加tmp_table_size(现在为16777216)和max_heap_table_size(目前为1677722016);提高指标;避免斑点等。

( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 473 - 0 ) / ( 473 + 0 ) = 100.0%-你准备好的发言结束了吗?--添加关闭。

( Com_stmt_close / Com_stmt_prepare ) = 0 / 473 = 0——Prepared语句应为Closed。--检查是否所有Prepared语句都是";关闭";。

( binlog_format ) = binlog_format = MIXED—语句/行/混合。--5.7(10.3)优选ROW

CCD_ 38——用于定义";"慢";查询--建议2

( Subquery_cache_hit / ( Subquery_cache_hit + Subquery_cache_miss ) ) = 0 / ( 0 + 1800 ) = 0——子查询缓存命中率

( log_queries_not_using_indexes ) = log_queries_not_using_indexes = ON——是否将其包含在slowlog中。--这扰乱了慢日志;关闭它,这样您就可以看到真正的慢速查询。并减少long_query_time(现在为5)以捕获最有趣的查询。

( back_log ) = 80——(自5.6.6起自动调整大小;基于最大连接数)--当进行大量连接时,提高到最小值(150,最大连接数(现在为151))可能会有所帮助。

异常小:

Delete_scan = 0.039 /HR
Handler_read_rnd_next / Handler_read_rnd = 2.06
Handler_write = 0.059 /sec
Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ) = 97.9%
Table_locks_immediate = 2.6 /HR
eq_range_index_dive_limit = 0

异常大:

( Innodb_pages_read + Innodb_pages_written ) / Uptime = 12,883
Com_release_savepoint = 5.5 /HR
Com_savepoint = 5.5 /HR
Handler_icp_attempts = 110666 /sec
Handler_icp_match = 110663 /sec
Handler_read_key = 62677 /sec
Handler_savepoint = 5.5 /HR
Handler_tmp_update = 1026 /sec
Handler_tmp_write = 40335 /sec
Innodb_buffer_pool_read_ahead = 131 /sec
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 43524924.1%
Innodb_data_read = 211015030 /sec
Innodb_data_reads = 12879 /sec
Innodb_pages_read = 12879 /sec
Innodb_pages_read + Innodb_pages_written = 12883 /sec
Select_full_range_join = 1.1 /sec
Select_full_range_join / Com_select = 3.0%
Tc_log_page_size = 4,096
innodb_open_files = 16,293
log_slow_rate_limit = 1,000
query_cache_limit = 3.36e+7
table_open_cache / max_connections = 107

异常字符串:

Innodb_have_snappy = ON
Slave_heartbeat_period = 0
Slave_received_heartbeats = 0
aria_recover_options = BACKUP,QUICK
innodb_fast_shutdown = 1
log_output = FILE,TABLE
log_slow_admin_statements = ON
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
sql_slave_skip_counter = 0
time_zone = +05:30

每秒速率=RPS

为您的"备份"AWS实例参数组考虑的建议

innodb_buffer_pool_size=10G  # from 128M to reduce innodb_data_reads RPS of 16
innodb_change_buffer_max_size=50  # from 25 percent to speed up INSERT completion
innodb_buffer_pool_instances=3  # from 1 to reduce mutex contention
innodb_write_io_threads=16  # from 4 for your intense data INSERT operations
innodb_buffer_pool_dump_pct=90  # from 25 percent to reduce WARM UP delays
innodb_fast_shutdown=0  #  from 1 to help avoid RECOVERY on instance START

您应该会发现这些更改减少了BACKUP实例所需的刷新DATA时间。您的LIVE实例具有不同的操作特性,但应该应用所有这些建议以及其他建议。请查看个人资料以获取联系信息,并与我们联系以获得更多帮助。

您提到的复制可能会被忽略,因为您不能在主服务器和从服务器上都有1的SERVER_ID,复制才能生存。您的LIVE服务器不能是MASTER,因为LOG_BIN由于第一个原因而关闭。

现场观察,com_begin计数为30,com_commit在正常运行3天后为0。通常我们发现commit和com_begin是一样的。有人忘了提交数据吗?

com_savepoint报告了430次操作。com_rollback_to_savepoint报告了430次操作。我们通常不会看到3 dqy中每个保存点都有回滚。

com_stmt_prepare报告了473次操作。com_stmt_execute报告了1211个操作。com_stmt_close报告了0个操作。忘记关闭准备好的语句当完成时,会留下本可以释放的资源。

handler_rollback计数器961。正常运行3天似乎不寻常。

slow_queries在超过5秒的3天内计数为87338。log_slow_verbosity=query_plan,explain将帮助您的团队确定缓慢的原因。您的慢速查询日志已打开。

Kerry,祝你和你的团队一切顺利。

相关内容

  • 没有找到相关文章

最新更新