[
https://issues.apache.org/jira/browse/BEAM-3714?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16382636#comment-16382636
]
Eugene Kirpichov commented on BEAM-3714:
----------------------------------------
The point of JdbcIO.read() is typically to read *a lot* of data, quickly.
Smaller fetch size = lower performance (more database roundtrips to fetch the
same amount of data), larger fetch size = better performance but more memory
usage (e.g. a fetch size of 1 billion rows is definitely not practical, as is
an unlimited fetch size).
Basically, we need the largest fetch size that will not cause us to run out of
memory.
I think something in the vicinity of like 50k rows would be reasonable.
> JdbcIO.read() should create a forward-only, read-only result set
> ----------------------------------------------------------------
>
> Key: BEAM-3714
> URL: https://issues.apache.org/jira/browse/BEAM-3714
> Project: Beam
> Issue Type: Bug
> Components: io-java-jdbc
> Reporter: Eugene Kirpichov
> Assignee: Innocent
> Priority: Major
>
> [https://stackoverflow.com/questions/48784889/streaming-data-from-cloudsql-into-dataflow/48819934#48819934]
> - a user is trying to load a large table from MySQL, and the MySQL JDBC
> driver requires special measures when loading large result sets.
> JdbcIO currently calls simply "connection.prepareStatement(query)"
> https://github.com/apache/beam/blob/bb8c12c4956cbe3c6f2e57113e7c0ce2a5c05009/sdks/java/io/jdbc/src/main/java/org/apache/beam/sdk/io/jdbc/JdbcIO.java#L508
> - it should specify type TYPE_FORWARD_ONLY and concurrency CONCUR_READ_ONLY
> - these values should always be used.
> Seems that different databases have different requirements for streaming
> result sets.
> E.g. MySQL requires setting fetch size; PostgreSQL says "The Connection must
> not be in autocommit mode."
> https://jdbc.postgresql.org/documentation/head/query.html#query-with-cursor .
> Oracle, I think, doesn't have any special requirements but I don't know.
> Fetch size should probably still be set to a reasonably large value.
> Seems that the common denominator of these requirements is: set fetch size to
> a reasonably large but not maximum value; disable autocommit (there's nothing
> to commit in read() anyway).
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
