Re: tableam vs. TOAST - Mailing list pgsql-hackers

From Prabhat Sahu
Subject Re: tableam vs. TOAST
Date
Msg-id CANEvxPopRBwM6RoEdkc4njuFU8PYJ0gKEHtgX1x4_wSu26Nk7g@mail.gmail.com
Whole thread Raw
In response to Re: tableam vs. TOAST  (Robert Haas <robertmhaas@gmail.com>)
Responses Re: tableam vs. TOAST
List 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 Tuples20000 Tuples40000 Tuples80000 Tuples
Without PatchWith PatchWithout PatchWith PatchWithout PatchWith PatchWithout PatchWith Patch
1. SCC INSERT125921.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 UPDATE263017.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 INSERT35415.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 UPDATE72043.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 INSERT26377.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 UPDATE78385.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 INSERT26214.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 UPDATE78423.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 INSERT38451.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 UPDATE82069.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 INSERT36325.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 UPDATE73740.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

Attachment

pgsql-hackers by date:

Previous
From: Fabien COELHO
Date:
Subject: Re: pgbench prints suspect tps numbers
Next
From: Heikki Linnakangas
Date:
Subject: Re: GiST VACUUM