MariaDB ColumnStore installation and testing

Preamble

After a first blog post using the container edition of MariaDB ColumnStore I wanted to deploy it on an existing custom MariaDB server installation. Because where I work we do prefer to put files where we like using the MOCA architecture.

I had give up on this part as the MariaDB documentation is really too poor and might come back to this article to update if things evolve positively…

MariaDB Community Server installation and configuration

I have updated my MOCA layout for MariaDB that we have seen a long time ago. MOCA stands for MariaDB Optimal Configuration Architecture (MOCA). So below MariaDB directory naming convention, mariadb01 is the name of the instance:

Directory	Used for
/mariadb/data01/mariadb01	Strore MyISAM and InnoDB files, dataxx directories can also be created to spread I/O
/mariadb/dump/mariadb01	All log files (slow log, error log, general log, …)
/mariadb/logs/mariadb01	All binary logs (log-bin, relay_log)
/mariadb/software/mariadb01	MariaDB binaries, you might also want to use /mariadb/software/10.5.4 and factor binaries for multiple MariaDB instances. I personally believe that the extra 1GB for binaries is worth the flexibility it gives. In other words you can upgrade one without touching the others. The my.cnf file is then stored in a conf sub-directory, as well as socket and pid files.

I have create a mariadb Linux account in dba group and a /mariadb mount point of 5GB (xfs).

The binaries I downloaded is mariadb-10.5.4-linux-systemd-x86_64.tar.gz (for systems with systemd) as I have a recent Linux… The tar.gz release is obviously deliberate as I want to be able to put it in the directory of my choice:

If you take the RPM you can only have one engine per server that can be really limiting (and really hard to manage with your customers) with modern powerful servers…

I created /mariadb/software/mariadb01/conf/my.cnf file with below content (this is just a starting point, any tuning on it for your own workload is mandatory):

[server]
# Primary variables
basedir                         = /mariadb/software/mariadb01
datadir                         = /mariadb/data01/mariadb01
max_allowed_packet              = 256M
max_connect_errors              = 1000000
pid_file                        = /mariadb/software/mariadb01/conf/mariadb01.pid
skip_external_locking
skip_name_resolve
 
# Logging
log_error                       = /mariadb/dump/mariadb01/mariadb01.err
log_queries_not_using_indexes   = ON
long_query_time                 = 5
slow_query_log                  = ON     # Disabled for production
slow_query_log_file             = /mariadb/dump/mariadb01/mariadb01-slow.log
 
tmpdir                          = /tmp
user                            = mariadb
 
# InnoDB Settings
default_storage_engine          = InnoDB
innodb_buffer_pool_size         = 1G    # Use up to 70-80% of RAM
innodb_file_per_table           = ON
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 512M
innodb_stats_on_metadata        = ON
innodb_read_io_threads          = 64
innodb_write_io_threads         = 64
 
# New plugin directory for Columnstore
plugin_dir                      = /usr/lib64/mysql/plugin
plugin_maturity                 = beta
 
[client-server]
port                            = 3316
socket                          = /mariadb/software/mariadb01/conf/mariadb01.sock

As root account I executed:

[root@server4 ~]# /mariadb/software/mariadb01/scripts/mariadb-install-db --defaults-file=/mariadb/software/mariadb01/conf/my.cnf --user=mariadb
Installing MariaDB/MySQL system tables in '/mariadb/data01/mariadb01' ...
OK
 
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
 
 
Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is mariadb@localhost, it has no password either, but
you need to be the system 'mariadb' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo
 
See the MariaDB Knowledgebase at https://mariadb.com/kb or the
MySQL manual for more instructions.
 
You can start the MariaDB daemon with:
cd '/mariadb/software/mariadb01' ; /mariadb/software/mariadb01/bin/mysqld_safe --datadir='/mariadb/data01/mariadb01'
 
You can test the MariaDB daemon with mysql-test-run.pl
cd '/mariadb/software/mariadb01/mysql-test' ; perl mysql-test-run.pl
 
Please report any problems at https://mariadb.org/jira
 
The latest information about MariaDB is available at https://mariadb.org/.
You can find additional information about the MySQL part at:
https://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

This is new (at least to me) that from now one you can connect with mariadb or root account without any password. In my mariadb Linux account I created three below aliases:

alias mariadb01='/mariadb/software/mariadb01/bin/mariadb --defaults-file=/mariadb/software/mariadb01/conf/my.cnf --user=mariadb'
alias start_mariadb01='cd /mariadb/software/mariadb01/; ./bin/mariadbd-safe --defaults-file=/mariadb/software/mariadb01/conf/my.cnf &'
alias stop_mariadb01='/mariadb/software/mariadb01/bin/mariadb-admin --defaults-file=/mariadb/software/mariadb01/conf/my.cnf --user=mariadb shutdown'

Start and stop command are working fine but client connection (mariadb01 alias) failed for:

/mariadb/software/mariadb01/bin/mariadb: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory

I resolved it with:

dnf -y install ncurses-compat-libs-6.1-7.20180224.el8.x86_64

You can also connect with root Linux account using (MariaDB accounts cannot be faked):

[root@server4 ~]# /mariadb/software/mariadb01/bin/mariadb --defaults-file=/mariadb/software/mariadb01/conf/my.cnf --user=root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.4-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]>

MariaDB ColumnStore installation and configuration

I expected, as it is written everywhere, to have ColumnStore available as a storage engine. But found nothing implemented by default:

MariaDB [(none)]> show engines;
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                                         | Transactions | XA   | Savepoints |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                                           | NO           | NO   | NO         |
| CSV                | YES     | Stores tables as CSV files                                                                      | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                                       | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                                  | YES          | NO   | YES        |
| Aria               | YES     | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                                              | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables                | YES          | YES  | YES        |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.000 sec)
 
MariaDB [(none)]> show plugins;
+-------------------------------+----------+--------------------+---------+---------+
| Name                          | Status   | Type               | Library | License |
+-------------------------------+----------+--------------------+---------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password         | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| mysql_old_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| MRG_MyISAM                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| CSV                           | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| Aria                          | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MyISAM                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| SPATIAL_REF_SYS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| GEOMETRY_COLUMNS              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| inet6                         | ACTIVE   | DATA TYPE          | NULL    | GPL     |
| inet_aton                     | ACTIVE   | FUNCTION           | NULL    | GPL     |
| inet_ntoa                     | ACTIVE   | FUNCTION           | NULL    | GPL     |
| inet6_aton                    | ACTIVE   | FUNCTION           | NULL    | GPL     |
| inet6_ntoa                    | ACTIVE   | FUNCTION           | NULL    | GPL     |
| is_ipv4                       | ACTIVE   | FUNCTION           | NULL    | GPL     |
| is_ipv6                       | ACTIVE   | FUNCTION           | NULL    | GPL     |
| is_ipv4_compat                | ACTIVE   | FUNCTION           | NULL    | GPL     |
| is_ipv4_mapped                | ACTIVE   | FUNCTION           | NULL    | GPL     |
| CLIENT_STATISTICS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INDEX_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| TABLE_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| USER_STATISTICS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| wsrep                         | ACTIVE   | REPLICATION        | NULL    | GPL     |
| SQL_SEQUENCE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA            | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCKS                  | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_LOCK_WAITS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLES             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESTATS         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_COLUMNS            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FIELDS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_FOREIGN_COLS       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_TABLESPACES        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_DATAFILES          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_VIRTUAL            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_MUTEXES                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SYS_SEMAPHORE_WAITS    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLESPACES_ENCRYPTION | ACTIVE   | INFORMATION SCHEMA | NULL    | BSD     |
| SEQUENCE                      | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| user_variables                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| unix_socket                   | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| FEEDBACK                      | DISABLED | INFORMATION SCHEMA | NULL    | GPL     |
| THREAD_POOL_GROUPS            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| THREAD_POOL_QUEUES            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| THREAD_POOL_STATS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| THREAD_POOL_WAITS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| partition                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
+-------------------------------+----------+--------------------+---------+---------+
68 rows in set (0.002 sec)

You have also this query that I found on MariaDB web site:

SELECT plugin_name, plugin_version, plugin_maturity FROM information_schema.plugins ORDER BY plugin_name;

I had to configure official MariaDB repository as explained in documentation:

[root@server4 ~]# cat /etc/yum.repos.d/mariadb.repo
# MariaDB 10.5 RedHat repository list - created 2020-07-09 15:06 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.5/rhel8-amd64
module_hotfixes=1
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

One the repository is configured you can see what’s available with:

dnf list mariadb*

I can see a MariaDB-columnstore-engine.x86_64 but its installation will also install MariaDB-server.x86_64 which I do not want… So far I have not found a way to just have the .so file to inject this ColumStore storage engine in my custom MariaDB Server installation…

[root@server4 ~]# mcsSetConfig CrossEngineSupport Host 127.0.0.1
[root@server4 ~]# mcsSetConfig CrossEngineSupport Port 3316
[root@server4 ~]# mcsSetConfig CrossEngineSupport User cross_engine
[root@server4 ~]# mcsSetConfig CrossEngineSupport Password cross_engine_passwd

MariaDB [(none)]> CREATE USER 'cross_engine'@'127.0.0.1' IDENTIFIED BY "cross_engine_passwd";
Query OK, 0 rows affected (0.001 sec)
 
MariaDB [(none)]> GRANT SELECT ON *.* TO 'cross_engine'@'127.0.0.1';
Query OK, 0 rows affected (0.001 sec)

[root@server4 ~]# systemctl status mariadb-columnstore
● mariadb-columnstore.service - mariadb-columnstore
   Loaded: loaded (/usr/lib/systemd/system/mariadb-columnstore.service; enabled; vendor preset: disabled)
   Active: active (exited) since Mon 2020-07-13 15:42:19 CEST; 3min 15s ago
  Process: 27960 ExecStop=/usr/bin/mariadb-columnstore-stop.sh (code=exited, status=0/SUCCESS)
  Process: 27998 ExecStart=/usr/bin/mariadb-columnstore-start.sh (code=exited, status=0/SUCCESS)
 Main PID: 27998 (code=exited, status=0/SUCCESS)
 
Jul 13 15:42:11 server4.domain.com systemd[1]: Stopped mariadb-columnstore.
Jul 13 15:42:11 server4.domain.com systemd[1]: Starting mariadb-columnstore...
Jul 13 15:42:12 server4.domain.com mariadb-columnstore-start.sh[27998]: Job for mcs-storagemanager.service failed because the control process exited with error code.
Jul 13 15:42:12 server4.domain.com mariadb-columnstore-start.sh[27998]: See "systemctl status mcs-storagemanager.service" and "journalctl -xe" for details.
Jul 13 15:42:19 server4.domain.com systemd[1]: Started mariadb-columnstore.

[root@server4 ~]# systemctl status mcs-storagemanager.service
● mcs-storagemanager.service - storagemanager
   Loaded: loaded (/usr/lib/systemd/system/mcs-storagemanager.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Mon 2020-07-13 15:42:14 CEST; 8min ago
  Process: 28010 ExecStartPre=/usr/bin/mcs-start-storagemanager.py (code=exited, status=1/FAILURE)
 
Jul 13 15:42:14 server4.domain.com systemd[1]: Starting storagemanager...
Jul 13 15:42:14 server4.domain.com mcs-start-storagemanager.py[28010]: S3 storage has not been set up for MariaDB ColumnStore. StorageManager service fails to start.
Jul 13 15:42:14 server4.domain.com systemd[1]: mcs-storagemanager.service: Control process exited, code=exited status=1
Jul 13 15:42:14 server4.domain.com systemd[1]: mcs-storagemanager.service: Failed with result 'exit-code'.
Jul 13 15:42:14 server4.domain.com systemd[1]: Failed to start storagemanager.

[root@server4 columnstore]# cat /var/log/mariadb/columnstore/debug.log
Jul 13 15:05:42 server4 IDBFile[26302]: 42.238256 |0|0|0| D 35 CAL0002: Failed to open file: /var/lib/columnstore/data1/systemFiles/dbrm/tablelocks, exception: unable to open Buffered file
Jul 13 15:05:42 server4 controllernode[26302]: 42.238358 |0|0|0| D 29 CAL0000: TableLockServer::load(): could not open the save file/var/lib/columnstore/data1/systemFiles/dbrm/tablelocks
Jul 13 15:42:17 server4 IDBFile[28020]: 17.117913 |0|0|0| D 35 CAL0002: Failed to open file: /var/lib/columnstore/data1/systemFiles/dbrm/tablelocks, exception: unable to open Buffered file
Jul 13 15:42:17 server4 controllernode[28020]: 17.118009 |0|0|0| D 29 CAL0000: TableLockServer::load(): could not open the save file/var/lib/columnstore/data1/systemFiles/dbrm/tablelocks

[root@server4 columnstore]# grep -v ^# /etc/columnstore/storagemanager.cnf | grep -v -e '^$'
[ObjectStorage]
service = LocalStorage
object_size = 5M
metadata_path = /mariadb/columnstore/storagemanager/metadata
journal_path = /mariadb/columnstore/storagemanager/journal
max_concurrent_downloads = 21
 
max_concurrent_uploads = 21
common_prefix_depth = 3
[S3]
region = some_region
bucket = some_bucket
[LocalStorage]
path = /mariadb/columnstore/storagemanager/fake-cloud
fake_latency = n
max_latency = 50000
[Cache]
cache_size = 2g
path = /mariadb/columnstore/storagemanager/cache

[mariadb@server4 mariadb]$ mkdir -p /mariadb/columnstore/storagemanager/fake-cloud
[mariadb@server4 mariadb]$ mkdir -p /mariadb/columnstore/storagemanager/cache
[mariadb@server4 mariadb]$ mkdir -p /mariadb/columnstore/storagemanager/metadata
[mariadb@server4 mariadb]$ mkdir -p /mariadb/columnstore/storagemanager/journal

[root@server4 ~]# mcsGetConfig -a | grep /var/lib
SystemConfig.DBRoot1 = /var/lib/columnstore/data1
SystemConfig.DBRMRoot = /var/lib/columnstore/data1/systemFiles/dbrm/BRM_saves
SystemConfig.TableLockSaveFile = /var/lib/columnstore/data1/systemFiles/dbrm/tablelocks
SessionManager.TxnIDFile = /var/lib/columnstore/data1/systemFiles/dbrm/SMTxnID
OIDManager.OIDBitmapFile = /var/lib/columnstore/data1/systemFiles/dbrm/oidbitmap
WriteEngine.BulkRoot = /var/lib/columnstore/data/bulk
WriteEngine.BulkRollbackDir = /var/lib/columnstore/data1/systemFiles/bulkRollback
[root@server4 ~]# mcsSetConfig SystemConfig DBRoot1 /mariadb/columnstore/data1
[root@server4 ~]# mcsGetConfig SystemConfig DBRoot1
/mariadb/columnstore/data1
[root@server4 ~]# mcsSetConfig SystemConfig DBRMRoot /mariadb/columnstore/data1/systemFiles/dbrm/BRM_saves
[root@server4 ~]# mcsSetConfig SystemConfig TableLockSaveFile /mariadb/columnstore/data1/systemFiles/dbrm/tablelocks
[root@server4 ~]# mcsSetConfig SessionManager TxnIDFile /mariadb/columnstore/data1/systemFiles/dbrm/SMTxnID
[root@server4 ~]# mcsSetConfig OIDManager OIDBitmapFile /mariadb/columnstore/data1/systemFiles/dbrm/oidbitmap
[root@server4 ~]# mcsSetConfig WriteEngine BulkRoot /mariadb/columnstore/data/bulk
[root@server4 ~]# mcsSetConfig WriteEngine BulkRollbackDir /mariadb/columnstore/data1/systemFiles/bulkRollback
[root@server4 ~]# mkdir -p /mariadb/columnstore/data1/systemFiles/dbrm/BRM_saves /mariadb/columnstore/data1/systemFiles/dbrm/tablelocks
[root@server4 ~]# mkdir -p /mariadb/columnstore/data1/systemFiles/dbrm/SMTxnID /mariadb/columnstore/data1/systemFiles/dbrm/SMTxnID
[root@server4 ~]# mkdir -p /mariadb/columnstore/data/bulk /mariadb/columnstore/data1/systemFiles/bulkRollback

Taking inspiration from the container version I have changed plugin_dir variable and plugin maturity allowance to:

# New plugin directory for Columnstore
plugin_dir                      = /usr/lib64/mysql/plugin
plugin_maturity                 = beta

Plugin maturity parameter is to avoid:

MariaDB [(none)]> INSTALL PLUGIN IF NOT EXISTS Columnstore SONAME 'ha_columnstore.so';
ERROR 1126 (HY000): Can't open shared library 'ha_columnstore.so' (errno: 1, Loading of beta plugin Columnstore is prohibited by --plugin-maturity=gamma)

And tried to load the plugin with:

MariaDB [(none)]> show engines;
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                                         | Transactions | XA   | Savepoints |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | Stores tables as CSV files                                                                      | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                                           | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                                       | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint                         | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                                  | YES          | NO   | YES        |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables                | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                                              | NO           | NO   | NO         |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.001 sec)
 
MariaDB [(none)]> INSTALL PLUGIN IF NOT EXISTS Columnstore SONAME 'ha_columnstore.so';
Query OK, 0 rows affected (0.111 sec)
 
MariaDB [(none)]> show engines;
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                                         | Transactions | XA   | Savepoints |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| Columnstore        | YES     | ColumnStore storage engine                                                                      | YES          | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                                           | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                                       | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint                         | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                                  | YES          | NO   | YES        |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables                | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                                              | NO           | NO   | NO         |
| CSV                | YES     | Stores tables as CSV files                                                                      | NO           | NO   | NO         |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.001 sec)

MariaDB [(none)]> show engines; +--------------------+---- | Engine +--------------------+---- | CSV | MRG_MyISAM | MEMORY | Aria | MyISAM | SEQUENCE | InnoDB | PERFORMANCE_SCHEMA | YES +--------------------+---- 8 rows in set (0.001 Query OK, 0 rows +--------------------+---- | Engine +--------------------+---- | Columnstore | MRG_MyISAM | MEMORY | Aria | MyISAM | SEQUENCE | InnoDB | PERFORMANCE_SCHEMA | YES | CSV +--------------------+---- 9 rows in set (0.001 sec) -----+-------------------------------------------------------------------------------------------------+--------------+------+------------+ | Support | Comment | Transactions | XA | Savepoints | -----+-------------------------------------------------------------------------------------------------+--------------+------+------------+ | YES | Stores tables as CSV files | NO | NO | NO | | YES | Collection of identical MyISAM tables | NO | NO | NO | | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | YES | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO | NO | NO | | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO | | YES | Generated tables filled with sequential values | YES | NO | YES | | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES | | Performance Schema | NO | NO | NO | -----+-------------------------------------------------------------------------------------------------+--------------+------+------------+ sec)MariaDB [(none)]> INSTALL PLUGIN IF NOT EXISTS Columnstore SONAME 'ha_columnstore.so'; affected (0.111 sec)MariaDB [(none)]> show engines; -----+-------------------------------------------------------------------------------------------------+--------------+------+------------+ | Support | Comment | Transactions | XA | Savepoints | -----+-------------------------------------------------------------------------------------------------+--------------+------+------------+ | YES | ColumnStore storage engine | YES | NO | NO | | YES | Collection of identical MyISAM tables | NO | NO | NO | | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | YES | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO | NO | NO | | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO | | YES | Generated tables filled with sequential values | YES | NO | YES | | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES | | Performance Schema | NO | NO | NO | | YES | Stores tables as CSV files | NO | NO | NO | -----+-------------------------------------------------------------------------------------------------+--------------+------+------------+

On the paper it works but the connection is lost when you try to create a table with ColumnStore storage engine…

IT World

MariaDB ColumnStore installation and testing – part 2

Table of contents

Preamble

MariaDB Community Server installation and configuration

MariaDB ColumnStore installation and configuration

References

About Post Author

Yannick Jaquier

Related Posts:

Leave a Reply Cancel Reply