From: | Jerry Sievers <gsievers19(at)comcast(dot)net> |
---|---|
To: | marcin kowalski <yoshi314(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: vacuum of empty table slows down as database table count grows |
Date: | 2017-01-04 19:22:19 |
Message-ID: | 86zij6mxzo.fsf@jerry.enova.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
marcin kowalski <yoshi314(at)gmail(dot)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?
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2017-01-04 19:32:11 | Re: vacuum of empty table slows down as database table count grows |
Previous Message | Adrian Klaver | 2017-01-04 17:41:06 | Re: Re: could not load library "$libdir/sslutils": in pg_upgrade process |