Обсуждение: reconfiguring diskspace while upgrading to 8.2.5

Поиск
Список
Период
Сортировка

reconfiguring diskspace while upgrading to 8.2.5

От
"Mark Steben"
Дата:

Admin team,

 

We are upgrading from Postgres 7.4.5 to 8.2.5 in mid January.  We are also adding new disks

As we go.  I am investigating using the new tablespace facility to move our biggest and most accessed

Table to the new disk.  Here are the statistics.  We are running RAID 10

  Current database size -------------   63 GIG

  Heavy accessed table to move:

           Table -----------------------   7.4 GIG

           2 indexes -------------------   3 GIG apiece

           2 other indexes -------------   2.5 GIG apiece

 

Current database disk configuration

          TOTAL space -------------- 404 GIG

           TOTAL spindles -----------      3

           TOTAL mirrors ------------      3

 New additional disk configuration

           TOTAL  space ------------   290 GIG

           TOTAL spindles -----------       2

           TOTAL mirrors   -----------      2

 

The choices we see are:

    1. Adding the two new spindles to the other three making one huge logical partition

        And all 350+ tables and 400+ indexes continue to reside there

    2. Keeping the two new spindles separate and dedicating the heavy access table

         And its 4 indexes to it.

    3. Keeping the two new spindles separate and dedicating the heavy access table

          To it, but keeping the 4 indexes on the old partition.

 

I know that maintaining almost 700 GIG of total disk space being utilized by a 63 GIG

  Database looks like disk-space overkill but we do expect massive growth over the

  Next 2 – 3 years.

 

Any thoughts / comments would be appreciated.

 

Also are there tools out there that monitor disk I/O and disk speed?

 

Thanks for your time,      

 

Mark Steben

Senior Database Administrator
@utoRevenue™
A Dominion Enterprises Company
480 Pleasant Street
Suite B200
Lee, MA 01238
413-243-4800 Home Office 
413-243-4809 Corporate Fax

msteben@autorevenue.com

Visit our new website at
www.autorevenue.com

 

IMPORTANT: The information contained in this e-mail message is confidential and is intended only for the named addressee(s). If the reader of this e-mail message is not the intended recipient (or the individual responsible for the delivery of this e-mail message to the intended recipient), please be advised that any re-use, dissemination, distribution or copying of this e-mail message is prohibited.  If you have received this e-mail message in error, please reply to the sender that you have received this e-mail message in error and then delete it.  Thank you.

Come Visit Us at NADA!  Booth #5735N
February 9th - February 12th
Moscone Center in San Francisco, CA

 

Re: reconfiguring diskspace while upgrading to 8.2.5

От
Peter Eisentraut
Дата:
Am Freitag, 28. Dezember 2007 schrieb Mark Steben:
> As we go.  I am investigating using the new tablespace facility to move our
> biggest and most accessed

I didn't see anything in your description about having the WAL on a separate
disk.  You should include that in your plans.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: reconfiguring diskspace while upgrading to 8.2.5

От
"Mark Steben"
Дата:

Postgres admin team,

 

I posed this question last week.  Probably a bad week to ask questions as many people off

For the holidays so response was light.  Thanks to Pete Eisentraut for his feedback.

 

I’d like to pose the question again.  Our upgrade time-table is getting close.  Any and

All feedback would be appreciated.

 

Thank you,

Mark Steben

 


From: Mark Steben [mailto:msteben@autorevenue.com]
Sent: Friday, December 28, 2007 10:09 AM
To: 'pgsql-admin@postgresql.org'
Cc: 'rich@autorevenue.com'; 'David Parks'; 'Craig Brothers'
Subject: reconfiguring diskspace while upgrading to 8.2.5

 

Admin team,

 

We are upgrading from Postgres 7.4.5 to 8.2.5 in mid January.  We are also adding new disks

As we go.  I am investigating using the new tablespace facility to move our biggest and most accessed

Table to the new disk.  Here are the statistics.  We are running RAID 10

  Current database size -------------   63 GIG

  Heavy accessed table to move:

           Table -----------------------   7.4 GIG

           2 indexes -------------------   3 GIG apiece

           2 other indexes -------------   2.5 GIG apiece

 

Current database disk configuration

          TOTAL space -------------- 404 GIG

           TOTAL spindles -----------      3

           TOTAL mirrors ------------      3

 New additional disk configuration

           TOTAL  space ------------   290 GIG

           TOTAL spindles -----------       2

           TOTAL mirrors   -----------      2

 

The choices we see are:

    1. Adding the two new spindles to the other three making one huge logical partition

        And all 350+ tables and 400+ indexes continue to reside there

    2. Keeping the two new spindles separate and dedicating the heavy access table

         And its 4 indexes to it.

    3. Keeping the two new spindles separate and dedicating the heavy access table

          To it, but keeping the 4 indexes on the old partition.

 

I know that maintaining almost 700 GIG of total disk space being utilized by a 63 GIG

  Database looks like disk-space overkill but we do expect massive growth over the

  Next 2 – 3 years.

 

Any thoughts / comments would be appreciated.

 

Also are there tools out there that monitor disk I/O and disk speed?

 

Thanks for your time,      

 

Mark Steben

Senior Database Administrator
@utoRevenue™
A Dominion Enterprises Company
480 Pleasant Street
Suite B200
Lee, MA 01238
413-243-4800 Home Office 
413-243-4809 Corporate Fax

msteben@autorevenue.com

Visit our new website at
www.autorevenue.com

 

IMPORTANT: The information contained in this e-mail message is confidential and is intended only for the named addressee(s). If the reader of this e-mail message is not the intended recipient (or the individual responsible for the delivery of this e-mail message to the intended recipient), please be advised that any re-use, dissemination, distribution or copying of this e-mail message is prohibited.  If you have received this e-mail message in error, please reply to the sender that you have received this e-mail message in error and then delete it.  Thank you.

Come Visit Us at NADA!  Booth #5735N
February 9th - February 12th
Moscone Center in San Francisco, CA

 

Re: reconfiguring diskspace while upgrading to 8.2.5

От
"Scott Marlowe"
Дата:
On Jan 2, 2008 9:04 AM, Mark Steben <msteben@autorevenue.com> wrote:
>
> Postgres admin team,
>
> We are upgrading from Postgres 7.4.5 to 8.2.5 in mid January.  We are also
> adding new disks
>
> As we go.  I am investigating using the new tablespace facility to move our
> biggest and most accessed

I think my decision would be driven pretty strongly by what type of
RAID controller (if any) and whether it had battery backed cache
etc...

Often, with a good controller with bbu cache, you're best off putting
all your disks into one big RAID-10 and letting the RAID controller
sort it out.

For monitoring IO, use iostat and / or vmstat.

Re: reconfiguring diskspace while upgrading to 8.2.5

От
Andrew Sullivan
Дата:
On Wed, Jan 02, 2008 at 10:04:53AM -0500, Mark Steben wrote:
> The choices we see are:

[. . .]

I think this is likely to depend almost entirely on benchmarking your case,
and testing the different possibilities.  That said, except in extremely
well-defined cases, it's often not a bad idea to make one large RAID anyway,
because you sometimes find that your assumptions about your access patterns
were wrong, and you've optimised for the wrong thing.  In that case, the
single large RAID offers greater flexibility, and if your controller is
smart enough, the advantages are small enough that you don't gain enough for
the loss in flexibility.

I also want to point out that you need to test your workload _on 8.2_, and
not make assumptions about disk use based on any experience you have gained
from your production systems.  Build a benchmark that looks like your
production traffic, by all means, but don't extrapolate from 7.x to later
versions for I/O information.  There have been several huge strides in
recent releases in reducing unecessary I/O, and 7.4 is susceptible to
"checkpoint storms" that make even the most exotic storage hardware fail to
perform well under some circumstances.

Are your estimates of size, &c., based on a fresh load of the data?  There
are some lost-space issues in 7.4 that are solved in later releases, so you
might find that some of your estimates of size are a little high.  This
matters for I/O, and might also affect your decision.

> Also are there tools out there that monitor disk I/O and disk speed?

Your OS should provide several.  The basic ones are iostat and vmstat, but
AIX has its own totally strange variants, and Solaris has some marvellous
I/O tools.

A