solidDB for MySQL

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:

solidmysql-5.0.27.0050
released on December 19, 2006
Windows
Windows binary
Windows source
solidDB for MySQL User Guide for
Windows
Release Notes
Linux 32-bit
Linux 32-bit binary
Linux source
solidDB for MySQL User Guide for
Linux
Release Notes
Linux 64-bit
Linux 64-bit binary
Linux source
solidDB for MySQL User Guide for
Linux
Release Notes

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:

FULLTEXT indexes
INSERT DELAYED
BACKUP
LOAD INDEX
OPTIMIZE
SAVEPOINT
Unsigned BIGINT
UNIQUE columns with more than one NULL value
Most collations
All new MySQL 5.1 features
Some platforms

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.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles