First, its better to ask the experts on this matters at
pgsql-general@postgresql.org
It is not definitive that for postgresql you'd better set
8kb block size since the OS already coalleses the files close
together anyway and there is no guarentee the page will be aligned
to the hardware block size (which can be very different) so I would
be carefull with that assumption.
Regarding the FS, I think you'd better use JFS or XFS for better
performance with DB files such as postgresql.
You cannot just change the postgresql page size (BLCKSZ constant)
since (1) its hardwired and you have to recompile and (2) its used
all over the code and assumed to be 8192 so I'd be carefull with
changing this.
If I had to bet on the best performance from what I have been reading
the last year on the mailing lists I would go with JFS but also try XFS
to see. I would suggest to use the lastest version of the kernel so
these FS will be the most stable.
This is not the whole story though, you have in the postgresql
configuration files ways to
allocate memory for sorts, merges, etc... that can be flushed to disk
just as with your OS swap files. It also depends on your load. I would
suggest you will give speacial attention to that alone
and also read the DB optimization section in the manual.
Postgresql ver 8 (now 8.1 beta is out) should be better at cleaning up
after you.
Also remember to vacuum periodically otherwise you disk would be
clogged.
AND ALSO, do periodical "vacuum analyze" to get the queries to adapt to
statistical
changes in your relations for best performance. If you have a lot of
entries, think about
letting go of indices or droping them and rebuilding them after the
inserts, etc...
DB performance is often more improved by improving your queries and not
the block size
of your FS.
Regards,
tzahi.
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Michael Ben-Nes
> Sent: Monday, September 05, 2005 10:00 AM
> To: linux-il@linux.org.il
> Subject: sys / db performance & block size
>
>
> Hi
>
>
> Im setting up a Postgresql sql server on IBM x345, dual xeon,
> raid1 ( 2 disks ), raid10 ( 4 disks ).
>
> The db data will be stored on the RAID10 and will consist
> mostly of product table of 5 mil rows and keywords table with
> 60 mil rows.
>
>
> Postgres default block size is 8192.
>
> Reading over the net I understood that for the best
> performance the filesystem block size should be the same ( 8192 ).
>
>
> I initialized the partion with reiserfs filesystem with block
> size of 8192, but couldnt mount it. it kept saying:
>
> wrong fs type, bad option, bad superblock on
> /dev/VolGroup01/LV-1-0, or too many mounted file systems
>
> Tried it with LVM & without.
>
> Any Ideas ?
>
>
> I also interested in the list opinion about what should be
> the DB block size, FS block size, LVM PE Size & RAID Strip Size.
>
>
> Cheers
>
> --
> --------------------------
> Canaan Surfing Ltd.
> Internet Service Providers
> Ben-Nes Michael - Manager
> Tel: 972-4-6991122
> Cel: 972-52-8555757
> Fax: 972-4-6990098
> http://www.canaan.net.il
> --------------------------
>
> =================================================================
> To unsubscribe, send mail to [EMAIL PROTECTED]
> with the word "unsubscribe" in the message body, e.g., run
> the command echo unsubscribe | mail [EMAIL PROTECTED]
>
>
>
=================================================================
To unsubscribe, send mail to [EMAIL PROTECTED] with
the word "unsubscribe" in the message body, e.g., run the command
echo unsubscribe | mail [EMAIL PROTECTED]
