Re: Question about disk layout for new OLAP db

Hello!

We're planning to have OpenSQL Camp in Portland. The event will be
free, and we're hoping to have 120 people there. We've started a wiki
page for adding yourself to the attendee list, and for helping with
planning!

http://opensqlcamp.org/Events/Portland2009/

We'll keep you posted as details evolve! Feel free to pass this on to
groups you think might be interested.

-selena

---------- Forwarded message ----------
From: Eric Day <eday(at)oddments(dot)org>
Date: Wed, Jul 29, 2009 at 3:15 PM
Subject: [OpenSQL Camp] OpenSQL Camp, November 2009 in Portland, OR
To: opensqlcamp(at)googlegroups(dot)com

Hi everyone!

Selena Deckelmann and I are organizing another OpenSQL Camp that will
be happening November 14th and 15th in Portland, OR. We have the space
confirmed and will be sending out more details soon. There are direct
flights to Portland from many locations in the US, and the venue is
right off public transportation (train), so it should be convenient
for everyone to get to.

We're starting to put together information on the wiki and will
send out updates with new content. We are paying for the space and
will be providing snacks, coffee, and lunches, so we are looking for
sponsors. Get in touch if you are interested!

More details coming soon.

-Eric

--
http://chesnok.com/daily - me
http://endpoint.com - work

I am new to PostgreSQL. I'm setting up a new PostgreSQL server to handle large amounts of performance data collected from our batch processing pools, we're currently handling about 8M rows of new data per day stored into tables with about 6B rows total. The server I have has eight cores and 32GB memory, with 9TB of fibre channel storage. I'm using LVM2 for disk allocation and XFS so the volume sizes can grow as needed. The default installation of PostgreSQL 8.4 uses one disk for both data and logs. Normally you would allocate multiple data disks and a transaction log disk. I have not seen anywhere in the documentation or configuration files where you can specify extra data disks or separate log disks.

Hi!

On Mon, Aug 3, 2009 at 6:51 AM, Ewan, Michael<michael(dot)ewan(at)intel(dot)com> wrote:
>
> I am new to PostgreSQL.  I'm setting up a new PostgreSQL server to handle large amounts of performance data collected from our batch processing pools, we're currently handling about 8M rows of new data per day stored into tables with about 6B rows total.  The server I have has eight cores and 32GB memory, with 9TB of fibre channel storage.  I'm using LVM2 for disk allocation and XFS so the volume sizes can grow as needed.  The default installation of PostgreSQL 8.4 uses one disk for both data and logs.  Normally you would allocate multiple data disks and a transaction log disk.  I have not seen anywhere in the documentation or configuration files where you can specify extra data disks or separate log disks.
>

For data volumes, you allocate 'tablespaces', which can be used for
individual indexes and tables.
http://www.postgresql.org/docs/8.4/interactive/manage-ag-tablespaces.html

For the separate log disks, you can just use symlinks. Stop the
database, move the pg_xlog directory to your desired location, and
make a symlink from the data directory to the new location. Verify
your permissions and then start the database back up :)

Do you currently have an IO bottleneck?

-selena

--
http://chesnok.com/daily - me
http://endpoint.com - work

Thanks for the info, that helps greatly.

I don't foresee any bottle necks, but I don't have any data loaded yet, I'm just setting up the server and want to get the optimum layout. Speaking of that, are there any tools for measuring PostgreSQL performance.

Also, what are the preferred query tools, besides command line and pgAdmin3?

-----Original Message-----
From: Selena Deckelmann [mailto:selenamarie(at)gmail(dot)com]
Sent: Monday, August 03, 2009 8:03 AM
To: Ewan, Michael
Cc: Postgresql PDX_Users
Subject: Re: Question about disk layout for new OLAP db

Hi!

On Mon, Aug 3, 2009 at 6:51 AM, Ewan, Michael<michael(dot)ewan(at)intel(dot)com> wrote:
>
> I am new to PostgreSQL.  I'm setting up a new PostgreSQL server to handle large amounts of performance data collected from our batch processing pools, we're currently handling about 8M rows of new data per day stored into tables with about 6B rows total.  The server I have has eight cores and 32GB memory, with 9TB of fibre channel storage.  I'm using LVM2 for disk allocation and XFS so the volume sizes can grow as needed.  The default installation of PostgreSQL 8.4 uses one disk for both data and logs.  Normally you would allocate multiple data disks and a transaction log disk.  I have not seen anywhere in the documentation or configuration files where you can specify extra data disks or separate log disks.
>

For data volumes, you allocate 'tablespaces', which can be used for
individual indexes and tables.
http://www.postgresql.org/docs/8.4/interactive/manage-ag-tablespaces.html

For the separate log disks, you can just use symlinks. Stop the
database, move the pg_xlog directory to your desired location, and
make a symlink from the data directory to the new location. Verify
your permissions and then start the database back up :)

Do you currently have an IO bottleneck?

-selena

--
http://chesnok.com/daily - me
http://endpoint.com - work