X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id E51E6D1C922 for ; Mon, 21 Jul 2003 20:04:57 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 04944-10 for ; Mon, 21 Jul 2003 20:04:46 +0000 (GMT) Received: from colsweeper.cranel.com (newmail.cranel.com [12.32.71.147]) by svr1.postgresql.org (Postfix) with ESMTP id 55756D1C4E0 for ; Mon, 21 Jul 2003 17:04:46 -0300 (ADT) Received: from colmail01.cranel.com (colmail01.cranel.com) by colsweeper.cranel.com (Content Technologies SMTPRS 4.2.5) with ESMTP id for ; Mon, 21 Jul 2003 15:58:01 -0400 Received: from cranel.com (gspiegelberg.cranel.com [192.168.11.134]) by colmail01.cranel.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id PLSRD8ZR; Mon, 21 Jul 2003 16:07:07 -0400 Message-ID: <3F1C3638.8060100@cranel.com> Date: Mon, 21 Jul 2003 14:51:36 -0400 From: Greg Spiegelberg Organization: Cranel, Inc. User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.9) Gecko/20020408 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Mass file imports Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/341 X-Sequence-Number: 2634 Hello, I'm hunting for some advice on loading 50,000+ files all less than 32KB to a 7.3.2 database. The table is simple. create table files ( id int8 not null primary key, file text not null, size int8 not null, uid int not null, raw oid ); The script (currently bash) pulls a TAR file out of a queue, unpacks it to a large ramdisk mounted with noatime and performs a battery of tests on the files included in the TAR file. For each file in the TAR is will add the following to a SQL file... update files set raw=lo_import('/path/to/file/from/tar') where file='/path/to/file/from/tar'; This file begins with BEGIN; and ends with END; and is fed to Postgres via a "psql -f sqlfile" command. This part of the process can take anywhere from 30 to over 90 minutes depending on the number of files included in the TAR file. System is a RedHat 7.3 running a current 2.4.20 RedHat kernel and dual PIII 1.4GHz 2GB of memory 512MB ramdisk (mounted noatime) mirrored internal SCSI160 10k rpm drives for OS and swap 1 PCI 66MHz 64bit QLA2300 1 Gbit SAN with several RAID5 LUN's on a Hitachi 9910 All filesystems are ext3. Any thoughts? Greg -- Greg Spiegelberg Sr. Product Development Engineer Cranel, Incorporated. Phone: 614.318.4314 Fax: 614.431.8388 Email: gspiegelberg@Cranel.com Cranel. Technology. Integrity. Focus.