Bug #97709 MySQL 8 Select Count(*) is very slow
Submitted: 20 Nov 2019 13:03 Modified: 7 Jan 14:37
Reporter: Vivek Texeira Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:8.0.17 OS:Ubuntu (Ubuntu 18.04.1 LTS)
Assigned to: CPU Architecture:Any

[20 Nov 2019 13:03] Vivek Texeira
Description:
I have a table with 10 columns, in which two columns are JSON field and total records in the table is 1 million. When I do select count(*) it takes more than 10 seconds to display the results, same query and same data in MySQL 5.7 gives results in 0.9 seconds.

If I remove two JSON columns I get results within 1.94 Seconds.

How to repeat:
CREATE TABLE `cases` (
  `id` INT unsigned NOT NULL AUTO_INCREMENT,
  `info` JSON NOT NULL,
  `party` JSON NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

select count(*) from cases;
[20 Nov 2019 16:37] Sinisa Milivojevic
Hi Mr. Texeira,

Thank you for your bug report.

However, this is not a bug.

InnoDB SE is based on MVCC principles, which means that each transaction can see a different number of rows. Hence, since its very beginning, due to this fact, InnoDB SE had to physically count all rows sequentially. When there are no big objects, like JSON or BLOB, then it of course goes faster.

If you wish to get an approximate number of rows instantly, you have to query information_schema.

This is all described in our Reference Manual.

Not a bug.
[21 Nov 2019 8:36] Vivek Texeira
Same data set works faster on MySQL 5.7 InnoDB.
[21 Nov 2019 13:02] Sinisa Milivojevic
Yes, that is expected behaviour.

Organisation of pages has slightly changed, for the new features, so the scanning is slower. But, then again, data scanning is not supposed to be fast.
[22 Nov 2019 5:07] Vivek Texeira
Could you please give me the documentation URL where this is mentioned. Thank you.
[22 Nov 2019 12:45] Norvald Ryeng
Hi Vivek,

I'm sorry, I think Sinisa's explanation is incorrect. There shouldn't be any significant performance drop from 5.7 to 8.0.

However, we're not able to reproduce your results. We don't see any performance regression at all. The test case you provided doesn't contain any data, so we used arbitrary values. Can you provide us with some test data to help us reproduce the bug?

Also, please specify the exact 5.7 version you're using.

Best regards,

Norvald H. Ryeng
[22 Nov 2019 13:51] Vivek Texeira
Hello Norvald,

Following are my table structure and sample data set.

CREATE TABLE `cases` (
 `id` INT unsigned NOT NULL AUTO_INCREMENT,
  `short_description` varchar(511) NOT NULL,
  `case_details` JSON NOT NULL,
  `party_details` JSON NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

INSERT INTO cases (`short_description`,`case_details`,`party_details`) VALUES ('Family case was filed by County Of Los Angeles against John in the jurisdiction of Los Angeles County Superior Courts, Central Civil West Courthouse located in Los Angeles, California.', '{"judge": {"link": "", "name": ""}, "case_type": {"link": "", "name": "Child Support", "group": "Family", "category": "Civil"}, "courthouse": {"link": "", "name": "Central Civil West Courthouse"}, "case_status": {"name": "Other Disposed", "category": "Disposed"}, "jurisdiction": {"link": "", "name": "Los Angeles County Superior Courts"}}', '{"more": {"count": 0}, "parties": [{"Other": {"link": "", "name": "FERNANDEZ ANA JANETTA", "attorneys": [], "representation_type": "Unrepresented"}}, {"Plaintiff": {"link": "", "name": "COUNTY OF LOS ANGELES", "attorneys": [{"link": "", "name": "CHILD SUPPORT SERVICES DEPT.", "analytics_link": ""}], "representation_type": "Attorney Represented"}}, {"Defendant": {"link": "", "name": "RUIZ EDWIN SAMUEL", "attorneys": [], "representation_type": "Unrepresented"}}, {"Undetermined": {"link": "", "name": "FERNANDEZ ANA JANETTA", "attorneys": [], "representation_type": "Unrepresented"}}, {"Petitioner": {"link": "", "name": "COUNTY OF LOS ANGELES", "attorneys": [], "representation_type": "Unrepresented"}}, {"Respondent": {"link": "", "name": "RUIZ EDWIN SAMUEL", "attorneys": [], "representation_type": "Unrepresented"}}]}');

My current MySQL version is 5.7.27-0ubuntu0.18.04.1 (Ubuntu)

MySQL 8 Version is Server version: 8.0.17 MySQL Community Server - GPL

Please add at least 1 Million records to see the difference.
[22 Nov 2019 16:42] Norvald Ryeng
Hi Vivek,

We're still not able to reproduce your results. We tried with both 1M and 4M rows:

5.7.27: 1.5s and 6.2s
8.0.17: 1.4s and 4.6s

You only supplied one row, so we inserted that exact same row 1M and 4M times. No special setup, just a plain server with normal settings.

Do you run with any special config in 5.7.27 or 8.0.17?

Best regards,

Norvald
[23 Nov 2019 7:36] Vivek Texeira
My Instance

Attachment: Screenshot from 2019-11-23 13:04:47.png (image/png, text), 128.18 KiB.

[23 Nov 2019 7:38] Vivek Texeira
Hello Norvald,

I have attached a screen shot, I'm using 2GB Ram with SSD AWS (t3.small) instance type with default MySQL configuration with same data provided to you.
[26 Nov 2019 11:55] Norvald Ryeng
Hi Vivek,

I don't doubt that you experience the slowdown, but without being able to reproduce the issue, there's not much we can do.

You say you're running with 2 GB RAM. That is very low. There may be changes in the buffer sizes that affect your query negatively.

Best regards,

Norvald
[27 Dec 2019 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[15 Jan 16:18] Shane Bester
for what it's worth I was able to verify this,  but re-filed it internally:

Bug 30766089 - 8.0.17: SEVERE REGRESSION IN PERFORMANCE OF SELECT COUNT(*) FROM LARGE TABLE WITH LOB
[15 Jan 17:18] Shane Bester
The problem exists in >=8.0.17 and is even worse when innodb_flush_method=O_DIRECT