MySQLStudy case study-MySQL System and storage engine

MySqlStudy case-MySql System and storage engine 1. Database and instance Database: a collection of physical operating system files or other forms of file types. In MySQL, database files can be files ending with frm, myd, myi, and ibd. When the NDB engine is used, database files may not be files on the operating system, but files stored in the memory

MySql Study Case-MySql System and storage engine 1. Database and instance Database: a collection of physical operating system files or other forms of file types. In MySQL, database files can be files ending with frm, myd, myi, and ibd. When the NDB engine is used, database files may not be files on the operating system, but files stored in the memory

MySql Study Case Study-MySql System and storage engine

1. Databases and Instances

Database: A collection of physical operating system files or other format file types. In MySQL, database files can be files ending with frm, myd, myi, and ibd. When the NDB engine is used, database files may not be files in the operating system, but files stored in the memory, but the definition remains unchanged.

Database instance: It consists of a database background process/thread and a shared memory zone. Shared memory can be shared by running background processes/threads. Remember that database instances are actually used to operate database files.

In MySQL, the relationship between instances and databases is usually one-to-one, that is, an instance corresponds to a database, and a database corresponds to an instance. However, a database may be accessible to multiple instances in a cluster.

MySQL is designed as a single-process multi-threaded database.

2. MySQL process

[Root @ rh6 ~] # Mysqld_safe & [root @ rh6 ~] # Ps-ef | grep mysql | grep-v greproot 4168 4130 0 00:00:00 pts/1/bin/sh. /mysqld_safemysql 4294 4168 0 00:00:14 pts/1/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. pid -- socket =/var/lib/mysql. sockroot 4643 4130 0 pts/1 00: 00:00 mysql-h localhost-u root-pMySQL configuration file: [root @ rh6 ~] # 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) [root @ rh6 bin] # ls-l/var/lib/mysql/total 28688-rw-rw ---- 1 mysql 56 Jan 28 17:25 auto. cnf-rw ---- 1 mysql 18874368 Feb 2 ibdata1-rw-rw ---- 1 mysql 5242880 Feb 2 ib_logfile0-rw-rw ---- 1 mysql 5242880 Jan 28 ib_logfile1drwx ------ 2 mysql root 4096 Jan 28 mysqlsrwxrwxrwx 1 mysql 0 Feb 2 mysql. sockdrwx ------ 2 mysql 4096 Jan 28 performance_schemadrwx ------ 2 mysql root 4096 Jan 28 test

3. MySQL Structure
Before introducing the storage engine of MySQL, let's first introduce the structure of MySQL.

650) this. width = 650; "alt =" "src =" http://www.68idc.cn/help/uploads/allimg/151111/1215302G7-0.png "/>

MySQL consists of the following parts:

• Connection Pool

• Manage services and tool components

• SQL interface

• Query Analyzer

• Optimizer

• Cache

• Plug-in storage engine

• Physical files

4. InnoDB Storage engine:

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1215304245-1.jpg "title =" 1.png" alt = "wKiom1TPPmGCP5nqAAOffBwE-Vc660.jpg"/>InnoDB is a storage engine for Mysql databases:

InnoDB provides Mysql tables with transaction, rollback, crash repair capabilities, multi-version concurrent control of Transaction Security, Gap lock (can effectively prevent the emergence of phantom read) supports secondary indexes, clustered indexes, adaptive hash indexes, hot standby, and row-level locks. In addition, InnoDB is the only Mysql engine that provides foreign key constraints.

In the InnoDB Storage engine, the table structure of the created table is separately stored and stored in the. frm file. Data and indexes are stored together and stored in tablespaces. However, by default, mysql stores all the InnoDB tables in a tablespace. In fact, this method is very inconvenient to manage and does not support advanced functions. Therefore, we recommend that you use the server variable innodb_file_per_table = 1 to store each table as a tablespace.

If you need to frequently update or delete databases, you can also select the InnoDB Storage engine. This storage engine can commit and roll back transactions.

The memory of the InnoDB Storage engine consists of the buffer pool, redo log buffer, and additional memory pool ), the size is determined by the innodb_buffer_pool_size and innodb_log_buffer_size parameters in the configuration file.

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1215302501-2.jpg "title =" 2.png" alt = "wKiom1TPPs6hU3AOAAGnL8_pdKA343.jpg"/>

InnoDB architecture:
The main function of background threads is to refresh the data in the memory pool and ensure that the memory in the buffer pool caches the latest data. In addition, the modified data file is refreshed to the disk file, and InnoDB can be restored to normal operation in case of database exceptions.
Background thread
Since Oracle is a multi-process architecture (except for Windows), you can use some simple commands to learn the background processes currently running in Oracle, such as the ipcs command. Generally, the core background processes of Oracle include CKPT, DBWn, LGWR, ARCn, PMON, and SMON.
Many DBAs asked me if the InnoDB Storage engine is also in this architecture, but after implementing the multi-threaded version, I decided to check the InnoDB source code, it is found that InnoDB does not perform such operations on Database processes. The InnoDB Storage engine implements almost all functions on a thread called a master thread.
By default, the InnoDB Storage engine has 7-4 IO threads, 1 master thread, 1 lock monitoring thread, and 1 error monitoring thread. The number of IO threads is controlled by the innodb_file _ io_threads parameter in the configuration file. The default value is 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 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 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync 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 log writes, 0 pending chkp writes8 log i/o's done, 0.00 log 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 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 sec)

Appendix:

Oracle architecture Diagram

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1215305B9-3.jpg "title =" 3.png" alt = "wKioL1TPRc2gI0bnAAKfK4JRodk509.jpg"/>