Re: Acclerating INSERT/UPDATE using UPS

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