Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Sizing Calculator: Estimating Size projections for Table with existing data #7468

Open
drewdeally opened this issue Jun 3, 2020 · 18 comments
Labels
C-doc-improvement O-sales-eng Internal source: Sales Engineering

Comments

@drewdeally
Copy link

drewdeally commented Jun 3, 2020

Andrew Deally (drewdeally) commented:

size projection SQL

WITH
table_rows
AS (
SELECT
count(1) AS cnt
FROM
ycsb1000000.usertable
),
table_size
AS (
SELECT
sum(
(
crdb_internal.range_stats(
start_key
)->>'key_bytes'
)::INT8
+ (
crdb_internal.range_stats(
start_key
)->>'val_bytes'
)::INT8
)
AS size
FROM
crdb_internal.ranges_no_leases
WHERE
table_name = 'usertable'
AND database_name = 'ycsb1000000'
GROUP BY
database_name, table_name
)
SELECT
cnt,
size,
size / cnt AS size_per_row,
(size / cnt * 10000000) AS estamted_size_10000000,
size / cnt * cnt AS validate
FROM
table_rows, table_size;
cnt | size | size_per_row | estamted_size_10000000 | validate
----------+-----------+--------------+------------------------+-------------------
1000000 | 553375965 | 553.375965 | 5533759650.000000 | 553375965.000000
(1 row)
Time: 4.313701085s
root@:26257/defaultdb> WITH table_rows AS (
SELECT count(1) AS cnt FROM ycsb10000000.usertable
),
table_size AS (
SELECT sum(
(
crdb_internal.range_stats(
start_key
)->>'key_bytes'
)::INT8
+ (
crdb_internal.range_stats(
start_key
)->>'val_bytes'
)::INT8
) AS size
FROM crdb_internal.ranges_no_leases
WHERE table_name = 'usertable'
AND database_name = 'ycsb10000000'
GROUP BY database_name, table_name
)
SELECT cnt,
size,
size / cnt AS size_per_row,
(size / cnt * 10000000) estamted_size_10000000,
size / cnt * cnt AS validate
FROM table_rows, table_size;
cnt | size | size_per_row | estamted_size_10000000 | validate
-----------+------------+--------------+------------------------+---------------------
10000000 | 5533745768 | 553.3745768 | 5533745768.0000000 | 5533745768.0000000
(1 row)
Time: 54.007199522s
root@:26257/defaultdb>

using bytes per row from ycsb1000000.usertable
and validating with ycsb10000000.usertable

5533759650.000000 calculated
actual is 5533745768

root@:26257/ycsb10000000> WITH
table_rows
AS (
SELECT
count(1) AS cnt
FROM
ycsb1000000.usertable
),
table_size
AS (
SELECT
sum(
(
crdb_internal.range_stats(
start_key
)->>'key_bytes'
)::INT8
+ (
crdb_internal.range_stats(
start_key
)->>'val_bytes'
)::INT8
)
AS size
FROM
crdb_internal.ranges_no_leases
WHERE
table_name = 'usertable'
AND database_name = 'ycsb1000000'
GROUP BY
database_name, table_name
)
SELECT
cnt,
size,
size / cnt AS size_per_row,
(size / cnt * 10000000) AS estamted_size_10000000,
size / cnt * cnt AS validate
FROM
table_rows, table_size;
cnt | size | size_per_row | estamted_size_10000000 | validate
----------+-----------+--------------+------------------------+------------------
1000000 | 719569410 | 719.56941 | 7195694100.00000 | 719569410.00000
(1 row)
Time: 659.006037ms
root@:26257/ycsb10000000> WITH table_rows AS (
SELECT count(1) AS cnt FROM ycsb10000000.usertable
),
table_size AS (
SELECT sum(
(
crdb_internal.range_stats(
start_key
)->>'key_bytes'
)::INT8
+ (
crdb_internal.range_stats(
start_key
)->>'val_bytes'
)::INT8
) AS size
FROM crdb_internal.ranges_no_leases
WHERE table_name = 'usertable'
AND database_name = 'ycsb10000000'
GROUP BY database_name, table_name
)
SELECT cnt,
size,
size / cnt AS size_per_row,
(size / cnt * 10000000) estamted_size_10000000,
size / cnt * cnt AS validate
FROM table_rows, table_size;
cnt | size | size_per_row | estamted_size_10000000 | validate
-----------+------------+--------------+------------------------+---------------------
10000000 | 7195676432 | 719.5676432 | 7195676432.0000000 | 7195676432.0000000
(1 row)
Time: 4.719286553s
root@:26257/ycsb10000000>

6257/tpch> WITH
table_rows
AS (SELECT count(1) AS cnt FROM tpchsf10.customer),
table_size
AS (
SELECT
sum(
(
crdb_internal.range_stats(
start_key
)->>'key_bytes'
)::INT8
+ (
crdb_internal.range_stats(
start_key
)->>'val_bytes'
)::INT8
)
AS size
FROM
crdb_internal.ranges_no_leases
WHERE
table_name = 'customer'
AND database_name = 'tpchsf10'
GROUP BY
database_name, table_name
)
SELECT
cnt,
size,
size / cnt AS size_per_row,
(size / cnt * 15000000) AS estamted_size_15000000,
size / cnt * cnt AS validate
FROM
table_rows, table_size;
cnt | size | size_per_row | estamted_size_15000000 | validate
----------+-----------+-----------------------+------------------------------+------------------------------
1500000 | 322310089 | 214.87339266666666667 | 3223100890.00000000005000000 | 322310089.00000000000500000
(1 row)
Time: 1.147665163s
root@:26257/tpch> show create table tpchsf10.customer;
table_name | create_statement
---------------------------+----------------------------------------------------------------------------------------------------------------
tpchsf10.public.customer | CREATE TABLE customer (
| c_custkey INT8 NOT NULL,
| c_name VARCHAR(25) NOT NULL,
| c_address VARCHAR(40) NOT NULL,
| c_nationkey INT8 NOT NULL,
| c_phone CHAR(15) NOT NULL,
| c_acctbal FLOAT8 NOT NULL,
| c_mktsegment CHAR(10) NOT NULL,
| c_comment VARCHAR(117) NOT NULL,
| CONSTRAINT "primary" PRIMARY KEY (c_custkey ASC),
| CONSTRAINT customer_fkey_nation FOREIGN KEY (c_nationkey) REFERENCES nation(n_nationkey),
| INDEX c_nk (c_nationkey ASC),
| FAMILY "primary" (c_custkey, c_name, c_address, c_nationkey, c_phone, c_acctbal, c_mktsegment, c_comment)
| )
(1 row)
Time: 529.061245ms
root@:26257/tpch> WITH table_rows AS (
SELECT count(1) AS cnt FROM tpch.customer
),
table_size AS (
SELECT sum(
(
crdb_internal.range_stats(
start_key
)->>'key_bytes'
)::INT8
+ (
crdb_internal.range_stats(
start_key
)->>'val_bytes'
)::INT8
) AS size
FROM crdb_internal.ranges_no_leases
WHERE table_name = 'customer'
AND database_name = 'tpch'
GROUP BY database_name, table_name
)
SELECT cnt,
size,
size / cnt AS size_per_row,
(size / cnt * 15000000) estamted_size_15000000,
size / cnt * cnt AS validate
FROM table_rows, table_size;
cnt | size | size_per_row | estamted_size_15000000 | validate
-----------+------------+--------------+------------------------+---------------------
15000000 | 3224900646 | 214.9933764 | 3224900646.0000000 | 3224900646.0000000
(1 row)
Time: 7.991273263s
11:45

Andrew Deally 1:53 PM
root@:26257/defaultdb> WITH
table_rows
AS (SELECT count(1) AS cnt FROM tpchsf10.orders),
table_size
AS (
SELECT
sum(
(
crdb_internal.range_stats(
start_key
)->>'key_bytes'
)::INT8
+ (
crdb_internal.range_stats(
start_key
)->>'val_bytes'
)::INT8
)
AS size
FROM
crdb_internal.ranges_no_leases
WHERE
table_name = 'orders'
AND database_name = 'tpchsf10'
GROUP BY
database_name, table_name
)
SELECT
cnt,
size,
size / cnt AS size_per_row,
(size / cnt * 150000000) AS estamted_size_150000000,
size / cnt * cnt AS validate
FROM
table_rows, table_size;
cnt | size | size_per_row | estamted_size_150000000 | validate
-----------+------------+--------------+-------------------------+--------------------
15000000 | 2858921070 | 190.594738 | 28589210700.000000 | 2858921070.000000
(1 row)
Time: 9.824237264s
root@:26257/defaultdb> WITH
table_rows AS (SELECT count(1) AS cnt FROM tpch.orders),
table_size
AS (
SELECT
sum(
(
crdb_internal.range_stats(
start_key
)->>'key_bytes'
)::INT8
+ (
crdb_internal.range_stats(
start_key
)->>'val_bytes'
)::INT8
)
AS size
FROM
crdb_internal.ranges_no_leases
WHERE
table_name = 'orders'
AND database_name = 'tpch'
GROUP BY
database_name, table_name
)
SELECT
cnt,
size,
size / cnt AS size_per_row,
(size / cnt * 150000000) AS estamted_size_150000000,
size / cnt * cnt AS validate
FROM
table_rows, table_size;
cnt | size | size_per_row | estamted_size_150000000 | validate
------------+-------------+--------------+-------------------------+-----------------------
150000000 | 28803450978 | 192.02300652 | 28803450978.00000000 | 28803450978.00000000
(1 row)
Time: 1m27.403918464s
root@:26257/defaultdb>

Jira Issue: DOC-531

@drewdeally
Copy link
Author

Proper SQL

WITH
	table_rows
		AS (SELECT count(1) AS cnt FROM tpch.lineitem),
	table_size
		AS (
			SELECT
				sum(
					(
						crdb_internal.range_stats(
							start_key
						)->>'key_bytes'
					)::INT8
					+ (
							crdb_internal.range_stats(
								start_key
							)->>'val_bytes'
						)::INT8
				)
					AS size
			FROM
				crdb_internal.ranges_no_leases
			WHERE
				table_name = 'lineitem'
				AND database_name = 'tpch'
			GROUP BY
				database_name, table_name
		)
SELECT
	cnt,
	size,
	size / cnt AS size_per_row,
	(size / cnt * 150000000) AS estamted_size_150000000,
	size / cnt * cnt AS validate
FROM
	table_rows, table_size;

@drewdeally
Copy link
Author

using TPCH SF10 and SF100

[1]+ Running nohup cockroach workload fixtures import tpch --scale-factor=100 &

check for expected rows is only supported with scale factor 1, so it was disabled
I200602 15:33:26.825458 1 ccl/workloadccl/fixture.go:316 starting import of 8 tables
I200602 15:33:29.488277 27 ccl/workloadccl/fixture.go:446 imported 2.5 KiB in nation table (25 rows, 25 index entries, took 2.662225915s, 0.00 MiB/s)
I200602 15:33:31.169084 28 ccl/workloadccl/fixture.go:446 imported 428 B in region table (5 rows, 0 index entries, took 4.343079809s, 0.00 MiB/s)
I200602 15:33:45.702604 30 ccl/workloadccl/fixture.go:446 imported 160 MiB in supplier table (1000000 rows, 1000000 index entries, took 18.876595505s, 8.48 MiB/s)
I200602 15:34:14.820989 29 ccl/workloadccl/fixture.go:446 imported 2.4 GiB in part table (20000000 rows, 0 index entries, took 47.994966279s, 50.88 MiB/s)
I200602 15:34:46.626794 32 ccl/workloadccl/fixture.go:446 imported 2.6 GiB in customer table (15000000 rows, 15000000 index entries, took 1m19.800761565s, 33.88 MiB/s)
I200602 15:49:35.846525 31 ccl/workloadccl/fixture.go:446 imported 13 GiB in partsupp table (80000000 rows, 80000000 index entries, took 16m9.020156044s, 13.69 MiB/s)
I200602 16:48:23.206450 33 ccl/workloadccl/fixture.go:446 imported 21 GiB in orders table (150000000 rows, 300000000 index entries, took 1h14m56.380352952s, 4.87 MiB/s)
I200602 22:54:05.247544 9 util/log/log_flush.go:101 hangup received, flushing logs
I200603 01:15:21.407266 146 ccl/workloadccl/fixture.go:446 imported 161 GiB in lineitem table (600002790 rows, 4800022320 index entries, took 9h41m54.581220781s, 4.72 MiB/s)
I200603 01:15:21.408572 1 ccl/workloadccl/fixture.go:325 imported 200 GiB bytes in 8 tables (took 9h41m54.58188804s, 5.88 MiB/s)

sf100

  SELECT count(1) AS cnt FROM tpch.lineitem
              ),
   table_size AS (
                  SELECT sum(
                            (
                                crdb_internal.range_stats(
                                    start_key
                                )->>'key_bytes'
                            )::INT8
                            + (
                                    crdb_internal.range_stats(
                                        start_key
                                    )->>'val_bytes'
                                )::INT8
                         ) AS size
                    FROM crdb_internal.ranges_no_leases
                   WHERE     table_name = 'lineitem'
                         AND database_name = 'tpch'
                GROUP BY database_name, table_name
              )

SELECT cnt,
size,
size / cnt AS size_per_row,
(size / cnt * 150000000) estamted_size_150000000,
size / cnt * cnt AS validate
FROM table_rows, table_size;
cnt | size | size_per_row | estamted_size_150000000 | validate
------------+--------------+-----------------------+-------------------------------+---------------------------------
600002790 | 243081569331 | 405.13406501159769607 | 60770109751.73965441050000000 | 243081569330.99999999957203530
(1 row)

WITH
table_rows
AS (SELECT count(1) AS cnt FROM tpchsf10.lineitem),
table_size
AS (
SELECT
sum(
(
crdb_internal.range_stats(
start_key
)->>'key_bytes'
)::INT8
+ (
crdb_internal.range_stats(
start_key
)->>'val_bytes'
)::INT8
)
AS size
FROM
crdb_internal.ranges_no_leases
WHERE
table_name = 'lineitem'
AND database_name = 'tpchsf10'
GROUP BY
database_name, table_name
)
SELECT
cnt,
size,
size / cnt AS size_per_row,
(size / cnt * 600002790) AS estamted_size_600002790,
size / cnt * cnt AS validate
FROM
table_rows, table_size;
cnt | size | size_per_row | estamted_size_600002790 | validate
-----------+-------------+-----------------------+--------------------------------+--------------------------------
60001307 | 23998114871 | 399.95986872419295800 | 239977037122.54951529835282000 | 23998114871.00000000019610600
(1 row)

Time: 33.377451296s
difference

root@:26257/defaultdb> select 239977037122/1024/1024/1024
-> ;
?column?

223.49603206105530262
(1 row)

Time: 364.863µs

root@:26257/defaultdb> select 243081569331/1024/1024/1024 ;
?column?

226.38735299091786146
(1 row)

Time: 403.708µs

root@:26257/defaultdb> select 3104532209/1024/1024/1024;
?column?

2.8913209298625588417
(1 row)

Time: 388.12µs

@drewdeally drewdeally changed the title Estimating Size projections for Table with existing data Sizing Calculator: Estimating Size projections for Table with existing data Jun 3, 2020
@robert-s-lee
Copy link
Contributor

@knz @tbg @jordanlewis we had another idea discussed on cockroachdb/cockroach#20712. using crdb_internal.ranges_no_leases seems to provide close match to the actual.

@tbg
Copy link
Member

tbg commented Jun 3, 2020

crdb_internal.ranges is not something we can use to "officially" expose this mechanism to users. It is not available to tenants, and it is too expensive (cluster-wide RPC). The solution here should rely either on stats or on the ApproximateSize call (which is what the UI already uses).

The exploration above is interesting. Could you give a summary of the final numbers? There's a little too much going on for me to grasp them at a glance.

@tbg
Copy link
Member

tbg commented Jun 3, 2020

I'm a little confused here. ranges_no_leases has no info on the range size. The range_stats function returns the MVCCSize, which has no good correlation with the actual on-disk size (MVCCSize does not take into account replication nor compression nor LSM overhead). I would expect that to significantly over- or undershoot, depending on the situation.

In the UI, we use the ApproximateSize method on the storage engine and sum that up. Was that found to be lacking? It is an approximation as the name suggests, but comes from the LSM (storage engine), which is generally supposed to have the best idea of how much data it's actually using.
Note that in your experiments, where you likely imported data and measured, the numbers may work out very differently vs. having the dataset grown organically, because the resulting LSM structure will be different.
I'm not sure the ApproximateSize RPC is accessible via SQL. That, however, could be changed. For now I'd just be curious if the numbers from the admin ui (which should come from that method) make enough sense.

@robert-s-lee
Copy link
Contributor

robert-s-lee commented Jun 3, 2020 via email

@drewdeally
Copy link
Author

drewdeally commented Jun 3, 2020 via email

@tbg
Copy link
Member

tbg commented Jun 3, 2020

I'll defer on the SQL team on how that would be exposed but it amounts to hitting this endpoint:

https://github.com/cockroachdb/cockroach/blob/56e92143e87e25d8f3fdfb29c28608ad9dc6d7b5/pkg/server/status.go#L1808-L1812

@tbg
Copy link
Member

tbg commented Jun 3, 2020

Actually it is exposed via http: http://127.0.0.1:xxx/_status/span though it is awkward to use due to the need to post KV-encoded spans.

The below is for the entire keyspace. The end_key below is echo -e '\xff\xff' | base64.

$ curl --header "Content-Type: application/json" --request POST --data '{"start_key": "", "end_key": "//8K"}' http://127.0.0.1:49557/_status/span
{
  "rangeCount": 62,
  "approximateDiskBytes": "0",
  "totalStats": {
    "containsEstimates": "17992",
    "lastUpdateNanos": "1591194714713249000",
    "intentAge": "0",
    "gcBytesAge": "6530516",
    "liveBytes": "1129846",
    "liveCount": "4950",
    "keyBytes": "284176",
    "keyCount": "5001",
    "valBytes": "895191",
    "valCount": "5266",
    "intentBytes": "0",
    "intentCount": "0",
    "sysBytes": "18974",
    "sysCount": "251"
  }
}

Note that we get 0 back since I'm using an in-mem engine:

  "approximateDiskBytes": "0",

@drewdeally
Copy link
Author

Thanks @tbg , I can look into this. I also noticed cockroachdb/cockroach#20712 (comment) so it seems it is in backlog.

@tbg
Copy link
Member

tbg commented Jun 3, 2020

I don't think anyone is really looking at this right now, it's worth reaching out to SQL PM if this is a big win. It should be relatively easy (mod the usual bike shedding)

@knz
Copy link
Contributor

knz commented Jun 3, 2020

FWIW there's two separate steps that can be looked at:

  • the "current" approach is to add a virtual table that exposes that information
  • a better/next approach will be to make RPCs universally reachable via SQL, e.g. via a RPC-over-SQL interface (which doesn't exist yet)

@robert-s-lee
Copy link
Contributor

robert-s-lee commented Jun 5, 2020 via email

@knz
Copy link
Contributor

knz commented Jun 5, 2020

effort!

it's one of the lower priority items on the 20.2 roadmap. But it's on the radar. We really don't like opening the main RPC port to clients and would rather have all operational traffic go either over HTTP or SQL.

@jseldess
Copy link
Contributor

Sounds like we should wait for engineering to create a more official avenue for using current capacity and row count to estimate growth. Leaving this on backlog until that happens. @taroface, @piyush-singh, I think this falls under deployment and ops.

@taroface
Copy link
Contributor

Relates to #7818.

@johnrk-zz
Copy link

This looks like a SQL issue cc @awoods187

@exalate-issue-sync
Copy link

exalate-issue-sync bot commented Nov 18, 2021

Jesse Seldess (jseldess) commented:
Piyush Singh, here’s another old docs issue with potentially useful sizing guidance. Should we keep it around, refresh it, or close?

@exalate-issue-sync exalate-issue-sync bot changed the title Sizing Calculator: Estimating Size projections for Table with existing data Sizing Calculator: Estimating Size projections for Table with existing data Feb 1, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
C-doc-improvement O-sales-eng Internal source: Sales Engineering
Projects
None yet
Development

No branches or pull requests

9 participants