I. MySQL architecture overviewRegardless of which database you use, it is extremely important to understand the architecture of your database. The MySQL architecture is primarily composed of databases and database instances.
Database: A collection of physical operating system files or other files, in MySQL, the database file can be frm, myd, Myi, IBD and other end of the file, when using the NDB storage engine, is not an OS file, is stored in memory files.
DB instance: consists of a database background process/thread and a shared memory area that can be shared by a running background process/thread.
The following is the architecture of the MySQL database:
Top-level services are not unique to MySQL, and most base-to-network client/server-side tools or services have similar architectures. such as connection processing, authorization authentication, security and so on.
The second tier architecture is mostlyThe MySQL Core Services feature is at this level, including query parsing, parsing, optimization, caching, and all built-in functions such as date, time, math, and cryptographic functions, all of which are implemented at this level: stored procedures, triggers, views, and so on.
The third layer contains the storage engine. The storage engine is responsible for the storage and extraction of data in MySQL. As with the various file systems under Gnu/linux, each storage engine has its advantages and disadvantages. The server communicates with the storage engine through the API. These interfaces mask the differences between different storage engines, making these differences transparent to the upper-level query process. The Storage Engine API contains dozens of underlying functions for performing operations such as "Start a transaction" or "fetch a row of records based on a primary key". But the storage engine does not parse the SQL, and the different storage engines do not communicate with each other, but simply respond to the upper server-side requests.
second, database background threadThe InnoDB storage engine, which is described by default, is no longer declared again. Background thread has 7--4 io thread,1 master thread,1 lock Monitoring thread, 1 error monitoring thread. The number of IO thread is controlled by the Innodb_file_io_threads parameter in the configuration file, which defaults to 4. 4 IO threads are insert buffer thread, log thread, read thread, write thread, respectively.
In MySQL 5.6.10, MySQL enterprise MySQL includes the thread pool, which is implemented using the server plugin. The default threading model on the MySQL server uses a thread statement that executes each client connection. As more and more clients connect to the server and execute statements, overall performance degrades. The thread pool plug-in provides the processing mode of other threads designed to reduce overhead and improve performance. The plug-in implements a thread pool that improves server performance by effectively managing a large number of client connections to the execution thread of the statement.
The InnoDB plugin version began to increase the number of default IO thread, with the default read thread and write thread growing to 4, and no longer using the Innodb_file_io_threads parameter, but using InnoDB respectively _read_io_threads and Innodb_write_io_threads parameters.
Thread pooling resolves several issues for single-threaded solutions for each connection model:
(the exact words are also in it, afraid of translation is wrong.) )
- An oversized thread stack causes the processor cache to be almost useless under high concurrency workloads. Thread pooling facilitates threading stack reuse to minimize processor cache.
Too Many thread stacks make CPU caches almost useless in highly parallel execution workloads. The thread pool promotes thread stack reuse to minimize the CPU cache footprint.
- With excessive thread concurrent calls, context switching brings a lot of performance consumption. At the same time, the operating system task scheduling is a challenging task. The thread pool controls the number of active threads to ensure high concurrency within the MySQL server, while manipulating and adapting the server to the operation of MySQL.
With too many threads executing in parallel, context switching overhead are high. This also presents a challenging task to the operating system scheduler. The thread pool controls the number of active threads to keep the parallelism within the MySQL server at a level that it C An handle and so is appropriate for the server host on which MySQL is executing.
- Concurrent execution causes too many transactions to compete for resources. In InnoDB, it leads to increased time spent in mutually exclusive. Once the transaction is turned on, the thread pool will control that there will not be too many concurrent executions.
Too many transactions executing in parallel increases resource contention. In InnoDB, this increases the time spent holding central mutexes. The thread pool controls when transactions start to ensure, that is too many execute in parallel.
third, database memoryThe 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 fileinnodb_buffer_pool_sizeAndinnodb_log_buffer_sizeThe size of the decision.
From for notes (Wiz)
MySQL Learning note-mysql architecture
