Обсуждение: Impossibly slow DELETEs

Dear You all,

(please tell me if this has already been discussed, I was unable to find any
convincing information)

I'm developing a small application, tied to a PG 7.4 beta 5 (i didn't
upgrade). The DB i use is roughly 20 tales each of them containing at most 30
records (I'm still in development). I can provide a whole dump if necessary.
I access the DB throug IODBC (Suse Linux 8.1), through PHP. The machine
everything runs on is 512M of Ram, 2.5GHz speed. So I assume it should be
blazingly fast.

So here's my trouble : some DELETE statement take up to 1 minute to complete
(but not always, sometimes it's fast, sometimes it's that slow). Here's a
typical one : DELETE FROM response_bool WHERE response_id = '125'
The response_bool table has no foreing key and no index on response_id column.
No foreign key reference the response_bool table. There are 6 rows in the
table (given that size, I assumed that an index was not necessary).

So 1 minute to complete look like I did something REALLY bad.

It is my feeling that doing the same query with psql works without problem,
but I can't be sure. The rest of my queries (inserts, updates) just work fine
and pretty fast.

Can someone help me or point me to a place where I can find help ? I didn't do
any in deep debugging though.

thx,

stF

Stefan Champailler wrote:
> Dear You all,
>
> (please tell me if this has already been discussed, I was unable to find any
> convincing information)
>
> I'm developing a small application, tied to a PG 7.4 beta 5 (i didn't
> upgrade). The DB i use is roughly 20 tales each of them containing at most 30
> records (I'm still in development). I can provide a whole dump if necessary.
> I access the DB throug IODBC (Suse Linux 8.1), through PHP. The machine
> everything runs on is 512M of Ram, 2.5GHz speed. So I assume it should be
> blazingly fast.
>
> So here's my trouble : some DELETE statement take up to 1 minute to complete
> (but not always, sometimes it's fast, sometimes it's that slow). Here's a
> typical one : DELETE FROM response_bool WHERE response_id = '125'
> The response_bool table has no foreing key and no index on response_id column.
> No foreign key reference the response_bool table. There are 6 rows in the
> table (given that size, I assumed that an index was not necessary).
>
> So 1 minute to complete look like I did something REALLY bad.
>
> It is my feeling that doing the same query with psql works without problem,
> but I can't be sure.

I think that last sentence is the crux of the problem.  If you can establish
for sure that the unreasonable delay is _only_ there when the command is
issued through IODBC, then it's not a Postgresql problem.

Out of curiosity, why are you using ODBC for PHP anyway?  PHP has Postgresql
libraries that work very well.  I use them quite often without problems.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

Stefan Champailler <schampailler@easynet.be> writes:
> So here's my trouble : some DELETE statement take up to 1 minute to
> complete (but not always, sometimes it's fast, sometimes it's that
> slow). Here's a typical one : DELETE FROM response_bool WHERE
> response_id = '125' The response_bool table has no foreing key and
> no index on response_id column. No foreign key reference the
> response_bool table.

I'm skeptical that PostgreSQL is causing the performance problem
here -- 1 minute for a DELETE on a single-page table is absurdly
slow. If you enable the log_min_duration_statement configuration
variable, you should be able to get an idea of how long it actually
takes PostgreSQL to execute each query -- do you see some 60 second
queries in the log?

What is the system load like when the query takes a long time? For
example, `vmstat 1` output around this point in time would be
helpful.

Does PostgreSQL consume a lot of CPU time or do a lot of disk I/O?

Can you confirm this problem using psql?

> There are 6 rows in the table (given that size, I assumed that an
> index was not necessary).

That's a reasonable assumption.

-Neil

Neil Conway <neilc@samurai.com> writes:
>> There are 6 rows in the table (given that size, I assumed that an
>> index was not necessary).

> That's a reasonable assumption.

But if he's updated those rows a few hundred thousand times and never
VACUUMed, he could be having some problems ...

            regards, tom lane

Is it possible another connection has updated the record and not committed,
and it takes a minute for the connection to time out and commit or roll back?

--
greg

I did not conduct much more test but from what I've seen, it looks like the
ODBC driver is in the doldrums, not PG. For example, when I run my software
on Windows rather than Linux, everything just works as expected. Sorry for
disturbing.

And btw, I use ODBC because my target DB is Oracle and I've been requested to
access it throguh ODBC. So, because I don't have Oracle, I do most of my
development with PG and then I'll port to Oracle. Since I'm doing "simple"
stuff, PG is almost 100% compatible with Oracle. (and before you ask, no,
they don't give me the proper dev environment, bastards :))

Thanks for all the answers.

Stefan


> Stefan Champailler wrote:
> > Dear You all,
> >
> > (please tell me if this has already been discussed, I was unable to find
> > any convincing information)
> >
> > I'm developing a small application, tied to a PG 7.4 beta 5 (i didn't
> > upgrade). The DB i use is roughly 20 tales each of them containing at
> > most 30 records (I'm still in development). I can provide a whole dump if
> > necessary. I access the DB throug IODBC (Suse Linux 8.1), through PHP.
> > The machine everything runs on is 512M of Ram, 2.5GHz speed. So I assume
> > it should be blazingly fast.
> >
> > So here's my trouble : some DELETE statement take up to 1 minute to
> > complete (but not always, sometimes it's fast, sometimes it's that slow).
> > Here's a typical one : DELETE FROM response_bool WHERE response_id =
> > '125' The response_bool table has no foreing key and no index on
> > response_id column. No foreign key reference the response_bool table.
> > There are 6 rows in the table (given that size, I assumed that an index
> > was not necessary).
> >
> > So 1 minute to complete look like I did something REALLY bad.
> >
> > It is my feeling that doing the same query with psql works without
> > problem, but I can't be sure.
>
> I think that last sentence is the crux of the problem.  If you can
> establish for sure that the unreasonable delay is _only_ there when the
> command is issued through IODBC, then it's not a Postgresql problem.
>
> Out of curiosity, why are you using ODBC for PHP anyway?  PHP has
> Postgresql libraries that work very well.  I use them quite often without
> problems.