From: Tom Lane <tgl@sss.pgh.pa.us>
To: Craig James <cjames@emolecules.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Extreme slowdown querying system tables
Date: Fri, 02 Feb 2018 18:52:36 -0500
Message-ID: <2696.1517615556@sss.pgh.pa.us> (raw)
In-Reply-To: <CAFwQ8reGR-px-ujcye_eOfqcvC=T6R=TVTkn01d7-qzgqCjf_A@mail.gmail.com>
References: <CAFwQ8reGR-px-ujcye_eOfqcvC=T6R=TVTkn01d7-qzgqCjf_A@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, cjames@emolecules.com
Subject: Re: Extreme slowdown querying system tables
In-Reply-To: <2696.1517615556@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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