Application feasibility analysis of MySQL large enterprise class

Here I'll discuss some ideas for enterprise-class applications of MySQL, and the ability to replace the current Oracle problem with MySQL.

First of all, I am not saying that MySQL does not have a large enterprise application, in fact, you can see more and more successful application of the MySQL, but not enough, there are many large enterprises key applications are not afraid to use MySQL. Perhaps this text can be together with you to explore some of the more "virtual" things.

Storage Engine

Since MySQL itself has never had a mature and reliable storage engine, it is estimated to be a sore spot (especially the most mature transactional engine, InnoDB, is in Oracle's hands). MySQL high hopes of Falcon in the development of more than two years later, the achievement is not big, and the project leader Jim Starkey before long and left the MySQL, the shabby house on the rain.

Will sun give MySQL a robust engine? I do not think I can achieve it in a short time. Unless, Sun buys InnoDB from Oracle hand.

If a large enterprise application, taking into account the stability of the engine itself, it seems that the optional only InnoDB, but InnoDB Backup tool is charged. As for MyISAM, although some people do like to use, but for concurrent ability requirements slightly stricter, myisam not at all.

Online DDL lock Table problem

MySQL, online table objects to do DDL operation is to lock the table, for the availability of high requirements, and the application of changes and more frequent environment, this is a very bad bottleneck. I didn't think there was any good way, unless, as we joked, all the tables were left with enough free columns to reduce the trouble of changing the column.

This MySQL-born flaw does not exist in PostgreSQL, such as creating an index, which can be used to reduce the impact using the CREATE index concurrently. (the problem was gradually improved in the MySQL successor version: The ONLINE keyword was added)

This may seem like a small problem, but in fact it is the most disturbing to many people.

Online Backup issues

MySQL finally has the ability to backup online after 6.0. But now, I'm afraid more radical users can only use version 5.

Many MySQL veteran users will be able to adapt their own backup methods according to their own characteristics (although there may be defects, such as point-in-time recovery).

As for another feature that is commonly used to measure db scalability: Partitioning, now MySQL is able to support it, although it is a bit late to implement. Users who use MySQL, generally take the sharding strategy to the data segmentation, so, the problem of partitioning does not seem to be the most critical.

This is the first of the series because it is the idea of sorting.

Storage Engine

Go on to the previous discussion and record some of the bits and pieces of my ideas about MySQL being commercially available at the big business level. The network is full of comparisons between the various engines. One caveat here is to note the granularity of the locks on each engine. InnoDB is a row lock, the implementation of the lock depends on the index, MyISAM is just a table lock. The lock granularity is an important index to measure the storage engine, and its ability determines the concurrency ability to a great extent.

As for the transaction isolation level, it is another indicator that needs to be measured.

It is commonplace that a certain engine is suitable for any type of application, and ultimately it is due to the mechanism of its implementation that determines the engine's characteristics.

Storage-tier Solutions

Believe that no one would like to use raw equipment on MySQL, many people almost directly to the data file on the file system (personally, for the database such applications, file system reliability is still lacking). I haven't found a solution like Oracle ASM on MySQL. If you use a file system, a single node's data storage capacity must be constrained-no one likes to throw a few t data onto a MySQL db? Once a file system fails, the trouble comes. From this point of view, perhaps LVM2 is an optional way.

Of course, it would be nice if you threw the data file on the SAN. On the one hand, storage vendors are far less focused on MySQL than older business databases such as Oracle and DB2. On the other hand, many MySQL users do not have a SAN environment, and the data is on the local disk.

Solid state drives and MySQL

Two days ago a friend in an analysis of the message, mentioned should focus on the application of Flash. In fact, it is not as good as the layout of the solid State hard disk (SSD) on the possible impact of MySQL problem. Believe that there are many enterprises need to be in the DB ioPS to seek a breakthrough, SSDs is a possible breakthrough, but from the current data I collected, there is not enough data to enable SSD-enabled MySQL to have the expected magnitude of the IOPS upgrade.

Business Support

Now there is sun behind MySQL, but if you don't buy a service, where do you find more formal business support (I mean software integrators)? Even if a service is purchased, can MySQL give immediate, effective technical response if the problem is with the storage engine? This is also MySQL has no own storage engine of a weakness, because the link of many links, once there are business problems, it is easy to fall into the stage of wrangling.

This is the second article in this series. If there is a third article, I would like to write a few ideas about MySQL.