solidDB for MySQL
February 7, 2007
Solid Information Technology (www.soliddb.com / www.solidtech.com) has been around for several years, not in the limelight, but familiar to cognoscenti of the SQL world. Trudy Pelzer and I even visited their office in Helsinki a few years ago; they were all friendly and competent. (If the place-name "Helsinki" rings a bell ... yes, that's also the headquarters of InnoDB, and quite close to the residence of MySQL's CTO.)
Recently Solid became a reseller of MySQL Enterprise and they're a MySQL partner (see
http://solutions.MySQL.com/solutions/partner.php?partner=1407). Their product "solidDB for MySQL" -- which henceforth I'll just call "Solid" -- is technically a combination of MySQL's server, MyISAM, and the solidDB "storage engine". A storage engine does the heavy lifting (file IO, index lookup, recovery log, etc.) while MySQL's server does the plans and contacts (SQL parsing, optimizing, general log, etc.). Some storage engines are MySQL's own MyISAM, InnoBase Oy's InnoDB, and the recently-announced "Falcon" for MySQL 5.1 (see http://dev.MySQL.com/doc/refman/5.1/en/se-falcon.html).
Solid differs from other storage engines in a major sense: it doesn't come with the regular MySQL distribution. If you want Solid, you must download it from forge.MySQL.com, or preferably from their site, http://dev.soliddb.com/download/ There's a registration form if you click from there, but you can skip it by clicking "No thanks. Go to Downloads". At the time I'm writing this, the download page says this:
released on December 19, 2006
We can all hope that Solid will soon be available for everything on the MySQL Enterprise "Supported Platforms" list http://www.MySQL.com/support/supportedplatforms/enterprise.html.
Until then, Solaris / HP-UX / Apple users will experience initial disappointment.
I picked the Linux 64-bit download, and said I wanted it on my personal area, which happens to be called /home/pgulutzan/Desktop. I had no trouble after that. This is what my console looked like, showing my instructions and the responses:
pgulutzan@linux:~/Desktop> cd /home/pgulutzan/Desktop pgulutzan@linux:~/Desktop> tar zxvf solidmysql-5.0.27-linux-x86_64-glibc23-0050.tar.gz ... [list of files] ... pgulutzan@linux:~/Desktop> cd solidmysql-5.0.27-linux-x86_64-glibc23-0050 pgulutzan@linux: ~/Desktop/solidmysql-5.0.27-linux-x86_64-glibc23-0050> su root Password: linux:/home/pgulutzan/Desktop/solidmysql-5.0.27-linux-x86_64-glibc23-0050 # bin/mysqld --user=root --skip-networking --default-storage-engine=solid solidDB for MySQL 5.0.27 - 06.00.0050 (Linux 2.6.13 AMD64 64bit MT) Copyright (C) Solid Information Technology Ltd 1993-2006 Database started. 070101 16:39:57 InnoDB: Started; log sequence number 0 12719128 070101 16:39:57 [ERROR] Can't open shared library 'udf_example.so' (errno: 0 udf_example.so: cannot open shared object file: No such file or directory) 070101 16:39:57 [Note] bin/mysqld: ready for connections. Version: '5.0.27' socket: '/tmp/MySQL.sock' port: 0 Source Distribution
This doesn't interfere with my existing installations of MySQL, although to be safe I shut down other mysqld instances before I really got going.
The First Impression
Before using the Solid engine, I just looked at MySQL, with the MySQL client program.
MySQL> SELECT VERSION(); +-----------+ | version() | +-----------+ | 5.0.27 | +-----------+ 1 row in set (0.00 sec)
Read the screen display above:
The only version available is 5.0.27. No doubt, there will be a "SolidDB for MySQL 5.1" eventually, but for now, I'll forget about playing with the new MySQL 5.1 features like events, partitions or row-level logs.
MySQL> SHOW ENGINES; +------------+---------+-----------------------------------------------------+ | Engine | Support | Comment | +------------+---------+-----------------------------------------------------+ | solidDB | YES | Fully transactional disk-based engine with multiversion optimistic/pessimistic concurrency control | ...
Read the screen display above:
The available storage engines include Solid, and all MySQL's "in house" engines, and InnoDB. So, I will be able to play with both those storage engines together at the same time.
MySQL> SHOW VARIABLES LIKE 'solid%'; +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | soliddb_cache_size | 67108864 | | 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 | 8192 | | soliddb_log_block_size | 16384 | | soliddb_backup_block_size | 65536 | | soliddb_checkpoint_interval | 50000 | | soliddb_checkpoint_time | 0 | | soliddb_io_threads | 5 | | soliddb_lockhash_size | 1000000 | | soliddb_pessimistic | ON | | soliddb_write_threads | 1 | | soliddb_extend_increment | 500 | | soliddb_readahead | 4 | +------------------------------+----------+ 19 rows in set (0.00 sec)
Read the screen display above:
There are a few Solid-related variables that I could use for tuning and fine adjustments. I just left them all as is. The defaults worked out pretty well.
My First Solid Table
MySQL> CREATE DATABASE solidtest; Query OK, 1 row affected (0.00 sec) MySQL> USE solidtest; Database changed MySQL> CREATE TABLE t (s1 INT,s2 VARCHAR(1000)) ENGINE=SOLIDDB; Query OK, 0 rows affected (0.01 sec) MySQL> CREATE INDEX i ON t (s1); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 MySQL> INSERT INTO t VALUES (1,'a'); Query OK, 1 row affected (0.01 sec) MySQL> SELECT * FROM t; +------+------+ | s1 | s2 | +------+------+ | 1 | a | +------+------+ 1 row in set (0.00 sec)
Read the screen display above.
The only thing that's different is that I said "ENGINE=SOLIDDB" when I created the table. (Actually, I didn't even need to do that, since I said Solid is the default storage engine when I started mysqld.) There's nothing different about the other SQL statements. Once I've said that the table is Solid, MySQL will know forever that it has to use Solid's storage engine whenever it works on that table.
And now -- tarara -- my first transactional test.
MySQL> SET @@autocommit=0; Query OK, 0 rows affected (0.00 sec) MySQL> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) MySQL> INSERT INTO t VALUES (2,'b'); Query OK, 1 row affected (0.00 sec) MySQL> ROLLBACK; Query OK, 0 rows affected (0.00 sec) MySQL> SELECT * FROM t WHERE s1 = 2; Empty set (0.00 sec)
Read the screen display above.
What it shows is: Solid is a "transactional" storage engine. That distinguishes it from MyISAM, and means it's more like InnoDB and Falcon.
Unfancy Timing Test
Solid's own site has various statements about speed and results of their own tests, including a TM1 test with multiple charts to show Solid is faster than InnoDB:
I don't pay much attention to that because I know the comparisons are against a MySQL version which had a performance glitch. Anyway, my first attempt at getting the feel of a product will always be something extremely simple, so that I won't fear that I'm missing a host of subtleties.
DELIMITER // CREATE PROCEDURE p () BEGIN DECLARE v INT DEFAULT 0; WHILE v < 10000 DO INSERT INTO t VALUES (v,'cde'); SET v = v + 1; END WHILE; END// DELIMITER ; CALL p(); -- Time this SELECT SUM(s1) FROM t; -- Time this UPDATE t SET s2=CONCAT(s2,s2); -- Time this
I did the same thing with the latest MySQL 5.1, with "engine=myisam" and "engine=innodb" instead of "engine=solid". My Results were (with all times in seconds):
MyISAM InnoDB Solid INSERT 0.78 3.92 9.01 UPDATE 0.01 0.03 0.20 DELETE 0.13 1.47 0.94
Looking at that, I know that I can welcome Solid as a perfectly respectable new member of the MySQL storage engine community. To get a more accurate idea I'd have to run non-simple tests, and I didn't, but I'm happy with this first impression.
What's Missing That Some Other Storage Engine Has
In my wanderings, I inevitably stumbled across features that Solid doesn't (yet) support. For most people, none of these will (yet) be showstoppers. However, they are features that I do see in at least one other MySQL storage engine, and I know that some folks will mourn their absence.
Summing up, and doing a bit of extrapolating from the bug progress notes, and some emails from Solid's on-the-ball tech support, I now take it that these MySQL features will not be supported initially:
What's Present That Some Other Storage Engine Lacks
On the other hand, and it's a big other hand (which I'll use to clap with), Solid has some things that you won't find with most of MySQL's other engines. I particularly like to see the support for Foreign Keys, which currently isn't in MySQL's Falcon. (It is in the pipe, but fair people will only compare current with current.)
MySQL> create table t1a (s1 int, primary key(s1)) engine=solid; Query OK, 0 rows affected (0.02 sec) MySQL> create table t1b (s1 int, foreign key (s1) references t1a(s1)) engine=solid; Query OK, 0 rows affected (0.02 sec) MySQL> insert into t1a values (1),(2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 MySQL> insert into t1b values (1); Query OK, 1 row affected (0.00 sec) MySQL> update t1a set s1 = s1 - 1; /* This won't cause an error! */ Query OK, 2 rows affected (0.04 sec) Rows matched: 2 Changed: 2 Warnings: 0 MySQL> create table t7a (s1 int, primary key(s1)) engine=innodb; Query OK, 0 rows affected (0.05 sec) MySQL> create table t7b (s1 int, foreign key (s1) references t7a(s1)) engine=innodb; Query OK, 0 rows affected (0.05 sec) MySQL> insert into t7a values (1),(2); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 MySQL> insert into t7b values (1); Query OK, 1 row affected (0.01 sec) MySQL> update t7a set s1 = s1 - 1; /* This will cause an error! */ ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Robust Crash Recovery.
Choice of Optimistic or Pessimistic.
--soliddb_pessimistic option). This is great, there's a place for pessimistic plans in some shops, and it's easier to migrate from DB2 or SQL Server. I regard this as Solid's Big Unique Feature.
So Keep On Trucking
As I write this, I have two MySQL clients running transactions on Solid tables with a total of 1 million rows, hitting them continuously with potentially conflicting transactions that search many rows and change or update one row. This job has been running for two hours. I haven't proved that it will run your tough business, I know. However, I think I've shown that it's time to give it a try.
There's documentation too, but I recommend doing as I did: download and experiment. It's easy, and it's "free" (in the usual GPL sense of the word).
Some Old Articles
Finnish Revenge as mySQL Gets Solid Support
Test Drive of Solid
About the author
Peter Gulutzan works for MySQL AB as senior software architect.
He lives in Edmonton, Alberta.