Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4D43E475FFC for ; Sun, 5 Jan 2003 23:59:37 -0500 (EST) Received: from kant.catalyst.net.nz (203-79-74-15.adsl.paradise.net.nz [203.79.74.15]) by postgresql.org (Postfix) with ESMTP id 788B2475CEE for ; Sun, 5 Jan 2003 23:59:35 -0500 (EST) Received: from 127.0.0.1 (ident=unknown) by kant.catalyst.net.nz with esmtp (masqmail 0.2.16) id 18VPM8-2Nk-00; Mon, 06 Jan 2003 17:59:20 +1300 Subject: Re: Database File system requirements. From: Andrew McMillan To: Gerard Samuel Cc: pgsql-php@postgresql.org In-Reply-To: <3E18FCBD.3000700@trini0.org> References: <3E18CF80.6070007@trini0.org> <1041814728.6611.72.camel@kant.mcmillan.net.nz> <3E18FCBD.3000700@trini0.org> Content-Type: text/plain Organization: Message-Id: <1041829160.6608.169.camel@kant.mcmillan.net.nz> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 06 Jan 2003 17:59:20 +1300 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/17 X-Sequence-Number: 1762 On Mon, 2003-01-06 at 16:49, Gerard Samuel wrote: > > > >Surely we'd need to know how many _rows_ there are in those tables? Also > >how many indexes and index rows? > > > True. But not sure how to gather all that info for right now. > To get a more accurate picture, I dropped the one database that is in > Postgre, stopped and restarted Postgre. > It seems to be using just over 62M. > I recreated the database and file usage remains unchanged (looking via > df -H), the database isn't really that large maybe about 1000 rows of > data currently. That seems normal. I think that PostgreSQL default WAL setup is to have four write-ahead log files of 16MB each, which would be 64M, and your data would kind of pale into insignificance beside that. The WAL files are where PostgreSQL writes changes during a database transaction before the transaction is committed to the database. Or something like that... (someone who really understands the database internals is probably cringing at my description :-) This overhead is kind of one-off. On systems that do very large transactions before COMMIT you will find the numbers of these files will increase, but in general 4 x 16M will be enough for most things. On a much larger database than the one on my laptop, the WAL files take up 130M even though the DB itself takes up 2.5G. The WAL files are in the data/pg_xlog directory, separate to the data/base directory for the actual database files, at least that's how it's organised under Debian - I think it's the same under other setups but YMMV. > > > >I believe that PostgreSQL does have greater overhead on disk than MySQL, > >however. Just to have the database started will mean that you have a > >number of write-ahead logs (typically 16MB each - on my laptop these > >take up 114M) which are used internally by the database server for > >transaction handling - MySQL doesn't need these, of course. > > > Well if its normal for PostgreSQL to need more disk space, looks like Im > going to have to do a couple of disk to disk copying soon, > and repartion my HD. Hopefully not too dramatic. I've given up on multiple partitions for my laptop the last few years, currently: 100M /boot, 512M of tmpfs on /tmp and the remaining 59G on / and I have saved myself a lot of hassles. Cheers, Andrew. -- --------------------------------------------------------------------- Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267 Survey for nothing with http://survey.net.nz/ ---------------------------------------------------------------------