From: Andrew McMillan <andrew@catalyst.net.nz>
To: Gerard Samuel <gsam@trini0.org>
Cc: pgsql-php@postgresql.org
Subject: Re: Database File system requirements.
Date: 06 Jan 2003 17:59:20 +1300
Message-ID: <1041829160.6608.169.camel@kant.mcmillan.net.nz> (raw)
In-Reply-To: <3E18FCBD.3000700@trini0.org>
References: <3E18CF80.6070007@trini0.org>
<1041814728.6611.72.camel@kant.mcmillan.net.nz>
<3E18FCBD.3000700@trini0.org>
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/
---------------------------------------------------------------------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: andrew@catalyst.net.nz, gsam@trini0.org
Subject: Re: Database File system requirements.
In-Reply-To: <1041829160.6608.169.camel@kant.mcmillan.net.nz>
References: <3E18CF80.6070007@trini0.org> <1041814728.6611.72.camel@kant.mcmillan.net.nz> <3E18FCBD.3000700@trini0.org> <1041829160.6608.169.camel@kant.mcmillan.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox