Re: priority on a process - Mailing list pgsql-general

On Fri, 16 May 2003, Chris Palmer wrote:

>  Scott Marlowe writes:
>
> > Yes, effective_cache_size is the postgresql setting that
> > tells the postmaster we have "about this much kernel cache"
> > on average.
> >
> > If it's set low, then postgresql assumes the kernel isn't
> > caching much, if it's higher, then it assumes it's more
> > likely for data to be "in memory"
> > and makes index scans more likely than seq scans.
> >
> > Sorry, I should have pointed out I was talking out about a
> > postgresql configuration parameter and not a linux one...
>
> Ahh. Thanks for the tip (I am still new to Pg).
>
> I guess the reason Pg can't ask the running kernel how much cache mem it
> has is that any such thing would be completely non-portable.
>
>
> Now branching the thread:
>
> The documentation (doc/runtime-config.html in my 7.3.2 source tree) says
> the value is a number of disk pages, and that disk pages are usualy 8KB.
> My filesystem has 4KB blocks; are blocks and pages the same thing in
> this context, or does "page" refer to the in-memory copy of a disk
> block? Are bigger blocks/pages on the filesystem used for Pg a good
> idea? I would guess yes, since Pg's data files are few and large,
> instead of many and small. Should I just crank my fs block size as big
> as it will go, on a partition dedicated to pg_data?

They mean it's measured in postgresql disk blocks, so, it's the size block
postgresql was compiled in that matters for that.  Linux is currently
limited to a maximum block size of whatever a memory page is, and under
x86, that's 4k.  On bigger hardware you can have larger blocks.Itanium can
do 8k, Itanium2 can do 16 to 64k, most other 64 bit archs do up to 64k
page size.

Now, postgresql has it's own block size, which defaults to 8k.

This is a good compromise in size.  If you're dealing with really large
rows all the time (say 32k and above) then going to a larger block size in
postgresql will help that.  However, this will come at the expense of
performance on smaller accesses.  Also, some subtle bugs have been known
to creep into postgresql when increasing the block size in the past, so
careful testing is called for if you're going to try increasing the block
size.

The idea behind blocks of 4k as opposed to the default 512 byte sector
size of most hard drives is that on a typical 9ms access hard drive,
you'll spend 9ms moving the head, another 4 or 5 waiting for the head to
settle, and another 4 or so waiting for the sector you want to spin around
under the head you're using.  Since we're waiting on the drive most of
this time, we might as well make the best of it and read in a bit more.
The Linux OS sets to to a default of 4k, while postgresql uses 8k blocks,
which means the OS will make two "high level" requests for those 4k
blocks.  Since a lot of what gets accessed next will likely be in the same
block as what we're reading, it's usually a win.  Note that once the
sectors are under the head the actual read time is tiny, say 1 ms or so.

But, when block size starts rising, then the read time of each block
rises, and if you are only using on average a small part of each block
(say 1 or 2k) then you're reading more than you need.

Hence, optimal block size may be influenced by the type of load, but there
is a maximum size at which you really don't see much return, even with a
load that reads large chunks at a time.

Note that also, the maximum size of an index entry is about 1/3 of a block
(pgsql block) so if you are indexing LARGE fields, then a larger block
size might be helpful, but often, indexes on large fields tend to be
suboptimal compared to a full text indexing engine, and postgresql has two
of those available that work quite well, so the argument for a larger
blocksize there kind of goes away for about 90% of all applications.