問(wèn)題

最近遇到 2 個(gè)超大 sql 文件導(dǎo)入，好一通折騰

文檔在哪里

調(diào)優(yōu)參數(shù)太多，文檔都看不過(guò)來(lái)
找到這些參數(shù)也費(fèi)勁,

1. ubuntu 在 /etc/mysql/mysql.conf.d/mysqld.cnf 中找到這個(gè)鏈接

   ......
   #
   # The MySQL  Server configuration file.
   #
   # For explanations see
   # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
   

2. centos 7 在 /etc/my.cnf 中找到這個(gè)鏈接

    # For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
   

3. 官方調(diào)優(yōu)文檔
   14.15 InnoDB Startup Options and System Variables

4. 耐心去閱讀文檔中那么多參數(shù)，需要勇氣！
   最終我也是從這里 mysql使用多cpu抄來(lái)的

    # wzh 20230814# Set the number of CPUs to be used by MySQL #  # to half of the total available CPUsinnodb_thread_concurrency = 2## Set the number of buffer pool instancesinnodb_buffer_pool_instances = 2# #  # Set the size of the query cachequery_cache_size = 64M## wzh 20230814 bulk_insert_buffer_size = 2Ginnodb_log_buffer_size = 2Ginnodb_autoinc_lock_mode = 2#
   

調(diào)優(yōu)后 三種導(dǎo)入方式對(duì)比

1. 方式一: 使用 mysql source 命令，直接導(dǎo)入一個(gè)大文件 (18G)

   為了統(tǒng)計(jì)用時(shí)和繪畫(huà)內(nèi)部?jī)?yōu)化，將實(shí)際mysqldump 文件包含到 all.sql

    # cat all.sqlSET GLOBAL innodb_lru_scan_depth=256;set session sql_mode = 'ALLOW_INVALID_DATES';SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;show global variables like 'innodb_lru_scan_depth';show variables like '%sql_mode%';  show variables like '%autocommit';show variables like '%unique_checks';show variables like '%foreign_key_checks';select now();-- one file 18Gsource /home/XXXdata/20191230_135112.sql;show global variables like 'innodb_lru_scan_depth';show variables like '%sql_mode%';show variables like '%autocommit';show variables like '%unique_checks';show variables like '%foreign_key_checks';select now();
   

   導(dǎo)入過(guò)程

    mysql > tee out-20230816.txtmysql > source /home/XXXdata/all.sql
   

   查看結(jié)果

    # cat out-20230816.txt......| 2023-08-16 08:17:02 |......| 2023-08-16 14:26:40 |
   

   總共耗時(shí) 6 小時(shí) +

2. 方式2??: 使用shell 命令行后臺(tái)任務(wù)，直接導(dǎo)入一個(gè)大文件 (18G)

   為了統(tǒng)計(jì)用時(shí)和繪畫(huà)內(nèi)部?jī)?yōu)化，將實(shí)際mysqldump 文件包含到 all.sql

    # cat all.sql 同前一個(gè)方式
   

   shell 后臺(tái)任務(wù)

    # nohup  mysql -uroot -pPassword@123 --default-character-set=utf8 --force zXXX< /home/zXXX/all.sql > /home/out-20230817.txt 2>&1 &
   

   查看執(zhí)行結(jié)果

    # cat out-20230817.txt......2023-08-16 21:08:52......2023-08-17 01:43:14
   

   用時(shí) 大約5 小時(shí)

3. 方式三：SQLDumpSplitter切割 sql 文件 ( 每個(gè) 2G)，使用 mysql source 命令，

   切割后的結(jié)果

    # ls *.sql -l-rw-r--r--. 1 root root 2147482476 8月  10 14:13 20191230_135112_0.sql-rw-r--r--. 1 root root 2147482521 8月  10 14:15 20191230_135112_1.sql-rw-r--r--. 1 root root 2147482263 8月  10 14:17 20191230_135112_2.sql-rw-r--r--. 1 root root 2147482371 8月  10 14:19 20191230_135112_3.sql-rw-r--r--. 1 root root 2147481971 8月  10 14:21 20191230_135112_4.sql-rw-r--r--. 1 root root 2147481699 8月  10 14:24 20191230_135112_5.sql-rw-r--r--. 1 root root 2147482612 8月  10 14:25 20191230_135112_6.sql-rw-r--r--. 1 root root 2147482594 8月  10 14:27 20191230_135112_7.sql-rw-r--r--. 1 root root  959044232 8月  10 14:28 20191230_135112_8.sql-rw-r--r--. 1 root root       1096 8月  16 14:14 all.sql
   

   all.sql

    # cat all.sql SELECT DATABASE();select now();SET GLOBAL innodb_lru_scan_depth=256;set session sql_mode = 'ALLOW_INVALID_DATES';SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;show global variables like 'innodb_lru_scan_depth';show variables like '%sql_mode%';  show variables like '%autocommit';show variables like '%unique_checks';show variables like '%foreign_key_checks';-- 00source /home/XXXdata/20191230_135112_0.sql ;-- 01source /home/XXXdata/20191230_135112_1.sql ;-- 02source /home/XXXdata/20191230_135112_2.sql ;-- 03source /home/XXXdata/20191230_135112_3.sql ;-- 04source /home/XXXdata/20191230_135112_4.sql ;-- 05source /home/XXXdata/20191230_135112_5.sql ;-- 06source /home/XXXdata/20191230_135112_6.sql ;-- 07source /home/XXXdata/20191230_135112_7.sql ;-- 08source /home/XXXdata/20191230_135112_0.sql ;-- ALL OKshow global variables like 'innodb_lru_scan_depth';show variables like '%sql_mode%';show variables like '%autocommit';show variables like '%unique_checks';show variables like '%foreign_key_checks';SELECT DATABASE();select now();
   

   導(dǎo)入過(guò)程

    mysql > tee out-20230816.txtmysql > source /home/XXXdata/all.sql
   

   查看結(jié)果

    cat out-20230816.txt......| 2023-08-16 08:17:02 |......| 2023-08-16 13:15:21 | 
   

   總共耗時(shí)大約 5 小時(shí)

不調(diào)優(yōu)，使用shell 命令行后臺(tái)任務(wù)，直接導(dǎo)入一個(gè)大文件 (18G)

1. 注釋掉 my.cnf 中的調(diào)優(yōu)， restart mysqld 服務(wù)

    # cat /etc/my.cnf# wzh 20230814# Set the number of CPUs to be used by MySQL #  # to half of the total available CPUs# innodb_thread_concurrency = 2## Set the number of buffer pool instances#innodb_buffer_pool_instances = 2#  # Set the size of the query cache# query_cache_size = 64M## wzh 20230814 # bulk_insert_buffer_size = 2G# innodb_log_buffer_size = 2G# innodb_autoinc_lock_mode = 2# wzh 20230817# default-time-zone='Asia/Shanghai'explicit_defaults_for_timestamp=truelog_timestamps=SYSTEM
   

   這就和安裝完 mysql 5.7 之后，全部使用缺省值一樣了

2. shell 后臺(tái)任務(wù)

    nohup  mysql -uroot -pPassword@123 --default-character-set=utf8 --force zXXX< /home/zXXX/all.sql > /home/out-20230817.txt 2>&1 &
   

3. 查看結(jié)果

   cat /home/out-20230817.txt

   …
   2023-08-18 14:50:11
   …
   2023-08-18 19:43:37

   大約 5 小時(shí)，對(duì)比前面調(diào)優(yōu)沒(méi)有多大改進(jìn)？

原因分析

決定實(shí)際完成快慢的，是該進(jìn)程的 CPU 占用時(shí)間（ cputime ） ？

以下是中途記錄的一段

#  ps -eo pid,euid,euser,lstart,etime,cputime,cmd | grep mysql
...1877     0 root     Fri Aug 18 08:22:21 2023    01:20:06 00:00:59 mysql -uroot -px xxxxxxxxxx --default-character-set=utf8 --force zXXX
...

可以看到，本次運(yùn)行時(shí)間 01:20:06 ，CPU 占用時(shí)間 00:00:59 ，說(shuō)明效率很高！

之前曾經(jīng)有過(guò)運(yùn)行一晚上，實(shí)際 cputime 才 2 個(gè)小時(shí)，效率太低了！

總結(jié)

1. 切割 SQL 文件并不能顯著改善導(dǎo)入速度 （也許我切割到 2G 還是太大了，感覺(jué)應(yīng)該 1G ）

2. 使用 shell 命令行 和 mysql source 命令要快大約 20%-30%

3. 使用多 CPU 和增加緩存等辦法，沒(méi)有測(cè)出有明顯效果

4. 影響導(dǎo)入速度的還是導(dǎo)入過(guò)程中的錯(cuò)誤忽略 sql_mode = ‘ALLOW_INVALID_DATES’ 和 autocommit 等優(yōu)化

5. shell 命令行 加上 --force

6. 不要同時(shí)執(zhí)行 2 個(gè)或以上大任務(wù)，互相影響！

7. 提前判斷好需要的硬盤(pán)空間，不要等最后才知道 disk full ，前功盡棄！

   查看磁盤(pán)空間

   df -h

   如果可以找到原始的數(shù)據(jù)庫(kù)來(lái)源，查看數(shù)據(jù)庫(kù)文件大小

   $ sudo du -sh /var/lib/mysql/zXXX
   26G /var/lib/mysql/zXXX