Index split WAL reduction
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
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
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
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?
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
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?--
Bruce Momjian bruce@momjian.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
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
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?
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
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?
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +