pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Compressing WAL
6+ messages / 4 participants
[nested] [flat]

* Compressing WAL
@ 2005-04-05 04:04 Jim C. Nasby <decibel@decibel.org>
  2005-04-08 05:36 ` Re: Compressing WAL Qingqing Zhou <zhouqq@cs.toronto.edu>
  2005-04-11 01:12 ` Re: Compressing WAL Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 2 replies; 6+ messages in thread

From: Jim C. Nasby @ 2005-04-05 04:04 UTC (permalink / raw)
  To: pgsql-performance

Maybe better for -hackers, but here it goes anyway...

Has anyone looked at compressing WAL's before writing to disk? On a
system generating a lot of WAL it seems there might be some gains to be
had WAL data could be compressed before going to disk, since today's
machines are generally more I/O bound than CPU bound. And unlike the
base tables, you generally don't need to read the WAL, so you don't
really need to worry about not being able to quickly scan through the
data without decompressing it.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Compressing WAL
  2005-04-05 04:04 Compressing WAL Jim C. Nasby <decibel@decibel.org>
@ 2005-04-08 05:36 ` Qingqing Zhou <zhouqq@cs.toronto.edu>
  1 sibling, 0 replies; 6+ messages in thread

From: Qingqing Zhou @ 2005-04-08 05:36 UTC (permalink / raw)
  To: pgsql-performance


""Jim C. Nasby"" <decibel@decibel.org> writes
> Has anyone looked at compressing WAL's before writing to disk? On a
> system generating a lot of WAL it seems there might be some gains to be
> had WAL data could be compressed before going to disk, since today's
> machines are generally more I/O bound than CPU bound. And unlike the
> base tables, you generally don't need to read the WAL, so you don't
> really need to worry about not being able to quickly scan through the
> data without decompressing it.
> -- 

The problem is where you put the compression code? If you put it inside
XLogInsert lock or XLogWrite lock, which will hold the lock too long? Or
anywhere else?

Regards,
Qingqing






^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Compressing WAL
  2005-04-05 04:04 Compressing WAL Jim C. Nasby <decibel@decibel.org>
@ 2005-04-11 01:12 ` Bruce Momjian <pgman@candle.pha.pa.us>
  2005-04-13 22:27   ` Re: Compressing WAL Jim C. Nasby <decibel@decibel.org>
  2005-04-13 23:33   ` Re: Compressing WAL Simon Riggs <simon@2ndquadrant.com>
  1 sibling, 2 replies; 6+ messages in thread

From: Bruce Momjian @ 2005-04-11 01:12 UTC (permalink / raw)
  To: Jim C. Nasby <decibel@decibel.org>; +Cc: pgsql-performance

Jim C. Nasby wrote:
> Maybe better for -hackers, but here it goes anyway...
> 
> Has anyone looked at compressing WAL's before writing to disk? On a
> system generating a lot of WAL it seems there might be some gains to be
> had WAL data could be compressed before going to disk, since today's
> machines are generally more I/O bound than CPU bound. And unlike the
> base tables, you generally don't need to read the WAL, so you don't
> really need to worry about not being able to quickly scan through the
> data without decompressing it.

I have never heard anyone talk about it, but it seems useful.  I think
compressing the page images written on first page modification since
checkpoint would be a big win.

Is this a TODO?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Compressing WAL
  2005-04-05 04:04 Compressing WAL Jim C. Nasby <decibel@decibel.org>
  2005-04-11 01:12 ` Re: Compressing WAL Bruce Momjian <pgman@candle.pha.pa.us>
@ 2005-04-13 22:27   ` Jim C. Nasby <decibel@decibel.org>
  1 sibling, 0 replies; 6+ messages in thread

From: Jim C. Nasby @ 2005-04-13 22:27 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-performance

On Sun, Apr 10, 2005 at 09:12:41PM -0400, Bruce Momjian wrote:
> I have never heard anyone talk about it, but it seems useful.  I think
> compressing the page images written on first page modification since
> checkpoint would be a big win.

Could you clarify that? Maybe I'm being naive, but it seems like you
could just put a compression routine between the log writer and the
filesystem.

> Is this a TODO?

ISTM it's at least worth hacking something together and doing some
performance testing...
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Compressing WAL
  2005-04-05 04:04 Compressing WAL Jim C. Nasby <decibel@decibel.org>
  2005-04-11 01:12 ` Re: Compressing WAL Bruce Momjian <pgman@candle.pha.pa.us>
@ 2005-04-13 23:33   ` Simon Riggs <simon@2ndquadrant.com>
  2005-04-18 18:31     ` Re: Compressing WAL Bruce Momjian <pgman@candle.pha.pa.us>
  1 sibling, 1 reply; 6+ messages in thread

From: Simon Riggs @ 2005-04-13 23:33 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Jim C. Nasby <decibel@decibel.org>; pgsql-performance; pgsql-hackers@postgresql.org

On Sun, 2005-04-10 at 21:12 -0400, Bruce Momjian wrote:
> Jim C. Nasby wrote:
> > Maybe better for -hackers, but here it goes anyway...
> > 
> > Has anyone looked at compressing WAL's before writing to disk? On a
> > system generating a lot of WAL it seems there might be some gains to be
> > had WAL data could be compressed before going to disk, since today's
> > machines are generally more I/O bound than CPU bound. And unlike the
> > base tables, you generally don't need to read the WAL, so you don't
> > really need to worry about not being able to quickly scan through the
> > data without decompressing it.
> 
> I have never heard anyone talk about it, but it seems useful.  I think
> compressing the page images written on first page modification since
> checkpoint would be a big win.

Well it was discussed 2-3 years ago as part of the PITR preamble. You
may be surprised to read that over...

A summary of thoughts to date on this are:

xlog.c XLogInsert places backup blocks into the wal buffers before
insertion, so is the right place to do this. It would be possible to do
this before any LWlocks are taken, so would not not necessarily impair
scalability.

Currently XLogInsert is a severe CPU bottleneck around the CRC
calculation, as identified recently by Tom. Digging further, the code
used seems to cause processor stalls on Intel CPUs, possibly responsible
for much of the CPU time. Discussions to move to a 32-bit CRC would also
be effected by this because of the byte-by-byte nature of the algorithm,
whatever the length of the generating polynomial. PostgreSQL's CRC
algorithm is the fastest BSD code available. Until improvement is made
there, I would not investigate compression further. Some input from
hardware tuning specialists is required...

The current LZW compression code uses a 4096 byte lookback size, so that
would need to be modified to extend across a whole block. An
alternative, suggested originally by Tom and rediscovered by me because
I just don't read everybody's fine words in history, is to simply take
out the freespace in the middle of every heap block that consists of
zeros.

Any solution in this area must take into account the variability of the
size of freespace in database blocks. Some databases have mostly full
blocks, others vary. There would also be considerable variation in
compressability of blocks, especially since some blocks (e.g. TOAST) are
likely to already be compressed. There'd need to be some testing done to
see exactly the point where the costs of compression produce realisable
benefits.

So any solution must be able to cope with both compressed blocks and
non-compressed blocks. My current thinking is that this could be
achieved by using the spare fourth bit of the BkpBlocks portion of the
XLog structure, so that either all included BkpBlocks are compressed or
none of them are, and hope that allows benefit to shine through. Not
thought about heap/index issues.

It is possible that an XLogWriter process could be used to assist in the
CRC and compression calculations also, an a similar process used to
assist decompression for recovery, in time.

I regret I do not currently have time to pursue further.

Best Regards, Simon Riggs




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Compressing WAL
  2005-04-05 04:04 Compressing WAL Jim C. Nasby <decibel@decibel.org>
  2005-04-11 01:12 ` Re: Compressing WAL Bruce Momjian <pgman@candle.pha.pa.us>
  2005-04-13 23:33   ` Re: Compressing WAL Simon Riggs <simon@2ndquadrant.com>
@ 2005-04-18 18:31     ` Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 0 replies; 6+ messages in thread

From: Bruce Momjian @ 2005-04-18 18:31 UTC (permalink / raw)
  To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Jim C. Nasby <decibel@decibel.org>; pgsql-performance; pgsql-hackers@postgresql.org


Added to TODO:

	* Compress WAL entries [wal]

I have also added this email to TODO.detail.

---------------------------------------------------------------------------

Simon Riggs wrote:
> On Sun, 2005-04-10 at 21:12 -0400, Bruce Momjian wrote:
> > Jim C. Nasby wrote:
> > > Maybe better for -hackers, but here it goes anyway...
> > > 
> > > Has anyone looked at compressing WAL's before writing to disk? On a
> > > system generating a lot of WAL it seems there might be some gains to be
> > > had WAL data could be compressed before going to disk, since today's
> > > machines are generally more I/O bound than CPU bound. And unlike the
> > > base tables, you generally don't need to read the WAL, so you don't
> > > really need to worry about not being able to quickly scan through the
> > > data without decompressing it.
> > 
> > I have never heard anyone talk about it, but it seems useful.  I think
> > compressing the page images written on first page modification since
> > checkpoint would be a big win.
> 
> Well it was discussed 2-3 years ago as part of the PITR preamble. You
> may be surprised to read that over...
> 
> A summary of thoughts to date on this are:
> 
> xlog.c XLogInsert places backup blocks into the wal buffers before
> insertion, so is the right place to do this. It would be possible to do
> this before any LWlocks are taken, so would not not necessarily impair
> scalability.
> 
> Currently XLogInsert is a severe CPU bottleneck around the CRC
> calculation, as identified recently by Tom. Digging further, the code
> used seems to cause processor stalls on Intel CPUs, possibly responsible
> for much of the CPU time. Discussions to move to a 32-bit CRC would also
> be effected by this because of the byte-by-byte nature of the algorithm,
> whatever the length of the generating polynomial. PostgreSQL's CRC
> algorithm is the fastest BSD code available. Until improvement is made
> there, I would not investigate compression further. Some input from
> hardware tuning specialists is required...
> 
> The current LZW compression code uses a 4096 byte lookback size, so that
> would need to be modified to extend across a whole block. An
> alternative, suggested originally by Tom and rediscovered by me because
> I just don't read everybody's fine words in history, is to simply take
> out the freespace in the middle of every heap block that consists of
> zeros.
> 
> Any solution in this area must take into account the variability of the
> size of freespace in database blocks. Some databases have mostly full
> blocks, others vary. There would also be considerable variation in
> compressability of blocks, especially since some blocks (e.g. TOAST) are
> likely to already be compressed. There'd need to be some testing done to
> see exactly the point where the costs of compression produce realisable
> benefits.
> 
> So any solution must be able to cope with both compressed blocks and
> non-compressed blocks. My current thinking is that this could be
> achieved by using the spare fourth bit of the BkpBlocks portion of the
> XLog structure, so that either all included BkpBlocks are compressed or
> none of them are, and hope that allows benefit to shine through. Not
> thought about heap/index issues.
> 
> It is possible that an XLogWriter process could be used to assist in the
> CRC and compression calculations also, an a similar process used to
> assist decompression for recovery, in time.
> 
> I regret I do not currently have time to pursue further.
> 
> Best Regards, Simon Riggs
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2005-04-18 18:31 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-05 04:04 Compressing WAL Jim C. Nasby <decibel@decibel.org>
2005-04-08 05:36 ` Qingqing Zhou <zhouqq@cs.toronto.edu>
2005-04-11 01:12 ` Bruce Momjian <pgman@candle.pha.pa.us>
2005-04-13 22:27   ` Jim C. Nasby <decibel@decibel.org>
2005-04-13 23:33   ` Simon Riggs <simon@2ndquadrant.com>
2005-04-18 18:31     ` Bruce Momjian <pgman@candle.pha.pa.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