Обсуждение: Delete large amount of records and INSERT (with indexes) goes VERY slow
Hi Guys I came accross a strange (at least to me) problem last night. (Thankfully a good nights sleep gave me the idea to fix it) I have a table with the following structure: CREATE TABLE StopTelephony ( RadAcctId BIGSERIAL PRIMARY KEY, UserName VARCHAR(32) DEFAULT '' NOT NULL, NASIPAddress INET NOT NULL, AcctSessionTime BIGINT, AcctInputOctets BIGINT, AcctOutputOctets BIGINT, CalledStationId VARCHAR(50) DEFAULT '' NOT NULL, CallingStationId VARCHAR(50) DEFAULT '' NOT NULL, AcctDelayTime SMALLINT, CiscoNASPort varchar(16) DEFAULT '' NOT NULL, h323CallOrigin varchar(10) DEFAULT '' NOT NULL, h323SetupTime timestamp with time zone NOT NULL, h323ConnectTime timestamp with time zone NOT NULL, h323DisconnectTime timestamp with time zone NOT NULL, h323DisconnectCause varchar(2) DEFAULT '' NOT NULL, H323RemoteAddress BOOLEAN DEFAULT false, H323VoiceQuality NUMERIC(2), h323ConfID VARCHAR(35) DEFAULT '' NOT NULL ); create UNIQUE index stoptelephonycombo on stoptelephony (h323SetupTime, nasipaddress, h323ConfID); This is part of the VoIP billing code (Which I maintain) at http://www.freeradius.org Now, I had a approx 5million records in this table and I usually get a combination of 250-300 SELECTS + 250-300 INSERTS per second on this table at this size (or around 500 SELECTS per second) Now I decided to remove all the data from the table and reimport due to a minor parsing error in my import script (No changes to the table schema were made) so I did DELETE FROM StopTelephony; then VACUUM ANALYZE; I then reran my import script and found that I was getting approximately 1 INSERT every 30 secconds!!! although SELECTS were working relatively quickly. I then tried a VACUUM FULL; a restart of postgres, a server reboot etc etc all to no avail. INSERTS stayed stubbonly at 1 every 30+ secconds. It wasn't until this morning that I decided to drop and recreate the index at which point everything went back to normal. Is this a known _feature_ of postgres indexes that they cant recover from large amounts of records being deleted?? I am running Postgres 7.3.2 as downloaded from ftp://ftp.suse.com/pub/people/max/8.1 on SuSE Linux 8.1 on a P4 1.8 with 1Gb of ram... Regards -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
On Wed, Apr 09, 2003 at 10:55:23AM +0300, Peter Nixon wrote: > Is this a known _feature_ of postgres indexes that they cant recover from > large amounts of records being deleted?? This is fixed in 7.4-to-be. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) One man's impedance mismatch is another man's layer of abstraction. (Lincoln Yeoh)
Peter Nixon <listuser@peternixon.net> writes:
> Now, I had a approx 5million records in this table and I usually get a
> combination of 250-300 SELECTS + 250-300 INSERTS per second on this table
> at this size (or around 500 SELECTS per second)
> Now I decided to remove all the data from the table and reimport due to a
> minor parsing error in my import script (No changes to the table schema
> were made) so I did DELETE FROM StopTelephony; then VACUUM ANALYZE;
> I then reran my import script and found that I was getting approximately 1
> INSERT every 30 secconds!!! although SELECTS were working relatively
> quickly.
> I then tried a VACUUM FULL; a restart of postgres, a server reboot etc etc
> all to no avail. INSERTS stayed stubbonly at 1 every 30+ secconds.
> It wasn't until this morning that I decided to drop and recreate the index
> at which point everything went back to normal.
I think your mistake was to do VACUUM ANALYZE while the table was empty.
That led the planner to generate plans suitable for a very small table
--- seqscans instead of indexscans, for example. A byproduct of
rebuilding the index was to update the planner's idea of how big the
table is, at which point the plans went back to normal. It's hard to
prove anything now, but it would have been a good idea to take note of
EXPLAIN ANALYZE output for the slow queries.
BTW: next time you want to do something like this, consider using
TRUNCATE TABLE instead of DELETE/VACUUM.
regards, tom lane
On Wed, Apr 09, 2003 at 12:59:01PM -0400, Tom Lane wrote: > Peter Nixon <listuser@peternixon.net> writes: > > > I then reran my import script and found that I was getting approximately 1 > > INSERT every 30 secconds!!! although SELECTS were working relatively > > quickly. > > I think your mistake was to do VACUUM ANALYZE while the table was empty. > That led the planner to generate plans suitable for a very small table > --- seqscans instead of indexscans, for example. But he _is_ getting good performance for SELECT, so the plans are not that bad. Only insertion is slow. Can this be related to the btree index bloating issue you fixed on january? > A byproduct of rebuilding the index was to update the planner's idea > of how big the table is, at which point the plans went back to normal. But the index was recreated from scratch and thus free of any bloating. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Hay que recordar que la existencia en el cosmos, y particularmente la elaboración de civilizaciones dentre de él no son, por desgracia, nada idílicas" (Ijon Tichy)
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> But he _is_ getting good performance for SELECT, so the plans are not
> that bad. Only insertion is slow. Can this be related to the btree
> index bloating issue you fixed on january?
I don't think so. He's reimporting the same data he had, so the range
of index entries should have been the same; no reason to expect index
bloat.
Info about the physical size of the index before and after rebuilding
(eg, VACUUM VERBOSE output) would have proved it one way or the other,
but we haven't got that.
regards, tom lane
Peter Nixon <listuser@peternixon.net> writes:
> Yes. But that still doesn't explain the speed. I am not joking when I said I
> was getting only ONE INSERT every 30 seconds - 1 minute!!!
The insertion itself couldn't possibly take that long; the problem had
to be in auxiliary operations invoked by the insert. Do you have any
triggers on that table? Any foreign references to or from it? What
indexes are on the table?
regards, tom lane
On Thu April 10 2003 17:00, you wrote: > Peter Nixon <listuser@peternixon.net> writes: > > Yes. But that still doesn't explain the speed. I am not joking when I > > said I was getting only ONE INSERT every 30 seconds - 1 minute!!! > > The insertion itself couldn't possibly take that long; the problem had > to be in auxiliary operations invoked by the insert. Do you have any > triggers on that table? Any foreign references to or from it? What > indexes are on the table? There is only one index. The structure is the following.. CREATE TABLE StopVoIP ( RadAcctId BIGSERIAL PRIMARY KEY, UserName VARCHAR(32) DEFAULT '' NOT NULL, NASIPAddress INET NOT NULL, AcctSessionTime BIGINT, AcctInputOctets BIGINT, AcctOutputOctets BIGINT, CalledStationId VARCHAR(50) DEFAULT '' NOT NULL, CallingStationId VARCHAR(50) DEFAULT '' NOT NULL, AcctDelayTime SMALLINT, CiscoNASPort BOOLEAN DEFAULT false, h323CallOrigin varchar(10) DEFAULT '' NOT NULL, h323SetupTime timestamp with time zone NOT NULL, h323ConnectTime timestamp with time zone NOT NULL, h323DisconnectTime timestamp with time zone NOT NULL, h323DisconnectCause varchar(2) DEFAULT '' NOT NULL, H323RemoteAddress INET NOT NULL, H323VoiceQuality NUMERIC(2), h323ConfID VARCHAR(35) DEFAULT '' NOT NULL ); create UNIQUE index stopvoipcombo on stopvoip (h323SetupTime, nasipaddress, h323ConfID); -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
On Wed April 9 2003 19:59, Tom Lane wrote: > Peter Nixon <listuser@peternixon.net> writes: > > Now, I had a approx 5million records in this table and I usually get a > > combination of 250-300 SELECTS + 250-300 INSERTS per second on this table > > at this size (or around 500 SELECTS per second) > > > > Now I decided to remove all the data from the table and reimport due to a > > minor parsing error in my import script (No changes to the table schema > > were made) so I did DELETE FROM StopTelephony; then VACUUM ANALYZE; > > > > I then reran my import script and found that I was getting approximately > > 1 INSERT every 30 secconds!!! although SELECTS were working relatively > > quickly. > > > > I then tried a VACUUM FULL; a restart of postgres, a server reboot etc > > etc all to no avail. INSERTS stayed stubbonly at 1 every 30+ secconds. > > > > It wasn't until this morning that I decided to drop and recreate the > > index at which point everything went back to normal. > > I think your mistake was to do VACUUM ANALYZE while the table was empty. > That led the planner to generate plans suitable for a very small table > --- seqscans instead of indexscans, for example. A byproduct of Yes. But that still doesn't explain the speed. I am not joking when I said I was getting only ONE INSERT every 30 seconds - 1 minute!!! I tried runnng VACUUM ANALYZE many times. about 10 -15 as I thought somethign like this might have happened, but even a terrible QUERY plan doesnt explain 20+ SELECTS per seccond but only one INSERT every 30sec on a table with only a few hundred records! Note: When the database is working as expected I get the following speed (across a network) "9065 records from detail-20020919.bz2 were processed in 24 seconds (377.7 records/sec)" processed means SELECT to see if the record is in the database, then INSERT if it is not (It should not be).. > rebuilding the index was to update the planner's idea of how big the > table is, at which point the plans went back to normal. It's hard to > prove anything now, but it would have been a good idea to take note of > EXPLAIN ANALYZE output for the slow queries. > > BTW: next time you want to do something like this, consider using > TRUNCATE TABLE instead of DELETE/VACUUM. OK Thanks Thanks for the great work guys. Postgres is a brilliant database! -- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc
Alvaro Herrera wrote: > On Wed, Apr 09, 2003 at 12:59:01PM -0400, Tom Lane wrote: > > Peter Nixon <listuser@peternixon.net> writes: > > > > > I then reran my import script and found that I was getting approximately 1 > > > INSERT every 30 secconds!!! although SELECTS were working relatively > > > quickly. > > > > I think your mistake was to do VACUUM ANALYZE while the table was empty. > > That led the planner to generate plans suitable for a very small table > > --- seqscans instead of indexscans, for example. > > But he _is_ getting good performance for SELECT, so the plans are not > that bad. Only insertion is slow. Can this be related to the btree > index bloating issue you fixed on january? It is my understanding that we only allowed indexes to reuse space after VACUUM, not shrink the index size during VACUUM FULL, so the index could still be huge. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073