When experimenting with the Google patches a few years ago I found that tcmalloc had a fairly large impact on performance of the MySQL Server. So the question I asked myself was obviously whether the libc malloc have regained some of the lost territory (also had regressions of major drop in performance in certain libc versions). Using tcmalloc used to have a 5-10% positive impact on performance, the matter of the fact is that this gain remains. I lost 8-10% in performance on all thread counts tested (16, 32, 64, 128 and 256) by not using tcmalloc in running Sysbench RW.
The experiments are performed on a fairly high-end x86 box with 4 sockets. I run the sysbench program on the same machine as the MySQL Server runs on. So this means that it's interesting to check whether I get better performance by locking the MySQL Server to 3 of the 4 sockets and let sysbench use its own socket compared to not control CPU usage at all.
What I discovered is a mixed picture. Performance when locking to CPU's was much more stable although top performance was better without locking. Performance at 16 threads improved 3% and at 32 threads it improved 7%. But at higher thread counts the performance was better for the locked scenario, 10% at 64 threads and 4% at 256 threads. I used the Linux feature taskset to lock the MySQL Server and Sysbench to certain CPUs.
So the conclusion is that locking to CPUs gives a more stable environment. When the number of threads increases the scheduler is allowed to use more CPUs than what is beneficial for MySQL execution. I've seen this also in other experiments that making sure that MySQL reuses the CPU caches as much as possible is very important for performance. Thus when MySQL competes with other programs on use of CPUs and there are many concurrent MySQL threads it's usually not beneficial to performance since the CPU caches will be too cold.
Using Unix sockets instead of TCP/IP sockets is very beneficial for MySQL performance still. I haven't made any recent experiments in this area but the difference is definitely significant. I have also seen OS bottlenecks sometimes appear when using TCP/IP sockets. This is an area for further investigation which I have had on my TODO list for a while. It's also interesting to experiment with different communication mechanisms when the Sysbench program and MySQL runs on different computers. However this is for future testing.
Thursday, October 28, 2010
Finding the optimum configuration of MySQL 5.5 running Sysbench
Sysbench is a commonly used benchmark tool to discover ways to improve MySQL performance. It is certainly not representative for every application, but it's still a useful tool for finding bottlenecks in the MySQL code.
In MySQL 5.5 a great number of new scalability improvements have been developed. Some of these will always be active and some of them requires using new configuration parameters.
In order to assist users of MySQL I am performing a fairly extensive benchmark series where I test the various configuration parameters that have an effect on running MySQL/InnoDB using Sysbench.
The parameters can be categorized into:
1) Changes of run-time environment
2) Compile time parameters (including choice of compiler)
3) Configuration parameters for MySQL
4) Configuration parameters for InnoDB
Finally there is also a set of parameters one can use to affect the execution of sysbench itself to have it behave differently. It's possible to have Sysbench use secondary index instead of primary key index, it's possible to let the table be partitioned and it's possible to run sysbench using multiple tables instead of only one (it's very syntetic to only have one table in a system which gets all the queries).
The parameters that I have found to be important to consider for performance of MySQL 5.5 when running sysbench are:
1.1) Use of tcmalloc
1.2) Use of taskset (lock MySQL to certain CPUs)
1.3) Affecting memory allocation by use of numactl
1.4) Connecting to MySQL through socket or using TCP/IP
2.1) Use of gcc compiler with platform-specific optimisations
2.2) Use --with-fast-mutexes flag when compiling MySQL
2.3) Choice of compiler (gcc, Open64, icc, SunStudio, ..)
2.4) Using feedback compilation in compilation
2.5) Compiling sysbench itself with platform specific optimisations
3.1) Choice of transaction isolation (READ COMMITTED or REPEATABLE READ)
3.2) Use of large pages through setting --large-pages=ON
3.3) Compiling with Performance Schema activated
3.4) Running with Performance Schema activated through --perfschema
3.5) Deactivating Query Cache completely through --query_cache_size=0
and --query_cache_type=0
4.1) Using new file format Barracuda or old format Antelope through
--innodb_file_format=x
4.2) Setting --innodb_stats_on_metadata to ON/OFF
4.3) Deactivating InnoDB doublewrite through --skip-innodb_doublewrite
4.4) Setting innodb-change-buffering to none/insert/all and so forth
4.5) Setting number of buffer pool instances using innodb-buffer-pool-instances=x
4.6) Setting InnoDB log file size using innodb-log-file-size=x
4.7) Setting InnoDB log buffer size using innodb-log-buffer-size=x
4.8) Setting InnoDB flush method (default fsync, O_DIRECT, O_DSYNC) using
--innodb_flush_method=x
4.9) Setting InnoDB to use file per table using --innodb_file_per_table
4.10) Deactivating InnoDB adaptive hash index using
--skip-innodb-adaptive-hash-index
4.11) Adapting read-ahead using --innodb_read_ahead=x
4.12) Using InnoDB max purge lag through --innodb_max_purge_lag=x
4.13) Using InnoDB purge thread through --innodb_purge_thread=1
4.14) Changing behaviour of InnoDB spin loops by changing
--innodb_sync_spin_loops=x and --innodb_spin_wait_delay=x
4.15) Changing InnoDB IO capacity by setting --innodb-io-capacity=x
4.16) Setting InnoDB buffer pool size through --innodb-buffer-pool-size=x
4.17) Setting InnoDB dirty pages percent through --innodb_dirty_pages_pct=x
4.18) Setting InnoDB old blocks percentage through --innodb_old_blocks_pct=x
4.19) Activating support for InnoDB XA through --innodb_support_xa=FALSE/TRUE
4.20) Activating InnoDB thread concurrency using
--innodb-thread-concurrency=x
4.21) Setting InnoDB commit mechanism through
--innodb-flush-log-at-trx-commit=x
4.22) Setting number of read and write IO threads through
--innodb-read-io-threads=x and --innodb-write-io-threads=x
This makes for a total of 36 parameters that can be tuned to affect sysbench performance. Most of these parameters have only a limited impact on performance but some of them can have a large impact and there is also a number of them where most reasonable values are ok, but if a bad value is used it can have a major impact on performance.
In addition the problem of finding the optimal values is a multi-dimensional search. So changing one parameter might very well affect the impact of other parameters. So my approach will be to start with a reasonable baseline configuration, then try each parameter and see how it affects the outcome, come to a new baseline by changing the most important parameter. Next step is to restart tests varying all the parameters which have had a measurable impact on performance. Using this method we should be able to find an optimum to a reasonable degree. Finding the absolute optimum is probably more or less practically impossible.
It's important here to understand that the MySQL defaults can sometimes have really bad values for performance. The reason is that MySQL defaults have been choosen mainly to make MySQL run on any HW platform and use very small resources. Remember that the normal use of MySQL isn't running sysbench on a 32-core server :) So it is important to consider all of these parameters to get the optimal performance of the MySQL Server.
I plan to write about these variables and how they affect sysbench performance in a number of upcoming blogs. I might also do a similar run with MySQL using the DBT2 benchmark. So hopefully after finishing these test runs I have found a reasonable optimum configuration to run sysbench and probably also the configuration parameters that needs changing when running DBT2 instead.
In MySQL 5.5 a great number of new scalability improvements have been developed. Some of these will always be active and some of them requires using new configuration parameters.
In order to assist users of MySQL I am performing a fairly extensive benchmark series where I test the various configuration parameters that have an effect on running MySQL/InnoDB using Sysbench.
The parameters can be categorized into:
1) Changes of run-time environment
2) Compile time parameters (including choice of compiler)
3) Configuration parameters for MySQL
4) Configuration parameters for InnoDB
Finally there is also a set of parameters one can use to affect the execution of sysbench itself to have it behave differently. It's possible to have Sysbench use secondary index instead of primary key index, it's possible to let the table be partitioned and it's possible to run sysbench using multiple tables instead of only one (it's very syntetic to only have one table in a system which gets all the queries).
The parameters that I have found to be important to consider for performance of MySQL 5.5 when running sysbench are:
1.1) Use of tcmalloc
1.2) Use of taskset (lock MySQL to certain CPUs)
1.3) Affecting memory allocation by use of numactl
1.4) Connecting to MySQL through socket or using TCP/IP
2.1) Use of gcc compiler with platform-specific optimisations
2.2) Use --with-fast-mutexes flag when compiling MySQL
2.3) Choice of compiler (gcc, Open64, icc, SunStudio, ..)
2.4) Using feedback compilation in compilation
2.5) Compiling sysbench itself with platform specific optimisations
3.1) Choice of transaction isolation (READ COMMITTED or REPEATABLE READ)
3.2) Use of large pages through setting --large-pages=ON
3.3) Compiling with Performance Schema activated
3.4) Running with Performance Schema activated through --perfschema
3.5) Deactivating Query Cache completely through --query_cache_size=0
and --query_cache_type=0
4.1) Using new file format Barracuda or old format Antelope through
--innodb_file_format=x
4.2) Setting --innodb_stats_on_metadata to ON/OFF
4.3) Deactivating InnoDB doublewrite through --skip-innodb_doublewrite
4.4) Setting innodb-change-buffering to none/insert/all and so forth
4.5) Setting number of buffer pool instances using innodb-buffer-pool-instances=x
4.6) Setting InnoDB log file size using innodb-log-file-size=x
4.7) Setting InnoDB log buffer size using innodb-log-buffer-size=x
4.8) Setting InnoDB flush method (default fsync, O_DIRECT, O_DSYNC) using
--innodb_flush_method=x
4.9) Setting InnoDB to use file per table using --innodb_file_per_table
4.10) Deactivating InnoDB adaptive hash index using
--skip-innodb-adaptive-hash-index
4.11) Adapting read-ahead using --innodb_read_ahead=x
4.12) Using InnoDB max purge lag through --innodb_max_purge_lag=x
4.13) Using InnoDB purge thread through --innodb_purge_thread=1
4.14) Changing behaviour of InnoDB spin loops by changing
--innodb_sync_spin_loops=x and --innodb_spin_wait_delay=x
4.15) Changing InnoDB IO capacity by setting --innodb-io-capacity=x
4.16) Setting InnoDB buffer pool size through --innodb-buffer-pool-size=x
4.17) Setting InnoDB dirty pages percent through --innodb_dirty_pages_pct=x
4.18) Setting InnoDB old blocks percentage through --innodb_old_blocks_pct=x
4.19) Activating support for InnoDB XA through --innodb_support_xa=FALSE/TRUE
4.20) Activating InnoDB thread concurrency using
--innodb-thread-concurrency=x
4.21) Setting InnoDB commit mechanism through
--innodb-flush-log-at-trx-commit=x
4.22) Setting number of read and write IO threads through
--innodb-read-io-threads=x and --innodb-write-io-threads=x
This makes for a total of 36 parameters that can be tuned to affect sysbench performance. Most of these parameters have only a limited impact on performance but some of them can have a large impact and there is also a number of them where most reasonable values are ok, but if a bad value is used it can have a major impact on performance.
In addition the problem of finding the optimal values is a multi-dimensional search. So changing one parameter might very well affect the impact of other parameters. So my approach will be to start with a reasonable baseline configuration, then try each parameter and see how it affects the outcome, come to a new baseline by changing the most important parameter. Next step is to restart tests varying all the parameters which have had a measurable impact on performance. Using this method we should be able to find an optimum to a reasonable degree. Finding the absolute optimum is probably more or less practically impossible.
It's important here to understand that the MySQL defaults can sometimes have really bad values for performance. The reason is that MySQL defaults have been choosen mainly to make MySQL run on any HW platform and use very small resources. Remember that the normal use of MySQL isn't running sysbench on a 32-core server :) So it is important to consider all of these parameters to get the optimal performance of the MySQL Server.
I plan to write about these variables and how they affect sysbench performance in a number of upcoming blogs. I might also do a similar run with MySQL using the DBT2 benchmark. So hopefully after finishing these test runs I have found a reasonable optimum configuration to run sysbench and probably also the configuration parameters that needs changing when running DBT2 instead.
Wednesday, September 22, 2010
How to speed up Sysbench on MySQL Cluster by 14x
The time period up to the 2010 MySQL Users conference was as usual packed with hard work. The last two conferences have been very focused on getting scalability of the MySQL Server and InnoDB improved. This year had a nasty surprise after the conference in the form of ash cloud from an icelandic volcano. When you're tired from hard work and just want to go home and get some rest then the concept of staying at a hotel room with absolutely no idea of when one could return home is no fun at all. So when I finally returned home I was happy that summer was close by, vacation days were available (swedes have a lot of vacation :)).
Now the summer is gone and I am rested up again, ready to take on new challenges and we've had some really interesting meet-ups in the MySQL team to discuss future developments. The renewed energy also is sufficient to write up some of the stories from the work I did during the summer :)
During the summer I had the opportunity to also get some work done on scalability of the MySQL Cluster product as well. Given that I once was the founder of this product it was nice to return again and check where it stands in scalability terms.
The objective was to compare MySQL Cluster to the Memory engine. The result of the exercise was almost obvious from the start. The memory engine having a table lock will have very limited scalability on any workload that contains writes. It will however have very good scalability on read-only workloads as this isn't limited by the table lock since readers don't contend each other. The Cluster engine should have good and fairly even results on read and write workloads.
Much to my surprise the early results showed a completely different story. The Memory engine gave me a performance of about 1-2 tps to start with. The early results of MySQL Cluster was also very dismaying. I covered the Memory engine in a previous blog, so in this blog I will focus on the MySQL Cluster benchmarks.
So the simple task of benchmarking as usual turned into some debugging of where the performance problems comes from.
In the first experiment I used the default configuration of the Fedora Linux OS, I also used the default set-up of the MySQL Cluster storage engine. It turned out that there are huge possibilities in adapting those defaults.
First the Fedora has a feature called cpuspeed. By default this feature is activated. The feature provides power management by scaling down the CPU frequency on an entire socket. The problem is that when you run the MySQL Server with few threads, it doesn't react to the workload and scales down frequency although there is a lot of work to do. So for the MySQL Server in general this means about half the throughput on up to 16 threads. However the impact on MySQL Cluster is even worse. The performance drops severely on all thread counts. Most likely this impact comes from the very short execution times of the NDB data nodes. It's possible that this small execution times is too short to even reach the radar of the power management tools in Linux.
So a simple sudo /etc/init.d/cpuspeed stop generated a major jump in performance of the MySQL Cluster in a simple Sysbench benchmark (all the benchmarks discussed here used 1 data node and all things running on one machine unless otherwise stated).
The next simple step was to add the MySQL Cluster configuration parameter MaxNoOfExecutionThreads to the configuration scripts and set this to the maximum which is 8. This means that one thread will handle receive on the sockets, one thread will handle transaction coordination and four threads will handle local database handling. There will also be a couple of other threads which are of no importance to a benchmark.
These two configuration together added about ~3.5x in increased performance. Off to a good start, but still performance isn't at all where I want it to be.
In NDB there is a major scalability bottleneck in the mutex protecting all socket traffic. It's the NDB API's variant of the big kernel mutex. There is however one method of decreasing the impact of this mutex by turning the MySQL Server into several client nodes from an NDB perspective. This is done by adding the --ndb-cluster-connection-pool parameter when starting the MySQL Server. We achieved the best performance when setting this to 8, in a bigger cluster it would probably make more sense to set it to 2 or 3 since this resolves most of the scalability issues without using up so much nodes in the NDB cluster.
Changing this from 1 to 8 added another ~2x in performance. So now the performance is up a decent 8x from the first experiments. No wonder I was dismayed by the early results.
However the story isn't done yet :)
MySQL Cluster has another feature whereby the various threads can be locked to CPUs. By using this feature we can achieve two things, the first is that the NDB data nodes doesn't share CPUs with the MySQL Server. This has some obvious benefits from CPU cache point of view for both node types. We can also avoid that the data node threads are moved from CPU to CPU which is greatly advantegous in busy workloads. So we locked the data nodes to 6 cores. The configuration variable we used to achieve this is LockExecuteThreadToCpu which is set to a comma separated list of CPU ids.
I also locked the MySQL Server and Sysbench to different set of CPUs using the taskset program available in Linux.
Using this locking of NDB data node threads to CPUs achieved another 80% boost in performance. So the final result gave us a decent 14x performance improvement.
So in summary things that matters greatly to performance of MySQL Cluster for Sysbench with a single data node.
1) Ensure the Linux cpuspeed isn't activated
2) Make sure to set MaxNoOfExecutionThreads to 8
3) Make sure the --ndb-cluster-configuration-pool parameter to the MySQL Server using around 8 nodes per MySQL Server
4) Lock NDB Data node threads to CPUs by using the LockExecuteThreadToCpu.
5) Lock MySQL Server and Sysbench processes to different sets of CPUs from NDB Data nodes and each other.
Doing this experiments also generated a lot of interesting ideas on how to improve things even further.
Now the summer is gone and I am rested up again, ready to take on new challenges and we've had some really interesting meet-ups in the MySQL team to discuss future developments. The renewed energy also is sufficient to write up some of the stories from the work I did during the summer :)
During the summer I had the opportunity to also get some work done on scalability of the MySQL Cluster product as well. Given that I once was the founder of this product it was nice to return again and check where it stands in scalability terms.
The objective was to compare MySQL Cluster to the Memory engine. The result of the exercise was almost obvious from the start. The memory engine having a table lock will have very limited scalability on any workload that contains writes. It will however have very good scalability on read-only workloads as this isn't limited by the table lock since readers don't contend each other. The Cluster engine should have good and fairly even results on read and write workloads.
Much to my surprise the early results showed a completely different story. The Memory engine gave me a performance of about 1-2 tps to start with. The early results of MySQL Cluster was also very dismaying. I covered the Memory engine in a previous blog, so in this blog I will focus on the MySQL Cluster benchmarks.
So the simple task of benchmarking as usual turned into some debugging of where the performance problems comes from.
In the first experiment I used the default configuration of the Fedora Linux OS, I also used the default set-up of the MySQL Cluster storage engine. It turned out that there are huge possibilities in adapting those defaults.
First the Fedora has a feature called cpuspeed. By default this feature is activated. The feature provides power management by scaling down the CPU frequency on an entire socket. The problem is that when you run the MySQL Server with few threads, it doesn't react to the workload and scales down frequency although there is a lot of work to do. So for the MySQL Server in general this means about half the throughput on up to 16 threads. However the impact on MySQL Cluster is even worse. The performance drops severely on all thread counts. Most likely this impact comes from the very short execution times of the NDB data nodes. It's possible that this small execution times is too short to even reach the radar of the power management tools in Linux.
So a simple sudo /etc/init.d/cpuspeed stop generated a major jump in performance of the MySQL Cluster in a simple Sysbench benchmark (all the benchmarks discussed here used 1 data node and all things running on one machine unless otherwise stated).
The next simple step was to add the MySQL Cluster configuration parameter MaxNoOfExecutionThreads to the configuration scripts and set this to the maximum which is 8. This means that one thread will handle receive on the sockets, one thread will handle transaction coordination and four threads will handle local database handling. There will also be a couple of other threads which are of no importance to a benchmark.
These two configuration together added about ~3.5x in increased performance. Off to a good start, but still performance isn't at all where I want it to be.
In NDB there is a major scalability bottleneck in the mutex protecting all socket traffic. It's the NDB API's variant of the big kernel mutex. There is however one method of decreasing the impact of this mutex by turning the MySQL Server into several client nodes from an NDB perspective. This is done by adding the --ndb-cluster-connection-pool parameter when starting the MySQL Server. We achieved the best performance when setting this to 8, in a bigger cluster it would probably make more sense to set it to 2 or 3 since this resolves most of the scalability issues without using up so much nodes in the NDB cluster.
Changing this from 1 to 8 added another ~2x in performance. So now the performance is up a decent 8x from the first experiments. No wonder I was dismayed by the early results.
However the story isn't done yet :)
MySQL Cluster has another feature whereby the various threads can be locked to CPUs. By using this feature we can achieve two things, the first is that the NDB data nodes doesn't share CPUs with the MySQL Server. This has some obvious benefits from CPU cache point of view for both node types. We can also avoid that the data node threads are moved from CPU to CPU which is greatly advantegous in busy workloads. So we locked the data nodes to 6 cores. The configuration variable we used to achieve this is LockExecuteThreadToCpu which is set to a comma separated list of CPU ids.
I also locked the MySQL Server and Sysbench to different set of CPUs using the taskset program available in Linux.
Using this locking of NDB data node threads to CPUs achieved another 80% boost in performance. So the final result gave us a decent 14x performance improvement.
So in summary things that matters greatly to performance of MySQL Cluster for Sysbench with a single data node.
1) Ensure the Linux cpuspeed isn't activated
2) Make sure to set MaxNoOfExecutionThreads to 8
3) Make sure the --ndb-cluster-configuration-pool parameter to the MySQL Server using around 8 nodes per MySQL Server
4) Lock NDB Data node threads to CPUs by using the LockExecuteThreadToCpu.
5) Lock MySQL Server and Sysbench processes to different sets of CPUs from NDB Data nodes and each other.
Doing this experiments also generated a lot of interesting ideas on how to improve things even further.
How to get Sysbench on Memory engine to perform
I had the opportunity to test the Memory engine during the summer. What I expected to be a very simple exercise in running the Sysbench benchmark turned out to be a lot more difficult than I expected.
My first experiments with the Memory engine gave very dismaying results. I got 0-2 TPS which is ridiculously bad. So I couldn't really think this was proper results, so I started searching for problems in my benchmarking environment. Eventually I started setting up a normal MySQL client session to the MySQL Server while the benchmark was running and issued some of the queries in the benchmark by hand and I was surprised to see some simple queries take seconds.
EXPLAIN came to my rescue. EXPLAIN showed that the range scans in Sysbench was in fact turned into full table scans. Now this was surprising given that the primary key index in most engines is always ordered, so a range scan should normally be translated to a simple ordered index scan.
What I found is that the Memory engine has a different default compared to the other storage engines. When we designed the NDB handler we decided that any SQL users expects to have an ordered index when they create an index on a table. Since primary key indexes are always hash-based, this meant that in NDB the default primary key index is actually two indexes, one primary hash index and one secondary ordered index on the same fields.
Not so in the Memory engine. The memory engine also uses a hash-based index by default. So when you create a new index on a Memory engine and specify no type, the index wil become a hash index. Hash indexes are not very good at range queries :) so thus the surprise to me when benchmarking the Memory engine in Sysbench.
Fixing this issue required some code changes in the sysbench test. It required the proposed fix from Mark Callaghan to add secondary indexes to sysbench. This was however not sufficient since this patch only added an index by adding KEY xid (id) and didn't specifically specify this index had to be an ordered index. So I changed this to KEY xid (id) USING BTREE and off the performance went.
The Memory engine is still more or less a single threaded engine for any write workload like Sysbench RW which limits performance very much.
However the Sysbench Readonly benchmark for the Memory engine was interesting since it used no limiting locks (concurrent readers don't contend with each other). I decided to see how much scalability was achievable for a storage engine without any concurrency issues internally.
I found that performance of the Memory engine was limited to 8% more than the InnoDB engine in the same benchmark. So my interpretation of this result is that for readonly workloads, the main limiting factor on scalability is the MySQL Server scalability issues and not the InnoDB ones.
So going forward when working on further improving the scalability of the MySQL Server parts there is a perfect benchmark that can be used to see how scalable the MySQL Server part is by using the Memory engine.
I plan on also adding a feature to sysbench making it possible to use more than one Sysbench table to see how much added scalability we get when there are several tables involved in the query mix. Sysbench is a a very syntectic benchmark in this manner that it only uses one table.
Only using one table provokes more bottlenecks than is found in most normal workloads, e.g. the LOCK_open in the MySQL Server, the meta data locks introduced in MySQL 5.5, the index mutex in InnoDB and even some unbalance to the usage of multiple buffer pools come from only using one table in the benchmark.
My first experiments with the Memory engine gave very dismaying results. I got 0-2 TPS which is ridiculously bad. So I couldn't really think this was proper results, so I started searching for problems in my benchmarking environment. Eventually I started setting up a normal MySQL client session to the MySQL Server while the benchmark was running and issued some of the queries in the benchmark by hand and I was surprised to see some simple queries take seconds.
EXPLAIN came to my rescue. EXPLAIN showed that the range scans in Sysbench was in fact turned into full table scans. Now this was surprising given that the primary key index in most engines is always ordered, so a range scan should normally be translated to a simple ordered index scan.
What I found is that the Memory engine has a different default compared to the other storage engines. When we designed the NDB handler we decided that any SQL users expects to have an ordered index when they create an index on a table. Since primary key indexes are always hash-based, this meant that in NDB the default primary key index is actually two indexes, one primary hash index and one secondary ordered index on the same fields.
Not so in the Memory engine. The memory engine also uses a hash-based index by default. So when you create a new index on a Memory engine and specify no type, the index wil become a hash index. Hash indexes are not very good at range queries :) so thus the surprise to me when benchmarking the Memory engine in Sysbench.
Fixing this issue required some code changes in the sysbench test. It required the proposed fix from Mark Callaghan to add secondary indexes to sysbench. This was however not sufficient since this patch only added an index by adding KEY xid (id) and didn't specifically specify this index had to be an ordered index. So I changed this to KEY xid (id) USING BTREE and off the performance went.
The Memory engine is still more or less a single threaded engine for any write workload like Sysbench RW which limits performance very much.
However the Sysbench Readonly benchmark for the Memory engine was interesting since it used no limiting locks (concurrent readers don't contend with each other). I decided to see how much scalability was achievable for a storage engine without any concurrency issues internally.
I found that performance of the Memory engine was limited to 8% more than the InnoDB engine in the same benchmark. So my interpretation of this result is that for readonly workloads, the main limiting factor on scalability is the MySQL Server scalability issues and not the InnoDB ones.
So going forward when working on further improving the scalability of the MySQL Server parts there is a perfect benchmark that can be used to see how scalable the MySQL Server part is by using the Memory engine.
I plan on also adding a feature to sysbench making it possible to use more than one Sysbench table to see how much added scalability we get when there are several tables involved in the query mix. Sysbench is a a very syntectic benchmark in this manner that it only uses one table.
Only using one table provokes more bottlenecks than is found in most normal workloads, e.g. the LOCK_open in the MySQL Server, the meta data locks introduced in MySQL 5.5, the index mutex in InnoDB and even some unbalance to the usage of multiple buffer pools come from only using one table in the benchmark.
Subscribe to:
Posts (Atom)