Fine-grained locking for POSIX local sockets (UNIX domain sockets)

[Robert Watson]

On Sun, 7 May 2006, Mike Jakubik wrote:

> The difference in performance is just ridiculous. Is mysql written to be 
> slow on freebsd or is there a problem with freebsd?

In past discussion, I think a reasonable conclusion has been some amount of 
both.  We've identified a few particular areas where there are differences 
deserving attention:

- MySQL has increasingly made use of time query calls, especially in more
   recent versions.  On FreeBSD, we provide very precise time at somewhat
   higher cost than Linux.  Presumably the design choice has been made in
   MySQL that since time is free [on Linux], it should be checked lots, which
   tends to hurt MySQL performance more on non-Linux platforms.  Some
   experimental work has been done to offer reduced time quality via
   alternative APIs, but we haven't settled on a particular approach.
   Currently APIs don't allow you to say "I don't want to pay a lot for my
   muf... timestamp.".  I have a wrapper libc library somewhere that causes
   applications to query lower quality time stamps using faster interfaces
   which has been shown to have a significant impact on MySQL performance.

- MySQL seems to really like Linux threading and scheduling.  For example, in
   the past it has been observed that you get significantly better MySQL
   performance on FreeBSD when you use the Linux threading implementation.
   libthr closes some of that gap, but last I checked wasn't there yet.  I'm
   not really familiar with the issues here so can't speak to them in detail.

- Locking granularity has been an issue on SMP for MySQL on FreeBSD.  The
   patch I posted is part of working to address this.  We have been seeing very
   high contention on IPC locks in particular.  With my change, Kris reports
   much lower IPC lock contention -- instead we start hitting process-related
   lock bottlenecks.

There's probably a lot more to this -- in the past there has also been 
speculation (although not, as far as I know, a lot of substantiation) that 
FreeBSD is running into some file system meta-data update bottlenecks, and 
that our sync interfaces for the file system are either more complete, less 
granular, or work too hard.  Again, outside of my areas of expertise.

Part of the problem is probably an application optimization problem to do with 
optimizing MySQL to use features that are more cheap on Linux than FreeBSD, 
rather than vice versa -- there are many other applications that perform 
measurably better on FreeBSD than Linux, especially when hitting the VM, which 
suggests that while we probably do have problems we need to fix, something 
disportionate may have happened for MySQL performance.  We do have significant 
overheads in our communications code that we've been working on actively, and 
differences in threading that need to be explored more.

Robert N M Watson