Column Store를 설치 하기 전에 MariaDB가 설치되어 있으면 안됨. MariaDB가 설치 되어 있으면 삭제 후에 진행을 해야 한다.
2018.01 당시 1.1.2 버전이 GA 되었으며, 지원하는 O/S는 RHEL/CentOS v6, v7, Ubuntu 16.04 LTS , Debian v8, v9 , SUSE 12
0. ColumnStore Download
Download here : https://mariadb.com/downloads/mariadb-ax
1. Preparation
Set Locate
|
[root@localhost ~]# localedef -i en_US -f UTF-8 en_US.UTF-8 |
Set Server information
Multi Server로 ColumnStore를 구성 할 경우 각각의 서버는 Hostname , IP 가 설정 되어 있어야 하며,
각각의 서버의 root 비밀번호와 사용하는 non-root유저의 비밀번호가 같아야 한다.
각각의 서버는 ssh 접속 시에 비밀번호가 없이 접속 가능하게 ssh key 설정이 되어 있어야 함.
ColumnStore는 root or non-root유저로 설치가 가능하다.
Set Port
ColumnStore의 데몬이 사용하는 포트는 3306이다.
ColumnStore 소프트웨어를 위해서 8600~8622,8700,8800 포트를 확보해야 한다.
해당 포트들은 방화벽에서 열려 있어야 한다.
방화벽에 해당 포트를 추가 하거나 다음 명령으로 방화벽 종료
|
# 방화벽 영구 정지
#chkconfig iptables off
OR
CentOS 7 #systemctl status firewalld #systemctl stop firewalld #systemctl disable firewalld
Ubuntu #chkconfig ufw off
SUSE #/sbin/rcSuSEfirewall2 status #/sbin/rcSuSEfirewall2 stop |
Set DBRoots
DBRoots는 ColumnStore Datafile이 저장될 디렉터리 이다.
예를 들면 /usr/local/mariadb/columnstore/data<N>
N은 dbroot Number 임.
Set Network
/etc/rc.d/rc.local 파일에 다음의 값을 세팅한다.
|
/sbin/ifconfig eth0 txqueuelen 10000 |
/etc/sysctl.conf에 다음을 추가
|
# increase TCP max buffer size net.core.rmem_max = 16777216 net.core.wmem_max = 16777216
# increase Linux autotuning TCP buffer limits # min, default, and max number of bytes to use net.ipv4.tcp_rmem = 4096 87380 16777216 net.ipv4.tcp_wmem = 4096 65536 16777216
# don't cache ssthresh from previous connection net.ipv4.tcp_no_metrics_save = 1
# recommended to increase this for 1000 BT or higher net.core.netdev_max_backlog = 2500
# for 10 GigE, use this net.core.netdev_max_backlog = 30000 |
Set vfs_cache_pressure (For Read performance)
|
# 현재 값 확인 [root@localhost ~]# cat /proc/sys/vm/vfs_cache_pressure 100
# 다음 Reboot까지 임시적으로 변경 [root@localhost ~]# sysctl -w vm.vfs_cache_pressure=10 vm.vfs_cache_pressure = 10
# Reboot후에도 자동으로 적용되게 하기 위하여 /etc/sysctl.conf 파일에 추가
[root@localhost ~]# cat /etc/sysctl.conf # Read performance vm.vfs_cache_pressure = 10 |
Set umask
mysql유저가 ColumnStore의 설정 파일인 Columnstore.xml 파일을 읽기 위해서umask가 022로 설정되어야 함.
기본으로 /etc/profile 파일 안에 umask가 022로 설정되어 있음.
022가 아닐 경우, 설치 전에 022로 설정하고 설치를 진행 한다.
|
# 현재 값 확인 [root@localhost ~]# umask 0022
# umask 설정 [root@localhost ~]# umask 022 [root@localhost ~]# umask 0022 |
Set SELinux
/etc/selinux/config 파일에 SELINUX값을 disabled로 변경
|
[root@localhost ~]# cat /etc/selinux/config SELINUX=disabled |
2. Install package dependencies
Boost libraries 1.53 or newer
|
# yum -y install boost or # apt-get -y install libboost-all-dev
# Cent6 wget https://downloads.mariadb.com/ColumnStore/1.0.x/centos/x86_64/centos6_boost_1_55.tar.gz tar xfz centos6_boost_1_55.tar.gz -C /usr/lib ldconfig
# SUSE 12 SUSEConnect -p sle-sdk/12.2/x86_64 zypper install boost-devel |
Other Packages
|
Centos 6/7 # yum -y install expect perl perl-DBI openssl zlib file sudo libaio rsync snappy net-tools
Ubuntu 16 # apt-get -y install tzdata libtcl8.6 expect perl openssl file sudo libdbi-perl libboost-all-dev libreadline-dev rsync libsnappy1v5 net-tools
Debian 8 # apt-get install expect perl openssl file sudo libdbi-perl libboost-all-dev libreadline-dev rsync libsnappy1 net-tools
Debian 9 # apt-get install expect perl openssl file sudo libdbi-perl libboost-all-dev libreadline-dev rsync net-tools libsnappy1v5 libreadline5 libaio1
SUSE 12 zypper install expect perl perl-DBI openssl file sudo libaio1 rsync net-tools libsnappy1 |
Data Redundancy packages
|
Centos 6/7 # yum -y install centos-release-gluster # yum -y install glusterfs glusterfs-fuse glusterfs-server
start / enable service: # systemctl enable glusterd.service # systemctl start glusterd.service
Ubuntu 16 # sudo apt-get install glusterfs-server
start / enable service: # sudo systemctl start glusterfs-server.service # sudo systemctl enable glusterfs-server.service
Debian 8 # apt-get -y install glusterfs-server
start / enable service: # update-rc.d glusterfs-server defaults
Debian 9 # sudo apt-get install glusterfs-server
start / enable service: # sudo systemctl start glusterfs-server.service # sudo systemctl enable glusterfs-server.service
SUSE 12 # zypper install glusterfs
start / enable service: # chkconfig glusterd on |
System Logging Package
ColumnStore 로그를 남기기 위해서 다음 중 하나가 설치 되어 있어야 한다.
syslog, rsyslog, syslog-ng
|
# yum install syslog |
3-1. Prepare ColumnStore by root user
설치가 완료 되면 /usr/local/mariadb/columnstore 디렉토리에 설치가 완료 됨.
with rpm
|
[root@localhost ~]# tar xvfz mariadb-columnstore-1.1.2-1-centos7.x86_64.rpm.tar.gz mariadb-columnstore-1.1.2-1-x86_64-centos7-client.rpm mariadb-columnstore-1.1.2-1-x86_64-centos7-common.rpm mariadb-columnstore-1.1.2-1-x86_64-centos7-gssapi-server.rpm mariadb-columnstore-1.1.2-1-x86_64-centos7-libs.rpm mariadb-columnstore-1.1.2-1-x86_64-centos7-platform.rpm mariadb-columnstore-1.1.2-1-x86_64-centos7-rocksdb-engine.rpm mariadb-columnstore-1.1.2-1-x86_64-centos7-server.rpm mariadb-columnstore-1.1.2-1-x86_64-centos7-shared.rpm mariadb-columnstore-1.1.2-1-x86_64-centos7-storage-engine.rpm mariadb-columnstore-1.1.2-1-x86_64-centos7-tokudb-engine.rpm
[root@localhost ~]# rpm -ivh mariadb-columnstore-1.1.2-1-x86_64-centos7*.rpm Preparing... ################################# [100%] Updating / installing... 1:mariadb-columnstore-common-1.1.2-################################# [ 10%] 2:mariadb-columnstore-libs-1.1.2-1 ################################# [ 20%] MariaDB ColumnStore RPM install completed 3:mariadb-columnstore-client-1.1.2-################################# [ 30%] 4:mariadb-columnstore-server-1.1.2-################################# [ 40%] 5:mariadb-columnstore-shared-1.1.2-################################# [ 50%] 6:mariadb-columnstore-platform-1.1.################################# [ 60%] The next step is:
If installing on a pm1 node:
/usr/local/mariadb/columnstore/bin/postConfigure
If installing on a non-pm1 using the non-distributed option:
/usr/local/mariadb/columnstore/bin/columnstore start
MariaDB ColumnStore RPM install completed 7:mariadb-columnstore-gssapi-server################################# [ 70%] 8:mariadb-columnstore-rocksdb-engin################################# [ 80%] 9:mariadb-columnstore-tokudb-engine################################# [ 90%] 10:mariadb-columnstore-storage-engin################################# [100%] MariaDB ColumnStore RPM install completed
[root@localhost columnstore]# /usr/local/mariadb/columnstore/bin/post-install The next step is:
If installing on a pm1 node:
/usr/local/mariadb/columnstore/bin/postConfigure
If installing on a non-pm1 using the non-distributed option:
/usr/local/mariadb/columnstore/bin/columnstore start
|
with binary
|
# ln -s /mnt/mariadb /usr/local # cp /root/mariadb-columnstore-release#.x86_64.bin.tar.gz /usr/local/ mariadb-columnstore-release#.x86_64.bin.tar.gz
Unpack the tarball, which will generate the /usr/local/ directory. # tar -zxvf mariadb-columnstore-release#.x86_64.bin.tar.gz
Run the post-install script: # /usr/local/mariadb/columnstore/bin/post-install |
Install ColumnStore DEB package (only Debian or Ubuntu OS )
|
Download the package mariadb-columnstore-release#.amd64.deb.tar.gz (DEB 64- BIT) into the /root directory of the server where you are installing MariaDB ColumnStore. Unpack the tarball, which will generate DEBs. tar -zxf mariadb-columnstore-release#.amd64.deb.tar.gz Install the MariaDB ColumnStore DEBs. The MariaDB ColumnStore software will be installed in /usr/ local/. dpkg -i mariadb-columnstore*release#*.deb |
3-2. Install ColumnStore by non-root user
https://mariadb.com/kb/en/library/preparing-for-columnstore-installation-11x/ 아랫부분 참조
4-1. Install ColumnStore by root user (Single Server)
ColumnStore설정 이 끝나면 자동으로 MariaDB가 설치 된다.
|
[root@localhost columnstore]# /usr/local/mariadb/columnstore/bin/postConfigure
Select the type of System Server install [1=single, 2=multi] (2) > 1
Performing the Single Server Install. Enter System Name (columnstore-1) > enter
===== Setup Storage Configuration =====
----- Setup Performance Module DBRoot Data Storage Mount Configuration -----
There are 2 options when configuring the storage: internal or external
'internal' - This is specified when a local disk is used for the DBRoot storage. High Availability Server Failover is not Supported in this mode
'external' - This is specified when the DBRoot directories are mounted. High Availability Server Failover is Supported in this mode.
Select the type of Data Storage [1=internal, 2=external] (1) > enter
Enter the list (Nx,Ny,Nz) or range (Nx-Nz) of DBRoot IDs assigned to module 'pm1' (1) > enter
===== Performing Configuration Setup and MariaDB ColumnStore Startup =====
NOTE: Setting 'NumBlocksPct' to 50% Setting 'TotalUmMemory' to 25% of total memory.
Running the MariaDB ColumnStore setup scripts
post-mysqld-install Successfully Completed post-mysql-install Successfully Completed
Starting MariaDB Columnstore Database Platform
MariaDB ColumnStore Database Platform Starting, please wait ..... DONE
System Catalog Successfull Created
MariaDB ColumnStore Install Successfully Completed, System is Active
Enter the following command to define MariaDB ColumnStore Alias Commands
. /usr/local/mariadb/columnstore/bin/columnstoreAlias
Enter 'mcsmysql' to access the MariaDB ColumnStore SQL console Enter 'mcsadmin' to access the MariaDB ColumnStore Admin console
NOTE: The MariaDB ColumnStore Alias Commands are in /etc/profile.d/columnstoreAlias
[root@localhost ~]# netstat -na | grep LISTEN tcp 0 0 0.0.0.0:8620 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:8622 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:8630 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:8601 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:8603 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:8604 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:8612 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:8614 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:8616 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:8700 0.0.0.0:* LISTEN tcp 0 0 0.0.0.0:8800 0.0.0.0:* LISTEN tcp6 0 0 :::3306 :::* LISTEN tcp6 0 0 :::111 :::* LISTEN tcp6 0 0 :::22 :::* LISTEN tcp6 0 0 ::1:25 :::* LISTEN
|
4-2. Install ColumnStore by non-root user(Single Server)
https://mariadb.com/kb/en/library/installing-and-configuring-mariadb-columnstore/ 참조
5. Configure ColumnStore Alias
configure
|
[root@localhost ~]# . /usr/local/mariadb/columnstore/bin/columnstoreAlias
[root@localhost ~]# cat .bash_profile alias mcsmysql='/usr/local/mariadb/columnstore/mysql/bin/mysql --defaults-file=/usr/local/mariadb/columnstore/mysql/my.cnf -u root' alias mcsadmin='/usr/local/mariadb/columnstore/bin/mcsadmin' |
mcsadmin
The MariaDB ColumnStore Management Console allows you to configure, monitor, and manage the MariaDB ColumnStore system and servers. For more detailed information, see MariaDB ColumnStore Administrative Console.
|
mcsadmin> help (전체 명령어)
mcsadmin> getsysteminfo getsysteminfo Mon Jan 15 21:19:10 2018
System columnstore-1
System and Module statuses
Component Status Last Status Change ------------ -------------------------- ------------------------ System ACTIVE Mon Jan 15 21:00:17 2018 Module pm1 ACTIVE Mon Jan 15 21:00:14 2018
MariaDB ColumnStore Process statuses
Process Module Status Last Status Change Process ID ------------------ ------ --------------- ------------------------ ---------- ProcessMonitor pm1 ACTIVE Mon Jan 15 20:59:41 2018 12868 ProcessManager pm1 ACTIVE Mon Jan 15 20:59:47 2018 12954 DBRMControllerNode pm1 ACTIVE Mon Jan 15 20:59:50 2018 13316 ServerMonitor pm1 ACTIVE Mon Jan 15 20:59:52 2018 13332 DBRMWorkerNode pm1 ACTIVE Mon Jan 15 20:59:52 2018 13366 DecomSvr pm1 ACTIVE Mon Jan 15 20:59:56 2018 13395 PrimProc pm1 ACTIVE Mon Jan 15 20:59:59 2018 13423 ExeMgr pm1 ACTIVE Mon Jan 15 21:00:03 2018 13523 WriteEngineServer pm1 ACTIVE Mon Jan 15 21:00:07 2018 13564 DDLProc pm1 ACTIVE Mon Jan 15 21:00:11 2018 13636 DMLProc pm1 ACTIVE Mon Jan 15 21:00:15 2018 13674 mysqld pm1 ACTIVE Mon Jan 15 20:59:57 2018 13255
Active Alarm Counts: Critical = 0, Major = 0, Minor = 0, Warning = 0, Info = 0
mcsadmin> getsystemstatus getsystemstatus Mon Jan 15 21:24:29 2018
System columnstore-1
System and Module statuses
Component Status Last Status Change ------------ -------------------------- ------------------------ System ACTIVE Mon Jan 15 21:00:17 2018 Module pm1 ACTIVE Mon Jan 15 21:00:14 2018
|
|
# Shutting down the system When you perform a shutdown, all MariaDB ColumnStore processes are stopped. This command would be used mainly when performing software upgrades.
mcsadmin shutdownSystem y
# Stopping the system Stopping the system stops the application processes. The platform process that supports the Management Console and System Alarms remain active. If you want to stop the system or server and immediately start the processes again (typically if the system or a server hangs), you can restart the system or servers as
mcsadmin stopSystem y
# Starting the system or modules
mcsadmin startSystem
# To start the system after a shutdownSystem:
If its a multi-server system and ssh-keys are not configured between the servers, you will be required to provide the user password for the other servers. If ssh-keys are configured, then password parameter isn't required
mcsadmin startSystem 'user-password'
# Restarting the system
c |
mcsmysql
|
기존의 mysql client 같이 mariadb에 접속해서 쿼리를 수행 할 수 있는 SQL툴
[root@localhost ~]# mcsmysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 10.2.10-MariaDB-log Columnstore 1.1.2-1
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases ; +---------------------+ | Database | +---------------------+ | calpontsys | | columnstore_info | | infinidb_querystats | | infinidb_vtable | | information_schema | | mysql | | performance_schema | | test | +---------------------+ 8 rows in set (0.00 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 | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | InfiniDB | YES | Columnstore storage engine (deprecated: use columnstore) | YES | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | | SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+ 10 rows in set (0.00 sec)
|
6. Configure Files
Files
|
# my.cnf
/usr/local/mariadb/columnstore/mysql/my.cnf
# Columnstore.xml
/usr/local/mariadb/columnstore/etc/Columnstore.xml
|
7. 조회
다음의 쿼리를 이용하여 칼럼별로 생성된 익스텐트 정보를 조회 할 수 있다.
|
SELECT a.table_name , a.column_name , b.min_value , b.max_value , b.max_blocks , b.data_size , c.filename, c.file_size , c.compressed_data_size FROM information_schema.columnstore_columns a , information_schema.columnstore_extents b , information_schema.columnstore_files c WHERE a.object_id = b.object_id AND a.object_id = c.object_id ORDER BY 1 , 2 ;
+-----------------+-----------------+-----------+-----------+------------+-----------+------------------------------------------------------------------------------------------+-----------+----------------------+ | table_name | column_name | min_value | max_value | max_blocks | data_size | filename | file_size | compressed_data_size | +-----------------+-----------------+-----------+-----------+------------+-----------+------------------------------------------------------------------------------------------+-----------+----------------------+ | iwillfindyou | whereareyou | NULL | NULL | 4096 | 0 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/228.dir/000.dir/FILE000.cdf | 1056768 | 73728 | | lineitem | l_comment | NULL | NULL | 8192 | 48070656 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/223.dir/000.dir/FILE000.cdf | 67117056 | 24305664 | | lineitem | l_commitdate | 130553854 | 130983870 | 4096 | 24035328 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/218.dir/000.dir/FILE000.cdf | 33562624 | 18006016 | | lineitem | l_discount | 0 | 10 | 8192 | 48070656 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/210.dir/000.dir/FILE000.cdf | 67117056 | 11665408 | | lineitem | l_extendedprice | 90200 | 10484950 | 8192 | 48070656 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/211.dir/000.dir/FILE000.cdf | 67117056 | 31244288 | | lineitem | l_linenumber | 1 | 7 | 4096 | 24035328 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/221.dir/000.dir/FILE000.cdf | 33562624 | 4890624 | | lineitem | l_linestatus | 70 | 79 | 1024 | 6012928 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/216.dir/000.dir/FILE000.cdf | 8396800 | 1851392 | | lineitem | l_orderkey | 1 | 6000000 | 4096 | 24035328 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/209.dir/000.dir/FILE000.cdf | 33562624 | 9207808 | | lineitem | l_partkey | 1 | 200000 | 4096 | 24035328 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/215.dir/000.dir/FILE000.cdf | 33562624 | 24158208 | | lineitem | l_quantity | 1 | 50 | 4096 | 24035328 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/213.dir/000.dir/FILE000.cdf | 33562624 | 11927552 | | lineitem | l_receiptdate | 130552126 | 130992062 | 4096 | 24035328 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/219.dir/000.dir/FILE000.cdf | 33562624 | 18210816 | | lineitem | l_returnflag | 65 | 82 | 1024 | 6012928 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/214.dir/000.dir/FILE000.cdf | 8396800 | 2244608 | | lineitem | l_shipdate | 130551998 | 130987966 | 4096 | 24035328 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/208.dir/000.dir/FILE000.cdf | 33562624 | 18202624 | | lineitem | l_shipinstruct | NULL | NULL | 8192 | 48070656 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/222.dir/000.dir/FILE000.cdf | 67117056 | 10428416 | | lineitem | l_shipmode | NULL | NULL | 8192 | 48070656 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/220.dir/000.dir/FILE000.cdf | 67117056 | 11288576 | | lineitem | l_suppkey | 1 | 10000 | 4096 | 24035328 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/212.dir/000.dir/FILE000.cdf | 33562624 | 21405696 | | lineitem | l_tax | 0 | 8 | 8192 | 48070656 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/217.dir/000.dir/FILE000.cdf | 67117056 | 11558912 | | nation | n_comment | NULL | NULL | 8192 | 24576 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/198.dir/000.dir/FILE000.cdf | 2105344 | 122880 | | nation | n_name | NULL | NULL | 8192 | 24576 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/196.dir/000.dir/FILE000.cdf | 2105344 | 122880 | | nation | n_nationkey | 0 | 24 | 4096 | 16384 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/195.dir/000.dir/FILE000.cdf | 1056768 | 73728 | | nation | n_regionkey | 0 | 4 | 4096 | 16384 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/197.dir/000.dir/FILE000.cdf | 1056768 | 73728 | | region | r_comment | NULL | NULL | 8192 | 24576 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/191.dir/000.dir/FILE000.cdf | 2105344 | 122880 | | region | r_name | NULL | NULL | 8192 | 24576 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/190.dir/000.dir/FILE000.cdf | 2105344 | 122880 | | region | r_regionkey | 0 | 4 | 4096 | 16384 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/189.dir/000.dir/FILE000.cdf | 1056768 | 73728 | | test_add_column | id | 3 | 4 | 4096 | 0 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/202.dir/000.dir/FILE000.cdf | 1056768 | 73728 | | test_add_column | id1 | NULL | NULL | 4096 | 0 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/203.dir/000.dir/FILE000.cdf | 1056768 | 73728 | | test_add_column | id3 | NULL | NULL | 4096 | 0 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/204.dir/000.dir/FILE000.cdf | 1056768 | 73728 | | test_add_column | id4 | NULL | NULL | 4096 | 0 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/205.dir/000.dir/FILE000.cdf | 1056768 | 73728 | | test_add_column | id5 | NULL | NULL | 4096 | 0 | /usr/local/mariadb/columnstore/data1/000.dir/000.dir/011.dir/206.dir/000.dir/FILE000.cdf | 1056768 | 73728 | +-----------------+-----------------+-----------+-----------+------------+-----------+------------------------------------------------------------------------------------------+-----------+----------------------+ 29 rows in set (0.14 sec)
MariaDB [columnstore]> call columnstore_info.table_usage(NULL, NULL); +--------------+-----------------+-----------------+-----------------+-------------+ | TABLE_SCHEMA | TABLE_NAME | DATA_DISK_USAGE | DICT_DISK_USAGE | TOTAL_USAGE | +--------------+-----------------+-----------------+-----------------+-------------+ | columnstore | iwillfindyou | 1.01 MB | 0 Bytes | 0 Bytes | | columnstore | lineitem | 656.12 MB | 196.02 MB | 852.15 MB | | columnstore | nation | 6.03 MB | 4.02 MB | 10.05 MB | | columnstore | region | 5.02 MB | 4.02 MB | 9.05 MB | | columnstore | test_add_column | 5.04 MB | 0 Bytes | 0 Bytes | +--------------+-----------------+-----------------+-----------------+-------------+ 5 rows in set (0.29 sec)
MariaDB [columnstore]> call columnstore_info.compression_ratio(); +-------------------+ | COMPRESSION_RATIO | +-------------------+ | 137.0776% | +-------------------+
|