Overview:
I am always keen to try and test out new technologies. The idea of having a choice in storage engines is a great feature of MySQL. In MySQL 5.1, the MySQL engine API is available to write your own storage engine.
In the meantime, there are a couple of MySQL storage engines produced by 3rd party companies. SolidDB is one of them.
I wanted to install and then test the SolidDB engine against mysqlslap. I have heard and read a little about SolidDB in the past. However I was amazed that if you use SolidDB you don’t get any of the other storage engines. It is the SolidDB or the highway, 3rd party engines are not usable.
Where to get SolidDB:
To download SolidDB use this link http://dev.soliddb.com/en/download/download_files.php.
I used the MySQL SolidDB 5.0.51.
ftp://developers.soliddb.com/pub/5.0.51-0081/solidmysql-5.0.51-linux-i686-0081.tar.gz
Documentation and FAQ:
If in doubt always have the documentation handy.
http://dev.soliddb.com/doc
http://dev.soliddb.com/doc/doc_html/079/soliddb_storageengine.html
Plus the FAQ is always a good idea when using a product for the first time.
http://www.solidtech.com/en/products/relationaldatabasemanagementsoftware/enterprise/faqs.asp
Summary:
The results for the SolidDB engine were as good as the Innodb. Nothing widely variant from the numbers. I will run another bunch of tests on a larger instance and really push it to see if there are any upper limits.
Once that is out of the way, the next thing would be to throw sysbench and then DBT benchmark at it.
I have no idea as to the level of adoption out in the real world, however the limitation of only using the solidDB engine may be limiting that. I am happy to be proving wrong on this. Feel free to comment.
Setup:
Download MySQL SolidDB
tar -zxvf solidmysql-5.0.51-linux-i686-0081.tar.gz -C /var/lib
cd /var/lib/solidmysql-5.0.51-linux-i686-0081
cp /var/lib/solidmysql-5.0.51-linux-i686-0081/support-files/my-soliddb.cnf /etc/my.cnf
/var/lib/solidmysql-5.0.51-linux-i686-0081/bin/mysqld_safe --default-storage-engine=solidDB &
Sample my.cnf file
more support-files/my-soliddb.cnf
# Example MySQL config file.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/var) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
thread_stack = 64K
# These are solidDB parameters
#
default-storage-engine = soliddb
soliddb_cache_size=256M
soliddb_durability_level=3
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
Checking out the SolidDB system variables
./mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.51 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database solid
-> ;
Query OK, 1 row affected (0.01 sec)
mysql> use solid;
Database changed
mysql> show variables like '%solid%';
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| have_soliddb | YES |
| soliddb_cache_size | 268435456 |
| soliddb_durability_level | 3 |
| soliddb_logdir | |
| soliddb_backupdir | |
| soliddb_admin_command | |
| soliddb_checkpoint_deletelog | ON |
| soliddb_filespec | |
| soliddb_lock_wait_timeout | 30 |
| soliddb_db_block_size | 0 |
| soliddb_log_block_size | 0 |
| soliddb_backup_block_size | 0 |
| soliddb_checkpoint_interval | 50000 |
| soliddb_checkpoint_time | 300 |
| soliddb_io_threads | 5 |
| soliddb_lockhash_size | 1000000 |
| soliddb_pessimistic | ON |
| soliddb_write_threads | 1 |
| soliddb_extend_increment | 500 |
| soliddb_readahead | 4 |
+------------------------------+-----------+
20 rows in set (0.00 sec)
Test and Results:
mysqlslap --concurrency=1,25,50,100,200 --iterations=10 \
--number-int-cols=2 --number-char-cols=3 --auto-generate-sql \
--engine=soliddb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=1000 --user=root
Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.364 seconds
Minimum number of seconds to run all queries: 0.303 seconds
Maximum number of seconds to run all queries: 0.557 seconds
Number of clients running queries: 1
Average number of queries per client: 1000
Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.391 seconds
Minimum number of seconds to run all queries: 0.311 seconds
Maximum number of seconds to run all queries: 0.508 seconds
Number of clients running queries: 25
Average number of queries per client: 40
Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.418 seconds
Minimum number of seconds to run all queries: 0.392 seconds
Maximum number of seconds to run all queries: 0.461 seconds
Number of clients running queries: 50
Average number of queries per client: 20
Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.456 seconds
Minimum number of seconds to run all queries: 0.403 seconds
Maximum number of seconds to run all queries: 0.500 seconds
Number of clients running queries: 100
Average number of queries per client: 10
Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.633 seconds
Minimum number of seconds to run all queries: 0.599 seconds
Maximum number of seconds to run all queries: 0.683 seconds
Number of clients running queries: 200
Average number of queries per client: 5
mysqlslap --concurrency=1,25,50,100,200 --iterations=10 \
--number-int-cols=2 --number-char-cols=3 --auto-generate-sql \
--engine=soliddb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=1000 \
--user=root --csv=/tmp/mysqlslap_q1000_soliddb.csv
cat /tmp/mysqlslap_q1000_soliddb.csv
soliddb,mixed,0.371,0.247,0.451,1,1000
soliddb,mixed,0.381,0.337,0.462,25,40
soliddb,mixed,0.418,0.369,0.483,50,20
soliddb,mixed,0.523,0.440,0.639,100,10
soliddb,mixed,0.703,0.634,0.804,200,5
Increase the size of soliddb_cache_size from 250M to 750M
soliddb_cache_size=750M
mysqlslap --concurrency=1,25,50,100,200 --iterations=10 \
--number-int-cols=2 --number-char-cols=3 --auto-generate-sql \
--engine=soliddb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=1000 --user=root
Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.336 seconds
Minimum number of seconds to run all queries: 0.240 seconds
Maximum number of seconds to run all queries: 0.425 seconds
Number of clients running queries: 1
Average number of queries per client: 1000
Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.382 seconds
Minimum number of seconds to run all queries: 0.331 seconds
Maximum number of seconds to run all queries: 0.454 seconds
Number of clients running queries: 25
Average number of queries per client: 40
Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.420 seconds
Minimum number of seconds to run all queries: 0.364 seconds
Maximum number of seconds to run all queries: 0.455 seconds
Number of clients running queries: 50
Average number of queries per client: 20
Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.491 seconds
Minimum number of seconds to run all queries: 0.426 seconds
Maximum number of seconds to run all queries: 0.518 seconds
Number of clients running queries: 100
Average number of queries per client: 10
Benchmark
Running for engine soliddb
Average number of seconds to run all queries: 0.657 seconds
Minimum number of seconds to run all queries: 0.604 seconds
Maximum number of seconds to run all queries: 0.740 seconds
Number of clients running queries: 200
Average number of queries per client: 5
mysqlslap --concurrency=1,25,50,100,200 --iterations=10 \
--number-int-cols=2 --number-char-cols=3 --auto-generate-sql \
--engine=soliddb --auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed --number-of-queries=1000 \
--user=root --csv=/tmp/mysqlslap_q1000_soliddb_750M.csv
cat /tmp/mysqlslap_q1000_soliddb_750M.csv
soliddb,mixed,0.315,0.266,0.392,1,1000
soliddb,mixed,0.397,0.355,0.463,25,40
soliddb,mixed,0.420,0.364,0.475,50,20
soliddb,mixed,0.495,0.446,0.556,100,10
soliddb,mixed,0.701,0.621,0.810,200,5
http://dev.soliddb.com/doc/doc_html/079/soliddb_storageengine.html#solid-autoincrement
Lets test the autoincrement out with a write only test
mysqlslap --concurrency=1,25,50,100,200 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --engine=soliddb \
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write \
--number-of-queries=1000 --user=root \
--csv=/tmp/mysqlslap_q1000_soliddb_750M_write.csv
soliddb,write,0.395,0.305,0.468,1,1000
soliddb,write,0.398,0.335,0.460,25,40
soliddb,write,0.507,0.437,0.757,50,20
soliddb,write,0.522,0.453,0.594,100,10
soliddb,write,0.754,0.682,0.808,200,5
Same again with 10,000 queries
mysql> set global max_connections = 500;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
mysqlslap --concurrency=1,25,50,100,200 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --engine=soliddb \
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write \
--number-of-queries=10000 --user=root --csv=/tmp/mysqlslap_q10000_soliddb_750M_write.csv
soliddb,write,5.207,4.382,6.535,1,10000
soliddb,write,4.686,4.002,5.141,25,400
soliddb,write,4.792,4.101,5.425,50,200
soliddb,write,5.633,4.472,7.151,100,100
soliddb,write,5.784,4.630,6.733,200,50
And back to a mixed load for 10,000 queries.
mysqlslap --concurrency=1,25,50,100,200 --iterations=10 --number-int-cols=2 \
--number-char-cols=3 --auto-generate-sql --engine=soliddb \
--auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed \
--number-of-queries=10000 --user=root --csv=/tmp/mysqlslap_q10000_soliddb_750M.csv
cat /tmp/mysqlslap_q10000_soliddb_750M.csv
soliddb,mixed,3.784,3.314,4.277,1,10000
soliddb,mixed,3.791,3.624,4.310,25,400
soliddb,mixed,4.043,3.516,4.857,50,200
soliddb,mixed,4.280,3.845,5.159,100,100
soliddb,mixed,4.464,3.969,5.602,200,50
vmstat 1 10
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 1 0 1222700 39820 315480 0 0 16 4566 374 4444 6 3 87 4
1 1 0 1222700 39824 315476 0 0 0 33840 2347 22313 23 11 6 59
19 2 0 1221448 39828 315472 0 0 0 13968 956 12484 55 9 9 26
9 2 0 1221324 39828 315472 0 0 0 7608 568 14670 59 41 0 0
1 1 0 1221324 39832 315468 0 0 0 6012 491 15793 61 20 0 18
7 1 0 1221460 39832 315468 0 0 0 8044 626 14697 60 20 0 20
18 2 0 1221324 39836 315464 0 0 0 13196 923 15057 77 10 6 6
6 2 0 1221200 39836 315464 0 0 0 3088 257 6661 21 5 0 74
1 0 0 1221200 39840 315460 0 0 0 5924 476 15433 70 20 0 9
1 1 0 1221200 39840 315460 0 0 0 7216 562 14654 62 24 0 13
DBA Dojo 