|
Hi!! after setting an HTAP Deployment on MariaDB 10.5 with Mariadb Columnstore 1.5.2 as storage engine on last release of MariaDB 10.5, we are facing serious performance regression when syncing data on MariaDB ColumnStore.
Even if we use cpimport for loading data as a topology starting point, the isolated issue is presented after when native replication executes INSERTs statements. This happens on adhoc INSERTs as well as shown in the following example.
CREATE TABLE `testColumn` (
`id` int(10) unsigned NOT NULL DEFAULT 0,
`ent` int(10) unsigned NOT NULL,
`title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`surname` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`company` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`salid` int(10) unsigned DEFAULT NULL,
`attid` int(10) unsigned DEFAULT NULL,
`sid` int(10) unsigned NOT NULL DEFAULT 1,
`pid` int(10) unsigned NOT NULL,
`cpid` int(10) unsigned DEFAULT NULL,
`ctime` datetime NOT NULL,
`aid` int(10) unsigned NOT NULL DEFAULT 1,
`email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`updatetime` datetime DEFAULT NULL,
`city` tinyint(3) unsigned NOT NULL,
`state` tinyint(3) unsigned NOT NULL
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `testInnodb` (
`id` int(10) unsigned NOT NULL DEFAULT 0,
`ent` int(10) unsigned NOT NULL,
`title` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`surname` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`company` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`salid` int(10) unsigned DEFAULT NULL,
`attid` int(10) unsigned DEFAULT NULL,
`sid` int(10) unsigned NOT NULL DEFAULT 1,
`pid` int(10) unsigned NOT NULL,
`cpid` int(10) unsigned DEFAULT NULL,
`ctime` datetime NOT NULL,
`aid` int(10) unsigned NOT NULL DEFAULT 1,
`email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`updatetime` datetime DEFAULT NULL,
`city` tinyint(3) unsigned NOT NULL,
`state` tinyint(3) unsigned NOT NULL
) ENGINE=Innodb DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
MariaDB []> INSERT INTO `testColumn` VALUES (999,5197,'Company tester director','Matías','Sanchez','National City',NULL,NULL,1,200,NULL,'2008-03-11 17:09:41',1,'matias.sanchez@avature.net','2018-05-24 19:54:07',1,1);
Query OK, 1 row affected (0.533 sec)
MariaDB []> INSERT INTO `testInnodb` VALUES (999,5197,'Company tester director','Matías','Sanchez','National City',NULL,NULL,1,200,NULL,'2008-03-11 17:09:41',1,'matias.sanchez@avature.net','2018-05-24 19:54:07',1,1);
Query OK, 1 row affected (0.002 sec)
The performance for Columnstore table is regressed over 50000% for INSERTs which makes impossible to maintain an near-synch analytical database , are there some recommendations for tuning inserts statements on MariaDB Columnstore 1.5 Plugin??
Following are current configured parameters for this tests
| column_compression_threshold |
100 |
| column_compression_zlib_level |
6 |
| column_compression_zlib_strategy |
DEFAULT_STRATEGY |
| column_compression_zlib_wrap |
OFF |
| columnstore_compression_type |
SNAPPY |
| columnstore_decimal_scale |
8 |
| columnstore_derived_handler |
ON |
| columnstore_diskjoin_bucketsize |
100 |
| columnstore_diskjoin_largesidelimit |
0 |
| columnstore_diskjoin_smallsidelimit |
0 |
| columnstore_double_for_decimal_math |
OFF |
| columnstore_group_by_handler |
ON |
| columnstore_import_for_batchinsert_delimiter |
7 |
| columnstore_import_for_batchinsert_enclosed_by |
17 |
| columnstore_local_query |
0 |
| columnstore_orderby_threads |
16 |
| columnstore_ordered_only |
OFF |
| columnstore_replication_slave |
ON |
| columnstore_select_handler |
ON |
| columnstore_string_scan_threshold |
10 |
| columnstore_stringtable_threshold |
20 |
| columnstore_um_mem_limit |
0 |
| columnstore_use_decimal_scale |
OFF |
| columnstore_use_import_for_batchinsert |
ON |
| columnstore_varbin_always_hex |
OFF |
| innodb_instant_alter_column_allowed |
add_drop_reorder |
Thanks you so much in advance for any help
Regard
|