Skip to content

Instantly share code, notes, and snippets.

@kazup0n

kazup0n/column store_benchmark.md

Last active December 27, 2016 01:50
Show Gist options
  • Save kazup0n/99c6a9d41b4429702c018c47a50f4eb6 to your computer and use it in GitHub Desktop.
Save kazup0n/99c6a9d41b4429702c018c47a50f4eb6 to your computer and use it in GitHub Desktop.
Download ZIP
ColumnStore and InnoDB performance comparison with tpch
Raw
column store_benchmark.md

column store

                      user     system      total        real
queries/11.sql    0.000000   0.000000   0.000000 (  0.374254)
queries/7.sql     0.000000   0.000000   0.000000 (  4.375019)
queries/6.sql     0.000000   0.000000   0.000000 (  1.119291)
queries/2.sql   　0.000000   0.000000   0.000000 (  0.059151)
ERROR 1815 (HY000) at line 4: Internal error: IDB-3012: Scalar filter and semi join are not from the same pair of tables.
queries/9.sql     0.000000   0.000000   0.000000 ( 10.410045)
queries/1.sql     0.000000   0.000000   0.000000 ( 13.189730)
queries/22.sql    0.000000   0.000000   0.000000 (  7.805449)
queries/16.sql    0.000000   0.000000   0.040000 (  1.774914)
queries/5.sql   　0.000000   0.000000   0.000000 (  0.104437)
ERROR 1815 (HY000) at line 4: Internal error: IDB-1003: Circular joins are not supported.
queries/21.sql  　0.000000   0.010000   0.010000 ( 15.923511)
ERROR 1815 (HY000) at line 4: Internal error: IDB-2001: Join or subselect exceeds memory limit.
queries/18.sql    0.000000   0.000000   0.000000 ( 11.869770)
queries/17.sql    0.000000   0.000000   0.000000 (  0.034782)
ERROR 1815 (HY000) at line 4: Internal error: IDB-3012: Scalar filter and semi join are not from the same pair of tables.
queries/4.sql     0.000000   0.000000   0.000000 (  9.685535)
queries/8.sql     0.000000   0.000000   0.000000 (  0.204195)
queries/14.sql    0.000000   0.000000   0.000000 (  2.096200)
queries/3.sql     0.000000   0.000000   0.000000 (  2.078424)
queries/19.sql    0.000000   0.000000   0.000000 (  0.060604)
  ERROR 1815 (HY000) at line 4: Internal error: IDB-1000: 'lineitem' and 'part' are not joined.
queries/10.sql    0.000000   0.000000   0.000000 (  2.600333)
queries/12.sql    0.000000   0.000000   0.000000 (  1.734252)
queries/15.sql    0.000000   0.010000   0.010000 (  2.760134)
queries/13.sql    0.000000   0.000000   0.000000 (  3.380494)
queries/20.sql    0.000000   0.000000   0.000000 ( 10.938959)

InnoDB

user     system      total        real
queries/11.sql    0.000000   0.000000   0.000000 ( 93.439064)
queries/7.sql     0.010000   0.000000   0.010000 (447.225915)
queries/6.sql     0.000000   0.000000   0.000000 (209.204607)
queries/2.sql     0.000000   0.000000   0.010000 ( 11.977638)
@kazup0n
Copy link
Author

kazup0n commented Dec 27, 2016 •
edited

query 2(best)

+------+--------------------+----------+--------+----------------------+---------+---------+---------------------------+---------+-------------------------------------------------+
| id   | select_type        | table    | type   | possible_keys        | key     | key_len | ref                       | rows    | Extra                                           |
+------+--------------------+----------+--------+----------------------+---------+---------+---------------------------+---------+-------------------------------------------------+
|    1 | PRIMARY            | part     | ALL    | PRIMARY              | NULL    | NULL    | NULL                      | 1238586 | Using where; Using temporary; Using filesort    |
|    1 | PRIMARY            | partsupp | ref    | PRIMARY              | PRIMARY | 4       | tpch.part.P_PARTKEY       |       2 | Using where                                     |
|    1 | PRIMARY            | supplier | eq_ref | PRIMARY,SUPPLIER_FK1 | PRIMARY | 4       | tpch.partsupp.PS_SUPPKEY  |       1 |                                                 |
|    1 | PRIMARY            | nation   | eq_ref | PRIMARY,NATION_FK1   | PRIMARY | 4       | tpch.supplier.S_NATIONKEY |       1 |                                                 |
|    1 | PRIMARY            | region   | ALL    | PRIMARY              | NULL    | NULL    | NULL                      |       5 | Using where; Using join buffer (flat, BNL join) |
|    2 | DEPENDENT SUBQUERY | partsupp | ref    | PRIMARY              | PRIMARY | 4       | tpch.part.P_PARTKEY       |       2 |                                                 |
|    2 | DEPENDENT SUBQUERY | supplier | eq_ref | PRIMARY,SUPPLIER_FK1 | PRIMARY | 4       | tpch.partsupp.PS_SUPPKEY  |       1 |                                                 |
|    2 | DEPENDENT SUBQUERY | nation   | eq_ref | PRIMARY,NATION_FK1   | PRIMARY | 4       | tpch.supplier.S_NATIONKEY |       1 |                                                 |
|    2 | DEPENDENT SUBQUERY | region   | eq_ref | PRIMARY              | PRIMARY | 4       | tpch.nation.N_REGIONKEY   |       1 | Using where                                     |
+------+--------------------+----------+--------+----------------------+---------+---------+---------------------------+---------+-------------------------------------------------+

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment