0

I am aware there are many questions on this topic, mysql has not improved much on these single-query performance bugs in the past years but my case is more unusual.

If mysql would access the table index as it was supposed to do and count the entries it should take 70mil entries * 4 byte per entry = ~275MB at ~1000mb/sec = 270 milliseconds to count the entries
The actual mysql performance is about 13500 times slower than it should be.

Given that I have 100 gig of RAM for innodb I'd expect a subsequent count to be done in single digit milliseconds (not in again one hour)

I have the same behaviour on 3 servers, with slgihtly 3 different mysql versions on 5 different innodb tables, so it's not a localized issue.
I have had more severe cases than the current one (count takes about an hour, I've seen it going for days too).
In this case the table is compressed (which should actually increase the speed)

1590603 root    localhost       locdb1       Query   2004    Sending data    select count(*) from logos     1003611 0       0

EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "35130458.29"
    },
    "table": {
      "table_name": "logos",
      "access_type": "index",
      "key": "PRIMARY",
      "used_key_parts": [
        "id"
      ],
      "key_length": "4",
      "rows_examined_per_scan": 9851928,
      "rows_produced_per_join": 9851928,
      "filtered": "100.00",
      "using_index": true,
      "cost_info": {
        "read_cost": "34145265.49",
        "eval_cost": "985192.80",
        "prefix_cost": "35130458.29",
        "data_read_per_join": "2G"
      }
    }
  }
}


CREATE TABLE `logos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_raw` mediumtext COLLATE utf8_bin,
  `a` mediumblob,
  `ab` varchar(3) COLLATE utf8_bin DEFAULT NULL,
  `b` mediumblob,
  `bb` varchar(3) COLLATE utf8_bin DEFAULT NULL,
  `last_error_code` smallint(6) DEFAULT NULL,
  `date_found` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=67945433 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPRESSED

IBD Filesize is 370 GiB
Disk performance is reliable 24,000 IPS at max 1.2 GiB/sec I've tried moving the table on a single non used disk and mysql was accessing it constantly at 4 MiB/sec (in that test case max speed would actually be 250MiB/sec at max 16k iops) CPU is 20% loaded

The table in this case was freshly rebuilt, I've also tested this on freshly optimized tables without improvement.

I'd be happy with 5 seconds as well. I'm not happy with an hour.

  • Your calculation, unfortunately, does not take into account the fact that the primary key, being the table's clustered index, contains not just the id values, but also values of all other columns for each table row. – mustaccio Jan 10 at 19:51
  • @mustaccio This won't make any difference, it's 14,000 times slower than it should be. Go make it 7000 times slower or 1000 times slower .. it's all the same result. Mysql is slower by magnitudes than it should, it is utilizing only 0.01% of the disk speed doing a count. – John Jan 10 at 19:58
  • You seem to have a very simplistic view of how relational database engines work. – mustaccio Jan 10 at 20:05
  • @mustaccio As a developer myself I know quite well how this works, even if written simplified. This is not a relational database task, it's a database storage engine task. There is nothing relational about counting entries in an index, it's a very straightforward task. – John Jan 10 at 20:47
  • You should probably check out the MySQL storage engine API. Storage engines are incapable of "counting entries in an index"; all they do is translate rows from the engine-specific format into the MySQL internal format and return them to the server one by one, BLOBs and all, in response to index_next() calls. It's the [relational] server that does counting or whatever else it thinks appropriate. – mustaccio Jan 11 at 0:58
2

Add INDEX(last_error_code), then do SELECT COUNT(*) FROM logos, it will run a lot faster because it will use the much smaller BTree for that secondary index.

SHOW TABLE STATUS LIKE 'logos' will give you an approximate count.

Why is the PK bloated? InnoDB orders the data by the PRIMARY KEY; hence the PK, as an INDEX is essentially included in the data. So to fetch a PK implies fetching the entire row. (Caveat: Bulky columns such as your texts and blobs are stored in a separate place, so they are not fetched when doing the COUNT(*).)

The Data+PK is in one B+Tree; each secondary index is in its own B+Tree.

Any "size" math (such as INT takes 4 bytes) needs to be multiplied by about 2 or 3 due to various overheads -- per column, per row, per block, etc. And the block nature has its own overhead due to block splits.

If the entire table happens to be cached in RAM (that is, in the innodb_buffer_pool), then there won't be any I/O. Still there is a non-trivial amount of CPU work to scan through your millions of rows.

I would guess that your table is slightly bigger than will fit in the buffer_pool. So, every time you do that COUNT(*) it is competing with all other queries for room in the cache. Not cool.

Note that the index I propose will take about 3GB, so it has a chance of being cached and perhaps even staying cached. The first time (after a restart) you do the COUNT(*), it will be slow because of lots of I/O. The second time (unless blocks have been bumped out of cache), it will be faster; perhaps ten times as fast. That will probably be less than an hour, but more than a few seconds.

What is the value of innodb_buffer_pool_size?

How do others solve the sluggishness of COUNT(*)? Have you noticed that search engines, if they say anything about 'how many', give you round numbers like "About 14,000,000 hits"? That's one approach -- periodically get the exact number and round it off until you get around to checking again.

Or they have, say, daily subtotals. Then the query SUMs up the daily counts. This is much faster. (But it only works for stuff that is continually coming in and not changing.)

Or ... (There are other techniques.) What is your use case?

The idea of "random areas, plus extrapolation" probably has a lot of hassles; I would not embark on such. Anyway, that is essentially what SHOW TABLE STATUS does.

MySQL 8.0.14 has Parallel scanning of by PRIMARY KEY (cf innodb_parallel_read_threads) for COUNT(*) w/o WHERE. That will provide a little speedup for your original query.

| improve this answer | |
  • First of all, thanks for responding Rick :) I'll look into that, what is bloating the primary key so much that this makes a big difference in my case ? How do big data environments solve that, dumping mysql totally and going for column/graph db or are there solutions with mysql ? My best current approach for counts is to make extrapolations, using 20 threads to count random areas and then extrapolate the total number hoping it's exact enough. Not too happy with it. – John Jan 11 at 6:16
  • @John - I augmented my Answer in several ways. – Rick James Jan 11 at 6:54
  • That's a great answer, I didn't know the PK is spread on the whole data storage! A secondary index speeds up the count indeed. The general sequential access speed on IO is still a problem but given I've invested dozens of hours in parameter tuning I don't think anything can solve that aside of internal changes in mysql. – John Jan 11 at 20:00
  • @John Please post to pastebin.com A) SHOW GLOBAL STATUS; after minimum of 24 hours of uptme and B) SHOW GLOBAL VARIABLES; for server workload analysis. – Wilson Hauck Jan 12 at 22:28
  • @WilsonHauck The problem is that those status messages are always full of NDA stuff AND the server is in production so it's polluted with alot of other queries. The problem resolved itself after I deleted all indexes and created them again .. I forgot about the mysql index bugs, they are still present in 8.0.13 at least. Write-heavy tables index degradation. After 1-2 months of writing some indexes needs to be re-created resulting in 100-300 times performance increase. count(*) is now from 2 days to 9 minutes – John Jan 17 at 14:13
1

I had the same issue on a data base with 2.6 million rows. I changed Count(*) to Count(wx_key). The wx_key is unique and on all rows. Execute time went from 77.8 secs to 7.2 secs. Mysql version 8.0.21.

| improve this answer | |
  • 2
    Now run the faster one, then the slower one. I suspect it is caching, not a real difference in performance. – Rick James Jul 30 at 3:10
0

Alternative to SELECT COUNT(*) FROM logos;

SELECT table_rows FROM information_schema.tables WHERE table_name = 'logos';

if you can survive with a 'close' number.

Other qualifiers may be necessary for schema, etc.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.