Thread: Delete large amount of records and INSERT (with indexes) goes VERY slow

Delete large amount of records and INSERT (with indexes) goes VERY slow

From

Peter Nixon

Date:

09 April 2003, 15:13:47

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

Re: Delete large amount of records and INSERT (with indexes) goes VERY slow

From

Alvaro Herrera

Date:

09 April 2003, 15:50:36

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)

Re: Delete large amount of records and INSERT (with indexes) goes VERY slow

From

Tom Lane

Date:

09 April 2003, 15:59:08

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

Re: Delete large amount of records and INSERT (with indexes) goes VERY slow

From

Alvaro Herrera

Date:

09 April 2003, 16:09:48

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)

Re: Delete large amount of records and INSERT (with indexes) goes VERY slow

From

Tom Lane

Date:

09 April 2003, 16:18:06

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

Re: Delete large amount of records and INSERT (with indexes) goes VERY slow

From

Tom Lane

Date:

10 April 2003, 13:00:25

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

Re: Delete large amount of records and INSERT (with indexes) goes VERY slow

From

Peter Nixon

Date:

11 April 2003, 16:39:55

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

Re: Delete large amount of records and INSERT (with indexes) goes VERY slow

From

Peter Nixon

Date:

11 April 2003, 17:01:45

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

Re: Delete large amount of records and INSERT (with indexes)

From

Bruce Momjian

Date:

15 April 2003, 13:47:39

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