pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndexes on ramdisk
6+ messages / 3 participants
[nested] [flat]
* Indexes on ramdisk
@ 2005-10-05 01:09 Emil Briggs <emil@baymountain.com>
0 siblings, 1 reply; 6+ messages in thread
From: Emil Briggs @ 2005-10-05 01:09 UTC (permalink / raw)
To: pgsql-performance
I have an application that has a table that is both read and write intensive.
Data from iostat indicates that the write speed of the system is the factor
that is limiting performance. The table has around 20 columns and most of the
columns are indexed. The data and the indices for the table are distributed
over several mirrored disk partitions and pg_xlog is on another. I'm looking
at ways to improve performance and besides the obvious one of getting an SSD
I thought about putting the indices on a ramdisk. That means that after a
power failure or shutdown I would have to recreate them but that is
acceptable for this application. What I am wondering though is whether or not
I would see much performance benefit and if there would be any startup
problems after a power down event due to the indices not being present. Any
insight would be appreciated.
Emil
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Indexes on ramdisk
@ 2005-10-05 03:23 Alex Turner <armtuk@gmail.com>
parent: Emil Briggs <emil@baymountain.com>
0 siblings, 1 reply; 6+ messages in thread
From: Alex Turner @ 2005-10-05 03:23 UTC (permalink / raw)
To: emil@baymountain.com; +Cc: pgsql-performance
Talk about your IO system a bit. There might be obvious ways to improve.
What System/Motherboard are you using?
What Controller Cards are you using?
What kind of Disks do you have (SATA, SCSI 7.6k 10k 15k)
What denominations (9, 18, 36, 72, 143, 80, 160, 200 240Gig)?
What kind of RAIDs do you have setup (How many drives what stripe sizes, how
many used for what).
What levels of RAID are you using (0,1,10,5,50)?
With good setup, a dual PCI-X bus motherboard can hit 2GB/sec and thousands
of transactions to disk if you have a controller/disks that can keep up.
That is typicaly enough for most people without resorting to SSD.
Alex Turner
NetEconomist
On 10/4/05, Emil Briggs <emil@baymountain.com> wrote:
>
>
> I have an application that has a table that is both read and write
> intensive.
> Data from iostat indicates that the write speed of the system is the
> factor
> that is limiting performance. The table has around 20 columns and most of
> the
> columns are indexed. The data and the indices for the table are
> distributed
> over several mirrored disk partitions and pg_xlog is on another. I'm
> looking
> at ways to improve performance and besides the obvious one of getting an
> SSD
> I thought about putting the indices on a ramdisk. That means that after a
> power failure or shutdown I would have to recreate them but that is
> acceptable for this application. What I am wondering though is whether or
> not
> I would see much performance benefit and if there would be any startup
> problems after a power down event due to the indices not being present.
> Any
> insight would be appreciated.
>
> Emil
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Indexes on ramdisk
@ 2005-10-05 03:41 Emil Briggs <emil@baymountain.com>
parent: Alex Turner <armtuk@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Emil Briggs @ 2005-10-05 03:41 UTC (permalink / raw)
To: pgsql-performance
> Talk about your IO system a bit. There might be obvious ways to improve.
>
> What System/Motherboard are you using?
> What Controller Cards are you using?
> What kind of Disks do you have (SATA, SCSI 7.6k 10k 15k)
> What denominations (9, 18, 36, 72, 143, 80, 160, 200 240Gig)?
> What kind of RAIDs do you have setup (How many drives what stripe sizes,
> how many used for what).
> What levels of RAID are you using (0,1,10,5,50)?
>
It's a quad opteron system. RAID controller is a 4 channel LSILogic Megaraid
320 connected to 10 15k 36.7G SCSI disks. The disks are configured in 5
mirrored partitions. The pg_xlog is on one mirror and the data and indexes
are spread over the other 4 using tablespaces. These numbers from
pg_stat_user_tables are from about 2 hours earlier today on this one table.
idx_scan 20578690
idx_tup_fetch 35866104841
n_tup_ins 1940081
n_tup_upd 1604041
n_tup_del 1880424
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Indexes on ramdisk
@ 2005-10-05 14:55 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 6+ messages in thread
From: Merlin Moncure @ 2005-10-05 14:55 UTC (permalink / raw)
To: emil@baymountain.com; +Cc: pgsql-performance
> It's a quad opteron system. RAID controller is a 4 channel LSILogic
> Megaraid
> 320 connected to 10 15k 36.7G SCSI disks. The disks are configured in
5
> mirrored partitions. The pg_xlog is on one mirror and the data and
indexes
> are spread over the other 4 using tablespaces. These numbers from
> pg_stat_user_tables are from about 2 hours earlier today on this one
> table.
>
>
> idx_scan 20578690
> idx_tup_fetch 35866104841
> n_tup_ins 1940081
> n_tup_upd 1604041
> n_tup_del 1880424
Is your raid controller configured to buffer your writes? How much RAM
are you packing? Are you running 64 bit?
Merlin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Indexes on ramdisk
@ 2005-10-05 15:03 Alex Turner <armtuk@gmail.com>
parent: Emil Briggs <emil@baymountain.com>
0 siblings, 1 reply; 6+ messages in thread
From: Alex Turner @ 2005-10-05 15:03 UTC (permalink / raw)
To: emil@baymountain.com; +Cc: pgsql-performance
What kind of order of improvement do you need to see?
What period are these number for? Were they collected over 1 hour, 1 day, 1
month?
How much Cache do you have on the controller?
You can certainly get more speed by adding more disk and possibly by adding
more controller RAM/a second controller. 10 disks isn't really that many for
a totally kick-ass DB server. You can acheive more block writes with RAID
10s than with RAID 1s. Wether it's cost effective is dependant on lots of
factors like your chassis and drive enclosures etc. vs SSD. SSD will be
faster, but last I heard was expensive, and I checked a few websites but
couldn't get much price info. Normaly when you can't get price info, thats a
bad sign ;). If you are doing large chunks of writes to a small number of
tables, then you might be better off with a single large RAID 10 for your
tablespace than with seperate RAID 1s. If you are writing 5 to 1 more table
data than index data, you are hurting yourself by seperating on to multiple
RAID 1s instead of a single RAID 10 which could write at 2-3x for the 5, and
2-3x for the 1 and only suffer a single seek penalty but get data onto disk
twice to three times as fast (depending how many RAID 1s you join). Try
unseperating RAID 1s, and combine to a RAID 10. for indexes and tablespaces.
The controller will re-sequence your writes/reads to help with effeciency,
and dbwriter is there to make things go easier.
You can at least get some idea by doing an iostat and see how many IOs and
how much throughput is happening. That will rappidly help determine if you
are bound by IOs or by MB/sec.
Worst case I'm wrong, but IMHO it's worth a try.
Alex Turner
NetEconomist
On 10/4/05, Emil Briggs <emil@baymountain.com> wrote:
>
> > Talk about your IO system a bit. There might be obvious ways to improve.
> >
> > What System/Motherboard are you using?
> > What Controller Cards are you using?
> > What kind of Disks do you have (SATA, SCSI 7.6k 10k 15k)
> > What denominations (9, 18, 36, 72, 143, 80, 160, 200 240Gig)?
> > What kind of RAIDs do you have setup (How many drives what stripe sizes,
> > how many used for what).
> > What levels of RAID are you using (0,1,10,5,50)?
> >
>
> It's a quad opteron system. RAID controller is a 4 channel LSILogic
> Megaraid
> 320 connected to 10 15k 36.7G SCSI disks. The disks are configured in 5
> mirrored partitions. The pg_xlog is on one mirror and the data and indexes
> are spread over the other 4 using tablespaces. These numbers from
> pg_stat_user_tables are from about 2 hours earlier today on this one
> table.
>
>
> idx_scan 20578690
> idx_tup_fetch 35866104841
> n_tup_ins 1940081
> n_tup_upd 1604041
> n_tup_del 1880424
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Indexes on ramdisk
@ 2005-10-05 23:46 Emil Briggs <emil@baymountain.com>
parent: Alex Turner <armtuk@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Emil Briggs @ 2005-10-05 23:46 UTC (permalink / raw)
To: pgsql-performance
> What kind of order of improvement do you need to see?
>
A lot since the load on the system is expected to increase by up to 100% over
the next 6 months.
> What period are these number for? Were they collected over 1 hour, 1 day, 1
> month?
>
I thought I mentioned that in the earlier post but it was from a 2 hour
period. It's a busy system.
> How much Cache do you have on the controller?
>
64Mbytes but I don't think that's an issue. As I mentioned in the first post
the table that is the bottleneck has indexes on 15 columns and is seeing a
lot of inserts, deletes and updates. The indexes are spread out over the 5
mirrors but it's still a couple of writes per mirror for each operation. I'm
going to order an SSD which should give us a lot more headroom than trying to
rearrange the RAID setup.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-10-05 23:46 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-05 01:09 Indexes on ramdisk Emil Briggs <emil@baymountain.com>
2005-10-05 03:23 ` Alex Turner <armtuk@gmail.com>
2005-10-05 03:41 ` Emil Briggs <emil@baymountain.com>
2005-10-05 15:03 ` Alex Turner <armtuk@gmail.com>
2005-10-05 23:46 ` Emil Briggs <emil@baymountain.com>
2005-10-05 14:55 Re: Indexes on ramdisk Merlin Moncure <merlin.moncure@rcsonline.com>
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