Improve the performance of the standby server when dropping tables on the primary server

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

Responses

Browse pgsql-hackers by date

  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