MariaDB developers:
This is a simple change that got up to a 17% speedup with sysbench on my 4 node system.
How do I find out if someone will pick this up to run with it?

Thanks,
Joe Mario

Hello Joe,
I am just a passer-by, but I suspect that some subscribers to the maria-developers mailing list:
https://lists.launchpad.net/maria-developers
would be interested in the change. Maybe try posting there "Review MDEV-5081 my_pthread_fastmutex_lock patch, sysbench oltp gains for many threads on NUMA".
Thanks and good luck.

I was wondering what was being used to analyze sysbench, and I found these resources helpful:
http://developerblog.redhat.com/2013/08/27/numa-hurt-app-perf/
http://developerblog.redhat.com/2013/05/31/dive-deeper-in-numa-systems/
Thank you Joe (and Don).

Joe, thanks for your contribution! I reviewed your analysis and your patch and came up with one small extension: I believe there is no much sense in randomizing timeout value, so I totally removed rng_state.

Spin locks on NUMA might be not that good idea. I'd like to do some extra benchmarking with fast mutexes disabled.

Hi Peter and Sergey:
Glad to be of help.

Sergey:
Do you want me to try your patch on sysbench on our 4-node server? Or did you already try it?

Joe

Hi Joe,

we will try it next week. If you have resources and want to test my patch - feel free to do so. Your feedback will be valuable.

Hi Sergey:
I did a build with your patch and compared it with my patch. This was on our 4-node server, with the database located on an SSD drive. I used the same sysbench command that I posted in my opening post.

Here's what I found:
12 Threads: Avg: 3.7% speedup over 3 runs
20 Threads: Avg: 15.5% slowdown over 3 runs
30 Threads: Avg: 15.5% slowdown over 2 runs
40 Threads: Avg: 10.6% slowdown over 2 runs
50 Threads: Avg: 4.3% slowdown over 1 run
60 Threads: Avg: 1.2% slowdown over 1 run

So something about that random backoff does help at the higher threads counts.
However, here's some more info. I see your deleted the "now unused" rng_state field from the struct. In the many iterations I did leading up to my patch, I noticed when I tried to change the size of the struct (either by adding padding or moving things around), I too got a speedup at the lower thread counts and slowdowns at the higher thread counts.

I'm not a regular database user, and I don't know if 12 threads is the sweet spot you're aiming to speedup. I suspect scailability at higher thread counts is important to you - but please confirm.

If you want, I can try again putting that hot mutex in its own aligned cacheline and pad it out so that nothing else conflicts with it. I can try it both with and without the spins field in it. I had mixed results earlier, but never with the rng_state deleted.
Joe

Hi Joe,

we're aiming to provide sane performance in all cases. According to the numbers your patch wins.

On my 64bit host structure sizes are not affected by removal of rng_state (due to 8byte alignment I guess):
sizeof(mysql_mutex_t)= 56, sizeof(my_pthread_fastmutex_t)= 48, sizeof(pthread_mutex_t)= 40

Did structure size change on your 4-node server?

Previously maximum possible increment for maxdelay was MY_PTHREAD_FASTMUTEX_DELAY (which is 4). I used this maximum value. Probably we should use lower number and increment maxdelay by 1 instead?

Also I had a look at pthread_mutex_lock() source and found out that it spins if mutex type is PTHREAD_MUTEX_ADAPTIVE_NP (which is the case with fast mutex). In other words we have double spinning.

Hi Sergey:
Let me redo my testing that I did yesterday. I want to double check my steps as I may have made a mistake. Give me a day or two to post back with any updated results.
Joe

Hi Sergey:
It's going to be a while before I get back to this. I did a couple of runs - enough to know that your change is within the noise level. But I didn't test it as thoroughly as I usually do, due to being pulled away with other interrupts. So go ahead and use your enhanced version.

In my last post, when I said I made a mistake, I realized I grabbed the last rpmbuild in the shell history, and it wasn't using the spec file I thought it used. Sorry about the confusion.

And I agree with you about the struct size after rng_state is deleted.

If I get a chance to get back and do more testing with this, I will.
Joe

Hi Joe,

thanks a lot for testing it. We did some tests on our side (results attached), but they don't look as good as yours. I'm currently working towards to understand the difference. Could you share you test system details and how did you build MariaDB?

Thanks,
Sergey

Hi Sergey:
See my reply in the attached file (which seems to preserve cleaner formatting). Let me know if I didn't answer your questions.

Joe

Hi Sergey:
I did another rerun over the weekend.

I first ran my test script (the one I previously posted) three times against the original v5.5.31 unmodified MariaDB. Then I ran it another three times against the version with my proposed changes.

While I wasn't able to reproduce my original speedup numbers, there is a positive effect as the thread count and contention increases. See the attached file for the results.

I also did two other runs (which I didn't attach).
First I compared your suggested changes against the changes I made. The difference was in the noise.

Second, I added padding to the fast_mutex struct, to make sure nothing else was adding additional contention to the mutex cacheline:
typedef struct st_my_pthread_fastmutex_t

my_pthread_fastmutex_t;

The results were inconclusive. It hurt 0 to 2% at 12 threads, but helped up to 10% at 20 threads, and then it was noisy at higher thread counts.
I mention these results just as an fyi.

Hopefully my input in this whole thread will help speedup MariaDB in some way.
Joe

Hi Joe,

your input is definitely valuable. We didn't do much wrt scalability on NUMA yet, but there seem to be some low-laying fruits hanging around.
I plan to do some additional tests on our side this week and will hopefully try perf tool that you were suggesting.

Thanks,
Sergey

Last week I did some tests on 4 CPU (64 cores) Sandy Bridge host. Unfortunately I wasn't able to reproduce performance improvement. Probably it is due to hardware difference.

I was able to reproduce reported problem. Fast mutexes shown worst throughput compared to other mutex types. Benchmark results are available here:
http://svoj-db.blogspot.ru/2014/02/mariadb-mutexes-scalability.html

Looks like this problem was already raised a few times in MySQL circles:
http://bugs.mysql.com/bug.php?id=58766
http://bugs.mysql.com/bug.php?id=38941
http://dev.mysql.com/worklog/task/?id=4601

Said the above fast mutexes will unlikely scale better than normal mutexes ever. We agreed to disable fast mutexes in our release build configuration.

Sergei, please review fix for this bug.

Hi Sergey and Sergei:
The patches to add the cacheline tugging detection to the perf tool (perf c2c) were recently submitted upstream. See http://lwn.net/Articles/585195/.
They are still in review with some cleanup to be added, but it's moving forward.

If I get a chance, I'll take the version of MariaDB that's part of RHEL, run "perf c2c" on it during a sysbench run, and will post the output here so you can see what the tool is showing.

Joe

ok to push

Fixed in 5.5.38:

revno: 4174

revision-id: svoj@mariadb.org-20140228114602-nyj6i2fejiywnhbx

parent: monty@mariadb.org-20140503161217-ac6ec1uoq5sdg40o

committer: Sergey Vojtovich <svoj@mariadb.org>

branch nick: 5.5-mdev5081

timestamp: Fri 2014-02-28 15:46:02 +0400

message:

  MDEV-5081 - Simple performance improvement for MariaDB

  Currently fast mutexes have lower throuput compared to normal mutexes.

  Remove them from release build configuration.

Joe, thanks for the c2c tool link. I will try to make use of it in further benchmark.