Shulou(Shulou.com)06/01 Report--

Comparing the performance of the MyISAM engine with the InnoDB engine in MySQL, basically we can consider using InnoDB instead of our MyISAM engine.

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 types of tables. The MySQL storage engine includes an engine for processing transactional security tables and an engine for processing non-transactional security tables: MyISAM manages non-transactional tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is supported in all MySQL configurations and 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 collections to process the same MyISAM table as a separate table. Just like MyISAM, the MEMORY and MERGE storage engines handle non-transactional tables, and both engines are included in MySQL by default. Explanation: the MEMORY storage engine is officially identified as the HEAP engine. The InnoDB and BDB storage engines provide transaction security tables. BDB is included in the MySQL-Max binary distribution released for the operating system that supports it. InnoDB is also included by default in all MySQL 5.1 binary distributions, and you can configure MySQL to allow or disable any engine as you like. The EXAMPLE storage engine is a "stub" engine that does nothing. You can use this engine to create tables, but no data is stored or retrieved from it. The purpose of this engine is to serve as an example in the MySQL source code that demonstrates how to start writing a new storage engine. Again, its main interest is in developers. "

[insert data-1] (innodb_flush_log_at_trx_commit=1)

MyISAM 1W:3/s

InnoDB 1W:219/s

MyISAM 10W:29/s

InnoDB 10W:2092/s

MyISAM 100W:287/s

InnoDB 100W: dare not test

[insert data-2] (innodb_flush_log_at_trx_commit=0)

MyISAM 1W:3/s

InnoDB 1W:3/s

MyISAM 10W:30/s

InnoDB 10W:29/s

MyISAM 100W:273/s

InnoDB 100W:423/s

[insert data 3] (innodb_buffer_pool_size=1024M)

InnoDB 1W:3/s

InnoDB 10W:33/s

InnoDB 100W:607/s

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

InnoDB 1W:3/s

InnoDB 10W:26/s

InnoDB 100W:379/s

[MySQL profile] (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=50M

Thread_cache_size=8

Myisam_max_sort_file_size=100G

Myisam_max_extra_sort_file_size=100G

Myisam_sort_buffer_size=100M

Key_buffer_size=82M

Read_buffer_size=64K

Read_rnd_buffer_size=256K

Sort_buffer_size=256K

Innodb_additional_mem_pool_size=4M

Innodb_flush_log_at_trx_commit=1

Innodb_log_buffer_size=2M

Innodb_buffer_pool_size=159M

Innodb_log_file_size=80M

Innodb_thread_concurrency=8

Summary

It can be seen that there is not much difference between the performance of MyISAM and InnoDB storage engines in MySQL 5.0. for InnoDB, it is the innodb_flush_log_at_trx_commit option that mainly affects performance. if it is set to 1, it will be automatically committed every time data is inserted, resulting in a sharp decline in performance. it should have something to do with refreshing logs. Setting it to 0 can see a significant improvement in efficiency, of course. Similarly, you can submit "SET AUTOCOMMIT = 0" in SQL to achieve good performance. In addition, I also heard that setting innodb_buffer_pool_size can improve the performance of InnoDB, but my tests found no significant improvement.

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 the performance of InnoDB must be much better than MyISAM, of course, the corresponding configuration in my.cnf is also more critical, good configuration, can effectively accelerate your application.

If it is not very complex Web applications, non-critical applications, you can continue to consider MyISAM, this specific situation can be considered by yourself.

Hardware configuration

CPU: AMD2500+ (1.8g) memory: 1G/ Modern hard disk: 80G/IDE

Software configuration

OS: Windows XP SP2 SE: PHP5.2.1 DB: MySQL5.0.37 Web: IIS6

Note: myisam engine does not support things. Myisam uses hash index by default while innodb uses btree index. In Internet companies, many businesses adopt the read-write separation mode of primary innodb slave myisam.

In addition, myisam is suitable for query and innodb is suitable for update.

This article is from: http://www.phpq.net/mysql/myisam-innodb.html

[@ more@]

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.