Re: [ADMIN] [PERFORM] Query tuning

2006-08-23 Thread Jason Minion
You really need to index the three fields you are restricting with your select query (OBJDOMAINID, OBJID and USERDOMAINID). Depending on whether or not you have other queries that filter for one of the three fields but not the others, you might want to have separate indexes across each of the fie

Re: [ADMIN] [PERFORM] Query tuning

2006-08-23 Thread Subbiah, Stalin
Changing limit or offset to a small number doesn't have any change in plans. Likewise enable_seqscan to false. They still take 8-10 mins to runs. -Original Message- From: Dave Dutcher [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 4:20 PM To: Subbiah, Stalin Cc: pgsql-perform

Re: [ADMIN] pg_hba changes not honored

2006-08-23 Thread Tom Lane
"Sriram Dandapani" <[EMAIL PROTECTED]> writes: > Pg_ctl is pointing to the same directory that postmaster points to on > startup. There is only 1 data directory/postgres installation that I > use. > Pg_ctl informs that postmaster is signaled. When I see the logs for > postmaster, it says "received

Re: [ADMIN] pg_hba changes not honored

2006-08-23 Thread Sriram Dandapani
Pg_ctl is pointing to the same directory that postmaster points to on startup. There is only 1 data directory/postgres installation that I use. Pg_ctl informs that postmaster is signaled. When I see the logs for postmaster, it says "received SIGHUP, reloading configuration files" Linux 2.4.21-40.

Re: [ADMIN] pg_hba changes not honored

2006-08-23 Thread Tom Lane
"Sriram Dandapani" <[EMAIL PROTECTED]> writes: > This problem occurs only when the changes are made while postmaster is > running and pg_ctl is used to reload config files. > When the changes are applied and postmaster is stopped and restarted, it > works fine. Hm. OK, that means we need to look

Re: [ADMIN] pg_hba changes not honored

2006-08-23 Thread Sriram Dandapani
This problem occurs only when the changes are made while postmaster is running and pg_ctl is used to reload config files. When the changes are applied and postmaster is stopped and restarted, it works fine. -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Wednesda

Re: [ADMIN] pg_hba changes not honored

2006-08-23 Thread Joshua D. Drake
Tom Lane wrote: "Sriram Dandapani" <[EMAIL PROTECTED]> writes: When I update pg_hba.conf to disallow certain client machines from connectiong (update pg_hba.conf, pg_ctl reload), I still see new connections appearing in the process list from the clients I want to disconnect. Sounds to me like

Re: [ADMIN] pg_hba changes not honored

2006-08-23 Thread Tom Lane
"Sriram Dandapani" <[EMAIL PROTECTED]> writes: > This is the complete pg_hba contents > local all all trust > # IPv4 local connections: > hostall all 127.0.0.1/32 trust > #host all all 172.31.0.84/24trust

Re: [ADMIN] pg_hba changes not honored

2006-08-23 Thread Sriram Dandapani
This is the complete pg_hba contents local all all trust # IPv4 local connections: hostall all 127.0.0.1/32 trust #host all all 172.31.0.84/24trust # IPv6 local connections: hostall all

Re: [ADMIN] pg_hba changes not honored

2006-08-23 Thread Tom Lane
"Sriram Dandapani" <[EMAIL PROTECTED]> writes: > When I update pg_hba.conf to disallow certain client machines from > connectiong (update pg_hba.conf, pg_ctl reload), I still see new > connections appearing in the process list from the clients I want to > disconnect. Sounds to me like a mistake in

[ADMIN] pg_hba changes not honored

2006-08-23 Thread Sriram Dandapani
When I update pg_hba.conf to disallow certain client machines from connectiong (update pg_hba.conf, pg_ctl reload), I still see new connections appearing in the process list from the clients I want to disconnect. Why does this happen. (I noticed this behavior with jdbc connections in jboss

Re: [ADMIN] [GENERAL] How to detect Postgres deadlocks?

2006-08-23 Thread Scott Marlowe
On Wed, 2006-08-23 at 14:45, andre wrote: > Hi, > > We are using Postgres 7.4.5, and I'm trying to find a way to detect > and gather deadlock information. > > 1) Which sql queries should I use to detect deadlocks while they are > happening? I see the deadlock info on the log file, but I'd like to

Re: [ADMIN] [GENERAL] How to detect Postgres deadlocks?

2006-08-23 Thread Peter Eisentraut
andre wrote: > 1) Which sql queries should I use to detect deadlocks while they are > happening? I see the deadlock info on the log file, but I'd like to > query the database to see them as they happen... Since deadlocks are broken up within one second, it will be hard to actually see them. You

Re: [ADMIN] Slow delete/insert.

2006-08-23 Thread Joshua D. Drake
It takes 6 minutes to ingest the data with an empty database and 25 minutes (wall time) if all the data is already in the database. The processing is done as follows: 1. Start transaction 2. check if message is in table 1 and if so delete records from table 1(1 row),2(2 rows),3(30 rows),4(50 ro

Re: [ADMIN] Server Down problem

2006-08-23 Thread Tom Lane
Volker <[EMAIL PROTECTED]> writes: > currently we have a server down problem. The server is a IBM xSeries > 306, 1GB RAM, HD 36GB RAID1. Operating System ist Debian Sarge 3.1. > Postgresql version is 7.4.7. > The reason for this ist not knwon. The postmaster won't come up any > more. Here is the re

[ADMIN] Slow delete/insert.

2006-08-23 Thread Thor Tall
I am in the process of converting an old system to a new system where I have chosen to use postgres in stead of a home grow system based on b-trees. The system receives 2650 message a total of 10Mbytes of data per 15 minutes this information have to be store in 4 tables in the database. Some of t

[ADMIN] Server Down problem

2006-08-23 Thread Volker
Hello all, currently we have a server down problem. The server is a IBM xSeries 306, 1GB RAM, HD 36GB RAID1. Operating System ist Debian Sarge 3.1. Postgresql version is 7.4.7. The reason for this ist not knwon. The postmaster won't come up any more. Here is the relevant content of the postgres.