pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Extreme slowdown querying system tables 3+ messages / 2 participants [nested] [flat]
* Extreme slowdown querying system tables @ 2018-02-02 18:41 Craig James <cjames@emolecules.com> 0 siblings, 1 reply; 3+ messages in thread From: Craig James @ 2018-02-02 18:41 UTC (permalink / raw) To: pgsql-admin 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. However, the system tables haven't recovered; see the highlighted time. db=> \timing Timing 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: 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 versions DETAIL: CPU 2.53s/2.46u sec elapsed 340.06 sec INFO: scanned index "pg_attribute_relid_attnum_index" to remove 4176243 row versions DETAIL: CPU 2.61s/2.04u sec elapsed 253.21 sec INFO: "pg_attribute": removed 4176243 row versions in 116877 pages DETAIL: CPU 2.99s/1.66u sec elapsed 354.83 sec INFO: index "pg_attribute_relid_attnam_index" now contains 5210524 row versions in 270863 pages DETAIL: 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 pages DETAIL: 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 pages DETAIL: 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 rows VACUUM db=# \timing Timing is on. db=# select count(1) from pg_catalog.pg_attribute; count --------- 5219505 (1 row) Time: 101105.252 ms What now? 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Extreme slowdown querying system tables @ 2018-02-02 23:52 Tom Lane <tgl@sss.pgh.pa.us> parent: Craig James <cjames@emolecules.com> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2018-02-02 23:52 UTC (permalink / raw) To: Craig James <cjames@emolecules.com>; +Cc: pgsql-admin 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Extreme slowdown querying system tables @ 2018-02-03 00:23 Craig James <cjames@emolecules.com> parent: Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Craig James @ 2018-02-03 00:23 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin 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 Officer eMolecules, Inc. --------------------------------- ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-02-03 00:23 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2018-02-02 18:41 Extreme slowdown querying system tables Craig James <cjames@emolecules.com> 2018-02-02 23:52 ` Tom Lane <tgl@sss.pgh.pa.us> 2018-02-03 00:23 ` Craig James <cjames@emolecules.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox