solidDB for MySQL is a turbo-charged version of MySQL database that leverages solidDB Storage Engine to support heavy transaction volumes and large numbers of concurrent users. It is a complete relational database management system (RDBMS) that combines MySQL Server, solidDB Storage Engine and MyISAM storage engine.

solidDB for MySQL is based on Solid's proven transactional product Solid EmbeddedEngine™, which is a fully-featured relational database that is being used in products from companies including HP, Siemens, Nortel and NEC. solidDB for MySQL will supports mission critical applications that require a high level of reliability and large transaction volumes. solidDB for MySQL is licensed under GNU GPL.

solidDB Storage Engine for MySQL leverages solidDB Storage Engine, which is a multi-threaded, transactional storage engine for MySQL Server. It is designed for mission-critical implementations that require a robust, transactional database able to fully leverage multi-processor environments to support heavy transaction volumes and a large number of concurrent users. solidDB Storage Engine for MySQL supports full ACID compliance with configurable transaction isolation levels, both optimistic and pessimistic concurrency control, Multi-Version Concurrency Control (MVCC) with non-blocking reads and writes, row-level locking, and support for foreign keys and referential integrity.

Contact information for Solid:

Web site: http://www.solidtech.com

Sales inquiries: <sales@solidtech.com>

Technical support: <techsupp@solidtech.com>

Marketing: <solidwebex@solidtech.com>

Offices

Americas

Asia Pacific

EMEA

This chapter covers solidDB for MySQL installation for both the Windows and Linux platforms.

This chapter explains how to compile solidDB for MySQL from source for both the Windows and Linux platforms.

The solidDB Storage Engine for MySQL is enabled by default. If you do not want to use solidDB tables, you can add the skip-soliddb option to your MySQL option file.

This section contains representative examples of solidDB configuration parameters. Refer to Section 1.7, "solidDB for MySQL Startup Options and System Variables" for additional information about solidDB-related configuration parameters.

To set up the solidDB database files, use the soliddb_filespec option in the [mysqld] section of the my.cnf option file. On Windows, you can use the my.ini file instead. The value of soliddb_filespec should be a list of one or more database file specifications. If you name more than one data file, separate them by comma (",") characters:

soliddb_filespec=database_file[,database_file2]...

For example,

[mysqld]
soliddb_filespec=soliddb1 10M
    

This setting configures a single 10MB database file named soliddb1. No location for the database file is given. By default, solidDB creates it in the MySQL data directory. The full syntax for a database file specification includes the filename and the file-size:

file_name file_size

solidDB creates tablespace files in the MySQL data directory by default. To specify a location explicitly, use the soliddb_filespec option with a path. For example, to use two files named soliddb1 and soliddb2 but create them in the /soliddb directory, configure solidDB like this:

[mysqld]
soliddb_filespec=/soliddb/soliddb1 10M,/soliddb/soliddb2 20M
  

For more information on file management, see Section 1.26, "solidDB File Space Management and Disk I/O".

If you want to tune other mysqld and solidDB parameters, the following values are typical and would suit most users:

[mysqld]
 skip-external-locking
 max_connections=1024
 read_buffer_size=1M
 sort_buffer_size=1M
 #
 # Set key_buffer to 5 - 50% of your RAM depending on how much
 # you use MyISAM tables, but keep key_buffer_size + solidDB
 # cache size < 80% of your RAM
 key_buffer_size=value
 #
 # Set the cache size to 70-80% of your ram
 #
 #
 soliddb_cache_size=value
 #
 # Set durability to strict
 #
 set-variable = soliddb_durability_level=3
 character-set-server=latin1
 collation-server=latin1_general_cs
 
 [client]
 default-character-set=latin1

This chapter explains basic tasks needed to start solidDB for MySQL.

mysqld_safe --mysqld=<mysql-server-name> --default-storage-engine=solidDB

This section describes the command-line options and system variables for solidDB for MySQL. System variables have two possible states: they are either true or false. System variables can be enabled at server startup by naming them, or disabled by using a skip- prefix. For example, to enable or disable deleting solidDB transaction log files after each successful checkpoint, you can use --soliddb_checkpoint_deletelog or --skip-soliddb_checkpoint_deletelog on the command line, or soliddb_checkpoint_deletelog or skip-soliddb_checkpoint_deleletelog in an option file.

System variables that take a numeric value can be specified as --var_name=value on the command line or as var_name=value in option files.

Data dictionary is a file that defines the basic organization of a database. A data dictionary contains a list of all files in the database, the number of records in each file, and the names and types of each field.

Database changed
    

Query OK, 0 rows affected (0.73 sec)
    

+-------+-------------------------+------------+--------------+
| ID    | TABLE_NAME              | TABLE_TYPE | TABLE_SCHEMA |
+-------+-------------------------+------------+--------------+
|   100 | SYS_TABLES              | BASE TABLE | _SYSTEM      |
|   200 | SYS_COLUMNS             | BASE TABLE | _SYSTEM      |
|   400 | SYS_UROLE               | BASE TABLE | _SYSTEM      |
|   500 | SYS_RELAUTH             | BASE TABLE | _SYSTEM      |
|   600 | SYS_ATTAUTH             | BASE TABLE | _SYSTEM      |
|   700 | SYS_VIEWS               | BASE TABLE | _SYSTEM      |
|   800 | SYS_KEYPARTS            | BASE TABLE | _SYSTEM      |
|   900 | SYS_KEYS                | BASE TABLE | _SYSTEM      |
|  1000 | SYS_CARDINAL            | BASE TABLE | _SYSTEM      |
|  1100 | SYS_INFO                | BASE TABLE | _SYSTEM      |
|  1300 | SYS_SYNONYM             | BASE TABLE | _SYSTEM      |
|  8000 | SYS_USERS               | BASE TABLE | _SYSTEM      |
|  8002 | SQL_LANGUAGES           | BASE TABLE | _SYSTEM      |
|  8004 | SYS_TYPES               | BASE TABLE | _SYSTEM      |
|  8006 | SYS_BLOBS               | BASE TABLE | _SYSTEM      |
|  8010 | SYS_FORKEYS             | BASE TABLE | _SYSTEM      |
|  8013 | SYS_FORKEYPARTS         | BASE TABLE | _SYSTEM      |
|  8015 | SYS_CHECKSTRINGS        | BASE TABLE | _SYSTEM      |
|  8017 | SYS_PROCEDURES          | BASE TABLE | _SYSTEM      |
|  8020 | SYS_TRIGGERS            | BASE TABLE | _SYSTEM      |
|  8024 | SYS_TABLEMODES          | BASE TABLE | _SYSTEM      |
|  8026 | SYS_EVENTS              | BASE TABLE | _SYSTEM      |
|  8029 | SYS_CATALOGS            | BASE TABLE | _SYSTEM      |
|  8032 | SYS_SCHEMAS             | BASE TABLE | _SYSTEM      |
|  8035 | SYS_SEQUENCES           | BASE TABLE | _SYSTEM      |
|  8038 | SYS_PROPERTIES          | BASE TABLE | _SYSTEM      |
|  8040 | SYS_HOTSTANDBY          | BASE TABLE | _SYSTEM      |
|  8043 | SYS_COLUMNS_AUX         | BASE TABLE | _SYSTEM      |
| 10000 | SOLIDDB_SYS_TABLES      | BASE TABLE | soliddb      |
| 10002 | SOLIDDB_SYS_COLUMNS     | BASE TABLE | soliddb      |
| 10004 | SOLIDDB_SYS_KEYS        | BASE TABLE | soliddb      |
| 10006 | SOLIDDB_SYS_KEYPARTS    | BASE TABLE | soliddb      |
| 10008 | SOLIDDB_SYS_FORKEYS     | BASE TABLE | soliddb      |
| 10010 | SOLIDDB_SYS_FORKEYPARTS | BASE TABLE | soliddb      |
| 10012 | SOLIDDB_SYS_SCHEMAS     | BASE TABLE | soliddb      |
| 10014 | SOLIDDB_SYS_SEQUENCES   | BASE TABLE | soliddb      |
| 10017 | SOLIDDB_SYS_BLOBS       | BASE TABLE | soliddb      |
| 10019 | SOLIDDB_SYS_CARDINAL    | BASE TABLE | soliddb      |
| 10021 | SOLIDDB_SYS_INFO        | BASE TABLE | soliddb      |
| 10023 | SOLIDDB_SYS_TABLEMODES  | BASE TABLE | soliddb      |
| 10025 | SOLIDDB_ADMIN_COMMANDS  | BASE TABLE | soliddb      |
+-------+-------------------------+------------+--------------+
44 rows in set (0.02 sec)
    

To create a solidDB table, specify the ENGINE = solidDB option in the CREATE TABLE statement:

CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=solidDB;

The statement mentioned previously creates a table and an index on column a in the solidDB database that consists of the data files that you specified in the my.cnf (or my.ini for Windows) file. In addition, solidDB for MySQL creates a customers.frm file in the test directory under the MySQL database directory. Internally, solidDB for MySQL adds an entry for the table to its own data dictionary. The entry includes the database name. For example, if you have a database named test, and you create a table called customers in it, the entry is for 'test/customers'. This means you can create a table of the same name (customers) in another database, and the table names do not collide inside solidDB for MySQL.

You can specify the table type, optimistic or pessimistic, when you create the table. For more information on table modes, see Section 1.19, "Concurrency Control". To create an optimistic table, you can use, for example, the command below:

CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=solidDB COMMENT='MODE=OPTIMISTIC';

To create a pessimistic table, you can use, for example, the command below:

CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=solidDB COMMENT='MODE=PESSIMISTIC';

If you specify no table type, the default value OPTIMISTIC is used.

If you want all your (non-system) tables to be created as solidDB tables, you can simply add the line default-storage-engine=soliddb to the [mysqld] section of your server option file.

You can check the table type for an existing table by two methods:

By default, solidDB for MySQL 5.0 uses the latin1 character set and the latin1_general_cs collation. Additionally, solidDB for MySQL 5.0 supports the UCS2 character set and the ucs2_bin collation for storing Unicode data.

There are a number of known issues with solidDB tables in this release. Solid is already working to address the most serious of these. Known issues:

Some solidDB system variables are dynamic and can be set at runtime using SET GLOBAL. You can also obtain their values using SELECT. See Using System Variables in MySQL documentation for more information.

The following chapters describe the dynamic solidDB system variables. The options that can be set with the SET statement are also included. Section SET Syntax discusses these options.

Variables that have a type of 'string' take a string value. Variables that have a type of 'numeric' take a numeric value.

Consider, for example, a situation where your database has reached the maximum size specified by soliddb_filespec. You can add a new database file dynamically using a MySQL client as follows:

shell> mysql
shell> SET GLOBAL soliddb_filespec='solid5.db 1G';
shell> exit;

You can only add one database file at a time. If you need to add more than one database file, you must set soliddb_filespec several times.

In relational databases, it is common for records in one relation to reference records in the same or other relations. The requirement that the referenced records must exist is called referential integrity. References can be made to link information together and it is an essential part of database normalization. One important type of referential integrity is the foreign key constraint.

A foreign key is a referential constraint between two tables in the database. The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referenced table must form a primary key or unique key. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that do not exist in the referenced table. Multiple rows in the referencing table may refer to the same row in the referenced table.

The referencing and referenced table may be the same table, that is, the foreign key refers back to the same table. Such a foreign key is known in SQL:2003 as a self-referencing or recursive foreign key. A table may have multiple foreign keys, and each foreign key may have a different referenced table. Each foreign key is independently forced by the database system. Therefore, cascading relationships between tables can be documented by using foreign keys.

Foreign keys are defined in the ANSI SQL Standard, by using a FOREIGN KEY constraint. The syntax to add such a constraint to an existing table is defined in SQL:2003. Leaving out the column list in the REFERENCES clause means that the foreign key references the primary key of the referenced table.

solidDB for MySQL supports foreign key constraints. solidDB uses the MySQL parser for foreign key definitions. See FOREIGN KEY Constraints in the MySQL 5.0 Reference Manual for more information on foreign keys and Using Foreign Keys in the MySQL 5.0 Reference Manual for more information on how to use foreign keys. Foreign key syntax can be found in CREATE TABLE Syntax and in ALTER TABLE Syntax.

solidDB for MySQL requires that both the referencing table and referenced tables are solidDB tables. Additionally, solidDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. The index on the foreign key is automatically created. Corresponding columns in the foreign key and the referenced key must have similar internal data types inside solidDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

The default referential action is ON UPDATE RESTRICT and ON DELETE RESTRICT unless the ON UPDATE and ON DELETE attributes are separately given. Here is an example of creating tables using foreign keys:

CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=SOLIDDB;

CREATE TABLE t2 (id INT PRIMARY KEY, t1_id INT NOT NULL, 
FOREIGN KEY fkey(t1_id) REFERENCES t1(id) ON DELETE CASCADE)
ENGINE=SOLIDDB;

If you attempt to insert a row to table t2 with a value that does not exist in the parent table t1, you get an error message:

mysql> insert into t2 values (0, 0);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (CONSTRAINT 'fkey' FOREIGN KEY ('t1_id') REFERENCES 
't1' ('id') ON DELETE CASCADE ON UPDATE RESTRICT)

You must define tables in such an order that all referenced tables are created before the tables referencing them. If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error number 155, then the table creation has failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to error number 155, then the table alteration has failed because a foreign key constraint was incorrectly formed for the altered table.

mysql> CREATE TABLE t3 (id INT PRIMARY KEY, t1_id INT, INDEX par_ind
(t1_id), FOREIGN KEY (t1_id) REFERENCES t4(id) ON DELETE CASCADE)
ENGINE=SOLIDDB;

ERROR 1005 (HY000): Can't create table './test/t3.frm' (errno: 155)

You can also see an error message in the server log:

SOLID Table Error 13011: Table t4 does not exist

See below for a more complex example, in which a product_order table has foreign keys for two other tables. One foreign key references a two-column index in the product table. The other references a single-column index in the customer table:

create table customer(customer_id int not null, customer_name
varchar(80), primary key(customer_id)) engine=soliddb;

create table product(product_id int not null, product_price int,
primary key(product_id)) engine=soliddb;

create table product_order(order_id int not null, customer_id int not
null, product_id int not null, number_of_products int,  primary
key(order_id), foreign key (customer_id) references
customer(customer_id), foreign key (product_id) references
product(product_id)) engine=soliddb;

Additionally, the foreign key can reference the same table:

create table t1(a int not null, b int not null, d int, primary key(a),
foreign key (b) references t1(a) on update no action on delete no
action) engine=soliddb;

create table t6(a int not null, b int not null, primary key(a),
foreign key ger (b) references t1(a) on update set default on delete
set default) engine=soliddb;

ERROR 1005 (HY000): Can't create table './test/t6.frm' (errno: 142)
                  

070313  9:30:13 [ERROR] This version of MySQL/solidDB does not support
SET_DEFAULT option on referential actions.
              

Normally, when a transaction is committed, the database server writes data to two locations: the database file, and the transaction log file. However, the data are not necessarily written to those two locations at the same time. When a transaction is committed, the server writes the data to the transaction log file immediately, that is, as soon as the server commits the transaction. However, the server does not necessarily write the data to the database file immediately. The server may wait until it is less busy, or until it has accumulated multiple changes, before writing the data to the database file.

If the server shuts down abnormally (due to a power failure, for example) before all data have been written to the database file, the server can recover 100% of committed data by reading the combination of the database file and the transaction log file. Any changes since the last write to the database file are in the transaction log file. The server can read those changes from the log file and then use that information to update the database file. The process of reading changes from the log file and updating the database file is called "recovery". At the end of the recovery process, the database file is 100% up to date.

The recovery process is automatically executed any time that the server restarts after an abnormal shutdown. The process is generally invisible to the user (except that there may be a delay before the server is ready to respond to new requests). Not surprisingly, to have a 100% recovery, you must have 100% of the transactions written to the log file. Normally, the database server writes data to the log file at the same time that the server commits the data. Thus committed transactions are stored on disk and will not be lost if the computer is shut down abnormally. This is called "strict durability". The data that have been committed are "durable", even if the server is shut down abnormally.

If durability is "strict", data are written to the disk drive at the time that the data are committed. The user is not told that his data have been committed until the data were successfully written to the transaction log on disk. This ensures that the data are recoverable if the server shuts down abnormally, for example, due to a power failure. Strict durability makes it almost impossible to lose data unless the hard disk drive itself fails. If durability is "relaxed", the user may be told that the data have been committed even before the data have been written to the transaction log on disk. The server may choose to delay writing the data by, for example, waiting until there are several transactions to write. If durability is relaxed, the server may lose a few committed transactions if there is a power failure before the data are written to disk.

Historically, the goal of most database servers has been to maximize safety, that is, to make sure that data are not lost due to a power failure or other problems. These database servers use "strict durability". This approach is appropriate for many types of data, such as accounting data, where it is often unacceptable to lose track of even a single transaction. Some database servers have been designed to maximize performance, without regard to safety. This is acceptable in situations where, for example, you only need to sample data, or where the server can simply operate on the most recent set of data, regardless of the size of that set.

Suppose, for example, that you have a server containing statistical data about performance, for instance which computers experience the heaviest loads at particular times of the day. You might use such information to balance the load on your computers. This information changes over time, and "old" data are less valuable than "new" data. In fact, you might completely discard any data that are more than a week old. If you were to lose the performance and load balancing data, then your system would still function, and within a week you would have acquired a complete set of new data (assuming that you normally discard data older than one week). In this situation, occasional or small data loss is acceptable, and performance may be more important. solidDB allows you to specify whether you want logging to be "strict" (that is, to guarantee that all committed data can be recovered after an unexpected shutdown) or "relaxed" (that is, to allow some recent transactions to be lost in some circumstances).

You can increase performance by instructing the server that it does not necessarily have to write to the log file at the same time that it commits data. This allows the server to write to the log file later, perhaps when the server is less busy, or when several transactions can be written at once. This is called "relaxed durability". It increases performance by decreasing the I/O load. If you set the transaction durability level to "relaxed", you risk losing some data if the server shuts down abnormally after it has committed some data but before it has written those data to the transaction log. Therefore, you should use relaxed durability only when you can afford to lose a small amount of recent data.

If you can afford to lose a small amount of recent data, and if performance is crucial to you, then you may want to use relaxed durability. Relaxed durability is appropriate when each individual transaction is not crucial. For example, if you are monitoring system performance and you want to store data on response times, you may only be interested in average response times, which will not be significantly affected if you are missing a few pieces of data.

In fact, since measuring performance will itself affect performance (by using up resources such as Central Processing Unit (CPU) time and I/O bandwidth), you probably want your performance tracking operations themselves to have high performance (low cost) rather than high precision. Relaxed durability is appropriate in this situation. On the other hand, if you are tracking financial data, such as bill payments, then you probably want to ensure that 100% of your committed data are stored and recoverable. In this situation, you will want strict durability. You should use relaxed durability only when you can afford to lose a few of the most recent transactions. Otherwise, use strict durability. If you are not sure whether strict or relaxed durability is appropriate use strict durability.

If you specify an AUTO_INCREMENT column for a solidDB table, the table handle in the solidDB data dictionary contains a special sequence object called the "auto-increment sequence object" that is used in assigning new values for the column. This auto-increment sequence object is stored to disk.

If a user does not explicitly specify a value for an AUTO_INCREMENT column, solidDB increments the counter by one and assigns the new value to the column. If the user inserts a row that explicitly specifies the column value, and the value is bigger than the current counter value, the counter is set to the specified column value.

You may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have generated numbers using the counter.

If a user specifies NULL or 0 for the AUTO_INCREMENT column in an INSERT, solidDB treats the row as if the value had not been specified and generates a new value for it.

The behavior of the auto-increment mechanism is not defined if a user assigns a negative value to the column or if the value becomes bigger than the maximum integer that can be stored in the specified integer type.

solidDB supports the AUTO_INCREMENT = N table option in CREATE TABLE and ALTER TABLE statements, to set the initial counter value or alter the current counter value.