Message-ID: <430C9DA6.2080801@commandprompt.com>
Date: Wed, 24 Aug 2005 09:17:42 -0700
From: "Joshua D. Drake" <jd@commandprompt.com>
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050727)
MIME-Version: 1.0
To: Alexandre Barros <alexandre@vectorx.com.br>
Cc: postgres performance <pgsql-performance@postgresql.org>
Subject: Re: performance drop on RAID5
References: <430C8779.9080500@vectorx.com.br>
In-Reply-To: <430C8779.9080500@vectorx.com.br>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Alexandre Barros wrote:

> Hello,
> i have a pg-8.0.3 running on Linux  kernel  2.6.8,  CPU  Sempron 
> 2600+, 1Gb RAM on IDE HD ( which could be called a "heavy desktop" ), 
> measuring this performance with pgbench ( found on /contrib ) it gave 
> me an average ( after several runs ) of 170 transactions per second;

That is going to be because IDE drives LIE about write times because of 
the large cache.

> for the sake of experimentation ( actually, i'm scared this IDE drive 
> could fail at any time, hence i'm looking for an alternative, more 
> "robust", machine ), i've installed on an aging Compaq Proliant server 
> ( freshly compiled SMP kernel 2.6.12.5  with preemption ), dual 
> Pentium III Xeon 500Mhz, 512Mb RAM, (older) SCSI-2 80pin drives, and 
> re-tested, when the database was on a single SCSI drive, pgbench gave 
> me an average of 90 transactions per second, but, and that scared me 
> most, when the database was on a RAID-5 array ( four 9Gb disks, using 
> linux software RAID mdadm and LVM2, with the default filesystem 
> cluster size of 32Kb ), the performance dropped to about 55 
> transactions per second.


That seems more reasonable and probably truthful. I would be curious 
what type of performance you would get with the exact same
setup EXCEPT remove LVM2. Just have the software RAID. In fact, since 
you have 4 drives you could do RAID 10.

>
> i wouldn't be so stunned if the newer machine was ( say ) twice faster 
> than the older server, but over three times faster is disturbing.
>
> the postgresql.conf of both machines is here:
>
> max_connections = 50
> shared_buffers = 1000           # min 16, at least max_connections*2, 
> 8KB each

You should look at the annotated conf:

http://www.powerpostgresql.com/Downloads/annotated_conf_80.html

Sincerely,

Joshua D. Drake


> debug_print_parse = false
> debug_print_rewritten = false
> debug_print_plan = false
> debug_pretty_print = false
> log_statement = 'all'
> log_parser_stats        = false
> log_planner_stats       = false
> log_executor_stats      = false
> log_statement_stats     = false
> lc_messages = 'en_US'           # locale for system error message strings
> lc_monetary = 'en_US'           # locale for monetary formatting
> lc_numeric = 'en_US'            # locale for number formatting
> lc_time = 'en_US'               # locale for time formatting
>
> many thanks in advance !
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq