Bug #87621 Huge InnoDB slowdown when selecting strings without indexes

• Submitted: 31 Aug 2017 9:45
• Modified: 4 Sep 2017 9:54
• Reporter: Sveta Smirnova (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.6.37, 5.7.19
• OS: Any
• CPU Architecture: Any

[31 Aug 2017 9:45] Sveta Smirnova

Description:
Originally reported at https://bugs.launchpad.net/percona-server/+bug/1714094

InnoDB performs very poorly if selects string from a table without using indexes. MyISAM, however, performs 30 times faster.

How to repeat:
1. Download csv file from
2. Start MTR with: ./mtr --start innodb --mysqld=--innodb_buffer_pool_size=2G --mysqld=--innodb_log_file_size=512M &
3. Put csv file into var/mysqld.1/data/test/
4. Run test:

mysql> CREATE TABLE `testtable` ( `col3` text ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0,45 sec)

mysql> load data infile 'data.csv' into table testtable fields enclosed by '"' ignore 1 lines;
Query OK, 5974526 rows affected (23 min 36,62 sec)
Records: 5974526 Deleted: 0 Skipped: 0 Warnings: 0

mysql> Select * from testtable where col3 LIKE '%qwertyd%'; Select * from testtable LIMIT 59745270,10;
Empty set (5 min 6,33 sec)

Empty set (4 min 43,52 sec)

mysql> alter table testtable engine=myisam;
Query OK, 5974526 rows affected (6 min 27,87 sec)
Records: 5974526 Duplicates: 0 Warnings: 0

mysql> Select * from testtable where col3 LIKE '%qwertyd%'; Select * from testtable LIMIT 59745270,10;
Empty set (14,24 sec)

Empty set (11,08 sec)

[31 Aug 2017 9:52] Sveta Smirnova

Using VARCHAR column instead of TEXT changes nothing.

[1 Sep 2017 11:49] MySQL Verification Team

Hello Sveta,

Thank you for the report and test case.
I tried to reproduce the issue starting from a relatively moderate box to huge boxes(mostly idle boxes), I saw 2x|4x difference but I'm not seeing the "huge" difference as you observed. Moreover, if you see my test results - even data load time is significantly low while testing at my end. Could you please confirm if there is anything else(I/O issues etc) causing the issue?   Joining my work log shortly for your reference.

Regards,
Umesh

[1 Sep 2017 11:50] MySQL Verification Team

Test results - 5.7.19, 5.6.37

Attachment: 87621_5.6.37_5.7.19.results (application/octet-stream, text), 23.20 KiB.

[1 Sep 2017 11:53] Sveta Smirnova

Hi Umesh,

yes, I am testing on laptop with spinning disk. This is certainly machine for the production use.

But you can simply generate larger file:

1. Install R
2. Download attached file
3. chmod 755 gen_data.R
4. ./gen_data.R 100000000

[1 Sep 2017 11:54] Sveta Smirnova

R script to generate data

Attachment: gen_data.R (text/x-r-source), 321 bytes.

[1 Sep 2017 11:55] Sveta Smirnova

Redirect output of data generator if you don't want to have 100M rows on your terminal: ./gen_data.R 100000000 > data.csv

[1 Sep 2017 11:56] Sveta Smirnova

*NOT machine for the production use. =)

[1 Sep 2017 12:09] MySQL Verification Team

Thank you Sveta, I'll give it a try and comeback to you if needed.
And, yes I have repeated the slow down during my tests 2x|4x times.

regards,
umesh

[4 Sep 2017 9:54] MySQL Verification Team

Thank you Sveta.

regards,
umesh

[4 Sep 2017 9:56] MySQL Verification Team

5.7.19  - Test results with 100M load file

Attachment: 87621.results (application/octet-stream, text), 4.97 KiB.

[8 Sep 2017 11:21] Athi Nivas

@Umesh Shastry I've been facing the same problem...could you explain more on why the slowness is actually happening...is it due to way, Innodb process the index (B+ Tree Traversal) or other things? 

Even if the data is in memory(buffer pool), the time lapse is too high 
 Select * from testtable LIMIT 59745270,10;
Empty set (5 min 6,33 sec)

--Athi

[28 Sep 2017 9:11] Athi Nivas

Hi,

Any update on why the slowness occurs?? 

Much Thanks,
Athi