Thread: 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/
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/
---------------------------------------------------------------------
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/
> 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
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/
---------------------------------------------------------------------
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" <gsam@trini0.org> To: <pgsql-php@postgresql.org> 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