Skip to content

Latest commit

 

History

History
143 lines (106 loc) · 5.67 KB

production_setup.md

File metadata and controls

143 lines (106 loc) · 5.67 KB
Raw

Production setup

Throughput & Memory Usage
Low Memory environments(5GB)
Initial Load
MySQL Setup
PostgreSQL Setup
ClickHouse Setup

Improving throughput and/or Memory usage.

As detailed in the diagram above, there are components that store the messages and can be configured to improve throughput and/or memory usage.

  1. Debezium Queue:

The following configuration parameters are used to configure the size of the debezium queue in terms of number of elements the queue can hold and the maximum size of the queue in bytes.

```    
#Positive integer value that specifies the maximum size of each batch of events that should be processed during each iteration of this connector. Defaults to 2048.
max.batch.size: 20000

#Positive integer value that specifies the maximum number of records that the blocking queue can hold.
max.queue.size: 100000

# A long integer value that specifies the maximum volume of the blocking queue in bytes.
max.queue.size.in.bytes: 1000000000
```
  1. Sink connector Queue:
    # The maximum number of records that should be loaded into memory while streaming data from MySQL to ClickHouse.
    sink.connector.max.queue.size: "100000"
  1. Thread Pool:
     # Maximum number of threads in the thread pool for processing CDC records.
    thread.pool.size: 10
    
    # Max number of records for the flush buffer.
    buffer.max.records: "1000000"
    
    Flush time of the buffer in milliseconds. The buffer that is stored in memory before being flushed to ClickHouse.
    buffer.flush.time.ms: "1000"

Low Memory environments(5GB)

The suggested configuration for a low memory environment is as follows to use a single threaded configuration. Single threaded configuration can be enabled in config.yml

single.threaded: "true"

As shown in the diagram below, the Single threaded configuration will skip the sink connector queue and threadpool and will insert batches directly from the debezium queue.

Initial Load

The following parameters might be useful to reduce the memory usage of the connector during the snapshotting phase.

Reduce the number of tables: Use the snapshot.include.collection.list to filter include only the necessary tables as part of the snapshot which will reduce the data retrieved from source.

snapshot.fetch.size: This defines the maximum number of rows that are fetched as part of the batch from the database. Use a lower number to reduce memory usage. Incremental

incremental.snapshot.chunk.size
Default value: 1024
The maximum number of rows that the connector fetches and reads into memory when it retrieves an incremental snapshot chunk. Increasing the chunk size provides greater efficiency, because the snapshot runs fewer snapshot queries of a greater size. However, larger chunk sizes also require more memory to buffer the snapshot data. Adjust the chunk size to a value that provides the best performance in your environment.

snapshot.max.threads: Increase this number from 1 to a higher value to enable parallel snapshotting.

MySQL Production Setup

How to Reproduce

  1. Replicate a table only in config.yml:

    table.include.list: "mydb.mytable"

  2. Do not write to the table on the source database side.

  3. Monitor the lag:

    select * from altinity_sink_connector.show_replica_status\G

  4. The lag increases if this table does not get written and the binary log position does not move. It should be synced periodically to show the binary log progress.

Workaround

Include a heartbeat table (see Percona Toolkit - pt-heartbeat):

Example

CREATE TABLE pt_heartbeat_db.heartbeat (
  id int NOT NULL PRIMARY KEY,
  ts datetime NOT NULL
);
=======
**Single Threaded (Low Memory/Slow replication)**:
By setting the `single.threaded: true` configuration variable in `config.yml`, the replication will skip the sink connector queue and threadpool
and will insert batches directly from the debezium queue.
This mode will work on lower memory setup but will increase the replication speed.

## ClickHouse Setup
The clickhouse user needs to have the following GRANTS to the 
offset storage/schema history  database(database provided in `offset.storage.jdbc.` configuration variable.
and the database that is replicated(database provided in `database.include.list` and `table.include.list`)

The following example creates user `sink` with necessary GRANTS
to the offset storage/schema history database and replicated databases.

ALTER SETTINGS PROFILE 'ingest' SETTINGS deduplicate_blocks_in_dependent_materialized_views=1, min_insert_block_size_rows_for_materialized_views=10000, throw_on_max_partitions_per_insert_block=0, max_partitions_per_insert_block=1000, date_time_input_format='best_effort';

CREATE USER OR REPLACE 'sink' IDENTIFIED WITH sha256_hash BY '' HOST IP '::/8' SETTINGS PROFILE 'ingest'; grant SELECT, INSERT, CREATE TABLE, CREATE DATABASE on altinity.* to sink; grant CLUSTER ON . to sink; grant SELECT, INSERT, CREATE TABLE, TRUNCATE on replicated_db.* to sink;

**User Profile**
`max_partitions_per_insert_block` - The default value is 100, its recommended to set this value **1000**

## PostgreSQL Production Setup

One of the common problems with PostgreSQL is the WAL size increasing.
[Handling PostgreSQL WAL Growth with Debezium Connectors](postgres_wal_growth.md)