Thread: Extreme slowdown querying system tables
A Chinese attacker out to steal massive amounts of our curated data attacked our website/database in a way that created a new user account on every access, which includes a couple tables per user. Bottom line: about 3.5 million tables were created. After we tightened our new-user signup procedure, all of the bogus accounts were deleted. Now the entire system (about 400 schemas) contains about 300,000 tables total, which is roughly where it's been for several years.
Obviously not acceptable. Among other things, it means that psql's auto-complete feature is completely broken (it freezes completely; I have to use pg_terminate_backend() to kill the pg_catalog query).
However, the system tables haven't recovered; see the highlighted time.
db=> \timingTiming is on.db=> select count(1) from pg_catalog.pg_attribute;count---------5199278(1 row)Time: 535712.996 ms
Obviously not acceptable. Among other things, it means that psql's auto-complete feature is completely broken (it freezes completely; I have to use pg_terminate_backend() to kill the pg_catalog query).
So I did a vacuum-analyze, which helped but is still not acceptable:
What now?
db=# vacuum analyze verbose pg_catalog.pg_attribute;INFO: vacuuming "pg_catalog.pg_attribute"INFO: scanned index "pg_attribute_relid_attnam_index" to remove 4176243 row versionsDETAIL: CPU 2.53s/2.46u sec elapsed 340.06 secINFO: scanned index "pg_attribute_relid_attnum_index" to remove 4176243 row versionsDETAIL: CPU 2.61s/2.04u sec elapsed 253.21 secINFO: "pg_attribute": removed 4176243 row versions in 116877 pagesDETAIL: CPU 2.99s/1.66u sec elapsed 354.83 secINFO: index "pg_attribute_relid_attnam_index" now contains 5210524 row versions in 270863 pagesDETAIL: 4176241 index row versions were removed.231334 index pages have been deleted, 203555 are currently reusable.CPU 0.00s/0.00u sec elapsed 0.30 sec.INFO: index "pg_attribute_relid_attnum_index" now contains 5211181 row versions in 190688 pagesDETAIL: 4176242 index row versions were removed.160280 index pages have been deleted, 139653 are currently reusable.CPU 0.00s/0.00u sec elapsed 0.17 sec.INFO: "pg_attribute": found 1091622 removable, 2922623 nonremovable row versions in 245372 out of 1415386 pagesDETAIL: 1395 dead row versions cannot be removed yet.There were 6347913 unused item pointers.Skipped 0 pages due to buffer pins.0 pages are entirely empty.CPU 12.21s/8.30u sec elapsed 1354.75 sec.INFO: analyzing "pg_catalog.pg_attribute"INFO: "pg_attribute": scanned 30000 of 1415386 pages, containing 106836 live rows and 49 dead rows; 30000 rows in sample, 21477499 estimated total rowsVACUUMdb=# \timingTiming is on.db=# select count(1) from pg_catalog.pg_attribute;count---------5219505(1 row)Time: 101105.252 ms
I'm reluctant to do a VACUUM FULL and REINDEX, for fear that an exclusive lock will halt the system (or that you shouldn't do this to system tables).
Thanks,
Craig
Craig James <cjames@emolecules.com> writes: > A Chinese attacker out to steal massive amounts of our curated data > attacked our website/database in a way that created a new user account on > every access, which includes a couple tables per user. Bottom line: about > 3.5 million tables were created. Ugh. > After we tightened our new-user signup > procedure, all of the bogus accounts were deleted. Now the entire system > (about 400 schemas) contains about 300,000 tables total, which is roughly > where it's been for several years. > However, the system tables haven't recovered; see the highlighted time. Yeah, you'll have a whole lot of bloat in the catalogs. You'll probably have to do a VACUUM FULL of at least pg_class and pg_attribute to get back to normal. Unfortunately, there's no way to do that without an exclusive lock, which will be tantamount to an outage. Hope you can schedule some downtime. > I'm reluctant to do a VACUUM FULL and REINDEX, for fear that an exclusive > lock will halt the system (or that you shouldn't do this to system tables). It's safe, and you would not need to REINDEX because the VACUUM would rebuild the table's indexes anyway. But no way around the lock :-( regards, tom lane
On Fri, Feb 2, 2018 at 3:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Craig James <cjames@emolecules.com> writes:
> A Chinese attacker out to steal massive amounts of our curated data
> attacked our website/database in a way that created a new user account on
> every access, which includes a couple tables per user. Bottom line: about
> 3.5 million tables were created.
Ugh.
> After we tightened our new-user signup
> procedure, all of the bogus accounts were deleted. Now the entire system
> (about 400 schemas) contains about 300,000 tables total, which is roughly
> where it's been for several years.
> However, the system tables haven't recovered; see the highlighted time.
Yeah, you'll have a whole lot of bloat in the catalogs. You'll probably
have to do a VACUUM FULL of at least pg_class and pg_attribute to get
back to normal. Unfortunately, there's no way to do that without an
exclusive lock, which will be tantamount to an outage. Hope you can
schedule some downtime.
> I'm reluctant to do a VACUUM FULL and REINDEX, for fear that an exclusive
> lock will halt the system (or that you shouldn't do this to system tables).
It's safe, and you would not need to REINDEX because the VACUUM would
rebuild the table's indexes anyway. But no way around the lock :-(
Thanks. That's exactly what I needed to know. We'll schedule some down time.
Craig
regards, tom lane
---------------------------------
Craig A. James
Chief Technology OfficerCraig A. James