Skip to content
This repository has been archived by the owner on Jun 12, 2020. It is now read-only.

The sql bench test insert performance problem

Jump to bottom
RIch Prohaska edited this page Apr 24, 2014 · 11 revisions

The sql-bench test-insert program builds a small table of about 300,000 rows with inserts and updates. It then runs a sequence of queries on the table.

Why does this query

mysql> select count(*) from bench1 as a left outer join bench1 as b on (a.id2=b.id3)

take 5 seconds with TokuDB 7.1.0 and 1200 seconds with TokuDB 7.1.5?

mysql> show create table bench1
| bench1 | CREATE TABLE `bench1` (
  `id` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `id3` int(11) NOT NULL,
  `dummy1` char(30) DEFAULT NULL,
  PRIMARY KEY (`id`,`id2`),
  KEY `ix_id3` (`id3`)
) ENGINE=TokuDB DEFAULT CHARSET=latin1

mysql> show table status
| Name   | Engine | Version | Row_format  | Rows   | Avg_row_length | Data_length | Max_data_length     | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
| bench1 | TokuDB |      10 | tokudb_zlib | 300000 |             43 |    12900000 | 9223372036854775807 |      3900000 |  11444224 |           NULL | 2014-04-23 13:18:19 | 2014-04-23 13:23:17 | NULL       | latin1_swedish_ci |     NULL |                |         | 

mysql> explain select count(*) from bench1 as a left outer join bench1 as b on (a.id2=b.id3)
+----+-------------+-------+-------+---------------+--------+---------+------------+--------+-------------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref        | rows   | Extra       |
+----+-------------+-------+-------+---------------+--------+---------+------------+--------+-------------+
|  1 | SIMPLE      | a     | index | NULL          | ix_id3 | 4       | NULL       | 300000 | Using index | 
|  1 | SIMPLE      | b     | ref   | ix_id3        | ix_id3 | 4       | test.a.id2 |     16 | Using index | 
+----+-------------+-------+-------+---------------+--------+---------+------------+--------+-------------+

mysql> select count(*) from bench1 as a left outer join bench1 as b on (a.id2=b.id3)
+----------+
| count(*) |
+----------+
|   535000 |
+----------+

After mysqld restart, the query takes 5 seconds.

After optimize table, the query takes 5 seconds.

When running the query, gdb captures the mysqld server thread here:

#0  0x00002aaaad13f921 in le_iterate_is_del (le=0x2aad1219092a, f=0x2aaaad10345b <does_txn_read_entry(TXNID, TOKUTXN)>, is_delp=0x2aad102c25bb, context=0x2aace5c26080)
    at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/storage/tokudb/ft-index/ft/ule.cc:2082
#1  0x00002aaaad103a16 in is_le_val_del (le=0x2aad1219092a, ftcursor=0x2aace5c53340) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/storage/tokudb/ft-index/ft/ft-ops.cc:4317
#2  0x00002aaaad1055d7 in ft_search_basement_node (node=0x2aaaac40f740, child_to_search=5, bn=0x2aad10b55c40, search=0x2aad102c2c40,
    getf=0x2aaaad093e90 <c_getf_set_range_callback(ITEMLEN, bytevec, ITEMLEN, bytevec, void*, bool)>, getf_v=0x2aad102c2cd0, doprefetch=0x2aad102c2a8f, ftcursor=0x2aace5c53340, can_bulk_fetch=false)
    at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/storage/tokudb/ft-index/ft/ft-ops.cc:5027
#3  0x00002aaaad10648f in ft_search_node (brt=0x2aace5c12aa0, node=0x2aaaac40f740, search=0x2aad102c2c40, child_to_search=5,
    getf=0x2aaaad093e90 <c_getf_set_range_callback(ITEMLEN, bytevec, ITEMLEN, bytevec, void*, bool)>, getf_v=0x2aad102c2cd0, doprefetch=0x2aad102c2a8f, ftcursor=0x2aace5c53340,
    unlockers=0x2aad102c27b0, ancestors=0x2aad102c2890, bounds=0x2aad102c2940, can_bulk_fetch=false) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/storage/tokudb/ft-index/ft/ft-ops.cc:5397
#4  0x00002aaaad105e78 in ft_search_child (brt=0x2aace5c12aa0, node=0x2aace5c12e60, childnum=8, search=0x2aad102c2c40,
    getf=0x2aaaad093e90 <c_getf_set_range_callback(ITEMLEN, bytevec, ITEMLEN, bytevec, void*, bool)>, getf_v=0x2aad102c2cd0, doprefetch=0x2aad102c2a8f, ftcursor=0x2aace5c53340,
    unlockers=0x2aad102c29f0, ancestors=0x0, bounds=0x2aad102c2940, can_bulk_fetch=false) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/storage/tokudb/ft-index/ft/ft-ops.cc:5226
#5  0x00002aaaad106442 in ft_search_node (brt=0x2aace5c12aa0, node=0x2aace5c12e60, search=0x2aad102c2c40, child_to_search=8,
    getf=0x2aaaad093e90 <c_getf_set_range_callback(ITEMLEN, bytevec, ITEMLEN, bytevec, void*, bool)>, getf_v=0x2aad102c2cd0, doprefetch=0x2aad102c2a8f, ftcursor=0x2aace5c53340,
    unlockers=0x2aad102c29f0, ancestors=0x0, bounds=0x2aaaad19e060 <infinite_bounds>, can_bulk_fetch=false) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/storage/tokudb/ft-index/ft/ft-ops.cc:5385
#6  0x00002aaaad1067ff in toku_ft_search (brt=0x2aace5c12aa0, search=0x2aad102c2c40, getf=0x2aaaad093e90 <c_getf_set_range_callback(ITEMLEN, bytevec, ITEMLEN, bytevec, void*, bool)>,
    getf_v=0x2aad102c2cd0, ftcursor=0x2aace5c53340, can_bulk_fetch=false) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/storage/tokudb/ft-index/ft/ft-ops.cc:5525
#7  0x00002aaaad106c4d in ft_cursor_search (cursor=0x2aace5c53340, search=0x2aad102c2c40, getf=0x2aaaad093e90 <c_getf_set_range_callback(ITEMLEN, bytevec, ITEMLEN, bytevec, void*, bool)>,
    getf_v=0x2aad102c2cd0, can_bulk_fetch=false) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/storage/tokudb/ft-index/ft/ft-ops.cc:5590
#8  0x00002aaaad10766a in toku_ft_cursor_set_range (cursor=0x2aace5c53340, key=0x2aad102c2e90, getf=0x2aaaad093e90 <c_getf_set_range_callback(ITEMLEN, bytevec, ITEMLEN, bytevec, void*, bool)>,
    getf_v=0x2aad102c2cd0) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/storage/tokudb/ft-index/ft/ft-ops.cc:5811
#9  0x00002aaaad093e3b in c_getf_set_range (c=0x2aace5c1cb40, flag=8388608, key=0x2aad102c2e90, f=0x2aaaad052f31 <smart_dbt_callback_ir_keyread(DBT const*, DBT const*, void*)>, extra=0x2aad102c2e40)
    at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/storage/tokudb/ft-index/src/ydb_cursor.cc:590
#10 0x00002aaaad05f091 in ha_tokudb::index_read (this=0x2aace5efa010, buf=0x2aace5c3c410 "\377", key=0x2aace5d61fe8 "\375l", key_len=4, find_flag=HA_READ_KEY_EXACT)
    at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/storage/tokudb/ha_tokudb.cc:4836
#11 0x000000000077ab64 in handler::index_read_map (this=0x2aace5efa010, buf=0x2aace5c3c410 "\377", key=0x2aace5d61fe8 "\375l", keypart_map=1, find_flag=HA_READ_KEY_EXACT)
    at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/sql/handler.h:1874
#12 0x000000000065cce1 in join_read_always_key (tab=0x2aad10fffaa0) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/sql/sql_select.cc:12455
#13 0x000000000065b51a in sub_select (join=0x2aad10ffe010, join_tab=0x2aad10fffaa0, end_of_records=false) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/sql/sql_select.cc:11806
#14 0x000000000065b8d1 in evaluate_join_record (join=0x2aad10ffe010, join_tab=0x2aad10fff838, error=0) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/sql/sql_select.cc:11954
#15 0x000000000065b562 in sub_select (join=0x2aad10ffe010, join_tab=0x2aad10fff838, end_of_records=false) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/sql/sql_select.cc:11812
#16 0x000000000065b118 in do_select (join=0x2aad10ffe010, fields=0x2aad10fff678, table=0x0, procedure=0x0) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/sql/sql_select.cc:11572
#17 0x0000000000644727 in JOIN::exec (this=0x2aad10ffe010) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/sql/sql_select.cc:2385
#18 0x0000000000644f23 in mysql_select (thd=0x2aacccba1000, rref_pointer_array=0x2aacccba3260, tables=0x2aace5d60338, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0,
    proc_param=0x0, select_options=2147748608, result=0x2aace5d617e8, unit=0x2aacccba2a58, select_lex=0x2aacccba3078) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/sql/sql_select.cc:2604
#19 0x000000000063d1e1 in handle_select (thd=0x2aacccba1000, lex=0x2aacccba29a8, result=0x2aace5d617e8, setup_tables_done_option=0)
    at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/sql/sql_select.cc:297
#20 0x000000000061a900 in execute_sqlcom_select (thd=0x2aacccba1000, all_tables=0x2aace5d60338) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/sql/sql_parse.cc:4641
#21 0x0000000000613bfe in mysql_execute_command (thd=0x2aacccba1000) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/sql/sql_parse.cc:2181
#22 0x000000000061ca00 in mysql_parse (thd=0x2aacccba1000, rawbuf=0x2aace5d60010 "select count(*) from bench1 as a left outer join bench1 as b on (a.id2=b.id3)", length=77, parser_state=0x2aad102c48b0)
    at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/sql/sql_parse.cc:5678
#23 0x00000000006113b2 in dispatch_command (command=COM_QUERY, thd=0x2aacccba1000, packet=0x2aace760c001 "", packet_length=77) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/sql/sql_parse.cc:1044
#24 0x000000000061073a in do_command (thd=0x2aacccba1000) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/sql/sql_parse.cc:777
#25 0x00000000006ef0d7 in do_handle_one_connection (thd_arg=0x2aacccba1000) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/sql/sql_connect.cc:862
#26 0x00000000006eec36 in handle_one_connection (arg=0x2aacccba1000) at /data/prohaska/my5536.sqlbench.rc4/mysql-5.5.36/sql/sql_connect.cc:781
#27 0x000000319980683d in start_thread () from /lib64/libpthread.so.0
#28 0x00000031990d526d in clone () from /lib64/libc.so.6
Clone this wiki locally