MySQL MyISAM engine and InnoDB Engine Performance Test

Simple Performance Test of MyISAM engine and InnoDB Engine in MySQL

[Hardware configuration]
CPU: AMD2500 + (1.8 GB)
Memory: 1 GB/modern
Hard Disk: 80 GB/IDE

[Software configuration]
OS: Windows XP SP2
SE: PHP5.2.1
DB: MySQL5.0.37
Web: IIS6

[MySQL table structure]

Create table 'myisam '(
'Id' int (11) not null auto_increment,
'Name' varchar (100) default NULL,
'Content' text,
Primary key ('id ')
) ENGINE = MyISAM default charset = gbk;

Create table 'innodb '(
'Id' int (11) not null auto_increment,
'Name' varchar (100) default NULL,
'Content' text,
Primary key ('id ')
) ENGINE = InnoDB default charset = gbk;

[Data content]

$ Name = "heiyeluren ";
$ Content = "MySQL supports several storage engines as processors for different table types. MySQL storage engine includes the engine for processing transaction security tables and the engine for processing non-transaction security tables: · MyISAM manages non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is supported in all MySQL configurations. It is the default storage engine, unless you configure MySQL to use another engine by default. · The MEMORY storage engine provides "in-MEMORY" tables. The MERGE storage engine allows a set to process the same MyISAM table as a separate table. Like MyISAM, MEMORY and MERGE storage engines process non-transaction tables. Both engines are included in MySQL by default. Note: The MEMORY storage engine is officially identified as the HEAP engine. · InnoDB and BDB storage engines provide transaction security tables. BDB is included in the MySQL-Max binary distribution version released for the operating system that supports it. InnoDB is also included in all MySQL 5.1 binary distributions by default. You can configure MySQL to allow or disable any engine as you like. · The EXAMPLE storage engine is a "stub" engine, which does not do anything. You can use this engine to create tables, but no data is stored or retrieved from them. The purpose of this engine is to provide a service. In the MySQL source code example, it demonstrates how to start writing a new storage engine. Similarly, it is mainly interested in developers. ";

[Insert data-1] (innodb_flush_log_at_trx_commit = 1)
MyISAM 1 W: 3/s
InnoDB, W: 219/s

MyISAM 10 W: 29/s
InnoDB 10 W: 2092/s

MyISAM 100 W: 287/s
InnoDB 100 W: dare not test

[Insert data-2] (innodb_flush_log_at_trx_commit = 0)
MyISAM 1 W: 3/s
InnoDB 1 W: 3/s

MyISAM 10 W: 30/s
InnoDB 10 W: 29/s

MyISAM 100 W: 273/s
InnoDB 100 W: 423/s

[Insert data 3] (innodb_buffer_pool_size = 1024 M)
InnoDB 1 W: 3/s
InnoDB 10 W: 33/s
InnoDB 100 W: 607/s

[Insert data 4] (innodb_buffer_pool_size = 256 M, innodb_flush_log_at_trx_commit = 1, set autocommit = 0)

InnoDB 1 W: 3/s
InnoDB 10 W: 26/s
InnoDB 100 W: 379/s

[MySQL configuration file] (default configuration)

# MySQL Server Instance Configuration File
[Client]
Port = 3306

[Mysql]
Default-character-set = gbk

[Mysqld]
Port = 3306
Basedir = "C:/mysql50 /"
Datadir = "C:/mysql50/Data /"
Default-character-set = gbk
Default-storage-engine = INNODB
SQL-mode = "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION"
Max_connections = 100

Query_cache_size = 0
Table_cache = 256
Tmp_table_size = 50 M
Thread_cache_size = 8
Myisam_max_sort_file_size = 100G
Myisam_max_extra_sort_file_size = 100G
Myisam_sort_buffer_size = 100 M
Key_buffer_size = 82 M
Read_buffer_size = 64 K
Read_rnd_buffer_size = 256 K
Sort_buffer_size = 256 K

Innodb_additional_mem_pool_size = 4 M
Innodb_flush_log_at_trx_commit = 1
Innodb_log_buffer_size = 2 M
Innodb_buffer_pool_size = 159 M
Innodb_log_file_size = 80 M
Innodb_thread_concurrency = 8

[Summary]

It can be seen that in MySQL 5.0, the performance of MyISAM and InnoDB Storage engines is not very different. For InnoDB, innodb_flush_log_at_trx_commit is the main option that affects performance. If it is set to 1, therefore, data is automatically submitted each time it is inserted, resulting in a sharp decline in performance. It should be related to refreshing logs. Setting it to 0 can significantly improve the efficiency. Of course, similarly, you can submit "set autocommit = 0" in SQL to SET the performance. In addition, I also heard that setting innodb_buffer_pool_size can improve the performance of InnoDB, but I did not find it significantly improved.

Basically, we can consider using InnoDB to replace our MyISAM engine, because InnoDB has many good features, such as transaction support, stored procedures, views, row-level locking, etc, in the case of a lot of concurrency, I believe that InnoDB must be much better than MyISAM. the configuration in cnf is also critical. A good configuration can effectively accelerate your application.

If it is not a complex Web application, non-critical application, you can continue to consider MyISAM. You can consider the specific situation as needed.

Reference URL:
Http://dev.mysql.com/doc/refman/5.1/zh/index.html
Http://dev.mysql.com/doc/refman/... engines.html # innodb

Trackback: http://tb.blog.csdn.net/TrackBack.aspx? PostId = 1559640

MySQL Study Notes: differences between InnoDB and MyISAM CSDN Blog introduces the article index concept. The article index is calculated based on the comprehensive score of the Blog article. The comprehensive score items are the clicks of the article, respectively, number of replies, number of indexed articles, Article length, and Article type; out of 100, updated every month.

InnoDB and MyISAM are the two most commonly used table types in MySQL, each with its own advantages and disadvantages, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. MyISAM tables emphasize performance, and the execution speed is faster than that of InnoDB, but transactions are not supported. InnoDB provides advanced database functions such as external keys for transactions.

MyIASM is a new version of the IASM table and has the following extensions:
Binary hierarchy portability.
NULL column index.
There are fewer fragments for Long-varying rows than the ISAM table.
Supports large files.
Better index compression.
Better key? statistical distribution.
Better and faster auto_increment processing.

1. The biggest advantage of MySQL is the simple SELECT, INSERT, and UPDATE operations under the MyISAM engine.
2. MyISAM data files can be copied in different operating systems, which is very important and convenient for deployment.

The following are some differences between details and specific implementations:

1. InnoDB does not support FULLTEXT indexes.
2. innoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.
3. For fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.
4. When deleting FROM table, InnoDB does not create a new table, but deletes a row.
5. the load table from master operation does not work for InnoDB. The solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then change the imported data to the InnoDB TABLE, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys.

In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the scope to be scanned when executing an SQL statement, the InnoDB table will also lock the entire table, for example, update table set num = 1 where name like "% aaa %"

Based on my understanding of the storage engine, I feel that InnoDB supports foreign keys. When the data volume can be described as "huge", on the basis of a good INDEX, innoDB Query speed should be faster than MyISAM.
Before Falcon has a stable version, I think MyISAM is an available option.

Any type of table is not omnipotent. You only need to select a proper table type for the business type to maximize the performance advantage of MySQL.

(Original address: http://bbs.chinaunix.net/thread-1696274-1-1.html)