Thread

Disk Groups/Storage Management for a Large Database in PostgreSQL

Amit Sharma <amitpgsql@gmail.com> — 2024-01-22T17:43:58Z

Hi,

We are building new VMs for PostgreSQL v15 on RHEL 8.x For a large database
of 15TB-20TB.

I would like to know from the experts that is it a good idea to create LVMs
to manage storage for the database?

Or are there any other better options/tools for disk groups in PostgreSQL,
similar to ASM in Oracle?

Thanks
Amit

Re: Disk Groups/Storage Management for a Large Database in PostgreSQL

Olivier Gautherot <ogautherot@gautherot.net> — 2024-01-23T06:35:56Z

Hi Amit,

El lun, 22 ene 2024 18:44, Amit Sharma <amitpgsql@gmail.com> escribió:

> Hi,
>
> We are building new VMs for PostgreSQL v15 on RHEL 8.x For a large
> database of 15TB-20TB.
>
> I would like to know from the experts that is it a good idea to create
> LVMs to manage storage for the database?
>
> Or are there any other better options/tools for disk groups in PostgreSQL,
> similar to ASM in Oracle?
>
> Thanks
> Amit
>

Simple question that requires a somewhat more complex answer. There are
actually 3 metrics to consider:

1) Capacity
Your database doesn't fit on a single disk, so you need to distribute your
data across several disks. LVM would indeed be an option (as well as ZFS or
RAID disk arrays)

2) Safety
If you loose 1 disk, your data is at risk, as you're likely to loose all
tables partially loaded on that disk. LVM is still an option as long as it
is configured on a RAID array. ZFS can do that natively.

3) Performance
Oracle ADM ensures performance by automatically controlling the
distribution of the tables. I would need to see on a real case how it is
actually done. For sure, LVM and ZFS won't have this type of granularity.

On the other hand, you can distribute your data in table partitions to help
this distribution. It is not automatic but will surely help you to
distribute your workload.

Hope it helps
Olivier


>

RE: Disk Groups/Storage Management for a Large Database in PostgreSQL

Scot Kreienkamp <scot.kreienkamp@la-z-boy.com> — 2024-01-23T14:49:47Z

El lun, 22 ene 2024 18:44, Amit Sharma <amitpgsql@gmail.com<mailto:amitpgsql@gmail.com>> escribió:
Hi,

We are building new VMs for PostgreSQL v15 on RHEL 8.x For a large database of 15TB-20TB.

I would like to know from the experts that is it a good idea to create LVMs to manage storage for the database?

Or are there any other better options/tools for disk groups in PostgreSQL, similar to ASM in Oracle?

Thanks
Amit

Simple question that requires a somewhat more complex answer. There are actually 3 metrics to consider:

1) Capacity
Your database doesn't fit on a single disk, so you need to distribute your data across several disks. LVM would indeed be an option (as well as ZFS or RAID disk arrays)

2) Safety
If you loose 1 disk, your data is at risk, as you're likely to loose all tables partially loaded on that disk. LVM is still an option as long as it is configured on a RAID array. ZFS can do that natively.

3) Performance
Oracle ADM ensures performance by automatically controlling the distribution of the tables. I would need to see on a real case how it is actually done. For sure, LVM and ZFS won't have this type of granularity.

On the other hand, you can distribute your data in table partitions to help this distribution. It is not automatic but will surely help you to distribute your workload.

As he is building VM’s I’m assuming the hardware level has all the redundancy for RAID/ZFS/etc. If that is the case then you don’t want to run RAID/ZFS/etc on top of that, let the hardware do its thing. If my assumption is wrong then ignore everything I’m saying.

One thing I found that helps with speed of reads/writes… you can spread your read/write load across multiple SCSI controllers/disks using LVM. For example, I’m assuming VMWare which allows 4 SCSI controllers. Set the OS disk on SCSI controller 0, then spread your database disks in sets of 3 across SCSI controllers 1-3, IE 3 disks of 5TB each, one on each SCSI controller. Then when you create your LVM partition specify the option to stripe it with 3 stripes. That gives you a setup where you are multiplexing reads/writes across all 3 SCSI controllers and disks instead of bottlenecking them all through 1 SCSI controller and disk at a time.

Scot Kreienkamp | Applications Infrastructure Architect | La-Z-Boy Corporate
One La-Z-Boy Drive | Monroe, Michigan 48162 | • (734) 384-6403 | | • 1-734-915-1444 | • Scot.Kreienkamp@la-z-boy.com
www.la-z-boy.com<http://www.la-z-boy.com> | facebook.com/lazboy<http://facebook.com/lazboy> | twitter.com/lazboy<http://twitter.com/lazboy> | youtube.com/lazboy<http://youtube.com/lazboy>
[cid:smallerlzbonlylogoforsign_b8ca06bf-75b5-4619-8093-c9418c455597.png]

This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.

Re: Disk Groups/Storage Management for a Large Database in PostgreSQL

Amit Sharma <amitpgsql@gmail.com> — 2024-01-23T15:21:31Z

Thanks Olivier and Scot for your inputs!

Another data point I would like to share is that VMs will be built in Azure
Cloud with Azure Managed Storage and Locally redundant storage (LRS) option
with a remote DR as well.

 LVM or ZFS would still be a good option to allow easy storage/disk
management like add, resize or remove disks while PostgreSQL services are
up?
Is equal data distribution a challenge on LVM/ZFS disks?


Thanks
Amit


On Tue, Jan 23, 2024 at 9:49 AM Scot Kreienkamp <
Scot.Kreienkamp@la-z-boy.com> wrote:

> El lun, 22 ene 2024 18:44, Amit Sharma <amitpgsql@gmail.com> escribió:
>
> Hi,
>
>
>
> We are building new VMs for PostgreSQL v15 on RHEL 8.x For a large
> database of 15TB-20TB.
>
>
>
> I would like to know from the experts that is it a good idea to create
> LVMs to manage storage for the database?
>
>
>
> Or are there any other better options/tools for disk groups in PostgreSQL,
> similar to ASM in Oracle?
>
>
>
> Thanks
>
> Amit
>
>
>
> Simple question that requires a somewhat more complex answer. There are
> actually 3 metrics to consider:
>
>
>
> 1) Capacity
>
> Your database doesn't fit on a single disk, so you need to distribute your
> data across several disks. LVM would indeed be an option (as well as ZFS or
> RAID disk arrays)
>
>
>
> 2) Safety
>
> If you loose 1 disk, your data is at risk, as you're likely to loose all
> tables partially loaded on that disk. LVM is still an option as long as it
> is configured on a RAID array. ZFS can do that natively.
>
>
>
> 3) Performance
>
> Oracle ADM ensures performance by automatically controlling the
> distribution of the tables. I would need to see on a real case how it is
> actually done. For sure, LVM and ZFS won't have this type of granularity.
>
>
>
> On the other hand, you can distribute your data in table partitions to
> help this distribution. It is not automatic but will surely help you to
> distribute your workload.
>
>
>
>
>
> As he is building VM’s I’m assuming the hardware level has all the
> redundancy for RAID/ZFS/etc.  If that is the case then you don’t want to
> run RAID/ZFS/etc on top of that, let the hardware do its thing.  If my
> assumption is wrong then ignore everything I’m saying.
>
>
>
> One thing I found that helps with speed of reads/writes… you can spread
> your read/write load across multiple SCSI controllers/disks using LVM.  For
> example, I’m assuming VMWare which allows 4 SCSI controllers.  Set the OS
> disk on SCSI controller 0, then spread your database disks in sets of 3
> across SCSI controllers 1-3, IE 3 disks of 5TB each, one on each SCSI
> controller.  Then when you create your LVM partition specify the option to
> stripe it with 3 stripes.  That gives you a setup where you are
> multiplexing reads/writes across all 3 SCSI controllers and disks instead
> of bottlenecking them all through 1 SCSI controller and disk at a time.
>
> *Scot Kreienkamp | Applications Infrastructure Architect | La-Z-Boy
> Corporate*
> One La-Z-Boy Drive | Monroe, Michigan 48162 | ( (734) 384-6403 | |  )
> 1-734-915-1444  | * Scot.Kreienkamp@la-z-boy.com
> www.la-z-boy.com  | facebook.com/lazboy  | twitter.com/lazboy |
> youtube.com/lazboy
> [image: Smaller LZB Only Logo for Sign.png]
>
> This message is intended only for the individual or entity to which it is
> addressed. It may contain privileged, confidential information which is
> exempt from disclosure under applicable laws. If you are not the intended
> recipient, you are strictly prohibited from disseminating or distributing
> this information (other than to the intended recipient) or copying this
> information. If you have received this communication in error, please
> notify us immediately by e-mail or by telephone at the above number. Thank
> you.
>

RE: Disk Groups/Storage Management for a Large Database in PostgreSQL

Scot Kreienkamp <scot.kreienkamp@la-z-boy.com> — 2024-01-23T20:33:32Z

Thanks Olivier and Scot for your inputs!

Another data point I would like to share is that VMs will be built in Azure Cloud with Azure Managed Storage and Locally redundant storage (LRS) option with a remote DR as well.

LVM or ZFS would still be a good option to allow easy storage/disk management like add, resize or remove disks while PostgreSQL services are up?
Is equal data distribution a challenge on LVM/ZFS disks?

Thanks
Amit

I would not call data distribution a challenge, but something to be aware of. If LVM has 3 disks in a pool it will, by default, use all of disk 1, then use all of disk 2, then use all of disk 3. The reason for that is with the default you can add new disks one at a time. With striping you must add new disks equal to the number of stripes. Either way I would still advise use of LVM.