Re: tableam vs. TOAST

Поиск

Список

Период

Сортировка

От	Prabhat Sahu
Тема	Re: tableam vs. TOAST
Дата	25 июня 2019 г. 09:19:09
Msg-id	CANEvxPopRBwM6RoEdkc4njuFU8PYJ0gKEHtgX1x4_wSu26Nk7g@mail.gmail.com обсуждение исходный текст
Ответ на	Re: tableam vs. TOAST (Robert Haas <robertmhaas@gmail.com>)
Ответы	Re: tableam vs. TOAST (Robert Haas <robertmhaas@gmail.com>)
Список	pgsql-hackers

Дерево обсуждения

On Tue, Jun 11, 2019 at 9:47 PM Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, May 21, 2019 at 2:10 PM Robert Haas <robertmhaas@gmail.com> wrote:
> Updated and rebased patches attached.

And again.

Hi Robert,

I have tested the TOAST patches(v3) with different storage options like(MAIN, EXTERNAL, EXTENDED, etc.), and
combinations of compression and out-of-line storage options.
I have used a few dummy tables with various tuple count say 10k, 20k, 40k, etc. with different column lengths.
Used manual CHECKPOINT option with (checkpoint_timeout = 1d, max_wal_size = 10GB) before the test to avoid performance fluctuations,
and calculated the results as a median value of a few consecutive test executions.

Please find the SQL script attached herewith, which I have used to perform the observation.

Below are the test scenarios, how I have checked the behavior and performance of TOAST patches against PG master.
1. where a single column is compressed(SCC)
2. where multiple columns are compressed(MCC)
-- ALTER the table column/s for storage as "MAIN" to make sure that the column values are COMPRESSED.

3. where a single column is pushed to the TOAST table but not compressed(SCTNC)
4. where multiple columns are pushed to the TOAST table but not compressed(MCTNC)
-- ALTER the table column/s for storage as "EXTERNAL" to make sure that the column values are pushed to the TOAST table but not COMPRESSED.

5. where a single column is pushed to the TOAST table and also compressed(SCTC)
6. where multiple columns are pushed to the TOAST table and also compressed(MCTC)
-- ALTER the table column/s for storage as "EXTENDED" to make sure that the column values are pushed to the TOAST table and also COMPRESSED.

7. updating the tuples with similar data shouldn't affect the behavior of storage options.

Please find my observation as below:
System Used: (VCPUs: 8, RAM: 16GB, Size: 640GB)

	10000 Tuples		20000 Tuples		40000 Tuples		80000 Tuples
	Without Patch	With Patch	Without Patch	With Patch	Without Patch	With Patch	Without Patch	With Patch
1. SCC INSERT	125921.737 ms (02:05.922)	125992.563 ms (02:05.993)	234263.295 ms (03:54.263)	235952.336 ms (03:55.952)	497290.442 ms (08:17.290)	502820.139 ms (08:22.820)	948470.603 ms (15:48.471)	941778.952 ms (15:41.779)
1. SCC UPDATE	263017.814 ms (04:23.018)	270893.910 ms (04:30.894)	488393.748 ms (08:08.394)	507937.377 ms (08:27.937)	1078862.613 ms (17:58.863)	1053029.428 ms (17:33.029)	2037119.576 ms (33:57.120)	2023633.862 ms (33:43.634)
2. MCC INSERT	35415.089 ms (00:35.415)	35910.552 ms (00:35.911)	70899.737 ms (01:10.900)	70800.964 ms (01:10.801)	142185.996 ms (02:22.186)	142241.913 ms (02:22.242)
2. MCC UPDATE	72043.757 ms (01:12.044)	73848.732 ms (01:13.849)	137717.696 ms (02:17.718)	137577.606 ms (02:17.578)	276358.752 ms (04:36.359)	276520.727 ms (04:36.521)
3. SCTNC INSERT	26377.274 ms (00:26.377)	25600.189 ms (00:25.600)	45702.630 ms (00:45.703)	45163.510 ms (00:45.164)	99903.299 ms (01:39.903)	100013.004 ms (01:40.013)
3. SCTNC UPDATE	78385.225 ms (01:18.385)	76680.325 ms (01:16.680)	151823.250 ms (02:31.823)	153503.971 ms (02:33.504)	308197.734 ms (05:08.198)	308474.937 ms (05:08.475)
4. MCTNC INSERT	26214.069 ms (00:26.214)	25383.522 ms (00:25.384)	50826.522 ms (00:50.827)	50221.669 ms (00:50.222)	106034.338 ms (01:46.034)	106122.827 ms (01:46.123)
4. MCTNC UPDATE	78423.817 ms (01:18.424)	75154.593 ms (01:15.155)	158885.787 ms (02:38.886)	156530.964 ms (02:36.531)	319721.266 ms (05:19.721)	322385.709 ms (05:22.386)
5. SCTC INSERT	38451.022 ms (00:38.451)	38652.520 ms (00:38.653)	71590.748 ms (01:11.591)	71048.975 ms (01:11.049)	143327.913 ms (02:23.328)	142593.207 ms (02:22.593)
5. SCTC UPDATE	82069.311 ms (01:22.069)	81678.131 ms (01:21.678)	138763.508 ms (02:18.764)	138625.473 ms (02:18.625)	277534.080 ms (04:37.534)	277091.611 ms (04:37.092)
6. MCTC INSERT	36325.730 ms (00:36.326)	35803.368 ms (00:35.803)	73285.204 ms (01:13.285)	72728.371 ms (01:12.728)	142324.859 ms (02:22.325)	144368.335 ms (02:24.368)
6. MCTC UPDATE	73740.729 ms (01:13.741)	73002.511 ms (01:13.003)	141309.859 ms (02:21.310)	139676.173 ms (02:19.676)	278906.647 ms (04:38.907)	279522.408 ms (04:39.522)

All the observation looks good to me,

except for the "Test1" for SCC UPDATE with tuple count(10K/20K), for SCC INSERT with tuple count(40K) there was a slightly increse in time taken

incase of "with patch" result. For a better observation, I also have ran the same "Test 1" for higher tuple count(i.e. 80K), and it also looks fine.

I also have performed the below test with TOAST table objects.
8. pg_dump/restore, pg_upgrade with these
9. Streaming Replication setup
10. Concurrent Transactions

While testing few concurrent transactions I have below query:
-- Concurrent transactions acquire a lock for TOAST option(ALTER TABLE .. SET STORAGE .. MAIN/EXTERNAL/EXTENDED/ etc)

-- Session 1:
CREATE TABLE a (a_id text PRIMARY KEY);
CREATE TABLE b (b_id text);
INSERT INTO a VALUES ('a'), ('b');
INSERT INTO b VALUES ('a'), ('b'), ('b');

BEGIN;
ALTER TABLE b ADD CONSTRAINT bfk FOREIGN KEY (b_id) REFERENCES a (a_id); -- Not Acquiring any lock

-- Session 2:
SELECT * FROM b WHERE b_id = 'a'; -- Shows result

-- Session 1:
ALTER TABLE b ALTER COLUMN b_id SET STORAGE EXTERNAL; -- Acquire a lock

-- Session 2:
SELECT * FROM b WHERE b_id = 'a'; -- Hang/Waiting for lock in session 1

Is this an expected behavior?

With Regards,

Prabhat Kumar Sahu