Skip site navigation (1) Skip section navigation (2)

update query blows out

From: shane hill <shill(at)adobe(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: update query blows out
Date: 2003-04-12 17:14:52
Message-ID: 3E98498C.80306@adobe.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi folks,   sorry if the following is confusing, I have just tried to 
provide the pertinent info and I have been up for more than 24 hours 
working. I am getting weary....

I am having a problem with an update transaction. I need to update 4000+ 
records but the update query keeps blowing out postgres and at times I 
am forced to restart the postmaster or reboot my server if I update 
2500+ records. The query is fine with 2225 records it is just somewhere 
beyond 2225 that brings the server down.

I assumed this was related to the shared memory settings but when I 
tried changing those values the behavior was identical. I did not try 
beyond 256 megs for  shmmax.
I then tried the temporary solution of lowering the number of 
shared_buffers and max_connections but that did not change anything either.
I then tried using the IN operator but that did not change anything either

I am wondering if there is some other limit that I am hitting in MacOSX 
that is not related to the SHM vars.

I hope I am just overlooking something simple and that the list will 
come back with some chiding and an answer :)

does anybody have any suggestions?

thank you very much,

-Shane


-----------------------------------------------------------
SQL DETAILS:

my query is of this form:

BEGIN;

UPDATE "mytable" SET "n_filtered"=0,"n_dirty"=1 where ("s_fileName" = 
'filename1' OR "s_fileName" = 'filename2' ....... OR "s_fileName" = 
'filename2000') AND ("n_objId=12345);

COMMIT;

explain tells me this:

seq scan on "mytable" (cost=0.00..5020.00 rows=5 width=174)  Filter 
 .........

-----------------------------------------------------------

-----------------------------------------------------------
SYSTEM:

os:           Mac 10.2.4
chip:        1.4 GHz
ram:        1 GB
-----------------------------------------------------------

-----------------------------------------------------------
ERROR MESSAGE:

server process (pid 650) was terminated by signal 11
all server processes terminated; reinitializing shared memopry and 
semaphores

-----------------------------------------------------------

-----------------------------------------------------------
SOLUTIONS I HAVE TRIED

1. tweaking the five kern.sysv vars. to every configurable option possible

currently I am at:

kern.sysv.shmmax: 1073741824
kern.sysv.shmmin: 256
kern.sysv.shmmni: 8192
kern.sysv.shmseg: 2048
kern.sysv.shmall: 262144

(I know shmmin is way too high according to the stuff I read, I was just 
getting desperate, I have tried just leaving it at 1)


2. lowering the number of shared_buffers and  max_connections to 32:16 
32:8 16:8 in postgresql.conf

currently I am at 64 shared_bufs  and 32 max_connects (these were the 
defaults)

3. Using the IN sql operator rather than a bunch of ORs, but I still 
have the same problem

-------------------------------------


In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2003-04-12 17:16:24
Subject: Re: update query blows out
Previous:From: Ron MayerDate: 2003-04-11 17:25:24
Subject: Re: Caching (was Re: choosing the right platform)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group