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 96E643A5C34 for ; Sat, 4 Dec 2004 13:37:38 +0000 (GMT) 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 50335-05 for ; Sat, 4 Dec 2004 13:37:30 +0000 (GMT) Received: from nitrogenio.carvalhaes.net (oxigenio.carvalhaes.net [200.102.144.130]) by svr1.postgresql.org (Postfix) with ESMTP id 33A3D3A5C24 for ; Sat, 4 Dec 2004 13:37:31 +0000 (GMT) Received: from [192.168.0.5] ([192.168.0.5]) (authenticated bits=0) by nitrogenio.carvalhaes.net (8.12.11/8.12.11) with ESMTP id iB4DT4iT004761 for ; Sat, 4 Dec 2004 11:29:04 -0200 Message-ID: <41B1BE1B.10301@carvalhaes.net> Date: Sat, 04 Dec 2004 11:39:39 -0200 From: Grupos User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Improve BULK insertion Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable X-Carvalhaes-MailScanner-Information: Favor contactar o suporte técnico para maiores informações suporte@triade.com X-Carvalhaes-MailScanner: Found to be clean X-MailScanner-From: grupos@carvalhaes.net X-Virus-Scanned: by amavisd-new at hub.org X-Amavis-Alert: BAD HEADER Non-encoded 8-bit data (char E9 hex) in message header 'X-Carvalhaes-MailScanner-Information': X-Carvalhaes-MailScanner-Information: Favor contactar o suporte t\351cnico para maio... X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200412/94 X-Sequence-Number: 9469 Hi ! I need to insert 500.000 records on a table frequently. It=B4s a bulk=20 insertion from my applicatoin. I am with a very poor performance. PostgreSQL insert very fast until the = tuple 200.000 and after it the insertion starts to be really slow. I am seeing on the log and there is a lot of transaction logs, something = like : 2004-12-04 11:08:59 LOG: recycled transaction log file "0000000600000012= " 2004-12-04 11:08:59 LOG: recycled transaction log file "0000000600000013= " 2004-12-04 11:08:59 LOG: recycled transaction log file "0000000600000011= " 2004-12-04 11:14:04 LOG: recycled transaction log file "0000000600000015= " 2004-12-04 11:14:04 LOG: recycled transaction log file "0000000600000014= " 2004-12-04 11:19:08 LOG: recycled transaction log file "0000000600000016= " 2004-12-04 11:19:08 LOG: recycled transaction log file "0000000600000017= " 2004-12-04 11:24:10 LOG: recycled transaction log file "0000000600000018= " How can I configure PostgreSQL to have a better performance on this bulk = insertions ? I already increased the memory values. My data: Conectiva linux kernel 2.6.9 PostgreSQL 7.4.6 - 1,5gb memory max_connections =3D 30 shared_buffers =3D 30000 sort_mem =3D 32768 vacuum_mem =3D 32768 max_fsm_pages =3D 30000 max_fsm_relations =3D 1500 The other configurations are default. Cheers, Rodrigo Carvalhaes =20