This repository was archived by the owner on Jun 12, 2020. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 129
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.