PostgreSQL on tmpfs

Running a database on a memory file system serves a dual (and unfortunately not easy to deintertwine) purpose: testing database scalability and testing operatin system scalability. On the other hand, I did it just to see what the results could look like given an almost infinitely fast storage device :)

The results of running PostgreSQL 8.4 on a 8 core (2x quad Xeon 5405), 4 GB machine, with FreeBSD 8.0 AMD64 with pgbench scale of "70" (a bit over 1.2 GB database) are given in this graph:

I tried three combinations: TPC-B-like test on the "md" device (memory device / ramdrive with UFS mounted "async" on top of it), TPC-B-like on tmpfs and SELECT-only test on tmpfs.

Tmpfs was consistently a bit faster than md (even for the SELECT-only variant which is not present in this graph) so I guess it's mature enough. Though the tmpfs in 8.1 contains some small bugs AFAIK, the one which will be in 9 doesn't (or at least passes internal fsx tests).

So there it is - given an infinitely fast hard drive, PostgreSQL on this hardware and this OS would pull a bit over 6000 TPS (write transactions) or a bit over 40,000 read-only transactions. I think the write transaction rate is lower than it should be but I cannot think of any tuning to do which would affect a database on tmpfs (while at the same time keeping the configuration similar to "production"-ready, i.e. no cheating like turning off fsync).

Then there is the matter of falling performance after 8 clients (8 being the number of CPUs in the system). At the same time, the system idle time rises sharply to around 60% so we have the paradox of less performance with more idle time - it could be a sign of some bad lock contention.

#1 Re: PostgreSQL on tmpfs

Added on 2010-07-27T05:10 by Gordon

Your test is funny, sure that is non-sence when DBMS running on RAM. But it may be a good simple performance compare method for DBMS without disk IO constrain. ;-)

#2 Re: PostgreSQL on tmpfs

Added on 2010-07-27T07:50 by Iwan Budi Kusnanto

Not funny at all, there is "in memory database".

#3 Re: PostgreSQL on tmpfs

Added on 2011-02-10T09:09 by Paul

Tweaking /etc/fstab and postgresql making additional worker processes like nginx and having the the processes use libevent (epoll, kqueue, etc.) come to mind. It starts to become an kernel issue if you can't add additional cores.

#4 Re: PostgreSQL on tmpfs

Added on 2011-02-10T09:25 by Paul effective_io_concurrency probably would've helped.

#5 Re: PostgreSQL on tmpfs

Added on 2011-02-10T10:02 by Paul

Page 30: That guy may have been right.

Comments !