I updated my benchmark scripts this week. These scripts can now
run:
- Sysbench benchmarks for MySQL and Drizzle
- DBT2 benchmarks for MySQL and MySQL Cluster
- TPC-W benchmark for MySQL Cluster
There is also a number of scripts to start and stop
MySQL, Drizzle and MySQL Cluster nodes.
In this version I added Drizzle support for sysbench and also
added a README-AUTOMATED file that describes the steps needed
to set-up a completely automated sysbench run for MySQL
and Drizzle.
To run a MySQL sysbench benchmark one needs the DBT2 tarball,
the sysbench tarball and a MySQL tarball (gzipped tarballs).
The tarball is found on www.iclaustron.com in the downloads
section and this version is named dbt2-0.37.47.tar.gz.
Tuesday, November 17, 2009
Thursday, November 12, 2009
Improvements by LOCK_* patches
I have done a long series of tests to verify that
the impact of the LOCK_alarm removal, removing two
variables from LOCK_threadcount protection to being
atomic increments instead and decreasing hold time
of LOCK_open is positive in most if not all cases.
There are a number of test cases needed:
1) With and Without cpuspeed activated
2) With sysbench local and with sysbench on another
server
3) With MySQL Server limited to 2,4,6,8,12,16 cores.
4) With number of threads going from 1 to 256 threads
in fair sized steps.
I've done most of those tests for Sysbench Readonly
and Sysbench Readwrite. The results are positive in
almost all cases towards the baseline which is based
off the MySQL 5.4.3 tree (not exactly 5.4.3 but close
enough).
In the case of networked benchmark with cpuspeed
activated the gain is biggest, top performance goes up
about 10% and also top performance moves from 64 to 128
threads, for 256 threads the performance increases
by about 50%.
When cpuspeed is deactivated and we use networked
benchmarks the network handling becomes a bottleneck, so
the numbers here are less interesting since we need to
resolve the network bottleneck first.
With cpuspeed activated and local communication the top
performance increase by about 8% and there is gain
for all number of threads. The gain is a bit higher
on sysbench readonly than on sysbench readwrite.
With cpuspeed deactivated and local communication we
naturally get the best numbers but also the
smallest gains. Top performance of Sysbench
Readonly increased by 2.5% and for Readwrite
it increased 4%, the top performance for
sysbench readwrite also moved from 16 threads
to 32 threads. The improvement is slightly
better on more threads.
the impact of the LOCK_alarm removal, removing two
variables from LOCK_threadcount protection to being
atomic increments instead and decreasing hold time
of LOCK_open is positive in most if not all cases.
There are a number of test cases needed:
1) With and Without cpuspeed activated
2) With sysbench local and with sysbench on another
server
3) With MySQL Server limited to 2,4,6,8,12,16 cores.
4) With number of threads going from 1 to 256 threads
in fair sized steps.
I've done most of those tests for Sysbench Readonly
and Sysbench Readwrite. The results are positive in
almost all cases towards the baseline which is based
off the MySQL 5.4.3 tree (not exactly 5.4.3 but close
enough).
In the case of networked benchmark with cpuspeed
activated the gain is biggest, top performance goes up
about 10% and also top performance moves from 64 to 128
threads, for 256 threads the performance increases
by about 50%.
When cpuspeed is deactivated and we use networked
benchmarks the network handling becomes a bottleneck, so
the numbers here are less interesting since we need to
resolve the network bottleneck first.
With cpuspeed activated and local communication the top
performance increase by about 8% and there is gain
for all number of threads. The gain is a bit higher
on sysbench readonly than on sysbench readwrite.
With cpuspeed deactivated and local communication we
naturally get the best numbers but also the
smallest gains. Top performance of Sysbench
Readonly increased by 2.5% and for Readwrite
it increased 4%, the top performance for
sysbench readwrite also moved from 16 threads
to 32 threads. The improvement is slightly
better on more threads.
Wednesday, November 11, 2009
Analysis of InnoDB adaptive hash index parameter for sysbench
As I mentioned in a previous blog post I was suspicious that the
adaptive hash index in InnoDB added to the scalability issues in
the MySQL Server. So I decided to run a test where I disabled the
adaptive hash index.
The results were fairly much as expected. The adaptive hash
index usage improves performance on low thread counts
(up to about 16) by a few percent. However at 32 threads and
beyond the performance is better without the adaptive hash
index and equal in some cases. In particular the top performance
goes up by about 3% when this is disabled.
This confirms the documentation in the InnoDB manual that the
adaptive hash index improves performance as long as the lock
around it doesn't hurt performance. So for the majority of users
it's a good idea to have it turned on, but for users with high-end
servers it's a good idea to test if turning it off will improve
performance.
For sysbench benchmarks it's clearly a good idea to turn it off.
adaptive hash index in InnoDB added to the scalability issues in
the MySQL Server. So I decided to run a test where I disabled the
adaptive hash index.
The results were fairly much as expected. The adaptive hash
index usage improves performance on low thread counts
(up to about 16) by a few percent. However at 32 threads and
beyond the performance is better without the adaptive hash
index and equal in some cases. In particular the top performance
goes up by about 3% when this is disabled.
This confirms the documentation in the InnoDB manual that the
adaptive hash index improves performance as long as the lock
around it doesn't hurt performance. So for the majority of users
it's a good idea to have it turned on, but for users with high-end
servers it's a good idea to test if turning it off will improve
performance.
For sysbench benchmarks it's clearly a good idea to turn it off.
245% improvement of MySQL performance in 1 year
When I did sysbench benchmarks 1 year ago I used a
4-socket server, a Linux kernel based on 2.6.18 and
MySQL 5.1. The sysbench readwrite numbers I got then
was around 2700. When I run the same benchmarks
now the numbers I get are 9300.
These improvements obviously comes from a mixture
of HW development, OS development (now using
a 2.6.31 based kernel) and MySQL development.
The machine is still a 4-socket server, the operating
system is still Linux and the database is still MySQL,
but the performance has improved by 245%. Needless
to say this is an extraordinary performance
improvement in just one year and clearly shows that
the both the HW industry and the open source SW
industry is quickly picking up on how to improve
performance using multi-core multi-socket servers.
4-socket server, a Linux kernel based on 2.6.18 and
MySQL 5.1. The sysbench readwrite numbers I got then
was around 2700. When I run the same benchmarks
now the numbers I get are 9300.
These improvements obviously comes from a mixture
of HW development, OS development (now using
a 2.6.31 based kernel) and MySQL development.
The machine is still a 4-socket server, the operating
system is still Linux and the database is still MySQL,
but the performance has improved by 245%. Needless
to say this is an extraordinary performance
improvement in just one year and clearly shows that
the both the HW industry and the open source SW
industry is quickly picking up on how to improve
performance using multi-core multi-socket servers.
Subscribe to:
Posts (Atom)