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.
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)
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
Attachment
pgsql-hackers by date: