X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 002F6475B85 for ; Sat, 12 Apr 2003 13:06:19 -0400 (EDT) Received: from smtp-relay-3.sea.adobe.com (smtp-relay-3.adobe.com [192.150.22.10]) by postgresql.org (Postfix) with ESMTP id 321F2475AE4 for ; Sat, 12 Apr 2003 13:06:13 -0400 (EDT) Received: from inner-relay-3.corp.adobe.com (inner-relay-3 [153.32.251.51]) by smtp-relay-3.sea.adobe.com (8.12.9/8.12.9) with ESMTP id h3CH67rT009475 for ; Sat, 12 Apr 2003 10:06:13 -0700 (PDT) Received: from mail-321.corp.adobe.com (mail-321 [153.32.2.46]) by inner-relay-3.corp.adobe.com (8.12.3/8.12.3) with ESMTP id h3CH67iQ028819 for ; Sat, 12 Apr 2003 10:06:07 -0700 (PDT) Received: from adobe.com (c-155-123.corp.adobe.com [153.32.155.123]) by mail-321.corp.adobe.com (8.11.4/8.11.4) with ESMTP id h3CH4o922189 for ; Sat, 12 Apr 2003 10:04:50 -0700 (PDT) Message-ID: <3E98498C.80306@adobe.com> Date: Sat, 12 Apr 2003 10:14:52 -0700 From: shane hill User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.0.0) Gecko/20020530 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance Subject: update query blows out References: <564664787.20030410102049@procedium.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-18.6 required=5.0 tests=BAYES_10,REFERENCES,USER_AGENT_MOZILLA_UA autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/124 X-Sequence-Number: 1630 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 -------------------------------------