pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSetting effective_io_concurrency in VM?
8+ messages / 7 participants
[nested] [flat]
* Setting effective_io_concurrency in VM?
@ 2017-11-27 18:23 Don Seiler <don@seiler.us>
0 siblings, 2 replies; 8+ messages in thread
From: Don Seiler @ 2017-11-27 18:23 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Good afternoon.
We run Postgres (currently 9.2, upgrading to 9.6 shortly) in VMWare ESX
machines. We currently have effective_io_concurrency set to the default of
1. I'm told that the data volume is a RAID 6 with 14 data drives and 2
parity drives. I know that RAID10 is recommended, just working with what
I've inherited for now (storage is high-end HP 3Par and HP recommended RAID
6 for best performance).
Anyway, I'm wondering if, in a virtualized environment with a VM datastore,
it makes sense to set effective_io_concurrency closer to the number of data
drives?
I'd also be interested in hearing how others have configured their
PostgreSQL instances for VMs (if there's anything special to think about).
Don.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Setting effective_io_concurrency in VM?
@ 2017-11-27 18:40 Scott Marlowe <scott.marlowe@gmail.com>
parent: Don Seiler <don@seiler.us>
1 sibling, 3 replies; 8+ messages in thread
From: Scott Marlowe @ 2017-11-27 18:40 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: pgsql-performance@lists.postgresql.org
On Mon, Nov 27, 2017 at 11:23 AM, Don Seiler <don@seiler.us> wrote:
> Good afternoon.
>
> We run Postgres (currently 9.2, upgrading to 9.6 shortly) in VMWare ESX
> machines. We currently have effective_io_concurrency set to the default of
> 1. I'm told that the data volume is a RAID 6 with 14 data drives and 2
> parity drives. I know that RAID10 is recommended, just working with what
> I've inherited for now (storage is high-end HP 3Par and HP recommended RAID
> 6 for best performance).
>
> Anyway, I'm wondering if, in a virtualized environment with a VM datastore,
> it makes sense to set effective_io_concurrency closer to the number of data
> drives?
>
> I'd also be interested in hearing how others have configured their
> PostgreSQL instances for VMs (if there's anything special to think about).
Generally VMs are never going to be as fast as running on bare metal
etc. You can adjust it and test it with something simple like pgbench
with various settings for -c (concurrency) and see where it peaks etc
with the setting. This will at least get you into the ball park.
A while back we needed fast machines with LOTS of storage (7TB data
drives with 5TB of data on them) and the only way to stuff that many
800GB SSDs into a single machine was to use RAID-5 with a spare (I
lobbied for RAID6 but was overidden eh...) We were able to achieve
over 15k TPS in pgbench with a 400GB data store on those boxes. The
secret was to turn off the cache in the RAID controller and cranl up
effective io concurrency to something around 10 (not sure, it's been a
while).
tl;dr: Only way to know is to benchmark it. I'd guess that somewhere
between 10 and 20 is going to get the best throughput but that's just
a guess. Benchmark it and let us know!
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Setting effective_io_concurrency in VM?
@ 2017-11-27 21:06 Fernando Hevia <fhevia@gmail.com>
parent: Don Seiler <don@seiler.us>
1 sibling, 1 reply; 8+ messages in thread
From: Fernando Hevia @ 2017-11-27 21:06 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: pgsql-performance@lists.postgresql.org
El 27 nov. 2017 15:24, "Don Seiler" <don@seiler.us> escribió:
Good afternoon.
We run Postgres (currently 9.2, upgrading to 9.6 shortly) in VMWare ESX
machines. We currently have effective_io_concurrency set to the default of
1. I'm told that the data volume is a RAID 6 with 14 data drives and 2
parity drives. I know that RAID10 is recommended, just working with what
I've inherited for now (storage is high-end HP 3Par and HP recommended RAID
6 for best performance).
Anyway, I'm wondering if, in a virtualized environment with a VM datastore,
it makes sense to set effective_io_concurrency closer to the number of data
drives?
I'd also be interested in hearing how others have configured their
PostgreSQL instances for VMs (if there's anything special to think about).
If the storage was exclusively for the Postgres box I'd try
effective_io_concurrency somewhere between 8 and 12. Since it is probably
not, it will depend on the load the other VMs exert on the storage.
Assuming the storage isnt already stressed and you need the extra IOPS, you
could test values between 4 and 8. You can of course be a lousy team player
and have PG paralelize as much as it can, but this eventually will piss
off the storage or vmware manager, which is never good as they can limit
your IO throughput at the virtualization or storage layers.
Cheers.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Setting effective_io_concurrency in VM?
@ 2017-11-27 21:44 Andrew Kerber <andrew.kerber@gmail.com>
parent: Fernando Hevia <fhevia@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Andrew Kerber @ 2017-11-27 21:44 UTC (permalink / raw)
To: Fernando Hevia <fhevia@gmail.com>; +Cc: Don Seiler <don@seiler.us>; pgsql-performance@lists.postgresql.org
Whats the guest OS? I have been able to get Oracle to perform just as well
on Virtuals as it does on Physicals. I suspect the settings are pretty
similar.
On Mon, Nov 27, 2017 at 3:06 PM, Fernando Hevia <fhevia@gmail.com> wrote:
>
>
> El 27 nov. 2017 15:24, "Don Seiler" <don@seiler.us> escribió:
>
> Good afternoon.
>
> We run Postgres (currently 9.2, upgrading to 9.6 shortly) in VMWare ESX
> machines. We currently have effective_io_concurrency set to the default of
> 1. I'm told that the data volume is a RAID 6 with 14 data drives and 2
> parity drives. I know that RAID10 is recommended, just working with what
> I've inherited for now (storage is high-end HP 3Par and HP recommended RAID
> 6 for best performance).
>
> Anyway, I'm wondering if, in a virtualized environment with a VM
> datastore, it makes sense to set effective_io_concurrency closer to the
> number of data drives?
>
> I'd also be interested in hearing how others have configured their
> PostgreSQL instances for VMs (if there's anything special to think about).
>
>
>
> If the storage was exclusively for the Postgres box I'd try
> effective_io_concurrency somewhere between 8 and 12. Since it is probably
> not, it will depend on the load the other VMs exert on the storage.
> Assuming the storage isnt already stressed and you need the extra IOPS, you
> could test values between 4 and 8. You can of course be a lousy team player
> and have PG paralelize as much as it can, but this eventually will piss
> off the storage or vmware manager, which is never good as they can limit
> your IO throughput at the virtualization or storage layers.
>
> Cheers.
>
>
>
>
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Setting effective_io_concurrency in VM?
@ 2017-11-27 22:03 Don Seiler <don@seiler.us>
parent: Andrew Kerber <andrew.kerber@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Don Seiler @ 2017-11-27 22:03 UTC (permalink / raw)
To: Andrew Kerber <andrew.kerber@gmail.com>; +Cc: Fernando Hevia <fhevia@gmail.com>; pgsql-performance@lists.postgresql.org
On Mon, Nov 27, 2017 at 3:44 PM, Andrew Kerber <andrew.kerber@gmail.com>
wrote:
> Whats the guest OS? I have been able to get Oracle to perform just as
> well on Virtuals as it does on Physicals. I suspect the settings are
> pretty similar.
>
Guest OS is CentOS 6 and CentOS 7 depending on which DB host we're looking
at. I'd be interested in learning for either case.
Don.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Setting effective_io_concurrency in VM?
@ 2017-11-27 23:18 Jeff Janes <jeff.janes@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Jeff Janes @ 2017-11-27 23:18 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Don Seiler <don@seiler.us>; pgsql-performance@lists.postgresql.org
On Mon, Nov 27, 2017 at 10:40 AM, Scott Marlowe <scott.marlowe@gmail.com>
wrote:
>
> Generally VMs are never going to be as fast as running on bare metal
> etc. You can adjust it and test it with something simple like pgbench
> with various settings for -c (concurrency) and see where it peaks etc
> with the setting. This will at least get you into the ball park.
>
None of the built-in workloads for pgbench cares a whit about
effective_io_concurrency. He would have to come up with some custom
transactions to exercise that feature. (Or use the tool people use to run
the TPCH benchmark, rather than using pgbench's built in transactions)
I think the best overall advice would be to configure it the same as you
would if it were not a VM. There may be cases where you diverge from that,
but I think each one would require extensive investigation and
experimentation, so can't be turned into a rule of thumb.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Setting effective_io_concurrency in VM?
@ 2017-11-27 23:57 Andres Freund <andres@anarazel.de>
parent: Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Andres Freund @ 2017-11-27 23:57 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Don Seiler <don@seiler.us>; pgsql-performance@lists.postgresql.org
Hi,
On 2017-11-27 11:40:19 -0700, Scott Marlowe wrote:
> tl;dr: Only way to know is to benchmark it. I'd guess that somewhere
> between 10 and 20 is going to get the best throughput but that's just
> a guess. Benchmark it and let us know!
FWIW, for SSDs my previous experiments suggest that the sweet spot is
more likely to be an order of magnitude or two bigger. Depends a bit on
your workload (including size of scans and concurrency) obviously.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Setting effective_io_concurrency in VM?
@ 2017-12-08 04:51 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Mark Kirkwood @ 2017-12-08 04:51 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; Don Seiler <don@seiler.us>; +Cc: pgsql-performance@lists.postgresql.org
On 28/11/17 07:40, Scott Marlowe wrote:
> On Mon, Nov 27, 2017 at 11:23 AM, Don Seiler <don@seiler.us> wrote:
>> Good afternoon.
>>
>> We run Postgres (currently 9.2, upgrading to 9.6 shortly) in VMWare ESX
>> machines. We currently have effective_io_concurrency set to the default of
>> 1. I'm told that the data volume is a RAID 6 with 14 data drives and 2
>> parity drives. I know that RAID10 is recommended, just working with what
>> I've inherited for now (storage is high-end HP 3Par and HP recommended RAID
>> 6 for best performance).
>>
>> Anyway, I'm wondering if, in a virtualized environment with a VM datastore,
>> it makes sense to set effective_io_concurrency closer to the number of data
>> drives?
>>
>> I'd also be interested in hearing how others have configured their
>> PostgreSQL instances for VMs (if there's anything special to think about).
> Generally VMs are never going to be as fast as running on bare metal
> etc. You can adjust it and test it with something simple like pgbench
> with various settings for -c (concurrency) and see where it peaks etc
> with the setting. This will at least get you into the ball park.
>
> A while back we needed fast machines with LOTS of storage (7TB data
> drives with 5TB of data on them) and the only way to stuff that many
> 800GB SSDs into a single machine was to use RAID-5 with a spare (I
> lobbied for RAID6 but was overidden eh...) We were able to achieve
> over 15k TPS in pgbench with a 400GB data store on those boxes. The
> secret was to turn off the cache in the RAID controller and cranl up
> effective io concurrency to something around 10 (not sure, it's been a
> while).
>
> tl;dr: Only way to know is to benchmark it. I'd guess that somewhere
> between 10 and 20 is going to get the best throughput but that's just
> a guess. Benchmark it and let us know!
Reasonably modern Linux hosts with Linux guests using Libvirt/KVM should
be able to get bare metal performance for moderate numbers of cpus (<=8
last time we benchmarked). It certainly *used* to be the case that
virtualization sucked for databases, but not so much now.
The advice to benhmark, however - is golden :-)
Cheers
Mark
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2017-12-08 04:51 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-11-27 18:23 Setting effective_io_concurrency in VM? Don Seiler <don@seiler.us>
2017-11-27 18:40 ` Scott Marlowe <scott.marlowe@gmail.com>
2017-11-27 23:18 ` Jeff Janes <jeff.janes@gmail.com>
2017-11-27 23:57 ` Andres Freund <andres@anarazel.de>
2017-12-08 04:51 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2017-11-27 21:06 ` Fernando Hevia <fhevia@gmail.com>
2017-11-27 21:44 ` Andrew Kerber <andrew.kerber@gmail.com>
2017-11-27 22:03 ` Don Seiler <don@seiler.us>
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