You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@seatunnel.apache.org by "samgang (via GitHub)" <gi...@apache.org> on 2023/04/24 09:53:18 UTC
[GitHub] [incubator-seatunnel] samgang opened a new issue, #4658: [Bug] [connector-jdbc] mysql to mysql slow
samgang opened a new issue, #4658:
URL: https://github.com/apache/incubator-seatunnel/issues/4658
### Search before asking
- [X] I had searched in the [issues](https://github.com/apache/incubator-seatunnel/issues?q=is%3Aissue+label%3A%22bug%22) and found no similar issues.
### What happened
The speed of data synchronization from MySQL to MySQL is very slow. So how should we improve the synchronization speed?
Also, is there a preSql function? such as deleting target table data before synchronization
### SeaTunnel Version
apache-seatunnel-incubating-2.3.1
### SeaTunnel Config
```conf
env {
execution.parallelism = 1
job.mode = "BATCH"
}
source {
Jdbc {
url = "jdbc:mysql://localhost:3306/test"
driver = "com.mysql.cj.jdbc.Driver"
connection_check_timeout_sec = 100
user = "xxxxxx"
password = "xxxxxx"
query = "select id,name from t1"
partition_column = "id"
partition_num = 10
}
}
sink {
jdbc {
url = "jdbc:mysql://localhost:3306/test"
driver = "com.mysql.cj.jdbc.Driver"
user = "xxxxxx"
password = "xxxxxx"
query = "INSERT INTO t2(id,name) VALUES (?,?)"
}
}
```
### Running Command
```shell
./bin/seatunnel.sh --config test2.conf -m local
```
### Error Exception
```log
Read Count So Far : 282048
Write Count So Far : 279999
Average Read Count : 233/s
Average Write Count : 233/s
Last Statistic Time : 2023-04-24 17:33:30
Current Statistic Time : 2023-04-24 17:34:30
Read Count So Far : 295048
Write Count So Far : 292999
Average Read Count : 216/s
Average Write Count : 216/s
Last Statistic Time : 2023-04-24 17:34:30
Current Statistic Time : 2023-04-24 17:35:30
```
### Flink or Spark Version
no
### Java or Scala Version
1.8
### Screenshots
_No response_
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug] [connector-jdbc] mysql to mysql slow [seatunnel]
Posted by "Yanhuanjin (via GitHub)" <gi...@apache.org>.
Yanhuanjin commented on issue #4658:
URL: https://github.com/apache/seatunnel/issues/4658#issuecomment-1857453272
I tried all the methods metioned above, and it was still very slow. mysql to postgre.
My setting looks like:
`env {
execution.parallelism = 10
job.mode = "BATCH"
}
source{
Jdbc {
url = "jdbc:mysql://host:port/test?rewriteBatchStatements=true"
driver = "com.mysql.cj.jdbc.Driver"
connection_check_timeout_sec = 3600
user = "root"
password = "xxxxx"
query = "select * from test"
fetch_size = 10000
}
}
sink {
Jdbc {
url = "jdbc:postgresql://host:port/test?rewriteBatchStatements=true"
driver = "org.postgresql.Driver"
connection_check_timeout_sec = 3600
user = "root"
password = "xxxx"
database = "test"
table = "ods.test"
generate_sink_sql = true
primary_keys = ["id"]
compatible_mode = "postgresLow"
batch_size = 10000
}
}`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-seatunnel] EricJoy2048 commented on issue #4658: [Bug] [connector-jdbc] mysql to mysql slow
Posted by "EricJoy2048 (via GitHub)" <gi...@apache.org>.
EricJoy2048 commented on issue #4658:
URL: https://github.com/apache/incubator-seatunnel/issues/4658#issuecomment-1522898945
try set `execution.parallelism=10`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-seatunnel] shannxisj commented on issue #4658: [Bug] [connector-jdbc] mysql to mysql slow
Posted by "shannxisj (via GitHub)" <gi...@apache.org>.
shannxisj commented on issue #4658:
URL: https://github.com/apache/incubator-seatunnel/issues/4658#issuecomment-1554552604
I had the same problem, the version is the same as his, but I added rewriteBatchStatements=true in the url, which did not solve it
`env {
job.mode = "BATCH",
execution.parallelism = 10
}
source {
Jdbc {
url = "jdbc:mysql://192.168.0.4:3307/sql?rewriteBatchStatements=true"
driver = "com.mysql.cj.jdbc.Driver"
user = "root"
password = "root"
query = "select * from test1"
}
}
sink {
Jdbc {
url = "jdbc:mysql://192.168.0.4:3307/sql?rewriteBatchStatements=true"
driver = "com.mysql.cj.jdbc.Driver"
user = "root"
password = "root"
query = "insert into test1_copy2(brand,stt,edt) values(?,?,?)"
}
}`
` Job Progress Information
***********************************************
Job Id : 711925978242220033
Read Count So Far : 6048
Write Count So Far : 3999
Average Read Count : 100/s
Average Write Count : 66/s
Last Statistic Time : 2023-05-19 20:59:42
Current Statistic Time : 2023-05-19 21:00:42
***********************************************
`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-seatunnel] samgang commented on issue #4658: [Bug] [connector-jdbc] mysql to mysql slow
Posted by "samgang (via GitHub)" <gi...@apache.org>.
samgang commented on issue #4658:
URL: https://github.com/apache/incubator-seatunnel/issues/4658#issuecomment-1521047568
> jdbc url add the option `rewriteBatchStatements=true`
Thanks, but `rewriteBatchedStatements=true` is ok for me.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-seatunnel] TyrantLucifer commented on issue #4658: [Bug] [connector-jdbc] mysql to mysql slow
Posted by "TyrantLucifer (via GitHub)" <gi...@apache.org>.
TyrantLucifer commented on issue #4658:
URL: https://github.com/apache/incubator-seatunnel/issues/4658#issuecomment-1520375215
jdbc url add the option `rewriteBatchStatements=true`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-seatunnel] samgang commented on issue #4658: [Bug] [connector-jdbc] mysql to mysql slow
Posted by "samgang (via GitHub)" <gi...@apache.org>.
samgang commented on issue #4658:
URL: https://github.com/apache/incubator-seatunnel/issues/4658#issuecomment-1521046997
>
Thanks, but `rewriteBatchedStatements=true` is ok for me.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [seatunnel] github-actions[bot] closed issue #4658: [Bug] [connector-jdbc] mysql to mysql slow
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #4658: [Bug] [connector-jdbc] mysql to mysql slow
URL: https://github.com/apache/seatunnel/issues/4658
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [seatunnel] github-actions[bot] commented on issue #4658: [Bug] [connector-jdbc] mysql to mysql slow
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #4658:
URL: https://github.com/apache/seatunnel/issues/4658#issuecomment-1646701309
This issue has been closed because it has not received response for too long time. You could reopen it if you encountered similar problems in the future.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [seatunnel] github-actions[bot] commented on issue #4658: [Bug] [connector-jdbc] mysql to mysql slow
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #4658:
URL: https://github.com/apache/seatunnel/issues/4658#issuecomment-1633367960
This issue has been automatically marked as stale because it has not had recent activity for 30 days. It will be closed in next 7 days if no further activity occurs.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [incubator-seatunnel] samgang commented on issue #4658: [Bug] [connector-jdbc] mysql to mysql slow
Posted by "samgang (via GitHub)" <gi...@apache.org>.
samgang commented on issue #4658:
URL: https://github.com/apache/incubator-seatunnel/issues/4658#issuecomment-1522932251
> try set `execution.parallelism=10`
Thanks, It seems that the improvement is not significant.
1. add `rewriteBatchedStatements=true` and set `execution.parallelism=1`
```
Read Count So Far : 5502980
Write Count So Far : 5500931
Average Read Count : 11292/s
Average Write Count : 11292/s
Total Time(s) : 532
Total Read Count : 6005032
Total Write Count : 6005032
```
2. add `rewriteBatchedStatements=true` and set `execution.parallelism=10`
```
Read Count So Far : 5657869
Write Count So Far : 5655820
Average Read Count : 11723/s
Average Write Count : 11723/s
Total Time(s) : 516
Total Read Count : 6005032
Total Write Count : 6005032
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@seatunnel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org