Soliddb for Mysql

Use this guide to understand soliddb for mysql and the factors that affect it. The information is organized to make the topic easier to apply in practice.

Peter Gulutzan

How Soliddb for Mysql Works

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

• 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.
• 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 app directly. It is quite easy to understand and completely free.