2017-01-04 20:22 GMT+01:00 Jerry Sievers <gsiever...@comcast.net>: > marcin kowalski <yoshi...@gmail.com> writes: > > > I am experiencing an odd issue, i've noticed it on 9.3 , but i can > reproduce it on 9.6. > > > > Basically, i have a database with a lot of schemas, but not that much > data. Each schema is maybe 2-4 GB in size, and often much less than that. > > > > The database has ~300-500 schemas, each with ~100-300 tables. Generally > a few hundred thousand tables total. Entire cluster has 2 or 3 such > databases. > > > > As the amount of tables grows, the time it takes to vacuum an _empty_ > table grows as well. The table is in public schema, and it is the only > table there. > > I presume since vacuum then has much larger catalogs to query as if to > find indexes and related toast tables to process along with your table > of interest. > > > I made a simple testing script to make sure that these things are > related. I set up a blank database, create a table with one column in > public and restore one schema. > > Then i vacuum that table three times, measure the execution times and > repeat the process, adding another schema to db. > > > > At ~200 tables it takes ~100ms for psql to issue a vacuum verbose and > exit. At 83K tables the time is already at ~1.5second. The progress appars > to be directly > > proportional to table amount, and grows linearly, eventually crossing > past 3seconds - for blank table with no data. > > > > I think this may severely impact the entire vacuumdb run, but i have not > verified that yet. > > > > This is irrelevant of amount of data restored, i am seeing the same > behavior with just schema restore, as well as with schema+data restores. > > > > If anyone is interested i may upload the schema data + my benchmarking > script with collected whisper data from my test run (i've been plotting it > in grafana via carbon) > > > > Is this a known issue? Can i do anything to improve performance here? >
we had 10K and more tables in one database - and we had lot of issues. I know so Tomas fixed some issues, but we need the stat files in tmpfs please, read this article https://blog.pgaddict.com/posts/the-two-kinds-of-stats-in-postgresql Regards Pavel > > > -- > Jerry Sievers > Postgres DBA/Development Consulting > e: postgres.consult...@comcast.net > p: 312.241.7800 > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
