Re: strange performance regression between 7.4 and 8.1

On Thu, 1 Mar 2007, Alex Deucher wrote:

> On 3/1/07, Jeff Frost <jeff(at)frostconsultingllc(dot)com> wrote:
>> On Thu, 1 Mar 2007, Alex Deucher wrote:
>>
>> >> Vacuum? Analayze? default_statistics_target? How many shared_buffers?
>> >> effective_cache_size? work_mem?
>> >>
>> >
>> > I'm running the autovacuum process on the 8.1 server. vacuuming on
>> > the old server was done manually.
>> >
>> > default_statistics_target and effective_cache_size are set to the the
>> > defaults on both.
>> >
>> > postgres 7.4 server:
>> > # - Memory -
>> > shared_buffers = 82000 # 1000 min 16, at least
>> > max_connections*2, 8KB each
>> > sort_mem = 8000 # 1024 min 64, size in KB
>> > vacuum_mem = 32000 # 8192 min 1024, size in KB
>> > # - Free Space Map -
>> > #max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
>> > #max_fsm_relations = 1000 # min 100, ~50 bytes each
>> > # - Kernel Resource Usage -
>> > #max_files_per_process = 1000 # min 25
>> >
>> > postgres 8.1 server:
>> > # - Memory -
>> > shared_buffers = 100000 # min 16 or max_connections*2,
>> 8KB
>> > each
>> > temp_buffers = 2000 #1000 # min 100, 8KB each
>> > max_prepared_transactions = 100 #5 # can be 0 or more
>> > # note: increasing max_prepared_transactions costs ~600 bytes of shared
>> > memory
>> > # per transaction slot, plus lock space (see max_locks_per_transaction).
>> > work_mem = 10000 #1024 # min 64, size in KB
>> > maintenance_work_mem = 524288 #16384 # min 1024, size in KB
>> > #max_stack_depth = 2048 # min 100, size in KB
>> >
>> > I've also tried using the same settings from the old server on the new
>> > one; same performance issues.
>> >
>>
>> If this is a linux system, could you give us the output of the 'free'
>> command?
>
> total used free shared buffers cached
> Mem: 8059852 8042868 16984 0 228 7888648
> -/+ buffers/cache: 153992 7905860
> Swap: 15631224 2164 15629060

So, I would set effective_cache_size = 988232 (7905860/8).

>
>> 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.

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