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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
-------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-04-12 17:16:24 | Re: update query blows out |
Previous Message | Ron Mayer | 2003-04-11 17:25:24 | Re: Caching (was Re: choosing the right platform) |