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

The sql bench test insert performance problem

RIch Prohaska edited this page Apr 23, 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 on TokuDB 7.1.0 and 1200 seconds on 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.

Clone this wiki locally