MySQL Database engine: MyISAM and InnoDB (performance optimization)

Transfer from http://yuwensan126.iteye.com/blog/1138022

The two most commonly used engines in the Mysql database are Innordb and MyISAM. The function of INNORDB is much stronger than myiasm, but Innordb performance is much worse than MyISAM, if your website just do simple query, update, delete, then use Myiasm is the best choice.

All performance tests are tested on computers with: Micrisoft window XP SP2, Intel (r) pentinum (r) M processor 1.6oGHz 1G memory.

Test results:

Test method: Submit 10 consecutive query, table records total: 380,000, time unit s

Engine Type Myisam INNORDB performance difference
Count 0.0008357 3.0163 3609
Query primary key 0.005708 0.1574 27.57
Querying for non-primary keys 24.01 80.37 3.348
Update primary key 0.008124 0.8183 100.7
Update non-primary key 0.004141 0.02625 6.338
Insertion 0.004188 0.3694 88.21

Conclusion:

1. After adding the index, for MyISAM query can be accelerated: 4 206.09733 times times, the Innordb query 510.72921 times times faster. At the same time, the update speed of the MyISAM update slows down to the original 1/2,innordb and slows down to the original 1/30. We have to look at the situation to determine whether to index, such as the log table does not query, do not do any index.
2. If your data volume is millions and there is no transaction processing, then using MyISAM is the best performance option.
3. Innordb the size of the table larger, with MyISAM can save a lot of hard disk space.

In this 38w table we tested, the table takes up space as follows:
Engine type                      myisam                innordb
  Data                         53,924 kb            58,976 KB
  Index                         13,640 kb            21,072 KB
  occupy total space              67,564 kb           80,048 KB
 
Another 176W record of the table, The table occupies space as follows:

Engine Type MyIsam Innordb
Data 56,166 KB 90,736 KB
Index 67,103 KB 88,848 KB
Total space occupied 123,269 KB 179,584 KB

MyISAM engine and Innordb engine introduction:

MyISAM

MyISAM is the ISAM extended format for MySQL and the default database engine. In addition to providing a number of functions for index and field management that are not available in ISAM, MyISAM also uses a form-locking mechanism to optimize multiple concurrent read and write operations. The cost is that you need to run the Optimize Table command frequently to restore space wasted by the updated mechanism (you should know that the data structure should be known, the ISAM file format is defective). MyISAM also has some useful extensions, such as the Myisamchk tool for repairing database files and the Myisampack tool for recovering wasted space.

MyISAM emphasizes fast read operations, which may be the main reason why MySQL is so popular with Web development: in Web development, the bulk of your data operations are read operations. Therefore, most virtual hosting providers and Internet Platform providers (Internet presence Provider,ipp) only allow the use of the MyISAM format. An important flaw in the MyISAM format is the inability to recover data after a table is corrupted.

Innordb

The InnoDB database engine is a direct product of the technology that makes MySQL flexible, and the technology is the mysql++ API. Every challenge you face when using MySQL comes from the ISAM and the MyISAM database engine does not support transactional processing or foreign keys. Although much slower than ISAM and MyISAM engines, InnoDB includes support for transactional and foreign keys, which are not available in the top two engines for two points. As mentioned earlier, if your design requires accesses than either or both of these features, you will be forced to use one of the latter two engines.

The official Mysql explanation for Innordb is this:

InnoDB provides MySQL with a transaction-safe (acid-compatible) storage engine with Commit, rollback, and crash resiliency. InnoDB locks the row-level and also provides an Oracle-style, non-locking read in the SELECT statement. These features add to multi-user deployment and performance. There is no need to widen the lock in the InnoDB because a row-level lock in InnoDB is suitable for very small spaces. InnoDB also supports foreign key coercion. In SQL queries, you are free to mix tables of the InnoDB type with other MySQL table types, even in the same query.

The InnoDB is designed for maximum performance when dealing with large amounts of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine.

The InnoDB storage engine is fully integrated with the MySQL server, and the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB stores its table and index in a table space, a tablespace can contain several files (or raw disk partitions). This is different from the MyISAM table, such as in the MyISAM table where each table is in a separate file. The InnoDB table can be any size, even if the file size is limited to 2GB on the operating system.

The InnoDB is included in the MySQL binary distribution by default. Windows Essentials Installer makes InnoDB a default table for MySQL on Windows.

InnoDB is used to generate large database sites that require high performance. The famous Internet news site slashdot.org runs on InnoDB. Mytrix, Inc. stores more than 1TB of data on InnoDB, and there are other sites on the InnoDB that handle an average of 800 insertions/updates per second

MySQL performance optimization (ii)

Today suddenly saw a year ago written by MySQL Optimizer (a), feeling somewhat fraught. I'll add some things today.
There may be gaps in the theory and in practice about engine selection, so there is an experimental spirit to treat MySQL.
Generally speaking
MYisam Suitable for:
1. Do a lot of count calculations.
2. Insert is not trivial, query very frequently.
3. No transaction

The Innordb is ideal for:
1. Reliability requirements are relatively high, or require transactions.
2. Table updates and queries are fairly frequent, and the chances of table locking are relatively large.

Of course, this is not absolute. We have to experiment before we know.
Many times, performance bottlenecks are not due to poor server configuration, but because SQL is not very good. The performance optimization of SQL is the key.
In addition to these common optimization methods,
You can also consider using memory tables. When you test a myisam and memory table, you will find that the speed is similar.
In fact, when the concurrency is strong, performance will vary by about five times times. I have a list of IP addresses and regions that I often want to query, and then put them in memory.
The previous query 5,000 IP address about 15S, now, as long as 2S more.
A lot of things, actually tested before you know. With what stress test, the test is not very accurate.

(GO) MySQL Database engine: MyISAM and InnoDB (performance optimized)