solidDB for MySQL

February 7, 2007

by Peter Gulutzan

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).

DOWNLOADING

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:

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:

http://www.solidtech.com/developers/CarrierGrade/TM1/SolidDB_TM1Benchmark.pdf

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.

• Any collation other than "latin1 case sensitive".
  The only collation is latin1_general_cs. If I have a column with a non-Latin character set or a non-case-sensitive collation, I can't index it.

• Fulltext.
  It's not Solid's fault, but "fulltext" indexes will only be available for MyISAM, as before.

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.)

Foreign Keys.
Solid has some defects that are the same as InnoDB's (for example one can't specify REFERENCES inline), and Solid has some defects that aren't in InnoDB (you can find complaints about them in Solid's Bug Tracker, http://dev.soliddb.com/bug). But Solid checks foreign-key violations at "end of statement" and InnoDB doesn't. Here's an example where I do an UPDATE on a primary-key table which would cause a foreign-key violation during the statement, but not at the end. With engine=solid, the test passes. With engine=innodb, the test fails.

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.
It recovers well from crashes. I tried the usual tests, like 'kill -9 <mysqld-solid job number>' while the engine is updating a big table. No corruption.

Choice of Optimistic or Pessimistic.
Optimistic and "multi-versioning" plans are common, as in InnoDB / PostgreSQL / Oracle / Falcon, and as in Solid (default). But pessimistic plans -- featuring the familiar and traditional "locks" for concurrency control -- are available only in Solid (with the

--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

http://blogs.zdnet.com/open-source/?p=620

Test Drive of Solid

http://www.mysqlperformanceblog.com/2006/09/22/test-drive-of-solid/

About the author

Peter Gulutzan works for MySQL AB as senior software architect.

He lives in Edmonton, Alberta.