MariaDB AX 1.1.2를 설치 한 후에 InnoDB / ColumnStore engine 성능비교
MariaDB AX 1.1.2를 설치 하면, MariaDB 10.2.10 버전이 자동으로 설치 된다.
이 서버에서 TPCH 스키마용 테이블을 hammerDB-2.23를 이용하여 생성한 후 성능 테스트를 진행.
서버 정보
|
구분 |
결과 |
Command |
|
O/S |
Cent 7 64bit |
cat /etc/*release |
|
CPU |
Cpu 10 / Socket 10 |
lscpu |
|
Memory |
20G |
cat /proc/meminfo |
비교 대상
tpch DB : InnoDB engine
columnstore DB : ColumnStore engine
|
실제 Row Count |
INNODB |
ColumnStore |
|
+------------+------------+ | Table_name | RowCount | +------------+------------+ | customer | 150,000 | | lineitem | 6,000,065 | | nation | 25 | | orders | 1,500,000 | | part | 200,000 | | partsupp | 800,000 | | region | 5 | | supplier | 10,000 | +------------+------------+ |
+------------+---------------+----------------+ | table_name | DATA_SIZE(MB) | INDEX_SIZE(MB) | +------------+---------------+----------------+ | customer | 27.56 | 3.52 | | lineitem | 803.00 | 953.09 | | nation | 0.02 | 0.02 | | orders | 188.70 | 77.16 | | part | 31.56 | 0.00 | | partsupp | 197.77 | 19.55 | | region | 0.02 | 0.00 | | supplier | 2.52 | 0.27 | +------------+---------------+----------------+ |
+------------+-----------------+-----------------+-------------+ | TABLE_NAME | DATA_DISK_USAGE | DICT_DISK_USAGE | TOTAL_USAGE | +------------+-----------------+-----------------+-------------+ | customer | 14.06 MB | 258.04 MB | 272.10 MB | | lineitem | 656.12 MB | 196.02 MB | 852.15 MB | | nation | 6.03 MB | 4.02 MB | 10.05 MB | | orders | 392.07 MB | 132.02 MB | 524.09 MB | | part | 16.07 MB | 136.05 MB | 152.12 MB | | partsupp | 192.04 MB | 128.02 MB | 320.05 MB | | region | 5.02 MB | 4.02 MB | 9.05 MB | | supplier | 12.05 MB | 8.03 MB | 20.09 MB | +------------+-----------------+-----------------+-------------+ |
위의 사이즈를 비교해 보면
ColumnStroe엔진은 인덱스를 사용하지 않으므로, Data Size만 비교해 보면 ColumnStore엔진이 사이즈가 작으나, 데이터가 적은 테이블에 대해서는 오히려 사이즈가 크다.
ColumStore엔진 자체가 DW용이므로, 작은 테이블의 데이터를 저장할 일이 없다고 가정하면 크게 문제는 되지 않을 것 같다.
그러나 customer테이블의 Dict Disk 부분에서 엄청난 사용량을 보이고 있다. 이 부분은 추후 확인이 필요 함.
Summary
|
|
Query 1 |
Query 3 |
Query 5 |
Query 6 |
Query 10 |
Query 12 |
Query 14 |
Query 19 |
|
INNODB |
14.81 sec |
18.48 sec |
6.44 sec |
3.49 sec |
3.15 sec |
7.89 sec |
2.02 sec |
0.53 sec |
|
ColumnStore |
0.85 sec |
0.66 sec |
Error |
0.16 sec |
1.62 sec |
1.24 sec |
0.50 sec |
0.34 sec |
1.Query 1
|
SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order FROM lineitem WHERE l_shipdate <= date '1998-12-01' - interval '90' day GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus; |
수행 결과
|
+--------------+--------------+----------+-----------------+-------------------+---------------------+---------+--------------+----------+-------------+ | l_returnflag | l_linestatus | sum_qty | sum_base_price | sum_disc_price | sum_charge | avg_qty | avg_price | avg_disc | count_order | +--------------+--------------+----------+-----------------+-------------------+---------------------+---------+--------------+----------+-------------+ | A | F | 37729967 | 56575664139.26 | 53749059314.2436 | 55898660796.025238 | 25.5004 | 38237.625476 | 0.049962 | 1479581 | | N | F | 981610 | 1470877367.92 | 1397067201.7503 | 1453178302.259662 | 25.4402 | 38120.444938 | 0.050071 | 38585 | | N | O | 74450600 | 111637510567.27 | 106057453075.2988 | 110296919208.363855 | 25.5009 | 38238.218234 | 0.049991 | 2919527 | | R | F | 37742206 | 56574391346.74 | 53745908969.6869 | 55898289745.443496 | 25.4936 | 38214.114380 | 0.049994 | 1480458 | +--------------+--------------+----------+-----------------+-------------------+---------------------+---------+--------------+----------+-------------+ |
|
|
tpch |
colmnstore |
|
exec time |
14.81 sec |
0.85 sec |
2. Query 3
|
SELECT l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority FROM customer, orders, lineitem WHERE c_mktsegment = 'BUILDING' AND c_custkey = o_custkey AND l_orderkey = o_orderkey AND o_orderdate < date '1995-03-15' AND l_shipdate > date '1995-03-15' GROUP BY l_orderkey, o_orderdate, o_shippriority ORDER BY revenue desc, o_orderdate LIMIT 20; |
수행 결과
|
+------------+-------------+-------------+----------------+ | l_orderkey | revenue | o_orderdate | o_shippriority | +------------+-------------+-------------+----------------+ | 5089957 | 432716.3434 | 1995-03-06 | 0 | | 1772102 | 417343.6335 | 1995-02-15 | 0 | | 3019617 | 385587.9884 | 1995-02-21 | 0 | | 1133920 | 383060.3325 | 1995-02-15 | 0 | | 2083746 | 381716.5291 | 1995-02-19 | 0 | | 4978465 | 380158.5628 | 1995-02-11 | 0 | | 5731015 | 374830.5108 | 1995-02-17 | 0 | | 4766022 | 374669.7707 | 1995-03-12 | 0 | | 3035139 | 373659.0160 | 1995-03-01 | 0 | | 417857 | 372321.0264 | 1995-01-31 | 0 | | 5678592 | 371803.7532 | 1995-03-07 | 0 | | 2941959 | 367252.8773 | 1995-03-14 | 0 | | 3263719 | 363983.8487 | 1995-02-23 | 0 | | 594087 | 361619.5976 | 1995-02-06 | 0 | | 4434247 | 359743.1379 | 1995-02-18 | 0 | | 77189 | 358799.6662 | 1995-03-08 | 0 | | 495431 | 358725.1059 | 1995-03-01 | 0 | | 3794179 | 357884.8161 | 1995-03-02 | 0 | | 3661350 | 356482.7291 | 1995-03-10 | 0 | | 2563236 | 354578.8212 | 1995-01-25 | 0 | +------------+-------------+-------------+----------------+ |
|
|
tpch |
colmnstore |
|
exec time |
18.48 sec |
0.66 sec |
3. Query 5
|
SELECT n_name, sum(l_extendedprice * (1 - l_discount)) as revenue FROM customer, orders, lineitem, supplier, nation, region WHERE c_custkey = o_custkey AND l_orderkey = o_orderkey AND l_suppkey = s_suppkey AND c_nationkey = s_nationkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'ASIA' AND o_orderdate >= date '1994-01-01' AND o_orderdate < date '1994-01-01' + interval '1' year GROUP BY n_name ORDER BY revenue desc; |
수행 결과
|
+-----------+---------------+ | n_name | revenue | +-----------+---------------+ | CHINA | 52506442.6670 | | INDONESIA | 49432824.0124 | | JAPAN | 48876525.1628 | | INDIA | 46459220.1641 | | VIETNAM | 46045515.3477 | +-----------+---------------+ |
|
|
tpch |
colmnstore |
|
exec time |
6.44 sec |
ERROR 1815 (HY000): Internal error: IDB-1003: Circular joins are not supported. |
4. Query 6
|
SELECT sum(l_extendedprice * l_discount) as revenue FROM lineitem WHERE l_shipdate >= date '1994-01-01' AND l_shipdate < date '1994-01-01' + interval '1' year AND l_discount between 0.06 - 0.01 AND 0.06 + 0.01 AND l_quantity < 24; |
수행 결과
|
+----------------+ | revenue | +----------------+ | 123170488.0089 | +----------------+ |
|
|
tpch |
colmnstore |
|
exec time |
3.49 sec |
0.16 sec |
5. Query 10
|
SELECT c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment FROM customer, orders, lineitem, nation WHERE c_custkey = o_custkey AND l_orderkey = o_orderkey AND o_orderdate >= date '1993-10-01' AND o_orderdate < date '1993-10-01' + interval '3' month AND l_returnflag = 'R' AND c_nationkey = n_nationkey GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment ORDER BY revenue desc LIMIT 20; |
수행 결과
|
+-----------+--------------------+--------------+-----------+---------------+---------------------------------------+--------------+----------------------------------------------------------------------------------------------------------------------+ | c_custkey | c_name | revenue | c_acctbal | n_name | c_address | c_phone | c_comment | +-----------+--------------------+--------------+-----------+---------------+---------------------------------------+--------------+----------------------------------------------------------------------------------------------------------------------+ | 139775 | Customer#000139775 | 1297097.6990 | 8228.95 | MOZAMBIQUE | 1EEcW45QYtmOFWEzToEfbXhFK5pW | 627-949-9653 | furiously final dependencies along the carefully express pinto beans cajole throughout the special pending idea | | 47912 | Customer#000047912 | 1139600.5702 | 5900.49 | RUSSIA | wxrQhOUtY,up | 570-320-8142 | unusual requests after the pinto beans sleep to the slyly ironic pinto beans. ca | | 29152 | Customer#000029152 | 1080858.0987 | 4349.18 | RUSSIA | r8azPz67bvR83vnX5 | 905-921-9594 | regular packages wake across the final instructions. even ironic accounts cajole. ironic deposits haggle furiously. | | 1235 | Customer#000001235 | 1027993.1481 | 8113.50 | CHINA | pxCdg08hQvO4IAhlYH | 818-850-1368 | stealthily regular platelets according to the patterns nod ironic regular courts. blithely unusu | | 101318 | Customer#000101318 | 1020683.0742 | 3197.16 | EGYPT | zyaXEfdDSxKCYzJh5obXMNdd3uRqa r | 839-721-7724 | deposits boost furiously slyly express dependenci | | 11962 | Customer#000011962 | 993860.0326 | 6840.29 | KENYA | 3kcDVp9Cf1 | 749-397-2933 | regular ironic deposits about the silent requests nag c | | 92723 | Customer#000092723 | 992401.7569 | 4903.68 | ETHIOPIA | jJn6 e6UbkQq1 | 627-537-3134 | fluffily express requests cajole slyly | | 109529 | Customer#000109529 | 991862.2696 | 257.06 | ARGENTINA | DPnXw4ReW7fy5i0rXToLsLrteky7VUm | 604-550-9623 | carefully special requests maintain! enticingly silent ideas al | | 107003 | Customer#000107003 | 991812.1735 | 189.82 | UNITED STATES | RS71RlHtJAoBggkG34zzMJDFMWUx1QcNfBQDs | 369-764-1350 | regular unusual ideas are. blithely regular theodolites dazzle fur | | 56221 | Customer#000056221 | 990818.8216 | 7434.83 | BRAZIL | r SumuZ6csgvafh0RRSDgEOQ5rZhPig | 551-541-4885 | requests nag. regular accounts sleep asymp | | 23020 | Customer#000023020 | 980028.2153 | 4784.05 | ARGENTINA | awPvTsrAFjgVS4aSKHN0VEAjjbo6Y | 703-295-9860 | ideas haggle final packages. blithely express accounts haggle blithely ironic foxes | | 131225 | Customer#000131225 | 975972.9937 | 7512.25 | RUSSIA | jBV6AnHNb7GsQOmkE0tI J | 666-275-7930 | slyly unusual deposits are. ruthless pearls snooze carefully. ironic foxes nag quickly. bol | | 91849 | Customer#000091849 | 968771.4609 | 9994.62 | ROMANIA | frdFdxM09o,7NwFgDmRGiP6MPBHgwPJ5 | 648-752-5079 | furiously regular asymptotes after the special even deposits sleep furiou | | 63994 | Customer#000063994 | 961169.2811 | 9621.31 | ALGERIA | 9Z1v0pCmvpWwmOvx85P2PEal4C1 | 633-553-9926 | excuses integrate furiously furiously final deposits. fluffily re | | 65297 | Customer#000065297 | 959154.8985 | 372.07 | ROMANIA | m0mXiaV3It2HppbsJJFy7NpbCTg | 494-810-8919 | final pinto beans cajole slyly. ironic ironic accoun | | 26483 | Customer#000026483 | 948061.2213 | 707.17 | CHINA | f88OjwCo9YgcxsLbr3,IjL,Qh7mWInkEJ | 694-999-7924 | slyly pending requests sleep speci | | 78479 | Customer#000078479 | 945334.8817 | 1874.82 | PERU | 6pSA,sEhfU4wt6lm9Xdp21RRSXV1yrN,U | 536-356-4592 | accounts above the regular foxes are furi | | 30062 | Customer#000030062 | 944860.3661 | 4118.25 | INDIA | JbjUuD3QRxYwgxJPcMmvdVHXkZnRQuLfzON | 536-999-5810 | thin deposits across the furiously quick requests detect blithely after the fluffily ironic deposits. quickly exp | | 17683 | Customer#000017683 | 938510.8227 | 7585.06 | SAUDI ARABIA | RrvzmSsEayaYpea5 | 370-706-1835 | furiously express excuses affix | | 1511 | Customer#000001511 | 935745.5311 | 1453.61 | INDIA | 9HbG,7DzCpaWP0xH | 330-928-7453 | quickly special instructions grow through the carefully regular pearls. furiou | +-----------+--------------------+--------------+-----------+---------------+---------------------------------------+--------------+----------------------------------------------------------------------------------------------------------------------+ |
|
|
tpch |
colmnstore |
|
exec time |
3.15 sec |
1.62 sec |
6. Query 12
|
SELECT l_shipmode, sum(case when o_orderpriority = '1-URGENT' OR o_orderpriority = '2-HIGH' then 1 else 0 end) as high_line_count, sum(case when o_orderpriority <> '1-URGENT' AND o_orderpriority <> '2-HIGH' then 1 else 0 end) AS low_line_count FROM orders, lineitem WHERE o_orderkey = l_orderkey AND l_shipmode in ('MAIL', 'SHIP') AND l_commitdate < l_receiptdate AND l_shipdate < l_commitdate AND l_receiptdate >= date '1994-01-01' AND l_receiptdate < date '1994-01-01' + interval '1' year GROUP BY l_shipmode ORDER BY l_shipmode; |
수행 결과
|
+------------+-----------------+----------------+ | l_shipmode | high_line_count | low_line_count | +------------+-----------------+----------------+ | MAIL | 6252 | 9436 | | SHIP | 6266 | 9200 | +------------+-----------------+----------------+ |
|
|
tpch |
colmnstore |
|
exec time |
7.89 sec |
1.24 sec |
7. Query 14
|
SELECT 100.00 * sum(case when p_type like 'PROMO%' then l_extendedprice * (1 - l_discount) else 0 end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue FROM lineitem, part WHERE l_partkey = p_partkey AND l_shipdate >= date '1995-09-01' AND l_shipdate < date '1995-09-01' + interval '1' month; |
수행 결과
|
+---------------+ | promo_revenue | +---------------+ | 16.6781923715 | +---------------+ |
|
|
tpch |
colmnstore |
|
exec time |
2.02 sec |
0.50 sec |
8. Query 19
|
SELECT sum(l_extendedprice* (1 - l_discount)) as revenue FROM lineitem, part WHERE ( p_partkey = l_partkey AND p_brand = 'Brand#12' AND p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND l_quantity >= 1 AND l_quantity <= 1 + 10 AND p_size between 1 AND 5 AND l_shipmode in ('AIR', 'AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON' ) OR ( p_partkey = l_partkey AND p_brand = 'Brand#23' AND p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND l_quantity >= 10 AND l_quantity <= 10 + 10 AND p_size between 1 AND 10 AND l_shipmode in ('AIR', 'AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON' ) OR ( p_partkey = l_partkey AND p_brand = 'Brand#34' AND p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND l_quantity >= 20 AND l_quantity <= 20 + 10 AND p_size between 1 AND 15 AND l_shipmode in ('AIR', 'AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON' ); |
수행 결과
|
+--------------+ | revenue | +--------------+ | 3382957.8358 | +--------------+ |
|
|
tpch |
colmnstore |
|
exec time |
0.53 sec |
ERROR 1815 (HY000): Internal error: IDB-1000: 'lineitem' and 'part' are not joined. |
Where 절에 ( ) OR ( ) OR ( ) 가 사용되니 ColumnStore 엔진에서는 위와 같은 에러가 발생 했다.
OR 부분을 UNION ALL로 수정 후 쿼리를 수행하면 정상적으로 데이터가 나옴.
|
변경 쿼리
SELECT sum(revenue) FROM ( SELECT sum(l_extendedprice* (1 - l_discount)) as revenue FROM lineitem, part WHERE ( p_partkey = l_partkey AND p_brand = 'Brand#12' AND p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND l_quantity >= 1 AND l_quantity <= 1 + 10 AND p_size between 1 AND 5 AND l_shipmode in ('AIR', 'AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON' ) UNION ALL SELECT sum(l_extendedprice* (1 - l_discount)) as revenue FROM lineitem, part WHERE ( p_partkey = l_partkey AND p_brand = 'Brand#23' AND p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND l_quantity >= 10 AND l_quantity <= 10 + 10 AND p_size between 1 AND 10 AND l_shipmode in ('AIR', 'AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON' ) UNION ALL SELECT sum(l_extendedprice* (1 - l_discount)) as revenue FROM lineitem, part WHERE ( p_partkey = l_partkey AND p_brand = 'Brand#34' AND p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND l_quantity >= 20 AND l_quantity <= 20 + 10 AND p_size between 1 AND 15 AND l_shipmode in ('AIR', 'AIR REG') AND l_shipinstruct = 'DELIVER IN PERSON' ) ) a ; |
|
|
tpch |
colmnstore |
|
exec time |
0.53 sec |
0.34 sec |