Обсуждение: Postgres Optimization: IO and Data Organization

I want to understand how Postgres organizes data and handles IO
operations so that I will better know how to optimize a Postgres
database server. I am looking for answers to specific questions and
pointers to where this stuff is documented.

How does Postgres organize its data? For example, is it grouped
together on the disk, or is it prone to be spread out over the disk?
Does vacuum reorganize the data? (Seeking to minimize disk head
movement.)

How does Postgres handle sequential IO? Does it treat is specially
such as issuing large IO operations that span block boundaries?

How does Postgres handle direct IOs (operations directly to disk,
bypassing the buffer cache)? Will it issue multiple asynchronous IO
operations?

Is Postgres always one process per client, or can it spawn additional
processes to parallelise some operations such as a nested loops join
operation?

Is there a recommended file system to use for Postgres data, such as
ext2 or another non-journaling FS?

james@unifiedmind.com (James Thornton) writes:

> I want to understand how Postgres organizes data and handles IO
> operations so that I will better know how to optimize a Postgres
> database server. I am looking for answers to specific questions and
> pointers to where this stuff is documented.

If you haven't read the Postgres docs in detail that would be a good
place to start.  :)

> How does Postgres organize its data? For example, is it grouped
> together on the disk, or is it prone to be spread out over the disk?
> Does vacuum reorganize the data? (Seeking to minimize disk head
> movement.)

Tables and indexes are stored in disk files in the filesystem, so PG
relies on the OS to lay out data on the disk.

> How does Postgres handle sequential IO? Does it treat is specially
> such as issuing large IO operations that span block boundaries?

The WAL (write-ahead log), a sort of journal, is written sequentially.
I"m not too familiar with whether WAL writes are ganged together if
possible, but I would think so.

> How does Postgres handle direct IOs (operations directly to disk,
> bypassing the buffer cache)? Will it issue multiple asynchronous IO
> operations?

No direct I/O, no async I/O.  A background checkpoint process handles
a lot of the data writeback I/O.

> Is Postgres always one process per client, or can it spawn additional
> processes to parallelise some operations such as a nested loops join
> operation?

One process per client connection.  Right now there is no spawning of
additional "worker" processes.

> Is there a recommended file system to use for Postgres data, such as
> ext2 or another non-journaling FS?

You definitely want a journaled FS or the equivalent, since losing
filesystem metadata on a crash can ruin your whole day, not to mention
the fsck times...

There doesn't seem to be a clear winner in the "which FS" debate.  If
you use ext3, it's probably fastest to mount with 'data=writeback' for
your DB partition, since you can rely on PG to journal the data
writes.  Most other FS's only journal metadata anyway.

Hope this helps!

-Doug

Doug McNaught wrote:

> james@unifiedmind.com (James Thornton) writes:

>>Is Postgres always one process per client, or can it spawn additional
>>processes to parallelise some operations such as a nested loops join
>>operation?
> 
> 
> One process per client connection.  Right now there is no spawning of
> additional "worker" processes.

I don't tink you'll see in the near future pre forked process, there was
a discussion about this and the result was a "no way" to do it,
basically because a process during is own boostrap process need to know
the DB name and also because the fork is not so expensive after all.

Please, don't consider optimization that other DBMS are performing will
be good also for postgres, I think Oracle made pre forking of a pool of
process and this because a Oracle process when is forked do more work
then a postmaster.


Regards
Gaetano Mendola