| Lists: | pgsql-patches |
|---|
| From: | "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> |
|---|---|
| To: | <pgsql-patches(at)postgresql(dot)org> |
| Subject: | Index split WAL reduction |
| Date: | 2006-12-05 11:41:13 |
| Message-ID: | 45755AD9.1040503@enterprisedb.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-patches |
Hi,
Currently, an index split writes all the data on the split page to WAL.
That's a lot of WAL traffic. The tuples that are copied to the right
page need to be WAL logged, but the tuples that stay on the original
page don't.
Here's a patch to do that. It needs further testing, I have used the
attached crude crashtest.sh to test the basics, but we need to test the
more obscure cases like splitting non-leaf or root page.
On a test case that inserts 10000 rows in increasing key order with a
100 characters wide text-field as key, the patch reduced the total
generated WAL traffic from 45MB to 33MB, or ~ 25%. Your mileage may
vary, depending on the tuple and key sizes, and the order of inserts.
Anyone see a problem with this?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
| Attachment | Content-Type | Size |
|---|---|---|
| split_wal_reduction-5.patch | text/x-patch | 16.6 KB |
| crashtest.sh | application/x-shellscript | 3.1 KB |
| From: | "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> |
|---|---|
| To: | <pgsql-patches(at)postgresql(dot)org> |
| Subject: | Re: Index split WAL reduction |
| Date: | 2006-12-07 19:01:26 |
| Message-ID: | 45786506.1060705@enterprisedb.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-patches |
Here's an updated patch, with a high key related bug and *_desc
functions fixed. And an updated crashtest.sh as well.
Heikki Linnakangas wrote:
> Hi,
>
> Currently, an index split writes all the data on the split page to WAL.
> That's a lot of WAL traffic. The tuples that are copied to the right
> page need to be WAL logged, but the tuples that stay on the original
> page don't.
>
> Here's a patch to do that. It needs further testing, I have used the
> attached crude crashtest.sh to test the basics, but we need to test the
> more obscure cases like splitting non-leaf or root page.
>
> On a test case that inserts 10000 rows in increasing key order with a
> 100 characters wide text-field as key, the patch reduced the total
> generated WAL traffic from 45MB to 33MB, or ~ 25%. Your mileage may
> vary, depending on the tuple and key sizes, and the order of inserts.
>
> Anyone see a problem with this?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
| Attachment | Content-Type | Size |
|---|---|---|
| crashtest.sh | application/x-shellscript | 3.8 KB |
| split_wal_reduction-6.patch | text/x-patch | 19.0 KB |
| From: | Heikki Linnakangas <heikki(at)enterprisedb(dot)com> |
|---|---|
| To: | Heikki Linnakangas <heikki(at)enterprisedb(dot)com> |
| Cc: | pgsql-patches(at)postgresql(dot)org |
| Subject: | Re: Index split WAL reduction |
| Date: | 2007-01-02 16:15:13 |
| Message-ID: | 459A8511.9050901@enterprisedb.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-patches |
Here's an updated patch that fixes a bug with full_page_writes, and an
alignment issue in replay code. Also added a new test case to
crashtest.sh that exercises the fixed bug.
Has anyone looked at this? I now consider it ready for committing, if
no-one sees a problem with it.
Here's the original description of the patch:
> Currently, an index split writes all the data on the split page to
> WAL. That's a lot of WAL traffic. The tuples that are copied to the
> right page need to be WAL logged, but the tuples that stay on the
> original page don't.
>
> Here's a patch to do that. It needs further testing, I have used the
> attached crude crashtest.sh to test the basics, but we need to test
> the more obscure cases like splitting non-leaf or root page.
>
> On a test case that inserts 10000 rows in increasing key order with a
> 100 characters wide text-field as key, the patch reduced the total
> generated WAL traffic from 45MB to 33MB, or ~ 25%. Your mileage may
> vary, depending on the tuple and key sizes, and the order of inserts.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
| Attachment | Content-Type | Size |
|---|---|---|
| crashtest.sh | application/x-shellscript | 5.6 KB |
| split_wal_reduction-7.patch | text/x-patch | 21.9 KB |
| From: | Bruce Momjian <bruce(at)momjian(dot)us> |
|---|---|
| To: | Heikki Linnakangas <heikki(at)enterprisedb(dot)com> |
| Cc: | pgsql-patches(at)postgresql(dot)org |
| Subject: | Re: Index split WAL reduction |
| Date: | 2007-02-04 00:00:01 |
| Message-ID: | 200702040000.l14001f26029@momjian.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-patches |
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---------------------------------------------------------------------------
Heikki Linnakangas wrote:
> Here's an updated patch that fixes a bug with full_page_writes, and an
> alignment issue in replay code. Also added a new test case to
> crashtest.sh that exercises the fixed bug.
>
> Has anyone looked at this? I now consider it ready for committing, if
> no-one sees a problem with it.
>
> Here's the original description of the patch:
>
> > Currently, an index split writes all the data on the split page to
> > WAL. That's a lot of WAL traffic. The tuples that are copied to the
> > right page need to be WAL logged, but the tuples that stay on the
> > original page don't.
> >
> > Here's a patch to do that. It needs further testing, I have used the
> > attached crude crashtest.sh to test the basics, but we need to test
> > the more obscure cases like splitting non-leaf or root page.
> >
> > On a test case that inserts 10000 rows in increasing key order with a
> > 100 characters wide text-field as key, the patch reduced the total
> > generated WAL traffic from 45MB to 33MB, or ~ 25%. Your mileage may
> > vary, depending on the tuple and key sizes, and the order of inserts.
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
[ application/x-shellscript is not supported, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
| From: | Bruce Momjian <bruce(at)momjian(dot)us> |
|---|---|
| To: | Bruce Momjian <bruce(at)momjian(dot)us> |
| Cc: | Heikki Linnakangas <heikki(at)enterprisedb(dot)com>, pgsql-patches(at)postgresql(dot)org |
| Subject: | Re: Index split WAL reduction |
| Date: | 2007-02-04 00:01:00 |
| Message-ID: | 200702040001.l14010p26420@momjian.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-patches |
Sorry, please disregard. Newer patch being added.
---------------------------------------------------------------------------
Bruce Momjian wrote:
>
> Your patch has been added to the PostgreSQL unapplied patches list at:
>
> http://momjian.postgresql.org/cgi-bin/pgpatches
>
> It will be applied as soon as one of the PostgreSQL committers reviews
> and approves it.
>
> ---------------------------------------------------------------------------
>
>
> Heikki Linnakangas wrote:
> > Here's an updated patch that fixes a bug with full_page_writes, and an
> > alignment issue in replay code. Also added a new test case to
> > crashtest.sh that exercises the fixed bug.
> >
> > Has anyone looked at this? I now consider it ready for committing, if
> > no-one sees a problem with it.
> >
> > Here's the original description of the patch:
> >
> > > Currently, an index split writes all the data on the split page to
> > > WAL. That's a lot of WAL traffic. The tuples that are copied to the
> > > right page need to be WAL logged, but the tuples that stay on the
> > > original page don't.
> > >
> > > Here's a patch to do that. It needs further testing, I have used the
> > > attached crude crashtest.sh to test the basics, but we need to test
> > > the more obscure cases like splitting non-leaf or root page.
> > >
> > > On a test case that inserts 10000 rows in increasing key order with a
> > > 100 characters wide text-field as key, the patch reduced the total
> > > generated WAL traffic from 45MB to 33MB, or ~ 25%. Your mileage may
> > > vary, depending on the tuple and key sizes, and the order of inserts.
> >
> > --
> > Heikki Linnakangas
> > EnterpriseDB http://www.enterprisedb.com
>
> [ application/x-shellscript is not supported, skipping... ]
>
>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
>
> --
> Bruce Momjian bruce(at)momjian(dot)us
> EnterpriseDB http://www.enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
| From: | Bruce Momjian <bruce(at)momjian(dot)us> |
|---|---|
| To: | Heikki Linnakangas <heikki(at)enterprisedb(dot)com> |
| Cc: | pgsql-patches(at)postgresql(dot)org |
| Subject: | Re: Index split WAL reduction |
| Date: | 2007-02-04 00:04:03 |
| Message-ID: | 200702040004.l14043126991@momjian.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-patches |
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---------------------------------------------------------------------------
Heikki Linnakangas wrote:
> Here's an updated patch that fixes a bug with full_page_writes, and an
> alignment issue in replay code. Also added a new test case to
> crashtest.sh that exercises the fixed bug.
>
> Has anyone looked at this? I now consider it ready for committing, if
> no-one sees a problem with it.
>
> Here's the original description of the patch:
>
> > Currently, an index split writes all the data on the split page to
> > WAL. That's a lot of WAL traffic. The tuples that are copied to the
> > right page need to be WAL logged, but the tuples that stay on the
> > original page don't.
> >
> > Here's a patch to do that. It needs further testing, I have used the
> > attached crude crashtest.sh to test the basics, but we need to test
> > the more obscure cases like splitting non-leaf or root page.
> >
> > On a test case that inserts 10000 rows in increasing key order with a
> > 100 characters wide text-field as key, the patch reduced the total
> > generated WAL traffic from 45MB to 33MB, or ~ 25%. Your mileage may
> > vary, depending on the tuple and key sizes, and the order of inserts.
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
[ application/x-shellscript is not supported, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
| From: | Bruce Momjian <bruce(at)momjian(dot)us> |
|---|---|
| To: | Heikki Linnakangas <heikki(at)enterprisedb(dot)com> |
| Cc: | pgsql-patches(at)postgresql(dot)org |
| Subject: | Re: Index split WAL reduction |
| Date: | 2007-02-08 05:06:06 |
| Message-ID: | 200702080506.l18566j03880@momjian.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-patches |
Patch applied. Thanks.
---------------------------------------------------------------------------
Heikki Linnakangas wrote:
> Here's an updated patch that fixes a bug with full_page_writes, and an
> alignment issue in replay code. Also added a new test case to
> crashtest.sh that exercises the fixed bug.
>
> Has anyone looked at this? I now consider it ready for committing, if
> no-one sees a problem with it.
>
> Here's the original description of the patch:
>
> > Currently, an index split writes all the data on the split page to
> > WAL. That's a lot of WAL traffic. The tuples that are copied to the
> > right page need to be WAL logged, but the tuples that stay on the
> > original page don't.
> >
> > Here's a patch to do that. It needs further testing, I have used the
> > attached crude crashtest.sh to test the basics, but we need to test
> > the more obscure cases like splitting non-leaf or root page.
> >
> > On a test case that inserts 10000 rows in increasing key order with a
> > 100 characters wide text-field as key, the patch reduced the total
> > generated WAL traffic from 45MB to 33MB, or ~ 25%. Your mileage may
> > vary, depending on the tuple and key sizes, and the order of inserts.
>
> --
> Heikki Linnakangas
> EnterpriseDB http://www.enterprisedb.com
[ application/x-shellscript is not supported, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +