Secondary index performance issue on huge data

sql

(Derek Zhang) #1

I currently have a table. “id” is the primary key, “column1” and “column2” each has a secondary index. “column3”, “column4” and “column5” are the regular columns with no index.

I generated data to populate the table, column1 was randomly set between 0-99999.
When the table contains 50 million records, the following query takes 9 seconds.
select * from table where column1<200;
When the table contains 312 million records, the following query takes 1mins40s.
select * from table where column1<32;

The expected # of results of the above two queries are basically the same (about 100,000). Why the second query is more than 10 times slower than the first query? I would expect the same time as long as the query results size are the same since the index is used so no table scan is done. Could anyone explain why the query is much slower on larger data?


(jordan) #2

Hi @derek.z,

Could you please give us more information about your schema, setup and queries? In general, it’s very hard to understand performance problems like this without more information - so any time you can fill us in with more details, the more likely it is that we can answer your question in one shot :slight_smile:

Please provide:

  1. SHOW CREATE TABLE for all relevant tables
  2. EXPLAIN for all relevant queries, EXPLAIN(DISTSQL) for all relevant queries if you have more than one node
  3. number and type of nodes in your cluster
  4. other details (are there other queries running in the cluster?)

You could also include results of session tracing for each of the queries. That might help explain what’s going on.


(jordan) #3

Also, just realized, you’re doing a SELECT *, which actually has to stream all results. Is there a difference in the speed of SELECT COUNT(*) on both queries? If not, the problem is likely however your client does buffering.


(Derek Zhang) #4

I have two tables with the same schema. To not reveal internal information, I replaced the column names with columnX.
±--------------±-------±------±--------±---------------------------------------------+
| Field | Type | Null | Default | Indices |
±--------------±-------±------±--------±---------------------------------------------+
| id | UUID | false | NULL | {“primary”,“column4Index”,“hereTimeIndex”} | random UUID
| column1 | INT | true | NULL | {} | random [0-999]
| column2 | BYTES | true | NULL | {} | “something”.getBytes()
| column3 | STRING | true | NULL | {} | “json”
| column4 | INT | true | NULL | {“column4Index”} | timestamp
| hour | INT | true | NULL | {“hereTimeIndex”} | random [0-99999]
| column6 | INT | true | NULL | {} | random [0-9999]
| column7 | BOOL | true | NULL | {} | true or false
| column8 | INT | true | NULL | {} | random [0-99]
| column9 | STRING | true | NULL | {} | random [“type0”-“type9”]
±--------------±-------±------±--------±---------------------------------------------+

One table is called “perf_1b” which contains 312,863,000 records (targeting at 1 billion records, but stopped ingestion at 312M), the other is “perf_50m” which contains 50,000,000 records.

explain select * from “perf_50m” where hour<200;
±-----------±------±-----------------------+
| Tree | Field | Description |
±-----------±------±-----------------------+
| index-join | | |
| ├── scan | | |
| │ | table | perf_50m@hereTimeIndex |
| │ | spans | /!NULL-/200 |
| └── scan | | |
| | table | perf_50m@primary |
±-----------±------±-----------------------+

explain select * from “perf_1b” where hour<32;
±-----------±------±----------------------+
| Tree | Field | Description |
±-----------±------±----------------------+
| index-join | | |
| ├── scan | | |
| │ | table | perf_1b@hereTimeIndex |
| │ | spans | /!NULL-/32 |
| └── scan | | |
| | table | perf_1b@primary |
±-----------±------±----------------------+

explain (distsql) select * from “perf_50m” where hour<200;
https://cockroachdb.github.io/distsqlplan/decode.html?eJyUkc1OwzAQhO88BZrzIiUp5ccnX8uhRVVvKEImXoqlxrZsRyqq8u4o8YEWtQGOnt1vZrQ-wDrNS9VyhHhBCUKFmuCDazhGFwY5Ly30HqIgGOu7NMg1oXGBIQ5IJu0YAkt34zwImpMyu3GpJ7gufSMxqS1DzHo6si2nbTfqbcdrVprDiTk-OPDGtLywmvfSc3h_nRctCKsuiWtZ4lJ8-Z_4J2fsuXQfTKvC55lckhXJGclbknOSdyTvST6QfCRZFhc7VSedfrn0mqN3NvKfjl30NYH1lvNvRteFhp-Da8aY_FyN3ChojilPZ_mxsHk0FDyGy0m4moarSbj4Adf91VcAAAD__xTo3t4=

explain (distsql) select * from “perf_1b” where hour<32;
https://cockroachdb.github.io/distsqlplan/decode.html?eJzEkT9v8jAQxvf3U7y6-SrhBPonk1c6QIXYqqgK8ZVGAp91diQqlO9eJR5oaEjpxOizn_v95OcIlg0tij15yF5BAUIKOYITLsl7lnYcH83NAbIJQmVdHdpxjlCyEGRHCFXYEWSwLjY7WlFhSADBUCiqXbf4g4TW1Z7m1tBBO5L3N7UBhGUdsv9aQd4gcB1Om30otgSZavB6-jNXdgjupNoX8vkTizpBnaKeop6hvkf9gPoR9RNqNbmolFxUOpnUlsWQkOmJ5M2A9ILv2J09GwanPbC6aRO_0G_dxIDSirxj6-mqr560TZHZUqzVcy0lvQiXHSYel12uGxjyId6qeJjbeNUKfg-r0XDSC6vzcDIano2T09HwdDw8_ZN23vz7CgAA__927moz

Cluster information: 5 nodes, 4 cpu, 8G ram, 220GB disk/node, 30% cache, 30% sql memory

Queries run on separate sql client pod while no other queries running in the cluster.

Let me know anything else you need.

Thanks,
Derek


(jordan) #5

Hi Derek,

Thanks for the information. Can you tell me whether the SELECT COUNT(*) performance of the queries is the same as the SELECT * performance? Also, are all of the database nodes in the same region? Is there significant latency between each node?

Jordan


(Derek Zhang) #6

The SELECT COUNT() is much faster than SELECT *
The following query takes 25ms:
select count(
) from “perf_50m” where hour<50;
The following query takes 24s:
select * from “perf_50m” where hour<50;


(jordan) #7

Is it the same for the larger, perf_1b table?


(Derek Zhang) #8

The following query takes 32ms:
select count(*) from “perf_1b” where hour<8;
The following query takes 25s:
select * from “perf_1b” where hour<8;

Looks right now the time is almost the same for the two tables, as long as the result size is the same. I may recorded the wrong query results in the previous tests. Let me investigate for now. I will reply with new discoveries if any.


(Derek Zhang) #9

Just forget about the previous run on 50m table. Is it normal to run for 25 second to retrieve 25000 records from a query such as “select * from table where secondaryIndex<?”?
From my understanding, the results should be returned immediately, right? Is it because it took 25s to load all the 25000 results to the client? Is there a way to improve the time?

Thanks,
Derek


(jordan) #10

I’m also surprised that that query was so slow, but there could be several reasons.

Were you using the cockroach sql CLI to measure your timings? By default, the CLI isn’t a good place to do performance testing, because it does a bunch of extra work to make sure the results are displayed in a “pretty” format.

If you type \set display_format=csv or raw before the query, it will disable the “pretty row” printing and the results will be streamed much more quickly. Unfortunately, the timings reported by the CLI include the time that it takes to format the table - and that part of the code is not very optimized right now because nobody’s had time.


(Derek Zhang) #11

[Resolved] Both csv and raw format return results immediately. Thank you so much for the explanation.

Thanks,
Derek


(Raphael 'kena' Poss) #12

I have filed https://github.com/cockroachdb/cockroach/issues/32656 to fix the time printed by cockroach sql.