Thread: Linux Filesystem for PG
Can anyone recemmend a filesystem to use for Postgres. I currently have one table that has 80 mil rows, and will take roughly 8GB of space without indexing. Obviously EXT3 will die for a file size this large. Any suggestions with be helpful.
Thanks,
Joe,
--------------------------------------------
Joseph M. Day
Global Innovative Solutions
On Mon, Mar 28, 2005 at 12:29:13AM -0600, Joseph M. Day wrote: > Can anyone recemmend a filesystem to use for Postgres. I currently have > one table that has 80 mil rows, and will take roughly 8GB of space > without indexing. Obviously EXT3 will die for a file size this large. From the "Database Physical Storage" chapter in the 8.0 documentation: When a table or index exceeds 1Gb, it is divided into gigabyte-sized segments. The first segment's file name is the same as the filenode; subsequent segments are named filenode.1, filenode.2, etc. This arrangement avoids problems on platforms that have file size limitations. http://www.postgresql.org/docs/8.0/interactive/storage.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Mon, Mar 28, 2005 at 12:55:52AM -0600, Joseph M. Day wrote: > > From the "Database Physical Storage" chapter in the 8.0 documentation: > > > > When a table or index exceeds 1Gb, it is divided into gigabyte-sized > > segments. The first segment's file name is the same as the > > filenode; subsequent segments are named filenode.1, filenode.2, > > etc. This arrangement avoids problems on platforms that have > > file size limitations. > > Is this a recent change? I have an old system loaded with Redhat 8.0 and > PG v7.2. Unless my memory fails me, it died trying to load the data into > the table. The 7.2 source code appears to have this logic; I don't know if there are any problems with it. "It died" doesn't mean much -- a load could have failed for a number of reasons, so without the error message it's impossible to know what happened. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Hello all, i am not being able to set the default autocommit feature of PG to off. i am using PG 8.0 Windows version and the following command : SET AUTOCOMMIT TO OFF throwing an error: ERROR: SET AUTOCOMMIT TO OFF is no longer supported Please suggest an alternative.
After a long battle with technology, jday@gisolutions.us ("Joseph M. Day"), an earthling, wrote:
> Can anyone recemmend a filesystem to use for Postgres. I currently
> have one table that has 80 mil rows, and will take roughly 8GB of
> space without indexing. Obviously EXT3 will die for a file size this
> large. Any suggestions with be helpful.
Actually, it is common for "obvious" facts to be entirely incorrect.
-> ext3 wouldn't "die" with a file of that size; it supports files up
to about 2TB in size, and 8GB shouldn't be an "uncomfortable" size
-> PostgreSQL normally switches to a new file at 1GB intervals, so
that no file is ever larger than 1GB in size
That's not to say that ext3 would be my "favorite" for the purpose;
while I am not entirely decided as to the relative merits of JFS and
XFS, I'd generally prefer them to ext3.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/slony.html
"If you haven't settled on your final year project, perhaps you would
like to write a C compiler that turns code into Turing machines : I
don't see anything wrong with that" -- Arthur Norman
On Mon, Mar 28, 2005 at 06:09:22PM +0530, Rajarshi Mukherjee wrote: > > i am not being able to set the default autocommit feature of PG to off. > i am using PG 8.0 Windows version and the following command : > SET AUTOCOMMIT TO OFF > throwing an error: > ERROR: SET AUTOCOMMIT TO OFF is no longer supported Server-side autocommit was removed in 7.4 so now it's just a client-side behavior. In psql you can use "\set AUTOCOMMIT off"; otherwise see the documentation for your client interface. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
From what I have gathered on the performance list, JFS seemed to be the best overall choice, but I'd say check the archives of pgsql-performance because so many of your I/O needs depends on what you're going to be doing with your database. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source — Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 28, 2005, at 12:29 AM, Joseph M. Day wrote: > Can anyone recemmend a filesystem to use for Postgres. I currently > have one table that has 80 mil rows, and will take roughly 8GB of > space without indexing. Obviously EXT3 will die for a file size this > large. Any suggestions with be helpful. > > Thanks, > > Joe, > > > > -------------------------------------------- > Joseph M. Day > Global Innovative Solutions
Actually, it is common for "obvious" facts to be entirely incorrect. -> ext3 wouldn't "die" with a file of that size; it supports files up to about 2TB in size, and 8GB shouldn't be an "uncomfortable" size -> PostgreSQL normally switches to a new file at 1GB intervals, so that no file is ever larger than 1GB in size That's not to say that ext3 would be my "favorite" for the purpose; while I am not entirely decided as to the relative merits of JFS and XFS, I'd generally prefer them to ext3. -- let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;; http://linuxdatabases.info/info/slony.html "If you haven't settled on your final year project, perhaps you would like to write a C compiler that turns code into Turing machines : I don't see anything wrong with that" -- Arthur Norman