Acclerating INSERT/UPDATE using UPS

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@postgresql.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:

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@postgresql.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@postgresql.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@commandprompt.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@cs.tsukuba.ac.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@cs.tsukuba.ac.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:

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@neopolitan.com

Gene <genekhart@gmail.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@cs.tsukuba.ac.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@gmail.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@commandprompt.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,

Thanks for your comments and I am sorry for my bad response because of
my business. In the first place, criticisms are welcome since without
criticisms and discussions, I cannot improve my idea.

Christopher, thanks to your comments, I understood why this community
does not incline to accept Sigres approach.
Reason 1: Sigres method may make PostgreSQL less robust.
Reason 2: There is a not-inconsiderable risk that people will fail to
read warnings

Let me write my opinions for the above.

Opinion for Reason 1:
Right. Apparently Sigres method makes PostgreSQL less robust. Thus I
propose to use Sigres method as an option like fsync=off. In my
understanding, fsync=off option is ambiguous. I think unfortunately it
is unclear when exactly the option should be used. Even if one uses
UPS, the option is dangerous because the final issue_xlog_sync called
by XLogWrite called by CreateCheckPoint called by ShutdownXLOG called
by bgWriter process at postgres shutdown is not executed. (Sorry for
the exact but wired descriptions). Without using UPS, the option never
be used.
On the other hand, *IF* UPS is reliable, Sigres assures
synchronization of wal records to a disk because Sigres executes
issue_xlog_fsync at each XLogFileClose() and at shutdown. That is, the
question would be expressed as "Can an administrator have a reliable
UPS system ?" If an administrator is confident on his/her UPS system,
the administrator may select Sigres method. In summary, fsync=off is
ambiguous option, and Sigres can be used *IF* an administrator is
confident on his/her UPS system.

Opinion for Reason 2:
Right. I think it is easy to avoid the risk. Sigres should not be used
in default such as fsync=off. If my opinion lacks in common sense, I
am sorry.

Regards,

-- Hideyuki

Christopher Browne wrote:

Christopher,

Thanks for your advices !
OK, I will submit a patch as soon as possible.
In the patch, Sigres will be activated by postgresql.conf.

Best Regards,

-- Hideyuki

A long time ago, in a galaxy far, far away, kawasima@cs.tsukuba.ac.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? ---------------------------(end of
broadcast)--------------------------- TIP 3: Have you checked our
extensive FAQ? http://www.postgresql.org/docs/faq

Andrew,

Your experience is really informative. Thanks for giving me such a
really precious information. Since I am a researcher, I rarely faces
on real troubles.

Andrew, I agree with you. UPS is not reliable all the time.
On the other hand, however, disks or raids are also not reliable all the time.
Moreover, on the earth, there is NO device which completely assures
durability of the ACID transaction property. Even if disks and power
supplies are perfect, the system would be destroyed by an earthquake
(thus, disaster recovery techniques are recently popular). Thus I
think the problem is not in a device. It is in the management of
system administrators. (However, this is just my opinion and it is not
objective one required by large questionnaire).

On the other hand, speed is required. My first motivation of this work
is a friend of mine who is working at a financial firm. He strongly
required performance of UPDATE/INSERT on the database servers in his
company (the database was a major commercial product). I think in such
a rich environment, strong power management facilities may be expected
though this is just my one thought).

In summary, I think the problem lies in system management rather than
device itself.

Regards,

-- Hideyuki

J. Andrew Rogers wrote: