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@frostconsultingllc.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@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954