【 TiDB 使用环境】生产环境
【 TiDB 版本】5.4
【遇到的问题:问题现象及影响】
迁移全表数据从TiDB到hive,为啥迁移全量数据39亿,持续5个小时,没有问题,迁移增量数据3000万,持续40分钟,oom。
迁移全量的sql: select * from table
迁移增量的sql: select * from table where time > ‘2023-02-15 00:00:00’
疑问:
TiDB 针对 select * from table 这种单表简单查询有什么优化策略么,比如说执行这条sql的时候,从tikv拿出数据不占用tidb的内存,就直接抛出给client端;
TiDB 报错日志如下:
[2023/02/16 02:18:12.207 +00:00] [INFO] [conn.go:1115] [“command dispatched failed”] [conn=2127961] [connInfo=“id:2127961, addr:11.77.124.97:57694 status:10, collation:utf8_general_ci, user:lbs_computer”] [command=Query] [status=“inTxn:0, autocommit:1”] [sql="select * from table where time > ‘2023-02-15 00:00:00’“] [txn_mode=PESSIMISTIC] [err="Out Of Memory Quota![conn_id=2127961]
程序错误日志如下:
[2023-02-15T15:05:12.729+08:00] [INFO] plumber.reader.mysql-reader.invoke(logging.clj 272) [clojure-agent-send-off-pool-2] : MySQL数据库异常信息如下:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 1,799,921 milliseconds ago. The last packet sent successfully to the server was 2,482,983 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3715)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3604)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4155)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:926)
at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:2051)
at com.mysql.jdbc.RowDataDynamic.nextRecord(RowDataDynamic.java:408)
at com.mysql.jdbc.RowDataDynamic.next(RowDataDynamic.java:387)
at com.mysql.jdbc.RowDataDynamic.close(RowDataDynamic.java:165)
at com.mysql.jdbc.ResultSetImpl.realClose(ResultSetImpl.java:7471)
at com.mysql.jdbc.ResultSetImpl.close(ResultSetImpl.java:918)
at jdbc.jdbc$db_with_query_results_STAR_.invoke(jdbc.clj:670)
at plumber.reader.db_reader$fetch_from_db$fn__11826.invoke(db_reader.clj:12)
at jdbc.jdbc$with_connection_STAR_.invoke(jdbc.clj:847)
at plumber.reader.db_reader$fetch_from_db.invoke(db_reader.clj:8)
at plumber.reader.mysql_reader$fetch.invoke(mysql_reader.clj:14)
at plumber.reader.reader$eval13347$fn__13348.invoke(reader.clj:38)
at clojure.lang.MultiFn.invoke(MultiFn.java:231)
at plumber.reader.reader$reader$fn__13472.invoke(reader.clj:183)
at plumber.reader.reader$reader.invoke(reader.clj:182)
at plumber.job$sub_job$reify__15622$fn__15624.invoke(job.clj:69)
at clojure.core$binding_conveyor_fn$fn__369.invoke(core.clj:1910)
at clojure.lang.AFn.call(AFn.java:18)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3161)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3615)
… 25 more
[2023-02-15T15:05:12.730+08:00] [INFO] plumber.monitor.LogErrorMatch.matchLogError(LogErrorMatch.java 33) [clojure-agent-send-off-pool-2] : dbType:mysql
[2023-02-15T15:05:12.730+08:00] [INFO] plumber.monitor.LogErrorMatch.matchLogError(LogErrorMatch.java 34) [clojure-agent-send-off-pool-2] : errorMessage:Communications link failure