sql: FK validations are slow

[vivekmenezes]

As part of #15026 a complex schema with FKs

CREATE TABLE raw_visits (
	id INT NOT NULL DEFAULT unique_rowid(),
	venue_id INT NOT NULL,
	device_id STRING NOT NULL,
	first_signal TIMESTAMP WITH TIME ZONE NULL,
	last_signal TIMESTAMP WITH TIME ZONE NULL,
	duration_signal INTERVAL NULL,
	signals_per_hour INT NULL,
	avg_distance INT NULL,
	visitortype INT NULL,
	durationtype INT NULL,
	dayssincelastvisit INT NULL,
	visitweight INT NULL,
	min_distance INT NULL DEFAULT 0,
	CONSTRAINT "primary" PRIMARY KEY (id ASC),
	CONSTRAINT fk_venue_id_ref_venues FOREIGN KEY (venue_id, device_id, first_signal, duration_signal) REFERENCES venues (id),
	CONSTRAINT fk_device_id_ref_devices FOREIGN KEY (device_id) REFERENCES devices (id),
	FAMILY "primary" (id, venue_id, device_id, first_signal, last_signal, duration_signal, signals_per_hour, avg_distance, visitortype, durationtype, dayssincelastvisit, visitweight, min_distance)
)

reveal high latency on SQL queries.

[vivekmenezes]

@mattiasmak lets discuss the latency issues you are observing here. Thanks!

[vivekmenezes]

We'd appreciate it if you attach the kinds of SQL requests you are running against this cluster, and also include the schema for the venue and device tables. Thanks

[mattiasmak]

CREATE TABLE devices (
	id STRING NOT NULL,
	prefix STRING NULL,
	name STRING NULL,
	CONSTRAINT "primary" PRIMARY KEY (id ASC),
	INDEX prefix_index (prefix ASC),
	FAMILY "primary" (id, prefix, name)
)

CREATE TABLE venues (
	id INT NOT NULL DEFAULT unique_rowid(),
	client_id INT NOT NULL,
	name STRING NULL,
	CONSTRAINT "primary" PRIMARY KEY (id ASC),
	CONSTRAINT fk_client_id_ref_clients FOREIGN KEY (client_id) REFERENCES clients (id),
	FAMILY "primary" (id, client_id, name)
)

These two queries are executed:

UPSERT INTO devices (id, prefix) VALUES ('aabbcc', 'aa');

INSERT INTO raw_visits (venue_id, device_id, first_signal, last_signal, duration_signal, signals_per_hour, avg_distance, min_distance) VALUES (1, 'aabbcc', ...etc );

[jordanlewis]

I have a few ideas here, ordered roughly from simpler to more challenging.

• Combine all of the FK scans for a single key into a single batch to give to dist sender. A similar strategy is used in the row writer itself.
• Cache foreign key lookups across the lifetime of a foreign key helper. This would have a major performance improvement for the common case of a large batch of inserts for a single entity - for example, inserting the 100,000 stock rows per warehouse in TPCC must ensure that the very same warehouse row is present for all 100,000 stock rows.
• Combine FK scans for several keys into a single batch, deferring the insertion of that batch's worth of keys until success is returned.
• Cache foreign key lookups across the lifetime of a transaction somehow, for the other common case where a batch of inserts is performed within a transaction but not in a single SQL batch. There are obvious complications here - what if one of the other statements in the transaction modifies the foreign table?

[petermattis]

@jordanlewis Can you explain how FK lookups are currently done? Is no batching being done?

[jordanlewis]

There is no batching done at all for FK lookups. On insert, a single scan is sent sequentially for each of the n foreign keys present in the table.

Proposal 1 above involves batching the n scans per insert row.
Proposal 3 involves batching the n * k scans for all k insert rows in a SQL-level insert batch.

[jordanlewis]

Well, to be more precise, proposal 3 has at most n * k scans in the case that all of the input inserts have distinct foreign key columns.

[petermattis]

Yowzer, that's not good.

[dt]

I expect helper-lifetime caching to be a big win too and am excited to see where that goes if you try it. Note that even in the statement-lifetime cache, you might want to disable caching for self-referential FKs: unlike cross-table FKs, a self-FK could easily be writing to the checked rows in the same statement, so just like the txn-lifetime cache, you'd have add some substantial invalidation complexity.

[jordanlewis]

#18730 helped with this, but there's still more work to be done. @BramGruneir would you be interested in looking at this after you're done with the FK actions work?

[knz]

FWIW @nvanbenschoten had proposed a KV-level key existence cache which would help here too.

[sploiselle]

@BramGruneir @jordanlewis Disregard request for description; didn't see this was written up in 1.0.

[nvanbenschoten]

@RaduBerinde think we can close this now? Is it providing any value?

[jordanlewis]

FKs have been rewritten recently. Feel free to open a new issue if you still are having problems, thanks!