Re: [PERFORM] Impossibly slow DELETEs
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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Impossibly slow DELETEs
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Impossibly slow DELETEs
Neil Conway <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Impossibly slow DELETEs
Stefan Champailler <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Impossibly slow DELETEs
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
