Skip site navigation (1)Skip section navigation (2)
Date:      Wed, 21 May 2014 18:50:50 +0200
From:      Palle Girgensohn <girgen@pingpong.net>
To:        =?utf-8?Q?"Gezeala_M._Bacu=C3=B1o_II"?= <gezeala@gmail.com>
Cc:        Adrian Chadd <adrian@freebsd.org>, "bsdmailinglist@googlegroups.com" <bsdmailinglist@googlegroups.com>, Petr Janda <janda.petr@gmail.com>, Steven Hartland <killing@multiplay.co.uk>, FreeBSD Mailing Lists <freebsd-performance@freebsd.org>, Sean Chittenden <sean@chittenden.org>
Subject:   Re: FreeBSD 10 and PostgreSQL 9.3 scalability issues
Message-ID:  <1473AF7C-B190-4CD4-B611-BA4090A081CB@pingpong.net>
In-Reply-To: <CAJKO3mX5KA8HZ5tQGTyOgfKbS6HvUvYH-gvzeewTkh3nWz=NRg@mail.gmail.com>
References:  <5327B9B7.3050103@gmail.com> <2610F490C952470C9D15999550F67068@multiplay.co.uk> <532A192A.1070509@gmail.com> <assp.0155c70d29.23ED6415-945D-4DF5-90DD-2F2CD7E198AF@chittenden.org> <f4ead73a-fae2-4eac-8499-3cf630eb3d31@googlegroups.com> <CAJ-VmomVOWFb7X5s-amRX7QFzbmT6Kt6bB9gaPVv2_hGx1OS5g@mail.gmail.com> <572540F9-13E4-4BA9-88AE-5F47FB19450A@pingpong.net> <CAJKO3mUTwgiQenSLYfOxHrZxuPQ9kvUPC44MrbLjvpLE=toZQA@mail.gmail.com> <1BC3D447-2044-4AB8-B183-B83957BC9112@pingpong.net> <CAJKO3mX5KA8HZ5tQGTyOgfKbS6HvUvYH-gvzeewTkh3nWz=NRg@mail.gmail.com>

next in thread | previous in thread | raw e-mail | index | archive | help
I did some tests with zfs, and results where appallingly bad, but that was w=
ith db size > ram.=20

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.=20

Palle

> 21 maj 2014 kl. 18:33 skrev Gezeala M. Bacu=C3=B1o II <gezeala@gmail.com>:=

>=20
> Gotcha.
>=20
> 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..
>=20
> 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" <girgen@pingpong.net> wrote:=

>>=20
>> I got no response about how to grab performance data.
>>=20
>> The PostgreSQL team is also making an effort by setting up machines
>> dedicated to performance measuring and tuning.
>>=20
>> And freebsd guys and PostgreSQL guys are apparently meeting at pgcon this=

>> week.
>>=20
>> We'll see where that leads.
>>=20
>> In the mean time, if I for some pointers on how to grab performance data,=

>> I could do some more tests.
>>=20
>> Palle
>>=20
>> 21 maj 2014 kl. 02:13 skrev Gezeala M. Bacu=C3=B1o II <gezeala@gmail.com>=
:
>>=20
>>=20
>> Do you guys have any updates on this?
>>=20
>> --
>>=20
>> regards
>>=20
>> gezeala bacu=C3=B1o II
>>=20
>>=20
>> On Tue, Apr 22, 2014 at 11:52 PM, Palle Girgensohn <girgen@pingpong.net>w=
rote:
>>=20
>>>=20
>>>=20
>>>> 23 apr 2014 kl. 01:04 skrev Adrian Chadd <adrian@freebsd.org>:
>>>>=20
>>>> Hi,
>>>>=20
>>>> Are you able to repeat these tests (for both 9.2 and 9.3) whilst
>>>> grabbing some performance data from lock profiling and hwpmc?
>>>=20
>>> I sure can, but I'd love some pointers as to how this is done. Please? :=
-)
>>>=20
>>>>=20
>>>> 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.
>>>=20
>>>=20
>>> 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.
>>>=20
>>> The initial post in *this* thread does however indicate that freebsd
>>> performs poorer than Linux and dragonflybsd, but does not really compare=

>>> PostgreSQL versions.
>>>=20
>>> Just so we're not pursuing the wrong problem here, let's be open minded
>>> about the definition of the problem. :-)
>>>=20
>>>>=20
>>>> What about with more clients? 64? 128? 256?
>>>=20
>>> My test went to 80. I can go higher as well, though other sources say 50=

>>> is a reasonable limit for PostgreSQL.
>>>=20
>>> Palle
>>>=20
>>>=20
>>>>=20
>>>>=20
>>>> Thanks!
>>>>=20
>>>>=20
>>>>=20
>>>> -a
>>>>=20
>>>>=20
>>>>> On 21 April 2014 14:11, Palle Girgensohn <girgen@pingpong.net> wrote:
>>>>>=20
>>>>>=20
>>>>>> Den torsdagen den 20:e mars 2014 kl. 00:33:10 UTC+1 skrev Sean
>>> Chittenden:
>>>>>>=20
>>>>>>> As far as I know, the test was done on both UFS2 and ZFS and the
>>>>>>> difference was marginal.
>>>>>>=20
>>>>>> 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
>>>>>>=20
>>>>>> http://www.postgresql.org/docs/9.3/static/release-9-3.html#AEN115039
>>>>>>=20
>>>>>>=20
>>>>>>>>> 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/4250b=
961/attachment-0001.pdf
>>>>>>>>=20
>>>>>>>>=20
>>>>>>>> Do you have the ability to test with FreeBSD 8.x and 9.x to see if
>>> this
>>>>>> is
>>>>>>>> regression?
>>>>>>>>=20
>>>>>>>> Also you don't mention the FS used in each case, so I'm wondering i=
f
>>>>>> you
>>>>>>>> used a ZFS install of FreeBSD which could help to explain things.
>>>>>>=20
>>>>>>=20
>>>>>> --
>>>>>> Sean Chittenden
>>>>>> se...@chittenden.org <javascript:>
>>>>> Hi,
>>>>>=20
>>>>> There is a fresh thread about this in postgresql-hackers [1].
>>>>>=20
>>>>> 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."
>>>>>=20
>>>>> 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?
>>>>>=20
>>>>> Palle
>>>>>=20
>>>>> [1]
>>> http://www.postgresql.org/message-id/2AE143D2-87D3-4AD1-AC78-CE2258230C0=
5@FreeBSD.org
>>>>> _______________________________________________
>>>>> freebsd-performance@freebsd.org mailing list
>>>>> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>>>>> To unsubscribe, send any mail to "
>>> freebsd-performance-unsubscribe@freebsd.org"
>>> _______________________________________________
>>> freebsd-performance@freebsd.org mailing list
>>> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
>>> To unsubscribe, send any mail to "
>>> freebsd-performance-unsubscribe@freebsd.org"
> _______________________________________________
> freebsd-performance@freebsd.org mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-performance
> To unsubscribe, send any mail to "freebsd-performance-unsubscribe@freebsd.=
org"



Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?1473AF7C-B190-4CD4-B611-BA4090A081CB>