Yes, it's been a long time. Week after week, I had ideas I wanted to share, but no time at all to blog. Priorities are clear: family and work come first, and it's very difficult to keep a balance between the two.
So I left a lot behind. The major project I worked on last summer, MySQL in a virtualised environment and with multiple instances on larger boxes, was part of the material we presented at the MySQL Customer Conference back in October. The results were pretty good and we showed a good level of scalability for real life applications, well beyond the typical sysbench results you can find by googling a bit here and there. I will blog about it at some point, it's just that it takes a lot of time to reorganise all the material to publish and make it understandable.
This week the topic is slightly different. We did it again, we dug into the art of building MySQL binaries and we did it on Solaris 10 for SPARC servers.
The build
Before going any further, a word of caution. My recommendation is always to rely on the certified binaries that our Build team provide, whether they are Community or Enterprise. But if you want to experiment and if you have time to work on extensive testing, then I suggest to have a look at some of the hints you can find on the net. Again, I have to thank my colleagues, Ritu Kamboj and Luojia Chen, for providing invaluable help and support on this matter.
Back to the real stuff. I built a new version of MySQL Enterprise 5.1.32 for a customer by tweaking some config params a bit and we ended up with a boost in performance between 5% and 20% on sysbench and between 18% and 33% on the application testing.
I could use a T2000 SPARC server to build MySQL, running Solaris 10. I used the latest version of SunStudio 12 and I simply changed this:
In the source code, the file .../storage/innobase/include/univ.i should have line 61 changed as:
/* MY CHANGE */
/* # if !defined(__GNUC__) */
# if !defined(__GNUC__) && !defined(__SUNPRO_C)
# undef UNIV_MUST_NOT_INLINE /* Remove compiler warning */
# define UNIV_MUST_NOT_INLINE
# endif
This is the configure command I used:
./configure
'--prefix=/usr/local/mysql'
'--localstatedir=/usr/local/mysql/data'
'--libexecdir=/usr/local/mysql/bin'
'--with-comment=MySQL Enterprise Server - Advanced Edition (GPL) Built by IZ'
'--with-server-suffix=-enterprise-gpl-advanced'
'--enable-thread-safe-client'
'--enable-local-infile'
'--with-mysqld-libs=-lmtmalloc'
'--with-pic'
'--with-client-ldflags=-static'
'--with-mysqld-ldflags=-static'
'--with-zlib-dir=bundled'
'--with-big-tables'
'--with-ssl'
'--with-readline'
'--with-embedded-server'
'--with-plugins=partition,innobase,archive,blackhole,csv,federated'
'--with-extra-charsets=complex'
'CC=/opt/SUNWspro/bin/cc'
'CXX=/opt/SUNWspro/bin/CC'
'CFLAGS=-Xa -fast -xbuiltin=%all -xO4 -xstrconst -mt -m64 -xunroll=2 -xprefetch=auto -xprefetch_level=3'
'LDFLAGS=-m64'
'CPPFLAGS=-D_FORTEC_'
'CXXFLAGS=-fast -xbuiltin=%all -mt -m64 -xO4 -xunroll=2 -xprefetch=auto -xprefetch_level=3 -noex -fsimple=1 -fns=no'
Here are some comments:
- CC and CXX refer to your SunStudio 12 compiler, so you should set the right path
- Partitioning, InnoDB, Archive, Blackhole, CSV and Federated are available (see '--with-plugins')
- Flags -xO4 (optimisation level) and -Xa (only for the C compiler) seem to give a good improvement.
The resultsWe ran two different sets of tests, one generic and one related to the application that the customer is going to implement.
The first set was based on the popular sysbench. We used InnoDB (since InnoDB is the storage engine that the customer was going to use), measuring read and write performance. The system scaled pretty well up to 64 threads - see table below - reaching 366tps with the new build against 307tps with the original build.
Threads IZ Std IZ vs Std
1 31.28 28.63 9.26%
2 60.21 58.15 3.54%
4 108.14 93.56 15.58%
8 179.59 170.46 5.36%
16 307.78 286.28 7.51%
32 400.14 352.90 13.39%
64 366.04 307.62 18.99%
128 266.61 222.49 19.83%
The final output on sysbench with 64 threads on the new build is here:
sysbench
--test=oltp
--oltp-table-size=1000000
--mysql-socket=/tmp/mysql.sock
--mysql-user=root
prepare
sysbench
--num-threads=64
--max-requests=100000
--test=oltp
--oltp-table-size=1000000
--mysql-socket=/tmp/mysql.sock
--mysql-user=root
run
Number of threads: 64
OLTP test statistics:
queries performed:
read: 1402170
write: 500775
other: 200310
total: 2103255
transactions: 100155 (366.04 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1902945 (6954.84 per sec.)
other operations: 200310 (732.09 per sec.)
Test execution summary:
total time: 273.6145s
total number of events: 100155
total time taken by event execution: 17481.6191
per-request statistics:
min: 40.55ms
avg: 174.55ms
max: 1029.61ms
approx. 95 percentile: 317.37ms
Threads fairness:
events (avg/stddev): 1564.9219/21.04
execution time (avg/stddev): 273.1503/0.04
In the second test, we used a large InnoDB table, with 200M rows, using approx 80GB of space. We used a relatively large buffer size (4 GB), but we measured the performance with a cold cache. This test first creates a large MyISAM temp table from the main InnoDB table, then it appends more rows on the temp table and it scans it to select and to aggregate data. The initial query is based on key access, but the cardinality is not very high and the filter on the SELECT query gets a result of several million rows.
The table below shows the results of the tests. In this test, the queries were executed in a single thread, in a typical transformation for OLTP data on reporting. As you can see, the large scan on the InnoDB tables has a significant improvement of 27% in the new build, whilst the initial sequential scan on the MyISAM table showed an amazing improvement of 33%. The numbers showed in the table are execution times in seconds.
IZ Build Std Build IZ vs Std
INS MyISAM TEMP fm InnoDB 4,584.61 5,830.58 27.18%
SEL MAX(Ln)) FROM MyISAM TEMP 898.31 1,194.78 33.00%
2nd INS MyISAM TEMP fm InnoDB 1,343.36 1,590.90 18.43%
SEL Cols and SUM() fm MyISAM TEMP 1,499.29 1,807.32 20.55%
SEL Cols and SUM() fm MyISAM TEMP 884.03 1,105.91 25.10%
SEL Cols and SUM() fm MyISAM TEMP 871.29 1,092.53 25.39%
SEL Cols and SUM() fm MyISAM TEMP 1,516.87 1,817.73 19.83%
SEL Cols and SUM() fm MyISAM TEMP 883.57 1,109.12 25.53%
SEL Cols and SUM() fm MyISAM TEMP 871.01 1,094.52 25.66%
SEL w/WHERE fm MyISAM TEMP 1,406.66 1,637.22 16.39%
SEL w/WHERE fm MyISAM TEMP 890.06 1,114.06 25.17%
TOTAL (sec) 15,649.06 19,394.67 23.94%
For your reference, here is the MySQL config file, for both tests:
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
innodb_locks_unsafe_for_binlog=1
back_log=50
innodb_additional_mem_pool_size=10M
innodb_buffer_pool_size=4G
innodb_flush_method=O_DIRECT
innodb_file_per_table
innodb_file_io_threads=8
innodb_log_buffer_size=6M
innodb_log_file_size=120M
join_buffer_size=8M
key_buffer_size=10M
log_long_format
long_query_time=60
lower_case_table_names=1
max_heap_table_size=16M
query_cache_limit=2M
query_cache_size=64M
read_buffer_size=4M
read_rnd_buffer_size=10M
sort_buffer_size=2M
table_cache=32
thread_cache_size=8
tmp_table_size=16M
