Re: Acclerating INSERT/UPDATE using UPS - Mailing list pgsql-hackers

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
downloadSigres 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
fromAdvanceXLInsertBuffer(). In addition, in XLogFileClose() issue_xlog_fsync() before close(). (In this point, Sigres
isdifferent 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
thissoftware 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月より所属が変わりました