接上文,本次在较高性能的X86物理机上,做真实生产环境的大数据量导入测试。
一、测试环境
■ CPU是24核,每核2线程,即48CPU
$ lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 48
On-line CPU(s) list: 0-47
Thread(s) per core: 2
Core(s) per socket: 12
座: 2
NUMA 节点: 2
厂商 ID: GenuineIntel
CPU 系列: 6
型号: 85
型号名称: Intel(R) Xeon(R) Gold 5118 CPU @ 2.30GHz
步进: 4
CPU MHz: 2301.000
■ 内存376G
$ free -g
total used free shared buff/cache available
Mem: 376 21 5 126 349 226
Swap: 16 0 16
■ 数据磁盘是SAS磁盘
Model: AVAGO HW-SAS3508 (scsi)
Disk /dev/sdb: 12.0TB
■ 磁盘IO写入性能测试,结果:1.1GB/s
[root@adg1:0 /u01]# time dd if=/dev/zero of=/u01/test bs=8k count=1000000
1000000+0 records in
1000000+0 records out
8192000000 bytes (8.2 GB) copied, 7.51518 s, 1.1 GB/s
■ 磁盘IO读取性能测试,结果:3.9GB/s
[root@adg1:0 /u01]# time dd if=/u01/test of=/dev/null bs=8k count=1000000
1000000+0 records in
1000000+0 records out
8192000000 bytes (8.2 GB) copied, 2.09241 s, 3.9 GB/s
■ 操作系统环境是红帽7.4
NAME="Red Hat Enterprise Linux Server"
VERSION="7.4 (Maipo)"
PRETTY_NAME="Red Hat Enterprise Linux Server 7.4 (Maipo)"
■ 文件系统采用xfs
Filesystem Type Size Used Avail Use% Mounted on
/dev/mapper/vgora-lv_u01 xfs 11T 792G 11T 8% /u01
■ db version
Server version: 5.5.56-MariaDB MariaDB Server
二、命令行导入方式
使用最原始的命令行方式测试,如下:
mysql -uroot -ppasswd' --default-character-set=utf8 dbname < mysql.sql
用一个简单的导入脚本,用于记录时间:
cat > test.sh << EOF
echo "Start ...("\`date "+%Y%m%d-%H:%M:%S"\`")"
mysql -uroot -p'passwd' --default-character-set=utf8 dbname < \$1
echo "Completed.("\`date "+%Y%m%d-%H:%M:%S"\`")"
EOF
chmod +x test.sh
测试结果如下:耗时57分钟
Start ...(20220227-12:22:12)
Completed.(20220227-13:19:04)
可见这个命令行导入方式,即使在实际的高性能生产环境,几千万条数据的导入,长达一个小时的时间,也是几乎无法接受的。但如果几百万行数据的导入,十几分钟时间还可以勉强接受。
三、LOAD DATA导入方式
关于LOAD DATA的详细介绍请网搜,此处不再赘述。
首先,修改原SQL文件格式为LOADDATA可用的csv文本格式:
sed -i "s/INSERT INTO \`tablename\` VALUES (//g" mysql.sql
sed -i "s/);//g" mysql.sql
经过以上自动编辑处理,原SQL文件内容成为如下格式:
'40601438', 'CF_0105', '121589425857.3000', '56814', null, '121589425857.3000'
'40601439', 'CF_0105', '113776588.1400', '56815', null, '113776588.1400'
然后,执行导入,如下所示:导入3000万条数据,耗时104秒
root@localhost:dbname> LOAD DATA LOCAL INFILE '/u01/mysql/mysql.sql'
-> INTO TABLE tablename
-> FIELDS TERMINATED BY ', '
-> ENCLOSED BY "'"
-> LINES TERMINATED BY '\n'
-> IGNORE 34 lines;
Query OK, 30578985 rows affected, 5932 warnings (1 min 43.76 sec)
Records: 30578985 Deleted: 0 Skipped: 0 Warnings: 5932
root@localhost:dbname> select count(*) from tablename;
+----------+
| count(*) |
+----------+
| 30578985 |
+----------+
创建索引耗时1分钟左右:
root@localhost:dbname> ALTER TABLE tablename ADD INDEX `master_id` (`master_id`);
Query OK, 0 rows affected (1 min 2.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:dbname> ALTER TABLE tablename ADD INDEX `code` (`code`);
Query OK, 0 rows affected (1 min 43.77 sec)
Records: 0 Duplicates: 0 Warnings: 0
四、结论
针对MySQL数据库,上千万条数据的大量导入,使用LOAD DATA方式导入,一般生产环境耗时1分钟左右。
文章评论