FreeBSD 10 and PostgreSQL 9.3 scalability issues

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

FreeBSD 10 and PostgreSQL 9.3 scalability issues

Petr Janda
Hi guys,

Just want to share these pgbench results done by DragonFlyBSD, and would
like some input on why these numbers look so bad and what can be done to
improve (ie. kernel tunables etc) the performance.

http://lists.dragonflybsd.org/pipermail/users/attachments/20140310/4250b961/attachment-0001.pdf

Cheers,
Peter
_______________________________________________
[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: FreeBSD 10 and PostgreSQL 9.3 scalability issues

Steven Hartland
----- Original Message -----
From: "Petr Janda" <[hidden email]>


> Hi guys,
>
> Just want to share these pgbench results done by DragonFlyBSD, and would
> like some input on why these numbers look so bad and what can be done to
> improve (ie. kernel tunables etc) the performance.
>
> http://lists.dragonflybsd.org/pipermail/users/attachments/20140310/4250b961/attachment-0001.pdf


Do you have the ability to test with FreeBSD 8.x and 9.x to see if this is
regression?

Also you don't mention the FS used in each case, so I'm wondering if you
used a ZFS install of FreeBSD which could help to explain things.

    Regards
    Steve
_______________________________________________
[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: FreeBSD 10 and PostgreSQL 9.3 scalability issues

Matthew Seaman-5
In reply to this post by Petr Janda
On 03/18/14 03:12, Petr Janda wrote:
> ust want to share these pgbench results done by DragonFlyBSD, and would
> like some input on why these numbers look so bad and what can be done to
> improve (ie. kernel tunables etc) the performance.
>
> http://lists.dragonflybsd.org/pipermail/users/attachments/20140310/4250b961/attachment-0001.pdf

Using ZFS as the backing for a RDBMS without:

    * Separate (fast) L2ARC devices
    * Tuning the ZFS block size to match the postgres IO block size
    * Setting primarycache to metadata
    * Tuning the ARC max so ZFS doesn't eat all the RAM
    * probably other things I can remember off-hand.

That's what is wrong.  ZFS is known to work particularly badly at the
sort of small random IOs that RDBMSes generate (mostly because of the
copy-on-write thing) without special tuning and extra hardware for
caches.  ie.  You can't construct a fair test of database performance
against other OSes/filesystems if you restrict yourself to using exactly
the same hardware.

Basically, install the FreeBSD box on UFS2 and try again.

        Cheers,

        Matthew



signature.asc (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: FreeBSD 10 and PostgreSQL 9.3 scalability issues

Adrian Chadd-2
Hi,

the pgsql testing done has been analysed by a few developers. The
TL;DR version is that there's significant lock contention in the VM /
mmap path and it sticks out like a sore thumb when one does lock
profiling.


-a


On 18 March 2014 05:00, Matthew Seaman <[hidden email]> wrote:

> On 03/18/14 03:12, Petr Janda wrote:
>> ust want to share these pgbench results done by DragonFlyBSD, and would
>> like some input on why these numbers look so bad and what can be done to
>> improve (ie. kernel tunables etc) the performance.
>>
>> http://lists.dragonflybsd.org/pipermail/users/attachments/20140310/4250b961/attachment-0001.pdf
>
> Using ZFS as the backing for a RDBMS without:
>
>     * Separate (fast) L2ARC devices
>     * Tuning the ZFS block size to match the postgres IO block size
>     * Setting primarycache to metadata
>     * Tuning the ARC max so ZFS doesn't eat all the RAM
>     * probably other things I can remember off-hand.
>
> That's what is wrong.  ZFS is known to work particularly badly at the
> sort of small random IOs that RDBMSes generate (mostly because of the
> copy-on-write thing) without special tuning and extra hardware for
> caches.  ie.  You can't construct a fair test of database performance
> against other OSes/filesystems if you restrict yourself to using exactly
> the same hardware.
>
> Basically, install the FreeBSD box on UFS2 and try again.
>
>         Cheers,
>
>         Matthew
>
>
_______________________________________________
[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: FreeBSD 10 and PostgreSQL 9.3 scalability issues

Petr Janda
In reply to this post by Steven Hartland
Hi,

As far as I know, the test was done on both UFS2 and ZFS and the
difference was marginal.

Petr

On 18/03/2014 10:47 PM, Steven Hartland wrote:

> ----- Original Message ----- From: "Petr Janda" <[hidden email]>
>
>
>> Hi guys,
>>
>> Just want to share these pgbench results done by DragonFlyBSD, and would
>> like some input on why these numbers look so bad and what can be done to
>> improve (ie. kernel tunables etc) the performance.
>>
>> http://lists.dragonflybsd.org/pipermail/users/attachments/20140310/4250b961/attachment-0001.pdf
>>
>
>
> Do you have the ability to test with FreeBSD 8.x and 9.x to see if this is
> regression?
>
> Also you don't mention the FS used in each case, so I'm wondering if you
> used a ZFS install of FreeBSD which could help to explain things.
>
>    Regards
>    Steve

_______________________________________________
[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: FreeBSD 10 and PostgreSQL 9.3 scalability issues

Sean Chittenden-4
> As far as I know, the test was done on both UFS2 and ZFS and the
> difference was marginal.

As Adrian pointed out, there is an mmap(2) mutex in the way. Starting in PostgreSQL 9.3, shared buffers are allocated out of mmap(2) instead of shm. shm is only used to notify the PostgreSQL postmaster that a child process exited/crashed (when a pid detaches from a shm segment, there is a kernel event, but there is no kernel event when detaching from an mmap(2) region). -sc

http://www.postgresql.org/docs/9.3/static/release-9-3.html#AEN115039


>>> Just want to share these pgbench results done by DragonFlyBSD, and would
>>> like some input on why these numbers look so bad and what can be done to
>>> improve (ie. kernel tunables etc) the performance.
>>>
>>> http://lists.dragonflybsd.org/pipermail/users/attachments/20140310/4250b961/attachment-0001.pdf
>>>
>>
>>
>> Do you have the ability to test with FreeBSD 8.x and 9.x to see if this is
>> regression?
>>
>> Also you don't mention the FS used in each case, so I'm wondering if you
>> used a ZFS install of FreeBSD which could help to explain things.


--
Sean Chittenden
[hidden email]

_______________________________________________
[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: FreeBSD 10 and PostgreSQL 9.3 scalability issues

Palle Girgensohn-2


Den torsdagen den 20:e mars 2014 kl. 00:33:10 UTC+1 skrev Sean Chittenden:

>
> > As far as I know, the test was done on both UFS2 and ZFS and the
> > difference was marginal.
>
> As Adrian pointed out, there is an mmap(2) mutex in the way. Starting in
> PostgreSQL 9.3, shared buffers are allocated out of mmap(2) instead of shm.
> shm is only used to notify the PostgreSQL postmaster that a child process
> exited/crashed (when a pid detaches from a shm segment, there is a kernel
> event, but there is no kernel event when detaching from an mmap(2) region).
> -sc
>
> http://www.postgresql.org/docs/9.3/static/release-9-3.html#AEN115039 
>
>
> >>> Just want to share these pgbench results done by DragonFlyBSD, and
> would
> >>> like some input on why these numbers look so bad and what can be done
> to
> >>> improve (ie. kernel tunables etc) the performance.
> >>>
> >>>
> http://lists.dragonflybsd.org/pipermail/users/attachments/20140310/4250b961/attachment-0001.pdf 
> >>>
> >>
> >>
> >> Do you have the ability to test with FreeBSD 8.x and 9.x to see if this
> is
> >> regression?
> >>
> >> Also you don't mention the FS used in each case, so I'm wondering if
> you
> >> used a ZFS install of FreeBSD which could help to explain things.
>
>
> --
> Sean Chittenden
> [hidden email] <javascript:>
>
>
>
Hi,

There is a fresh thread about this in postgresql-hackers [1].

There are two parallel approaches suggested there, where one is to have an
option to continue using the old SYSV shared memory in PostgreSQL, and the
other is the suggestion that "somebody needs to hold the FreeBSD folks'
feet to the fire about when we can expect to see a fix from their side."

Looking at the original post in this thread, it seems to me that FreeBSD
has scalability problems beyond what the SYSV vs mmap change in PostgreSQL
introduces? Check my test of PostgreSQL 9.2 vs 9.3 on FreeBSD 10.0 at [1].
The difference between PG92 and PG93 is not huge, ~17%. The difference
between FreeBSD and the other OS:es in this thread's original post's
performance chart seems to be about a lot more?

Palle

[1]
http://www.postgresql.org/message-id/2AE143D2-87D3-4AD1-AC78-CE2258230C05@... 
_______________________________________________
[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: FreeBSD 10 and PostgreSQL 9.3 scalability issues

Adrian Chadd-2
Hi,

Are you able to repeat these tests (for both 9.2 and 9.3) whilst
grabbing some performance data from lock profiling and hwpmc?

The benchmarking is great but it doesn't tell us enough information as
to "why" things behave poorly compared to Linux and why the mmap drop
isn't so great.

What about with more clients? 64? 128? 256?


Thanks!



-a


On 21 April 2014 14:11, Palle Girgensohn <[hidden email]> wrote:

>
>
> Den torsdagen den 20:e mars 2014 kl. 00:33:10 UTC+1 skrev Sean Chittenden:
>>
>> > As far as I know, the test was done on both UFS2 and ZFS and the
>> > difference was marginal.
>>
>> As Adrian pointed out, there is an mmap(2) mutex in the way. Starting in
>> PostgreSQL 9.3, shared buffers are allocated out of mmap(2) instead of shm.
>> shm is only used to notify the PostgreSQL postmaster that a child process
>> exited/crashed (when a pid detaches from a shm segment, there is a kernel
>> event, but there is no kernel event when detaching from an mmap(2) region).
>> -sc
>>
>> http://www.postgresql.org/docs/9.3/static/release-9-3.html#AEN115039
>>
>>
>> >>> Just want to share these pgbench results done by DragonFlyBSD, and
>> would
>> >>> like some input on why these numbers look so bad and what can be done
>> to
>> >>> improve (ie. kernel tunables etc) the performance.
>> >>>
>> >>>
>> http://lists.dragonflybsd.org/pipermail/users/attachments/20140310/4250b961/attachment-0001.pdf
>> >>>
>> >>
>> >>
>> >> Do you have the ability to test with FreeBSD 8.x and 9.x to see if this
>> is
>> >> regression?
>> >>
>> >> Also you don't mention the FS used in each case, so I'm wondering if
>> you
>> >> used a ZFS install of FreeBSD which could help to explain things.
>>
>>
>> --
>> Sean Chittenden
>> [hidden email] <javascript:>
>>
>>
>>
> Hi,
>
> There is a fresh thread about this in postgresql-hackers [1].
>
> There are two parallel approaches suggested there, where one is to have an
> option to continue using the old SYSV shared memory in PostgreSQL, and the
> other is the suggestion that "somebody needs to hold the FreeBSD folks'
> feet to the fire about when we can expect to see a fix from their side."
>
> Looking at the original post in this thread, it seems to me that FreeBSD
> has scalability problems beyond what the SYSV vs mmap change in PostgreSQL
> introduces? Check my test of PostgreSQL 9.2 vs 9.3 on FreeBSD 10.0 at [1].
> The difference between PG92 and PG93 is not huge, ~17%. The difference
> between FreeBSD and the other OS:es in this thread's original post's
> performance chart seems to be about a lot more?
>
> Palle
>
> [1]
> http://www.postgresql.org/message-id/2AE143D2-87D3-4AD1-AC78-CE2258230C05@...
> _______________________________________________
> [hidden email] mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
> To unsubscribe, send any mail to "[hidden email]"
_______________________________________________
[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: FreeBSD 10 and PostgreSQL 9.3 scalability issues

Palle Girgensohn-2


> 23 apr 2014 kl. 01:04 skrev Adrian Chadd <[hidden email]>:
>
> Hi,
>
> Are you able to repeat these tests (for both 9.2 and 9.3) whilst
> grabbing some performance data from lock profiling and hwpmc?

I sure can, but I'd love some pointers as to how this is done. Please? :-)

>
> The benchmarking is great but it doesn't tell us enough information as
> to "why" things behave poorly compared to Linux and why the mmap drop
> isn't so great.


As per the discussion on postresql-hackers, the regression between pg9.2 and pg9.3, which includes the sysv->mmap shift, *might* also exist, at least partly, on Linux as well.

The initial post in *this* thread does however indicate that freebsd performs poorer than Linux and dragonflybsd, but does not really compare PostgreSQL versions.

Just so we're not pursuing the wrong problem here, let's be open minded about the definition of the problem. :-)

>
> What about with more clients? 64? 128? 256?

My test went to 80. I can go higher as well, though other sources say 50 is a reasonable limit for PostgreSQL.

Palle


>
>
> Thanks!
>
>
>
> -a
>
>
>> On 21 April 2014 14:11, Palle Girgensohn <[hidden email]> wrote:
>>
>>
>>> Den torsdagen den 20:e mars 2014 kl. 00:33:10 UTC+1 skrev Sean Chittenden:
>>>
>>>> As far as I know, the test was done on both UFS2 and ZFS and the
>>>> difference was marginal.
>>>
>>> As Adrian pointed out, there is an mmap(2) mutex in the way. Starting in
>>> PostgreSQL 9.3, shared buffers are allocated out of mmap(2) instead of shm.
>>> shm is only used to notify the PostgreSQL postmaster that a child process
>>> exited/crashed (when a pid detaches from a shm segment, there is a kernel
>>> event, but there is no kernel event when detaching from an mmap(2) region).
>>> -sc
>>>
>>> http://www.postgresql.org/docs/9.3/static/release-9-3.html#AEN115039
>>>
>>>
>>>>>> Just want to share these pgbench results done by DragonFlyBSD, and
>>> would
>>>>>> like some input on why these numbers look so bad and what can be done
>>> to
>>>>>> improve (ie. kernel tunables etc) the performance.
>>> http://lists.dragonflybsd.org/pipermail/users/attachments/20140310/4250b961/attachment-0001.pdf
>>>>>
>>>>>
>>>>> Do you have the ability to test with FreeBSD 8.x and 9.x to see if this
>>> is
>>>>> regression?
>>>>>
>>>>> Also you don't mention the FS used in each case, so I'm wondering if
>>> you
>>>>> used a ZFS install of FreeBSD which could help to explain things.
>>>
>>>
>>> --
>>> Sean Chittenden
>>> [hidden email] <javascript:>
>> Hi,
>>
>> There is a fresh thread about this in postgresql-hackers [1].
>>
>> There are two parallel approaches suggested there, where one is to have an
>> option to continue using the old SYSV shared memory in PostgreSQL, and the
>> other is the suggestion that "somebody needs to hold the FreeBSD folks'
>> feet to the fire about when we can expect to see a fix from their side."
>>
>> Looking at the original post in this thread, it seems to me that FreeBSD
>> has scalability problems beyond what the SYSV vs mmap change in PostgreSQL
>> introduces? Check my test of PostgreSQL 9.2 vs 9.3 on FreeBSD 10.0 at [1].
>> The difference between PG92 and PG93 is not huge, ~17%. The difference
>> between FreeBSD and the other OS:es in this thread's original post's
>> performance chart seems to be about a lot more?
>>
>> Palle
>>
>> [1]
>> http://www.postgresql.org/message-id/2AE143D2-87D3-4AD1-AC78-CE2258230C05@...
>> _______________________________________________
>> [hidden email] mailing list
>> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>> To unsubscribe, send any mail to "[hidden email]"
_______________________________________________
[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: FreeBSD 10 and PostgreSQL 9.3 scalability issues

Gezeala M. Bacuño II
Do you guys have any updates on this?

--

regards

gezeala bacuño II


On Tue, Apr 22, 2014 at 11:52 PM, Palle Girgensohn <[hidden email]>wrote:

>
>
> > 23 apr 2014 kl. 01:04 skrev Adrian Chadd <[hidden email]>:
> >
> > Hi,
> >
> > Are you able to repeat these tests (for both 9.2 and 9.3) whilst
> > grabbing some performance data from lock profiling and hwpmc?
>
> I sure can, but I'd love some pointers as to how this is done. Please? :-)
>
> >
> > The benchmarking is great but it doesn't tell us enough information as
> > to "why" things behave poorly compared to Linux and why the mmap drop
> > isn't so great.
>
>
> As per the discussion on postresql-hackers, the regression between pg9.2
> and pg9.3, which includes the sysv->mmap shift, *might* also exist, at
> least partly, on Linux as well.
>
> The initial post in *this* thread does however indicate that freebsd
> performs poorer than Linux and dragonflybsd, but does not really compare
> PostgreSQL versions.
>
> Just so we're not pursuing the wrong problem here, let's be open minded
> about the definition of the problem. :-)
>
> >
> > What about with more clients? 64? 128? 256?
>
> My test went to 80. I can go higher as well, though other sources say 50
> is a reasonable limit for PostgreSQL.
>
> Palle
>
>
> >
> >
> > Thanks!
> >
> >
> >
> > -a
> >
> >
> >> On 21 April 2014 14:11, Palle Girgensohn <[hidden email]> wrote:
> >>
> >>
> >>> Den torsdagen den 20:e mars 2014 kl. 00:33:10 UTC+1 skrev Sean
> Chittenden:
> >>>
> >>>> As far as I know, the test was done on both UFS2 and ZFS and the
> >>>> difference was marginal.
> >>>
> >>> As Adrian pointed out, there is an mmap(2) mutex in the way. Starting
> in
> >>> PostgreSQL 9.3, shared buffers are allocated out of mmap(2) instead of
> shm.
> >>> shm is only used to notify the PostgreSQL postmaster that a child
> process
> >>> exited/crashed (when a pid detaches from a shm segment, there is a
> kernel
> >>> event, but there is no kernel event when detaching from an mmap(2)
> region).
> >>> -sc
> >>>
> >>> http://www.postgresql.org/docs/9.3/static/release-9-3.html#AEN115039
> >>>
> >>>
> >>>>>> Just want to share these pgbench results done by DragonFlyBSD, and
> >>> would
> >>>>>> like some input on why these numbers look so bad and what can be
> done
> >>> to
> >>>>>> improve (ie. kernel tunables etc) the performance.
> >>>
> http://lists.dragonflybsd.org/pipermail/users/attachments/20140310/4250b961/attachment-0001.pdf
> >>>>>
> >>>>>
> >>>>> Do you have the ability to test with FreeBSD 8.x and 9.x to see if
> this
> >>> is
> >>>>> regression?
> >>>>>
> >>>>> Also you don't mention the FS used in each case, so I'm wondering if
> >>> you
> >>>>> used a ZFS install of FreeBSD which could help to explain things.
> >>>
> >>>
> >>> --
> >>> Sean Chittenden
> >>> [hidden email] <javascript:>
> >> Hi,
> >>
> >> There is a fresh thread about this in postgresql-hackers [1].
> >>
> >> There are two parallel approaches suggested there, where one is to have
> an
> >> option to continue using the old SYSV shared memory in PostgreSQL, and
> the
> >> other is the suggestion that "somebody needs to hold the FreeBSD folks'
> >> feet to the fire about when we can expect to see a fix from their side."
> >>
> >> Looking at the original post in this thread, it seems to me that FreeBSD
> >> has scalability problems beyond what the SYSV vs mmap change in
> PostgreSQL
> >> introduces? Check my test of PostgreSQL 9.2 vs 9.3 on FreeBSD 10.0 at
> [1].
> >> The difference between PG92 and PG93 is not huge, ~17%. The difference
> >> between FreeBSD and the other OS:es in this thread's original post's
> >> performance chart seems to be about a lot more?
> >>
> >> Palle
> >>
> >> [1]
> >>
> http://www.postgresql.org/message-id/2AE143D2-87D3-4AD1-AC78-CE2258230C05@...
> >> _______________________________________________
> >> [hidden email] mailing list
> >> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
> >> To unsubscribe, send any mail to "
> [hidden email]"
> _______________________________________________
> [hidden email] mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
> To unsubscribe, send any mail to "
> [hidden email]"
>
_______________________________________________
[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: FreeBSD 10 and PostgreSQL 9.3 scalability issues

Palle Girgensohn-2
I got no response about how to grab performance data.

The PostgreSQL team is also making an effort by setting up machines dedicated to performance measuring and tuning.

And freebsd guys and PostgreSQL guys are apparently meeting at pgcon this week.

We'll see where that leads.

In the mean time, if I for some pointers on how to grab performance data, I could do some more tests.

Palle

> 21 maj 2014 kl. 02:13 skrev Gezeala M. Bacuño II <[hidden email]>:
>
>
> Do you guys have any updates on this?
>
> --
>
> regards
>
> gezeala bacuño II
>
>
>> On Tue, Apr 22, 2014 at 11:52 PM, Palle Girgensohn <[hidden email]> wrote:
>>
>>
>> > 23 apr 2014 kl. 01:04 skrev Adrian Chadd <[hidden email]>:
>> >
>> > Hi,
>> >
>> > Are you able to repeat these tests (for both 9.2 and 9.3) whilst
>> > grabbing some performance data from lock profiling and hwpmc?
>>
>> I sure can, but I'd love some pointers as to how this is done. Please? :-)
>>
>> >
>> > The benchmarking is great but it doesn't tell us enough information as
>> > to "why" things behave poorly compared to Linux and why the mmap drop
>> > isn't so great.
>>
>>
>> As per the discussion on postresql-hackers, the regression between pg9.2 and pg9.3, which includes the sysv->mmap shift, *might* also exist, at least partly, on Linux as well.
>>
>> The initial post in *this* thread does however indicate that freebsd performs poorer than Linux and dragonflybsd, but does not really compare PostgreSQL versions.
>>
>> Just so we're not pursuing the wrong problem here, let's be open minded about the definition of the problem. :-)
>>
>> >
>> > What about with more clients? 64? 128? 256?
>>
>> My test went to 80. I can go higher as well, though other sources say 50 is a reasonable limit for PostgreSQL.
>>
>> Palle
>>
>>
>> >
>> >
>> > Thanks!
>> >
>> >
>> >
>> > -a
>> >
>> >
>> >> On 21 April 2014 14:11, Palle Girgensohn <[hidden email]> wrote:
>> >>
>> >>
>> >>> Den torsdagen den 20:e mars 2014 kl. 00:33:10 UTC+1 skrev Sean Chittenden:
>> >>>
>> >>>> As far as I know, the test was done on both UFS2 and ZFS and the
>> >>>> difference was marginal.
>> >>>
>> >>> As Adrian pointed out, there is an mmap(2) mutex in the way. Starting in
>> >>> PostgreSQL 9.3, shared buffers are allocated out of mmap(2) instead of shm.
>> >>> shm is only used to notify the PostgreSQL postmaster that a child process
>> >>> exited/crashed (when a pid detaches from a shm segment, there is a kernel
>> >>> event, but there is no kernel event when detaching from an mmap(2) region).
>> >>> -sc
>> >>>
>> >>> http://www.postgresql.org/docs/9.3/static/release-9-3.html#AEN115039
>> >>>
>> >>>
>> >>>>>> Just want to share these pgbench results done by DragonFlyBSD, and
>> >>> would
>> >>>>>> like some input on why these numbers look so bad and what can be done
>> >>> to
>> >>>>>> improve (ie. kernel tunables etc) the performance.
>> >>> http://lists.dragonflybsd.org/pipermail/users/attachments/20140310/4250b961/attachment-0001.pdf
>> >>>>>
>> >>>>>
>> >>>>> Do you have the ability to test with FreeBSD 8.x and 9.x to see if this
>> >>> is
>> >>>>> regression?
>> >>>>>
>> >>>>> Also you don't mention the FS used in each case, so I'm wondering if
>> >>> you
>> >>>>> used a ZFS install of FreeBSD which could help to explain things.
>> >>>
>> >>>
>> >>> --
>> >>> Sean Chittenden
>> >>> [hidden email] <javascript:>
>> >> Hi,
>> >>
>> >> There is a fresh thread about this in postgresql-hackers [1].
>> >>
>> >> There are two parallel approaches suggested there, where one is to have an
>> >> option to continue using the old SYSV shared memory in PostgreSQL, and the
>> >> other is the suggestion that "somebody needs to hold the FreeBSD folks'
>> >> feet to the fire about when we can expect to see a fix from their side."
>> >>
>> >> Looking at the original post in this thread, it seems to me that FreeBSD
>> >> has scalability problems beyond what the SYSV vs mmap change in PostgreSQL
>> >> introduces? Check my test of PostgreSQL 9.2 vs 9.3 on FreeBSD 10.0 at [1].
>> >> The difference between PG92 and PG93 is not huge, ~17%. The difference
>> >> between FreeBSD and the other OS:es in this thread's original post's
>> >> performance chart seems to be about a lot more?
>> >>
>> >> Palle
>> >>
>> >> [1]
>> >> http://www.postgresql.org/message-id/2AE143D2-87D3-4AD1-AC78-CE2258230C05@...
>> >> _______________________________________________
>> >> [hidden email] mailing list
>> >> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>> >> To unsubscribe, send any mail to "[hidden email]"
>> _______________________________________________
>> [hidden email] mailing list
>> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>> To unsubscribe, send any mail to "[hidden email]"
>
_______________________________________________
[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: FreeBSD 10 and PostgreSQL 9.3 scalability issues

Gezeala M. Bacuño II
Gotcha.

I've been testing using pgbench on FreeBSD 9.0 release + ZFS + pg 9.3.. I
can reach the freebsd 10 stats on the pdf files if the dataset <RAM. It
gets way lower if the dataset >RAM. Test was done on pools without L2ARC
and with/without compression. I also remember increasing a vm.pmap sysctl.
I'm out of the office right now sick so I can't provide the stats but yes,
with mmap it is pretty bad..

Keep us in the loop. I'd like to help on getting the performance data they
need.
On May 20, 2014 11:16 PM, "Palle Girgensohn" <[hidden email]> wrote:

> I got no response about how to grab performance data.
>
> The PostgreSQL team is also making an effort by setting up machines
> dedicated to performance measuring and tuning.
>
> And freebsd guys and PostgreSQL guys are apparently meeting at pgcon this
> week.
>
> We'll see where that leads.
>
> In the mean time, if I for some pointers on how to grab performance data,
> I could do some more tests.
>
> Palle
>
> 21 maj 2014 kl. 02:13 skrev Gezeala M. Bacuño II <[hidden email]>:
>
>
> Do you guys have any updates on this?
>
> --
>
> regards
>
> gezeala bacuño II
>
>
> On Tue, Apr 22, 2014 at 11:52 PM, Palle Girgensohn <[hidden email]>wrote:
>
>>
>>
>> > 23 apr 2014 kl. 01:04 skrev Adrian Chadd <[hidden email]>:
>> >
>> > Hi,
>> >
>> > Are you able to repeat these tests (for both 9.2 and 9.3) whilst
>> > grabbing some performance data from lock profiling and hwpmc?
>>
>> I sure can, but I'd love some pointers as to how this is done. Please? :-)
>>
>> >
>> > The benchmarking is great but it doesn't tell us enough information as
>> > to "why" things behave poorly compared to Linux and why the mmap drop
>> > isn't so great.
>>
>>
>> As per the discussion on postresql-hackers, the regression between pg9.2
>> and pg9.3, which includes the sysv->mmap shift, *might* also exist, at
>> least partly, on Linux as well.
>>
>> The initial post in *this* thread does however indicate that freebsd
>> performs poorer than Linux and dragonflybsd, but does not really compare
>> PostgreSQL versions.
>>
>> Just so we're not pursuing the wrong problem here, let's be open minded
>> about the definition of the problem. :-)
>>
>> >
>> > What about with more clients? 64? 128? 256?
>>
>> My test went to 80. I can go higher as well, though other sources say 50
>> is a reasonable limit for PostgreSQL.
>>
>> Palle
>>
>>
>> >
>> >
>> > Thanks!
>> >
>> >
>> >
>> > -a
>> >
>> >
>> >> On 21 April 2014 14:11, Palle Girgensohn <[hidden email]> wrote:
>> >>
>> >>
>> >>> Den torsdagen den 20:e mars 2014 kl. 00:33:10 UTC+1 skrev Sean
>> Chittenden:
>> >>>
>> >>>> As far as I know, the test was done on both UFS2 and ZFS and the
>> >>>> difference was marginal.
>> >>>
>> >>> As Adrian pointed out, there is an mmap(2) mutex in the way. Starting
>> in
>> >>> PostgreSQL 9.3, shared buffers are allocated out of mmap(2) instead
>> of shm.
>> >>> shm is only used to notify the PostgreSQL postmaster that a child
>> process
>> >>> exited/crashed (when a pid detaches from a shm segment, there is a
>> kernel
>> >>> event, but there is no kernel event when detaching from an mmap(2)
>> region).
>> >>> -sc
>> >>>
>> >>> http://www.postgresql.org/docs/9.3/static/release-9-3.html#AEN115039
>> >>>
>> >>>
>> >>>>>> Just want to share these pgbench results done by DragonFlyBSD, and
>> >>> would
>> >>>>>> like some input on why these numbers look so bad and what can be
>> done
>> >>> to
>> >>>>>> improve (ie. kernel tunables etc) the performance.
>> >>>
>> http://lists.dragonflybsd.org/pipermail/users/attachments/20140310/4250b961/attachment-0001.pdf
>> >>>>>
>> >>>>>
>> >>>>> Do you have the ability to test with FreeBSD 8.x and 9.x to see if
>> this
>> >>> is
>> >>>>> regression?
>> >>>>>
>> >>>>> Also you don't mention the FS used in each case, so I'm wondering if
>> >>> you
>> >>>>> used a ZFS install of FreeBSD which could help to explain things.
>> >>>
>> >>>
>> >>> --
>> >>> Sean Chittenden
>> >>> [hidden email] <javascript:>
>> >> Hi,
>> >>
>> >> There is a fresh thread about this in postgresql-hackers [1].
>> >>
>> >> There are two parallel approaches suggested there, where one is to
>> have an
>> >> option to continue using the old SYSV shared memory in PostgreSQL, and
>> the
>> >> other is the suggestion that "somebody needs to hold the FreeBSD folks'
>> >> feet to the fire about when we can expect to see a fix from their
>> side."
>> >>
>> >> Looking at the original post in this thread, it seems to me that
>> FreeBSD
>> >> has scalability problems beyond what the SYSV vs mmap change in
>> PostgreSQL
>> >> introduces? Check my test of PostgreSQL 9.2 vs 9.3 on FreeBSD 10.0 at
>> [1].
>> >> The difference between PG92 and PG93 is not huge, ~17%. The difference
>> >> between FreeBSD and the other OS:es in this thread's original post's
>> >> performance chart seems to be about a lot more?
>> >>
>> >> Palle
>> >>
>> >> [1]
>> >>
>> http://www.postgresql.org/message-id/2AE143D2-87D3-4AD1-AC78-CE2258230C05@...
>> >> _______________________________________________
>> >> [hidden email] mailing list
>> >> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>> >> To unsubscribe, send any mail to "
>> [hidden email]"
>> _______________________________________________
>> [hidden email] mailing list
>> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>> To unsubscribe, send any mail to "
>> [hidden email]"
>>
>
>
_______________________________________________
[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: FreeBSD 10 and PostgreSQL 9.3 scalability issues

Palle Girgensohn-2
I did some tests with zfs, and results where appallingly bad, but that was with db size > ram.

I think the model used by PostgreSQL, as most databases, are very disk block centric. Using zfs makes it hard to get good performance. But this was some time ago, maybe things have improved.

Palle

> 21 maj 2014 kl. 18:33 skrev Gezeala M. Bacuño II <[hidden email]>:
>
> Gotcha.
>
> I've been testing using pgbench on FreeBSD 9.0 release + ZFS + pg 9.3.. I
> can reach the freebsd 10 stats on the pdf files if the dataset <RAM. It
> gets way lower if the dataset >RAM. Test was done on pools without L2ARC
> and with/without compression. I also remember increasing a vm.pmap sysctl.
> I'm out of the office right now sick so I can't provide the stats but yes,
> with mmap it is pretty bad..
>
> Keep us in the loop. I'd like to help on getting the performance data they
> need.
>> On May 20, 2014 11:16 PM, "Palle Girgensohn" <[hidden email]> wrote:
>>
>> I got no response about how to grab performance data.
>>
>> The PostgreSQL team is also making an effort by setting up machines
>> dedicated to performance measuring and tuning.
>>
>> And freebsd guys and PostgreSQL guys are apparently meeting at pgcon this
>> week.
>>
>> We'll see where that leads.
>>
>> In the mean time, if I for some pointers on how to grab performance data,
>> I could do some more tests.
>>
>> Palle
>>
>> 21 maj 2014 kl. 02:13 skrev Gezeala M. Bacuño II <[hidden email]>:
>>
>>
>> Do you guys have any updates on this?
>>
>> --
>>
>> regards
>>
>> gezeala bacuño II
>>
>>
>> On Tue, Apr 22, 2014 at 11:52 PM, Palle Girgensohn <[hidden email]>wrote:
>>
>>>
>>>
>>>> 23 apr 2014 kl. 01:04 skrev Adrian Chadd <[hidden email]>:
>>>>
>>>> Hi,
>>>>
>>>> Are you able to repeat these tests (for both 9.2 and 9.3) whilst
>>>> grabbing some performance data from lock profiling and hwpmc?
>>>
>>> I sure can, but I'd love some pointers as to how this is done. Please? :-)
>>>
>>>>
>>>> The benchmarking is great but it doesn't tell us enough information as
>>>> to "why" things behave poorly compared to Linux and why the mmap drop
>>>> isn't so great.
>>>
>>>
>>> As per the discussion on postresql-hackers, the regression between pg9.2
>>> and pg9.3, which includes the sysv->mmap shift, *might* also exist, at
>>> least partly, on Linux as well.
>>>
>>> The initial post in *this* thread does however indicate that freebsd
>>> performs poorer than Linux and dragonflybsd, but does not really compare
>>> PostgreSQL versions.
>>>
>>> Just so we're not pursuing the wrong problem here, let's be open minded
>>> about the definition of the problem. :-)
>>>
>>>>
>>>> What about with more clients? 64? 128? 256?
>>>
>>> My test went to 80. I can go higher as well, though other sources say 50
>>> is a reasonable limit for PostgreSQL.
>>>
>>> Palle
>>>
>>>
>>>>
>>>>
>>>> Thanks!
>>>>
>>>>
>>>>
>>>> -a
>>>>
>>>>
>>>>> On 21 April 2014 14:11, Palle Girgensohn <[hidden email]> wrote:
>>>>>
>>>>>
>>>>>> Den torsdagen den 20:e mars 2014 kl. 00:33:10 UTC+1 skrev Sean
>>> Chittenden:
>>>>>>
>>>>>>> As far as I know, the test was done on both UFS2 and ZFS and the
>>>>>>> difference was marginal.
>>>>>>
>>>>>> As Adrian pointed out, there is an mmap(2) mutex in the way. Starting
>>> in
>>>>>> PostgreSQL 9.3, shared buffers are allocated out of mmap(2) instead
>>> of shm.
>>>>>> shm is only used to notify the PostgreSQL postmaster that a child
>>> process
>>>>>> exited/crashed (when a pid detaches from a shm segment, there is a
>>> kernel
>>>>>> event, but there is no kernel event when detaching from an mmap(2)
>>> region).
>>>>>> -sc
>>>>>>
>>>>>> http://www.postgresql.org/docs/9.3/static/release-9-3.html#AEN115039
>>>>>>
>>>>>>
>>>>>>>>> Just want to share these pgbench results done by DragonFlyBSD, and
>>>>>> would
>>>>>>>>> like some input on why these numbers look so bad and what can be
>>> done
>>>>>> to
>>>>>>>>> improve (ie. kernel tunables etc) the performance.
>>> http://lists.dragonflybsd.org/pipermail/users/attachments/20140310/4250b961/attachment-0001.pdf
>>>>>>>>
>>>>>>>>
>>>>>>>> Do you have the ability to test with FreeBSD 8.x and 9.x to see if
>>> this
>>>>>> is
>>>>>>>> regression?
>>>>>>>>
>>>>>>>> Also you don't mention the FS used in each case, so I'm wondering if
>>>>>> you
>>>>>>>> used a ZFS install of FreeBSD which could help to explain things.
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Sean Chittenden
>>>>>> [hidden email] <javascript:>
>>>>> Hi,
>>>>>
>>>>> There is a fresh thread about this in postgresql-hackers [1].
>>>>>
>>>>> There are two parallel approaches suggested there, where one is to
>>> have an
>>>>> option to continue using the old SYSV shared memory in PostgreSQL, and
>>> the
>>>>> other is the suggestion that "somebody needs to hold the FreeBSD folks'
>>>>> feet to the fire about when we can expect to see a fix from their
>>> side."
>>>>>
>>>>> Looking at the original post in this thread, it seems to me that
>>> FreeBSD
>>>>> has scalability problems beyond what the SYSV vs mmap change in
>>> PostgreSQL
>>>>> introduces? Check my test of PostgreSQL 9.2 vs 9.3 on FreeBSD 10.0 at
>>> [1].
>>>>> The difference between PG92 and PG93 is not huge, ~17%. The difference
>>>>> between FreeBSD and the other OS:es in this thread's original post's
>>>>> performance chart seems to be about a lot more?
>>>>>
>>>>> Palle
>>>>>
>>>>> [1]
>>> http://www.postgresql.org/message-id/2AE143D2-87D3-4AD1-AC78-CE2258230C05@...
>>>>> _______________________________________________
>>>>> [hidden email] mailing list
>>>>> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>>>>> To unsubscribe, send any mail to "
>>> [hidden email]"
>>> _______________________________________________
>>> [hidden email] mailing list
>>> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>>> To unsubscribe, send any mail to "
>>> [hidden email]"
> _______________________________________________
> [hidden email] mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
> To unsubscribe, send any mail to "[hidden email]"
_______________________________________________
[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: FreeBSD 10 and PostgreSQL 9.3 scalability issues

Sean Chittenden-4
> I did some tests with zfs, and results where appallingly bad, but that was with db size > ram.
>
> I think the model used by PostgreSQL, as most databases, are very disk block centric. Using zfs makes it hard to get good performance. But this was some time ago, maybe things have improved.

I have some hardware that I ran with last week wherein I was *not* able to reproduce any performance difference between ZFS and UFS2. On both UFS2 and ZFS I was seeing the same performance when using a a RAID10 / set of mirrors. I talked with the Dragonfly folk who originally performed these tests and they also saw the same thing: no real performance difference between ZFS and UFS. I ran my tests on a host with 16 drive, 10K SAS, 192GB RAM. I also created a kernel profiling image and ran the 20 concurrent user test under kgprof(1), dtrace, and pmcstat and have the results available:

http://people.freebsd.org/~seanc/pg9.3-fbsd10-profiling/

There are some investigations that are ongoing as a result of these findings. The dfly methodology was observed when generating these results. Stay tuned. -sc


--
Sean Chittenden
[hidden email]


>
> Palle
>
>> 21 maj 2014 kl. 18:33 skrev Gezeala M. Bacuño II <[hidden email]>:
>>
>> Gotcha.
>>
>> I've been testing using pgbench on FreeBSD 9.0 release + ZFS + pg 9.3.. I
>> can reach the freebsd 10 stats on the pdf files if the dataset <RAM. It
>> gets way lower if the dataset >RAM. Test was done on pools without L2ARC
>> and with/without compression. I also remember increasing a vm.pmap sysctl.
>> I'm out of the office right now sick so I can't provide the stats but yes,
>> with mmap it is pretty bad..
>>
>> Keep us in the loop. I'd like to help on getting the performance data they
>> need.
>>> On May 20, 2014 11:16 PM, "Palle Girgensohn" <[hidden email]> wrote:
>>>
>>> I got no response about how to grab performance data.
>>>
>>> The PostgreSQL team is also making an effort by setting up machines
>>> dedicated to performance measuring and tuning.
>>>
>>> And freebsd guys and PostgreSQL guys are apparently meeting at pgcon this
>>> week.
>>>
>>> We'll see where that leads.
>>>
>>> In the mean time, if I for some pointers on how to grab performance data,
>>> I could do some more tests.
>>>
>>> Palle
>>>
>>> 21 maj 2014 kl. 02:13 skrev Gezeala M. Bacuño II <[hidden email]>:
>>>
>>>
>>> Do you guys have any updates on this?
>>>
>>> --
>>>
>>> regards
>>>
>>> gezeala bacuño II
>>>
>>>
>>> On Tue, Apr 22, 2014 at 11:52 PM, Palle Girgensohn <[hidden email]>wrote:
>>>
>>>>
>>>>
>>>>> 23 apr 2014 kl. 01:04 skrev Adrian Chadd <[hidden email]>:
>>>>>
>>>>> Hi,
>>>>>
>>>>> Are you able to repeat these tests (for both 9.2 and 9.3) whilst
>>>>> grabbing some performance data from lock profiling and hwpmc?
>>>>
>>>> I sure can, but I'd love some pointers as to how this is done. Please? :-)
>>>>
>>>>>
>>>>> The benchmarking is great but it doesn't tell us enough information as
>>>>> to "why" things behave poorly compared to Linux and why the mmap drop
>>>>> isn't so great.
>>>>
>>>>
>>>> As per the discussion on postresql-hackers, the regression between pg9.2
>>>> and pg9.3, which includes the sysv->mmap shift, *might* also exist, at
>>>> least partly, on Linux as well.
>>>>
>>>> The initial post in *this* thread does however indicate that freebsd
>>>> performs poorer than Linux and dragonflybsd, but does not really compare
>>>> PostgreSQL versions.
>>>>
>>>> Just so we're not pursuing the wrong problem here, let's be open minded
>>>> about the definition of the problem. :-)
>>>>
>>>>>
>>>>> What about with more clients? 64? 128? 256?
>>>>
>>>> My test went to 80. I can go higher as well, though other sources say 50
>>>> is a reasonable limit for PostgreSQL.
>>>>
>>>> Palle
>>>>
>>>>
>>>>>
>>>>>
>>>>> Thanks!
>>>>>
>>>>>
>>>>>
>>>>> -a
>>>>>
>>>>>
>>>>>> On 21 April 2014 14:11, Palle Girgensohn <[hidden email]> wrote:
>>>>>>
>>>>>>
>>>>>>> Den torsdagen den 20:e mars 2014 kl. 00:33:10 UTC+1 skrev Sean
>>>> Chittenden:
>>>>>>>
>>>>>>>> As far as I know, the test was done on both UFS2 and ZFS and the
>>>>>>>> difference was marginal.
>>>>>>>
>>>>>>> As Adrian pointed out, there is an mmap(2) mutex in the way. Starting
>>>> in
>>>>>>> PostgreSQL 9.3, shared buffers are allocated out of mmap(2) instead
>>>> of shm.
>>>>>>> shm is only used to notify the PostgreSQL postmaster that a child
>>>> process
>>>>>>> exited/crashed (when a pid detaches from a shm segment, there is a
>>>> kernel
>>>>>>> event, but there is no kernel event when detaching from an mmap(2)
>>>> region).
>>>>>>> -sc
>>>>>>>
>>>>>>> http://www.postgresql.org/docs/9.3/static/release-9-3.html#AEN115039
>>>>>>>
>>>>>>>
>>>>>>>>>> Just want to share these pgbench results done by DragonFlyBSD, and
>>>>>>> would
>>>>>>>>>> like some input on why these numbers look so bad and what can be
>>>> done
>>>>>>> to
>>>>>>>>>> improve (ie. kernel tunables etc) the performance.
>>>> http://lists.dragonflybsd.org/pipermail/users/attachments/20140310/4250b961/attachment-0001.pdf
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Do you have the ability to test with FreeBSD 8.x and 9.x to see if
>>>> this
>>>>>>> is
>>>>>>>>> regression?
>>>>>>>>>
>>>>>>>>> Also you don't mention the FS used in each case, so I'm wondering if
>>>>>>> you
>>>>>>>>> used a ZFS install of FreeBSD which could help to explain things.
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Sean Chittenden
>>>>>>> [hidden email] <javascript:>
>>>>>> Hi,
>>>>>>
>>>>>> There is a fresh thread about this in postgresql-hackers [1].
>>>>>>
>>>>>> There are two parallel approaches suggested there, where one is to
>>>> have an
>>>>>> option to continue using the old SYSV shared memory in PostgreSQL, and
>>>> the
>>>>>> other is the suggestion that "somebody needs to hold the FreeBSD folks'
>>>>>> feet to the fire about when we can expect to see a fix from their
>>>> side."
>>>>>>
>>>>>> Looking at the original post in this thread, it seems to me that
>>>> FreeBSD
>>>>>> has scalability problems beyond what the SYSV vs mmap change in
>>>> PostgreSQL
>>>>>> introduces? Check my test of PostgreSQL 9.2 vs 9.3 on FreeBSD 10.0 at
>>>> [1].
>>>>>> The difference between PG92 and PG93 is not huge, ~17%. The difference
>>>>>> between FreeBSD and the other OS:es in this thread's original post's
>>>>>> performance chart seems to be about a lot more?
>>>>>>
>>>>>> Palle
>>>>>>
>>>>>> [1]
>>>> http://www.postgresql.org/message-id/2AE143D2-87D3-4AD1-AC78-CE2258230C05@...
>>>>>> _______________________________________________
>>>>>> [hidden email] mailing list
>>>>>> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>>>>>> To unsubscribe, send any mail to "
>>>> [hidden email]"
>>>> _______________________________________________
>>>> [hidden email] mailing list
>>>> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>>>> To unsubscribe, send any mail to "
>>>> [hidden email]"
>> _______________________________________________
>> [hidden email] mailing list
>> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>> To unsubscribe, send any mail to "[hidden email]"
> _______________________________________________
> [hidden email] mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
> To unsubscribe, send any mail to "[hidden email]"

_______________________________________________
[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: FreeBSD 10 and PostgreSQL 9.3 scalability issues

Matthew Seaman-5
On 21/05/2014 18:17, Sean Chittenden wrote:
>> I did some tests with zfs, and results where appallingly bad, but that was with db size > ram.
>> >
>> > I think the model used by PostgreSQL, as most databases, are very disk block centric. Using zfs makes it hard to get good performance. But this was some time ago, maybe things have improved.
> I have some hardware that I ran with last week wherein I was *not* able to reproduce any performance difference between ZFS and UFS2. On both UFS2 and ZFS I was seeing the same performance when using a a RAID10 / set of mirrors. I talked with the Dragonfly folk who originally performed these tests and they also saw the same thing: no real performance difference between ZFS and UFS. I ran my tests on a host with 16 drive, 10K SAS, 192GB RAM. I also created a kernel profiling image and ran the 20 concurrent user test under kgprof(1), dtrace, and pmcstat and have the results available:
>
> http://people.freebsd.org/~seanc/pg9.3-fbsd10-profiling/
>
> There are some investigations that are ongoing as a result of these findings. The dfly methodology was observed when generating these results. Stay tuned. -sc
>

I'm not sure that the ZFS vs UFS2 question is at the core of the
performance problem.  We're definitely seeing marked slowdowns between
Pg 9.2 and 9.3 on UFS2 (RAID10 + Dell H710p (mfi) raid controller with
1GB NVRAM)

In our case the DB size is significantly bigger than RAM, and we also
run with a large (3GB) work mem, which seems to exacerbate the slow-down
effect.

        Cheers,

        Matthew

--
Dr Matthew J Seaman MA, D.Phil.
PGP: http://www.infracaninophile.co.uk/pgpkey



signature.asc (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: FreeBSD 10 and PostgreSQL 9.3 scalability issues

Sean Chittenden-4
>>> I did some tests with zfs, and results where appallingly bad, but that was with db size > ram.
>>>>
>>>> I think the model used by PostgreSQL, as most databases, are very disk block centric. Using zfs makes it hard to get good performance. But this was some time ago, maybe things have improved.
>> I have some hardware that I ran with last week wherein I was *not* able to reproduce any performance difference between ZFS and UFS2. On both UFS2 and ZFS I was seeing the same performance when using a a RAID10 / set of mirrors. I talked with the Dragonfly folk who originally performed these tests and they also saw the same thing: no real performance difference between ZFS and UFS. I ran my tests on a host with 16 drive, 10K SAS, 192GB RAM. I also created a kernel profiling image and ran the 20 concurrent user test under kgprof(1), dtrace, and pmcstat and have the results available:
>>
>> http://people.freebsd.org/~seanc/pg9.3-fbsd10-profiling/
>>
>> There are some investigations that are ongoing as a result of these findings. The dfly methodology was observed when generating these results. Stay tuned. -sc
>>
>
> I'm not sure that the ZFS vs UFS2 question is at the core of the
> performance problem.  We're definitely seeing marked slowdowns between
> Pg 9.2 and 9.3 on UFS2 (RAID10 + Dell H710p (mfi) raid controller with
> 1GB NVRAM)

When the working set fits in RAM (OS + PG), there isn't a performance difference between 9.2 and 9.3.

This is a good data point. I will try and reproduce this workload and will run the performance profiling again to see if something else pops up in the profiling. -sc

--
Sean Chittenden
[hidden email]

_______________________________________________
[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: FreeBSD 10 and PostgreSQL 9.3 scalability issues

Palle Girgensohn-2


21 maj 2014 kl. 22:05 skrev Sean Chittenden <[hidden email]>:

>>>> I did some tests with zfs, and results where appallingly bad, but that was with db size > ram.
>>>>>
>>>>> I think the model used by PostgreSQL, as most databases, are very disk block centric. Using zfs makes it hard to get good performance. But this was some time ago, maybe things have improved.
>>> I have some hardware that I ran with last week wherein I was *not* able to reproduce any performance difference between ZFS and UFS2. On both UFS2 and ZFS I was seeing the same performance when using a a RAID10 / set of mirrors. I talked with the Dragonfly folk who originally performed these tests and they also saw the same thing: no real performance difference between ZFS and UFS. I ran my tests on a host with 16 drive, 10K SAS, 192GB RAM. I also created a kernel profiling image and ran the 20 concurrent user test under kgprof(1), dtrace, and pmcstat and have the results available:
>>>
>>> http://people.freebsd.org/~seanc/pg9.3-fbsd10-profiling/
>>>
>>> There are some investigations that are ongoing as a result of these findings. The dfly methodology was observed when generating these results. Stay tuned. -sc
>>
>> I'm not sure that the ZFS vs UFS2 question is at the core of the
>> performance problem.  We're definitely seeing marked slowdowns between
>> Pg 9.2 and 9.3 on UFS2 (RAID10 + Dell H710p (mfi) raid controller with
>> 1GB NVRAM)


I never meant that it was the core of our problem. Zfs vs ufs is out of scope here.


>
> When the working set fits in RAM (OS + PG), there isn't a performance difference between 9.2 and 9.3.
>
> This is a good data point. I will try and reproduce this workload and will run the performance profiling again to see if something else pops up in the profiling. -sc
>

I don't agree. My original measurements showed a 20% slowdown for a reasonably small ( <RAM) pgbench database.

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