Archive for the ‘MySQL’ Category

MySQL 5.6.25 sql-bench results on Linux 4.2.3 kernel and SSD RAID 1

Friday, October 23rd, 2015

This post provides fresh sql-bench results for the test that was performed a while back.

MySQL sql-bench results

Tuesday, June 16th, 2009

UPDATE: There was a newer test performed, with MySQL 5.6.25 on Linux 4.2.3 64bit, on almost the same hardware. You can see it here.

This is a follow-up article to the MySQL Super Smack benchmark results. Results from sql-bench benchmark suite can easily pinpoint some of the potential system bottlenecks. I find it especially useful for discovering filesystem performance or – better – slowness.

Total execution time is: 562 seconds

# run-all-tests
alter-table: Total time: 8 wallclock secs ( 0.02 usr 0.01 sys + 0.00 cusr 0.00 csys = 0.03 CPU)
ATIS: Total time: 2 wallclock secs ( 1.20 usr 0.09 sys + 0.00 cusr 0.00 csys = 1.29 CPU)
big-tables: Total time: 5 wallclock secs ( 2.45 usr 0.08 sys + 0.00 cusr 0.00 csys = 2.53 CPU)
connect: Total time: 50 wallclock secs (12.74 usr 4.50 sys + 0.00 cusr 0.00 csys = 17.24 CPU)
create: Total time: 31 wallclock secs ( 1.20 usr 0.44 sys + 0.00 cusr 0.00 csys = 1.64 CPU)
insert: Total time: 397 wallclock secs (97.95 usr 13.61 sys + 0.00 cusr 0.00 csys = 111.56 CPU)
select: Total time: 44 wallclock secs ( 8.71 usr 0.88 sys + 0.00 cusr 0.00 csys = 9.59 CPU)
transactions: Test skipped because the database doesn’t support transactions
wisconsin: Total time: 3 wallclock secs ( 0.91 usr 0.23 sys + 0.00 cusr 0.00 csys = 1.14 CPU)
TOTALS 562.00 123.77 19.82 143.59 3425950

System specification can be found here.

ReiserFS vs others
In the age of Linux kernel 2.4.x we used ReiserFS v3 as the filesystem of choice. With the available options of ReiserFS (journal, performance), ext2 (stable but slow) and ext3 (probably stable, but not so speedy as ReiserFS) the choice was obvious. I skipped few years then and this year again tried using ReiserFS with linux but it turned out to be even slower than ext2 was in the old days. Googling around for an answer gave some hints that ReiserFS has an issue with someting called BIG_KERNEL_LOCK on 2.6 kernels. I didn’t really investigate further, but went down the ext3 way.

Comments on test-create
If test-create takes much more time than, say, 30-60 seconds, then you definitely have a problem with filesystem write performance. On HP DL360 and DL380 class of servers this correlates with the presence and activation of BBWC (Battery-Backed Write Cache enabler kit). Without BBWC and hence without write cache enabled, this test took more than 10 minutes to complete. Thus, if you are purchasing some new HP servers, be sure that you also order BBWCs.

Question about test-insert
Looking at the test times, this test-insert result is really standing out. Again, I do not have any other data to compare it to, but somewhere deep down in my memory I seem to remember that the total time for all the tests was around 300 seconds. This obviously means that this test-insert result is the bad guy here. Can someone comment on this result, or paste in the comments his own? Thanks.

If you have any questions, recommendations or benchmark results to compare, do not hesitate to leave a comment.

UPDATE1: 2014-09-09
I forgot to mention explicitly that this system is running 32bit version of Slackware

UPDATE2: 2014-09-09
Fortunately this system is still up and running. During these five years only storage has been expanded with 300GB 10K SAS drives in RAID 1 configuration. Software was upgraded regularly and is currently on MySQL 5.5.39 and pending 5.6.20 upgrade. I retested the test-create today and the result was 85 wall-clock seconds. This is almost 3x worse as initially. The server is currently lightly loaded.