MySQL Study--mysql Architecture in-depth analysis

MySQL Study--mysql Architecture in-depth analysis

MySQL Architecture

Consists of the connection pool component, the management service and the? Tools component, the SQL interface component, the Query Analyzer component , the optimizer component, the buffer component, the plug-in storage engine, and the physical file. MySQL is a unique plug-in architecture, and each storage engine has its own characteristics.

MySQL memory structure:

MYSQL process Structure

MySQL does not function as a multi-process as Oracle does. By default, the InnoDB storage engine has a background thread of 7:

4 IO Thread,

1 Master Thread,

1 locks (lock) monitor threads.

1 Error monitoring threads.

The number of default IO thread was added at the beginning of the InnoDB plugin version number, with the default read thread and write thread increasing to 4 respectively. Instead of using Innodb_file_ io_threads, the innodb_read_io_threads and innodb_write_io_threads parameters are used respectively.

View MySQL Thread:

<strong><span style= "FONT-SIZE:14PX;" >mysql> Show variables like ' innodb_%version% '; </span></strong>+----------------+-------+| Variable_name  | Value |+----------------+-------+| innodb_version | 1.2.4 |+----------------+-------+1 row in Set (0.00 sec)

<strong><span style= "FONT-SIZE:14PX;" >mysql> Show engine InnoDB status;</span></strong>=====================================150709 15:26:40 INNODB MONITOR output=====================================per second averages calculated from Thelast 12 Seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops:0 srv_active, 0srv_shutdown, 804 Srv_idlesrv_master_thread log flush and writes:804----------semaphores----------OS WAIT ARRAY info:reservation Count 2O S WAIT ARRAY info:signal count 2Mutex spin waits 0, rounds 0, OS waits 0rw-shared spins 2, rounds, OS waits 2rw-excl s Pins 0, Rounds 0, OS waits 0Spin rounds per wait:0.00 mutex, 30.00rw-shared, 0.00 rw-excl------------Transactions--------  ----TRX ID counter 5377Purge done for Trx ' s N:o < 4872 undo n:o< 0History List length 1LIST of transactions for each SESSION:---TRANSACTION 0, not startedmysql thread ID 3, OS thread handle0x7f1656c75700, query ID 5 localhost root initsho W ENgine InnoDB status--------FILE I/o--------<strong>i/o thread 0 state:waiting for Completedaio requests (insert Buf Fer thread) I/O thread 1 state:waiting for Completedaio requests (log thread) I/O thread 2 state:waiting for Completedaio Requests (read thread) I/O thread 3 state:waiting for Completedaio requests (read thread) I/O thread 4 state:waiting for C Ompletedaio requests (read thread) I/O thread 5 state:waiting for Completedaio requests (read thread) I/O thread 6 state:w Aiting for Completedaio requests (write thread) I/O thread 7 state:waiting for Completedaio requests (write thread) I/O thr EAD 8 state:waiting for Completedaio requests (write thread) I/O thread 9 state:waiting for Completedaio requests (write Thread) Pending normal AIO reads:0 [0, 0, 0, 0], Aio writes:0 [0, 0, 0, 0],</strong> Ibufaio reads:0, log I/O ' s: 0, Sync I/O ' s:0pending flushes (fsync) log:0; bufferpool:0283 OS file reads, 5 OS file writes, 5 OSfsyncs0.00 reads/s, 0 avg bytes/read, 0.00WRITES/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, D Elete Mark 0, delete 0discarded operations:insert 0, delete mark 0, delete 0Hash table size 276707, node heap has 0buffer (s) 0.00 Hash searches/s, 0.00 NON-HASHSEARCHES/S---Log---log sequence number 1611537Log flushed up to 1611537Pages flush Ed up to 1611537Last checkpoint at 16115370 pending log writes, 0 pending CHKP writes8 log I/O ' s done, 0.00 log I/O ' s/sec Ond----------------------BUFFER POOL and memory----------------------total memory allocated 137363456; Inadditional Pool allocated 0Dictionary memory allocated 39010Buffer pool Si

MySQL Study--mysql Architecture in-depth analysis