SerializedBufferingStrategy issues and optimizations

[robertomczak]

Topic

SerializedBufferingStrategy might be causing issues in some cases.

Relevant information

Hello Airbyte community.
Currently we are running Airbyte deployed on EKS via HELM. This Airbyte deployment is handling MySQL -> S3, PostgreSQL ->, Amplitude -> S3 and few other connections.

Note: Each MySQL has two DBs being sourced, one with 30 tables, second with only 4.
During operations of those connections we've observed that schema containing 30 tables (CDC replicated), is having various issues, that includes:

1. Small files fragmentation observed on S3, many (houndreds per sync) 4kb files observed for low utilized tables (low volume of Binlog events generated for table) - This makes it really unefficient for further processing.
2. Frequent Failed to deserialize data of EventHeaderV4 errors - Synchronization failures

Possible root cause of issues:

1. Low value of MAX_CONCURRENT_STREAM_IN_BUFFER with high number of streams results with frequent SerializedBufferingStrategy(flushAllBuffers) that closes all buffers. By that resulting with flushing object to S3 with small size, as buffer was containing low volume of data at given moment.
2. Regarding to com.github.shyiko.mysql.binlog.event.deserialization.EventDataDeserializationException: Failed to deserialize data of EventHeaderV4{timestamp=1631767918000, eventType=UPDATE_ROWS, serverId=2121124478, headerLength=19, dataLength=7701, nextPosition=217369687, flags=0} apache/flink-cdc#432
   "This issue might be caused by a long pause of TaskManager, such as a prolonged full GC, at which time the MySQL server thinks the client is not responsive so it stops transmitting binlog to the client, resulting in the EOFException."
   Which in this case might be linked to frequent flushAllBuffers.
   Doesn't look like connection or binlog corruption issue as other schema(using same binlog) is synchronizing correctly.

Possible solutions:

• One of possible solutions is to expose MAX_CONCURRENT_STREAM_IN_BUFFER (DEFAULT_MAX_CONCURRENT_STREAM_IN_BUFFER) to eg. env variables. This will allow to tweak how many concurrent streams worker can handle without calling flushAllBuffers frequently.

• Optimize stream assigment, eg. split streams belonging to single connections to multiple workers based on
  MAX_CONCURRENT_STREAM_IN_BUFFER setting.