SolidDB for MySQL

Peter Gulutzan

Solid Information Technology ( www.soliddb.com/ www.solidtech.com ) has been around for a few years now. Although not too prominent, the company has become familiar with connoisseurs of the SQL world.

Recently, Solid became a redistributor of MySQL Enterprise and they are also a customer of MySQL. The company's "solidDB for MySQL" product (now called Solid) is technically a server combination of MySQL (MyISAM) and solidDB storage engine. Storage engine performs heavy lifting (such as IO file, index search, log file recovery, .). The server of MySQL is responsible for planning and contacts (such as SQL parsing, optimization, normal log file, .). Some interesting storage engines are MyISAM of MySQL, InnoDB of InnoBase Oy and recently published new mechanism 'Falcon' for MySQL 5.1 (http://dev.MySQL.com/doc/refman/5.1/ en / se-falcon.html).

Download

Solid has some fundamental differences with other storage engines: it is not available in regular MySQL distribution. If you want to use Solid, you must download from forge.MySQL.com or best on the company's website: http://dev.soliddb.com/download/. A registration form is available when you visit the website, but you can ignore it by clicking: "No thanks. Go to Downloads". At the time of writing, the website information is:

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

We can hope that Solid will quickly support all components on the MySQL Enterprise "Supported Platforms" list http://www.MySQL.com/support/supportedplatforms/enterprise.html.

But users of Solaris, HP-UX and Apple may be disappointed a bit.

Select the 64-bit Linux download and choose the location / home / pgulutzan / Desktop personal area, the download is in the form:

pgulutzan @ linux: ~ / Desktop> cd / home / pgulutzan / Desktop
pgulutzan @ linux: ~ / Desktop> tar zxvf
solidmysql-5.0.27-linux-x86_64-glibc23-0050.tar.gz
.
[danh sách các tập tin]
.
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 Password root:
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] Không thể mở thư mục chia sẻ 'udf_example.so'
(errno: 0 udf_example.so: không thể mở tập tin đối tượng tập tin: Không có tập tin hay directory)
070101 16:39:57 [Note] bin / mysqld: ready for connections.
Version: '5.0.27' socket: '/tmp/MySQL.sock' port: 0 Source
Distribution

When downloading it is not hampered by the settings already on MySQL, although to be safe I have turned off all mysqld programs that are open before proceeding.

First impression

Before using the Solid mechanism, review in MySQL with the MySQL client program:

MySQL> SELECT VERSION ();
+ ----------- +
| version () |
+ ----------- +
| 5.0.27 |
+ ----------- +
1 row trong đặt (0.00 sec)

Read the display on:

Only one usable version is 5.0.27. It will later be "SolidDB for MySQL 5.1", but now we cannot play with the new MySQL 5.1 components such as event, partition, or row-level log.

MySQL> SHOW ENGINES;
+ ------------ + --------- + -------------------------- --------------------------- +
| Engine | Support | Comment
|
+ ------------ + --------- + -------------------------- --------------------------- +
| solidDB | YES | Fully giao dịch đĩa-Based với máy ảnh
multiversion optimistic / pessimistic concurrency control |
.

Read the display on:

The storage engines that can be used are Solid, the 'at home' mechanism of MySQL and InnoDB. We can use two mechanisms 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 display on:

Some Solid-related variables you can use to adjust, but the default values ​​are pretty good.

Solid table first

MySQL>
CREATE DATABASE solidtest;
Query OK, 1 hàng hiệu (0.00 sec)
MySQL>
USE solidtest;
Database changed
MySQL>
CREATE TABLE t (s1 INT, s2 VARCHAR (1000)) ENGINE = SOLIDDB;
Query OK, 0 đối tượng hàng (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 hàng hiệu (0.01 sec)
MySQL>
SELECT * FROM t;
+ ------ + ------ +
| s1 | s2 |
+ ------ + ------ +
| 1 | a |
+ ------ + ------ +
1 row trong đặt (0.00 sec)

Read the display on:

The only other thing is to provide "ENGINE = SOLIDDB" information when creating the table (In fact, you do not need to declare this if you use Solid as the storage engine). SQL commands are no different. When declaring this table as Solid, MySQL will know that it must use Solid's storage engine when working with the table.

And now the first check transaction.

MySQL>
SET @@ autocommit = 0;
Query OK, 0 đối số hàng (0.00 sec)
MySQL>
START TRANSACTION;
Query OK, 0 đối số hàng (0.00 sec)
MySQL>
INSERT INTO t VALUES (2, 'b');
Query OK, 1 hàng hiệu (0.00 sec)
MySQL>
ROLLBACK;
Query OK, 0 đối số hàng (0.00 sec)
MySQL>
SELECT * FROM t WHERE s1 = 2;
Empty set (0.00 sec)

Read the display on:

The result of the code shows: Solid is the 'transaction' storage engine. This is different from MyISAM and more like InnoDB and Falcon.

Check time accuracy

Solid's website has a number of different commands on their own speed and test results like TM1, checking with many diagrams showing Solid faster than InnoDB:
http://www.solidtech.com/developers/CarrierGrade/TM1/SolidDB_TM1Benchmark.pdf

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

When performing such a test with the latest version of MySQL 5.1, declare "engine = myisam" and "engine = innodb" instead of "engine = solid", the result is (all times are 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

You can see Solid deserves to become a new member deserving of the MySQL hosting community. That's just a simple test. For a more accurate evaluation, we will have to do some other complicated tests, but the first impression of Solid is not bad.

The missing ones

There are some components that are not supported in Solid. Not all components are welcome, but most of them are present in some other MySQL storage engines. And many residents of the SQL community will feel sorry for their absence.

1. There is only one type of 'Latin upper case' collation.
   Only one type of word matching is latin1_general_cs. If any other column is not a set of Latin characters or no capitalization, you cannot find it.
2. Fulltext (must type full name)
   This is not a shortcoming of Solid, but the 'fulltext' index type is in MyISAM and has not been changed.

Summarizing and performing some extrapolations on the debugging section, from an e-mail number of Solid technical support, the following MySQL components are not available on Solid:

The index FULLTEXT
INSERT DELAYED
BACKUP
LOAD INDEX
OPTIMIZE
SAVEPOINT
Unsigned BIGINT
UNIQUE columns have more than one NULL value
Most types of comparisons.
All new MySQL 5.1 components.
Some platforms.

Some new components are not yet available on other storage devices

Solid has some things that you won't be able to find on any other MySQL mechanism. For example, the Foreign Keys are not available on the Falcon.

Foreign Keys

Solid has some downsides like InnoDB (such as being unable to refer to inline) and some other drawbacks (you can find complaints about them on Bug Tracker: http://dev.soliddb.com/bug) . But Solid checks the violation of the foreign-key foreign key at the end of the command, while InnoDB does not. Here is an example in which I use the UPDATE command of the primary key table. That may be the cause of breaking the foreign key in the command, but not at the end. When declaring: engine = solid, validated and passed. As for the innodb engine, the test failed.

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) tham references t1a (s1))
engine = solid;
Query OK, 0 rows affected (0.02 sec)
MySQL>
insert into t1a values ​​(1), (2);
Query OK, 2 rows đã hỗ trợ (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MySQL>
insert into t1b values ​​(1);
Query OK, 1 hàng hiệu (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 hàng hỗ trợ (0.05 sec)
MySQL>
tạo bảng t7b (s1 int, foreign key (s1) tham chiếu t7a (s1))
engine = innodb;
Query OK, 0 hàng hỗ trợ (0.05 sec)
MySQL>
insert into t7a values ​​(1), (2);
Query OK, 2 rows đã hỗ trợ (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MySQL>
insert into t7b values ​​(1);
Query OK, 1 hàng hiệu (0.01 sec)
MySQL>
update t7a set s1 = s1 - 1; / * This will cause an error! * /
ERROR 1451 (23000): Cannot delete or update a row parent: a foreign key
constraint fails

Strong recovery function

Solid can recover accurately and quickly after a problem. I have tried some common tests like 'kill -9' when the program is updating a large table. The process is not interrupted.

Choose Optimistic or Pessimistic

The optimistic and 'multi-versioning' plans (multi versions) are very popular, like in InnoDB / PostgreSQL / Oracle / Falcon or in Solid (default). But the pesimistic plan (shaping the traditional and familiar 'keys' for polymerization control) is only available in Solid (with the --soliddb_pessimistic option). This is great, the place for pessimistic in shop plans and the ease of migrating from DB2 or SQL Server. This is Solid's Big Unique Feature.

Maintain transactions

When writing this article I am having two MySQL client machines running transactions on the Solid table for a total of one million rows. The risk of conflict is always hidden when searching for multiple changes, updating a row. This work takes one to two hours and cannot be verified whether it is effective for long-term business activities or not.

There are some references, but it is best to download and test the software directly. It is quite easy to understand and completely free.