Linux - Rails - PostgreSQL partitioning

I am getting some larger hard drives and will migrate my intranet
application onto the new hard drives. I am using CentOS 4. The current
setup uses one small boot volume (ext2) and the rest is partitioned into
one big LVM.

Does it make sense to partition /var/lib/pgsql separately? (thinking
mostly in terms of performance)

Craig

On 18/08/06, Craig W. remo[email protected] wrote:

I am getting some larger hard drives and will migrate my intranet
application onto the new hard drives. I am using CentOS 4. The current
setup uses one small boot volume (ext2) and the rest is partitioned into
one big LVM.

Does it make sense to partition /var/lib/pgsql separately? (thinking
mostly in terms of performance)

If you’re using LVM, then probably not (you don’t have an easy way
of telling where the extents are physically located on disk).

Dick D. wrote:

of telling where the extents are physically located on disk).
short answer: yes, I would

explanation:

It may not give you a big performance difference, but it does separate
the postgres files from the rest of the system.

If your main system drive does lots of updates, the fragmentation might
slow things down. I don’t know the disk usage patterns of postgres, but
I did have a separate partition for it and another app which did lots of
updates and cause fragmentation.

using a separate partition also insulates postgres from other daemons in
case the fill up the disk.

Jason E.

On Fri, 2006-08-18 at 09:06 -0400, Jason E. wrote:

If you’re using LVM, then probably not (you don’t have an easy way
I did have a separate partition for it and another app which did lots of
updates and cause fragmentation.

using a separate partition also insulates postgres from other daemons in
case the fill up the disk.


This server is only going to be used for the Rails intranet
application.

Thus the only things of interest are going to be
Apache/PostgreSQL/ruby-rails and I will install enough to run a KDE
desktop when necessary.

I don’t think I will have any issues with fragmentation or rampant
logging - at least thus far, the load level and disk usage has been tame
but I do have a large section of code getting ready to be deployed which
also means that the number of users simultaneously accessing the Rails
application will jump up.

Even with 10-15 simultaneous users, I don’t expect there to be too many
issues with performance but I am trying to anticipate the issues and
disk partitioning is much harder to do after the fact which is why I
asked before I make my final deployment choices.

Craig

Craig W. wrote:

It may not give you a big performance difference, but it does separate


also means that the number of users simultaneously accessing the Rails
application will jump up.

Even with 10-15 simultaneous users, I don’t expect there to be too many
issues with performance but I am trying to anticipate the issues and
disk partitioning is much harder to do after the fact which is why I
asked before I make my final deployment choices.

If you have the option, leave say half or a quarter of your LVM volume
group unallocated. Then you can create/grow volumes as needed.

Jason

On Sat, 2006-08-19 at 10:03 -0400, Michael A. wrote:

PG partitioning can also provide performance benefits if used
your LVM on top of RAID10, you be fine for most applications. At that
point, you just need to benchmark your app and see if you are IO
bound. If so, rinse and repeat.


I was thinking primarily about partitioning to help out with IO. I am
only going to use RAID 1 and keep the 3rd drive as a hot spare since
what I have noticed is that RAID 5 doesn’t enhance performance on Linux

  • this is a Dell 1750 with a PERC 3/Di controller.

I might be able to partition some of the databases down the road
thinking that I will be able to archive and remove some of the data over
time but I haven’t written that part of the application yet… :wink:

Craig

Does it make sense to partition /var/lib/pgsql separately? (thinking
mostly in terms of performance)

Really depends on your usage pattern and you app. Partitioning with
PG is great when you can logically partition your data, like by month
or by division or something along those lines. You can then easily
manage each partition individually, and say, only backup the most
current month or archive the earliest month to disk and drop the
partition.

PG partitioning can also provide performance benefits if used
correctly and you remember to turn on constraint_exclusion. Then, you
will hopefully only be searching/updating the tables that meet some
sort of constraint criteria. So you need to see if you can easily
logically partition your data and if you access patterns don’t usually
cross too many partition boundaries. Oh, and pgpsql is a tremendous
help in generating all the DDL that you might need.

It sounds like you might also be talking about using partitioning to
help out with IO. This really works best if you take a little more
care in setting up your disks. If you follow best practices and put
your LVM on top of RAID10, you be fine for most applications. At that
point, you just need to benchmark your app and see if you are IO
bound. If so, rinse and repeat.