PlanetMysql.ru — информация о СУБД MySQL

I recently came across a dev VM running MySQL 5.0.77 (an old release, 28 January 2009) that didn’t have InnoDB available. skip-innodb wasn’t set, SHOW VARIABLES LIKE '%innodb%' looked as expected, but with one exception: the value of have-innodb was DISABLED.

I confirmed this with SHOW ENGINES:

(root@localhost) [(none)]> show engines;
+------------+----------+----------------------------------------------------------------+
| Engine     | Support  | Comment                                                        |
+------------+----------+----------------------------------------------------------------+
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | DISABLED | Supports transactions, row-level locking, and foreign keys     |
...

(and chuckled to myself over the comment about MyISAM’s performance)

/var/log/mysqld.log yielded the answer:

120127 17:17:51  mysqld started
120127 17:17:51 [Warning] /usr/libexec/mysqld: ignoring option '--engine-condition-pushdown' due to invalid value 'InnoDB'
InnoDB: Error: log file ./ib_logfile0 is of different size 0 20971520 bytes
InnoDB: than specified in the .cnf file 0 104857600 bytes!
120127 17:17:51 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

innodb_file_per_table was not set on this VM, and when the InnoDB log files had become corrupted and were not recognised, the engine couldn’t start. These ib_logfile files are the crash recovery logs:

The unsung heroes of InnoDB are the logfiles. They are what makes InnoDB automatic crash recovery possible.

Database administrators of other DBMS may be familiar with the concept of a “redo” log. When data is changed, affected data pages are changed in the innodb_buffer_pool. Then, the change is written to the redo log, which in MySQL is the InnoDB logfile (ib_logfile0 and ib_logfile1). The pages are marked as “dirty”, and eventually get flushed and written to disk.

If MySQL crashes, there may be data that is changed that has not been written to disk. Those data pages were marked as “dirty” in the innodb_buffer_pool, but after a MySQL crash the innodb_buffer_pool no longer exists. However, they were written to the redo log. On crash recovery, MySQL can read the redo log (InnoDB log files) and apply any changes that were not written to disk.

The solution is to move the logs and allow InnoDB to recreate them. Don’t delete them – you may need them if your server has crashed or in case of data loss.

/etc/init.d/mysql stop
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak
/etc/init.d/mysql start

With the files absent, InnoDB recreates them and the engine is loaded successfully.

Of note: when this problem occurs, MySQL 5.0.77 erroneously returns this:

(root@some_host) [some_db]> SHOW ENGINE INNODB STATUS;
ERROR 1235 (42000): Cannot call SHOW INNODB STATUS because skip-innodb is defined

PlanetMySQL Voting: Vote UP / Vote DOWN

Amazon’s SimpleDB isn’t a relational database, but it does provide elastic scalability and high-availability. Amazon’s recently announced Relational Database Services (RDS) is a relational database, but it doesn’t provide elastic scalability or high-availability. If you are deploying enterprise applications on the cloud (including Amazon Web Services), you might want to look at ScaleDB because it is a relational database and it does provide elastic scalability and high-availability.

Amazon describes SimpleDB by comparing it to a clustered database:

"A traditional, clustered relational database requires a sizable upfront capital outlay, is complex to design, and often requires extensive and repetitive database administration. Amazon SimpleDB is dramatically simpler, requiring no schema, automatically indexing your data and providing a simple API for storage and access. This approach eliminates the administrative burden of data modeling, index maintenance, and performance tuning. Developers gain access to this functionality within Amazon’s proven computing environment, are able to scale instantly, and pay only for what they use."

In other words, if there was a clustered database that was cost-efficient, simple, low-maintenance, and provided dynamic elasticity, that would be ideal. That is exactly what ScaleDB provides. Granted it isn’t as simple to use as SimpleDB (just look at the name, one is simple, the other is scale) but it does eliminate data partitioning and slaves/replication, both of which account for the bulk of the pain in clustering. ScaleDB also runs MySQL applications without modification.

Amazon, in a nod to SQL developers and MySQL applications, released Relational Database Services (RDS) this week. This too comes up short of Amazon’s ideal of a dynamically scalable and highly available MySQL database. Again, that is exactly what ScaleDB provides.

Comparing SimpleDB, RDS and ScaleDB

Function	SimpleDB	RDS	ScaleDB
Transactions	No	Yes	Yes
Joins	No	Yes	Yes1
Data Consistency	No (Eventual)	Yes	Yes2
SQL Support	No	Yes	Yes
ACID Compliant	No	Yes	Yes
Exploits EBS	No	Yes	Yes
Supports MySQL applications without modification	No	Yes	Yes
Dynamic Elasticity (w/o interrupting the application)	Yes	No	Yes
High-Availability	Yes	No	Yes
Eliminates Partitioning	Yes	No	Yes
Eliminates possible 5-minute data loss upon failure	Yes	No	Yes
Cluster-level load balancing	Yes	No	Yes

1The ScaleDB index delivers multi-table joins with the performance of a single table lookup using a technology that rivals materialized views but without the data synchronization headache.

2ScaleDB’s shared-disk architecture ensures data consistency across all nodes in the cluster.

ScaleDB is a storage engine that plugs into MySQL. It turns MySQL into a shared-disk DBMS, like Oracle RAC. ScaleDB, running on AWS provides elastic scalability, adding/removing nodes according to the number of database connections, all without interrupting any running applications. Also, because ScaleDB doesn’t rely on data partitioning-as you would with shared-nothing databases-the set-up and tuning are very simple.

SimpleDB and RDS are very good and they have their roles. However, I believe that ScaleDB is really the high-end solution, without the high-end price-that enterprise users of the cloud are looking for.

PlanetMySQL Voting: Vote UP / Vote DOWN