Re: PostgreSQL performance on FreeBSD

On Sat, Jun 28, 2014 at 01:37:20PM +0200, Palle Girgensohn wrote:
> 
> 
> > 28 jun 2014 kl. 12:21 skrev Konstantin Belousov <kostikbel_at_gmail.com>:
> > 
> >> On Sat, Jun 28, 2014 at 12:08:39PM +0200, Palle Girgensohn wrote:
> >> 
> >> 
> >>> 27 jun 2014 kl. 18:34 skrev Konstantin Belousov <kostikbel_at_gmail.com>:
> >>> 
> >>>> On Fri, Jun 27, 2014 at 10:57:53AM -0400, John Baldwin wrote:
> >>>>> On Friday, June 27, 2014 8:56:13 am Konstantin Belousov wrote:
> >>>>> Hi,
> >>>>> I did some measurements and hacks to see about the performance and
> >>>>> scalability of PostgreSQL 9.3 on FreeBSD, sponsored by The FreeBSD
> >>>>> Foundation.
> >>>>> 
> >>>>> The results are described in https://kib.kiev.ua/kib/pgsql_perf.pdf.
> >>>>> The uncommitted patches, referenced in the article, are available as
> >>>>> https://kib.kiev.ua/kib/pig1.patch.txt
> >>>>> https://kib.kiev.ua/kib/patch-2
> >>>> 
> >>>> Did you run the same benchmark on the same hardware with any other OS's to 
> >>>> compare results?
> >>> 
> >>> No.
> >>> 
> >>> FWIW, before the failing after the 30 clients is corrected, I do not
> >>> think it is much interesting to do such comparision.
> >> 
> >> This is great work!
> >> 
> >> Does anybody know how far back in FreeBSD versions using posix semaphore instead of sysv would make a difference?  It seems we need a rather current version? 8.x did not support it at all, at some point at lest, and in 9 it was buggy. I could add he patch-2 to the port, but I reckon it needs a conditional based on FreeBSD version?
> > I recommend to add it as an option.  The currently supported versions
> > of stable/9 and higher have new posix semaphores implementation.
> > The stable/8 also has posix semaphores, but there it is kernel-based
> > interface, I do not plan to evaluate it in any way.
> 
> According to one source, posix semaphores uses O(N^2) file descriptors, where N is the number of connections. Do you know if this is true? (I'll try it, naturally, just checking). 
> 
(New) posix semaphores implementation, done by David Xu, opens a file
descriptor during the sem_open(3), which is used to mmap the area carrying
the lock word, and is immediately closed afterward in sem_open().  In
other words, if you have N semaphores and M processes, there would
be N*M open(2)/close(2) pairs, and N files, each mmaped to M processes.
New implementation does not use file descriptor during semaphore use,
and does not keep the backing file open.

> > 
> > 
> >> The clang bug should go upstreams, right?
> > I believe there is already some activity about it.  I do not follow
> > clang development.
> 
> Sounds good enough. 
> 
> > 
> >> 
> >> I have seen similar curves, presented by Greg Smith (PostgreSQL
> >> hacker) where he concluded that there is no point in running more
> >> than 50 concurrent connections. This was for Linux. In your measures,
> >> the knee is at 30. That's said, FreeBSD could and should do better,
> >> but probably there is a limit where there will be a knee in the graph
> >> and performance will drop. It should be more than 30, though, as you
> >> rightly commented.
> >> 
> >> Do you any ideas to pursue this further apart from complicated
> >> rewrites like DragonFly?
> > I do.
> > 
> > The scope of the current work was done to obtain understanding where do we stay
> > and, if possible, evaluate ideas, possibly in the hackish way. I hope
> > and almost sure that this will be continued, but cannot provide any time
> > estimation.
> 
> Great. If you need help testing, I might be able to help. 
I have the test set up and the graphing mostly automated, although
the repeat of the configuration would be quite laborous.

On the other hand, if you get access to zoo, replication could be easier.