RE: Recovery performance of DROP DATABASE with many tablespaces

From: "Jamison, Kirk" <k(dot)jamison(at)jp(dot)fujitsu(dot)com>
To: 'Fujii Masao' <masao(dot)fujii(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: RE: Recovery performance of DROP DATABASE with many tablespaces
Date: 2018-07-04 07:47:24
Message-ID: D09B13F772D2274BB348A310EE3027C62F699E@g01jpexmbkw24
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi, Fujii-san

I came across this post and I got interested in it,
so I tried to apply/test the patch but I am not sure if I did it correctly.
I set-up master-slave sync, 200GB shared_buffers, 20000 max_locks_per_transaction,
1 DB with 500 table partitions shared evenly across 5 tablespaces.

After dropping the db, with or without patch,
there were no difference in recovery performance when dropping database,
so maybe I made a mistake somewhere. But anyway, here's the results.

======WITHOUT PATCH=======
[200GB shared buffers]
DROPDB only (skipped DROP TABLE and DROP TABLESPACE)
2018/07/04_13:35:00.161
dropdb
2018/07/04_13:35:05.591 5.591 sec

[200GB shared_buffers]
DROPDB (including DROP TABLE and DROP TABLESPACE)
real 3m19.717s
user 0m0.001s
sys 0m0.001s

======WITH PATCH=======
[200GB shared_buffers]
DROPDB only (skipped DROP TABLE and DROP TABLESPACE)
2018/07/04_14:19:47.128
dropdb
2018/07/04_14:19:53.177 6.049 sec

[200GB shared_buffers]
DROPDB (included the DROP TABLE and DROP TABLESPACE commands)
real 3m51.834s
user 0m0.001s
sys 0m0.002s

Just in case, do you also have some performance test numbers/case
to show the recovery perf improvement when dropping database that contain multiple tablespaces?

Regards,
Kirk Jamison

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro HORIGUCHI 2018-07-04 08:28:38 Re: [HACKERS] Restricting maximum keep segments by repslots
Previous Message Peter Eisentraut 2018-07-04 07:46:58 Re: Test-cases for deferred constraints in plpgsql_transaction.sql