X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D9CCCD1B181 for ; Wed, 14 Jul 2004 18:19:26 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 34849-03 for ; Wed, 14 Jul 2004 21:19:20 +0000 (GMT) Received: from mx.noos.fr (pm-mx4.mgn.net [195.46.220.210]) by svr1.postgresql.org (Postfix) with ESMTP id B6894D1B199 for ; Wed, 14 Jul 2004 18:19:16 -0300 (ADT) Received: from noos.fr (pm-mnet3.mgn.net [195.46.220.218]) by mx.noos.fr (Postfix) with SMTP id 3A8C726C5A; Wed, 14 Jul 2004 23:19:17 +0200 (MEST) X-Mailbox-Line: From footcow@noos.fr Wed Jul 14 23:19:17 2004 Received: from e115.dhcp212-198-145.noos.fr (e115.dhcp212-198-145.noos.fr [212.198.145.115]) by pm-mnet3.mgn.net with ESMTP; Wed, 14 Jul 2004 23:19:17 (MEST) From: =?iso-8859-15?q?Herv=E9_Piedvache?= To: pgsql-performance@postgresql.org Subject: Re: Insert are going slower ... Date: Wed, 14 Jul 2004 23:19:16 +0200 User-Agent: KMail/1.6.2 Cc: Josh Berkus , =?iso-8859-15?q?Herv=E9_Piedvache?= , Shridhar Daithankar References: <200407131850.52095.herve@elma.fr> <200407140142.11720.footcow@noos.fr> <200407140928.29498.josh@agliodbs.com> In-Reply-To: <200407140928.29498.josh@agliodbs.com> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: quoted-printable Message-Id: <200407142319.16607.footcow@noos.fr> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=RCVD_IN_NJABL, RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200407/85 X-Sequence-Number: 7476 Josh, Le mercredi 14 Juillet 2004 18:28, Josh Berkus a =E9crit : > > I forgot to ask about your hardware. How much RAM, and what's your disk > setup? CPU? 8 Gb of RAM Bi - Intel Xeon 2.00GHz Hard drive in SCSI RAID 5 /dev/sdb6 101G 87G 8.7G 91% /usr/local/pgsql/data /dev/sda7 1.8G 129M 1.6G 8% /usr/local/pgsql/data/pg_xlog Server dedicated to PostgreSQL with only one database. > > sort_mem =3D 512000 > > Huh? Sort_mem is in K. The above says that you've allocated 512MB sort > mem. Is this process the *only* thing going on on the machine? PostgreSQL dedicated server yes ... so it's too much ? How you decide the good value ? > > vacuum_mem =3D 409600 > > Again, 409.6MB vacuum mem? That's an odd number, and quite high. Yep but I have 8 Gb of memory ... ;o) So why not ? Just explain me why it's not a good choice ... I have done this because of= =20 this text from you found somewhere : "As this setting only uses RAM when VACUUM is running, you may wish to=20 increase it on high-RAM machines to make VACUUM run faster (but never more= =20 than 20% of available RAM!)" So that's less than 20% of my memory ... > > max_fsm_pages =3D 50000000 > > 50million? That's quite high. Certianly enough to have an effect on > your memory usage. How did you calculate this number? Not done by me ... and the guy is out ... but in same time with 8 Gb of=20 RAM ... that's not a crazy number ? > > checkpoint_segments =3D 3 > > You should probably increase this if you have the disk space. For massive > insert operations, I've found it useful to have as much as 128 segments > (although this means about 1.5GB disk space) > > > effective_cache_size =3D 5000000 > > If you actually have that much RAM, I'd love to play on your box. Please? Hum ... yes as Shridhar told me the number is a crazy one and now down to= =20 875000 ... > > Off the top of my head, you have allocated roughly 48K shard buffers > > which seems bit on higher side. Can you check with something like > > 10K-15K? > > Shridhar, that depends on how much RAM he has. On 4GB dedicated machine= s, > I've set Shared_Buffers as high as 750MB. Could you explain me the interest to reduce this size ?? I really miss understand this point ... regards, --=20 Bill Footcow