Re: About the performance of startup after dropping many tables

On Thu, Feb 17, 2011 at 10:37 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Gan Jiadong <ganjd(at)huawei(dot)com> writes:
>> we have PG 8.3.13 in our system. When running performance cases, we find the
>> startup recovery cost about 3 minutes. It is too long in our system.
>
> Maybe you should rethink the assumption that dropping 40000 tables is a
> cheap operation.  Why do you have that many in the first place, let
> alone that many that you drop and recreate frequently?  Almost
> certainly, you need a better-conceived schema.

Possibly, but it's not necessarily a bad idea to improve performance
for people with crazy schemas.

What concerns me a little bit about the proposed scheme, though, is
that it's only going to work if all over those tables are dropped by a
single transaction. You still need one pass through all of
shared_buffers for every transaction that drops one or more relations.
Now, I'm not sure, maybe there's no help for that, but ever since
commit c2281ac87cf4828b6b828dc8585a10aeb3a176e0 it's been on my mind
that loops that iterate through the entire buffer cache are bad for
scalability.

Conventional wisdom seems to be that performance tops out at, or just
before, 8GB, but it's already the case that that's a quite a small
fraction of the memory on a large machine, and that's only going to
keep getting worse. Admittedly, the existing places where we loop
through the whole buffer cache are probably not the primary reason for
that limitation, but...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company