The MySQL server adopts a modular style, and each part remains relatively independent, especially in the storage architecture. Storage Engine
Manage data storage and MySQL indexes. Through the defined API, the MySQL server can communicate with the storage engine. Currently, MyISAM and InnoDB are the most used.
After InnoDB is acquired by Oracle, Falcon, a new storage engine developed by MySQL, will be introduced in MySQL.
The MyISAM engine is a non-transactional engine that provides high-speed storage and retrieval, as well as full-text search capabilities. It is suitable for applications with frequent queries such as data warehouses. In MyISAM, a table is actually saved as three files, the. frm storage table definition,. MYD storage data, and. MYI storage index.
InnoDB
It is an engine that supports transactions. Therefore, data is stored in one or more data files and supports Oracle-like locking mechanisms. It is widely used in OLTP applications. If no
InnoDB configuration options, MySQL will create an automatic extended data file named ibdata1 under the MySQL data directory, and two files named ib_logfile0 and
Ib_logfile1 log file.
When creating a table, you can use the engine keyword to specify the storage engine to use. If it is omitted, use the default storage engine:
Create Table T (I INT) engine = MyISAM;
View the supported storage engine types in the system:
Mysql> show engines;
+ ------------ + --------- + ---------------------------------------------------------------- +
| Engine | Support | comment |
+ ------------ + --------- + ---------------------------------------------------------------- +
| MyISAM | Yes | default engine as of MySQL 3.23 With great performance |
| Memory | Yes | hash based, stored in memory, useful for temporary tables |
| InnoDB | default | supports transactions, row-level locking, and foreign keys |
| BerkeleyDB | NO | Supports transactions and page-level locking |
| BLACKHOLE | NO |/dev/null storage engine (anything you write to it disappears) |
| EXAMPLE | NO | Example storage engine |
| ARCHIVE | YES | Archive storage engine |
| CSV | NO | CSV storage engine |
| Ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| FEDERATED | NO | Federated MySQL storage engine |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| Isam | no | obsolete storage engine |
+ ------------ + --------- + ---------------------------------------------------------------- +
12 rows in SET (0.00 Sec)
The standard installer only supports some engines. To use other storage engines, you must use the source code and different parameters to re-compile. Here, DEFAULT indicates the DEFAULT storage engine of the system. You can modify the configuration parameters:
Default-storage-engine = MyISAM
View the details of a storage engine
Mysql> show engine InnoDB status/g;
* *************************** 1. row ***************************
Status:
============================================
071201 14:34:13 InnoDB monitor output
============================================
Per Second averages calculated from the last 6 seconds
----------
Semaphores
----------
OS wait array info: Reservation count 10, signal count 10
Mutex spin waits 0, rounds 100, OS waits 3
RW-shared spins 12, OS waits 6; RW-excl spins 1, OS waits 1
------------
Transactions
------------
Trx id counter 0 59141
Purge done for TRX's N: O <0 58137 undo N: O <0 0
History List length 3
Total number of lock structs in row lock Hash Table 0
List of transactions for each session:
--- Transaction 0 0, not started, OS thread ID 3100
MySQL thread ID 3, query ID 32 localhost 127.0.0.1 Root
Show engine InnoDB status
--------
File I/O
--------
I/O Thread 0 state: Wait windows AIO (insert buffer thread)
I/O thread 1 State: Wait windows AIO (log thread)
I/O thread 2 State: Wait windows AIO (read thread)
I/O thread 3 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
Ibuf aio reads: 0, log I/o's: 0, sync I/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
40 OS file reads, 35 OS file writes, 24 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 34679, used cells 0, node heap has 1 buffer (s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 8755515
Log flushed up to 0 8755515
Last checkpoint at 0 8755515
0 pending log writes, 0 pending chkp writes
18 log I/O's done, 0.00 log I/O's/second
----------------------
Buffer Pool and memory
----------------------
Total memory allocated 15529216; in additional pool allocated 1401728
Buffer pool size 512
Free buffers 480
Database pages 31
Modified dB pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 30, created 1, written 18
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
Row Operations
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 508, state: waiting for server activity
Number of rows inserted 2, updated 0, deleted 0, read 11
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
======================================
1 row in set (0.00 sec)
ERROR:
No query specified
