Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4215

Performance regression on insert of 50000% on a Hybrid Transactional/Analytical Processing (HTAP)

    XMLWordPrintable

Details

    Description

      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

      Attachments

        Activity

          People

            Unassigned Unassigned
            matias.sanchez@avature.net Matias Sanchez
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.