Date: Tue, 04 Apr 2006 17:12:04 +1200
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: bad performance on Solaris 10
In-reply-to: <1144111178.3723.80.camel@dell.home.lan>
To: Chris Mair <list@1006.org>
Cc: pgsql-performance@postgresql.org
Message-id: <44320024.60908@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7bit
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20051106)
References: <1144111178.3723.80.camel@dell.home.lan>

Chris Mair wrote:
> Hi,
> 
> I've got a somewhat puzzling performance problem here.
> 
> I'm trying to do a few tests with PostgreSQL 8.1.3 under Solaris
> (an OS I'm sort of a newbie in).
> 
> The machine is a X4100 and the OS is Solaris 10 1/06 fresh install
> according to manual. It's got two SAS disks in RAID 1, 4GB of RAM.
> 
> Now the problem is: this box is *much* slower than I expect.
> 
> I've got a libpg test program that happily inserts data
> using PQputCopyData().
> 
> It performs an order of magnitude worse than the same thing
> on a small Sun (Ultra20) running Linux. Or 4 times slower than
> an iBook (sic!) running MacOS X.
> 
> So, I've this very bad feeling that there is something basic
> I'm missing here.
> 
> Following are some stats:
> 
> "sync; dd; sync" show these disks write at 53 MB/s => good.
> 
> iostat 1 while my test is running says:
> 
>    tty        sd0           sd1           sd2           sd5
> cpu
>  tin tout kps tps serv  kps tps serv  kps tps serv  kps tps serv   us sy
> wt id
>    1   57   0   0    0    0   0    0    0   0    0  1809  23   70    0
> 1  0 99
>    0  235   0   0    0    0   0    0    0   0    0  2186 223   14    1
> 1  0 99
>    0   81   0   0    0    0   0    0    0   0    0  2488 251   13    1
> 1  0 98
>    0   81   0   0    0    0   0    0    0   0    0  2296 232   15    1
> 0  0 99
>    0   81   0   0    0    0   0    0    0   0    0  2416 166    9    1
> 0  0 98
>    0   81   0   0    0    0   0    0    0   0    0  2528 218   14    1
> 1  0 99
>    0   81   0   0    0    0   0    0    0   0    0  2272 223   15    1
> 0  0 99
> 
> If I interpret this correctly the disk writes at not more than 2.5
> MB/sec while the Opterons do nothing => this is bad.
> 
> I've tried both, a hand compile with gcc and the solarispackages
> from pgfoundry.org => same result.
> 
> Eons ago PCs had those "turbo" switches (it was never totally clear
> why they put them there in the first place, anyway). I've this bad
> feeling there's a secret "turbo" switch I can't spot hidden somewhere
> in Solaris :/
> 

I ran across something like this on a Solaris 8, RAID1 system, and 
switching off logging on filesystem containing postgres made a huge 
difference!

Now solaris 8 is ancient history, however see:

http://bugs.opensolaris.org/bugdatabase/view_bug.do?bug_id=6238533

Apparently there can still be issues with logging without forcedirectio 
(which is the default I think).

I suspect that making a *separate* filesystem for the pg_xlog directory 
and mounting that logging + forcedirectio would be a nice way to also 
get performance while keeping the advantages of logging + file 
buffercache for the *rest* of the postgres components.
Cheers

Mark