Hi Sergei, MySQL has the following optimization: WL#5772 - Add partitioned Table Definition Cache to avoid using LOCK_open and its derivatives in DML queries. We didn't agree with it and implemented it differently: MDEV-4702 - Reduce usage of LOCK_open. It was a major rewrite of table cache which improved things considerably, but we didn't reach MySQL numbers yet. We need to do a few extra minor things to get closer to MySQL numbers, MDEV-4956 - Reduce usage of LOCK_open: TABLE_SHARE::tdc.used_tables is among of them (patch is ready). Regards, Sergey On Thu, Nov 21, 2013 at 10:01:35PM +0400, Sergei Petrunia wrote:

On Thu, Nov 21, 2013 at 06:32:45PM +0100, Axel Schwenke wrote:

...

I have run another sysbench with in-memory workload to check where we are with respect to mutex contention.

Great to see the results!

...

The benchmark is sysbench OLTP (using the bzr trunk of sysbench with LUA support). 10 mio rows in a single table (about 2.5G .ibd file). InnoDB buffer pool set to 32G and warmed up.

I have run the test on two different machines. First on our old trusty facebook2 machine with 8 cores/16 threads in two NUMA zones. Second on the fat lizard2 machine with 32 cores/64 threads in 4 NUMA zones.

As expected, the heavy contention of the LOCK_open mutex is more visible on the bigger machine (I have not done any detailed analysis in this benchmark, but before while doing benchmarks for MDEV-5081)

I notice a visible performance gain in MariaDB-10.0 over MariaDB-5.5 at 32 threads. This is probably due to the work on the InnoDB kernel mutex (impossible to prove since 5.5 doesn't have mutex instrumentation). At higher concurrency however the LOCK_open mutex kicks in and performance is clearly dominated by that mutex alone (it flattens out for all 5.5 and 10.0 versions of MariaDB)

MySQL 5.6 clearly outperforms MariaDB on 32 cores. Do we know - Is this because 5.6 has some optimization to LOCK_open (can anybody point at a patch or WL number?) , and that optimization hasn't been ported to 10.0?

...

The good news is, that 10.0.4, 10.0.5 and 10.0.6 are quite close. There is a small regression from 10.0.4 to 10.0.5, but IMHO this isn't worth investigation while the LOCK_open issue is not fixed.

Attached: diagrams. OpenOffice spreadsheets.

Thanks a lot for doing this!

BR Sergei -- Sergei Petrunia, Software Developer MariaDB | Skype: sergefp | Blog: http://s.petrunia.net/blog

_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp

Resources are finite, but it would be great if MariaDB also did something about single-thread performance regressions. MySQL has not done/said much about them in public. Bugs I created on bugs.mysql.com are getting some work, but I think all are still open. On Thu, Nov 21, 2013 at 11:30 PM, Colin Charles wrote:

Hi XL,

On 22 Nov 2013, at 01:32, Axel Schwenke wrote:

...

Hi,

I have run another sysbench with in-memory workload to check where we are with respect to mutex contention.

Thank you for doing this. This is *amazing*, and I hope this continues

<snipped>

...

I notice a visible performance gain in MariaDB-10.0 over MariaDB-5.5 at 32 threads. This is probably due to the work on the InnoDB kernel mutex (impossible to prove since 5.5 doesn't have mutex instrumentation). At higher concurrency however the LOCK_open mutex kicks in and performance is clearly dominated by that mutex alone (it flattens out for all 5.5 and 10.0 versions of MariaDB)

OK, from my reading, it seems that yes, MariaDB 10 is better than 5.5, but in all occurrences performance is not matched to MySQL 5.6. This is something we clearly need to look into before we go GA

Readonly, at 32 threads shows a difference 2,000 tps. And from 10.0.4 -> 10.0.6 we seem to have lost some 1,500 tps as well. This delta is much worse when we have 64 or 128 threads. This seems to also be the case for sysbench r/w.

That said, now is a good time to start fixing these problems, as we clearly need a performant DB for a GA release.

Thank you again XL for doing this, and lets hope we see a lot more

cheers, -colin

-- Colin Charles, Chief Evangelist MariaDB | t: +6-012-204-3201 | Skype: colincharles

_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp

-- Mark Callaghan mdcallag@gmail.com

You can find lots of problems just with sysbench. The issue is limited to simple queries - anything with a short response time. There is work in progress to fix some of this in 5.6 and 5.7. But I have not revisited the bugs lately. Regardless, I think this is an opportunity where MariaDB can distinguish itself. Alas, given my narrow perspective I have no idea whether that is a big deal to others. * http://mysqlha.blogspot.com/2013/09/mysql-41-forever.html * http://mysqlha.blogspot.com/2013/09/single-threaded-connect-performance.html * http://mysqlha.blogspot.com/2013/09/mysql-572-single-threaded-performance.ht... * http://mysqlha.blogspot.com/2013/05/mysql-56-single-threaded-performance.htm... * http://mysqlha.blogspot.com/2013/05/mysql-56-versus-40-for-read-only.html * http://mysqlha.blogspot.com/2013/04/mysql-56-single-thread-update-only.html * http://mysqlha.blogspot.com/2013/03/mysql-56-single-threaded-read-only.html On Fri, Nov 22, 2013 at 12:46 PM, Kristian Nielsen

wrote:

MARK CALLAGHAN writes:

...

Resources are finite, but it would be great if MariaDB also did something about single-thread performance regressions. MySQL has not done/said much

Do you have some pointers about where to start with this? I am thinking about which types of queries / benchmarks to check and improve on. I know you did some work on investigating these regressions already, which is sure to be useful.

- Kristian.

-- Mark Callaghan mdcallag@gmail.com