pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedComments requested on IO performance : new db server
5+ messages / 3 participants
[nested] [flat]
* Comments requested on IO performance : new db server
@ 2012-03-09 11:15 Rory Campbell-Lange <rory@campbell-lange.net>
2012-03-09 16:11 ` Re: Comments requested on IO performance : new db server Merlin Moncure <mmoncure@gmail.com>
2012-03-10 10:51 ` Re: Comments requested on IO performance : new db server Rory Campbell-Lange <rory@campbell-lange.net>
0 siblings, 2 replies; 5+ messages in thread
From: Rory Campbell-Lange @ 2012-03-09 11:15 UTC (permalink / raw)
To: pgsql-performance
I've taken the liberty of reposting this message as my addendum to a
long thread that I started on the subject of adding a new db server to
our existing 4-year old workhorse got lost in discussion.
Our workload is several small databases totalling less than 40GB of disk
space. The proposed system has 48GB RAM, 2 * quad core E5620 @ 2.40GHz
and 4 WD Raptors behind an LSI SAS card. Our supplier has just run a set
of tests on the machine we intend to buy. The test rig had the following
setup:
LSI MegaRAID SAS 9260-8i
Firmware: 12.12.0-0090
Kernel: 2.6.39.4
Hard disks: 4x WD6000BLHX
Test done on 256GB volume
BS = blocksize in bytes
The test tool is fio. I'd be grateful to know if the results below are
considered acceptable. An ancillary question is whether a 4096 block
size is a good idea. I suppose we will be using XFS which I understand
has a default block size of 4096 bytes.
RAID 10
--------------------------------------
Read sequential
BS MB/s IOPs
512 0129.26 264730.80
1024 0229.75 235273.40
4096 0363.14 092965.50
16384 0475.02 030401.50
65536 0472.79 007564.65
131072 0428.15 003425.20
--------------------------------------
Write sequential
BS MB/s IOPs
512 0036.08 073908.00
1024 0065.61 067192.60
4096 0170.15 043560.40
16384 0219.80 014067.57
65536 0240.05 003840.91
131072 0243.96 001951.74
--------------------------------------
Random read
BS MB/s IOPs
512 0001.50 003077.20
1024 0002.91 002981.40
4096 0011.59 002968.30
16384 0044.50 002848.28
65536 0156.96 002511.41
131072 0170.65 001365.25
--------------------------------------
Random write
BS MB/s IOPs
512 0000.53 001103.60
1024 0001.15 001179.20
4096 0004.43 001135.30
16384 0017.61 001127.56
65536 0061.39 000982.39
131072 0079.27 000634.16
--------------------------------------
--
Rory Campbell-Lange
rory@campbell-lange.net
Campbell-Lange Workshop
www.campbell-lange.net
0207 6311 555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Comments requested on IO performance : new db server
2012-03-09 11:15 Comments requested on IO performance : new db server Rory Campbell-Lange <rory@campbell-lange.net>
@ 2012-03-09 16:11 ` Merlin Moncure <mmoncure@gmail.com>
2012-03-10 10:19 ` Re: Comments requested on IO performance : new db server Rory Campbell-Lange <rory@campbell-lange.net>
1 sibling, 1 reply; 5+ messages in thread
From: Merlin Moncure @ 2012-03-09 16:11 UTC (permalink / raw)
To: Rory Campbell-Lange <rory@campbell-lange.net>; +Cc: pgsql-performance
On Fri, Mar 9, 2012 at 5:15 AM, Rory Campbell-Lange
<rory@campbell-lange.net> wrote:
> I've taken the liberty of reposting this message as my addendum to a
> long thread that I started on the subject of adding a new db server to
> our existing 4-year old workhorse got lost in discussion.
>
> Our workload is several small databases totalling less than 40GB of disk
> space. The proposed system has 48GB RAM, 2 * quad core E5620 @ 2.40GHz
> and 4 WD Raptors behind an LSI SAS card. Our supplier has just run a set
> of tests on the machine we intend to buy. The test rig had the following
> setup:
>
> LSI MegaRAID SAS 9260-8i
> Firmware: 12.12.0-0090
> Kernel: 2.6.39.4
> Hard disks: 4x WD6000BLHX
> Test done on 256GB volume
> BS = blocksize in bytes
>
> The test tool is fio. I'd be grateful to know if the results below are
> considered acceptable. An ancillary question is whether a 4096 block
> size is a good idea. I suppose we will be using XFS which I understand
> has a default block size of 4096 bytes.
>
> RAID 10
> --------------------------------------
> Read sequential
>
> BS MB/s IOPs
> 512 0129.26 264730.80
> 1024 0229.75 235273.40
> 4096 0363.14 092965.50
> 16384 0475.02 030401.50
> 65536 0472.79 007564.65
> 131072 0428.15 003425.20
> --------------------------------------
> Write sequential
>
> BS MB/s IOPs
> 512 0036.08 073908.00
> 1024 0065.61 067192.60
> 4096 0170.15 043560.40
> 16384 0219.80 014067.57
> 65536 0240.05 003840.91
> 131072 0243.96 001951.74
> --------------------------------------
> Random read
>
> BS MB/s IOPs
> 512 0001.50 003077.20
> 1024 0002.91 002981.40
> 4096 0011.59 002968.30
> 16384 0044.50 002848.28
> 65536 0156.96 002511.41
> 131072 0170.65 001365.25
> --------------------------------------
> Random write
>
> BS MB/s IOPs
> 512 0000.53 001103.60
> 1024 0001.15 001179.20
> 4096 0004.43 001135.30
> 16384 0017.61 001127.56
> 65536 0061.39 000982.39
> 131072 0079.27 000634.16
> --------------------------------------
since your RAM is larger than the database size, read performance is
essentially a non-issue. your major gating factors are going to be
cpu bound queries and random writes -- 1000 IOPS essentially puts an
upper bound on your write TPS, especially if your writes are frequent
and randomly distributed, the case that is more or less simulated by
pgbench with large scaling factors.
Now, 1000 write tps is quite alot (3.6 mil transactions/hour) and
your workload will drive the hardware consideration.
merlin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Comments requested on IO performance : new db server
2012-03-09 11:15 Comments requested on IO performance : new db server Rory Campbell-Lange <rory@campbell-lange.net>
2012-03-09 16:11 ` Re: Comments requested on IO performance : new db server Merlin Moncure <mmoncure@gmail.com>
@ 2012-03-10 10:19 ` Rory Campbell-Lange <rory@campbell-lange.net>
0 siblings, 0 replies; 5+ messages in thread
From: Rory Campbell-Lange @ 2012-03-10 10:19 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
On 09/03/12, Merlin Moncure (mmoncure@gmail.com) wrote:
> On Fri, Mar 9, 2012 at 5:15 AM, Rory Campbell-Lange
> <rory@campbell-lange.net> wrote:
> > I've taken the liberty of reposting this message as my addendum to a
> > long thread that I started on the subject of adding a new db server to
> > our existing 4-year old workhorse got lost in discussion.
> >
> > Our workload is several small databases totalling less than 40GB of disk
> > space. The proposed system has 48GB RAM, 2 * quad core E5620 @ 2.40GHz
> > and 4 WD Raptors behind an LSI SAS card. Our supplier has just run a set
> > of tests on the machine we intend to buy. The test rig had the following
> > setup:
> >
> > LSI MegaRAID SAS 9260-8i
> > Firmware: 12.12.0-0090
> > Kernel: 2.6.39.4
> > Hard disks: 4x WD6000BLHX
> > Test done on 256GB volume
> > BS = blocksize in bytes
> >
> > The test tool is fio. I'd be grateful to know if the results below are
> > considered acceptable. An ancillary question is whether a 4096 block
> > size is a good idea. I suppose we will be using XFS which I understand
> > has a default block size of 4096 bytes.
> >
> > RAID 10
> > --------------------------------------
...
> > --------------------------------------
> > Random write
> >
> > BS MB/s IOPs
> > 512 0000.53 001103.60
> > 1024 0001.15 001179.20
> > 4096 0004.43 001135.30
> > 16384 0017.61 001127.56
> > 65536 0061.39 000982.39
> > 131072 0079.27 000634.16
> > --------------------------------------
>
> since your RAM is larger than the database size, read performance is
> essentially a non-issue. your major gating factors are going to be
> cpu bound queries and random writes -- 1000 IOPS essentially puts an
> upper bound on your write TPS, especially if your writes are frequent
> and randomly distributed, the case that is more or less simulated by
> pgbench with large scaling factors.
>
> Now, 1000 write tps is quite alot (3.6 mil transactions/hour) and
> your workload will drive the hardware consideration.
Thanks for your comments, Merlin. With regard to the "gating factors" I
believe the following is pertinent:
CPU
My current server has 2 * quad Xeon E5420 @ 2.50GHz. The server
occasionally reaches 20% sutained utilisation according to sar.
This cpu has a "passmark" of 7,730.
http://www.cpubenchmark.net/cpu_lookup.php?cpu=[Dual+CPU]+Intel+Xeon+E5420+%40+2.50GHz
My proposed CPU is an E5620 @ 2.40GHz with CPU "passmark" of 9,620
http://www.cpubenchmark.net/cpu_lookup.php?cpu=[Dual+CPU]+Intel+Xeon+E5620+%40+2.40GHz
Since the workload will be very similar I'm hoping for about 20% better
CPU performance from the new server, which should drop max CPU load by
5% or so.
Random Writes
I'll have to test this. My current server (R10 4*15K SCSI) produced the
following pgbench stats while running its normal workload:
-c -t TPS
5 20000 446
10 10000 542
20 5000 601
30 3333 647
I'd be grateful to know what parameters I should use for a "large
scaling factor" pgbench test.
Many thanks
Rory
--
Rory Campbell-Lange
rory@campbell-lange.net
Campbell-Lange Workshop
www.campbell-lange.net
0207 6311 555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Comments requested on IO performance : new db server
2012-03-09 11:15 Comments requested on IO performance : new db server Rory Campbell-Lange <rory@campbell-lange.net>
@ 2012-03-10 10:51 ` Rory Campbell-Lange <rory@campbell-lange.net>
2012-03-10 15:12 ` Re: Comments requested on IO performance : new db server Tomas Vondra <tv@fuzzy.cz>
1 sibling, 1 reply; 5+ messages in thread
From: Rory Campbell-Lange @ 2012-03-10 10:51 UTC (permalink / raw)
To: pgsql-performance
Is a block size of 4096 a good idea both for the filesystem and
postgresql? The analysis here:
http://www.fuzzy.cz/en/articles/benchmark-results-hdd-read-write-pgbench/
appears to suggest that at least for database block sizes of 4096
read/write performance is much higher than for smaller block sizes.
Rory
On 09/03/12, Rory Campbell-Lange (rory@campbell-lange.net) wrote:
> ...An ancillary question is whether a 4096 block size is a good idea.
> I suppose we will be using XFS which I understand has a default block
> size of 4096 bytes.
>
> RAID 10
> --------------------------------------
> Read sequential
>
> BS MB/s IOPs
> 512 0129.26 264730.80
> 1024 0229.75 235273.40
> 4096 0363.14 092965.50
> 16384 0475.02 030401.50
> 65536 0472.79 007564.65
> 131072 0428.15 003425.20
> --------------------------------------
> Write sequential
>
> BS MB/s IOPs
> 512 0036.08 073908.00
> 1024 0065.61 067192.60
> 4096 0170.15 043560.40
> 16384 0219.80 014067.57
> 65536 0240.05 003840.91
> 131072 0243.96 001951.74
> --------------------------------------
> Random read
>
> BS MB/s IOPs
> 512 0001.50 003077.20
> 1024 0002.91 002981.40
> 4096 0011.59 002968.30
> 16384 0044.50 002848.28
> 65536 0156.96 002511.41
> 131072 0170.65 001365.25
> --------------------------------------
> Random write
>
> BS MB/s IOPs
> 512 0000.53 001103.60
> 1024 0001.15 001179.20
> 4096 0004.43 001135.30
> 16384 0017.61 001127.56
> 65536 0061.39 000982.39
> 131072 0079.27 000634.16
> --------------------------------------
--
Rory Campbell-Lange
rory@campbell-lange.net
Campbell-Lange Workshop
www.campbell-lange.net
0207 6311 555
3 Tottenham Street London W1T 2AF
Registered in England No. 04551928
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Comments requested on IO performance : new db server
2012-03-09 11:15 Comments requested on IO performance : new db server Rory Campbell-Lange <rory@campbell-lange.net>
2012-03-10 10:51 ` Re: Comments requested on IO performance : new db server Rory Campbell-Lange <rory@campbell-lange.net>
@ 2012-03-10 15:12 ` Tomas Vondra <tv@fuzzy.cz>
0 siblings, 0 replies; 5+ messages in thread
From: Tomas Vondra @ 2012-03-10 15:12 UTC (permalink / raw)
To: pgsql-performance
On 10.3.2012 11:51, Rory Campbell-Lange wrote:
> Is a block size of 4096 a good idea both for the filesystem and
> postgresql? The analysis here:
> http://www.fuzzy.cz/en/articles/benchmark-results-hdd-read-write-pgbench/
> appears to suggest that at least for database block sizes of 4096
> read/write performance is much higher than for smaller block sizes.
Hi,
interpreting those results is a bit tricky for several reasons. First,
those are 'average results' for all filesystems (and the behavior of
filesystems may vary significantly). I'd recommend checking results for
the filesystem you're going to use (http://www.fuzzy.cz/bench)
Second, the article discusses just TPC-B (OLTP-like) workload results.
It's quite probable your workload is going to mix that with other
workload types (e.g. DSS/DWH). And that's exactly where larger block
sizes are better.
To me, 8kB seems like a good compromise. Don't use other block sizes
unless you actually test the benefits for your workload.
Tomas
>
> Rory
>
> On 09/03/12, Rory Campbell-Lange (rory@campbell-lange.net) wrote:
>> ...An ancillary question is whether a 4096 block size is a good idea.
>> I suppose we will be using XFS which I understand has a default block
>> size of 4096 bytes.
>>
>> RAID 10
>> --------------------------------------
>> Read sequential
>>
>> BS MB/s IOPs
>> 512 0129.26 264730.80
>> 1024 0229.75 235273.40
>> 4096 0363.14 092965.50
>> 16384 0475.02 030401.50
>> 65536 0472.79 007564.65
>> 131072 0428.15 003425.20
>> --------------------------------------
>> Write sequential
>>
>> BS MB/s IOPs
>> 512 0036.08 073908.00
>> 1024 0065.61 067192.60
>> 4096 0170.15 043560.40
>> 16384 0219.80 014067.57
>> 65536 0240.05 003840.91
>> 131072 0243.96 001951.74
>> --------------------------------------
>> Random read
>>
>> BS MB/s IOPs
>> 512 0001.50 003077.20
>> 1024 0002.91 002981.40
>> 4096 0011.59 002968.30
>> 16384 0044.50 002848.28
>> 65536 0156.96 002511.41
>> 131072 0170.65 001365.25
>> --------------------------------------
>> Random write
>>
>> BS MB/s IOPs
>> 512 0000.53 001103.60
>> 1024 0001.15 001179.20
>> 4096 0004.43 001135.30
>> 16384 0017.61 001127.56
>> 65536 0061.39 000982.39
>> 131072 0079.27 000634.16
>> --------------------------------------
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-03-10 15:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-09 11:15 Comments requested on IO performance : new db server Rory Campbell-Lange <rory@campbell-lange.net>
2012-03-09 16:11 ` Merlin Moncure <mmoncure@gmail.com>
2012-03-10 10:19 ` Rory Campbell-Lange <rory@campbell-lange.net>
2012-03-10 10:51 ` Rory Campbell-Lange <rory@campbell-lange.net>
2012-03-10 15:12 ` Tomas Vondra <tv@fuzzy.cz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox