Database File system requirements.
Database File system requirements.
От:
Gerard Samuel <gsam@trini0.org>
Дата:
Im not sure if this is the right list, but here goes. I have a dev box, that I allocated, 104M of space to share between, mysql & pgsql. In mysql, I have 376 tables across 11 databases which is taking up only 1.3M. In pgsql, I have 33 tables on 1 database, and that seems to be taking up 74M. Does postgresql require soooo much disk space to operate?? Thanks for any insight you may provide. -- Gerard Samuel http://www.trini0.org:81/ http://dev.trini0.org:81/
Re: Database File system requirements.
От:
Andrew McMillan <andrew@catalyst.net.nz>
Дата:
On Mon, 2003-01-06 at 13:36, Gerard Samuel wrote:
> Im not sure if this is the right list, but here goes.
> I have a dev box, that I allocated, 104M of space to share between,
> mysql & pgsql.
> In mysql, I have 376 tables across 11 databases which is taking up only
> 1.3M.
> In pgsql, I have 33 tables on 1 database, and that seems to be taking up
> 74M.
>
> Does postgresql require soooo much disk space to operate??
> Thanks for any insight you may provide.
Surely we'd need to know how many _rows_ there are in those tables? Also
how many indexes and index rows?
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.
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/
---------------------------------------------------------------------
Re: Database File system requirements.
От:
Gerard Samuel <gsam@trini0.org>
Дата:
Andrew McMillan wrote: >On Mon, 2003-01-06 at 13:36, Gerard Samuel wrote: > > >>Im not sure if this is the right list, but here goes. >>I have a dev box, that I allocated, 104M of space to share between, >>mysql & pgsql. >>In mysql, I have 376 tables across 11 databases which is taking up only >>1.3M. >>In pgsql, I have 33 tables on 1 database, and that seems to be taking up >>74M. >> >>Does postgresql require soooo much disk space to operate?? >>Thanks for any insight you may provide. >> >> > >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. > >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. Thanks > >Cheers, > Andrew. > > -- Gerard Samuel http://www.trini0.org:81/ http://dev.trini0.org:81/
Re: Database File system requirements.
От:
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Дата:
> 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. > 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. What's taking up all the space is probably several 16MB WAL files - unrelated to your data size. PostgreSQL is an enterprise class DB engine, MySQL isn't - you have to accept that it takes up more room and requires more power. Still, it does seem a little large for what you have in there? Do you have heaps and heaps of indexes? Have you run VACUUM FULL? Have you run the reindexdb script? Chris
Re: Database File system requirements.
От:
Andrew McMillan <andrew@catalyst.net.nz>
Дата:
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/
---------------------------------------------------------------------
Re: Database File system requirements.
От:
"David Busby" <busby@pnts.com>
Дата:
Also look at the vacuum command, that makes PG clean up itself, you've got to run it though. http://www.postgresql.org/idocs/index.php?sql-vacuum.html ----- Original Message ----- From: "Gerard Samuel" To: Sent: Sunday, January 05, 2003 16:36 Subject: [PHP] Database File system requirements. > Im not sure if this is the right list, but here goes. > I have a dev box, that I allocated, 104M of space to share between, > mysql & pgsql. > In mysql, I have 376 tables across 11 databases which is taking up only > 1.3M. > In pgsql, I have 33 tables on 1 database, and that seems to be taking up > 74M. > > Does postgresql require soooo much disk space to operate?? > Thanks for any insight you may provide. > > -- > Gerard Samuel > http://www.trini0.org:81/ > http://dev.trini0.org:81/ > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly