ELASTICSEARCH-JDBC Batch sync MySQL data failed

Recently, there are many fuzzy queries in the company system, the data volume is large, and the multi-table connection query will affect the performance. So consider using search engines to do all fuzzy query, thinking:

MySQL database data synchronization to the ES type, synchronous use of full-volume synchronization + timing increment, the application directly from ES to query the desired results.

Through a search, decided to use ELASTICSEARCH-JDBC for data synchronization, five or six tables of connection results, in the small amount of data development and testing environment is running normally, but in the data volume is larger than the performance test environment to do data synchronization, there will be problems, the following is the synchronization times of some errors, No useful things were found on GitHub. No one in the group is using it.

An error is the connection commit errors, the following is the interception of partial error messages

[13:17:17,678] [INFO] [Metrics.source.plain] [pool-3-thread-1] totalrows = 0, 7 minutes seconds = 479982 ms, 0 = 0.0 bytes bytes, 0.0 bytes = 0 avg Size, 0 DPS, 0 M b/S
[13:17:17,678] [INFO] [Metrics.sink.plain] [pool-3-thread-1] 7 minutes seconds = 479335 ms, submitted = 0, succeeded = 0, failed = 0, 0 = 0.0 bytes bytes, 0.0 byt ES = 0 avg size, 0 DPS, 0 mb/s
[13:19:40,589] [INFO] [Metrics.source.plain] [pool-3-thread-1] totalrows = 0, 8 minutes seconds = 524264 ms, 0 = 0.0 bytes bytes, 0.0 bytes = 0 avg Size, 0 DPS, 0 M b/S
[13:19:40,589] [INFO] [Metrics.sink.plain] [pool-3-thread-1] minutes seconds = 622247 ms, submitted = 0, succeeded = 0, failed = 0, 0 = 0.0 bytes bytes, 0.0 by TES = 0 avg Size, 0 DPS, 0 mb/s
[13:19:40,590] [INFO] [Metrics.source.plain] [pool-3-thread-1] totalrows = 0, minutes 0 seconds = 622895 ms, 0 = 0.0 bytes bytes, 0.0 bytes = avg Size, 0 DPS, 0 MB/s
[13:19:40,590] [INFO] [Metrics.sink.plain] [pool-3-thread-1] minutes seconds = 622247 ms, submitted = 0, succeeded = 0, failed = 0, 0 = 0.0 bytes bytes, 0.0 by TES = 0 avg Size, 0 DPS, 0 mb/s
[13:19:40,595] [INFO] [Metrics.source.plain] [pool-3-thread-1] totalrows = 0, minutes 0 seconds = 622900 ms, 0 = 0.0 bytes bytes, 0.0 bytes = avg Size, 0 DPS, 0 MB/s
[13:19:40,598] [INFO] [Metrics.sink.plain] [pool-3-thread-1] minutes seconds = 622256 ms, submitted = 0, succeeded = 0, failed = 0, 0 = 0.0 bytes bytes, 0.0 by TES = 0 avg Size, 0 DPS, 0 mb/s
[13:19:40,599] [INFO] [Metrics.source.plain] [pool-3-thread-1] totalrows = 0, minutes 0 seconds = 622904 ms, 0 = 0.0 bytes bytes, 0.0 bytes = avg Size, 0 DPS, 0 MB/s
[13:19:40,599] [INFO] [Metrics.sink.plain] [pool-3-thread-1] minutes seconds = 622257 ms, submitted = 0, succeeded = 0, failed = 0, 0 = 0.0 bytes bytes, 0.0 by TES = 0 avg Size, 0 DPS, 0 mb/s
[13:19:40,618] [WARN] [Importer.jdbc.source.standard] [Pool-2-thread-1] While closing read connection:communications link failure during commit (). Transaction resolution unknown.

Another overflow for thread memory:

[17:42:34,243] [INFO] [Metrics.source.plain] [pool-5-thread-1] totalrows = 0, 8 minutes seconds = 510305 ms, 0 = 0.0 bytes bytes, 0.0 bytes = 0 avg Size, 0 DPS, 0 M b/S
[17:43:33,523] [INFO] [Metrics.sink.plain] [Pool-5-thread-1] 8 minutes seconds = 516618 ms, submitted = 0, succeeded = 0, failed = 0, 0 = 0.0 bytes bytes, 0.0 byt ES = 0 avg size, 0 DPS, 0 mb/s
[17:46:00,561] [INFO] [Metrics.source.plain] [pool-5-thread-1] totalrows = 0, one minutes seconds = 679116 ms, 0 = 0.0 bytes bytes, 0.0 bytes = 0 avg Size, 0 DPS, 0 MB/s
[17:47:27,876] [INFO] [Metrics.sink.plain] [Pool-5-thread-1] minutes PNs seconds = 757511 ms, submitted = 0, succeeded = 0, failed = 0, 0 = 0.0 bytes bytes, 0.0 by TES = 0 avg Size, 0 DPS, 0 mb/s
[17:48:23,974] [INFO] [Metrics.source.plain] [pool-5-thread-1] totalrows = 0, minutes PNs seconds = 817186 ms, 0 = 0.0 bytes bytes, 0.0 bytes = 0 avg Size, 0 DPS, 0 MB/s
Exception in Thread "pool-5-thread-1" Exception in Thread "elasticsearch[importer][generic][t#3]" Java.lang.OutOfMemoryError:Java Heap Space
At Java.util.concurrent.locks.abstractqueuedsynchronizer$conditionobject.addconditionwaiter ( abstractqueuedsynchronizer.java:1855)
At Java.util.concurrent.locks.abstractqueuedsynchronizer$conditionobject.await (AbstractQueuedSynchronizer.java : 2035)
At Java.util.concurrent.scheduledthreadpoolexecutor$delayedworkqueue.take (scheduledthreadpoolexecutor.java:1081 )
At Java.util.concurrent.scheduledthreadpoolexecutor$delayedworkqueue.take (scheduledthreadpoolexecutor.java:809)
At Java.util.concurrent.ThreadPoolExecutor.getTask (threadpoolexecutor.java:1067)
At Java.util.concurrent.ThreadPoolExecutor.runWorker (threadpoolexecutor.java:1127)
At Java.util.concurrent.threadpoolexecutor$worker.run (threadpoolexecutor.java:617)
At Java.lang.Thread.run (thread.java:745)
Java.lang.OutOfMemoryError:Java Heap Space
At Java.util.concurrent.synchronousqueue$transferstack.snode (synchronousqueue.java:318)
At Java.util.concurrent.synchronousqueue$transferstack.transfer (synchronousqueue.java:361)
At Java.util.concurrent.SynchronousQueue.poll (synchronousqueue.java:941)
At Java.util.concurrent.ThreadPoolExecutor.getTask (threadpoolexecutor.java:1066)
At Java.util.concurrent.ThreadPoolExecutor.runWorker (threadpoolexecutor.java:1127)
At Java.util.concurrent.threadpoolexecutor$worker.run (threadpoolexecutor.java:617)
At Java.lang.Thread.run (thread.java:745)
Java.io.IOException:pool did not terminate
At Org.xbib.tools.JDBCImporter.shutdown (jdbcimporter.java:265)
At Org.xbib.tools.jdbcimporter$2.run (jdbcimporter.java:322)

Need to think of ways to solve the bulk data from the MySQL synchronization to Es method, found ES has bulk_request API, but this is the text or log for batch processing import, it seems to have not been used in the MySQL database near real-time synchronization. The problem is pending.

This article is from the "strive to the front" blog, please be sure to keep this source http://liucb.blog.51cto.com/3230681/1907105

Elasticsearch-jdbc bulk sync mysql data failed