Re: Acclerating INSERT/UPDATE using UPS

Hello PostgreSQL Hackers,

I have made a modification of PostgreSQL which accelerates INSERT/UPDATE using UPS. The name of the software is "Sigres", and the philosophy is considering a battery supplied memory as a persistent device instead of a disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ .

In the maximum case, Sigres is 7 times faster than PostgreSQL default (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% faster than PostgreSQL without fsync (fsync=off).

The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres is different from just simply setting fsync=off.)

Although I think Sigres can be considered as one of the future directions of PostgreSQL, I do not know whether this software can be accepted or not. Could you please give me some comments ?

Best Regards,

-- Hideyuki Kawashima
Assistant Professor, University of Tsukuba

Hideyuki Kawashima wrote:
> Hello PostgreSQL Hackers,
>
> I have made a modification of PostgreSQL which accelerates INSERT/UPDATE using UPS. The name of the software is "Sigres", and the philosophy is considering a battery supplied memory as a persistent device instead of a disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ .
>
> In the maximum case, Sigres is 7 times faster than PostgreSQL default (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% faster than PostgreSQL without fsync (fsync=off).

Interesting and what happens when the UPS fails? My main concern is that
one of the purposes of PostgreSQL is data integrity. I am all for every
performance enhancement we can achieve, that does *not* sacrifice that.

Sincerely,

Joshua D. Drake

>
> The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres is different from just simply setting fsync=off.)
>
> Although I think Sigres can be considered as one of the future directions of PostgreSQL, I do not know whether this software can be accepted or not. Could you please give me some comments ?
>
> Best Regards,
>
> -- Hideyuki Kawashima
> Assistant Professor, University of Tsukuba
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Joshua,

Thanks for your comments !

Right. As you pointed out, Sigres cannot recover if UPS fails.
Therefore, I think currently Sigres cannot be used for mission critical
applications.
Sigres keeps data integrity *only when* UPS works.

On the other hand, some users such as researchers of sensor networks
would like store
and analyze 10^6 Hz sensor data insertions in real-time, and obviously
research usages are not mission critical. Thus Sigres may be accepted
for researchers, but I have no prospect now since I have just started to
distributing Sigres for research institutes in Japan.

BTW, Joshua, could you please let me know or give me any pointers for
the reason why fsync=off option exists on PostgreSQL although PostgreSQL
developers does not allow sacrificing data integrity ?
If the reason is famous and clear in the community, I am sorry for
bothering you.

-- Hideyuki

Joshua D. Drake wrote:
> Hideyuki Kawashima wrote:
>
>> Hello PostgreSQL Hackers,
>>
>> I have made a modification of PostgreSQL which accelerates INSERT/UPDATE using UPS. The name of the software is "Sigres", and the philosophy is considering a battery supplied memory as a persistent device instead of a disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ .
>>
>> In the maximum case, Sigres is 7 times faster than PostgreSQL default (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% faster than PostgreSQL without fsync (fsync=off).
>>
>
> Interesting and what happens when the UPS fails? My main concern is that
> one of the purposes of PostgreSQL is data integrity. I am all for every
> performance enhancement we can achieve, that does *not* sacrifice that.
>
> Sincerely,
>
> Joshua D. Drake
>
>
>> The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres is different from just simply setting fsync=off.)
>>
>> Although I think Sigres can be considered as one of the future directions of PostgreSQL, I do not know whether this software can be accepted or not. Could you please give me some comments ?
>>
>> Best Regards,
>>
>> -- Hideyuki Kawashima
>> Assistant Professor, University of Tsukuba
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>> message can get through to the mailing list cleanly
>>
>>
>
>
>

> BTW, Joshua, could you please let me know or give me any pointers for
> the reason why fsync=off option exists on PostgreSQL although PostgreSQL

A couple of reasons that I can think of. One would be data loads or
restoring from backup. Another would be on data that you can afford to
throw away.

> developers does not allow sacrificing data integrity ?
> If the reason is famous and clear in the community, I am sorry for
> bothering you.

No bother at all! We invite all good ideas and I am glad to see more
from our eastern community participate.

Another option you might want to look at to further give yourself a
boost in performance is full_page_writes.

Joshua D. Drake

>
>
> -- Hideyuki
>
> Joshua D. Drake wrote:
>> Hideyuki Kawashima wrote:
>>
>>> Hello PostgreSQL Hackers,
>>>
>>> I have made a modification of PostgreSQL which accelerates INSERT/UPDATE using UPS. The name of the software is "Sigres", and the philosophy is considering a battery supplied memory as a persistent device instead of a disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ .
>>>
>>> In the maximum case, Sigres is 7 times faster than PostgreSQL default (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% faster than PostgreSQL without fsync (fsync=off).
>>>
>> Interesting and what happens when the UPS fails? My main concern is that
>> one of the purposes of PostgreSQL is data integrity. I am all for every
>> performance enhancement we can achieve, that does *not* sacrifice that.
>>
>> Sincerely,
>>
>> Joshua D. Drake
>>
>>
>>> The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres is different from just simply setting fsync=off.)
>>>
>>> Although I think Sigres can be considered as one of the future directions of PostgreSQL, I do not know whether this software can be accepted or not. Could you please give me some comments ?
>>>
>>> Best Regards,
>>>
>>> -- Hideyuki Kawashima
>>> Assistant Professor, University of Tsukuba
>>>
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 1: if posting/reading through Usenet, please send an appropriate
>>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>>> message can get through to the mailing list cleanly
>>>
>>>
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Joshua,

I appreciate your quick & informative reply. And, I also really
appreciate your kind comments. Since I have joined this ML 3 hours ago,
I tried to be polite and slightly nervous. But I was relieved by your
message.

Major topic.
The full_page_writes option is already ignored. In Sigres, bgWriter does
not call CreatCheckPoint periodically. Sigres calls CreateCheckPoint
only when bgWriter calls ShutdownXLOG. Thus also from this point,
durable UPS is required for Sigres.

The reason why I made the Sigres is, the advances of recent non volatile
memories. Just now we do not usually use non volatile memories. But in
the near future, situation would change. I think if a non volatile
memories can be considered as a persistence device, PostgreSQL WAL
mechanism should be modified.
However, I do not use such devices usually. Thus I made Sigres which
requires UPS.

Currently I have just ignored XLogWrite and WALWriteLock, but a friend
of mine (a Japanese great hacker of PostgreSQL) has more idea to improve
WAL if a battery supplied memory can be considered as a persistent device.

Regards,

-- Hideyuki

Joshua D. Drake wrote:
>> BTW, Joshua, could you please let me know or give me any pointers for
>> the reason why fsync=off option exists on PostgreSQL although PostgreSQL
>>
>
> A couple of reasons that I can think of. One would be data loads or
> restoring from backup. Another would be on data that you can afford to
> throw away.
>
>
>
>> developers does not allow sacrificing data integrity ?
>> If the reason is famous and clear in the community, I am sorry for
>> bothering you.
>>
>
> No bother at all! We invite all good ideas and I am glad to see more
> from our eastern community participate.
>
> Another option you might want to look at to further give yourself a
> boost in performance is full_page_writes.
>
> Joshua D. Drake
>
>
>
>
>> -- Hideyuki
>>
>> Joshua D. Drake wrote:
>>
>>> Hideyuki Kawashima wrote:
>>>
>>>
>>>> Hello PostgreSQL Hackers,
>>>>
>>>> I have made a modification of PostgreSQL which accelerates INSERT/UPDATE using UPS. The name of the software is "Sigres", and the philosophy is considering a battery supplied memory as a persistent device instead of a disk. You can download Sigres from http://sourceforge.jp/projects/sigres/ .
>>>>
>>>> In the maximum case, Sigres is 7 times faster than PostgreSQL default (fsync=on) in my environment (CoreDuo 2.66GHz, UDMA/133), and it is also 10% faster than PostgreSQL without fsync (fsync=off).
>>>>
>>>>
>>> Interesting and what happens when the UPS fails? My main concern is that
>>> one of the purposes of PostgreSQL is data integrity. I am all for every
>>> performance enhancement we can achieve, that does *not* sacrifice that.
>>>
>>> Sincerely,
>>>
>>> Joshua D. Drake
>>>
>>>
>>>
>>>> The magic lies in usually skipping XLogWrite() and ignoring WALWriteLock. The exceptions are XLogWrite() calls from AdvanceXLInsertBuffer(). In addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres is different from just simply setting fsync=off.)
>>>>
>>>> Although I think Sigres can be considered as one of the future directions of PostgreSQL, I do not know whether this software can be accepted or not. Could you please give me some comments ?
>>>>
>>>> Best Regards,
>>>>
>>>> -- Hideyuki Kawashima
>>>> Assistant Professor, University of Tsukuba
>>>>
>>>>
>>>>
>>>> ---------------------------(end of broadcast)---------------------------
>>>> TIP 1: if posting/reading through Usenet, please send an appropriate
>>>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>>>> message can get through to the mailing list cleanly
>>>>
>>>>
>>>>
>>>
>>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>>
>
>
>

--
筑波大学大学院システム情報工学研究科 講師 川島英之
〒305-8573 つくば市天王台1-1-1 TEL: 029-853-5322
＃2月より所属が変わりました

Hideyuki Kawashima wrote:
> Joshua,

:)

> The reason why I made the Sigres is, the advances of recent non volatile
> memories. Just now we do not usually use non volatile memories. But in
> the near future, situation would change. I think if a non volatile
> memories can be considered as a persistence device, PostgreSQL WAL
> mechanism should be modified.
> However, I do not use such devices usually. Thus I made Sigres which
> requires UPS.

This is actually very interesting. We (www.commandprompt.com) have had
several customers ask us how we can make PostgreSQL more reasonable
within a flash environment.

I agree with you that in the future you will see many such databases
including PostgreSQL living on these devices.

Tom? What do you think? Is there some room for movement here within the
postgresql.conf to make something like sigres usable within PostgreSQL
proper?

>
> Currently I have just ignored XLogWrite and WALWriteLock, but a friend
> of mine (a Japanese great hacker of PostgreSQL) has more idea to improve
> WAL if a battery supplied memory can be considered as a persistent device.
>

We are coming up very quickly on a feature freeze for the next version
of PostgreSQL. If... we can has something out quickly enough and in a
thought out fashion, the hackers may be willing to accept a patch for
8.3.. If not there is always 8.4..

Sincerely,

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

I think it would be great to have this kind of functionality built into
postgres (optional and disabled by default of course) I use postgres mainly
for its querying and concurrency features (a 10x increase in insert/update
speed would be phenomenal) I know most people need 100% data integrity but
as Hideyuki pointed out we all don't need 100%. On our project, when the
power is out, we aren't receiving data anyways... just my two cents. on a
side note, would putting the wal on a tmpfs partition give you something
similar?

On 2/10/07, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
>
> Hideyuki Kawashima wrote:
> > Joshua,
>
> :)
>
> > The reason why I made the Sigres is, the advances of recent non volatile
> > memories. Just now we do not usually use non volatile memories. But in
> > the near future, situation would change. I think if a non volatile
> > memories can be considered as a persistence device, PostgreSQL WAL
> > mechanism should be modified.
> > However, I do not use such devices usually. Thus I made Sigres which
> > requires UPS.
>
> This is actually very interesting. We (www.commandprompt.com) have had
> several customers ask us how we can make PostgreSQL more reasonable
> within a flash environment.
>
> I agree with you that in the future you will see many such databases
> including PostgreSQL living on these devices.
>
> Tom? What do you think? Is there some room for movement here within the
> postgresql.conf to make something like sigres usable within PostgreSQL
> proper?
>
> >
> > Currently I have just ignored XLogWrite and WALWriteLock, but a friend
> > of mine (a Japanese great hacker of PostgreSQL) has more idea to improve
> > WAL if a battery supplied memory can be considered as a persistent
> device.
> >
>
> We are coming up very quickly on a feature freeze for the next version
> of PostgreSQL. If... we can has something out quickly enough and in a
> thought out fashion, the hackers may be willing to accept a patch for
> 8.3.. If not there is always 8.4..
>
> Sincerely,
>
> Joshua D. Drake
>
>
>
>
> --
>
> === The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
> Providing the most comprehensive PostgreSQL solutions since 1997
> http://www.commandprompt.com/
>
> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
> PostgreSQL Replication: http://www.commandprompt.com/products/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Gene Hart
cell: 443-604-2679

Joshua,

I appreciate your great suggestion!
It is great honor for me if Sigres will be merged to PostgreSQL.
Since the changes of Sigres from PostgreSQL-8.2.1 are not many,
and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif,
incorporating Sigres into PostgreSQL would be easy.

However, Sigres modifies WAL which is the most important point of DBMS
on stability.
Although I myself could not find any bugs in Sigres, I am really afraid
of it. It a bug exists on Sigres, it puts everyone to huge
inconvenience... Therefore, before incorporating Sigres into PostgreSQL,
the code must be checked, and the behaviors of Sigres must be checked
carefully. Since PostgreSQL is a famous and wide spread software, I
strongly want to avoid losing its great reputation. Unfortunately in
Japan, I do not know any WAL hackers except for a friend of mine, and he
is too busy to check Sigres. So, if pgsql-hackers checks Sigres, I am
really happy.

Best Regards,

-- Hideyuki

Joshua D. Drake wrote:
> Hideyuki Kawashima wrote:
>
>> Joshua,
>>
>
> :)
>
>
>> The reason why I made the Sigres is, the advances of recent non volatile
>> memories. Just now we do not usually use non volatile memories. But in
>> the near future, situation would change. I think if a non volatile
>> memories can be considered as a persistence device, PostgreSQL WAL
>> mechanism should be modified.
>> However, I do not use such devices usually. Thus I made Sigres which
>> requires UPS.
>>
>
> This is actually very interesting. We (www.commandprompt.com) have had
> several customers ask us how we can make PostgreSQL more reasonable
> within a flash environment.
>
> I agree with you that in the future you will see many such databases
> including PostgreSQL living on these devices.
>
> Tom? What do you think? Is there some room for movement here within the
> postgresql.conf to make something like sigres usable within PostgreSQL
> proper?
>
>
>> Currently I have just ignored XLogWrite and WALWriteLock, but a friend
>> of mine (a Japanese great hacker of PostgreSQL) has more idea to improve
>> WAL if a battery supplied memory can be considered as a persistent device.
>>
>>
>
> We are coming up very quickly on a feature freeze for the next version
> of PostgreSQL. If... we can has something out quickly enough and in a
> thought out fashion, the hackers may be willing to accept a patch for
> 8.3.. If not there is always 8.4..
>
> Sincerely,
>
> Joshua D. Drake
>
>
>
>
>

--
筑波大学大学院システム情報工学研究科 講師 川島英之
〒305-8573 つくば市天王台1-1-1 TEL: 029-853-5322
＃2月より所属が変わりました

Hideyuki Kawashima wrote:
> Joshua,
>
> I appreciate your great suggestion!
> It is great honor for me if Sigres will be merged to PostgreSQL.
> Since the changes of Sigres from PostgreSQL-8.2.1 are not many,
> and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif,
> incorporating Sigres into PostgreSQL would be easy.

The best way is to create a patch against -head and submit that patch
with a complete description of why, and what. If you have test cases
that show the improvement all the better.

I would suggest though if you are going to submit the patch that you
take a look at how you could disable/enable the feature within the
postgresql.conf via a guc.

Sincerely,

Joshua D. Drake

>
> However, Sigres modifies WAL which is the most important point of DBMS
> on stability.
> Although I myself could not find any bugs in Sigres, I am really afraid
> of it. It a bug exists on Sigres, it puts everyone to huge
> inconvenience... Therefore, before incorporating Sigres into PostgreSQL,
> the code must be checked, and the behaviors of Sigres must be checked
> carefully. Since PostgreSQL is a famous and wide spread software, I
> strongly want to avoid losing its great reputation. Unfortunately in
> Japan, I do not know any WAL hackers except for a friend of mine, and he
> is too busy to check Sigres. So, if pgsql-hackers checks Sigres, I am
> really happy.
>
> Best Regards,
>
> -- Hideyuki
>
> Joshua D. Drake wrote:
>> Hideyuki Kawashima wrote:
>>
>>> Joshua,
>>>
>> :)
>>
>>
>>> The reason why I made the Sigres is, the advances of recent non volatile
>>> memories. Just now we do not usually use non volatile memories. But in
>>> the near future, situation would change. I think if a non volatile
>>> memories can be considered as a persistence device, PostgreSQL WAL
>>> mechanism should be modified.
>>> However, I do not use such devices usually. Thus I made Sigres which
>>> requires UPS.
>>>
>> This is actually very interesting. We (www.commandprompt.com) have had
>> several customers ask us how we can make PostgreSQL more reasonable
>> within a flash environment.
>>
>> I agree with you that in the future you will see many such databases
>> including PostgreSQL living on these devices.
>>
>> Tom? What do you think? Is there some room for movement here within the
>> postgresql.conf to make something like sigres usable within PostgreSQL
>> proper?
>>
>>
>>> Currently I have just ignored XLogWrite and WALWriteLock, but a friend
>>> of mine (a Japanese great hacker of PostgreSQL) has more idea to improve
>>> WAL if a battery supplied memory can be considered as a persistent device.
>>>
>>>
>> We are coming up very quickly on a feature freeze for the next version
>> of PostgreSQL. If... we can has something out quickly enough and in a
>> thought out fashion, the hackers may be willing to accept a patch for
>> 8.3.. If not there is always 8.4..
>>
>> Sincerely,
>>
>> Joshua D. Drake
>>
>>
>>
>>
>>
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

kawasima(at)cs(dot)tsukuba(dot)ac(dot)jp (Hideyuki Kawashima) wrote:
> Joshua,
>
> I appreciate your quick & informative reply. And, I also really
> appreciate your kind comments. Since I have joined this ML 3 hours
> ago, I tried to be polite and slightly nervous. But I was relieved
> by your message.

Your idea sounds interesting; there is likely to be a considerable
resistance to mechanisms, however, that would be likely to make
PostgreSQL less robust.

Be aware, also, that in a public forum like this, people are sometimes
less gentle than Joshua.

The fundamental trouble with this mechanism is that a power outage can
instantly turn a database into crud.

One may try to mitigate that problem by supporting the memory device
with multiple power supplies *and* multiple UPSes.

But there is a not-inconsiderable risk that people will fail to read
warnings, deploy databases in a way that leaves them exposed to total
loss, and then lay blame at this community's feet. I'm sure you can
understand why the community might resist that...
--
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxdatabases.info/info/internet.html
Babbage's Rule: "No man's cipher is worth looking at unless the
inventor has himself solved a very difficult cipher" (The Codebreakers
by Kahn, 2nd ed, pg 765)

A long time ago, in a galaxy far, far away, kawasima(at)cs(dot)tsukuba(dot)ac(dot)jp (Hideyuki Kawashima) wrote:
> I appreciate your great suggestion!
> It is great honor for me if Sigres will be merged to PostgreSQL.
> Since the changes of Sigres from PostgreSQL-8.2.1 are not many,
> and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif,
> incorporating Sigres into PostgreSQL would be easy.

You should consider submitting a patch for this against CVS HEAD.

And actually, I'd think it a better idea to define a GUC variable and
use that to control whether Sigres is active or not.

At the more sophisticated end of the spectrum, you might set things up
so that it could be activated/deactivated at runtime by a superuser.

At the less sophisticated end, it might need to be configured in
postgresql.conf...
--
output = ("cbbrowne" "@" "acm.org")
http://linuxfinances.info/info/
If you've done six impossible things this morning, why not round it
off with breakfast at Milliways, the Restaurant at the End of the
Universe?

Gene,

Thanks for your comments !

> On our project, when the power is out, we aren't receiving data
anyways... just my two cents.

I am sorry, but I do not know how I can appropriately answer since I do
not understand what you would like to ask me in this sentence. I
understand that you have a project with UPS, and when the power failure
occurs on UPS, suddenly your system cannot get data. Is my understanding
right ? If so, then I agree with you and the reliability and durability
of UPS is mandatory for your system.

> on a side note, would putting the wal on a tmpfs partition give you
something similar?

There are two differences between Sigres method and tmpfs with UPS method.
1: XLogWrite
Even if you use tmpfs, your system executes XLogWrite which includes
write().
Since write() is heavy system call, Sigres would be slightly faster than
tmpfs method.
2: XLogWriteLock
Even if you use tmpfs, your system lock/release XLogWriteLock while
Sigres ignores.
Although the frequency of XLogWriteLock accesses is lower than
XLogInsertLock,
ignoring XLogWriteLock improves the performance especially in many core
environment.

Best Regards,

-- Hideyuki

Gene wrote:
> I think it would be great to have this kind of functionality built
> into postgres (optional and disabled by default of course) I use
> postgres mainly for its querying and concurrency features (a 10x
> increase in insert/update speed would be phenomenal) I know most
> people need 100% data integrity but as Hideyuki pointed out we all
> don't need 100%. On our project, when the power is out, we aren't
> receiving data anyways... just my two cents. on a side note, would
> putting the wal on a tmpfs partition give you something similar?
>
> On 2/10/07, *Joshua D. Drake* <jd(at)commandprompt(dot)com
> <mailto:jd(at)commandprompt(dot)com>> wrote:
>
> Hideyuki Kawashima wrote:
> > Joshua,
>
> :)
>
> > The reason why I made the Sigres is, the advances of recent non
> volatile
> > memories. Just now we do not usually use non volatile memories.
> But in
> > the near future, situation would change. I think if a non volatile
> > memories can be considered as a persistence device, PostgreSQL WAL
> > mechanism should be modified.
> > However, I do not use such devices usually. Thus I made Sigres which
> > requires UPS.
>
> This is actually very interesting. We ( www.commandprompt.com
> <http://www.commandprompt.com>) have had
> several customers ask us how we can make PostgreSQL more reasonable
> within a flash environment.
>
> I agree with you that in the future you will see many such databases
> including PostgreSQL living on these devices.
>
> Tom? What do you think? Is there some room for movement here
> within the
> postgresql.conf to make something like sigres usable within PostgreSQL
> proper?
>
> >
> > Currently I have just ignored XLogWrite and WALWriteLock, but a
> friend
> > of mine (a Japanese great hacker of PostgreSQL) has more idea to
> improve
> > WAL if a battery supplied memory can be considered as a
> persistent device.
> >
>
> We are coming up very quickly on a feature freeze for the next version
> of PostgreSQL. If... we can has something out quickly enough and in a
> thought out fashion, the hackers may be willing to accept a patch for
> 8.3.. If not there is always 8.4..
>
> Sincerely,
>
> Joshua D. Drake
>
>
>
>
> --
>
> === The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
> Providing the most comprehensive PostgreSQL solutions since 1997
> http://www.commandprompt.com/
>
> Donate to the PostgreSQL Project:
> http://www.postgresql.org/about/donate
> PostgreSQL Replication: http://www.commandprompt.com/products/
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
>
>
> --
> Gene Hart
> cell: 443-604-2679

On Feb 10, 2007, at 9:33 PM, Christopher Browne wrote:
> The fundamental trouble with this mechanism is that a power outage can
> instantly turn a database into crud.
>
> One may try to mitigate that problem by supporting the memory device
> with multiple power supplies *and* multiple UPSes.

Ask me about the time a year ago that I had a 24x7 database, with two
power supplies connected to two UPSes on independent mains circuits,
dropped dead because one UPS was overloaded (more than one server
connected to it, apparently too much) and the other UPS was simply
dead (undetected zombie UPS), when a catastrophic power failure
killed both of the generator backed mains circuits.

I wasn't pleased, but it happened nonetheless. A UPS is not a 100%
guarantee of anything. They fail more often than they should. No
amount of paranoia guarantees uptime.

That said, I see plenty of use for loosening restrictions on
databases where the contents do not matter and a little loss is
acceptable.

Cheers,

J. Andrew Rogers
jrogers(at)neopolitan(dot)com

Gene <genekhart(at)gmail(dot)com> writes:
> ... just my two cents. on a side note, would putting the wal on a
> tmpfs partition give you something similar?

Indeed, I'm wondering why one needs to hack the Postgres core to throw
away data integrity guarantees; there are plenty of ways to do that
already :-(. Hideyuki-san has not explained exactly what integrity
assumptions he wants to make or not make. I'm surely willing to listen
to supporting a different set of assumptions than we currently use, but
I'd like to see a clear explanation of what assumptions are being made
and why they represent a useful case.

regards, tom lane

Christopher Browne wrote:
> kawasima(at)cs(dot)tsukuba(dot)ac(dot)jp (Hideyuki Kawashima) wrote:
>> Joshua,
>>
>> I appreciate your quick & informative reply. And, I also really
>> appreciate your kind comments. Since I have joined this ML 3 hours
>> ago, I tried to be polite and slightly nervous. But I was relieved
>> by your message.
>
> Your idea sounds interesting; there is likely to be a considerable
> resistance to mechanisms, however, that would be likely to make
> PostgreSQL less robust.
>
> Be aware, also, that in a public forum like this, people are sometimes
> less gentle than Joshua.
>
> The fundamental trouble with this mechanism is that a power outage can
> instantly turn a database into crud.

Correct, that is certainly a problem. However, I think the interesting
opportunity here is in devices that don't typically loose power. A PDA
for example.

The PostgreSQL footprint is actually quite small, and PDAs are getting
larger and larger in capacity. Heck, they even have 32GB SD now. In the
near future I believe we can expect to see always on, mini laptops as well.

From an deployable application perspective, this could be a big deal. We
are already starting to see very large traction in the Win32 desktop app
arena.

>
> One may try to mitigate that problem by supporting the memory device
> with multiple power supplies *and* multiple UPSes.
>
> But there is a not-inconsiderable risk that people will fail to read
> warnings, deploy databases in a way that leaves them exposed to total
> loss, and then lay blame at this community's feet. I'm sure you can
> understand why the community might resist that...

I certainly can, but a feature left off by default and documented
thoroughly can mitigate a lot of that. Heck if we really wanted to we
could even make it a custom build; --with-lazy-wal ;)

Sincerely,

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Tom Lane wrote:
> Gene <genekhart(at)gmail(dot)com> writes:
>> ... just my two cents. on a side note, would putting the wal on a
>> tmpfs partition give you something similar?
>
> Indeed, I'm wondering why one needs to hack the Postgres core to throw
> away data integrity guarantees; there are plenty of ways to do that
> already :-(.

Under a Linux or FreeBSD environment sure... but what about where a good
portion of the memory *is* flash? We have a customer right now where
they have a device that has 64 megs of ram and 512 megs of flash. The
system itself considers it total ram. They run over the 64 megs almost
on boot.

Allowing postgresql to be less aggressive on writes would help them
quite a bit.

> Hideyuki-san has not explained exactly what integrity
> assumptions he wants to make or not make. I'm surely willing to listen
> to supporting a different set of assumptions than we currently use, but
> I'd like to see a clear explanation of what assumptions are being made
> and why they represent a useful case.

Absolutely agreed there.

Sincerely,

Joshua D. Drake

>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> Tom Lane wrote:
>> I'd like to see a clear explanation of what assumptions are being made
>> and why they represent a useful case.

> Absolutely agreed there.

Just to be clear: I believe our current assumptions can be stated as
"Postgres will not lose data if the kernel and disk drive do not lose
data that they have acknowledged as being successfully fsync'd."
This is independent of any questions about Postgres bugs or measures
that we take to limit the impact of our bugs --- it's about what our
extent of responsibility is. I think that Hideyuki-san is proposing
a different contract for data integrity, and I want to understand what
that contract is and why someone would want it.

regards, tom lane

Christopher Browne wrote:
> kawasima(at)cs(dot)tsukuba(dot)ac(dot)jp (Hideyuki Kawashima) wrote:
>> Joshua,
>>
>> I appreciate your quick & informative reply. And, I also really
>> appreciate your kind comments. Since I have joined this ML 3 hours
>> ago, I tried to be polite and slightly nervous. But I was relieved
>> by your message.
>
> Your idea sounds interesting; there is likely to be a considerable
> resistance to mechanisms, however, that would be likely to make
> PostgreSQL less robust.
>
> Be aware, also, that in a public forum like this, people are sometimes
> less gentle than Joshua.
>
> The fundamental trouble with this mechanism is that a power outage can
> instantly turn a database into crud.

I can think of a few places where I don't care about the data if the
power is lost -

* Web-based session data

A lot of web sites have separate session-only databases.
If the database goes down, we have to truncate the tables anyways when
it comes back up.

* Reporting slaves

We have replication slaves setup for internal (staff-only) reporting.
Often a lot of temp and summary tables as well.

If the data is lost, don't care. Its a reporting database.

Re-syncing from another slave is no biggie for total data loss.

Less a concern given the speed increase of the data it creates as well
as data coming in from the master.

* Front-end cache slaves

Same type of situation as the reporting slaves.
Basic front-end cache that replicates data to take load off the master.

The slaves still have to do all the same insert/updates, but this means
they'll spend less time in locks.

They crash, point the apps to the master or another slave while you fix it.

Weslee

Tom Lane wrote:
> Gene <genekhart(at)gmail(dot)com> writes:
> > ... just my two cents. on a side note, would putting the wal on a
> > tmpfs partition give you something similar?
>
> Indeed, I'm wondering why one needs to hack the Postgres core to throw
> away data integrity guarantees; there are plenty of ways to do that
> already :-(. Hideyuki-san has not explained exactly what integrity
> assumptions he wants to make or not make. I'm surely willing to listen
> to supporting a different set of assumptions than we currently use, but
> I'd like to see a clear explanation of what assumptions are being made
> and why they represent a useful case.

I am unsure why Sigres is significantly faster than just creating a file
system on the permanent memory and putting xlog on there.

--
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. +

I was curious to see how postgres would perform with wal on a tmpfs vs disk
here are some numbers I got from pgbench. Let me know if I did something
stupid, this is the first time I've used pgbench. The wal on tmpfs method is
not significantly faster.

[[ WAL ON TMPFS ]]
pgbench -i -s 10 -U postgres -d benchmark
...
pgbench -Upostgres -s 10 -c 10 -t 10000 benchmark
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
tps = 5817.693724 (including connections establishing)
tps = 5825.646441 (excluding connections establishing)

[[ WAL ON EXT2 14 U320 DRIVE RAID10 WITH BBU (same as data) ]]
pgbench -Upostgres -s 10 -c 10 -t 10000 benchmark
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
tps = 5653.187997 (including connections establishing)
tps = 5660.554438 (excluding connections establishing)

pgbench -Upostgres -s 100 -c 10 -t 10000 benchmark
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 100
number of clients: 10
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
tps = 5536.019864 (including connections establishing)
tps = 5543.834350 (excluding connections establishing)

On 2/12/07, Gene <genekhart(at)gmail(dot)com> wrote:
>
> I was curious to see how postgres would perform with wal on a tmpfs vs
> disk here are some numbers I got from pgbench. Let me know if I did
> something stupid, this is the first time I've used pgbench. The wal on tmpfs
> method is not significantly faster.
>

tmpfs uses virtual memory, so it is likely hitting the disk. I would try
and repeat your cases with ramfs, which uses real memory. Testing shows
that just putting pg_xlog on ramfs yields a 30% boost in performance over
disk with a stock configuration. So there is room for improvement, I am
sure.

Gene <genekhart(at)gmail(dot)com> writes:
> I was curious to see how postgres would perform with wal on a tmpfs vs disk
> here are some numbers I got from pgbench. Let me know if I did something
> stupid, this is the first time I've used pgbench. The wal on tmpfs method is
> not significantly faster.

This comparison is not very useful because you were using battery-backed
write cache, which gives pretty much all the performance improvement
that is to be looked for in this area. Try it against a plain vanilla
disk drive (that's not lying about write complete) and you'll find the
maximum TPS rate is closely related to the disk's rotation rate.

At the same time though, the existence of BBWC solutions makes me wonder
why we need another.

regards, tom lane

Bruce,

Thanks for your comments, and let me answer to your question.
Sigres is *not* significantly faster than just creating a file system on
the permanent memory and putting xlog on there.
Sigres is slightly faster than the case because each backend does not
call XLogWrite while bgWriter does.

-- Hideyuki

Bruce Momjian wrote:
> Tom Lane wrote:
>
>> Gene <genekhart(at)gmail(dot)com> writes:
>>
>>> ... just my two cents. on a side note, would putting the wal on a
>>> tmpfs partition give you something similar?
>>>
>> Indeed, I'm wondering why one needs to hack the Postgres core to throw
>> away data integrity guarantees; there are plenty of ways to do that
>> already :-(. Hideyuki-san has not explained exactly what integrity
>> assumptions he wants to make or not make. I'm surely willing to listen
>> to supporting a different set of assumptions than we currently use, but
>> I'd like to see a clear explanation of what assumptions are being made
>> and why they represent a useful case.
>>
>
> I am unsure why Sigres is significantly faster than just creating a file
> system on the permanent memory and putting xlog on there.
>
>

Hideyuki Kawashima wrote:
> Bruce,
>
> Thanks for your comments, and let me answer to your question.
> Sigres is *not* significantly faster than just creating a file system on
> the permanent memory and putting xlog on there.
> Sigres is slightly faster than the case because each backend does not
> call XLogWrite while bgWriter does.

The question then is how much faster is it, and is that worth adding
extra code to improve it.

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

>
> -- Hideyuki
>
> Bruce Momjian wrote:
> > Tom Lane wrote:
> >
> >> Gene <genekhart(at)gmail(dot)com> writes:
> >>
> >>> ... just my two cents. on a side note, would putting the wal on a
> >>> tmpfs partition give you something similar?
> >>>
> >> Indeed, I'm wondering why one needs to hack the Postgres core to throw
> >> away data integrity guarantees; there are plenty of ways to do that
> >> already :-(. Hideyuki-san has not explained exactly what integrity
> >> assumptions he wants to make or not make. I'm surely willing to listen
> >> to supporting a different set of assumptions than we currently use, but
> >> I'd like to see a clear explanation of what assumptions are being made
> >> and why they represent a useful case.
> >>
> >
> > I am unsure why Sigres is significantly faster than just creating a file
> > system on the permanent memory and putting xlog on there.
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

--
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. +

Gene,

I think tmpfs/ramfs method would increase the performance of PostgreSQL
when workload is write-intensive.
Does pgbench issue write intensive queries ? Since I do not know the
detailed queries issued in pgbench, I am happy if you teach me.

Regards,

-- Hideyuki

Gene wrote:
> I was curious to see how postgres would perform with wal on a tmpfs vs
> disk here are some numbers I got from pgbench. Let me know if I did
> something stupid, this is the first time I've used pgbench. The wal on
> tmpfs method is not significantly faster.
>
> [[ WAL ON TMPFS ]]
> pgbench -i -s 10 -U postgres -d benchmark
> ...
> pgbench -Upostgres -s 10 -c 10 -t 10000 benchmark
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 10
> number of clients: 10
> number of transactions per client: 10000
> number of transactions actually processed: 100000/100000
> tps = 5817.693724 (including connections establishing)
> tps = 5825.646441 (excluding connections establishing)
>
> [[ WAL ON EXT2 14 U320 DRIVE RAID10 WITH BBU (same as data) ]]
> pgbench -Upostgres -s 10 -c 10 -t 10000 benchmark
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 10
> number of clients: 10
> number of transactions per client: 10000
> number of transactions actually processed: 100000/100000
> tps = 5653.187997 (including connections establishing)
> tps = 5660.554438 (excluding connections establishing)
>
> pgbench -Upostgres -s 100 -c 10 -t 10000 benchmark
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 100
> number of clients: 10
> number of transactions per client: 10000
> number of transactions actually processed: 100000/100000
> tps = 5536.019864 (including connections establishing)
> tps = 5543.834350 (excluding connections establishing)
>

Bruce,

Let me answer to your question.

Question 1: How much faster is it ?

To answer to your question, I measured execution times of INSERT
operations on concurrent accesses on dual-core x 2 CPU (each 2.80GHz),
In the experiment, each client issues 5000 INSERT operations concurrently.
The following shows the average times (seconds) of 5 measurements.
#Cli: The number of concurrent clients
P-D: PostgreSQL Default (i.e. usual file system)
P-T: PostgreSQL tmpfs
S-D: Sigres Default (i.e. usual file system)
S-T: Sigres tmpfs
P-T/S-T Improve ratio of S-T to P-T

The result shows S-T is 10% to 18% faster than P-T.
Thus my answer to your question is 10% to 18% when concurrency is from1
to 100.

#Cli P-D P-T S-D S-T P-T/S-T
1 1.72 0.50 0.46 0.45 1.10
2 2.87 0.62 0.58 0.54 1.15
3 3.08 0.89 0.77 0.77 1.15
4 3.14 0.98 0.86 0.84 1.16
5 3.31 1.23 1.09 1.07 1.15
6 3.57 1.44 1.31 1.27 1.14
7 3.91 1.68 1.51 1.48 1.14
8 4.49 1.89 1.71 1.67 1.13
9 4.78 2.21 1.9 1.92 1.15
10 5.33 2.47 2.22 2.14 1.15
20 11.50 5.66 5.16 4.86 1.16
50 32.96 16.54 14.92 13.97 1.18
100 79.60 43.71 39.55 38.38 1.14

Question 2: Is that worth adding extra code to improve it ?

Yes, I think it is worth. It is because in the case of commercial DBMS,
only 5% improvement is achieved with version-up.

BTW, I and a friend of mine try to design & implement a parallel access
way to the wal buffer on a shared memory. I think this is promising
direction since WALInsertLock is more frequently issued than
WALWriteLock, and the number of CPU-cores will increase definitely.

-- Hideyuki

Bruce Momjian wrote:
> Hideyuki Kawashima wrote:
>
>> Bruce,
>>
>> Thanks for your comments, and let me answer to your question.
>> Sigres is *not* significantly faster than just creating a file system on
>> the permanent memory and putting xlog on there.
>> Sigres is slightly faster than the case because each backend does not
>> call XLogWrite while bgWriter does.
>>
>
> The question then is how much faster is it, and is that worth adding
> extra code to improve it.
>
> ---------------------------------------------------------------------------
>
>
>
>> -- Hideyuki
>>
>> Bruce Momjian wrote:
>>
>>> Tom Lane wrote:
>>>
>>>
>>>> Gene <genekhart(at)gmail(dot)com> writes:
>>>>
>>>>
>>>>> ... just my two cents. on a side note, would putting the wal on a
>>>>> tmpfs partition give you something similar?
>>>>>
>>>>>
>>>> Indeed, I'm wondering why one needs to hack the Postgres core to throw
>>>> away data integrity guarantees; there are plenty of ways to do that
>>>> already :-(. Hideyuki-san has not explained exactly what integrity
>>>> assumptions he wants to make or not make. I'm surely willing to listen
>>>> to supporting a different set of assumptions than we currently use, but
>>>> I'd like to see a clear explanation of what assumptions are being made
>>>> and why they represent a useful case.
>>>>
>>>>
>>> I am unsure why Sigres is significantly faster than just creating a file
>>> system on the permanent memory and putting xlog on there.
>>>
>>>
>>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: if posting/reading through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>> message can get through to the mailing list cleanly
>>
>
>

--
Hideyuki Kawashima (Ph.D), University of Tsukuba,
Graduate School of Systems and Information Engineering
Assistant Professor, TEL: +81-29-853-5322

Tom,

In my experience, in last April, a BBWC solution did not accelerate
PostgreSQL well.
The device which I tried was i-ram by gigabyte
(http://techreport.com/reviews/2006q1/gigabyte-iram/index.x?pg=1 ).
The i-ram showed only a little performance improvement compared to
PostgreSQL with fsync to disk. (However, in then case of PostgreSQL
fsync=off, the performance improvement was great).
Thus I think Sigres is better than BBWC, to the best of my knowledge.

However, I do not know other BBWC technologies such as HP smart array
E200 controller.
(http://h18004.www1.hp.com/products/servers/proliantstorage/arraycontrollers/smartarraye200/index.html)
So, I am sorry if I describe wrong conclusion.

Best Regards,

-- Hideyuki

Tom Lane wrote:
> Gene <genekhart(at)gmail(dot)com> writes:
>
>> I was curious to see how postgres would perform with wal on a tmpfs vs disk
>> here are some numbers I got from pgbench. Let me know if I did something
>> stupid, this is the first time I've used pgbench. The wal on tmpfs method is
>> not significantly faster.
>>
>
> This comparison is not very useful because you were using battery-backed
> write cache, which gives pretty much all the performance improvement
> that is to be looked for in this area. Try it against a plain vanilla
> disk drive (that's not lying about write complete) and you'll find the
> maximum TPS rate is closely related to the disk's rotation rate.
>
> At the same time though, the existence of BBWC solutions makes me wonder
> why we need another.
>
> regards, tom lane
>
>
>

--
Hideyuki Kawashima (Ph.D), University of Tsukuba,
Graduate School of Systems and Information Engineering
Assistant Professor, TEL: +81-29-853-5322

Joshua,

I revised. Now Sigres can be activated by setting "sigres = on" in
postgresql.conf.
You can download the version (0.1.2) from
http://sourceforge.jp/projects/sigres .

And, I attach the diff between PostgreSQL-8.2.1 and Sigres-0.1.2 to this
mail.

Thanks for your comments.

-- Hideyuki

Joshua D. Drake wrote:
> Hideyuki Kawashima wrote:
>
>> Joshua,
>>
>> I appreciate your great suggestion!
>> It is great honor for me if Sigres will be merged to PostgreSQL.
>> Since the changes of Sigres from PostgreSQL-8.2.1 are not many,
>> and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif,
>> incorporating Sigres into PostgreSQL would be easy.
>>
>
> The best way is to create a patch against -head and submit that patch
> with a complete description of why, and what. If you have test cases
> that show the improvement all the better.
>
> I would suggest though if you are going to submit the patch that you
> take a look at how you could disable/enable the feature within the
> postgresql.conf via a guc.
>
> Sincerely,
>
> Joshua D. Drake
>
>
>> However, Sigres modifies WAL which is the most important point of DBMS
>> on stability.
>> Although I myself could not find any bugs in Sigres, I am really afraid
>> of it. It a bug exists on Sigres, it puts everyone to huge
>> inconvenience... Therefore, before incorporating Sigres into PostgreSQL,
>> the code must be checked, and the behaviors of Sigres must be checked
>> carefully. Since PostgreSQL is a famous and wide spread software, I
>> strongly want to avoid losing its great reputation. Unfortunately in
>> Japan, I do not know any WAL hackers except for a friend of mine, and he
>> is too busy to check Sigres. So, if pgsql-hackers checks Sigres, I am
>> really happy.
>>
>> Best Regards,
>>
>> -- Hideyuki
>>
>> Joshua D. Drake wrote:
>>
>>> Hideyuki Kawashima wrote:
>>>
>>>
>>>> Joshua,
>>>>
>>>>
>>> :)
>>>
>>>
>>>
>>>> The reason why I made the Sigres is, the advances of recent non volatile
>>>> memories. Just now we do not usually use non volatile memories. But in
>>>> the near future, situation would change. I think if a non volatile
>>>> memories can be considered as a persistence device, PostgreSQL WAL
>>>> mechanism should be modified.
>>>> However, I do not use such devices usually. Thus I made Sigres which
>>>> requires UPS.
>>>>
>>>>
>>> This is actually very interesting. We (www.commandprompt.com) have had
>>> several customers ask us how we can make PostgreSQL more reasonable
>>> within a flash environment.
>>>
>>> I agree with you that in the future you will see many such databases
>>> including PostgreSQL living on these devices.
>>>
>>> Tom? What do you think? Is there some room for movement here within the
>>> postgresql.conf to make something like sigres usable within PostgreSQL
>>> proper?
>>>
>>>
>>>
>>>> Currently I have just ignored XLogWrite and WALWriteLock, but a friend
>>>> of mine (a Japanese great hacker of PostgreSQL) has more idea to improve
>>>> WAL if a battery supplied memory can be considered as a persistent device.
>>>>
>>>>
>>>>
>>> We are coming up very quickly on a feature freeze for the next version
>>> of PostgreSQL. If... we can has something out quickly enough and in a
>>> thought out fashion, the hackers may be willing to accept a patch for
>>> 8.3.. If not there is always 8.4..
>>>
>>> Sincerely,
>>>
>>> Joshua D. Drake
>>>
>>>
>>>
>>>
>>>
>>>
>
>
>

--
Hideyuki Kawashima (Ph.D), University of Tsukuba,
Graduate School of Systems and Information Engineering
Assistant Professor, TEL: +81-29-853-5322

Christopher Browne wrote:
> A long time ago, in a galaxy far, far away, kawasima(at)cs(dot)tsukuba(dot)ac(dot)jp (Hideyuki Kawashima) wrote:
>> I appreciate your great suggestion!
>> It is great honor for me if Sigres will be merged to PostgreSQL.
>> Since the changes of Sigres from PostgreSQL-8.2.1 are not many,
>> and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif,
>> incorporating Sigres into PostgreSQL would be easy.
>
> You should consider submitting a patch for this against CVS HEAD.
>
> And actually, I'd think it a better idea to define a GUC variable and
> use that to control whether Sigres is active or not.
>
> At the more sophisticated end of the spectrum, you might set things up
> so that it could be activated/deactivated at runtime by a superuser.
>
> At the less sophisticated end, it might need to be configured in
> postgresql.conf...

Whatever happen with this?

Joshua D. Drake

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Joshua D. Drake wrote:
> Christopher Browne wrote:
> > A long time ago, in a galaxy far, far away, kawasima(at)cs(dot)tsukuba(dot)ac(dot)jp (Hideyuki Kawashima) wrote:
> >> I appreciate your great suggestion!
> >> It is great honor for me if Sigres will be merged to PostgreSQL.
> >> Since the changes of Sigres from PostgreSQL-8.2.1 are not many,
> >> and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif,
> >> incorporating Sigres into PostgreSQL would be easy.
> >
> > You should consider submitting a patch for this against CVS HEAD.
> >
> > And actually, I'd think it a better idea to define a GUC variable and
> > use that to control whether Sigres is active or not.
> >
> > At the more sophisticated end of the spectrum, you might set things up
> > so that it could be activated/deactivated at runtime by a superuser.
> >
> > At the less sophisticated end, it might need to be configured in
> > postgresql.conf...
>
> Whatever happen with this?

I would like to see more analysis about why Sigres is faster than an
in-memory file system. I think the idea was that locking was reduced
but I am unclear on why locking is different in the two cases.

--
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. +

On Mon, 2007-02-26 at 09:44 -0500, Bruce Momjian wrote:
> Joshua D. Drake wrote:
> > Christopher Browne wrote:
> > > A long time ago, in a galaxy far, far away, kawasima(at)cs(dot)tsukuba(dot)ac(dot)jp (Hideyuki Kawashima) wrote:
> > >> I appreciate your great suggestion!
> > >> It is great honor for me if Sigres will be merged to PostgreSQL.
> > >> Since the changes of Sigres from PostgreSQL-8.2.1 are not many,
> > >> and moreover, all of changes are surrounded with #ifdef SIGRES --- #endif,
> > >> incorporating Sigres into PostgreSQL would be easy.
> > >
> > > You should consider submitting a patch for this against CVS HEAD.
> > >
> > > And actually, I'd think it a better idea to define a GUC variable and
> > > use that to control whether Sigres is active or not.
> > >
> > > At the more sophisticated end of the spectrum, you might set things up
> > > so that it could be activated/deactivated at runtime by a superuser.
> > >
> > > At the less sophisticated end, it might need to be configured in
> > > postgresql.conf...
> >
> > Whatever happen with this?
>
> I would like to see more analysis about why Sigres is faster than an
> in-memory file system. I think the idea was that locking was reduced
> but I am unclear on why locking is different in the two cases.

Reading through the design, I see the following

- bgwriter performs XLogWrite, not each backend
- WAL fsync is only performed when WAL file fills
- no checkpoints are performed until shutdown

This is approximately the same performance as fsync=off, with a big
boost because we never do checkpoints either (i.e. 10% as stated by the
OP). There is a slight gain because of reduced lock contention, but I
wouldn't expect that to be major.

This comes back to the idea of deferred fsync we've spoken about a few
times, to provide "MySQL mode" performance and reduced robustness
guarantees. The idea of having a special backend perform the XLogWrites
rather than the individual backends is already a TODO item, even if this
implementation is somewhat more extreme in its approach to fsync delay.
The actual potential data loss is similar to an archive recovery though,
so I do like that touch - i.e. we might lose everything in the current
WAL file.

IMHO, it shouldn't be the bgwriter that fsyncs WAL because it cannot
clean shared_buffers and fsync the WAL concurrently with any
effectiveness. Perhaps such infrequent fsyncs mean thats not a problem.

Not checkpointing at all is not a good plan, since this will lead to an
enormous build up of WAL files and a very long recovery time if the
system does fail.

Overall, these choices effect the whole system whereas what I think we
need is something that can be applied to just certain transactions or
tables.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> Reading through the design, I see the following

> - bgwriter performs XLogWrite, not each backend
> - WAL fsync is only performed when WAL file fills
> - no checkpoints are performed until shutdown

> Not checkpointing at all is not a good plan, since this will lead to an
> enormous build up of WAL files and a very long recovery time if the
> system does fail.

Indeed, that seems utterly unacceptable; moreover it's hard to see how
you get a noticeable performance win, compared to a sane checkpoint
interval of some-fraction-of-an-hour.

regards, tom lane

Joshua D. Drake wrote:
> From an deployable application perspective, this could be a big deal. We
> are already starting to see very large traction in the Win32 desktop app
> arena.
>

There seem to be a few overlapping proposals in terms of reducing
various guarantees in the name of performance.

As more and more options are added that affect integrity (fsync, full
page writes, commit nowait, sigres) it might be nice to outline and
compare the approaches, and particularly to describe clearly the failure
scenarios and how they are significantly different from one another.

One potentially needs to track an increasing number of ways in which
items might be set which reduce certain guarantees on data integrity
which is unpleasant.

If a setting is wrong on a performance knob, no problem, when there are
complaints things are slow you can go through and adjust them. The same
is not true of data consistency. When the complaint comes it is usually
too late to fiddle with knobs.

I'm just thinking some caution should be exercised in adding too many of
them in the first place. I happen to love COMMIT NOWAIT though, for
many, this replaces fsync=off.

- August