Thread: 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.