MySQL study case--mysql system and storage engine

MYSQL study case--mysql system and storage engine

1. Databases and instances

database: A collection of physical operating system files or other forms of file types. In MySQL, the database file can be a file ending with frm, myd, Myi, IBD. When using the NDB engine, the database file may not be a file on the operating system, but it will be stored in memory, but the definition will remain unchanged.

DB Instance : Consists of a database background process/thread and a shared memory area. Shared memory can be shared by running background processes/threads. Keep in mind that a DB instance is really used to manipulate database files.

In MySQL, the instance and database typically have a one by one correspondence, that is, one instance corresponds to a database, and one database corresponds to one instance. However, there may be situations where a database can be accessed by multiple instances in a cluster.

MySQL is designed as a database of single-process multithreaded architectures.

2. mysql Process

[[email protected] ~] #mysqld_safe  &[[email protected] ~]# ps -ef|grep  mysql |grep -v greproot      4168  4130   0 14:48 pts/1    00:00:00 /bin/sh ./mysqld_safemysql      4294  4168  0 14:48 pts/1    00:00:14  /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql -- plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file= /var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sockroot      4643   4130  0 15:26 pts/1    00:00:00 mysql -h  Localhost -u root -pmysql configuration file: [[email protected] ~]# mysql --help | grep my.cnf                       order of preference, my.cnf,  $MYSQL _tcp_port,/etc/mysql/my.cnf /etc/my.cnf  ~/.my.cnf  data file path:mysql> show variables like  ' DataDir '  \g***************  1. row ***************************Variable_name: datadir         Value: /var/lib/mysql/1 row in set  (0.00 SEC) [[ EMAIL PROTECTED] BIN]# LS -L /VAR/LIB/MYSQL/TOTAL 28688-RW-RW---- 1  mysql mysql       56 jan 28 17:25  AUTO.CNF-RW-RW---- 1 mysql mysql 18874368 Feb  2 14:48  IBDATA1-RW-RW---- 1 mysql mysql  5242880 feb  2 14:48 ib_ LOGFILE0-RW-RW---- 1 mysql mysql  5242880 JAN 28 17:21 IB_LOGFILE1DRWX------ 2 mysql root       4096 jan 28 17:21 mysqlsrwxrwxrwx 1 mysql mysql         0 FEB  2 14:48 MYSQL.SOCKDRWX------  2 mysql mysql     4096 jan 28 17:21 performance_ SCHEMADRWX------ 2 mysql root      4096 Jan 28  17:21 test

3. mysql structure
Before you go into the MySQL storage engine, let's introduce the structure of MySQL.

650) this.width=650; "src=" Http://pic002.cnblogs.com/images/2012/111874/2012070322224517.png "/>

You can see that MySQL consists of the following parts:

• Connection pool

• Manage services and tools components

• SQL interface

• Query Analyzer

• Optimizer

• Cache

• Plug-in storage engine

• Physical files

innodb is a storage engine for the MySQL database:

   innodb provides MySQL tables with transactions, rollback, crash-repair capabilities, transactional security for multiple versions of concurrency control, gap locks (which can effectively prevent Phantom reads from appearing), support for secondary indexes, clustered indexes, adaptive hash indexes, support for hot spares, row-level locks. And InnoDB is the only engine on MySQL that provides a foreign key constraint.

In the InnoDB storage engine, the table structure of the tables created is stored separately and stored in the. frm file. Data and indexes are stored together and stored in a table space. But by default, MySQL stores all the InnoDB tables of the database in a table space. In fact, this way of management is very inconvenient and does not support advanced features, so it is recommended that each table be stored as a table space implementation by using the server variable innodb_file_per_table = 1.

The InnoDB storage engine can also be selected if a database that requires frequent updates and deletions is required. Because the storage engine can implement transaction commits and rollback.

    innodb Storage engine memory Consists of the buffer pool, the redo log buffer pool (redo log buffer), and the additional memory pool (additional), respectively, by the parameters in the configuration file Innodb_buffer_pool_ Size and innodb_log_buffer_size are determined.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/59/4F/wKiom1TPPs6hU3AOAAGnL8_pdKA343.jpg "title=" 2.png " alt= "Wkiom1tpps6hu3aoaagnl8_pdka343.jpg"/>

InnoDB Architecture:
The primary role of a background thread is to refresh the data in the memory pool to ensure that the memory in the buffer pool caches the most recent data. In addition, the modified data file is flushed to the disk file, and InnoDB can be restored to normal operation in the event of an exception in the database.
Background thread
Because Oracle is a multi-process architecture (except under Windows), there are some simple commands to learn about the background processes that Oracle is currently running, such as the IPCS command. In general, Oracle's core background processes are ckpt, dbwn, LGWR, ARCn, Pmon, Smon, and so on.
Many DBAs asked me if the InnoDB storage engine was the same architecture, but after the implementation of the multithreaded version, I decided to look at the source code of InnoDB and found that InnoDB was not working on the database process. The InnoDB storage engine implements almost all of the functionality on a thread called the master thread.
By default, the background thread of the InnoDB storage engine has 7-4 io thread,1 master thread,1 Locks (lock) monitoring threads, and 1 Error monitoring threads. The number of IO thread is controlled by the Innodb_file_ io_threads parameter in the configuration file, which defaults to 4, as shown below.

Mysql> show engine innodb status \g*************************** 1. row  ***************************  type: innodb  name: status: ============ =========================150202 17:15:33 innodb monitor output============================ =========per second averages calculated from the last 19  Seconds-----------------Background thread-----------------srv_master_thread loops: 0 srv_ active, 0 srv_shutdown, 8787 srv_idlesrv_master_thread log flush and  writes: 8787----------semaphores----------Os wait array info: reservation count  3os wait array info: signal count 3mutex spin waits 2,  rounds 60, OS waits 1RW-shared spins 2, rounds 60, OS  Waits 2rw-excl spins&nbsP;0, rounds 0, os waits 0spin rounds per wait: 30.00 mutex,  30.00 rw-shared, 0.00 rw-excl------------Transactions------------trx id counter  2305purge done for trx ' s n:o < 0 undo n:o <  0history list length 0list of transactions for each session:--- Transaction 0, not startedmysql thread id 3, os thread handle  0x7fe2a80c2700, query id 45 localhost root initshow engine  Innodb status--------file i/o--------i/o thread 0 state: waiting for  completed aio requests  (Insert buffer thread) i/o thread 1 state:  waiting for completed aio requests  (Log thread) I/O thread 2 state :  waiting for completed aio requests  (Read thread) i/o thread 3 state: waiting for  completed aio requests  (Read thread) i/o thread 4 state: waiting  for completed aio requests  (read thread) I/o thread 5 state:  waiting for completed aio requests  (Read thread) i/o thread 6  state: waiting for completed aio requests  (Write thread) i/o  thread 7 state: waiting for completed aio requests  (Write thread) i/o thread 8 state: waiting for completed aio requests  (write  thread) i/o thread 9 state: waiting for completed aio requests   (Write thread) pending normal aio reads: 0 [0, 0, 0, 0]  , aio writes: 0&nbsp [0,&NBSP;0,&NBSP;0,&NBSP;0]&NBSP;,&NBSP;IBUF&NBSP;AIO&NBSP;READS:&NBSP;0,&NBSP;LOG&NBSP;I/O ' s: 0,  SYNC&NBSP;I/O ' s: 0pending flushes  (fsync)  log: 0; buffer pool: 0161  os file reads, 5 os file writes, 5 os fsyncs0.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 mergesmerged operations: insert 0, delete mark  0, delete 0discarded operations: insert 0, delete mark 0,  Delete 0hash table size 276707, node heap has 0 buffer (s) 0.00  hash searches/s, 0.00 non-hash searchES/S---LOG---log sequence number 1602871log flushed up to    1602871pages flushed up to 1602871last checkpoint at  16028710  PENDING&NBSP;LOG&NBSP;WRITES,&NBSP;0&NBSP;PENDING&NBSP;CHKP&NBSP;WRITES8&NBSP;LOG&NBSP;I/O ' s done,  0.00&NBSP;LOG&NBSP;I/O ' S/second----------------------buffer pool and  MEMORY----------------------Total memory allocated 137363456; in additional pool  allocated 0Dictionary memory allocated 43148Buffer pool size    8192Free buffers       8042Database pages      150old database pages 0modified db pages  0pending reads  0pending writes: lru 0, flush list 0 single page 0pages  made young 0, not young&nbSp;00.00 youngs/s, 0.00 non-youngs/spages read 150, created 0, written  10.00 reads/s, 0.00 creates/s, 0.00 writes/sno buffer pool page  gets since the last printoutpages read ahead 0.00/s, evicted  without access 0.00/s, random read ahead 0.00/slru len: 150,  unzip_lru len: 0i/o sum[0]:cur[0], unzip sum[0]:cur[0]--------------ROW  OPERATIONS--------------0 queries inside innodb, 0 queries in queue0  Read views open inside innodbmain thread process no. 4294, id  140611110070016, state: sleepingnumber of rows inserted 0, updated  0, deleted 0, read 00.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&NBSP;SEC)

Report:

Oracle Architecture Diagram

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/59/4C/wKioL1TPRc2gI0bnAAKfK4JRodk509.jpg "title=" 3.png " alt= "Wkiol1tprc2gi0bnaakfk4jrodk509.jpg"/>

This article is from the "Tianya blog," Please make sure to keep this source http://tiany.blog.51cto.com/513694/1610840

MySQL study case--mysql system and storage engine