Performance Comparison Between MySQL MyISAM engine and InnoDB Engine

I believe that MySQL MyISAM engine and InnoDB engine are familiar to everyone. Next, we will test the performance comparison between MySQL MyISAM and InnoDB, hoping to help you.

Performance Comparison Between MySQL MyISAM engine and InnoDB engine:

First, we will introduce the configuration of "hardware" and "software.

1: hardware configuration

CPU: AMD2500 + (1.8 GB)
Memory: 1 GB/modern
Hard Disk: 80 GB/IDE
 
2: Software Configuration

OS: Windows XP SP2
SE: PHP5.2.1
DB: MySQL5.0.37
Web: IIS6
 
3: 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;  
  
  
 
 
 

4: 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
 
5. 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]

The main difference between the two types is that InnoDB supports transaction processing and Foreign keys. myISAM does not support. because MyISAM is relatively simple, it is more efficient than InnoDB .. using MyISAM for small applications is a good choice.

MyISAM tables are saved as files. Using MyISAM storage in cross-platform data transfer saves a lot of trouble ..

All InnoDB tables are stored in the same data file ibdata1 and may also be multiple files or independent tablespace files), which is relatively difficult to back up, the free solution can be copying data files, backing up binlogs, or using mysqldump.

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.
 

MySQL left join query Experience Summary

How to display MYSQL table information

Three Common MySQL table creation statements

MySQL connection Query

Mysql multi-Table query implementation