pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRevisiting disk layout on ZFS systems
10+ messages / 5 participants
[nested] [flat]
* Revisiting disk layout on ZFS systems
@ 2014-04-28 15:47 Karl Denninger <karl@denninger.net>
0 siblings, 3 replies; 10+ messages in thread
From: Karl Denninger @ 2014-04-28 15:47 UTC (permalink / raw)
To: pgsql-performance
I've been doing a bit of benchmarking and real-world performance
testing, and have found some curious results.
The load in question is a fairly-busy machine hosting a web service that
uses Postgresql as its back end.
"Conventional Wisdom" is that you want to run an 8k record size to match
Postgresql's inherent write size for the database.
However, operational experience says this may no longer be the case now
that modern ZFS systems support LZ4 compression, because modern CPUs can
compress fast enough that they overrun raw I/O capacity. This in turn
means that the recordsize is no longer the record size, basically, and
Postgresql's on-disk file format is rather compressible -- indeed, in
actual performance on my dataset it appears to be roughly 1.24x which is
nothing to sneeze at.
The odd thing is that I am getting better performance with a 128k record
size on this application than I get with an 8k one! Not only is the
system faster to respond subjectively and can it sustain a higher TPS
load objectively but the I/O busy percentage as measured during
operation is MARKEDLY lower (by nearly an order of magnitude!)
This is not expected behavior!
What I am curious about, however, is the xlog -- that appears to suffer
pretty badly from 128k record size, although it compresses even
more-materially; 1.94x (!)
The files in the xlog directory are large (16MB each) and thus "first
blush" would be that having a larger record size for that storage area
would help. It appears that instead it hurts.
Ideas?
--
-- Karl
karl@denninger.net
Attachments:
smime.p7s (application/pkcs7-signature, 2.6K) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Revisiting disk layout on ZFS systems
@ 2014-04-28 18:04 Heikki Linnakangas <hlinnakangas@vmware.com>
parent: Karl Denninger <karl@denninger.net>
2 siblings, 1 reply; 10+ messages in thread
From: Heikki Linnakangas @ 2014-04-28 18:04 UTC (permalink / raw)
To: Karl Denninger <karl@denninger.net>; pgsql-performance
On 04/28/2014 06:47 PM, Karl Denninger wrote:
> What I am curious about, however, is the xlog -- that appears to suffer
> pretty badly from 128k record size, although it compresses even
> more-materially; 1.94x (!)
>
> The files in the xlog directory are large (16MB each) and thus "first
> blush" would be that having a larger record size for that storage area
> would help. It appears that instead it hurts.
The WAL is fsync'd frequently. My guess is that that causes a lot of
extra work to repeatedly recompress the same data, or something like that.
- Heikki
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Revisiting disk layout on ZFS systems
@ 2014-04-28 18:07 Karl Denninger <karl@denninger.net>
parent: Heikki Linnakangas <hlinnakangas@vmware.com>
0 siblings, 2 replies; 10+ messages in thread
From: Karl Denninger @ 2014-04-28 18:07 UTC (permalink / raw)
To: pgsql-performance
On 4/28/2014 1:04 PM, Heikki Linnakangas wrote:
> On 04/28/2014 06:47 PM, Karl Denninger wrote:
>> What I am curious about, however, is the xlog -- that appears to suffer
>> pretty badly from 128k record size, although it compresses even
>> more-materially; 1.94x (!)
>>
>> The files in the xlog directory are large (16MB each) and thus "first
>> blush" would be that having a larger record size for that storage area
>> would help. It appears that instead it hurts.
>
> The WAL is fsync'd frequently. My guess is that that causes a lot of
> extra work to repeatedly recompress the same data, or something like
> that.
>
> - Heikki
>
It shouldn't as ZFS re-writes on change, and what's showing up is not
high I/O *count* but rather percentage-busy, which implies lots of head
movement (that is, lots of sub-allocation unit writes.)
Isn't WAL essentially sequential writes during normal operation?
--
-- Karl
karl@denninger.net
Attachments:
smime.p7s (application/pkcs7-signature, 2.6K) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Revisiting disk layout on ZFS systems
@ 2014-04-28 18:22 Heikki Linnakangas <hlinnakangas@vmware.com>
parent: Karl Denninger <karl@denninger.net>
1 sibling, 1 reply; 10+ messages in thread
From: Heikki Linnakangas @ 2014-04-28 18:22 UTC (permalink / raw)
To: Karl Denninger <karl@denninger.net>; pgsql-performance
On 04/28/2014 09:07 PM, Karl Denninger wrote:
>> The WAL is fsync'd frequently. My guess is that that causes a lot of
>> extra work to repeatedly recompress the same data, or something like
>> that.
>
> It shouldn't as ZFS re-writes on change, and what's showing up is not
> high I/O*count* but rather percentage-busy, which implies lots of head
> movement (that is, lots of sub-allocation unit writes.)
That sounds consistent frequent fsyncs.
> Isn't WAL essentially sequential writes during normal operation?
Yes, it's totally sequential. But it's fsync'd at every commit, which
means a lot of small writes.
- Heikki
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Revisiting disk layout on ZFS systems
@ 2014-04-28 18:26 Jeff Janes <jeff.janes@gmail.com>
parent: Karl Denninger <karl@denninger.net>
1 sibling, 1 reply; 10+ messages in thread
From: Jeff Janes @ 2014-04-28 18:26 UTC (permalink / raw)
To: Karl Denninger <karl@denninger.net>; +Cc: pgsql-performance
On Mon, Apr 28, 2014 at 11:07 AM, Karl Denninger <karl@denninger.net> wrote:
>
> On 4/28/2014 1:04 PM, Heikki Linnakangas wrote:
>
>> On 04/28/2014 06:47 PM, Karl Denninger wrote:
>>
>>> What I am curious about, however, is the xlog -- that appears to suffer
>>> pretty badly from 128k record size, although it compresses even
>>> more-materially; 1.94x (!)
>>>
>>> The files in the xlog directory are large (16MB each) and thus "first
>>> blush" would be that having a larger record size for that storage area
>>> would help. It appears that instead it hurts.
>>>
>>
>> The WAL is fsync'd frequently. My guess is that that causes a lot of
>> extra work to repeatedly recompress the same data, or something like that.
>>
>> - Heikki
>>
>> It shouldn't as ZFS re-writes on change, and what's showing up is not
> high I/O *count* but rather percentage-busy, which implies lots of head
> movement (that is, lots of sub-allocation unit writes.)
>
> Isn't WAL essentially sequential writes during normal operation?
Only if you have some sort of non-volatile intermediary, or are willing to
risk your data integrity. Otherwise, the fsync nature trumps the
sequential nature.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Revisiting disk layout on ZFS systems
@ 2014-04-28 18:27 Karl Denninger <karl@denninger.net>
parent: Heikki Linnakangas <hlinnakangas@vmware.com>
0 siblings, 0 replies; 10+ messages in thread
From: Karl Denninger @ 2014-04-28 18:27 UTC (permalink / raw)
To: pgsql-performance
On 4/28/2014 1:22 PM, Heikki Linnakangas wrote:
> On 04/28/2014 09:07 PM, Karl Denninger wrote:
>>> The WAL is fsync'd frequently. My guess is that that causes a lot of
>>> extra work to repeatedly recompress the same data, or something like
>>> that.
>>
>> It shouldn't as ZFS re-writes on change, and what's showing up is not
>> high I/O*count* but rather percentage-busy, which implies lots of head
>> movement (that is, lots of sub-allocation unit writes.)
>
> That sounds consistent frequent fsyncs.
>
>> Isn't WAL essentially sequential writes during normal operation?
>
> Yes, it's totally sequential. But it's fsync'd at every commit, which
> means a lot of small writes.
>
> - Heikki
Makes sense; I'll muse on whether there's a way to optimize this
further... I'm not running into performance problems at present but I'd
rather be ahead of it....
--
-- Karl
karl@denninger.net
Attachments:
smime.p7s (application/pkcs7-signature, 2.6K) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Revisiting disk layout on ZFS systems
@ 2014-04-28 18:29 Karl Denninger <karl@denninger.net>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Karl Denninger @ 2014-04-28 18:29 UTC (permalink / raw)
To: pgsql-performance
On 4/28/2014 1:26 PM, Jeff Janes wrote:
> On Mon, Apr 28, 2014 at 11:07 AM, Karl Denninger <karl@denninger.net
> <mailto:karl@denninger.net>> wrote:
>
>
>
> Isn't WAL essentially sequential writes during normal operation?
>
>
> Only if you have some sort of non-volatile intermediary, or are
> willing to risk your data integrity. Otherwise, the fsync nature
> trumps the sequential nature.
>
That would be a "no" on the data integrity :-)
--
-- Karl
karl@denninger.net
Attachments:
smime.p7s (application/pkcs7-signature, 2.6K) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Revisiting disk layout on ZFS systems
@ 2014-04-29 08:13 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Karl Denninger <karl@denninger.net>
2 siblings, 1 reply; 10+ messages in thread
From: Albe Laurenz @ 2014-04-29 08:13 UTC (permalink / raw)
To: Karl Denninger *EXTERN* <karl@denninger.net>; pgsql-performance
Karl Denninger wrote:
> I've been doing a bit of benchmarking and real-world performance
> testing, and have found some curious results.
[...]
> The odd thing is that I am getting better performance with a 128k record
> size on this application than I get with an 8k one!
[...]
> What I am curious about, however, is the xlog -- that appears to suffer
> pretty badly from 128k record size, although it compresses even
> more-materially; 1.94x (!)
>
> The files in the xlog directory are large (16MB each) and thus "first
> blush" would be that having a larger record size for that storage area
> would help. It appears that instead it hurts.
As has been explained, the access patterns for WAL are quite different.
For your experiment, I'd keep them on different file systems so that
you can tune them independently.
Yours,
Laurenz Albe
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Revisiting disk layout on ZFS systems
@ 2014-04-29 12:12 Karl Denninger <karl@denninger.net>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 0 replies; 10+ messages in thread
From: Karl Denninger @ 2014-04-29 12:12 UTC (permalink / raw)
To: pgsql-performance
On 4/29/2014 3:13 AM, Albe Laurenz wrote:
> Karl Denninger wrote:
>> I've been doing a bit of benchmarking and real-world performance
>> testing, and have found some curious results.
> [...]
>
>> The odd thing is that I am getting better performance with a 128k record
>> size on this application than I get with an 8k one!
> [...]
>
>> What I am curious about, however, is the xlog -- that appears to suffer
>> pretty badly from 128k record size, although it compresses even
>> more-materially; 1.94x (!)
>>
>> The files in the xlog directory are large (16MB each) and thus "first
>> blush" would be that having a larger record size for that storage area
>> would help. It appears that instead it hurts.
> As has been explained, the access patterns for WAL are quite different.
>
> For your experiment, I'd keep them on different file systems so that
> you can tune them independently.
>
They're on physically-different packs (pools and groups of spindles) as
that has been best practice for performance reasons pretty-much always
-- I just thought it was interesting, and worth noting, that the usual
recommendation to run an 8k record size for the data store itself may no
longer be valid.
It certainly isn't with my workload.
--
-- Karl
karl@denninger.net
Attachments:
smime.p7s (application/pkcs7-signature, 2.6K) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Revisiting disk layout on ZFS systems
@ 2014-05-01 21:34 Josh Berkus <josh@agliodbs.com>
parent: Karl Denninger <karl@denninger.net>
2 siblings, 0 replies; 10+ messages in thread
From: Josh Berkus @ 2014-05-01 21:34 UTC (permalink / raw)
To: Karl Denninger <karl@denninger.net>; pgsql-performance
On 04/28/2014 08:47 AM, Karl Denninger wrote:
> The odd thing is that I am getting better performance with a 128k record
> size on this application than I get with an 8k one! Not only is the
> system faster to respond subjectively and can it sustain a higher TPS
> load objectively but the I/O busy percentage as measured during
> operation is MARKEDLY lower (by nearly an order of magnitude!)
Thanks for posting your experience! I'd love it even more if you could
post some numbers to go with.
Questions:
1) is your database (or the active portion thereof) smaller than RAM?
2) is this a DW workload, where most writes are large writes?
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2014-05-01 21:34 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-04-28 15:47 Revisiting disk layout on ZFS systems Karl Denninger <karl@denninger.net>
2014-04-28 18:04 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2014-04-28 18:07 ` Karl Denninger <karl@denninger.net>
2014-04-28 18:22 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2014-04-28 18:27 ` Karl Denninger <karl@denninger.net>
2014-04-28 18:26 ` Jeff Janes <jeff.janes@gmail.com>
2014-04-28 18:29 ` Karl Denninger <karl@denninger.net>
2014-04-29 08:13 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2014-04-29 12:12 ` Karl Denninger <karl@denninger.net>
2014-05-01 21:34 ` Josh Berkus <josh@agliodbs.com>
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