Can I somehow force PogSQL to do direct I/O on Ext3 file system? All
those FS buffers are messing up free memory and swap partitions.



--
http://mgogala.byethost5.com

On Tue, 26 Jan 2010 21:55:54 +0000, Mladen Gogala wrote:

I have a solution, not for the fainthearted: I installed a "single node
CRS" oracle RAC cluster from the version 11.2 and ocfs2 for the kernel
2.6.18.164.11.1. I was able to add a tablespace on the ocfs2 file system.
Unfortunately, when I do that, there are licensing considerations. Oracle
CRS is not free and ocfs2 will not work without CRS. Is there any other
file system which supports ONLY direct I/O? Is there any Linux FS which
can be mounted for direct I/O only?



--
http://mgogala.freehostia.com

Mladen Gogala wrote:
Sure, all you have to do is mount the file system with the "sync"
option, like:

mount -t ext3 -o sync /dev/whatever /mountpoint

The man page for "mount" describes "sync" as follows:

sync All I/O to the file system should be done synchronously.

Some notes on your original problem:

File system buffering should not cause problems.
Only memory that is not required elsewhere should
be used. Any problem you experience here should be a problem
of the Linux kernel.
Is it a problem for you if free memory is used by the
file system buffer?

I ask because there are two approaches to buffering PostgreSQL
databases in RAM:
a) make shared_buffers as large as possible and let PostgreSQL
do the buffering (this is probably what you are doing).
b) keep shared_buffers comparatively small and let the file
system buffer do the work.

Contrary to what I had expected, many knowledgable PostgreSQL
developers advocate version b) and say that they experience
better performance that way.

I guess that nobody would advocate b) if there were known problems
with file system buffering.

Yours,
Laurenz Albe

On Wed, 27 Jan 2010 11:00:33 +0100, Laurenz Albe wrote:


Thanks, I will try although it doesn't look exactly like the direct I/O.
Direct I/O bypasses the system buffers and uses the user buffer.
Consequence is that the I/O is synchronous, but there is also no memory
being wasted. This might be the case with synchronous mount, I don't know.

Using the free memory for FS buffers is a problem. The memory is not
shown as free, which means that it isn't directly usable by the other
processes. Also, swap has to be allocated. As for that being the problem
of the Linux kernel, I couldn't agree more. Linux kernel does extremely
poor job managing virtual memory when compared with some true blue Unix
variants like HP-UX, AiX and Solaris which all can limit the amount of
memory allocated for the FS buffers. The inventions of "swapiness" and
"Out Of Memory killer" are laughable solutions to creating an idiot-proof
VM management system. Linux is geared toward a desktop user and Unix is a
server OS. That is where such a difference in mentality and philosophy is
coming from.


That is exactly right. The database should probably better know what to
do with the database blocks than a general purpose FS.


If that is so, that is a bad testimony for PostgreSQL and the efficiency
of the buffer cache. I don't have default buffer cache, my cache is
moderately sized, 512MB.


I find it very difficult to accept that the general purpose FS buffering
could be more efficient than the specialized DB buffering. If that is so,
what is the purpose of the latter?



--
http://mgogala.freehostia.com

Il 27/01/2010 11.00, Laurenz Albe ha scritto:
Hi, usually I keep shared_buffers high (25% of RAM)
how much % of memory is it meant for comparatively small?
What impact would have a low shared_buffers value on checkpoints?

are performance better only on write or in queries too?

Mladen Gogala wrote:
I don't know ext3 internals, but I would assume that synchronous I/O
bypasses the filesystem cache. Why should it mess with the cache if
it waits for the disk anyway?

As far as I know, the memory *is* usable by other processes.
Normally "free" will report very small values on a Linux machine,
but that won't keep a large memory allocation request from succeeding.

Here is a link to a mail from Tom Lane, for reference:
http://archives.postgresql.org/pgsql...2/msg00471.php

I cannot answer your question or dispel your doubts.
Performance questions are frequently hard questions, and often there
is no single simple good answer.

What I would recommend is that you run a couple of performance tests
on your particular system with various settings. That way you could
find a satisfactory answer for what is better in your particular case.

A test should also reveal if "sync" improves your performance.

Yours,
Laurenz Albe

On Wed, 27 Jan 2010 15:44:15 +0100, Anselmo Canfora wrote:

I imagine that the performance would be better primarily on queries,
because of the pre-fetch.



--
http://mgogala.byethost5.com

Anselmo Canfora wrote:
I don't know :^)

25% of RAM seems to be the wrong value in any case -
see the mail I quoted in my response to Mladen's posting
in the same thread.

I would expect checkpoints to be faster, because there are
probably fewer dirty pages. On the downside, overall I/O performance
may be worse because dirty pages will be forced to disk more
often...

Again, I don't know. You'd have to experiment.

Yours,
Laurenz Albe

On 01/27/2010 03:24 PM, Mladen Gogala wrote:

There have been several flames about the matter on the linux-kernel
mailing list.

this is the first like I got:

http://kerneltrap.org/node/7563

On Wed, 27 Jan 2010 17:08:01 +0100, Marco Mariani wrote:

I am afraid that I am the one of those "brain dead database people" who
has been using it on Unix, long before the advent of Linux. What is more,
I am hell bent on remaining one of those "brain dead database people" and
if Linux doesn't provide that interface, something else will. The
question is only whether my DB of choice will allow me to use it. If not,
there is always another DB.



--
http://mgogala.byethost5.com