pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuad processor options - summary
8+ messages / 5 participants
[nested] [flat]
* Quad processor options - summary
@ 2004-05-12 22:40 Bjoern Metzdorf <bm@turtle-entertainment.de>
0 siblings, 1 reply; 8+ messages in thread
From: Bjoern Metzdorf @ 2004-05-12 22:40 UTC (permalink / raw)
To: pgsql-performance; pgsql-admin@postgresql.org
Hi,
at first, many thanks for your valuable replies. On my quest for the
ultimate hardware platform I'll try to summarize the things I learned.
-------------------------------------------------------------
This is our current setup:
Hardware:
Dual Xeon DP 2.4 on a TYAN S2722-533 with HT enabled
3 GB Ram (2 x 1 GB + 2 x 512 MB)
Mylex Extremeraid Controller U160 running RAID 10 with 4 x 18 GB SCSI
10K RPM, no other drives involved (system, pgdata and wal are all on the
same volume).
Software:
Debian 3.0 Woody
Postgresql 7.4.1 (selfcompiled, no special optimizations)
Kernel 2.4.22 + fixes
Database specs:
Size of a gzipped -9 full dump is roughly 1 gb
70-80% selects, 20-30% updates (roughly estimated)
up to 700-800 connections during peak times
kernel.shmall = 805306368
kernel.shmmax = 805306368
max_connections = 900
shared_buffers = 20000
sort_mem = 16384
checkpoint_segments = 6
statistics collector is enabled (for pg_autovacuum)
Loads:
We are experiencing average CPU loads of up to 70% during peak hours. As
Paul Tuckfield correctly pointed out, my vmstat output didn't support
this. This output was not taken during peak times, it was freshly
grabbed when I wrote my initial mail. It resembles perhaps 50-60% peak
time load (30% cpu usage). iostat does not give results about disk
usage, I don't know exactly why, the blk_read/wrtn columns are just
empty. (Perhaps due to the Mylex rd driver, I don't know).
-------------------------------------------------------------
Suggestions and solutions given:
Anjan Dave reported, that he is pretty confident with his Quad Xeon
setups, which will cost less than $20K at Dell with a reasonable
hardware setup. ( Dell 6650 with 2.0GHz/1MB cache/8GB Memory, 5 internal
drives (4 in RAID 10, 1 spare) on U320, 128MB cache on the PERC controller)
Scott Marlowe pointed out, that one should consider more than 4 drives
(6 to 8, 10K rpm is enough, 15K is rip-off) for a Raid 10 setup, because
that can boost performance quite a lot. One should also be using a
battery backed raid controller. Scott has good experiences with the LSI
Megaraid single channel controller, which is reasonably priced at ~
$500. He also stated, that 20-30% writes on a database is quite a lot.
Next Rob Sell told us about his research on more-than-2-way Intel based
systems. The memory bandwidth on the xeon platform is always shared
between the cpus. While a 2way xeon may perform quite well, a 4way
system will be suffering due to the reduced memory bandwith available
for each processor.
J. Andrew Roberts supports this. He said that 4way opteron systems scale
much better than a 4way xeon system. Scaling limits begin at 6-8 cpus on
the opteron platform. He also says that a fully equipped dual channel
LSI Megaraid 320 with 256MB cache ram will be less that $1K. A complete
4way opteron system will be at $10K-$12K.
Paul Tuckfield then gave the suggestion to bump up my shared_buffers.
With a 3GB memory system, I could happily be using 1GB for shared
buffers (125000). This was questioned by Andrew McMillian, Manfred
Kolzar and Halford Dace, who say that common tuning advices limit
reasonable settings to 10000-20000 shared buffers, because the OS is
better at caching than the database.
-------------------------------------------------------------
Conclusion:
After having read some comparisons between n-way xeon and opteron systems:
http://www.anandtech.com/IT/showdoc.html?i=1982
http://www.aceshardware.com/read.jsp?id=60000275
I was given the impression, that an opteron system is the way to go.
This is what I am considering the ultimate platform for postgresql:
Hardware:
Tyan Thunder K8QS board
2-4 x Opteron 848 in NUMA mode
4-8 GB RAM (DDR400 ECC Registered 1 GB modules, 2 for each processor)
LSI Megaraid 320-2 with 256 MB cache ram and battery backup
6 x 36GB SCSI 10K drives + 1 spare running in RAID 10, split over both
channels (3 + 4) for pgdata including indexes and wal.
2 x 80 GB S-ATA IDE for system, running linux software raid 1 or
available onboard hardware raid (perhaps also 2 x 36 GB SCSI)
Software:
Debian Woody in amd64 biarch mode, or perhaps Redhat/SuSE Enterprise
64bit distributions.
Kernel 2.6
Postgres 7.4.2 in 64bit mode
shared_buffers = 20000
a bumbed up effective_cache_size
Now the only problem left (besides my budget) is the availability of
such a system.
I have found some vendors which ship similar systems, so I will have to
talk to them about my dream configuration. I will not self build this
system, there are too many obstacles.
I expect this system to come out on about 12-15K Euro. Very optimistic,
I know :)
These are the vendors I found up to now:
http://www.appro.com/product/server_4144h.asp
http://www.appro.com/product/server_4145h.asp
http://www.pyramid.de/d/builttosuit/server/4opteron.shtml
http://www.rainbow-it.co.uk/productslist.aspx?CategoryID=4&selection=2
http://www.quadopteron.com/
They all seem to sell more or less the same system. I found also some
other vendors which built systems on celestica or amd boards, but they
are way too expensive.
Buying such a machine is worth some good thoughts. If budget is a limit
and such a machine might not be maxed out during the next few months, it
would make more sense to go for a slightly slower system and an upgrade
when more power is needed.
Thanks again for all your replies. I hope to have given a somehow clear
summary.
Regards,
Bjoern
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [PERFORM] Quad processor options - summary
@ 2004-05-13 21:02 James Thornton <james@jamesthornton.com>
parent: Bjoern Metzdorf <bm@turtle-entertainment.de>
0 siblings, 1 reply; 8+ messages in thread
From: James Thornton @ 2004-05-13 21:02 UTC (permalink / raw)
To: Bjoern Metzdorf <bm@turtle-entertainment.de>; +Cc: pgsql-performance; pgsql-admin@postgresql.org
Bjoern Metzdorf wrote:
> Hi,
>
> at first, many thanks for your valuable replies. On my quest for the
> ultimate hardware platform I'll try to summarize the things I learned.
>
> -------------------------------------------------------------
> This is what I am considering the ultimate platform for postgresql:
>
> Hardware:
> Tyan Thunder K8QS board
> 2-4 x Opteron 848 in NUMA mode
> 4-8 GB RAM (DDR400 ECC Registered 1 GB modules, 2 for each processor)
> LSI Megaraid 320-2 with 256 MB cache ram and battery backup
> 6 x 36GB SCSI 10K drives + 1 spare running in RAID 10, split over both
> channels (3 + 4) for pgdata including indexes and wal.
You might also consider configuring the Postgres data drives for a RAID
10 SAME configuration as described in the Oracle paper "Optimal Storage
Configuration Made Easy"
(http://otn.oracle.com/deploy/availability/pdf/oow2000_same.pdf). Has
anyone delved into this before?
--
James Thornton
______________________________________________________
Internet Business Consultant, http://jamesthornton.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [PERFORM] Quad processor options - summary
@ 2004-05-13 21:53 Bjoern Metzdorf <bm@turtle-entertainment.de>
parent: James Thornton <james@jamesthornton.com>
0 siblings, 4 replies; 8+ messages in thread
From: Bjoern Metzdorf @ 2004-05-13 21:53 UTC (permalink / raw)
To: James Thornton <james@jamesthornton.com>; +Cc: pgsql-performance; pgsql-admin@postgresql.org
James Thornton wrote:
>> This is what I am considering the ultimate platform for postgresql:
>>
>> Hardware:
>> Tyan Thunder K8QS board
>> 2-4 x Opteron 848 in NUMA mode
>> 4-8 GB RAM (DDR400 ECC Registered 1 GB modules, 2 for each processor)
>> LSI Megaraid 320-2 with 256 MB cache ram and battery backup
>> 6 x 36GB SCSI 10K drives + 1 spare running in RAID 10, split over both
>> channels (3 + 4) for pgdata including indexes and wal.
>
> You might also consider configuring the Postgres data drives for a RAID
> 10 SAME configuration as described in the Oracle paper "Optimal Storage
> Configuration Made Easy"
> (http://otn.oracle.com/deploy/availability/pdf/oow2000_same.pdf). Has
> anyone delved into this before?
Ok, if I understand it correctly the papers recommends the following:
1. Get many drives and stripe them into a RAID0 with a stripe width of
1MB. I am not quite sure if this stripe width is to be controlled at the
application level (does postgres support this?) or if e.g. the "chunk
size" of the linux software driver is meant. Normally a chunk size of
4KB is recommended, so 1MB sounds fairly large.
2. Mirror your RAID0 and get a RAID10.
3. Use primarily the fast, outer regions of your disks. In practice this
might be achieved by putting only half of the disk (the outer half) into
your stripe set. E.g. put only the outer 18GB of your 36GB disks into
the stripe set. Btw, is it common for all drives that the outer region
is on the higher block numbers? Or is it sometimes on the lower block
numbers?
4. Subset data by partition, not disk. If you have 8 disks, then don't
take a 4 disk RAID10 for data and the other one for log or indexes, but
make a global 8 drive RAID10 and have it partitioned the way that data
and log + indexes are located on all drives.
They say, which is very interesting, as it is really contrary to what is
normally recommended, that it is good or better to have one big stripe
set over all disks available, than to put log + indexes on a separated
stripe set. Having one big stripe set means that the speed of this big
stripe set is available to all data. In practice this setup is as fast
as or even faster than the "old" approach.
----------------------------------------------------------------
Bottom line for a normal, less than 10 disk setup:
Get many disks (8 + spare), create a RAID0 with 4 disks and mirror it to
the other 4 disks for a RAID10. Make sure to create the RAID on the
outer half of the disks (setup may depend on the disk model and raid
controller used), leaving the inner half empty.
Use a logical volume manager (LVM), which always helps when adding disk
space, and create 2 partitions on your RAID10. One for data and one for
log + indexes. This should look like this:
----- ----- ----- -----
| 1 | | 1 | | 1 | | 1 |
----- ----- ----- ----- <- outer, faster half of the disk
| 2 | | 2 | | 2 | | 2 | part of the RAID10
----- ----- ----- -----
| | | | | | | |
| | | | | | | | <- inner, slower half of the disk
| | | | | | | | not used at all
----- ----- ----- -----
Partition 1 for data, partition 2 for log + indexes. All mirrored to the
other 4 disks not shown.
If you take 36GB disks, this should end up like this:
RAID10 has size of 36 / 2 * 4 = 72GB
Partition 1 is 36 GB
Partition 2 is 36 GB
If 36GB is not enough for your pgdata set, you might consider moving to
72GB disks, or (even better) make a 16 drive RAID10 out of 36GB disks,
which both will end up in a size of 72GB for your data (but the 16 drive
version will be faster).
Any comments?
Regards,
Bjoern
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [PERFORM] Quad processor options - summary
@ 2004-05-13 22:50 James Thornton <james@jamesthornton.com>
parent: Bjoern Metzdorf <bm@turtle-entertainment.de>
3 siblings, 0 replies; 8+ messages in thread
From: James Thornton @ 2004-05-13 22:50 UTC (permalink / raw)
To: Bjoern Metzdorf <bm@turtle-entertainment.de>; +Cc: pgsql-performance; pgsql-admin@postgresql.org
Bjoern Metzdorf wrote:
>> You might also consider configuring the Postgres data drives for a
>> RAID 10 SAME configuration as described in the Oracle paper "Optimal
>> Storage Configuration Made Easy"
>> (http://otn.oracle.com/deploy/availability/pdf/oow2000_same.pdf). Has
>> anyone delved into this before?
>
> Ok, if I understand it correctly the papers recommends the following:
>
> 1. Get many drives and stripe them into a RAID0 with a stripe width of
> 1MB. I am not quite sure if this stripe width is to be controlled at the
> application level (does postgres support this?) or if e.g. the "chunk
> size" of the linux software driver is meant. Normally a chunk size of
> 4KB is recommended, so 1MB sounds fairly large.
>
> 2. Mirror your RAID0 and get a RAID10.
Don't use RAID 0+1 -- use RAID 1+0 instead. Performance is the same, but
if a disk fails in a RAID 0+1 configuration, you are left with a RAID 0
array. In a RAID 1+0 configuration, multiple disks can fail.
A few weeks ago I called LSI asking about the Dell PERC4-Di card, which
is actually an LSI Megaraid 320-2. Dell's documentation said that its
support for RAID 10 was in the form of RAID-1 concatenated, but LSI said
that this is incorrect and that it supports RAID 10 proper.
> 3. Use primarily the fast, outer regions of your disks. In practice this
> might be achieved by putting only half of the disk (the outer half) into
> your stripe set. E.g. put only the outer 18GB of your 36GB disks into
> the stripe set.
You can still use the inner-half of the drives, just relegate it to
less-frequently accessed data.
You also need to consider the filesystem.
SGI and IBM did a detailed study on Linux filesystem performance, which
included XFS, ext2, ext3 (various modes), ReiserFS, and JFS, and the
results are presented in a paper entitled "Filesystem Performance and
Scalability in Linux 2.4.17"
(http://oss.sgi.com/projects/xfs/papers/filesystem-perf-tm.pdf).
The scaling and load are key factors when selecting a filesystem. Since
Postgres data is stored in large files, ReiserFS is not the ideal choice
since it has been optimized for small files. XFS is probably the best
choice for a database server running on a quad processor box.
However, Dr. Bert Scalzo of Quest argues that general file system
benchmarks aren't ideal for benchmarking a filesystem for a database
server. In a paper entitled "Tuning an Oracle8i Database running Linux"
(http://otn.oracle.com/oramag/webcolumns/2002/techarticles/scalzo_linux02.html),
he says, "The trouble with these tests-for example, Bonnie, Bonnie++,
Dbench, Iobench, Iozone, Mongo, and Postmark-is that they are basic file
system throughput tests, so their results generally do not pertain in
any meaningful fashion to the way relational database systems access
data files." Instead he suggests using these two well-known and widely
accepted database benchmarks:
* AS3AP: a scalable, portable ANSI SQL relational database benchmark
that provides a comprehensive set of tests of database-processing power;
has built-in scalability and portability for testing a broad range of
systems; minimizes human effort in implementing and running benchmark
tests; and provides a uniform, metric, straightforward interpretation of
the results.
* TPC-C: an online transaction processing (OLTP) benchmark that involves
a mix of five concurrent transactions of various types and either
executes completely online or queries for deferred execution. The
database comprises nine types of tables, having a wide range of record
and population sizes. This benchmark measures the number of transactions
per second.
In the paper, Scalzo benchmarks ext2, ext3, ReiserFS, JFS, but not XFS.
Surprisingly ext3 won, but Scalzo didn't address scaling/load. The
results are surprising because most think ext3 is just ext2 with
journaling, thus having extra overhead from journaling.
If you read papers on ext3, you'll discover that has some optimizations
that reduce disk head movement. For example, Daniel Robbins' "Advanced
filesystem implementor's guide, Part 7: Introducing ext3"
(http://www-106.ibm.com/developerworks/library/l-fs7/) says:
"The approach that the [ext3 Journaling Block Device layer API] uses is
called physical journaling, which means that the JBD uses complete
physical blocks as the underlying currency for implementing the
journal...the use of full blocks allows ext3 to perform some additional
optimizations, such as "squishing" multiple pending IO operations within
a single block into the same in-memory data structure. This, in turn,
allows ext3 to write these multiple changes to disk in a single write
operation, rather than many. In addition, because the literal block data
is stored in memory, little or no massaging of the in-memory data is
required before writing it to disk, greatly reducing CPU overhead."
I suspect that less writes may be the key factor in ext3 winning
Scalzo's DB benchmark. But as I said, Scalzo didn't benchmark XFS and he
didn't address scaling.
XFS has a feature called delayed allocation that reduces IO
(http://www-106.ibm.com/developerworks/library/l-fs9/), and it scales
much better than ext3 so while I haven't tested it, I suspect that it
may be the ideal choice for large Linux DB servers:
"XFS handles allocation by breaking it into a two-step process. First,
when XFS receives new data to be written, it records the pending
transaction in RAM and simply reserves an appropriate amount of space on
the underlying filesystem. However, while XFS reserves space for the new
data, it doesn't decide what filesystem blocks will be used to store the
data, at least not yet. XFS procrastinates, delaying this decision to
the last possible moment, right before this data is actually written to
disk.
By delaying allocation, XFS gains many opportunities to optimize write
performance. When it comes time to write the data to disk, XFS can now
allocate free space intelligently, in a way that optimizes filesystem
performance. In particular, if a bunch of new data is being appended to
a single file, XFS can allocate a single, contiguous region on disk to
store this data. If XFS hadn't delayed its allocation decision, it may
have unknowingly written the data into multiple non-contiguous chunks,
reducing write performance significantly. But, because XFS delayed its
allocation decision, it was able to write the data in one fell swoop,
improving write performance as well as reducing overall filesystem
fragmentation.
Delayed allocation also has another performance benefit. In situations
where many short-lived temporary files are created, XFS may never need
to write these files to disk at all. Since no blocks are ever allocated,
there's no need to deallocate any blocks, and the underlying filesystem
metadata doesn't even get touched."
For further study, I have compiled a list of Linux filesystem resources
at: http://jamesthornton.com/hotlist/linux-filesystems/.
--
James Thornton
______________________________________________________
Internet Business Consultant, http://jamesthornton.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [PERFORM] Quad processor options - summary
@ 2004-05-13 22:59 Hadley Willan <hadley.willan@deeperdesign.co.nz>
parent: Bjoern Metzdorf <bm@turtle-entertainment.de>
3 siblings, 1 reply; 8+ messages in thread
From: Hadley Willan @ 2004-05-13 22:59 UTC (permalink / raw)
To: Bjoern Metzdorf <bm@turtle-entertainment.de>; +Cc: James Thornton <james@jamesthornton.com>; pgsql-performance; pgsql-admin@postgresql.org
I see you've got an LSI Megaraid card with oodles of Cache. However,
don't underestimate the power of the software RAID implementation that
Red Hat Linux comes with.
We're using RHE 2.1 and I can recommend Red Hat Enterprise Linux if you
want an excellent implementation of software RAID. In fact we have
found the software implementation more flexible than that of some
expensive hardware controllers. In addition there are also tools to
enhance the base implementation even further, making setup and
maintenance even easier. An advantage of the software implementation is
being able to RAID by partition, not necessarily entire disks.
To answer question 1, if you use software raid the chunk size is part of
the /etc/raidtab file that is used on initial container creation. 4KB is
the standard and a LARGE chunk size of 1MB may affect performance if
you're not writing down to blocks in that size continuously. If you
make it to big and you're constantly needing to write out smaller chunks
of information, then you will find the disk "always" working and would
be an inefficient use of the blocks. There is some free info around
about calculating the ideal chunk size. Looking for "Calculating chunk
size for RAID" through google.
In the software implementation, after setup the raidtab is uncessary as
the superblocks of the disks now contain their relevant information.
As for the application knowing any of this, no, the application layers
are entirely unaware of the lower implementation. They simply function
as normal by writing to directories that are now mounted a different
way. The kernel takes care of the underlying RAID writes and syncs.
3 is easy to implement with software raid under linux. You simply
partition the drive like normal, mark the partitions you want to "raid"
as 'fd' 'linux raid autodetect', then configure the /etc/raidtab and do
a mkraid /dev/mdxx where mdxx is the matching partition for the raid
setup. You can map them anyway you want, but it can get confusing if
you're mapping /dev/sda6 > /dev/sdb8 and calling it /dev/md7.
We've found it easier to make them all line up, /dev/sda6 > /dev/sdb6 >
/dev/md6
FYI, if you want better performance, use 15K SCSI disks, and make sure
you've got more than 8MB of cache per disk. Also, you're correct in
splitting the drives across the channel, that's a trap for young players
;-)
Bjoern is right to recommend an LVM, it will allow you to dynamically
allocate new size to the RAID volume when you add more disks. However
I've no experience in implementation with an LVM under the software RAID
for Linux, though I believe it can be done.
The software RAID implementation allows you to stop and start software
RAID devices as desired, add new hot spare disks to the containers as
needed and rebuild containers on the fly. You can even change kernel
options to speed up or slow down the sync speed when rebuilding the
container.
Anyway, have fun, cause striping is the hot rod of the RAID
implementations ;-)
Regards.
Hadley
On Fri, 2004-05-14 at 09:53, Bjoern Metzdorf wrote:
> James Thornton wrote:
>
> >> This is what I am considering the ultimate platform for postgresql:
> >>
> >> Hardware:
> >> Tyan Thunder K8QS board
> >> 2-4 x Opteron 848 in NUMA mode
> >> 4-8 GB RAM (DDR400 ECC Registered 1 GB modules, 2 for each processor)
> >> LSI Megaraid 320-2 with 256 MB cache ram and battery backup
> >> 6 x 36GB SCSI 10K drives + 1 spare running in RAID 10, split over both
> >> channels (3 + 4) for pgdata including indexes and wal.
> >
> > You might also consider configuring the Postgres data drives for a RAID
> > 10 SAME configuration as described in the Oracle paper "Optimal Storage
> > Configuration Made Easy"
> > (http://otn.oracle.com/deploy/availability/pdf/oow2000_same.pdf). Has
> > anyone delved into this before?
>
> Ok, if I understand it correctly the papers recommends the following:
>
> 1. Get many drives and stripe them into a RAID0 with a stripe width of
> 1MB. I am not quite sure if this stripe width is to be controlled at the
> application level (does postgres support this?) or if e.g. the "chunk
> size" of the linux software driver is meant. Normally a chunk size of
> 4KB is recommended, so 1MB sounds fairly large.
>
> 2. Mirror your RAID0 and get a RAID10.
>
> 3. Use primarily the fast, outer regions of your disks. In practice this
> might be achieved by putting only half of the disk (the outer half) into
> your stripe set. E.g. put only the outer 18GB of your 36GB disks into
> the stripe set. Btw, is it common for all drives that the outer region
> is on the higher block numbers? Or is it sometimes on the lower block
> numbers?
>
> 4. Subset data by partition, not disk. If you have 8 disks, then don't
> take a 4 disk RAID10 for data and the other one for log or indexes, but
> make a global 8 drive RAID10 and have it partitioned the way that data
> and log + indexes are located on all drives.
>
> They say, which is very interesting, as it is really contrary to what is
> normally recommended, that it is good or better to have one big stripe
> set over all disks available, than to put log + indexes on a separated
> stripe set. Having one big stripe set means that the speed of this big
> stripe set is available to all data. In practice this setup is as fast
> as or even faster than the "old" approach.
>
> ----------------------------------------------------------------
>
> Bottom line for a normal, less than 10 disk setup:
>
> Get many disks (8 + spare), create a RAID0 with 4 disks and mirror it to
> the other 4 disks for a RAID10. Make sure to create the RAID on the
> outer half of the disks (setup may depend on the disk model and raid
> controller used), leaving the inner half empty.
> Use a logical volume manager (LVM), which always helps when adding disk
> space, and create 2 partitions on your RAID10. One for data and one for
> log + indexes. This should look like this:
>
> ----- ----- ----- -----
> | 1 | | 1 | | 1 | | 1 |
> ----- ----- ----- ----- <- outer, faster half of the disk
> | 2 | | 2 | | 2 | | 2 | part of the RAID10
> ----- ----- ----- -----
> | | | | | | | |
> | | | | | | | | <- inner, slower half of the disk
> | | | | | | | | not used at all
> ----- ----- ----- -----
>
> Partition 1 for data, partition 2 for log + indexes. All mirrored to the
> other 4 disks not shown.
>
> If you take 36GB disks, this should end up like this:
>
> RAID10 has size of 36 / 2 * 4 = 72GB
> Partition 1 is 36 GB
> Partition 2 is 36 GB
>
> If 36GB is not enough for your pgdata set, you might consider moving to
> 72GB disks, or (even better) make a 16 drive RAID10 out of 36GB disks,
> which both will end up in a size of 72GB for your data (but the 16 drive
> version will be faster).
>
> Any comments?
>
> Regards,
> Bjoern
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [PERFORM] Quad processor options - summary
@ 2004-05-13 23:36 James Thornton <james@jamesthornton.com>
parent: Hadley Willan <hadley.willan@deeperdesign.co.nz>
0 siblings, 0 replies; 8+ messages in thread
From: James Thornton @ 2004-05-13 23:36 UTC (permalink / raw)
To: Hadley Willan <hadley.willan@deeperdesign.co.nz>; +Cc: Bjoern Metzdorf <bm@turtle-entertainment.de>; pgsql-performance; pgsql-admin@postgresql.org
Hadley Willan wrote:
> To answer question 1, if you use software raid the chunk size is part of
> the /etc/raidtab file that is used on initial container creation. 4KB is
> the standard and a LARGE chunk size of 1MB may affect performance if
> you're not writing down to blocks in that size continuously. If you
> make it to big and you're constantly needing to write out smaller chunks
> of information, then you will find the disk "always" working and would
> be an inefficient use of the blocks. There is some free info around
> about calculating the ideal chunk size. Looking for "Calculating chunk
> size for RAID" through google.
"Why does the SAME configuration recommend a one megabyte stripe width?
Let’s examine the reasoning behind this choice. Why not use a stripe
depth smaller than one megabyte? Smaller stripe depths can improve disk
throughput for a single process by spreading a single IO across multiple
disks. However IOs that are much smaller than a megabyte can cause seek
time to becomes a large fraction of the total IO time. Therefore, the
overall efficiency of the storage system is reduced. In some cases it
may be worth trading off some efficiency for the increased throughput
that smaller stripe depths provide. In general it is not necessary to do
this though. Parallel execution at database level achieves high disk
throughput while keeping efficiency high. Also, remember that the degree
of parallelism can be dynamically tuned, whereas the stripe depth is
very costly to change.
Why not use a stripe depth bigger than one megabyte? One megabyte is
large enough that a sequential scan will spend most of its time
transferring data instead of positioning the disk head. A bigger stripe
depth will improve scan efficiency but only modestly. One megabyte is
small enough that a large IO operation will not “hog” a single disk for
very long before moving to the next one. Further, one megabyte is small
enough that Oracle’s asynchronous readahead operations access multiple
disks. One megabyte is also small enough that a single stripe unit will
not become a hot-spot. Any access hot-spot that is smaller than a
megabyte should fit comfortably in the database buffer cache. Therefore
it will not create a hot-spot on disk."
The SAME configuration paper says to ensure that that large IO
operations aren't broken up between the DB and the disk, you need to be
able to ensure that the database file multi-block read count (Oracle has
a param called db_file_multiblock_read_count, does Postgres?) is the
same size as the stripe width and the OS IO limits should be at least
this size.
Also, it says, "Ideally we would like to stripe the log files using the
same one megabyte stripe width as the rest of the files. However, the
log files are written sequentially, and many storage systems limit the
maximum size of a single write operation to one megabyte (or even less).
If the maximum write size is limited, then using a one megabyte stripe
width for the log files may not work well. In this case, a smaller
stripe width such as 64K may work better. Caching RAID controllers are
an exception to this. If the storage subsystem can cache write
operations in nonvolatile RAM, then a one megabyte stripe width will
work well for the log files. In this case, the write operation will be
buffered in cache and the next log writes can be issued before the
previous write is destaged to disk."
--
James Thornton
______________________________________________________
Internet Business Consultant, http://jamesthornton.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [PERFORM] Quad processor options - summary
@ 2004-05-14 00:51 Paul Tuckfield <paul@tuckfield.com>
parent: Bjoern Metzdorf <bm@turtle-entertainment.de>
3 siblings, 0 replies; 8+ messages in thread
From: Paul Tuckfield @ 2004-05-14 00:51 UTC (permalink / raw)
To: Bjoern Metzdorf <bm@turtle-entertainment.de>; +Cc: pgsql-admin@postgresql.org, James Thornton <james@jamesthornton.com>; pgsql-performance
One big caveat re. the "SAME" striping strategy, is that readahead can
really hurt an OLTP you.
Mind you, if you're going from a few disks to a caching array with many
disks, it'll be hard to not have a big improvement
But if you push the envelope of the array with a "SAME" configuration,
readahead will hurt. Readahead is good for sequential reads but bad
for random reads, because the various caches (array and filesystem) get
flooded with all the blocks that happen to come after whatever random
blocks you're reading. Because they're random reads these extra
blocks are genarally *not* read by subsequent queries if the database
is large enough to be much larger than the cache itself. Of course,
the readahead blocks are good if you're doing sequential scans, but
you're not doing sequential scans because it's an OLTP database, right?
So this'll probably incite flames but:
In an OLTP environment of decent size, readahead is bad. The ideal
would be to adjust it dynamically til optimum (likely no readahead) if
the array allows it, but most people are fooled by good performance of
readahead on simple singlethreaded or small dataset tests, and get
bitten by this under concurrent loads or large datasets.
James Thornton wrote:
>
>>> This is what I am considering the ultimate platform for postgresql:
>>>
>>> Hardware:
>>> Tyan Thunder K8QS board
>>> 2-4 x Opteron 848 in NUMA mode
>>> 4-8 GB RAM (DDR400 ECC Registered 1 GB modules, 2 for each processor)
>>> LSI Megaraid 320-2 with 256 MB cache ram and battery backup
>>> 6 x 36GB SCSI 10K drives + 1 spare running in RAID 10, split over
>>> both channels (3 + 4) for pgdata including indexes and wal.
>> You might also consider configuring the Postgres data drives for a
>> RAID 10 SAME configuration as described in the Oracle paper "Optimal
>> Storage Configuration Made Easy"
>> (http://otn.oracle.com/deploy/availability/pdf/oow2000_same.pdf). Has
>> anyone delved into this before?
>
> Ok, if I understand it correctly the papers recommends the following:
>
> 1. Get many drives and stripe them into a RAID0 with a stripe width of
> 1MB. I am not quite sure if this stripe width is to be controlled at
> the application level (does postgres support this?) or if e.g. the
> "chunk size" of the linux software driver is meant. Normally a chunk
> size of 4KB is recommended, so 1MB sounds fairly large.
>
> 2. Mirror your RAID0 and get a RAID10.
>
> 3. Use primarily the fast, outer regions of your disks. In practice
> this might be achieved by putting only half of the disk (the outer
> half) into your stripe set. E.g. put only the outer 18GB of your 36GB
> disks into the stripe set. Btw, is it common for all drives that the
> outer region is on the higher block numbers? Or is it sometimes on the
> lower block numbers?
>
> 4. Subset data by partition, not disk. If you have 8 disks, then don't
> take a 4 disk RAID10 for data and the other one for log or indexes,
> but make a global 8 drive RAID10 and have it partitioned the way that
> data and log + indexes are located on all drives.
>
> They say, which is very interesting, as it is really contrary to what
> is normally recommended, that it is good or better to have one big
> stripe set over all disks available, than to put log + indexes on a
> separated stripe set. Having one big stripe set means that the speed
> of this big stripe set is available to all data. In practice this
> setup is as fast as or even faster than the "old" approach.
>
> ----------------------------------------------------------------
>
> Bottom line for a normal, less than 10 disk setup:
>
> Get many disks (8 + spare), create a RAID0 with 4 disks and mirror it
> to the other 4 disks for a RAID10. Make sure to create the RAID on the
> outer half of the disks (setup may depend on the disk model and raid
> controller used), leaving the inner half empty.
> Use a logical volume manager (LVM), which always helps when adding
> disk space, and create 2 partitions on your RAID10. One for data and
> one for log + indexes. This should look like this:
>
> ----- ----- ----- -----
> | 1 | | 1 | | 1 | | 1 |
> ----- ----- ----- ----- <- outer, faster half of the disk
> | 2 | | 2 | | 2 | | 2 | part of the RAID10
> ----- ----- ----- -----
> | | | | | | | |
> | | | | | | | | <- inner, slower half of the disk
> | | | | | | | | not used at all
> ----- ----- ----- -----
>
> Partition 1 for data, partition 2 for log + indexes. All mirrored to
> the other 4 disks not shown.
>
> If you take 36GB disks, this should end up like this:
>
> RAID10 has size of 36 / 2 * 4 = 72GB
> Partition 1 is 36 GB
> Partition 2 is 36 GB
>
> If 36GB is not enough for your pgdata set, you might consider moving
> to 72GB disks, or (even better) make a 16 drive RAID10 out of 36GB
> disks, which both will end up in a size of 72GB for your data (but the
> 16 drive version will be faster).
>
> Any comments?
>
> Regards,
> Bjoern
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [PERFORM] Quad processor options - summary
@ 2004-05-14 10:17 Mark Kirkwood <markir@paradise.net.nz>
parent: Bjoern Metzdorf <bm@turtle-entertainment.de>
3 siblings, 0 replies; 8+ messages in thread
From: Mark Kirkwood @ 2004-05-14 10:17 UTC (permalink / raw)
To: Bjoern Metzdorf <bm@turtle-entertainment.de>; +Cc: James Thornton <james@jamesthornton.com>; pgsql-performance; pgsql-admin@postgresql.org
I would recommend trying out several stripe sizes, and making your own
measurements.
A while ago I was involved in building a data warehouse system (Oracle,
DB2) and after several file and db benchmark exercises we used 256K
stripes, as these gave the best overall performance results for both
systems.
I am not saying "1M is wrong", but I am saying "1M may not be right" :-)
regards
Mark
Bjoern Metzdorf wrote:
>
> 1. Get many drives and stripe them into a RAID0 with a stripe width of
> 1MB. I am not quite sure if this stripe width is to be controlled at
> the application level (does postgres support this?) or if e.g. the
> "chunk size" of the linux software driver is meant. Normally a chunk
> size of 4KB is recommended, so 1MB sounds fairly large.
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2004-05-14 10:17 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-12 22:40 Quad processor options - summary Bjoern Metzdorf <bm@turtle-entertainment.de>
2004-05-13 21:02 ` James Thornton <james@jamesthornton.com>
2004-05-13 21:53 ` Bjoern Metzdorf <bm@turtle-entertainment.de>
2004-05-13 22:50 ` James Thornton <james@jamesthornton.com>
2004-05-13 22:59 ` Hadley Willan <hadley.willan@deeperdesign.co.nz>
2004-05-13 23:36 ` James Thornton <james@jamesthornton.com>
2004-05-14 00:51 ` Paul Tuckfield <paul@tuckfield.com>
2004-05-14 10:17 ` Mark Kirkwood <markir@paradise.net.nz>
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