Thread: Tips performance under solaris

Hi,

I try postgresql V 7.1.2 under solaris 2.8 ( patch + the last version ) and i use directio implementation
for ufs . 
Improved UFS Direct I/O Concurrency (Quick I/O Equivalent) 
Solaris 8 1/01 update release allows concurrent read and write access to regular UFS files. As databases generally
pre-allocatefiles and seldom extend them thereafter, the effects of this enhancement are seen during the normal
databaseoperations. The improvement brings I/O-bound database performance on a UFS file system to about 90% of raw
partitionaccess speeds. 
 


When you mount an ufs partition,
just try this command in order to test directio:
mount -F ufs -o forcedirectio /dev/dsk/XXX /testdb

I try on the same machine 2 databases location :
One under partition with directio 
One under normal ufs partition

I use the same postgresql.conf and with pgbench i obtain
this resulats:

Pgbench -c 4 -v -t 100 testdb ( directio ufs )
tps = 13.425330
tps = 13.626090


Pgbench -c 4 -v -t 100 testdb ( ufs )
tps = 30.052012
tps = 30.630632

If you interest with directio try this links :

http://gecitsolutions.systemnews.com/system-news/jobdir/submitted/2001.03/3076/3076.html
http://www.idg.net/crd_solaris_452714_102.html


Cheers,

PEJAC Pascal

> Hi,
> 
> I try postgresql V 7.1.2 under solaris 2.8 ( patch + the last version ) and i use directio implementation
> for ufs . 
> Improved UFS Direct I/O Concurrency (Quick I/O Equivalent) 
> Solaris 8 1/01 update release allows concurrent read and write access to regular UFS files. As databases generally
pre-allocatefiles and seldom extend them thereafter, the effects of this enhancement are seen during the normal
databaseoperations. The improvement brings I/O-bound database performance on a UFS file system to about 90% of raw
partitionaccess speeds. 
 
> 
> 
>  
> When you mount an ufs partition,
> just try this command in order to test directio:
> mount -F ufs -o forcedirectio /dev/dsk/XXX /testdb
> 
> I try on the same machine 2 databases location :
> One under partition with directio 
> One under normal ufs partition
> 
> I use the same postgresql.conf and with pgbench i obtain
> this resulats:
> 
> Pgbench -c 4 -v -t 100 testdb ( directio ufs )
> tps = 13.425330
> tps = 13.626090
> 
> 
> Pgbench -c 4 -v -t 100 testdb ( ufs )
> tps = 30.052012
> tps = 30.630632
> 
> If you interest with directio try this links :
> 
> http://gecitsolutions.systemnews.com/system-news/jobdir/submitted/2001.03/3076/3076.html
> http://www.idg.net/crd_solaris_452714_102.html

I looked around and found that directio is:
O_DIRECT
           If set, all reads and writes on the resulting file descriptor will           be performed directly to or
fromthe user program buffer, provided           appropriate size and alignment restrictions are met.  Refer to the
    F_SETFL and F_DIOINFO commands in the fcntl(2) manual entry for           information about how to determine the
alignmentconstraints.           O_DIRECT is a Silicon Graphics extension and is only supported on           local EFS
filesystems.
 

So it does I/O directly from the user buffer to disk, bypassing the
system cache.  I am not sure if that is a good idea because you are not
using the system buffer cache nor is it allowing writes to be re-ordered
for optimial performance.  It does prevent copying the buffer into
kernel space, which I suppose is the major advantage for that feature.

I see discussion at:
http://groups.google.com/groups?q=solaris+direct+ufs&hl=en&safe=off&rnum=1&ic=1&selm=Dy1sx9.378%40baerlap.north.de

and

http://groups.google.com/groups?q=solaris+direct+ufs&hl=en&safe=off&rnum=2&ic=1&selm=0cosks09u834jipekdh4r9sr8tb17liokj%404ax.com

Specifically, the users say that sometimes it makes Oracle slower too. 
You might try increasing the number of PostgreSQL shared buffers and see
if you can increase that enough so this option is a win.


--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> So it does I/O directly from the user buffer to disk, bypassing the
> system cache.  I am not sure if that is a good idea because you are not
> using the system buffer cache nor is it allowing writes to be re-ordered
> for optimial performance.

... and, more than likely, the user program is blocked for the whole
physical write operation, not just for long enough to memcpy the data
into a kernel buffer.  Given that info, I find it completely
unsurprising that this "feature" makes Postgres a lot slower.  It seems
that Sun's idea of what a database does has little connection to what
Postgres does.

It might possibly make sense to set this bit on WAL writes, but not on
writes to data files.
        regards, tom lane