pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Tuning PostgreSQL
47+ messages / 19 participants
[nested] [flat]
* Re: Tuning PostgreSQL
@ 2003-07-13 10:11 Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
0 siblings, 0 replies; 47+ messages in thread
From: Shridhar Daithankar @ 2003-07-13 10:11 UTC (permalink / raw)
To: Ron Johnson <ron.l.johnson@cox.net>; +Cc: pgsql-performance; pgsql-general@postgresql.org
On Sunday 13 July 2003 10:23, Ron Johnson wrote:
> On Fri, 2003-07-04 at 09:49, Shridhar Daithankar wrote:
> > On 4 Jul 2003 at 16:35, Michael Mattox wrote:
>
> [snip]
>
> > On a positive note, me and Josh are finishing a bare bone performance
> > article that would answer lot of your questions. I am counting on you to
> > provide valuable feedback. I expect it out tomorrow or on sunday..Josh
> > will confirm that..
>
> Hello,
>
> Is this doc publicly available yet?
Yes. See http://www.varlena.com/GeneralBits/
I thought I announved it on performance.. anyways..
Shridhar
^ permalink raw reply [nested|flat] 47+ messages in thread
* Tuning PostgreSQL
@ 2003-07-21 08:31 Alexander Priem <ap@cict.nl>
2003-07-21 08:51 ` Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
0 siblings, 1 reply; 47+ messages in thread
From: Alexander Priem @ 2003-07-21 08:31 UTC (permalink / raw)
To: pgsql-performance
Hi guys,
I am new to PostgreSQL and have done some "extensive" research already. If you could give me some advice/confirmation, I would be really grateful.
I am going to build a PostgreSQL database server for a client. This database will contain many tables (over 100, maybe more), with some of them containing over 1 million records pretty soon. This database will be accessed via a desktop application (Windows) and a web application (PHP/Apache). There may be over 100 people accessing the database at any given time (selecting, inserting and updating), but I don't think it will be a lot more than 100 at any given time. Most of the time, it will be less.
What I am thinking about is buying a server with the following specifications:
* 1 or 2 Intel Xeon processors (2.4 GHz).
* 2 Gigabytes of RAM (DDR/ECC).
* Three 36Gb SCSI160 disks (10.000rpm) in a RAID-5 config, giving 72Gb storage space (right?). The RAID-5 controller has a (hardware) cache of 128Mb.
* 100Mbit ethernet.
I will run RedHat Linux 9 (kernel 2.40) with PostgreSQL 7.3.3 on this server.
What would you think of this hardware config? Would it do? Of would 4Gb RAM be a lot better? What do you think about the need for two Xeon procs?
Finally, I have some questions about postgresql.conf (who doesnt?). After some research, I think I will go for the following settings initially. Maybe benchmarking will lead to other values, but I think these settings will be a fine starting point :
shared_buffers = 6000 (kernel.shmmax = 60000000)
sort_mem = 4096
max_connections = 150
vacuum_mem = 65536
What do you think of these settings? Do you have any other hints for optimizing PostgreSQL
Many many thanks in advance :)
Kind regards,
Alexander Priem
CICT Solutions
Email: ap@cict.nl
Internet: www.cict.nl
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 08:31 Tuning PostgreSQL Alexander Priem <ap@cict.nl>
@ 2003-07-21 08:51 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
0 siblings, 0 replies; 47+ messages in thread
From: Shridhar Daithankar @ 2003-07-21 08:51 UTC (permalink / raw)
To: pgsql-performance
On 21 Jul 2003 at 10:31, Alexander Priem wrote:
> What I am thinking about is buying a server with the following specifications:
>
> * 1 or 2 Intel Xeon processors (2.4 GHz).
> * 2 Gigabytes of RAM (DDR/ECC).
> * Three 36Gb SCSI160 disks (10.000rpm) in a RAID-5 config, giving 72Gb storage
> space (right?). The RAID-5 controller has a(hardware) cache of 128Mb.
> * 100Mbit ethernet.
>
> I will run RedHat Linux 9 (kernel 2.40) with PostgreSQL 7.3.3 on this server.
You might scale down a little on hardware front if required. Of course, if you
can get it, get it.
> What would you think of this hardware config? Would it do? Of would 4Gb RAM be
> a lot better? What do you think about the need for two Xeon procs?
I would say get an SMP board with one processor in it. If requierd you can
upgrade. I suppose that would make hefty difference in price.
> shared_buffers = 6000(kernel.shmmax = 60000000)
> sort_mem = 4096
> max_connections = 150
> vacuum_mem = 65536
effective_cache_size
noatime for data partition
A good filesystem.
WAL on separate drive.
Now that is a good start..
Bye
Shridhar
--
QOTD: "I'm on a seafood diet -- I see food and I eat it."
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
@ 2003-07-21 09:33 Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 09:40 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 10:09 ` Re: Tuning PostgreSQL Ang Chin Han <angch@bytecraft.com.my>
2003-07-21 10:31 ` Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-22 09:52 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
0 siblings, 4 replies; 47+ messages in thread
From: Shridhar Daithankar @ 2003-07-21 09:33 UTC (permalink / raw)
To: Alexander Priem <ap@cict.nl>; +Cc: pgsql-performance
Hi Alexander ,
On 21 Jul 2003 at 11:23, Alexander Priem wrote:
> So the memory settings I specified are pretty much OK?
As of now yes, You need to test with these settings and make sure that they
perform as per your requirement. That tweaking will always be there...
> What would be good guidelines for setting effective_cache_size, noatime ?
I suggest you look at
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html.
That should help you.
> I use ext3 filesystem, which probably is not the best performer, is it?
No. You also need to check ext2, reiser and XFS. There is no agreement between
users as in what works best. You need to benchmark and decide.
> I will set the WAL on a separate drive. What do I need to change in the conf
> files to achive this?
No. You need to shutdown postgresql server process and symlink WAL and clog
directories in postgresql database cluster to another place. That should do it.
HTH
Bye
Shridhar
--
Meade's Maxim: Always remember that you are absolutely unique, just like everyone else.
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-21 09:40 ` Alexander Priem <ap@cict.nl>
3 siblings, 0 replies; 47+ messages in thread
From: Alexander Priem @ 2003-07-21 09:40 UTC (permalink / raw)
To: shridhar_daithankar@persistent.co.in; +Cc: pgsql-performance
Thanks, I will look at the site you sent me and purchase some hardware. Then
I will run some benchmarks.
Kind regards,
Alexander.
----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: "Alexander Priem" <ap@cict.nl>
Cc: <pgsql-performance@postgresql.org>
Sent: Monday, July 21, 2003 11:33 AM
Subject: Re: [PERFORM] Tuning PostgreSQL
> Hi Alexander ,
>
> On 21 Jul 2003 at 11:23, Alexander Priem wrote:
> > So the memory settings I specified are pretty much OK?
>
> As of now yes, You need to test with these settings and make sure that
they
> perform as per your requirement. That tweaking will always be there...
>
> > What would be good guidelines for setting effective_cache_size, noatime
?
>
> I suggest you look at
> http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html.
>
> That should help you.
>
> > I use ext3 filesystem, which probably is not the best performer, is it?
>
> No. You also need to check ext2, reiser and XFS. There is no agreement
between
> users as in what works best. You need to benchmark and decide.
>
> > I will set the WAL on a separate drive. What do I need to change in the
conf
> > files to achive this?
>
> No. You need to shutdown postgresql server process and symlink WAL and
clog
> directories in postgresql database cluster to another place. That should
do it.
>
> HTH
>
> Bye
> Shridhar
>
> --
> Meade's Maxim: Always remember that you are absolutely unique, just like
everyone else.
>
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-21 10:09 ` Ang Chin Han <angch@bytecraft.com.my>
3 siblings, 0 replies; 47+ messages in thread
From: Ang Chin Han @ 2003-07-21 10:09 UTC (permalink / raw)
To: shridhar_daithankar@persistent.co.in; +Cc: Alexander Priem <ap@cict.nl>; pgsql-performance
Shridhar Daithankar wrote:
> On 21 Jul 2003 at 11:23, Alexander Priem wrote:
>>I use ext3 filesystem, which probably is not the best performer, is it?
>
> No. You also need to check ext2, reiser and XFS. There is no agreement between
> users as in what works best. You need to benchmark and decide.
Need? Maybe I'm a bit disillusioned, but are the performances between
the filesystems differ so much as to warrant the additional effort?
(e.g. XFS doesn't come with Red Hat 9 -- you'll have to patch the
source, and compile it yourself).
Benchmarking it properly before deployment is tough: are the test load
on the db/fs representative of actual load? Is 0.5% reduction in CPU
usage worth it? Did you test for catastrophic failure by pulling the
plug during write operations (ext2) to test if the fs can handle it? Is
the code base for the particular fs stable enough? Obscure bugs in the fs?
For the record, we tried several filesystems, but stuck with 2.4.9's
ext3 (Red Hat Advanced Server). Didn't hit a load high enough for the
filesystem choices to matter after all. :(
--
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386
GNU/Linux
5:30pm up 207 days, 8:35, 5 users, load average: 5.33, 5.16, 5.21
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-21 10:31 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 11:27 ` Re: Tuning PostgreSQL Ang Chin Han <angch@bytecraft.com.my>
2003-07-21 11:39 ` Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 11:45 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
3 siblings, 3 replies; 47+ messages in thread
From: Shridhar Daithankar @ 2003-07-21 10:31 UTC (permalink / raw)
To: pgsql-performance
On 21 Jul 2003 at 18:09, Ang Chin Han wrote:
> Shridhar Daithankar wrote:
> > On 21 Jul 2003 at 11:23, Alexander Priem wrote:
>
> >>I use ext3 filesystem, which probably is not the best performer, is it?
> >
> > No. You also need to check ext2, reiser and XFS. There is no agreement between
> > users as in what works best. You need to benchmark and decide.
>
> Need? Maybe I'm a bit disillusioned, but are the performances between
> the filesystems differ so much as to warrant the additional effort?
> (e.g. XFS doesn't come with Red Hat 9 -- you'll have to patch the
> source, and compile it yourself).
Well, the benchmarking is not to prove which filesystem is fastest and feature
rich but to find out which one suits your needs best.
> Benchmarking it properly before deployment is tough: are the test load
> on the db/fs representative of actual load? Is 0.5% reduction in CPU
> usage worth it? Did you test for catastrophic failure by pulling the
> plug during write operations (ext2) to test if the fs can handle it? Is
> the code base for the particular fs stable enough? Obscure bugs in the fs?
Well, that is what that 'benchmark' is supposed to find out. Call it pre-
deployment testing or whatever other fancy name one sees fit. But it is a must
in almost all serious usage.
> For the record, we tried several filesystems, but stuck with 2.4.9's
> ext3 (Red Hat Advanced Server). Didn't hit a load high enough for the
> filesystem choices to matter after all. :(
Good for you. You have time at hand to find out which one suits you best. Do
the testing before you have load that needs another FS..:-)
Bye
Shridhar
--
It would be illogical to assume that all conditions remain stable. -- Spock, "The Enterprise" Incident", stardate 5027.3
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 10:31 ` Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-21 11:27 ` Ang Chin Han <angch@bytecraft.com.my>
2 siblings, 0 replies; 47+ messages in thread
From: Ang Chin Han @ 2003-07-21 11:27 UTC (permalink / raw)
To: shridhar_daithankar@persistent.co.in; +Cc: pgsql-performance
Shridhar Daithankar wrote:
> Good for you. You have time at hand to find out which one suits you best. Do
> the testing before you have load that needs another FS..:-)
Kinda my point is that when we've more load, we'd be using RAID-0 over
RAID-5, or getting faster SCSI drives, or even turn fsync off if that's
a bottleneck, because the different filesystems do not have that much
performance difference[1] -- the filesystem is not a bottleneck. Just
need to tweak most of them a bit, like noatime,data=writeback.
[1] That is, AFAIK, from our testing. Please, please correct me if I'm
wrong: has anyone found that different filesystems produces wildly
different performance for postgresql, FreeBSD's filesystems not included?
--
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386
GNU/Linux
7:00pm up 207 days, 10:05, 5 users, load average: 5.00, 5.03, 5.06
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 10:31 ` Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-21 11:39 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2 siblings, 0 replies; 47+ messages in thread
From: Shridhar Daithankar @ 2003-07-21 11:39 UTC (permalink / raw)
To: pgsql-performance
On 21 Jul 2003 at 19:27, Ang Chin Han wrote:
> [1] That is, AFAIK, from our testing. Please, please correct me if I'm
> wrong: has anyone found that different filesystems produces wildly
> different performance for postgresql, FreeBSD's filesystems not included?
well, when postgresql starts splitting table files after a gig, filesystem sure
makes difference. IIRC, frommy last test XFS was at least 10-15% faster than
reiserfs for such databases. That was around an year back, with mandrake 8.0.
Bye
Shridhar
--
modesty, n.: Being comfortable that others will discover your greatness.
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 10:31 ` Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-21 11:45 ` Alexander Priem <ap@cict.nl>
2003-07-21 12:05 ` Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2 siblings, 1 reply; 47+ messages in thread
From: Alexander Priem @ 2003-07-21 11:45 UTC (permalink / raw)
To: shridhar_daithankar@persistent.co.in; pgsql-performance
So where can I set the noatime & data=writeback variables? They are not
PostgreSQL settings, but rather Linux settings, right? Where can I find
these?
Kind regards,
Alexander Priem.
----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: <pgsql-performance@postgresql.org>
Sent: Monday, July 21, 2003 12:31 PM
Subject: Re: [PERFORM] Tuning PostgreSQL
> On 21 Jul 2003 at 18:09, Ang Chin Han wrote:
>
> > Shridhar Daithankar wrote:
> > > On 21 Jul 2003 at 11:23, Alexander Priem wrote:
> >
> > >>I use ext3 filesystem, which probably is not the best performer, is
it?
> > >
> > > No. You also need to check ext2, reiser and XFS. There is no agreement
between
> > > users as in what works best. You need to benchmark and decide.
> >
> > Need? Maybe I'm a bit disillusioned, but are the performances between
> > the filesystems differ so much as to warrant the additional effort?
> > (e.g. XFS doesn't come with Red Hat 9 -- you'll have to patch the
> > source, and compile it yourself).
>
> Well, the benchmarking is not to prove which filesystem is fastest and
feature
> rich but to find out which one suits your needs best.
>
> > Benchmarking it properly before deployment is tough: are the test load
> > on the db/fs representative of actual load? Is 0.5% reduction in CPU
> > usage worth it? Did you test for catastrophic failure by pulling the
> > plug during write operations (ext2) to test if the fs can handle it? Is
> > the code base for the particular fs stable enough? Obscure bugs in the
fs?
>
> Well, that is what that 'benchmark' is supposed to find out. Call it pre-
> deployment testing or whatever other fancy name one sees fit. But it is a
must
> in almost all serious usage.
>
> > For the record, we tried several filesystems, but stuck with 2.4.9's
> > ext3 (Red Hat Advanced Server). Didn't hit a load high enough for the
> > filesystem choices to matter after all. :(
>
> Good for you. You have time at hand to find out which one suits you best.
Do
> the testing before you have load that needs another FS..:-)
>
> Bye
> Shridhar
>
> --
> It would be illogical to assume that all conditions remain stable. --
Spock, "The Enterprise" Incident", stardate 5027.3
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 10:31 ` Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 11:45 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
@ 2003-07-21 12:05 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 12:43 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
0 siblings, 1 reply; 47+ messages in thread
From: Shridhar Daithankar @ 2003-07-21 12:05 UTC (permalink / raw)
To: pgsql-performance
On 21 Jul 2003 at 13:45, Alexander Priem wrote:
> So where can I set the noatime & data=writeback variables? They are not
> PostgreSQL settings, but rather Linux settings, right? Where can I find
> these?
These are typicaly set in /etc/fstab.conf. These are mount settings. man mount
for more details.
The second setting data=writeback is ext3 specific, IIRC.
HTH
Bye
Shridhar
--
History tends to exaggerate. -- Col. Green, "The Savage Curtain", stardate
5906.4
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 10:31 ` Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 11:45 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 12:05 ` Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-21 12:43 ` Alexander Priem <ap@cict.nl>
0 siblings, 0 replies; 47+ messages in thread
From: Alexander Priem @ 2003-07-21 12:43 UTC (permalink / raw)
To: shridhar_daithankar@persistent.co.in; pgsql-performance
Thanks, i'll look further into these mount setting.
I was just thinking, the server will have a (RAID) controller containing
128Mb of battery-backed cache memory. This would really speed up inserts to
the disk and would prevent data loss in case of a power-down also.
What would you guys think of not using RAID5 in that case, but just a really
fast 15.000 rpm SCSI-320 disk?
Kind regards,
Alexander.
----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: <pgsql-performance@postgresql.org>
Sent: Monday, July 21, 2003 2:05 PM
Subject: Re: [PERFORM] Tuning PostgreSQL
> On 21 Jul 2003 at 13:45, Alexander Priem wrote:
>
> > So where can I set the noatime & data=writeback variables? They are not
> > PostgreSQL settings, but rather Linux settings, right? Where can I find
> > these?
>
> These are typicaly set in /etc/fstab.conf. These are mount settings. man
mount
> for more details.
>
> The second setting data=writeback is ext3 specific, IIRC.
>
> HTH
>
> Bye
> Shridhar
>
> --
> History tends to exaggerate. -- Col. Green, "The Savage Curtain", stardate
> 5906.4
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
@ 2003-07-22 09:52 ` Ron Johnson <ron.l.johnson@cox.net>
2003-07-22 12:53 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
3 siblings, 1 reply; 47+ messages in thread
From: Ron Johnson @ 2003-07-22 09:52 UTC (permalink / raw)
To: pgsql-performance
On Mon, 2003-07-21 at 04:33, Shridhar Daithankar wrote:
> Hi Alexander ,
>
> On 21 Jul 2003 at 11:23, Alexander Priem wrote:
[snip]
> > I use ext3 filesystem, which probably is not the best performer, is it?
>
> No. You also need to check ext2, reiser and XFS. There is no agreement between
> users as in what works best. You need to benchmark and decide.
According to Jeremy Allison of SAMBA, ""They used ext3, which is one
of the slowest filesystems on Linux," Allison said. "In a real
comparative test, you would use XFS".
http://www.linuxworld.com/story/32673.htm
--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-22 09:52 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
@ 2003-07-22 12:53 ` Alexander Priem <ap@cict.nl>
2003-07-22 14:37 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-25 10:45 ` Re: Tuning PostgreSQL Andrew McMillan <andrew@catalyst.net.nz>
0 siblings, 2 replies; 47+ messages in thread
From: Alexander Priem @ 2003-07-22 12:53 UTC (permalink / raw)
To: pgsql-performance
Wow, I never figured how many different RAID configurations one could think
of :)
After reading lots of material, forums and of course, this mailing-list, I
think I am going for a RAID5 configuration of 6 disks (18Gb, 15.000 rpm
each), one of those six disks will be a 'hot spare'. I will just put the OS,
the WAL and the data one one volume. RAID10 is way to expensive :)
If I understand correctly, this will give great read-performance, but less
write-performance. But since this server will be equipped with an embedded
RAID controller featuring 128Mb of battery-backed cache, I figure that this
controller will negate that (at least somewhat). I will need to find out
whether this cache can be configured so that it will ONLY cache WRITES, not
READS....
Also because of this battery backed cache controller, I will go for the ext2
file system, mounted with 'noatime'. I will use a UPS, so I don't think I
need the journaling of ext3. XFS is not natively supported by RedHat and I
will go for the easy way here :)
1 Gb of RAM should be enough, I think. That is about the only point that
almost everyone agrees on :) Do you think ECC is very important? The
server I have in mind does not support it. Another one does, but is is about
1.000 euros more expensive :(
One CPU should also be enough.
As for postgresql.conf settings, I think I will start with the following :
max_connections = 128
superuser_reserved_connections = 1
shared_buffers = 8192
max_fsm_relations = 1000
max_fsm_pages = 100000
wal_buffers = 32
sort_mem = 2048
vacuum_mem = 32768
effective_cache_size = 28672 (this one I'm not sure about, maybe this one
needs to be higher)
random_page_cost = 2
geq0_threshold = 20
This pretty much sums it up. What do you think about this config? It may not
be the fastest, but a server like this will cost about 4750 euros, and that
is including an Intel Xeon 2.4GHz cpu, redundant power supply, WITHOUT the
UPS. Seems very reasonable to me...
Kind regards,
Alexander Priem.
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-22 09:52 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-22 12:53 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
@ 2003-07-22 14:37 ` Ron Johnson <ron.l.johnson@cox.net>
1 sibling, 0 replies; 47+ messages in thread
From: Ron Johnson @ 2003-07-22 14:37 UTC (permalink / raw)
To: pgsql-performance
On Tue, 2003-07-22 at 07:53, Alexander Priem wrote:
> Wow, I never figured how many different RAID configurations one could think
[snip]
> Also because of this battery backed cache controller, I will go for the ext2
> file system, mounted with 'noatime'. I will use a UPS, so I don't think I
> need the journaling of ext3.
Oooooo, I don't think I'd do that!!!!! It's akin to saying, "I
don't need to make backups, because I have RAID[1,5,10,1+0]
If the power is out for 26 minutes and your UPS only lasts for 25
minutes, you could be in be in for a long, painful boot process if
the box crashes. (For example, the UPS auto-shutdown daemon doesn't
work properly, and no one can get to the console to shut it down
properly before the batteries die.)
--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-22 09:52 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-22 12:53 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
@ 2003-07-25 10:45 ` Andrew McMillan <andrew@catalyst.net.nz>
2003-07-26 10:18 ` Re: Tuning PostgreSQL Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
1 sibling, 1 reply; 47+ messages in thread
From: Andrew McMillan @ 2003-07-25 10:45 UTC (permalink / raw)
To: Alexander Priem <ap@cict.nl>; +Cc: pgsql-performance
On Wed, 2003-07-23 at 00:53, Alexander Priem wrote:
> Wow, I never figured how many different RAID configurations one could think
> of :)
>
> After reading lots of material, forums and of course, this mailing-list, I
> think I am going for a RAID5 configuration of 6 disks (18Gb, 15.000 rpm
> each), one of those six disks will be a 'hot spare'. I will just put the OS,
> the WAL and the data one one volume. RAID10 is way to expensive :)
The general heuristic is that RAID-5 is not the way to deal with
databases. Now surely someone will disagree with me, but as I
understand it RAID-5 has a bottleneck on a single disk for the
(checksum) information. Bottleneck is not the word you want to hear in
the context of "database server".
RAID-1 (mirroring) or RAID-10 (sort-of-mirrored-RAID-5) is the best
choice.
As far as FS performance goes, a year or two ago I remember someone
doing an evaluation of FS performance for PostgreSQL and they found that
the best performance was...
FAT
Yep: FAT
The reason is that a lot of what the database is doing, especially
guaranteeing writes (WAL) and so forth is best handled through a
filesystem that does not get in the way. The fundamentals will not have
changed.
It is for this reason that ext2 is very much likely to be better than
ext3. XFS is possibly (maybe, perhaps) OK, because there are
optimisations in there for databases, but the best optimisation is to
not be there at all. That's why Oracle want direct IO to disk
partitions so they can implement their own "filesystem" (i.e. record
system... table system...) on a raw partition.
Personally I don't plan to reboot my DB server more than once a year (if
that (even my_laptop currently has 37 days uptime, not including
suspend). On our DB servers I use ext2 (rather than ext3) mounted with
noatime, and I bite the 15 minutes to fsck (once a year) rather than
screw general performance with journalling database on top of
journalling FS. I split pg_xlog onto a separate physical disk, if
performance requirements are extreme.
Catalyst's last significant project was to write the Domain Name
registration system for .nz (using PostgreSQL). Currently we are
developing the electoral roll for the same country (2.8 million electors
living at 1.4 million addresses). We use Oracle (or Progress, or MySQL)
if a client demands them, but we use PostgreSQL if we get to choose.
Increasingly we get to choose. Good.
Regards,
Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Survey for nothing with http://survey.net.nz/
---------------------------------------------------------------------
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-22 09:52 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-22 12:53 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-25 10:45 ` Re: Tuning PostgreSQL Andrew McMillan <andrew@catalyst.net.nz>
@ 2003-07-26 10:18 ` Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
2003-07-26 14:12 ` Re: Tuning PostgreSQL Tom Lane <tgl@sss.pgh.pa.us>
2003-07-26 21:08 ` Re: Tuning PostgreSQL Balazs Wellisch <balazs@neusolutions.com>
0 siblings, 2 replies; 47+ messages in thread
From: Arjen van der Meijden @ 2003-07-26 10:18 UTC (permalink / raw)
To: 'Andrew McMillan' <andrew@catalyst.net.nz>; 'Alexander Priem' <ap@cict.nl>; +Cc: pgsql-performance
> Andrew McMillan wrote:
>
> The general heuristic is that RAID-5 is not the way to deal
> with databases. Now surely someone will disagree with me,
> but as I understand it RAID-5 has a bottleneck on a single
> disk for the
> (checksum) information. Bottleneck is not the word you want
> to hear in the context of "database server".
That's indeed incorrect. There is no single disk "special" in a Raid-5,
you might be mistaking it for Raid-3/4 (where a single disk holds the
checksum). In raid-5 the checksums are scattered around on all the
harddisks.
Raid-5's problem is the write-performance, but with a decent
raid-controller it outperforms a less-decent raid-controller (with the
same harddisks) on both read- and writeperformance which is running a
raid-10.
With a decent raid-controller you end up with "about the same" write
performance as with raid-1, but slightly lower read performance.
At least, that's what I was able to gather from some tests of a
colleague of mine with different raid-setups.
> RAID-1 (mirroring) or RAID-10 (sort-of-mirrored-RAID-5) is
> the best choice.
Raid-10 is _not_ similar to raid-5, it is raid1+0 i.e. a mirroring set
of stripes (raid-0 is more-or-less a stripe).
For databases, raid-10 is supposed to be the fastest, since you have the
advantage of the striping for both reading and writing. While you also
have the advantage of the mirroring for reading.
The main disadvantage of raid-1 (and also of raid-10) is the heavy waste
of harddisk space. Another advantage of raid-5 over raid-10 is that when
you don't care about space, raid-5 is more save with four harddrives
than raid-10 (i.e. set it up with a 3-disk+1spare).
> As far as FS performance goes, a year or two ago I remember
> someone doing an evaluation of FS performance for PostgreSQL
> and they found that the best performance was...
>
> FAT
>
> Yep: FAT
FAT has a few disadvantages afaik, I wouldn't use it for my database at
least.
> Personally I don't plan to reboot my DB server more than once
> a year (if that (even my_laptop currently has 37 days uptime,
> not including suspend). On our DB servers I use ext2 (rather
> than ext3) mounted with noatime, and I bite the 15 minutes to
> fsck (once a year) rather than screw general performance with
> journalling database on top of journalling FS. I split
> pg_xlog onto a separate physical disk, if performance
> requirements are extreme.
Well, reboting is not a problem with ext2, but crashing might be... And
normally you don't plan a systemcrash ;)
Ext3 and xfs handle that much better.
Regards,
Arjen
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-22 09:52 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-22 12:53 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-25 10:45 ` Re: Tuning PostgreSQL Andrew McMillan <andrew@catalyst.net.nz>
2003-07-26 10:18 ` Re: Tuning PostgreSQL Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
@ 2003-07-26 14:12 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 47+ messages in thread
From: Tom Lane @ 2003-07-26 14:12 UTC (permalink / raw)
To: Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>; +Cc: 'Andrew McMillan' <andrew@catalyst.net.nz>; 'Alexander Priem' <ap@cict.nl>; pgsql-performance
"Arjen van der Meijden" <acmmailing@vulcanus.its.tudelft.nl> writes:
> Well, reboting is not a problem with ext2, but crashing might be... And
> normally you don't plan a systemcrash ;)
> Ext3 and xfs handle that much better.
A journaling filesystem is good to use if you can set it to journal
metadata but not file contents. PG's WAL logic can recover lost file
contents, but we have no way to help out the filesystem if it's lost
metadata.
regards, tom lane
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-22 09:52 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-22 12:53 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-25 10:45 ` Re: Tuning PostgreSQL Andrew McMillan <andrew@catalyst.net.nz>
2003-07-26 10:18 ` Re: Tuning PostgreSQL Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
@ 2003-07-26 21:08 ` Balazs Wellisch <balazs@neusolutions.com>
2003-07-28 17:10 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 47+ messages in thread
From: Balazs Wellisch @ 2003-07-26 21:08 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
Since there seem to be a lot of different opinions regarding the various
different RAID configurations I thought I'd post this link to the list:
http://www.storagereview.com/guide2000/ref/hdd/perf/raid/index.html
This is the best resource for information on RAID and hard drive
performance I found online.
I hope this helps.
Balazs
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-22 09:52 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-22 12:53 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-25 10:45 ` Re: Tuning PostgreSQL Andrew McMillan <andrew@catalyst.net.nz>
2003-07-26 10:18 ` Re: Tuning PostgreSQL Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
2003-07-26 21:08 ` Re: Tuning PostgreSQL Balazs Wellisch <balazs@neusolutions.com>
@ 2003-07-28 17:10 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 47+ messages in thread
From: Josh Berkus @ 2003-07-28 17:10 UTC (permalink / raw)
To: Balazs Wellisch <balazs@neusolutions.com>; +Cc: pgsql-performance
Balasz,
> Since there seem to be a lot of different opinions regarding the various
> different RAID configurations I thought I'd post this link to the list:
> http://www.storagereview.com/guide2000/ref/hdd/perf/raid/index.html
Yeah ... this is a really good article. Made me realize why "stripey" RAID
sucks for OLTP databases, unless you throw a lot of platters at them.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
@ 2003-07-21 13:45 Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
0 siblings, 1 reply; 47+ messages in thread
From: Roman Fail @ 2003-07-21 13:45 UTC (permalink / raw)
To: Alexander Priem <ap@cict.nl>; shridhar_daithankar@persistent.co.in; pgsql-performance
> What would you guys think of not using RAID5 in that case, but just a really
> fast 15.000 rpm SCSI-320 disk?
I'd say you must be able to tolerate losing all the data since your last database backup. Your battery backed cache, rotational speed, and transfer rate aren't going to help at all when the drive itself degrades and corrupts data. If you can really only afford 3 drives, I'd have a single drive with the OS & WAL on it, and the data on a RAID-1 mirror set using the other 2 drives. If you need more space for data, or want your OS drives to be mirrored - it's going to cost more. See if you can get 2x18GB drives for the OS and 2x73GB drives for the data.
You have to consider how much headache that small amount of additional money is going to save you (and your users) down the road.
Roman
-----Original Message-----
From: Alexander Priem [mailto:ap@cict.nl]
Sent: Mon 7/21/2003 5:43 AM
To: shridhar_daithankar@persistent.co.in; pgsql-performance@postgresql.org
Cc:
Subject: Re: [PERFORM] Tuning PostgreSQL
Thanks, i'll look further into these mount setting.
I was just thinking, the server will have a (RAID) controller containing
128Mb of battery-backed cache memory. This would really speed up inserts to
the disk and would prevent data loss in case of a power-down also.
What would you guys think of not using RAID5 in that case, but just a really
fast 15.000 rpm SCSI-320 disk?
Kind regards,
Alexander.
----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: <pgsql-performance@postgresql.org>
Sent: Monday, July 21, 2003 2:05 PM
Subject: Re: [PERFORM] Tuning PostgreSQL
> On 21 Jul 2003 at 13:45, Alexander Priem wrote:
>
> > So where can I set the noatime & data=writeback variables? They are not
> > PostgreSQL settings, but rather Linux settings, right? Where can I find
> > these?
>
> These are typicaly set in /etc/fstab.conf. These are mount settings. man
mount
> for more details.
>
> The second setting data=writeback is ext3 specific, IIRC.
>
> HTH
>
> Bye
> Shridhar
>
> --
> History tends to exaggerate. -- Col. Green, "The Savage Curtain", stardate
> 5906.4
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
@ 2003-07-21 14:00 ` Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 47+ messages in thread
From: Alexander Priem @ 2003-07-21 14:00 UTC (permalink / raw)
To: Roman Fail <rfail@posportal.com>; shridhar_daithankar@persistent.co.in; pgsql-performance
That's true, certainly, and with four disks (2x18 and 2x72 or 36), I would
be able to (a) be safe and (b) split the data and WAL.
Hmmm. Seems to me that this setup would be better than one RAID5 with three
36Gb disks, wouldn't you think so? With one RAID5 array, I would still have
the data and the WAL on one volume...
Thanks for all your help so far.
Kind regards,
Alexander Priem.
----- Original Message -----
From: "Roman Fail" <rfail@posportal.com>
To: "Alexander Priem" <ap@cict.nl>; <shridhar_daithankar@persistent.co.in>;
<pgsql-performance@postgresql.org>
Sent: Monday, July 21, 2003 3:45 PM
Subject: Re: [PERFORM] Tuning PostgreSQL
> > What would you guys think of not using RAID5 in that case, but just a
really
> > fast 15.000 rpm SCSI-320 disk?
>
>
> I'd say you must be able to tolerate losing all the data since your last
database backup. Your battery backed cache, rotational speed, and transfer
rate aren't going to help at all when the drive itself degrades and corrupts
data. If you can really only afford 3 drives, I'd have a single drive with
the OS & WAL on it, and the data on a RAID-1 mirror set using the other 2
drives. If you need more space for data, or want your OS drives to be
mirrored - it's going to cost more. See if you can get 2x18GB drives for
the OS and 2x73GB drives for the data.
>
> You have to consider how much headache that small amount of additional
money is going to save you (and your users) down the road.
>
> Roman
>
> -----Original Message-----
> From: Alexander Priem [mailto:ap@cict.nl]
> Sent: Mon 7/21/2003 5:43 AM
> To: shridhar_daithankar@persistent.co.in; pgsql-performance@postgresql.org
> Cc:
> Subject: Re: [PERFORM] Tuning PostgreSQL
>
>
>
> Thanks, i'll look further into these mount setting.
>
> I was just thinking, the server will have a (RAID) controller containing
> 128Mb of battery-backed cache memory. This would really speed up inserts
to
> the disk and would prevent data loss in case of a power-down also.
>
> What would you guys think of not using RAID5 in that case, but just a
really
> fast 15.000 rpm SCSI-320 disk?
>
> Kind regards,
> Alexander.
>
>
> ----- Original Message -----
> From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
> To: <pgsql-performance@postgresql.org>
> Sent: Monday, July 21, 2003 2:05 PM
> Subject: Re: [PERFORM] Tuning PostgreSQL
>
>
> > On 21 Jul 2003 at 13:45, Alexander Priem wrote:
> >
> > > So where can I set the noatime & data=writeback variables? They are
not
> > > PostgreSQL settings, but rather Linux settings, right? Where can I
find
> > > these?
> >
> > These are typicaly set in /etc/fstab.conf. These are mount settings. man
> mount
> > for more details.
> >
> > The second setting data=writeback is ext3 specific, IIRC.
> >
> > HTH
> >
> > Bye
> > Shridhar
> >
> > --
> > History tends to exaggerate. -- Col. Green, "The Savage Curtain",
stardate
> > 5906.4
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
@ 2003-07-21 16:06 ` Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
0 siblings, 1 reply; 47+ messages in thread
From: Josh Berkus @ 2003-07-21 16:06 UTC (permalink / raw)
To: Alexander Priem <ap@cict.nl>; Roman Fail <rfail@posportal.com>; shridhar_daithankar@persistent.co.in; pgsql-performance
Alexander,
> Hmmm. Seems to me that this setup would be better than one RAID5 with three
> 36Gb disks, wouldn't you think so? With one RAID5 array, I would still have
> the data and the WAL on one volume...
Definitely. As I've said, my experience with RAID5 is that with less than 5
disks, it performs around 40% of a single scsi disk for large read-write
operation on Postgres.
If you have only 3 disks, I'd advocate one disk for WAL and one RAID 1 array
for the database.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
@ 2003-07-21 16:28 ` Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
0 siblings, 1 reply; 47+ messages in thread
From: Vincent van Leeuwen @ 2003-07-21 16:28 UTC (permalink / raw)
To: pgsql-performance
On 2003-07-21 09:06:10 -0700, Josh Berkus wrote:
> Alexander,
>
> > Hmmm. Seems to me that this setup would be better than one RAID5 with three
> > 36Gb disks, wouldn't you think so? With one RAID5 array, I would still have
> > the data and the WAL on one volume...
>
> Definitely. As I've said, my experience with RAID5 is that with less than 5
> disks, it performs around 40% of a single scsi disk for large read-write
> operation on Postgres.
>
> If you have only 3 disks, I'd advocate one disk for WAL and one RAID 1 array
> for the database.
>
In this setup your database is still screwed if a single disk (the WAL disk)
stops working. You'll have to revert to your last backup if this happens. The
RAID-1 redundancy on your data disks buys you almost nothing: marginally
better performance and no real redundancy should a single disk fail.
I'd use RAID-5 if you absolutely cannot use more disks, but I would use
RAID-10 or two RAID-1 partitions if you can afford to use 4 disks.
Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
@ 2003-07-22 07:04 ` Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
0 siblings, 1 reply; 47+ messages in thread
From: Alexander Priem @ 2003-07-22 07:04 UTC (permalink / raw)
To: Vincent van Leeuwen <pgsql.spam@vinz.nl>; pgsql-performance
Hi all,
Vincent, You said that using RAID1, you don't have real redundancy. But
RAID1 is mirroring, right? So if one of the two disks should fail, there
should be no data lost, right?
I have been thinking some more. 18Gb drives are cheaper than 36 or 72Gb
drives. I don't know if I can get the money for this, but how would the
following setup sound?
Two 18Gb (15.000rpm) disks in RAID1 array for Operating System + WAL.
Four 18Gb (15.000rpm) disks in RAID5 array for data.
For the same amount of money, I could also get:
Two 36Gb (10.000rpm) disks in RAID1 array for Operating System + WAL.
Five/Six 36Gb (10.000rpm) disks in RAID5 array for data.
Which would be the best of the above? The one with four 15k-rpm disks or the
one with five/six 10k-rpm disks?
Would these configs be better than all disks in one huge RAID5 array? There
are so many possible configs with RAID.......
Kind regards,
Alexander Priem.
----- Original Message -----
From: "Vincent van Leeuwen" <pgsql.spam@vinz.nl>
To: <pgsql-performance@postgresql.org>
Sent: Monday, July 21, 2003 6:28 PM
Subject: Re: [PERFORM] Tuning PostgreSQL
> On 2003-07-21 09:06:10 -0700, Josh Berkus wrote:
> > Alexander,
> >
> > > Hmmm. Seems to me that this setup would be better than one RAID5 with
three
> > > 36Gb disks, wouldn't you think so? With one RAID5 array, I would still
have
> > > the data and the WAL on one volume...
> >
> > Definitely. As I've said, my experience with RAID5 is that with less
than 5
> > disks, it performs around 40% of a single scsi disk for large read-write
> > operation on Postgres.
> >
> > If you have only 3 disks, I'd advocate one disk for WAL and one RAID 1
array
> > for the database.
> >
>
> In this setup your database is still screwed if a single disk (the WAL
disk)
> stops working. You'll have to revert to your last backup if this happens.
The
> RAID-1 redundancy on your data disks buys you almost nothing: marginally
> better performance and no real redundancy should a single disk fail.
>
> I'd use RAID-5 if you absolutely cannot use more disks, but I would use
> RAID-10 or two RAID-1 partitions if you can afford to use 4 disks.
>
> Vincent van Leeuwen
> Media Design - http://www.mediadesign.nl/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
@ 2003-07-22 09:40 ` Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 17:10 ` Re: Tuning PostgreSQL Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 47+ messages in thread
From: Vincent van Leeuwen @ 2003-07-22 09:40 UTC (permalink / raw)
To: pgsql-performance
On 2003-07-22 09:04:42 +0200, Alexander Priem wrote:
> Hi all,
>
> Vincent, You said that using RAID1, you don't have real redundancy. But
> RAID1 is mirroring, right? So if one of the two disks should fail, there
> should be no data lost, right?
>
Right. But the proposal was a single disk for WAL, without redundancy, and I
argued that wasn't really safe. RAID1 by itself is extremely safe, possibly
even the safest RAID type there is.
> I have been thinking some more. 18Gb drives are cheaper than 36 or 72Gb
> drives. I don't know if I can get the money for this, but how would the
> following setup sound?
>
> Two 18Gb (15.000rpm) disks in RAID1 array for Operating System + WAL.
> Four 18Gb (15.000rpm) disks in RAID5 array for data.
>
Our own testing has shown that a 6 disk RAID-10 array is faster than what you
describe. Of course, this is very much dependant on how much INSERT/UPDATES
you generate (which taxes your WAL more), so your mileage may vary.
> For the same amount of money, I could also get:
>
> Two 36Gb (10.000rpm) disks in RAID1 array for Operating System + WAL.
> Five/Six 36Gb (10.000rpm) disks in RAID5 array for data.
>
It is said that a higher RPM is particularly useful for a WAL disk. So you
might consider using two 18GB 15K rpm drives for a RAID-1 WAL disk (+OS and
swap), and using 36GB 10K rpm disks in a RAID-5 array if you need that
diskspace.
> Which would be the best of the above? The one with four 15k-rpm disks or the
> one with five/six 10k-rpm disks?
> Would these configs be better than all disks in one huge RAID5 array? There
> are so many possible configs with RAID.......
>
15K rpm disks are significantly faster than 10K rpm disks. If your only
concern is performance, buy 15K rpm disks. If you want more diskspace for your
money, fall back to larger 10K rpm disks.
I personally think seperate WAL disks are vastly overrated, since they haven't
shown a big performance gain in our own tests. But as I have said, this is
extremely dependant on the type of load you generate, so only your own tests
can tell you what you should do in this respect.
About RAID types: the fastest RAID type by far is RAID-10. However, this will
cost you a lot of useable diskspace, so it isn't for everyone. You need at
least 4 disks for a RAID-10 array. RAID-5 is a nice compromise if you want as
much useable diskspace as possible and still want to be redundant. RAID-1 is
very useful for small (2-disk) arrays.
If you have the time and are settled on buying 6 disks, I'd test the following
scenarios:
- 6-disk RAID-10 array (should perform best)
- 4-disk RAID-10 array containing data, 2-disk RAID-1 array for WAL, OS, etc
- 4-disk RAID-5 array containing data, 2-disk RAID-1 array for WAL, OS, etc
- 6-disk RAID-5 array (will probably perform worst)
Hope this helps.
Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
@ 2003-07-22 13:27 ` Alexander Priem <ap@cict.nl>
2003-07-22 14:12 ` Re: Tuning PostgreSQL Andrew Sullivan <andrew@libertyrms.info>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
1 sibling, 2 replies; 47+ messages in thread
From: Alexander Priem @ 2003-07-22 13:27 UTC (permalink / raw)
To: Vincent van Leeuwen <pgsql.spam@vinz.nl>; pgsql-performance
Wow, I never figured how many different RAID configurations one could think
of :)
After reading lots of material, forums and of course, this mailing-list, I
think I am going for a RAID5 configuration of 6 disks (18Gb, 15.000 rpm
each), one of those six disks will be a 'hot spare'. I will just put the OS,
the WAL and the data one one volume. RAID10 is way to expensive :)
If I understand correctly, this will give great read-performance, but less
write-performance. But since this server will be equipped with an embedded
RAID controller featuring 128Mb of battery-backed cache, I figure that this
controller will negate that (at least somewhat). I will need to find out
whether this cache can be configured so that it will ONLY cache WRITES, not
READS....
Also because of this battery backed cache controller, I will go for the ext2
file system, mounted with 'noatime'. I will use a UPS, so I don't think I
need the journaling of ext3. XFS is not natively supported by RedHat and I
will go for the easy way here :)
1 Gb of RAM should be enough, I think. That is about the only point that
almost everyone agrees on :) Do you think ECC is very important? The
server I have in mind does not support it. Another one does, but is is about
1.000 euros more expensive :(
One CPU should also be enough.
As for postgresql.conf settings, I think I will start with the following :
max_connections = 128
superuser_reserved_connections = 1
shared_buffers = 8192
max_fsm_relations = 1000
max_fsm_pages = 100000
wal_buffers = 32
sort_mem = 2048
vacuum_mem = 32768
effective_cache_size = 28672 (this one I'm not sure about, maybe this one
needs to be higher)
random_page_cost = 2
geq0_threshold = 20
This pretty much sums it up. What do you think about this config? It may not
be the fastest, but a server like this will cost about 4750 euros, and that
is including an Intel Xeon 2.4GHz cpu, redundant power supply, WITHOUT the
UPS. Seems very reasonable to me...
Kind regards,
Alexander Priem.
----- Original Message -----
From: "Vincent van Leeuwen" <pgsql.spam@vinz.nl>
To: <pgsql-performance@postgresql.org>
Sent: Tuesday, July 22, 2003 11:40 AM
Subject: Re: [PERFORM] Tuning PostgreSQL
> On 2003-07-22 09:04:42 +0200, Alexander Priem wrote:
> > Hi all,
> >
> > Vincent, You said that using RAID1, you don't have real redundancy. But
> > RAID1 is mirroring, right? So if one of the two disks should fail, there
> > should be no data lost, right?
> >
>
> Right. But the proposal was a single disk for WAL, without redundancy, and
I
> argued that wasn't really safe. RAID1 by itself is extremely safe,
possibly
> even the safest RAID type there is.
>
> > I have been thinking some more. 18Gb drives are cheaper than 36 or 72Gb
> > drives. I don't know if I can get the money for this, but how would the
> > following setup sound?
> >
> > Two 18Gb (15.000rpm) disks in RAID1 array for Operating System + WAL.
> > Four 18Gb (15.000rpm) disks in RAID5 array for data.
> >
>
> Our own testing has shown that a 6 disk RAID-10 array is faster than what
you
> describe. Of course, this is very much dependant on how much
INSERT/UPDATES
> you generate (which taxes your WAL more), so your mileage may vary.
>
> > For the same amount of money, I could also get:
> >
> > Two 36Gb (10.000rpm) disks in RAID1 array for Operating System + WAL.
> > Five/Six 36Gb (10.000rpm) disks in RAID5 array for data.
> >
>
> It is said that a higher RPM is particularly useful for a WAL disk. So you
> might consider using two 18GB 15K rpm drives for a RAID-1 WAL disk (+OS
and
> swap), and using 36GB 10K rpm disks in a RAID-5 array if you need that
> diskspace.
>
> > Which would be the best of the above? The one with four 15k-rpm disks or
the
> > one with five/six 10k-rpm disks?
> > Would these configs be better than all disks in one huge RAID5 array?
There
> > are so many possible configs with RAID.......
> >
>
> 15K rpm disks are significantly faster than 10K rpm disks. If your only
> concern is performance, buy 15K rpm disks. If you want more diskspace for
your
> money, fall back to larger 10K rpm disks.
>
> I personally think seperate WAL disks are vastly overrated, since they
haven't
> shown a big performance gain in our own tests. But as I have said, this is
> extremely dependant on the type of load you generate, so only your own
tests
> can tell you what you should do in this respect.
>
> About RAID types: the fastest RAID type by far is RAID-10. However, this
will
> cost you a lot of useable diskspace, so it isn't for everyone. You need at
> least 4 disks for a RAID-10 array. RAID-5 is a nice compromise if you want
as
> much useable diskspace as possible and still want to be redundant. RAID-1
is
> very useful for small (2-disk) arrays.
>
> If you have the time and are settled on buying 6 disks, I'd test the
following
> scenarios:
> - 6-disk RAID-10 array (should perform best)
> - 4-disk RAID-10 array containing data, 2-disk RAID-1 array for WAL, OS,
etc
> - 4-disk RAID-5 array containing data, 2-disk RAID-1 array for WAL, OS,
etc
> - 6-disk RAID-5 array (will probably perform worst)
>
>
> Hope this helps.
>
> Vincent van Leeuwen
> Media Design - http://www.mediadesign.nl/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
@ 2003-07-22 14:12 ` Andrew Sullivan <andrew@libertyrms.info>
1 sibling, 0 replies; 47+ messages in thread
From: Andrew Sullivan @ 2003-07-22 14:12 UTC (permalink / raw)
To: pgsql-performance
On Tue, Jul 22, 2003 at 03:27:20PM +0200, Alexander Priem wrote:
> file system, mounted with 'noatime'. I will use a UPS, so I don't think I
> need the journaling of ext3. XFS is not natively supported by RedHat and I
Just in case you're still thinking, why do you suppose that only
power failures lead to system crashes? Surprise kernel panics due to
bad hardware or OS upgrades with bugs in them, sudden failures
because of bad memory, &c: all these things also can lead to crashes,
and though super-redundant hardware can mitigate that risk, they
can't eliminate them completely. This is not advice, of course, but
for my money, its a bad idea not to use a journalled filesystem (or
something similar) for production systems.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
@ 2003-07-22 14:33 ` Jim C. Nasby <jim@nasby.net>
2003-07-22 15:01 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 17:19 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 2 replies; 47+ messages in thread
From: Jim C. Nasby @ 2003-07-22 14:33 UTC (permalink / raw)
To: Alexander Priem <ap@cict.nl>; +Cc: Vincent van Leeuwen <pgsql.spam@vinz.nl>; pgsql-performance
On Tue, Jul 22, 2003 at 03:27:20PM +0200, Alexander Priem wrote:
> Wow, I never figured how many different RAID configurations one could think
> of :)
>
> After reading lots of material, forums and of course, this mailing-list, I
> think I am going for a RAID5 configuration of 6 disks (18Gb, 15.000 rpm
> each), one of those six disks will be a 'hot spare'. I will just put the OS,
> the WAL and the data one one volume. RAID10 is way to expensive :)
>
> If I understand correctly, this will give great read-performance, but less
> write-performance. But since this server will be equipped with an embedded
> RAID controller featuring 128Mb of battery-backed cache, I figure that this
> controller will negate that (at least somewhat). I will need to find out
> whether this cache can be configured so that it will ONLY cache WRITES, not
> READS....
I think the bigger isssue with RAID5 write performance in a database is
that it hits every spindle. The real performance bottleneck you run into
is latency, especially the latency of positioning the heads. I don't
have any proof to this theory, but I believe this is why moving WAL
and/or temp_db to seperate drives from the main database files can be a
big benefit for some applications; not because of disk bandwidth but
because it drastically cuts down the amount of time the heads have to
spend flying around the disk.
Of course, this is also highly dependant on how the filesystem operates,
too. If it puts your WALs, temp_db, and database files very close to
each other on the drive, splitting them out to seperate spindles won't
help as much.
--
Jim C. Nasby, Database Consultant jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
@ 2003-07-22 15:01 ` Alexander Priem <ap@cict.nl>
2003-07-22 15:36 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-22 16:12 ` Re: Tuning PostgreSQL Vivek Khera <khera@kcilink.com>
1 sibling, 2 replies; 47+ messages in thread
From: Alexander Priem @ 2003-07-22 15:01 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: Vincent van Leeuwen <pgsql.spam@vinz.nl>; pgsql-performance
OK, another change of plans :)
ext2 seems to be a bad idea. So i'll stick with ext3. Better safe than
sorry...
About the RAID-config: Maybe RAID-10 with six disks is affordable after all.
I would have to take the smallest disks in this case, 18Gb per disk. So six
18Gb disks (15000rpm) would result in a total capacity of 54 Gb, right? This
volume would hold OS, WAL and data, but since RAID10 appears to deliver such
great performance (according to several people), in combination with the
128Mb of battery backed cache, this would be a good solution?
Hmmm. I keep changing my mind about this. My Db would be mostly 'selecting',
but there would also be pretty much inserting and updating done. But most of
the work would be selects. So would this config be OK?
Kind regards,
Alexander.
----- Original Message -----
From: "Jim C. Nasby" <jim@nasby.net>
To: "Alexander Priem" <ap@cict.nl>
Cc: "Vincent van Leeuwen" <pgsql.spam@vinz.nl>;
<pgsql-performance@postgresql.org>
Sent: Tuesday, July 22, 2003 4:33 PM
Subject: Re: [PERFORM] Tuning PostgreSQL
> On Tue, Jul 22, 2003 at 03:27:20PM +0200, Alexander Priem wrote:
> > Wow, I never figured how many different RAID configurations one could
think
> > of :)
> >
> > After reading lots of material, forums and of course, this mailing-list,
I
> > think I am going for a RAID5 configuration of 6 disks (18Gb, 15.000 rpm
> > each), one of those six disks will be a 'hot spare'. I will just put the
OS,
> > the WAL and the data one one volume. RAID10 is way to expensive :)
> >
> > If I understand correctly, this will give great read-performance, but
less
> > write-performance. But since this server will be equipped with an
embedded
> > RAID controller featuring 128Mb of battery-backed cache, I figure that
this
> > controller will negate that (at least somewhat). I will need to find out
> > whether this cache can be configured so that it will ONLY cache WRITES,
not
> > READS....
>
> I think the bigger isssue with RAID5 write performance in a database is
> that it hits every spindle. The real performance bottleneck you run into
> is latency, especially the latency of positioning the heads. I don't
> have any proof to this theory, but I believe this is why moving WAL
> and/or temp_db to seperate drives from the main database files can be a
> big benefit for some applications; not because of disk bandwidth but
> because it drastically cuts down the amount of time the heads have to
> spend flying around the disk.
>
> Of course, this is also highly dependant on how the filesystem operates,
> too. If it puts your WALs, temp_db, and database files very close to
> each other on the drive, splitting them out to seperate spindles won't
> help as much.
> --
> Jim C. Nasby, Database Consultant jim@nasby.net
> Member: Triangle Fraternity, Sports Car Club of America
> Give your computer some brain candy! www.distributed.net Team #1828
>
> Windows: "Where do you want to go today?"
> Linux: "Where do you want to go tomorrow?"
> FreeBSD: "Are you guys coming, or what?"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
2003-07-22 15:01 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
@ 2003-07-22 15:36 ` Ron Johnson <ron.l.johnson@cox.net>
1 sibling, 0 replies; 47+ messages in thread
From: Ron Johnson @ 2003-07-22 15:36 UTC (permalink / raw)
To: pgsql-performance
On Tue, 2003-07-22 at 10:01, Alexander Priem wrote:
> OK, another change of plans :)
>
> ext2 seems to be a bad idea. So i'll stick with ext3. Better safe than
> sorry...
Don't forget noatime!
> About the RAID-config: Maybe RAID-10 with six disks is affordable after all.
> I would have to take the smallest disks in this case, 18Gb per disk. So six
> 18Gb disks (15000rpm) would result in a total capacity of 54 Gb, right? This
> volume would hold OS, WAL and data, but since RAID10 appears to deliver such
> great performance (according to several people), in combination with the
> 128Mb of battery backed cache, this would be a good solution?
>
> Hmmm. I keep changing my mind about this. My Db would be mostly 'selecting',
> but there would also be pretty much inserting and updating done. But most of
> the work would be selects. So would this config be OK?
Others may disagree, but I'd put the OS and executables on a separate
disk from the db and WAL, and make it an IDE drive, since it's so
much less expensive than SCSI disks. (Make a copy of the disk, and
if it craps out, pop out the old disk, stick in the new disk, and
fire the box right back up...)
Thus, you'll have an OS/executables disk, and a separate DB disk,
and never the twain shall meet. Theoretically, you could pick up
those 6 drives and controller, move them to another machine, and
the data should be just as it was on the other box.
--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
2003-07-22 15:01 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
@ 2003-07-22 16:12 ` Vivek Khera <khera@kcilink.com>
1 sibling, 0 replies; 47+ messages in thread
From: Vivek Khera @ 2003-07-22 16:12 UTC (permalink / raw)
To: pgsql-performance
>>>>> "AP" == Alexander Priem <ap@cict.nl> writes:
AP> Hmmm. I keep changing my mind about this. My Db would be mostly
AP> 'selecting', but there would also be pretty much inserting and
AP> updating done. But most of the work would be selects. So would
AP> this config be OK?
I'm about to order a new server. I haven't decided exactly how many
disks I will get, but my plan is to get an 8-disk RAID10 with 15k RPM
drives. I don't need the volume, just the speed and number of
spindles, so I'm buying the smallest drives that meet my speed
probably 18Gb each (sheesh! I remember getting my first 5Mb disk for
my 8088 PC in college and thinking that was too much space).
My mix is nearly even read/write, but probably a little biased towards
the reading.
This machine is replacing a 5-disk box that was switched from RAID5 to
4-disk RAID10 for data plus one system disk in January (what a pain
that was to re-index, but that's another story). The switch from
RAID5 to RAID10 made an enormous improvement in performance. The
speedup wasn't from recreating the database: It was restored from a
file-level backup so the actual files were not compacted or secretly
"improved" in any way, other than my occasional reindexing.
So I think your 6-disk RAID10 will be good.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
@ 2003-07-22 17:19 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-24 18:29 ` Re: Tuning PostgreSQL Greg Stark <gsstark@mit.edu>
1 sibling, 1 reply; 47+ messages in thread
From: scott.marlowe @ 2003-07-22 17:19 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: Alexander Priem <ap@cict.nl>; Vincent van Leeuwen <pgsql.spam@vinz.nl>; pgsql-performance
On Tue, 22 Jul 2003, Jim C. Nasby wrote:
> On Tue, Jul 22, 2003 at 03:27:20PM +0200, Alexander Priem wrote:
> > Wow, I never figured how many different RAID configurations one could think
> > of :)
> >
> > After reading lots of material, forums and of course, this mailing-list, I
> > think I am going for a RAID5 configuration of 6 disks (18Gb, 15.000 rpm
> > each), one of those six disks will be a 'hot spare'. I will just put the OS,
> > the WAL and the data one one volume. RAID10 is way to expensive :)
> >
> > If I understand correctly, this will give great read-performance, but less
> > write-performance. But since this server will be equipped with an embedded
> > RAID controller featuring 128Mb of battery-backed cache, I figure that this
> > controller will negate that (at least somewhat). I will need to find out
> > whether this cache can be configured so that it will ONLY cache WRITES, not
> > READS....
>
> I think the bigger isssue with RAID5 write performance in a database is
> that it hits every spindle.
This is a common, and wrong misconception.
If you are writing 4k out to a RAID5 of 10 disks, this is what happens:
(assumiung 64k stipes...)
READ data stripe (64k read)
READ parity stripe (64k read)
make changes to data stripe
XOR new data stripe with old parity stripe to get a new parity stripe
write new parity stripe (64k)
write new data stripe (64k)
So it's not as bad as you might think. No modern controller (or sw raid
for linux) hits all the spindles anymore for writes. As you add more
drives to a RAID5 writes actually get faster on average, because there's
less chance of having contention for the same drives (remember, parity
moves about in RAID5 so the parity disk isn't a choke point in RAID5 like
it is in RAID4.)
> The real performance bottleneck you run into
> is latency, especially the latency of positioning the heads. I don't
> have any proof to this theory, but I believe this is why moving WAL
> and/or temp_db to seperate drives from the main database files can be a
> big benefit for some applications; not because of disk bandwidth but
> because it drastically cuts down the amount of time the heads have to
> spend flying around the disk.
This is absolutely true. moving the heads costs hugely. while most
modern drives have SEEK times <10 ms, the SETTLE times tend to be about
that as well, followed by the average of about 3 ms for rotational latency
to allow the proper sector to be under the head (10krpm drives rotate once
about every 6 ms.)
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
2003-07-22 17:19 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
@ 2003-07-24 18:29 ` Greg Stark <gsstark@mit.edu>
2003-07-24 20:13 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 15:14 ` Re: Tuning PostgreSQL Vivek Khera <khera@kcilink.com>
0 siblings, 2 replies; 47+ messages in thread
From: Greg Stark @ 2003-07-24 18:29 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Jim C. Nasby <jim@nasby.net>; Alexander Priem <ap@cict.nl>; Vincent van Leeuwen <pgsql.spam@vinz.nl>; pgsql-performance
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> If you are writing 4k out to a RAID5 of 10 disks, this is what happens:
>
> (assumiung 64k stipes...)
> READ data stripe (64k read)
> READ parity stripe (64k read)
> make changes to data stripe
> XOR new data stripe with old parity stripe to get a new parity stripe
> write new parity stripe (64k)
> write new data stripe (64k)
>
> So it's not as bad as you might think.
The main negative for RAID5 is that it had to do that extra READ. If you're
doing lots of tiny updates then the extra latency to have to go read the
parity block before it can write the parity block out is a real killer. For
that reason people prefer 0+1 for OLTP systems.
But you have to actually test your setup in practice to see if it hurts. A big
data warehousing system will be faster under RAID5 than under RAID1+0 because
of the extra disks in the stripeset. The more disks in the stripeset the more
bandwidth you get.
Even for OLTP systems I've had success with RAID5 or not depending largely on
the quality of the implementation. The Hitachi systems were amazing. They had
enough battery backed cache that the extra latency for the parity read/write
cycle really never showed up at all. But it had a lot more than 128M. I think
it had 1G and could be expanded.
--
greg
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
2003-07-22 17:19 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-24 18:29 ` Re: Tuning PostgreSQL Greg Stark <gsstark@mit.edu>
@ 2003-07-24 20:13 ` Ron Johnson <ron.l.johnson@cox.net>
1 sibling, 0 replies; 47+ messages in thread
From: Ron Johnson @ 2003-07-24 20:13 UTC (permalink / raw)
To: pgsql-performance
On Thu, 2003-07-24 at 13:29, Greg Stark wrote:
> "scott.marlowe" <scott.marlowe@ihs.com> writes:
>
> > If you are writing 4k out to a RAID5 of 10 disks, this is what happens:
> >
> > (assumiung 64k stipes...)
> > READ data stripe (64k read)
> > READ parity stripe (64k read)
> > make changes to data stripe
> > XOR new data stripe with old parity stripe to get a new parity stripe
> > write new parity stripe (64k)
> > write new data stripe (64k)
> >
> > So it's not as bad as you might think.
>
> The main negative for RAID5 is that it had to do that extra READ. If you're
> doing lots of tiny updates then the extra latency to have to go read the
> parity block before it can write the parity block out is a real killer. For
> that reason people prefer 0+1 for OLTP systems.
>
> But you have to actually test your setup in practice to see if it hurts. A big
> data warehousing system will be faster under RAID5 than under RAID1+0 because
> of the extra disks in the stripeset. The more disks in the stripeset the more
> bandwidth you get.
>
> Even for OLTP systems I've had success with RAID5 or not depending largely on
> the quality of the implementation. The Hitachi systems were amazing. They had
> enough battery backed cache that the extra latency for the parity read/write
> cycle really never showed up at all. But it had a lot more than 128M. I think
> it had 1G and could be expanded.
Your last paragraph just stole the objection to the 1st paragraph
right out of my mouth, since enough cache will allow it to "batch"
all those tiny updates into big updates. But those Hitachi controllers
weren't plugged into x86-type boxen, were they?
--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
2003-07-22 17:19 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-24 18:29 ` Re: Tuning PostgreSQL Greg Stark <gsstark@mit.edu>
@ 2003-07-29 15:14 ` Vivek Khera <khera@kcilink.com>
2003-07-29 15:46 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 16:16 ` Re: Tuning PostgreSQL Will LaShell <will@lashell.net>
1 sibling, 2 replies; 47+ messages in thread
From: Vivek Khera @ 2003-07-29 15:14 UTC (permalink / raw)
To: pgsql-performance
>>>>> "GS" == Greg Stark <gsstark@mit.edu> writes:
GS> "scott.marlowe" <scott.marlowe@ihs.com> writes:
GS> But you have to actually test your setup in practice to see if it
GS> hurts. A big data warehousing system will be faster under RAID5
GS> than under RAID1+0 because of the extra disks in the
GS> stripeset. The more disks in the stripeset the more bandwidth you
GS> get.
Anyone have ideas on 14 spindles? I just ordered a disk subsystem
with 14 high speed (U320 15kRPM) SCSI disks to hook up with a dell
PERC3/DC controller (only 128MB cache, though).
My plan was to do RAID10, but I think I'll play with RAID5 again and
see which gives me the best performance. Unfortunatly, it is
difficult to recreate the highly fragmented tables I have now (vacuum
full takes over 14 hours on one of the tables) so I'm not sure how to
best compare them.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
2003-07-22 17:19 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-24 18:29 ` Re: Tuning PostgreSQL Greg Stark <gsstark@mit.edu>
2003-07-29 15:14 ` Re: Tuning PostgreSQL Vivek Khera <khera@kcilink.com>
@ 2003-07-29 15:46 ` Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 16:18 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 1 reply; 47+ messages in thread
From: Ron Johnson @ 2003-07-29 15:46 UTC (permalink / raw)
To: pgsql-performance
On Tue, 2003-07-29 at 10:14, Vivek Khera wrote:
> >>>>> "GS" == Greg Stark <gsstark@mit.edu> writes:
>
> GS> "scott.marlowe" <scott.marlowe@ihs.com> writes:
>
> GS> But you have to actually test your setup in practice to see if it
> GS> hurts. A big data warehousing system will be faster under RAID5
> GS> than under RAID1+0 because of the extra disks in the
> GS> stripeset. The more disks in the stripeset the more bandwidth you
> GS> get.
>
> Anyone have ideas on 14 spindles? I just ordered a disk subsystem
> with 14 high speed (U320 15kRPM) SCSI disks to hook up with a dell
> PERC3/DC controller (only 128MB cache, though).
14 drives on one SCSI card, eh? I'd be worried about saturating
the bus.
Maybe it's an old rule of thumb, but I would fill a SCSI chain
more than half full.
> My plan was to do RAID10, but I think I'll play with RAID5 again and
> see which gives me the best performance. Unfortunatly, it is
> difficult to recreate the highly fragmented tables I have now (vacuum
> full takes over 14 hours on one of the tables) so I'm not sure how to
> best compare them.
Also IMO: if I needed something *really* high performance, I'd
start with a mobo that has dual PCI buses (133MB/s can get swamped
quickly by U320 devices) or PCI-X (but they're so new...).
Then:
- get dual U320 SCSI cards (one for each PCI bus)
- plug them into dual redundant fast path external storage controllers
that have, oh, 512MB RAM cache *each*)
- dual port the drives, so they plug into both storage controllers
Since I wouldn't put 14 drives on one SCSI chain, double what I
just said, and only plug 7 drives in each controller.
If your app needs One Big Honkin' Device, use the Linux Volume
Manager (LVM) to merge the 2 RAID logical devices into one "super-
logical" device.
Yes, that's lot's of money, but is the data, and speed, important
enough?
--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
2003-07-22 17:19 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-24 18:29 ` Re: Tuning PostgreSQL Greg Stark <gsstark@mit.edu>
2003-07-29 15:14 ` Re: Tuning PostgreSQL Vivek Khera <khera@kcilink.com>
2003-07-29 15:46 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
@ 2003-07-29 16:18 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-29 18:08 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
0 siblings, 1 reply; 47+ messages in thread
From: scott.marlowe @ 2003-07-29 16:18 UTC (permalink / raw)
To: Ron Johnson <ron.l.johnson@cox.net>; +Cc: pgsql-performance
On 29 Jul 2003, Ron Johnson wrote:
> On Tue, 2003-07-29 at 10:14, Vivek Khera wrote:
> > >>>>> "GS" == Greg Stark <gsstark@mit.edu> writes:
> >
> > GS> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> >
> > GS> But you have to actually test your setup in practice to see if it
> > GS> hurts. A big data warehousing system will be faster under RAID5
> > GS> than under RAID1+0 because of the extra disks in the
> > GS> stripeset. The more disks in the stripeset the more bandwidth you
> > GS> get.
> >
> > Anyone have ideas on 14 spindles? I just ordered a disk subsystem
> > with 14 high speed (U320 15kRPM) SCSI disks to hook up with a dell
> > PERC3/DC controller (only 128MB cache, though).
>
> 14 drives on one SCSI card, eh? I'd be worried about saturating
> the bus.
I'm pretty sure those PERCs are based on the megaraid cards, which can
handle 3 or 4 channels each...
> Maybe it's an old rule of thumb, but I would fill a SCSI chain
> more than half full.
It's an old rule of thumb, but it still applies, it just takes more drives
to saturate the channel. Figure ~ 30 to 50 MBytes a second per drive, on
a U320 port it would take 10 drives to saturate it, and considering random
accesses will be much slower than the max ~30 megs a second off the
platter rate, it might take more than the max 14 drives to saturate U320.
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
2003-07-22 17:19 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-24 18:29 ` Re: Tuning PostgreSQL Greg Stark <gsstark@mit.edu>
2003-07-29 15:14 ` Re: Tuning PostgreSQL Vivek Khera <khera@kcilink.com>
2003-07-29 15:46 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 16:18 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
@ 2003-07-29 18:08 ` Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 19:00 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 47+ messages in thread
From: Ron Johnson @ 2003-07-29 18:08 UTC (permalink / raw)
To: pgsql-performance
On Tue, 2003-07-29 at 11:18, scott.marlowe wrote:
> On 29 Jul 2003, Ron Johnson wrote:
>
> > On Tue, 2003-07-29 at 10:14, Vivek Khera wrote:
> > > >>>>> "GS" == Greg Stark <gsstark@mit.edu> writes:
> > >
> > > GS> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > >
> > > GS> But you have to actually test your setup in practice to see if it
> > > GS> hurts. A big data warehousing system will be faster under RAID5
> > > GS> than under RAID1+0 because of the extra disks in the
> > > GS> stripeset. The more disks in the stripeset the more bandwidth you
> > > GS> get.
> > >
> > > Anyone have ideas on 14 spindles? I just ordered a disk subsystem
> > > with 14 high speed (U320 15kRPM) SCSI disks to hook up with a dell
> > > PERC3/DC controller (only 128MB cache, though).
> >
> > 14 drives on one SCSI card, eh? I'd be worried about saturating
> > the bus.
>
> I'm pretty sure those PERCs are based on the megaraid cards, which can
> handle 3 or 4 channels each...
Each with 14 devices? If so, isn't that a concentrated point of
failure, even if the channels are 1/2 full?
> > Maybe it's an old rule of thumb, but I would fill a SCSI chain
> > more than half full.
>
> It's an old rule of thumb, but it still applies, it just takes more drives
> to saturate the channel. Figure ~ 30 to 50 MBytes a second per drive, on
> a U320 port it would take 10 drives to saturate it, and considering random
> accesses will be much slower than the max ~30 megs a second off the
> platter rate, it might take more than the max 14 drives to saturate U320.
Ok. You'd still saturate the 133MB/s PCI bus at 133/30 = 4.4 drives.
--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
2003-07-22 17:19 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-24 18:29 ` Re: Tuning PostgreSQL Greg Stark <gsstark@mit.edu>
2003-07-29 15:14 ` Re: Tuning PostgreSQL Vivek Khera <khera@kcilink.com>
2003-07-29 15:46 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 16:18 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-29 18:08 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
@ 2003-07-29 19:00 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-29 19:50 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
0 siblings, 1 reply; 47+ messages in thread
From: scott.marlowe @ 2003-07-29 19:00 UTC (permalink / raw)
To: Ron Johnson <ron.l.johnson@cox.net>; +Cc: pgsql-performance
On 29 Jul 2003, Ron Johnson wrote:
> On Tue, 2003-07-29 at 11:18, scott.marlowe wrote:
> > On 29 Jul 2003, Ron Johnson wrote:
> >
> > > On Tue, 2003-07-29 at 10:14, Vivek Khera wrote:
> > > > >>>>> "GS" == Greg Stark <gsstark@mit.edu> writes:
> > > >
> > > > GS> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > > >
> > > > GS> But you have to actually test your setup in practice to see if it
> > > > GS> hurts. A big data warehousing system will be faster under RAID5
> > > > GS> than under RAID1+0 because of the extra disks in the
> > > > GS> stripeset. The more disks in the stripeset the more bandwidth you
> > > > GS> get.
> > > >
> > > > Anyone have ideas on 14 spindles? I just ordered a disk subsystem
> > > > with 14 high speed (U320 15kRPM) SCSI disks to hook up with a dell
> > > > PERC3/DC controller (only 128MB cache, though).
> > >
> > > 14 drives on one SCSI card, eh? I'd be worried about saturating
> > > the bus.
> >
> > I'm pretty sure those PERCs are based on the megaraid cards, which can
> > handle 3 or 4 channels each...
>
> Each with 14 devices? If so, isn't that a concentrated point of
> failure, even if the channels are 1/2 full?
Yep. I've built one once before when BIG hard drives were 9 gigs. :-)
And it is a point of concentrated failure, which brings me to my favorite
part about the LSI megaraid cards (which most / all perc3s are
apparently.)
If you build a RAID1+0 or 0+1, you can seperate it out so each sub part is
on it's own card, and the other cards keep acting like one big card.
Assuming the bad card isn't killing your PCI bus or draining the 12V rail
or something.
> > > Maybe it's an old rule of thumb, but I would fill a SCSI chain
> > > more than half full.
> >
> > It's an old rule of thumb, but it still applies, it just takes more drives
> > to saturate the channel. Figure ~ 30 to 50 MBytes a second per drive, on
> > a U320 port it would take 10 drives to saturate it, and considering random
> > accesses will be much slower than the max ~30 megs a second off the
> > platter rate, it might take more than the max 14 drives to saturate U320.
>
> Ok. You'd still saturate the 133MB/s PCI bus at 133/30 = 4.4 drives.
But that's seq scan. For many database applications, random access
performance is much more important. Imagine 200 people entering
reservations of 8k or less each into a transaction processing engine.
Each transactions chance to hit an unoccupied spindle is what really
counts. If there's 30 spindles, each doing a stripe's worth of access all
the time, it's likely to never flood the channel.
If random access is 1/4th the speed of seq scan, then you need to multiply
it by 4 to get the number of drives that'd saturate the PCI bus.
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
2003-07-22 17:19 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-24 18:29 ` Re: Tuning PostgreSQL Greg Stark <gsstark@mit.edu>
2003-07-29 15:14 ` Re: Tuning PostgreSQL Vivek Khera <khera@kcilink.com>
2003-07-29 15:46 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 16:18 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-29 18:08 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 19:00 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
@ 2003-07-29 19:50 ` Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 20:09 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-31 18:57 ` Re: Tuning PostgreSQL Vivek Khera <khera@kcilink.com>
0 siblings, 2 replies; 47+ messages in thread
From: Ron Johnson @ 2003-07-29 19:50 UTC (permalink / raw)
To: pgsql-performance
On Tue, 2003-07-29 at 14:00, scott.marlowe wrote:
> On 29 Jul 2003, Ron Johnson wrote:
>
> > On Tue, 2003-07-29 at 11:18, scott.marlowe wrote:
> > > On 29 Jul 2003, Ron Johnson wrote:
> > >
> > > > On Tue, 2003-07-29 at 10:14, Vivek Khera wrote:
> > > > > >>>>> "GS" == Greg Stark <gsstark@mit.edu> writes:
> > > > >
> > > > > GS> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > > > >
> > > > > GS> But you have to actually test your setup in practice to see if it
> > > > > GS> hurts. A big data warehousing system will be faster under RAID5
> > > > > GS> than under RAID1+0 because of the extra disks in the
> > > > > GS> stripeset. The more disks in the stripeset the more bandwidth you
> > > > > GS> get.
> > > > >
> > > > > Anyone have ideas on 14 spindles? I just ordered a disk subsystem
> > > > > with 14 high speed (U320 15kRPM) SCSI disks to hook up with a dell
> > > > > PERC3/DC controller (only 128MB cache, though).
> > > >
> > > > 14 drives on one SCSI card, eh? I'd be worried about saturating
> > > > the bus.
> > >
> > > I'm pretty sure those PERCs are based on the megaraid cards, which can
> > > handle 3 or 4 channels each...
> >
> > Each with 14 devices? If so, isn't that a concentrated point of
> > failure, even if the channels are 1/2 full?
>
> Yep. I've built one once before when BIG hard drives were 9 gigs. :-)
>
> And it is a point of concentrated failure, which brings me to my favorite
> part about the LSI megaraid cards (which most / all perc3s are
> apparently.)
>
> If you build a RAID1+0 or 0+1, you can seperate it out so each sub part is
> on it's own card, and the other cards keep acting like one big card.
> Assuming the bad card isn't killing your PCI bus or draining the 12V rail
> or something.
Sounds like my kinda card!
Is the cache battery-backed up?
How much cache can you stuff in them?
> > > > Maybe it's an old rule of thumb, but I would fill a SCSI chain
> > > > more than half full.
> > >
> > > It's an old rule of thumb, but it still applies, it just takes more drives
> > > to saturate the channel. Figure ~ 30 to 50 MBytes a second per drive, on
> > > a U320 port it would take 10 drives to saturate it, and considering random
> > > accesses will be much slower than the max ~30 megs a second off the
> > > platter rate, it might take more than the max 14 drives to saturate U320.
> >
> > Ok. You'd still saturate the 133MB/s PCI bus at 133/30 = 4.4 drives.
>
> But that's seq scan. For many database applications, random access
> performance is much more important. Imagine 200 people entering
> reservations of 8k or less each into a transaction processing engine.
> Each transactions chance to hit an unoccupied spindle is what really
> counts. If there's 30 spindles, each doing a stripe's worth of access all
> the time, it's likely to never flood the channel.
>
> If random access is 1/4th the speed of seq scan, then you need to multiply
> it by 4 to get the number of drives that'd saturate the PCI bus.
Maybe it's just me, but I've never seen a purely TP system.
Even if roll off the daily updates to a "reporting database" each
night, some yahoo manager with enough juice to have his way still
wants up-to-the-minute reports...
Better yet, the Access Jockey, who thinks s/he's an SQL whiz but
couldn't JOIN himself out of a paper bag...
--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
2003-07-22 17:19 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-24 18:29 ` Re: Tuning PostgreSQL Greg Stark <gsstark@mit.edu>
2003-07-29 15:14 ` Re: Tuning PostgreSQL Vivek Khera <khera@kcilink.com>
2003-07-29 15:46 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 16:18 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-29 18:08 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 19:00 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-29 19:50 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
@ 2003-07-29 20:09 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-29 20:38 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
1 sibling, 1 reply; 47+ messages in thread
From: scott.marlowe @ 2003-07-29 20:09 UTC (permalink / raw)
To: Ron Johnson <ron.l.johnson@cox.net>; +Cc: pgsql-performance
On 29 Jul 2003, Ron Johnson wrote:
> On Tue, 2003-07-29 at 14:00, scott.marlowe wrote:
> > On 29 Jul 2003, Ron Johnson wrote:
> >
> > > On Tue, 2003-07-29 at 11:18, scott.marlowe wrote:
> > > > On 29 Jul 2003, Ron Johnson wrote:
> > > >
> > > > > On Tue, 2003-07-29 at 10:14, Vivek Khera wrote:
> > > > > > >>>>> "GS" == Greg Stark <gsstark@mit.edu> writes:
> > > > > >
> > > > > > GS> "scott.marlowe" <scott.marlowe@ihs.com> writes:
> > > > > >
> > > > > > GS> But you have to actually test your setup in practice to see if it
> > > > > > GS> hurts. A big data warehousing system will be faster under RAID5
> > > > > > GS> than under RAID1+0 because of the extra disks in the
> > > > > > GS> stripeset. The more disks in the stripeset the more bandwidth you
> > > > > > GS> get.
> > > > > >
> > > > > > Anyone have ideas on 14 spindles? I just ordered a disk subsystem
> > > > > > with 14 high speed (U320 15kRPM) SCSI disks to hook up with a dell
> > > > > > PERC3/DC controller (only 128MB cache, though).
> > > > >
> > > > > 14 drives on one SCSI card, eh? I'd be worried about saturating
> > > > > the bus.
> > > >
> > > > I'm pretty sure those PERCs are based on the megaraid cards, which can
> > > > handle 3 or 4 channels each...
> > >
> > > Each with 14 devices? If so, isn't that a concentrated point of
> > > failure, even if the channels are 1/2 full?
> >
> > Yep. I've built one once before when BIG hard drives were 9 gigs. :-)
> >
> > And it is a point of concentrated failure, which brings me to my favorite
> > part about the LSI megaraid cards (which most / all perc3s are
> > apparently.)
> >
> > If you build a RAID1+0 or 0+1, you can seperate it out so each sub part is
> > on it's own card, and the other cards keep acting like one big card.
> > Assuming the bad card isn't killing your PCI bus or draining the 12V rail
> > or something.
>
> Sounds like my kinda card!
>
> Is the cache battery-backed up?
Yep
> How much cache can you stuff in them?
the old old old school MegaRAID428 could hold up to 128 Meg. I'm sure the
new ones can handle 512Meg or more.
> > > > > Maybe it's an old rule of thumb, but I would fill a SCSI chain
> > > > > more than half full.
> > > >
> > > > It's an old rule of thumb, but it still applies, it just takes more drives
> > > > to saturate the channel. Figure ~ 30 to 50 MBytes a second per drive, on
> > > > a U320 port it would take 10 drives to saturate it, and considering random
> > > > accesses will be much slower than the max ~30 megs a second off the
> > > > platter rate, it might take more than the max 14 drives to saturate U320.
> > >
> > > Ok. You'd still saturate the 133MB/s PCI bus at 133/30 = 4.4 drives.
> >
> > But that's seq scan. For many database applications, random access
> > performance is much more important. Imagine 200 people entering
> > reservations of 8k or less each into a transaction processing engine.
> > Each transactions chance to hit an unoccupied spindle is what really
> > counts. If there's 30 spindles, each doing a stripe's worth of access all
> > the time, it's likely to never flood the channel.
> >
> > If random access is 1/4th the speed of seq scan, then you need to multiply
> > it by 4 to get the number of drives that'd saturate the PCI bus.
>
> Maybe it's just me, but I've never seen a purely TP system.
I think most of them are running under TPF on a mainframe in a basement
somewhere, like for airline reservations. I've never worked on one, but
met one of the guys who runs one, and they use 12 mainframes for 6 live
machines and each live machine has a failover machine behind it in sysplex
mode. I kept thinking of the giant dinosaurs in Jurassic park...
> Even if roll off the daily updates to a "reporting database" each
> night, some yahoo manager with enough juice to have his way still
> wants up-to-the-minute reports...
Just because it's TP doesn't mean it doesn't have real time reporting.
But expensive reports probably do get run at night.
> Better yet, the Access Jockey, who thinks s/he's an SQL whiz but
> couldn't JOIN himself out of a paper bag...
I've seen a few who got joins and unions and what not, but explaining fks
or transactions got me a glazed look... :-)
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
2003-07-22 17:19 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-24 18:29 ` Re: Tuning PostgreSQL Greg Stark <gsstark@mit.edu>
2003-07-29 15:14 ` Re: Tuning PostgreSQL Vivek Khera <khera@kcilink.com>
2003-07-29 15:46 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 16:18 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-29 18:08 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 19:00 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-29 19:50 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 20:09 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
@ 2003-07-29 20:38 ` Ron Johnson <ron.l.johnson@cox.net>
0 siblings, 0 replies; 47+ messages in thread
From: Ron Johnson @ 2003-07-29 20:38 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: pgsql-performance
On Tue, 2003-07-29 at 15:09, scott.marlowe wrote:
> On 29 Jul 2003, Ron Johnson wrote:
>
> > On Tue, 2003-07-29 at 14:00, scott.marlowe wrote:
> > > On 29 Jul 2003, Ron Johnson wrote:
> > >
> > > > On Tue, 2003-07-29 at 11:18, scott.marlowe wrote:
> > > > > On 29 Jul 2003, Ron Johnson wrote:
> > > > >
> > > > > > On Tue, 2003-07-29 at 10:14, Vivek Khera wrote:
> > > > > > > >>>>> "GS" == Greg Stark <gsstark@mit.edu> writes:
> > > > > > >
> > > > > > > GS> "scott.marlowe" <scott.marlowe@ihs.com> writes:
[snip]
> > > But that's seq scan. For many database applications, random access
> > > performance is much more important. Imagine 200 people entering
> > > reservations of 8k or less each into a transaction processing engine.
> > > Each transactions chance to hit an unoccupied spindle is what really
> > > counts. If there's 30 spindles, each doing a stripe's worth of access all
> > > the time, it's likely to never flood the channel.
> > >
> > > If random access is 1/4th the speed of seq scan, then you need to multiply
> > > it by 4 to get the number of drives that'd saturate the PCI bus.
> >
> > Maybe it's just me, but I've never seen a purely TP system.
>
> I think most of them are running under TPF on a mainframe in a basement
> somewhere, like for airline reservations. I've never worked on one, but
> met one of the guys who runs one, and they use 12 mainframes for 6 live
> machines and each live machine has a failover machine behind it in sysplex
> mode. I kept thinking of the giant dinosaurs in Jurassic park...
We have something similar running on Alphas and VMS; does about
8M Txn/day. Anyone who uses E-ZPass in the northeast eventually
gets stuck in our systems.
(Made me fear Big Brother...)
> > Even if roll off the daily updates to a "reporting database" each
> > night, some yahoo manager with enough juice to have his way still
> > wants up-to-the-minute reports...
>
> Just because it's TP doesn't mean it doesn't have real time reporting.
> But expensive reports probably do get run at night.
Yes, but... There's always the exception.
> > Better yet, the Access Jockey, who thinks s/he's an SQL whiz but
> > couldn't JOIN himself out of a paper bag...
>
> I've seen a few who got joins and unions and what not, but explaining fks
> or transactions got me a glazed look... :-)
Wow! They understood joins? You lucky dog!!!
--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
2003-07-22 17:19 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-24 18:29 ` Re: Tuning PostgreSQL Greg Stark <gsstark@mit.edu>
2003-07-29 15:14 ` Re: Tuning PostgreSQL Vivek Khera <khera@kcilink.com>
2003-07-29 15:46 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 16:18 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-29 18:08 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 19:00 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-29 19:50 ` Re: Tuning PostgreSQL Ron Johnson <ron.l.johnson@cox.net>
@ 2003-07-31 18:57 ` Vivek Khera <khera@kcilink.com>
1 sibling, 0 replies; 47+ messages in thread
From: Vivek Khera @ 2003-07-31 18:57 UTC (permalink / raw)
To: pgsql-performance
>>>>> "RJ" == Ron Johnson <ron.l.johnson@cox.net> writes:
RJ> On Tue, 2003-07-29 at 14:00, scott.marlowe wrote:
RJ> Sounds like my kinda card!
RJ> Is the cache battery-backed up?
yep
RJ> How much cache can you stuff in them?
as per dell, the max is 128Mb, which was a bummer.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 14:33 ` Re: Tuning PostgreSQL Jim C. Nasby <jim@nasby.net>
2003-07-22 17:19 ` Re: Tuning PostgreSQL scott.marlowe <scott.marlowe@ihs.com>
2003-07-24 18:29 ` Re: Tuning PostgreSQL Greg Stark <gsstark@mit.edu>
2003-07-29 15:14 ` Re: Tuning PostgreSQL Vivek Khera <khera@kcilink.com>
@ 2003-07-29 16:16 ` Will LaShell <will@lashell.net>
1 sibling, 0 replies; 47+ messages in thread
From: Will LaShell @ 2003-07-29 16:16 UTC (permalink / raw)
To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance
On Tue, 2003-07-29 at 08:14, Vivek Khera wrote:
> >>>>> "GS" == Greg Stark <gsstark@mit.edu> writes:
>
> GS> "scott.marlowe" <scott.marlowe@ihs.com> writes:
>
> GS> But you have to actually test your setup in practice to see if it
> GS> hurts. A big data warehousing system will be faster under RAID5
> GS> than under RAID1+0 because of the extra disks in the
> GS> stripeset. The more disks in the stripeset the more bandwidth you
> GS> get.
>
> Anyone have ideas on 14 spindles? I just ordered a disk subsystem
> with 14 high speed (U320 15kRPM) SCSI disks to hook up with a dell
> PERC3/DC controller (only 128MB cache, though).
<SNIP>
Hey one comment on this. With dell Perc3/DC you should check the
megaraid-devel list to find the best BIOS settings for maximum
performance. There have been many comments on it and trials to get it
going really well. All told though I totally love the LSI Megaraid (
which is what the perc3/dc is ) controllers. We use the Elite 1650 with
seagate cheetah drives for a nice little array.
--Will
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 47+ messages in thread
* Re: Tuning PostgreSQL
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Re: Tuning PostgreSQL Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Re: Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Re: Tuning PostgreSQL Vincent van Leeuwen <pgsql.spam@vinz.nl>
@ 2003-07-22 17:10 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 47+ messages in thread
From: Bruno Wolff III @ 2003-07-22 17:10 UTC (permalink / raw)
To: pgsql-performance
On Tue, Jul 22, 2003 at 11:40:35 +0200,
Vincent van Leeuwen <pgsql.spam@vinz.nl> wrote:
>
> About RAID types: the fastest RAID type by far is RAID-10. However, this will
> cost you a lot of useable diskspace, so it isn't for everyone. You need at
> least 4 disks for a RAID-10 array. RAID-5 is a nice compromise if you want as
> much useable diskspace as possible and still want to be redundant. RAID-1 is
> very useful for small (2-disk) arrays.
Note that while raid 10 requires 4 disks, you get the space of 2 disks.
This is the same ratio as for raid 1.
^ permalink raw reply [nested|flat] 47+ messages in thread
* Tuning PostgreSQL
@ 2005-04-01 14:54 Mindaugas Riauba <mind@bi.lt>
0 siblings, 0 replies; 47+ messages in thread
From: Mindaugas Riauba @ 2005-04-01 14:54 UTC (permalink / raw)
To: pgsql-performance
Hello,
What would be reasonable settings for quite heavily used
but not large database?
Dabatase is under 1G in size and fits into server cache (server
has 2GB of memeory). Two of most used tables are ~100k rows each
but they get up to 50inserts/updates/deletes per second.
How to tweak fsm (?) and pg_auovacuum settings for such case?
What I do not like about one table is "unused item pointers" number.
Now I use max_fsm_relations=1000 and max_fsm_pages=200000.
pg_autovacuum ran with default settings.
Thanks,
Mindaugas
# VACUUM VERBOSE msq;
INFO: vacuuming "msq"
INFO: index "msq_next" now contains 74983 row versions in 537 pages
DETAIL: 75963 index row versions were removed.
123 index pages have been deleted, 0 are currently reusable.
CPU 0.05s/0.13u sec elapsed 2.00 sec.
INFO: index "msq_recipient_idx" now contains 75014 row versions in 740
pages
DETAIL: 75963 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.16u sec elapsed 0.17 sec.
INFO: index "msq_id_pk" now contains 75065 row versions in 396 pages
DETAIL: 75963 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.15u sec elapsed 0.17 sec.
INFO: "msq": removed 75963 row versions in 6118 pages
DETAIL: CPU 0.62s/0.56u sec elapsed 17.02 sec.
INFO: "msq": found 75963 removable, 74553 nonremovable row versions in
49386 pages
DETAIL: 1221 dead row versions cannot be removed yet.
There were 1634616 unused item pointers.
0 pages are entirely empty.
CPU 1.36s/1.24u sec elapsed 33.23 sec.
^ permalink raw reply [nested|flat] 47+ messages in thread
end of thread, other threads:[~2005-04-01 14:54 UTC | newest]
Thread overview: 47+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-13 10:11 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 08:31 Tuning PostgreSQL Alexander Priem <ap@cict.nl>
2003-07-21 08:51 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 09:33 Re: Tuning PostgreSQL Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 09:40 ` Alexander Priem <ap@cict.nl>
2003-07-21 10:09 ` Ang Chin Han <angch@bytecraft.com.my>
2003-07-21 10:31 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 11:27 ` Ang Chin Han <angch@bytecraft.com.my>
2003-07-21 11:39 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 11:45 ` Alexander Priem <ap@cict.nl>
2003-07-21 12:05 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-21 12:43 ` Alexander Priem <ap@cict.nl>
2003-07-22 09:52 ` Ron Johnson <ron.l.johnson@cox.net>
2003-07-22 12:53 ` Alexander Priem <ap@cict.nl>
2003-07-22 14:37 ` Ron Johnson <ron.l.johnson@cox.net>
2003-07-25 10:45 ` Andrew McMillan <andrew@catalyst.net.nz>
2003-07-26 10:18 ` Arjen van der Meijden <acmmailing@vulcanus.its.tudelft.nl>
2003-07-26 14:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-07-26 21:08 ` Balazs Wellisch <balazs@neusolutions.com>
2003-07-28 17:10 ` Josh Berkus <josh@agliodbs.com>
2003-07-21 13:45 Re: Tuning PostgreSQL Roman Fail <rfail@posportal.com>
2003-07-21 14:00 ` Alexander Priem <ap@cict.nl>
2003-07-21 16:06 ` Josh Berkus <josh@agliodbs.com>
2003-07-21 16:28 ` Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 07:04 ` Alexander Priem <ap@cict.nl>
2003-07-22 09:40 ` Vincent van Leeuwen <pgsql.spam@vinz.nl>
2003-07-22 13:27 ` Alexander Priem <ap@cict.nl>
2003-07-22 14:12 ` Andrew Sullivan <andrew@libertyrms.info>
2003-07-22 14:33 ` Jim C. Nasby <jim@nasby.net>
2003-07-22 15:01 ` Alexander Priem <ap@cict.nl>
2003-07-22 15:36 ` Ron Johnson <ron.l.johnson@cox.net>
2003-07-22 16:12 ` Vivek Khera <khera@kcilink.com>
2003-07-22 17:19 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-24 18:29 ` Greg Stark <gsstark@mit.edu>
2003-07-24 20:13 ` Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 15:14 ` Vivek Khera <khera@kcilink.com>
2003-07-29 15:46 ` Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 16:18 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-29 18:08 ` Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 19:00 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-29 19:50 ` Ron Johnson <ron.l.johnson@cox.net>
2003-07-29 20:09 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-29 20:38 ` Ron Johnson <ron.l.johnson@cox.net>
2003-07-31 18:57 ` Vivek Khera <khera@kcilink.com>
2003-07-29 16:16 ` Will LaShell <will@lashell.net>
2003-07-22 17:10 ` Bruno Wolff III <bruno@wolff.to>
2005-04-01 14:54 Tuning PostgreSQL Mindaugas Riauba <mind@bi.lt>
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