Re: File system performance and pg_xlog

Marko Kreen wrote:
>
> On Sat, May 05, 2001 at 06:43:51PM -0400, mlw wrote:
> > Marko Kreen wrote:
> > > On Sat, May 05, 2001 at 01:09:38PM -0400, mlw wrote:
> > > > A small debate started with bad performance on ReiserFS. I pondered the likely
> > > > advantages to raw device access. It also occured to me that the FAT file system
> > > > is about as close to a managed raw device as one could get. So I did some
> > > > tests:
>
> > I think a "pgfs" could easily be a derivative of FAT, or even FAT with some
> > Ioctls. It is simple, it is fast, it does not attempt to do things postgres
> > doesn't need.
>
> Well, my opinion too is that it is waste of resources to try
> implement PostgreSQL-specific filesystem. As you already showed
> that there are noticeable differences of different filesystems,
> the Right Thing would be to make a FAQ/web-page/knowledge-base
> of comments on different filesystem in point of view of DB
> (PostgreSQL) server.
>
> Also users will have different priorities:
> reliability/speed-of-reads/speed-of-writes - I mean different
> users have them ordered differently - so it should be mentioned
> this fs is good for this but bad on this, etc... It is good
> to put this part of db on this fs but not that part of db...
> Suggestions on mount flags to use...

I think it is simpler problem than that. Postgres, with fsync enabled, does a
lot of work trying to maintain data integrity. It is logical to conclude that a
file system that does as little as possible would almost always perform better.
Regardless of what the file system does, eventually it writes blocks of data to
sectors on a disk.

Many databases use their own data volume management. I am not suggesting that
anyone create a new file system, but after performing some tests, I am really
starting to see why products like oracle manage their own table spaces.

If one looks at the FAT file system with an open mind and a clear understanding
of how it will be used, some small modifications may make it the functional
equivalent of a managed table space volume, at least under Linux.

Some of the benchmark numbers are hovering around 20% improvement! That's
nothing to sneeze at. I have a database loader that does a select nextval(..)
followed by a begin, a series of inserts, followed by a commit.

With xlog on a FAT file system, I can get 53-60 sets per second. With Xlog
sitting on ext2, I can get 40-45 sets per second. (Of the same data) These are
not insignificant improvements, and should be examined. If not from a Postgres
development perspective, at least from a deployment perspective.

>
> There already exist bazillion filesystems, _some_ of them should
> be usable for PostgreSQL too :)

I agree.

--
I'm not offering myself as an example; every life evolves by its own laws.
------------------------
http://www.mohawksoft.com