PostgreSQL user experience: FreeBSD (ZFS) vs OpenIndiana (ZFS) vs Linux (EXT4)

classic Classic list List threaded Threaded
3 messages Options
mm
Reply | Threaded
Open this post in threaded view
|

PostgreSQL user experience: FreeBSD (ZFS) vs OpenIndiana (ZFS) vs Linux (EXT4)

mm
Hi everyone,

I would like to share some of our expreience with PostgreSQL on FreeBSD.
It has been a while ago since we had to stop using FreeBSD for our
customer's PostgreSQL servers.

PostgreSQL (8.4 and 9.0) was demonstrating slow performance under heavy
loads and one day I decided to compare it to other alternatives on the
very same system (one 8-core with 16GB RAM, another 12-core with 48GB
RAM). With ZFS it was extremely slow and with UFS the speed was
acceptable (still 10-20% slower) if not under high load. Our databases
have a size from several gigabytes to tens of gigabytes.

A single real-world query on a idle system was noticeably slower on a
FreeBSD system than on the other systems (ZFS and UFS). We compared the
EXPLAIN ANALYZE output and the performance penalty was almost equally
spread on all items. With rising loads, PostgreSQL processes remain a
long time in "semwait" and "msgwait" states and the "top" output shows a
high system load on FreeBSD.

I have also tried different tunings, compilers and optimizations, but
with that I was able to gain only 5-10% better results.

The result of a pgbench run by one of my customers on a 12-core system
with 48GB RAM is here (FreeBSD ZFS vs OpenIndiana (ZFS) vs Linux (EXT4):
http://www.vx.sk/benchmarks/postgresql/pgbench_20110630.ods

So our decision so far is the following:
- if we are building a PostgreSQL server for heavy loads, we prefer
Solaris/OpenIndiana (ZFS or UFS) or Linux (EXT4) to FreeBSD (ZFS or UFS)
- if we want to use PostgreSQL on FreeBSD, we prefer UFS to ZFS

P.S: Our webservers still run FreeBSD and e.g. OpenIndiana (Solaris)
performed much worse in our high load real-world web application.

--
Martin Matuska
FreeBSD committer
http://blog.vx.sk

_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL user experience: FreeBSD (ZFS) vs OpenIndiana (ZFS) vs Linux (EXT4)

Adam Vande More
On Sat, Dec 17, 2011 at 7:26 AM, Martin Matuska <[hidden email]> wrote:

> I would like to share some of our expreience with PostgreSQL on FreeBSD.
> It has been a while ago since we had to stop using FreeBSD for our
> customer's PostgreSQL servers.
>

I don't claim any expertise in this area so please take this with that in
mind.

A couple of things that might help.

Setting zfs set sync=disabled on fs where the WAL resides(all alone).  Of
course this would help ZFS regardless of OS.

http://www.postgresql.org/docs/8.4/static/wal-intro.html

I've also seen other reports of slow PostgreSQL on FreeBSD, and in that
scenario there were a large amount of gettimeofday(2) calls as the
bottleneck and such calls on FreeBSD are significantly slower.

http://postgresql.1045698.n5.nabble.com/Postgresql-9-0-2-explain-analyze-very-slow-10x-compared-to-actual-query-time-td3336664.html

If you have a faster timecounter, it might help to switch.

--
Adam Vande More
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL user experience: FreeBSD (ZFS) vs OpenIndiana (ZFS) vs Linux (EXT4)

Eduardo Morras
In reply to this post by mm
At 14:26 17/12/2011, you wrote:

>Hi everyone,
>
>I would like to share some of our expreience with PostgreSQL on FreeBSD.
>It has been a while ago since we had to stop using FreeBSD for our
>customer's PostgreSQL servers.
>
>PostgreSQL (8.4 and 9.0) was demonstrating slow performance under heavy
>loads and one day I decided to compare it to other alternatives on the
>very same system (one 8-core with 16GB RAM, another 12-core with 48GB
>RAM). With ZFS it was extremely slow and with UFS the speed was
>acceptable (still 10-20% slower) if not under high load. Our databases
>have a size from several gigabytes to tens of gigabytes.
>
>A single real-world query on a idle system was noticeably slower on a
>FreeBSD system than on the other systems (ZFS and UFS). We compared the
>EXPLAIN ANALYZE output and the performance penalty was almost equally
>spread on all items. With rising loads, PostgreSQL processes remain a
>long time in "semwait" and "msgwait" states and the "top" output shows a
>high system load on FreeBSD.
>
>I have also tried different tunings, compilers and optimizations, but
>with that I was able to gain only 5-10% better results.
>
>The result of a pgbench run by one of my customers on a 12-core system
>with 48GB RAM is here (FreeBSD ZFS vs OpenIndiana (ZFS) vs Linux (EXT4):
>http://www.vx.sk/benchmarks/postgresql/pgbench_20110630.ods
>
>So our decision so far is the following:
>- if we are building a PostgreSQL server for heavy loads, we prefer
>Solaris/OpenIndiana (ZFS or UFS) or Linux (EXT4) to FreeBSD (ZFS or UFS)
>- if we want to use PostgreSQL on FreeBSD, we prefer UFS to ZFS
>
>P.S: Our webservers still run FreeBSD and e.g. OpenIndiana (Solaris)
>performed much worse in our high load real-world web application.

What FreeBSD tunings have you used in tests? Have you set
kern.ipc.shm_use_phys=1? Have you tuned the FreeBSD semaphores? Them
increased more than 5-10% on my server, i increased clients from 25
(with saturation problems) to 40 (without problems). About UFS/ZFS, i
prefer UFS, it has less memory/cpu footprint that can use for more
postgresql cache and tps.

>--
>Martin Matuska
>FreeBSD committer
>http://blog.vx.sk


_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "[hidden email]"