mariadb - 表大小——MariaDB Columnstore 与 InnoDB

Question

我在 MariaDB 的 ColumnStore 上发现的每一项分析都声称它使用的磁盘空间比 InnoDB 等常规引擎少，例如:https://www.percona.com/blog/2017/03/17/column-store-database-benchmarks-mariadb-columnstore-vs-clickhouse-vs-apache-spark/

但这不是我在测试中发现的

CREATE TABLE `innodb_test` (id int, value1 bigint, value2 bigint, value3 bigint, value4 bigint, value5 bigint) ENGINE=innodb;

CREATE TABLE `columnstore_test` (id int COMMENT 'compression=2', value1 bigint COMMENT 'compression=2', value2 bigint COMMENT 'compression=2', value3 bigint COMMENT 'compression=2', value4 bigint COMMENT 'compression=2',value5 bigint COMMENT 'compression=2') ENGINE=columnstore;

向表中插入值为 0 的 100 万行(5 列):

INSERT INTO innodb_test
SELECT CONCAT(a1.id,a2.id,a3.id,a4.id,a5.id,a6.id),
0,0,0,0,0
from 
  (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a1, 
  (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a2,
  (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a3,
  (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a4,
  (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a5,
  (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a6;

INSERT INTO columnstore_test SELECT * FROM innodb_test;

columnstore 表的大小比 innoDB 表大:

call columnstore_info.table_usage(NULL, 'columnstore_test');
+--------------+------------------+-----------------+-----------------+-------------+
| TABLE_SCHEMA | TABLE_NAME       | DATA_DISK_USAGE | DICT_DISK_USAGE | TOTAL_USAGE |
+--------------+------------------+-----------------+-----------------+-------------+
| size_comp    | columnstore_test | 352.05 MB       | 0 Bytes         | 0 Bytes     |
+--------------+------------------+-----------------+-----------------+-------------+

SELECT table_name, (data_length + index_length) / (1024 * 1024) "Size in MB"  FROM information_schema.tables WHERE table_schema = schema() AND table_name = 'innodb_test';
+-------------+------------+
| table_name  | Size in MB |
+-------------+------------+
| innodb_test | 71.6094    |
+-------------+------------+

此外，如果我创建没有压缩的表，大小是相同的:

CREATE TABLE `columnstore_no_compression` (id int COMMENT 'compression=0', value1 bigint COMMENT 'compression=0', value2 bigint COMMENT 'compression=0', value3 bigint COMMENT 'compression=0', value4 bigint COMMENT 'compression=0',value5 bigint COMMENT 'compression=0') ENGINE=columnstore;

INSERT INTO columnstore_no_compression SELECT * FROM innodb_test;

call columnstore_info.table_usage(NULL, 'columnstore_no_compression');
+--------------+----------------------------+-----------------+-----------------+-------------+
| TABLE_SCHEMA | TABLE_NAME                 | DATA_DISK_USAGE | DICT_DISK_USAGE | TOTAL_USAGE |
+--------------+----------------------------+-----------------+-----------------+-------------+
| size_comp    | columnstore_no_compression | 352.00 MB       | 0 Bytes         | 0 Bytes     |
+--------------+----------------------------+-----------------+-----------------+-------------+

我使用的是 mariadb-columnstore-1.1.2-1 版本

我的.ini文件:

[client]
port = 3306
socket          = /usr/local/mariadb/columnstore/mysql/lib/mysql/mysql.sock

[mysqld]
loose-server_audit_syslog_info = columnstore-1
port = 3306
socket          = /usr/local/mariadb/columnstore/mysql/lib/mysql/mysql.sock
datadir         = /ssd/mariadb/db
skip-external-locking
key_buffer_size = 512M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 0
thread_stack = 512K
lower_case_table_names=1
group_concat_max_len=512
sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
infinidb_compression_type=2
infinidb_stringtable_threshold=20
infinidb_local_query=0
infinidb_diskjoin_smallsidelimit=0
infinidb_diskjoin_largesidelimit=0
infinidb_diskjoin_bucketsize=100
infinidb_um_mem_limit=0
infinidb_use_import_for_batchinsert=1
infinidb_import_for_batchinsert_delimiter=7
basedir                         = /usr/local/mariadb/columnstore/mysql/
character-sets-dir              = /usr/local/mariadb/columnstore/mysql/share/charsets/
lc-messages-dir                 = /usr/local/mariadb/columnstore/mysql/share/
plugin_dir                      = /usr/local/mariadb/columnstore/mysql/lib/plugin
binlog_format=ROW
server-id = 1
log-bin=/usr/local/mariadb/columnstore/mysql/db/mysql-bin
relay-log=/usr/local/mariadb/columnstore/mysql/db/relay-bin
relay-log-index = /usr/local/mariadb/columnstore/mysql/db/relay-bin.index
relay-log-info-file = /usr/local/mariadb/columnstore/mysql/db/relay-bin.info
tmpdir          = /ssd/tmp/

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

这是预期的行为还是我做错了什么？

Answer 1

我是 MariaDB ColumnStore 的首席软件工程师。

ColumnStore 针对大型数据集进行了优化，并为列预分配了磁盘空间。这样做的好处是在磁盘轴上碎片的可能性较小。缺点是在像您这样的小数据集上，它分配了很多未使用的空间。

它首先为第一列范围预分配 256KB，然后将其扩展到 2^23 行(刚刚超过 800 万)。因此，对于您的每个 BIGINT 列，它将预分配 64MB，对于您的 INT，它将预分配 32MB。对于压缩文件上的 header block ，压缩/未压缩之间的细微差别。我们有一些 information_schema 表可以向您显示实际使用情况(8KB 以内):

https://mariadb.com/kb/en/library/columnstore-information-schema-tables/

因此，除非您计划使用更大的数据集(至少在几 GB 的范围内)，否则不幸的是您会在数据很少时看到大量磁盘使用。