Re: strange performance regression between 7.4 and 8.1

On Thu, 1 Mar 2007, Alex Deucher wrote:

>> >> Postgresql might be choosing a bad plan because your
>> effective_cache_size
>> >> is
>> >> way off (it's the default now right?). Also, what was the block
>> read/write
>> >
>> > yes it's set to the default.
>> >
>> >> speed of the SAN from your bonnie tests? Probably want to tune
>> >> random_page_cost as well if it's also at the default.
>> >>
>> >
>> > ------Sequential Output------ --Sequential Input-
>> > --Random-
>> > -Per Chr- --Block-- -Rewrite- -Per Chr- --Block--
>> > --Seeks--
>> > Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP
>> /sec
>> > %CP
>> > luna12-san 16000M 58896 91 62931 9 35870 5 54869 82 145504 13
>> 397.7
>> > 0
>> >
>>
>> So, you're getting 62MB/s writes and 145MB/s reads. Just FYI, that write
>> speed is about the same as my single SATA drive write speed on my
>> workstation,
>> so not that great. The read speed is decent, though and with that sort of
>> read performance, you might want to lower random_page_cost to something
>> like
>> 2.5 or 2 so the planner will tend to prefer index scans.
>>
>
> Right, but the old box was getting ~45MBps on both reads and writes,
> so it's an improvement for me :) Thanks for the advice, I'll let you
> know how it goes.

Do you think that is because you have a different interface between you and
the SAN? ~45MBps is pretty slow - your average 7200RPM ATA133 drive can do
that and costs quite a bit less than a SAN.

Is the SAN being shared between the database servers and other servers? Maybe
it was just random timing that gave you the poor write performance on the old
server which might be also yielding occassional poor performance on the new
one.

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954