MySQL Bugs: #37703: MySQL performance with and without Fast Mutexes using Sysbench Workload

Bug #37703	MySQL performance with and without Fast Mutexes using Sysbench Workload
Submitted:	27 Jun 2008 21:20	Modified:	29 Apr 2015 0:09
Reporter:	Harita Chilukuri	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S5 (Performance)
Version:	MYSQL 6.0.4 (Source Distribution)	OS:	Linux (EL5.1)
Assigned to:		CPU Architecture:	Any
Tags:	fast mutexes

Description:
I did some of the work related to MySQL performance with and without fast mutexes. I see that there is no performance difference between them. From the cycles profiling data, it shows that the % cycles is divided among __pthread_mutex_trylock, __pthread_mutex_lock and my_pthread_fastmutex_lock (with fast mutexes) which is equal to the __pthread_mutex_lock ( without fast mutexes. 

I am using Intel® 2x Clovertown (quad core processor) and Sysbench workload.

How to repeat:
Run MYSQL 6.0.4 with and without fast mutexes using sysbench workload.

Suggested fix:
Decrease the number of spins and delays to 2 in MY_PTHREAD_FASTMUTEX_LOCK(). By decreasing the spins and delays, the chances of acquiring a lock increases. This gives ~2.01% gain/improvement in performance.

Thank you for a problem report. Please, try to repeat with a newer version, 6.0.6, and inform about the results. In case of the same problem, please, specify the exact sysbench commands used to test.

I cannot try this with 6.0.6 as I do hit this bug http://bugs.mysql.com/bug.php?id=39490, which is fixed in the 6.0.7 version.

Hence, I will post the results using 6.0.7 soon.

Thanks!!!

OK, let's wait for 6.0.7.

I tried the same experiment with MySQL 6.0.7 from the development tree. 

The problem still exists: there is no performance difference at all (within noise range) when mysql is complied with or without fast mutexes. Also, if I reduce the number of spins and delays there is no improvement in the performance (within noise range).

Has there been any specific changes made in this mysql version, as I dont see any performance difference by decreasing the number of spins and delays??

Below is the detailed results:

Test               Transactions per second	CPU utilization(usr sys idle wt)
-------------------------------------------------------------------------------
Without                 90578.66                  73    22    5    0
Fast Mutexes			
----------------------------------------------------------------------------
With Fast Mutexes	90470.06	          73   22    4    0
----------------------------------------------------------------------------
Reduced the number 
of spins and delays	 90310.32	           73   22    4    0

Sysbench Tests:

1) ./sysbench prepare --test=oltp --mysql-table-engine=falcon --mysql-engine-trx=yes --mysql-socket=/tmp/mysql.sock  --oltp-table-size=100000 --oltp-test-mode=simple

2) ./sysbench run --num-threads=8 --max-time=220 --max-requests=0 --mysql-table-engine=falcon --mysql-engine-trx=yes --test=oltp --oltp-test-mode=simple --oltp-table-size=100000 --mysql-socket=/tmp/mysql.sock  > $OUTPUT_RESULT_DIR/metrics.out

Thanks,
Harita

WL#4601

Noted in 5.7.8 changelog.

The so-called "fast mutex" code has been removed from the server
sources. It provides no measurable benefit, complicates the code, and
is problematic for certain architectures such as POWER8. The
(undocumented) WITH_FAST_MUTEXES CMake option has also been removed.