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

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@commandprompt.com 
> <mailto: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
>     <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