mysql千万数据快速插入-实战

ShayneLee8

已于 2024-12-20 18:27:46 修改

阅读量516

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql 数据库

于 2023-11-10 00:25:25 首次发布

本文链接：https://blog.csdn.net/ShayneLee8/article/details/134322678

mysql 专栏收录该内容

5 篇文章

订阅专栏

文章目录

前言
环境
一、配置
二、效果
整体概况
内存问题

前言

数据量太大了，每天半夜要同步很大数据到 mysql 数据库，其中一张表就上2千万，总计上亿条数据。同步任务每天0点之后开始任务（因为到0之后才能统计前一天数据），业务需求每天早上8点要看到数据。
so, 解决mysql 插入效率就成了重中之重

环境

虚拟机
linux
cpu: 16核
内存：64G
mysql5.7

一、配置

附：配置内容有点多，部分属于主从复制相关，mysql主从复制-使用心得
mysql 默认配置对于插入速度比较慢，所以需要做相关的调整。
我的主要部分如下：

#    调优
back_log=3000
binlog_cache_size=2M
binlog_order_commits=OFF
bulk_insert_buffer_size=64M
innodb_autoextend_increment=64
innodb_buffer_pool_size=32G
innodb_checksums=OFF
innodb_doublewrite=OFF
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_io_capacity_max=2000
innodb_large_prefix=OFF
innodb_log_buffer_size=16M
innodb_log_file_size=1500M
innodb_read_io_threads=32
innodb_write_io_threads=32
innodb_support_xa=OFF
interactive_timeout=1800
join_buffer_size=1M
key_buffer_size=64M
log_output=FILE
log_slave_updates=0
master_info_repository=TABLE
max_allowed_packet=1024M
max_connect_errors=10
max_connections=1000
max_heap_table_size=64M
open_files_limit=20000
query_cache_type=1
query_cache_limit=2M
query_cache_size=256M
read_buffer_size=1M
read_rnd_buffer_size=1M
relay_log_info_repository=TABLE
relay_log_recovery=ON
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=16
slave_pending_jobs_size_max=1024M
skip_external_locking
skip-name-resolve
sort_buffer_size=2M
sync_binlog=0
thread_cache_size=100
tmp_table_size=32M
wait_timeout=1800

二、效果

2千多万条数据（20个字段的大表），用时 11771s, 合为 3.3个小时（没有优化前，20小时左右），完全满足需求。
在这里插入图片描述

整体概况

1.配置上牺牲了一点点数据安全性来获得更高效的数据插入，如果系统突然宕机或者其它客观因素是可能影响数据完整性的，这个对于我目前业务不影响，我认为这个事情发生概率很小。上述配置使用要具体根据自己实际情况衡量。

2.开始数据库服务器内存一直满载，自己也没注意看，就以为是mysql的问题，但是后面仔细看 top 命令的内存情况，才发现有个 auditd 搞事情，重启后内存一下降了很多，网上有人说我使用的版本有问题。
在这里插入图片描述

内存问题

使用一段时间发现，主库的内存一直居高不下，后面查找网上资料得知默认的mysql5.7内存回收算法有问题，

手动清理（不建议）

手动清理，替换 1242030 为实际 mysql 进程id

sudo  gdb  --batch --pid  1242030 --ex 'call  (int)malloc_trim(0)'

清理前
在这里插入图片描述

在这里插入图片描述
清理后

jemalloc

还有一种方式使用 jemalloc
在这里插入图片描述

使用 jemalloc

创建软链接：

ln -s /usr/local/lib/libjemalloc.so.2 /usr/lib64/libjemalloc.so.1

编辑 my.cnf
添加

[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so.1

重启数据库服务
查看已经使用 jemalloc
在这里插入图片描述

—————— 但行好事莫问前程，你若盛开蝴蝶自来