Comment by sendob | Hacker News Reader

Comment by sendob | Hacker News Reader

Parent Full thread

from the link: "In Postgres 9.2, this improvement automatically becomes available without any further configuration."

"Essentially, it accomplishes this by reducing the lock contention surrounding an internal lock called WALWriteLock. When an individual backend/connection holds this lock, it is empowered to write WAL into wal_buffers, an area of shared memory that temporarily holds WAL until it is written, and ultimately flushed to persistent storage."

"With this patch, we don’t have the backends queue up for the WALWriteLock to write their WAL as before. Rather, they either immediately obtain the WALWriteLock, or else queue up for it. However, when the lock becomes available, no waiting backend actually immediately acquires the lock. Rather, each backend once again checks if WAL has been flushed up to the LSN that the transaction being committed needs to be flushed up to. Oftentimes, they will find that this has happened, and will be able to simply fastpath out of the function that ensures that WAL is flushed (a call to that function is required to honour transactional semantics). In fact, it is expected that only a small minority of backends (one at a time, dubbed “the leader”) will actually ever go through with flushing WAL. In this manner, we batch commits, resulting in a really large increase in throughput..."

I am sorry to cut and paste so much of the article, but I hope this is helpful?

Looking at your linked posting more closely I do not believe it is applicable to this situation for two reasons. Again I'm not an expert on postgresql but:

1) It appears to me that the technique discussed in your linked post is about ganging unrelated transactions together into a single flush to disk. I do not see that to be the case in the OP. Since all of the writes are going to the same table they are related.

2) Looking at the second graph in the linked post pgbench transactions/sec insert.sql. The number of clients is high. I got the impression from the OP that there was only a single client. Indeed if there were more than a few clients the benchmark would have been subject to the deficiencies of the client libraries used.

you are good to be suspicious into any benchmark(as even the original slides note). I dont think we really have any information as it relates to transaction boundaries present, nor clients used:

From the slides: "Scripts read a CSV ﬁle, parse it into the appropriate format, INSERT it into the database. • We measure total load time, including parsing time. • (COPY will be much much much faster.) • mongoimport too, most likely."

Postgres does its best to use intelligent defaults, but it is only a part of the system, it is generally up to practioners to be wary. Tools like: http://www.postgresql.org/docs/current/static/pgtestfsync.ht... Assist in this goal, but generally have to watch out for things like raid controllers that are not battery backed etc ( depending upon your environment).

There is no mention of the number of clients used directly.

I was simply trying to highlight that with the (little) information available, it is very possible.

It is also possible, that the session(s) should themselves choose to pursue an asynchronous commit strategy ( on a session level see:http://www.postgresql.org/docs/9.2/static/wal-async-commit.h... ) which would also not require modifying the configuration, I do not know as I have not seen the scripts, but it is similar to how a library could interact with:

http://docs.mongodb.org/manual/reference/command/getLastErro...

Thanks.

Nice discussion, thank you.

Reply on news.ycombinator.com