To test the ColumnStore insert cache for replication, perform the steps as would be required for a normal replication set up between an InnoDB master and a ColumnStore slave (refer to https://mariadb.com/kb/en/setting-up-replication/). But this time, enable the cache on the slave node.

To enable the cache, turn on the read-only system variable 'columnstore_cache_inserts'. This can be set either in the .cnf config file for the slave node ('columnstore_cache_inserts=ON'), or as a command line option '--columnstore_cache_inserts' at server start up. Ensure that the cache is enabled by executing "show variables like 'columnstore_cache_inserts';" command. The cache is disabled by default.

There is an additional session variable that is added: 'columnstore_cache_flush_threshold'. This controls the number of records after which the cache will be flushed to the ColumnStore table. Default value is set to 500000, but the user can increase or decrease this setting based on the workload (it shouldn't be set to too low else the cache will be flushed too often. On the other hand, it shouldn't be set to too high, else a single cache flush might take too long).

Current limitations of the cache:

• Rollbacks are not supported. This is not an issue for the replication use case since the binlog discards transactions with rollbacks, and the events are not played on the slave.

Things to keep in mind:

• A ColumnStore table created with the cache disabled (default behaviour) cannot participate in DQL or DML queries if the server is restarted with the cache enabled. This behaviour is expected since the actual cache was not created in the first place when the original ColumnStore table was created. UPDATE: This is fixed in MCOL-4769.
• Cache only improves performance of INSERTs, including batch inserts as well as LDI. INSERT..SELECT (as well as any other type of DML) is not improved, however.

For QA: Refer to my previous comment for instructions on testing the cache.

Build tested: 1.5.4-1 (drone #496)

Tested in two configurations

1. Single node, ColumnStore tables
insert, successful
insert..select, successful
LDI, successful

2. Single node, InnoDB tables, replicating to ColumnStore table in another server
insert, successful
insert..select, successful
LDI, failed.

LDI on master caused MariaDB on slave crashed and not recoverable

On master:

load data infile '/data/qa/shares/mcol-4285/lineitem.100.tbl' into table lineitem fields terminated by '|';

Slave

/var/log/messages file:

Sep 1 18:33:12 localhost mariadbd: 200901 18:33:12 [ERROR] mysqld got signal 11 ;
Sep 1 18:33:12 localhost mariadbd: This could be because you hit a bug. It is also possible that this binary
Sep 1 18:33:12 localhost mariadbd: or one of the libraries it was linked against is corrupt, improperly built,
Sep 1 18:33:12 localhost mariadbd: or misconfigured. This error can also be caused by malfunctioning hardware.
Sep 1 18:33:12 localhost mariadbd: To report this bug, see https://mariadb.com/kb/en/reporting-bugs
Sep 1 18:33:12 localhost mariadbd: We will try our best to scrape up some info that will hopefully help
Sep 1 18:33:12 localhost mariadbd: diagnose the problem, but since we have already crashed,
Sep 1 18:33:12 localhost mariadbd: something is definitely wrong and this may fail.
Sep 1 18:33:12 localhost mariadbd: Server version: 10.6.0-MariaDB-log
Sep 1 18:33:12 localhost mariadbd: key_buffer_size=134217728
Sep 1 18:33:12 localhost mariadbd: read_buffer_size=131072
Sep 1 18:33:12 localhost mariadbd: max_used_connections=1
Sep 1 18:33:12 localhost mariadbd: max_threads=153
Sep 1 18:33:12 localhost mariadbd: thread_count=5
Sep 1 18:33:12 localhost mariadbd: It is possible that mysqld could use up to
Sep 1 18:33:12 localhost mariadbd: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467576 K bytes of memory
Sep 1 18:33:12 localhost mariadbd: Hope that's ok; if not, decrease some variables in the equation.
Sep 1 18:33:12 localhost mariadbd: Thread pointer: 0x7f32840087a8
Sep 1 18:33:12 localhost mariadbd: Attempting backtrace. You can use the following information to find out
Sep 1 18:33:12 localhost mariadbd: where mysqld died. If you see no messages after this, something went
Sep 1 18:33:12 localhost mariadbd: terribly wrong...
Sep 1 18:33:12 localhost mariadbd: stack_bottom = 0x7f32a8b963f0 thread_stack 0x49000
Sep 1 18:33:12 localhost mariadbd: ??:0(my_print_stacktrace)[0x55babc0c91ee]
Sep 1 18:33:12 localhost mariadbd: ??:0(handle_fatal_signal)[0x55babbace567]
Sep 1 18:33:12 localhost mariadbd: sigaction.c:0(__restore_rt)[0x7f32c4e3f630]
Sep 1 18:33:12 localhost mariadbd: :0(__strlen_sse2_pminub)[0x7f32c3249691]
Sep 1 18:33:12 localhost mariadbd: ??:0(std::string::assign(char const*))[0x7f32c3868344]
Sep 1 18:33:12 localhost mariadbd: ??:0(ha_mcs_impl_start_bulk_insert(unsigned long long, TABLE*, bool))[0x7f32c028a6e5]
Sep 1 18:33:12 localhost mariadbd: ??:0(ha_mcs::start_bulk_insert_from_cache(unsigned long long, unsigned int))[0x7f32c0283cdd]
Sep 1 18:33:12 localhost mariadbd: ??:0(Rows_log_event::write_row(rpl_group_info*, bool))[0x55babbbe187b]
Sep 1 18:33:12 localhost mariadbd: ??:0(Write_rows_log_event::do_exec_row(rpl_group_info*))[0x55babbbe1cd3]
Sep 1 18:33:12 localhost mariadbd: ??:0(Rows_log_event::do_apply_event(rpl_group_info*))[0x55babbbd750c]
Sep 1 18:33:12 localhost mariadbd: ??:0(non-virtual thunk to Item_string_sys::~Item_string_sys())[0x55babb8222a7]
Sep 1 18:33:12 localhost mariadbd: ??:0(handle_slave_sql)[0x55babb82c47c]
Sep 1 18:33:12 localhost mariadbd: ??:0(MyCTX_nopad::finish(unsigned char*, unsigned int*))[0x55babbd3e03d]
Sep 1 18:33:12 localhost mariadbd: pthread_create.c:0(start_thread)[0x7f32c4e37ea5]
Sep 1 18:33:12 localhost mariadbd: ??:0(__clone)[0x7f32c31d88dd]
Sep 1 18:33:12 localhost mariadbd: Trying to get some variables.
Sep 1 18:33:12 localhost mariadbd: Some pointers may be invalid and cause the dump to abort.
Sep 1 18:33:12 localhost mariadbd: Query (0x0):
Sep 1 18:33:12 localhost mariadbd: Connection ID (thread ID): 6
Sep 1 18:33:12 localhost mariadbd: Status: NOT_KILLED
Sep 1 18:33:12 localhost mariadbd: Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
Sep 1 18:33:12 localhost mariadbd: The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
Sep 1 18:33:12 localhost mariadbd: information that should help you find out what is causing the crash.
Sep 1 18:33:12 localhost mariadbd: Writing a core file...
Sep 1 18:33:12 localhost mariadbd: Working directory at /var/lib/mysql
Sep 1 18:33:12 localhost mariadbd: Resource Limits:
Sep 1 18:33:12 localhost mariadbd: Limit Soft Limit Hard Limit Units
Sep 1 18:33:12 localhost mariadbd: Max cpu time unlimited unlimited seconds
Sep 1 18:33:12 localhost mariadbd: Max file size unlimited unlimited bytes
Sep 1 18:33:12 localhost mariadbd: Max data size unlimited unlimited bytes
Sep 1 18:33:12 localhost mariadbd: Max stack size 8388608 unlimited bytes
Sep 1 18:33:12 localhost mariadbd: Max core file size 0 unlimited bytes
Sep 1 18:33:12 localhost mariadbd: Max resident set unlimited unlimited bytes
Sep 1 18:33:12 localhost mariadbd: Max processes 23129 23129 processes
Sep 1 18:33:12 localhost mariadbd: Max open files 16384 16384 files
Sep 1 18:33:12 localhost mariadbd: Max locked memory 65536 65536 bytes
Sep 1 18:33:12 localhost mariadbd: Max address space unlimited unlimited bytes
Sep 1 18:33:12 localhost kernel: mariadbd[13932]: segfault at 0 ip 00007f32c3249691 sp 00007f32a8b95b38 error 4 in libc-2.17.so[7f32c30da000+1c3000]
Sep 1 18:33:12 localhost mariadbd: Max file locks unlimited unlimited locks
Sep 1 18:33:12 localhost mariadbd: Max pending signals 23129 23129 signals
Sep 1 18:33:12 localhost mariadbd: Max msgqueue size 819200 819200 bytes
Sep 1 18:33:12 localhost mariadbd: Max nice priority 0 0
Sep 1 18:33:12 localhost mariadbd: Max realtime priority 0 0
Sep 1 18:33:12 localhost mariadbd: Max realtime timeout unlimited unlimited us
Sep 1 18:33:12 localhost mariadbd: Core pattern: core
Sep 1 18:33:12 localhost systemd: mariadb.service: main process exited, code=killed, status=11/SEGV
Sep 1 18:33:12 localhost systemd: Unit mariadb.service entered failed state.
Sep 1 18:33:12 localhost systemd: mariadb.service failed.
Sep 1 18:33:17 localhost systemd: mariadb.service holdoff time over, scheduling restart.
Sep 1 18:33:17 localhost systemd: Stopped MariaDB 10.6.0 database server.
Sep 1 18:33:17 localhost systemd: Starting MariaDB 10.6.0 database server...
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] /usr/sbin/mariadbd (mysqld 10.6.0-MariaDB-log) starting as process 14304 ...
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32186)
Sep 1 18:33:17 localhost mariadbd: 200901 18:33:17 Columnstore: Started; Version: 1.5.4-1
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: Using Linux native AIO
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: Uses event mutexes
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: Number of pools: 1
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: Using SSE4.2 crc32 instructions
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: Completed initialization of buffer pool
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=97979
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: 128 rollback segments are active.
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: Creating shared tablespace for temporary tables
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: 10.6.0 started; log sequence number 97991; transaction id 272
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] Plugin 'FEEDBACK' is disabled.
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] InnoDB: Buffer pool(s) load completed at 200901 18:33:17
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] Recovering after a crash using master1-bin
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] Starting crash recovery...
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] Crash recovery finished.
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] Server socket created on IP: '::'.
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] Reading of all Master_info entries succeeded
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] Added new Master_info '' to hash table
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 5 [Note] Slave I/O thread: Start asynchronous replication to master 'replication_user@s1pm1:3306' in log 'master1-bin.000006' at position 30654622
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 0 [Note] /usr/sbin/mariadbd: ready for connections.
Sep 1 18:33:17 localhost mariadbd: Version: '10.6.0-MariaDB-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 6 [Note] Slave SQL thread initialized, starting replication in log 'master1-bin.000006' at position 30643828, relay log './master1-relay-bin.000002' position: 31405
Sep 1 18:33:17 localhost mariadbd: 2020-09-01 18:33:17 5 [Note] Slave I/O thread: connected to master 'replication_user@s1pm1:3306',replication started in log 'master1-bin.000006' at position 30654622

Performance timing:

Single server without replication, ColumnStore table

Insert only, end-to-end timing
columnstore_cache_flush_threshold=500000

rowcnt Disabled(s) Enabled(s)
100 60 0
1000 600 1
10000 2
100000 14

Insert with replication, Innodb-to-Columnstore
columnstore_cache_flush_threshold=500000

Replication time is from start of insert to end of replication

Disabled Enabled
rowcnt insert replication insert replication
100 0 79 0 2
1000 0 739 1 1
10000 5 13
100000 52 71
1000000 514 698

columnstore_cache_flush_threshold=1000
100000 50 74

negative tests
a. restart of master
restarted master MariaDB server in the middle of 100000 inserts, both master and slave ended with 19773 rows. matched
b. restart of slave
restarted slave MariaDB server, replication continued and finished correctly