From: | "Tokuda, Takashi" <tokuda(dot)takashi(at)jp(dot)fujitsu(dot)com> |
---|---|
To: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Improve the performance of the standby server when dropping tables on the primary server |
Date: | 2017-08-01 04:45:41 |
Message-ID: | A1CF58A8CBA14341B3F3AC6A468F18454545E4F3@g01jpexmbyt23 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
The attached patch changes data structure storing unowned SMgrRelation objects
from list structure to hash structure.
The reason why I change it is that list structure very slowly removes a node.
And list structure takes longer time to remove a node than hash structure.
The problem was reported in BUG #14575.
https://www.postgresql.org/message-id/20170303023246.25054.66379@wrigleys.postgresql.org
In my customer's case, the standby server was delayed more than 20 minites
when dropping many table at once.
- Performance check
I confirmed the performance of dropping tables by the following method.
1. Set up a synchronous streaming replication environment.
And set synchronous_commit = remote_apply in postgresql.conf.
2. Create 100,000 tables (tbl1, tbl2, ... , tbl100000).
And insert one row in each table.
3. Measure the time to drop 50 tables by psql
$ time psql -d ${DATABSE} -p ${PORT} -f drop.sql
drop.sql
--
begin;
drop table tbl1;
drop table tbl2;
...
drop table tbl50;
commit;
--
Result:
without this patch
real 0m3.734s
user 0m0.003s
sys 0m0.005s
with this patch
real 0m1.292s
user 0m0.005s
sys 0m0.003s
Even in this case, we have improved considerably,
so I suggest you might approve it.
Regards,
Takashi Tokuda
Attachment | Content-Type | Size |
---|---|---|
change_data_structure_storing_unowned_SMgrRelation.patch | application/octet-stream | 6.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Noah Misch | 2017-08-01 04:46:50 | Re: segfault in HEAD when too many nested functions call |
Previous Message | Amit Langote | 2017-08-01 04:45:20 | Re: [BUGS] BUG #14759: insert into foreign data partitions fail |