您现在的位置是：首页 >技术交流 >海量数据同步到DDM（oracle到mysql）网站首页技术交流

1、由于oracle的rownum性能，所以通过主键ID实现分页；

2、数据可能存在重复，批量插入mysql使用insert ignore语法；

3、DDM数据库，过千万后并发插入，性能就很差：采用单线程一千条批量插入（最多不超过两个线程-实测）

4、避免影响生产，首先将oracle导一个备份库（同时drs增量监听生产库）；

5、会员表连表，分页查询结果，写入CSV；解析时组装业务表（八九张表，单线程内并发插入这几张表）

一、DDM写入瓶颈

二、报错

1、连接池（新建连接，保证连接有效）：

test-on-borrow: true

2、使用事务LocalTxUtil，rollback关闭连接找不到

Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet successfully received from the server was 69,170 milliseconds ago. The last packet sent successfully to the server was 69,170 milliseconds ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_201]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_201]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_201]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_201]
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.27.jar!/:8.0.27]
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-java-8.0.27.jar!/:8.0.27]
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-java-8.0.27.jar!/:8.0.27]
        at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167) ~[mysql-connector-java-8.0.27.jar!/:8.0.27]
        at com.mysql.cj.protocol.a.NativeProtocol.send(NativeProtocol.java:567) ~[mysql-connector-java-8.0.27.jar!/:8.0.27]
        at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:622) ~[mysql-connector-java-8.0.27.jar!/:8.0.27]
        at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:137) ~[mysql-connector-java-8.0.27.jar!/:8.0.27]
        at com.mysql.cj.NativeSession.sendCommand(NativeSession.java:279) ~[mysql-connector-java-8.0.27.jar!/:8.0.27]
        at com.mysql.cj.NativeSession.queryServerVariable(NativeSession.java:599) ~[mysql-connector-java-8.0.27.jar!/:8.0.27]
        at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:1395) ~[mysql-connector-java-8.0.27.jar!/:8.0.27]
        ... 57 common frames omitted
Caused by: java.net.SocketException: Connection reset
        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:115) ~[na:1.8.0_201]
        at java.net.SocketOutputStream.write(SocketOutputStream.java:155) ~[na:1.8.0_201]
        at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) ~[na:1.8.0_201]
        at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) ~[na:1.8.0_201]
        at com.mysql.cj.protocol.a.SimplePacketSender.send(SimplePacketSender.java:55) ~[mysql-connector-java-8.0.27.jar!/:8.0.27]
        at com.mysql.cj.protocol.a.TimeTrackingPacketSender.send(TimeTrackingPacketSender.java:50) ~[mysql-connector-java-8.0.27.jar!/:8.0.27]
        at com.mysql.cj.protocol.a.NativeProtocol.send(NativeProtocol.java:558) ~[mysql-connector-java-8.0.27.jar!/:8.0.27]
        ... 62 common frames omitted