MySQL optimization scheme for large data updates

1. Business needs
Due to the reasons for the business is not synchronized, resulting in user management side Oracle and 139 mailbox side MySQL user status inconsistent, now need to take the user management side, in addition to add synchronization program, need to make 139 mailbox side of the data update.
The content that is included in the 139 insert does not exist for the user, updates and properties that are not the same as the pipe side.
Initial steps to get 139 full-volume users and status in CSV format, submitted to the DBA colleagues in the tube, and compare the same in CSV format for the users and attributes that need to be updated and inserted.

2. Introduction to the Environment
2.1, MySQL mailbox side and Oracle on the side of the network is not interoperable, you need to transfer files via FTP server
2.2, the user volume is huge, the library table according to the method of the related route is divided into the 8*128 table, the data with the tube feedback is only a CSV file, the operation needs to carry on the route the action
2.3, feedback information, need to change the line has 5100w, need to insert the row has 600w

3, the failure of the program
3.1, according to the general thinking, because of the existence of routing policy, plus the user information exists in the CSV file, you can consider using the file for a loop to get the data for each row, route to the related table, and then update the operation
3.2, in order to speed up processing, consider increasing the number of processes, fragmented files into multiple small files, each small file for separate processing
3.3, Phenomenon one: Host CPU utilization is relatively high, but the IO use is quite low (possibly due to 20 processes in parallel, not much attention)
Phenomenon Two: Data update slowly, a full day of time updated data is 200w, it takes one months to complete the task of the "black line"

4, the concept of new solutions
Reflection reasons, host performance has shown that several tasks are assigned to the CPU-intensive business, and this update action is obviously an IO-intensive transaction, how can the cart before the horse, check the implementation of the script,
Enlightened, the script needs to use awk to get the user ID in order to get the route, and the regular expression is a CPU-intensive command, so the current CPU load is high, and the process spends a lot of time on the regular expression, so the database update is very slow
So how do we make improvements? Lenovo to insert optimization scheme
Because the time is mainly spent on the route, plus each row of data need to do a database connection and final commit, resulting in a slow database update phenomenon. The problem that should be addressed by the new scenario is to deal with the routing process and the bottleneck of multiple connection submissions in advance.
In order to resolve multiple commits to create a temporary table using a connection query, the CSV file can also be used to import the database using load data, but how to determine the route, two scenarios:
Each table corresponds to an Update table, the total amount will be 128*8 Zhang, very unrealistic.
Another scenario is to add the database and table fields to the staging table, which can be implemented through stored procedures.
Attempt to use the temporary table scheme (the following scenarios are supported by my boss)

5, the implementation of new programmes
5.1. Create a temporary table

CREATE TABLE ' insert_mobilenumber_6836653 ' (' Id ' int (one) not null auto_increment, ' mobileusernumber ' varchar () NOT NULL , ' Cardtype ' int (2) default null, ' Provcode ' varchar () default NULL, ' AreaCode ' varchar (default) NULL, ' lastaction ' int (4) default null, ' Status ' int (2) default null, ' Reasonid ' int (4) default null, ' Registertype ' int (4) default NULL, ' Createti Me ' datetime default NULL, ' Modifytime ' datetime default NULL, ' Registertime ' datetime default NULL, ' Freezetime ' datetime D Efault null, ' unfreezetime ' datetime default NULL, ' Expiretime ' datetime default NULL, ' dbid ' int (one-by-one) default null, ' Tbid ' in T (one) default Null,primary KEY (' Id ')) engine=innodb default Charset=utf8; CREATE TABLE ' update_mobilenumber_51069865 ' (' Id ' int (one) not NULL auto_increment, ' mobileusernumber ' varchar (20) Default NULL, ' Status ' int (2) default null, ' Registertime ' datetime default NULL, ' Freezetime ' datetime default NULL, ' Unfreezetime ' datetime default NULL, ' Expiretime ' datetime default NULL, ' dbid ' int (one) defaultNULL, ' tbid ' int (one) default Null,primary KEY (' Id ')) engine=innodb default Charset=utf8; 

　　

5.2. Load Import:

mysql> load Data infile "/home/mysql/itkeeper/20170424/replace.txt" into table insert_mobilenumber_6836653 fields Terminated by ', ', ' (Mobileusernumber,cardtype,provcode,areacode,lastaction,status,reasonid,registertype, Createtime,modifytime,registertime,freezetime,unfreezetime,expiretime); Query OK, 6836653 rows affected, 65535 warnings (6 min 35.89 sec) records:6836653 deleted:0 skipped:0 warnings:24782499 ysql> load Data infile "/home/mysql/itkeeper/20170424/zhao_comm.txt" into table update_mobilenumber_51069865 fields Terminated by ', ', ' (mobileusernumber,status,registertime,freezetime,unfreezetime,expiretime); Query OK, 51069865 rows affected, 65535 warnings (min 31.30 sec) records:51069865 deleted:0 skipped:0 warnings:99206 015mysql> mysql>

Io is coming up, too.

device:rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await SVCTM%utilsdb 0.00 7654.00 6.00 1098.00 248.00 70072. XX 63.70 2.46 2.23 0.54 59.50SDC 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00SDA 0.00 83.50 0.00 70.50 0.00 1232 .17.48 0.03 0.40 0.03 0.20sdd 0.00 8124.00 6.50 1096.00 272.00 73800.00 67.19 2.44 2.22 0.56 61.65sde 0.00 0.00 0.00 0. 00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

5.3, update dbid,tbid;

--Using the ID self-increment feature:

create PROCEDURE p_update_insert_mobilenumber () Begindeclarev int default 0; While v < 7000000 do update insert_mobilenumber_6836653 set dbid = mod (mobileusernumber,8) + 1, tbid = mod (Mobileusernu mber,127) + 1 where ID >= V and ID < V + 10000;set v = v + 10000;end while;end; CREATE PROCEDURE p_update_update_mobilenumber () begindeclarev int default 0;while v < 52000000 do update Update_mobilen  umber_51069865 Set dbid = mod (mobileusernumber,8) + 1, tbid = mod (mobileusernumber,127) + 1 where ID >= V and ID < V + 10000;set v = v + 10000;end while;end; Mysql> call P_update_insert_mobilenumber; Query OK, 0 rows affected (8 min 26.29 sec) mysql> mysql> mysql> call P_update_update_mobilenumber; Query OK, 0 rows affected (min 9.58 sec) mysql>mysql> CREATE index idx_dbid_tbid on update_mobilenumber_51069865 (d Bid,tbid); Query OK, 0 rows affected (7 min 6.12 sec) records:0 duplicates:0 warnings:0mysql> 

5.4, to update, 4 processes, two libraries per process, concurrent execution, 51 million, approximately about the hour or so of the update completed.
----This step, if there is synchronization for the library, such as to synchronize Cnmn_db_001-8, then the following method will lead to loss of data synchronization, the normal step is to use cnmn_db_001 first, and then to update.

#/bin/bashfor i in ' seq 1 2 '; Dofor x in ' seq 1 + ' do begin_time= ' date +%y%m%d%h%m%s ' mysql-u-e ' UPDATE cnmn_db_00$i.mail_usernumber_info_$x A, MYEFZ . update_mobilenumber_51069865 B Set A.status=b.status,a.freezetime=b.freezetime,a.unfreezetime=b.unfreezetime,a. Expiretime=b.expiretime, A.modifytime=now () where b.dbid = $i and b.tbid= $x and b.mobileusernumber=a.usernumber; "; End_time= ' Date +%y%m%d%h%m%s ' total_time= ' expr $END _time-$BEGIN _time ' echo ' db_ ' $i '. Table_ "$x" is ok| " $BEGIN _time "|" $END _time "|" $TOTAL _time >> Result_log.logdone;done;

5.5, insert data processing, 4 processes, two libraries per process, concurrent execution.

#/bin/bashfor i in ' seq 1 2 '; Dofor x in ' seq 1 ';d o mysql-u-E "use cnmn_db_00 ' $i ', insert into Mail_usernumber_info_ ' $x ' (USERNUMBER,CARDTYPE,PROVC Ode,areacode,lastaction,status,reasonid,registertype,createtime,modifytime,registertime,freezetime, Unfreezetime,expiretime) Select Mobileusernumber,cardtype,provcode,areacode,lastaction,status,reasonid, Registertype,createtime,modifytime,registertime,freezetime,unfreezetime,expiretime from Myefz.insert_ mobilenumber_6836653 where dbid = ' $i ' and tbid= ' $x ' and Mobileusernumber not in (select Usernumber from Mail_usernumber_i Nfo_ ' $x '); "Done;done;

MySQL optimization scheme for large data updates