Since graduation, I have been exposed to more than 300,000 pieces of data imported into MySQL for the first time (a little low). That is, when SF Express company accesses our EMM products, it needs to import the employee data in AD into MySQL. Therefore, connector, the module in charge of the head of the building, comes into use. Thanks to the efforts of the developer, the online data synchronization code went from an initial performance of nearly 16 hours (and other problems, which will be discussed later) to a final performance optimization of 25 minutes.

Advertising, the building owner made their own wheels, interested in please click github.com/haifeiWu/li…

The code goes straight to Jenkins

Before the landlord is responsible for the connector modules experienced the largest amount of data is only of thousands, of course, in the face of thousands of data and the code is running fast, no impact, however, when in motion formal environment online for the first time, because the data volume is larger, the original poster is serial execution of code, transaction stay time is very long, Hence the following error message:

Lock wait timeout exceeded; try restarting transaction
Copy the code

MySQL > select * from ‘lock’

show OPEN TABLES where In_use > 0;
Copy the code

In addition, there are three tables under information_SCHEMA that make it easier to monitor current transactions and analyze possible problems :INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS (problem resolution methods).

More commonly used columns:

  • Trx_id: unique transaction ID inside InnoDB storage engine
  • Trx_status: indicates the current transaction status
  • Trx_status: transaction start time
  • Trx_requested_lock_id: ID of the lock waiting for a transaction
  • Trx_wait_started: indicates the start time of the transaction wait
  • Trx_weight: the weight of a transaction, reflecting the number of rows modified and locked by a transaction. If a deadlock is found and needs to be rolled back, the lower the weight value is rolled back
  • Trx_mysql_thread_id: indicates the ID of the process in MySQL, which corresponds to the ID value in show processList
  • Trx_query: SQL statement used to run a transaction

Kill process ID, the root cause of the above error occurs when the operation of the business logic code on the database ignores a large amount of data. For example, when the data volume is relatively large, it will appear that the record has just been modified, and then modify again, the above problem will occur.

Code optimization process

Use thread pools and execute concurrently to improve efficiency

Due to the large amount of data, the first way to think of is to get the data after the data is divided into N copies, by a number of threads concurrent implementation of the import operation. When dealing with multithreading problems, the shared data structure like Map, List should use the data structure provided by the JDK under the current package, in addition to the operation of the database should be locked, the main use is the JDK provided by ReentrantLock. Use JDK’s own JVisualVM to monitor the code and find the optimal number of threads. The following is the monitored data.

Here is the thread pool creation code

ThreadFactory namedThreadFactory = new ThreadFactoryBuilder()
                .setNameFormat("demo-pool-%d").build();
ExecutorService singleThreadPool = new ThreadPoolExecutor(1, 1,0L, TimeUnit.MILLISECONDS,
                new LinkedBlockingQueue<Runnable>(1024), namedThreadFactory, new ThreadPoolExecutor.AbortPolicy());

singleThreadPool.execute(()-> System.out.println(Thread.currentThread().getName()));
singleThreadPool.shutdown();
Copy the code

Sure enough, after multithreading, the data insertion efficiency dropped from a few dozen hours to three hours, and then it didn’t work as well as we expected, so we moved on.

Use Druid to monitor SQL where problems are found

  • Configure druid monitoring, add the configuration to your application’s web.xml, and release it/druidThe intercept
<! <filter> <filter-name>DruidWebStatFilter</filter-name> <filter-class>com.alibaba.druid.support.http.WebStatFilter</filter-class> <init-param> <param-name>exclusions</param-name> <param-value>*.js,*.gif,*.jpg,*.png,*.css,*.ico,*.jsp,/druid/*,/download/*</param-value> </init-param> <init-param> <param-name>sessionStatMaxCount</param-name> <param-value>2000</param-value> </init-param> <init-param> <param-name>sessionStatEnable</param-name> <param-value>true</param-value>
		</init-param>
		<init-param>
			<param-name>principalSessionName</param-name>
			<param-value>session_user_key</param-value>
		</init-param>
		<init-param>
			<param-name>profileEnable</param-name>
			<param-value>true</param-value> </init-param> </filter> <filter-mapping> <filter-name>DruidWebStatFilter</filter-name> <url-pattern>/*</url-pattern> </filter-mapping> <servlet> <servlet-name>DruidStatView</servlet-name> <servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class> <! --<init-param> <param-name>allow</param-name> <param-value>*.*.*.*</param-value> </init-param>--> <init-param> <! <param-name> <param-name> <param-value>true</param-value> </init-param> <init-param> <! Druid </param-name> <param-value> </init-param> <init-param> <! -- Password --> <param-name>loginPassword</param-name> <param-name> druid</ param-name> </init-param> </servlet> <servlet-mapping> <servlet-name>DruidStatView</servlet-name> <url-pattern>/druid/*</url-pattern> </servlet-mapping>Copy the code
  • Configure the database connection pool
<! -- Data source configuration, using BoneCP database connection pool --> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <! DriverClass --> <property name="driverClassName" value="${jdbc.driver}"/ > <! -- Basic properties URL, user, password --> <property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}"/ > <! -- Configure initial size, min, Max --> <property name="initialSize" value="${jdbc.pool.init}" />
		<property name="minIdle" value="${jdbc.pool.minIdle}" />
		<property name="maxActive" value="${jdbc.pool.maxActive}"/ > <! -- Set the timeout for waiting to get connections --> <property name="maxWait" value="60000"/ > <! -- Configure how often to check for idle connections to close, in milliseconds --> <property name="timeBetweenEvictionRunsMillis" value="60000"/ > <! -- Set the minimum time for a connection to live in the pool, in milliseconds --> <property name="minEvictableIdleTimeMillis" value="300000" />

		<property name="validationQuery" value="${jdbc.testSql}" />
		<property name="testWhileIdle" value="true" />
		<property name="testOnBorrow" value="true" />
		<property name="testOnReturn" value="false"/ > <! -- Configure the filters for monitoring statistics interception --> <property name="filters" value="stat" />
	</bean>
Copy the code
  • Visit http://ip/xxx/druid, enter your username and password to log in, and you will see the image below

  • Use Druid’s SQL monitoring to identify problematic SQL statements and optimize SQL

Check the gc status of the program by command

Run jstat -gc pid to check the gc status of the program. The following is the gc status of the main program after data synchronization

S0C, S1C, S0U, and S1U: Survivor 0/1 Capacity and Used EC, EU: Eden Capacity and Used OC, OU: Capacity and Used of the elderly generation PC, PU: Capacity and Used of the permanent generation YGC, YGT: FGC and FGCT: Full GC counts and Full GC durationCopy the code

summary

Through the optimization process above, the data cohabitation efficiency of the main connector of the building also decreased from hour level to minute level, and the synchronization time of 300,000 + thousand can be completed in 25 minutes. Classics this one battle, building Lord is harvest full. Holding a pious attitude of learning, not floating not impetuous, happy growth.