Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 7, 2007

solidDB for MySQL

By DatabaseJournal.com Staff

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


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- released on December 19, 2006
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
[list of files]
 cd solidmysql-5.0.27-linux-x86_64-glibc23-0050 
 su root Password:
 # bin/mysqld 
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

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.

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

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

| 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

Query OK, 1 row affected (0.00 sec)
 USE solidtest;
Database changed
Query OK, 0 rows affected (0.01 sec)
 CREATE INDEX i ON t (s1);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
Query OK, 1 row affected (0.01 sec)
| 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.

 SET @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
 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.

  WHILE v <
 10000 DO
    INSERT INTO t VALUES (v,'cde');
    SET v = v + 1;
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
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.

 create table t1a (s1 int, primary key(s1)) engine=solid;
Query OK, 0 rows affected (0.02 sec)
 create table t1b (s1 int, foreign key (s1) references t1a(s1))
Query OK, 0 rows affected (0.02 sec)
 insert into t1a values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 insert into t1b values (1);
Query OK, 1 row affected (0.00 sec)
 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
 create table t7a (s1 int, primary key(s1)) engine=innodb;
Query OK, 0 rows affected (0.05 sec)
 create table t7b (s1 int, foreign key (s1) references t7a(s1))
Query OK, 0 rows affected (0.05 sec)
 insert into t7a values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 insert into t7b values (1);
Query OK, 1 row affected (0.01 sec)
 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


Test Drive of Solid


About the author

Peter Gulutzan works for MySQL AB as senior software architect.

He lives in Edmonton, Alberta.

MySQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM