Обсуждение: Scalability to very large databases under Linux
Dear Folks, We have been running 6.4.2 under Linux on a dual Xeon box with mostly good success. Our current dataset is 20 million records of mostly numerical data (56 fields of floats [70%], ints [20%] and a few text fields [10%]). The performance is acceptable, the only problem so far has been the difficultly in floating type promotion "confusing" the optimizer. Our database is about to expans to 150 million records. Given sufficient diskspace (e.g. a larger RAID array) will postgres perform on this volume? Are there any gotchas? General comments from people with experience with databases of this volume under postgres? Thanks! --Martin =========================================================================== Martin Weinberg Phone: (413) 545-3821 Dept. of Physics and Astronomy FAX: (413) 545-2117/0648 530 Graduate Research Tower University of Massachusetts Amherst, MA 01003-4525
> Dear Folks, > > We have been running 6.4.2 under Linux on a dual Xeon box > with mostly good success. Our current dataset is 20 million records > of mostly numerical data (56 fields of floats [70%], ints [20%] and > a few text fields [10%]). The performance is acceptable, the only > problem so far has been the difficultly in floating type promotion > "confusing" the optimizer. > > Our database is about to expans to 150 million records. Given > sufficient diskspace (e.g. a larger RAID array) will postgres > perform on this volume? Are there any gotchas? General comments > from people with experience with databases of this volume under > postgres? The optimizer is overhauled in 6.5, due out in a day or two. I recommend an upgrade. Optimizer should get less confused. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> Dear Folks, > > We have been running 6.4.2 under Linux on a dual Xeon box > with mostly good success. Our current dataset is 20 million records > of mostly numerical data (56 fields of floats [70%], ints [20%] and > a few text fields [10%]). The performance is acceptable, the only > problem so far has been the difficultly in floating type promotion > "confusing" the optimizer. > > Our database is about to expans to 150 million records. Given > sufficient diskspace (e.g. a larger RAID array) will postgres > perform on this volume? Are there any gotchas? General comments > from people with experience with databases of this volume under > postgres? The only problem I could see immediately is that the Linux ext2 filesystem has a 2GB limit on any one file. Postgresql has functionality to get round that (as was discussed a few months ago on the list) but it apparently causes problems in some instances. I suggest you look through the archives for this one.
M Simms wrote on Tue, 15 Jun 1999 19:58:28 BST >> Dear Folks, >> >> We have been running 6.4.2 under Linux on a dual Xeon box >> with mostly good success. Our current dataset is 20 million records >> of mostly numerical data (56 fields of floats [70%], ints [20%] and >> a few text fields [10%]). The performance is acceptable, the only >> problem so far has been the difficultly in floating type promotion >> "confusing" the optimizer. >> >> Our database is about to expans to 150 million records. Given >> sufficient diskspace (e.g. a larger RAID array) will postgres >> perform on this volume? Are there any gotchas? General comments >> from people with experience with databases of this volume under >> postgres? > >The only problem I could see immediately is that the Linux ext2 filesystem has >a 2GB limit on any one file. Postgresql has functionality to get round that (a >s >was discussed a few months ago on the list) but it apparently causes problems >in some instances. I suggest you look through the archives for this one. > > There is one I know about, which was a fencepost-type bug with the addressing in Linux. There was a patch posted in "hackers" which I had to apply to get this to work in 6.4.2. Our current database already exceeds the 2Gb limit. --Martin