Re: Extreme slowdown querying system tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Feb 2, 2018 at 3:52 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Craig James <cjames@xxxxxxxxxxxxxx> 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.
---------------------------------

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux