海量数据同步到DDM（oracle到mysql） |
您所在的位置：网站首页 › DDM模型 › 海量数据同步到DDM（oracle到mysql） |
海量数据同步到DDM（oracle到mysql）
|
1、由于oracle的rownum性能，所以通过主键ID实现分页； 2、数据可能存在重复，批量插入mysql使用insert ignore语法； 3、DDM数据库，过千万后并发插入，性能就很差：采用单线程一千条批量插入（最多不超过两个线程-实测） 4、避免影响生产，首先将oracle导一个备份库（同时drs增量监听生产库，避免数据丢失）； 5、会员表连表，分页查询结果，写入CSV；解析时组装业务表（八九张表，单线程内并发插入这几张表） 一、DDM写入瓶颈 
DDM性能--瓶颈
二、报错 1、连接池（新建连接，保证连接有效）： test-on-borrow: true2、使用事务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 |
【本文地址】
今日新闻 |
推荐新闻 |