Re: response time is very long in PG9.5.5 using psql or jdbc

On Tue, 13 Feb 2018 13:58:00 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> =?gb2312?B?yq/Twrui?= <SHIYONGHU651(at)pingan(dot)com(dot)cn> writes:
> > Yes,we have more than 500 thousand of objects,and the total size of the database is almost 10TB.Just as you said,we may need to reduce the objects number,or you have any better solution?
>
> Hmph. I tried creating 500000 tables in a test database, and couldn't
> detect any obvious performance problem in session startup. So there's
> something very odd about your results. You might try looking at the
> sizes of the system catalogs, e.g like
> select pg_size_pretty(pg_total_relation_size('pg_attribute'));
> (In my test database, pg_class is about 80MB and pg_attribute about
> 800MB.)

I see this problem fairly frequently. Typically the problem catalog is
pg_attribute as it has the most rows. However the problem really arises when
the catalogs become bloated. Once the total size of the catalogs that get
read at start up approaches the size of shared buffers, and especially if
several processes start at the same time, it becomes quite noticeable.

Catalog bloat happens with large numbers of objects because autovacuum is
ineffective when:

- Catalog churn leading to dead rows
- More than about 50,000 tables in one database, causes large stats file
- Stats_temp_directory is not set to point to tmpfs so autovacuum stats
requests flap the disks. Locally we also patch autovacuum to avoid this.
- inflated reltuples prevents vacuum. See patches for
bugs 14863 and 15005.

Example from one of a clients production instances:

# analyze verbose pg_attribute;
INFO: analyzing "pg_catalog.pg_attribute"
INFO: "pg_attribute": scanned 30000 of 24519424 pages, containing 6475 live
rows and 83 dead rows; 6475 rows in sample, 800983035 estimated total
rows.

The reltuples estimate gets scaled by the bloat and once it gets large enough
autovacuum won't look at it anymore as updated rows < 0.2 * reltuples.

I have a fix into the next commitfest for the reltuples estimate which once
it is reviewed I'd like to recommend for backpatching.

-dg

--
David Gould daveg(at)sonic(dot)net
If simplicity worked, the world would be overrun with insects.