MySQL column - Linux underlying interaction and RAID storage architecture

A Dong lazy 2021-09-15 04:22:49
mysql column linux underlying interaction

Mysql special column - Linux Underlying interaction and Raid Storage architecture


​ In the previous articles of the column , We summarized the buffer pool , Cache pages ,redo log,undo log, And how data pages and rows are stored at the bottom , Table spaces are introduced later , paragraph , Area and other concepts . This section is special , It's about and Linux About the interaction principle , Because most mysql It's all deployed in linux On your server , This section will briefly introduce linux How to deal with mysql Requested by , as well as linux What problems will the system bring


  • Introduce mysql Random and sequential reading and writing , That part is random reading and writing , That part is sequential reading and writing
  • linux Hierarchical logical structure of the system , And the mysql Interaction steps of the system .
  • Raid Simple understanding of the storage structure , And his concern for mysql The impact of services .
  • Supplementary information too many connections Causes of problems and solutions .

mysql Random and sequential reading and writing

​ mysql When performing addition, deletion, modification and query , The data page will be read from the disk file of the table space , First of all, we need to understand , Although the data page of the disk file in the table space is Random, speaking, reading and writing , But it includes Redo logbin log Wait, these log files are actually on disk Sequential reading and writing Of .

Random read of disk

​ Random reading a data page to the cache is a random read operation on the disk , Because the position of the data page is not fixed, it may be anywhere on the disk , So the whole read-write performance is very poor , According to the previous introduction , When the data page is read, it is placed in buffer pool Inside .

​ in order to IO Performance of , Here you need to understand two important performance indicators :IOPS and Response delay .IOPS It refers to how many disk read and write operations the storage system can perform per second , The underlying disk supports execution per second 1000 A random read-write and 200 There is a big gap . The response delay is about once IO How long does it take to respond after the request , It also has a great impact , One second 200 Reading and writing is 10MS Complete and once 200 Reading and writing is 1S The gap between completion and response is also very large .

redo The journal is written in sequence

​ Although the disk is read and written randomly to the buffer pool , But after the cache page is updated , This data needs to be written in order redo log The log , Sequential writing means that you can also read sequentially when reading .( About redo log The details will be introduced in the following articles ) The performance of disk sequential write is actually very important , To some extent, high-speed sequential disk access can almost catch up with the reading and writing performance of memory , Especially in the database os cache Mechanism Under the circumstances , That is to say redo log Before sequentially writing to disk , First, enter os cache, It is managed by the operating system Memory cache in .


About os cache You can turn over the previous articles , He also has a role if innodb Set in the engine :innodb_flush_log_at_trx_commit by 2, Will be written in redo When logging, the data is displayed and written to os cache among , And in 1S after ( Probably ) Then refresh the data back to redo log Disk files for , But that's what's going on mysql The hidden danger of data loss will occur during downtime ....

In addition, I found out about os cache as well as application cache Speed consumption comparison , In the answer, the validity criteria of cache are discussed , And when to use cache. If you are interested, you can check the specific content of the article :


​ Finally, we summarize the following figure according to the above text :

Follow the instructions above , We can roughly summarize the following two points :

  • Write redo log The faster the speed, the better the query sql The higher the performance .
  • The disk can write more data per second , It also shows that the higher the performance .

Linux System layering

​ Now let's get to the point , Simply speaking Linux The storage system is divided into VFS layer 、 File system layer 、Page Cache Cache layer 、 Universal Block layer 、IO Scheduling layer 、Block Device drive Dynamic layer 、Block Equipment layer , Here's the picture :

mysql and linux Interaction steps :

​ Here is mysql and llinux The general process of interaction :

  1. VFS layer : When mysql Initiate a random read / write of the data page , once redo log When reading and writing sequentially , In fact, it will put io Request to hand over to linux Of vfs level .
  2. file system :vfs Will be able to io The request shall be operated, read and written according to the corresponding file management system . such as ext1、ext2、ext3
  3. Page cache: The file system will be in page cache The memory based cache finds the required data , be based on Memory cache To perform reading and writing , If not, it will be handed over to block layer
  4. Block layer : Put your comments on the file on the first floor io Request conversion to block io
  5. io Scheduling layer : The default is CFS Fair scheduling algorithm ,io After scheduling , Will decide that io Request to execute that first and then .

CFS Scheduler : A completely fair scheduling algorithm is adopted , Introduce the concept of virtual runtime ;

Deadline Scheduler : Use Red and black trees , Sort processes according to absolute deadlines , Select the smallest process for scheduling

In practice, multiple database requests are executed sql At the same time io, Therefore, it is suggested that the production environment io The scheduling algorithm is replaced by deadline Dispatch , The core idea is any one io You can't wait as long as you want , stay Specified scope He must execute .

  1. Block Device driver layer : At this point, you can execute io Your request will be handed over to block Device driver layer , Finally, give the driver to the equipment layer

  2. Block Equipment layer : The hardware device has completed IO After the read-write operation, either write or read , Finally, the response is returned in reverse order through the above level .

​ Final MySQL You can get this IO The result of a read / write operation , The final structure diagram is as follows :

linux The underlying scheduling process
linux The underlying scheduling process

ERROR 1040(HY000): Too many connections problem

​ Here is a more common linux Related issues .

​ In the first introduction of the column, we know that the database has the concept of connection pool , But the capacity of the connection pool is limited , This error indicates that the connection pool is full .

​ By default, the default connection pool size for each machine is 200, Each station java The system can establish at most 200 A connection , If it is 2 Taiwan machine , most 400 A connection .

​ The key parameters for this setting are max_connections: For example, the maximum number of connections is 800.

actually , During operation, it is usually found that :show variables like 'max_connections' The settings on the line are only 214, What's going on here ? Why? mysql It will reset automatically ? Because linux The number of open handles is 1024 Only... Can be connected 214 individual .

How to solve the above problems

​ stay linux among : Carry out orders Ulimit -HSn 65535 You can modify the configuration of the handle , To verify success , You can view the configuration of the handle through the following command :

Cat /ect/security/limits.conf

Cat /etc/rc.load

​ For the sake of insurance , It can also be in my.cnf The interior of the ensures mac_connections Parameter configuration .

Why is the maximum number of connections 214 Well ?

​ linux By default, you will limit the use of machine resources by each process , Including restrictions on file handles that can be opened , Limit on the number of child processes that can be opened , Limitations of network cache , Maximum lockable memory size .

​ In order to make mysql Make the most of , The setting value of the handle is usually 65535.

 So we can usually use `ulimit` Command to set the amount of resources each process is restricted to use , use ulimit -a You can see the various resources that the process is restricted to use Amount of source, such as core file size The size limit of the dump file when the representative process crashes ,max locked memory Is the maximum locked memory size ,open files Is the maximum number of file handles that can be opened ,max user processes Is the maximum number of child processes you can have .

​ After setting , We need to make sure that the changes are implemented to /etc/security/limits.conf In the document , Permanently set the resource limit of the process, so execute ulimit -HSn 65535 After the command , Use the following command to check whether it has landed in the configuration file .

cat /etc/security/limits.conf as well as cat /etc/rc.local

Raid Preliminary understanding of storage architecture

​ After understanding the system hierarchy, let's talk Raid Storage architecture .

Relationship between process operating system and server

​ mysql To put it bluntly, it is also a programming language , So it also depends on the operating system , He needs to use cpu Memory and hard disk are used for storage .

Disk redundant array is easy to understand :

​ Redundant array of disks , The simplest understanding is to use one hard disk to manage multiple hard disks , Use multiple disks for management at the storage level . So disk RAID What's the use of your hard drive ?

RAID Introduction to hard disk :

  1. Data redundancy mechanism - Backup : When one of the multiple disks hangs up ,RAID The hard disk can redundancy data of one hard disk to other disks .
  2. Multi disk management technology :RAID 0 RAID 1 …....
  3. RAID Card cache : Similar to the main memory mode of the server , Similar to memory SDRAM, It is generally believed that memory based storage .

about RAID Hard disk , There is usually a piece called SDRAM Space , Memory cache used to store the operating system , But what if this memory like thing loses power , In this way, if the data is not refreshed to the hard disk, it will not be lost ? therefore RAID be based on Lithium battery powered Run the disk that writes the cached data to the disk array .

​ However, lithium batteries also need to rest , There is also performance degradation , So you need to be in 30 - 90 Automatically charge and discharge once a day , This can prolong the life of lithium battery and calibrate the battery capacity .

​ Due to the problem of charge and discharge , Usually, when charging and discharging, you need to write the data to the disk rather than to the cache to prevent data loss , But also because of this problem, the performance will fluctuate greatly due to regular charging and discharging , In the actual experience, there are regular online fluctuations in the database .

​ As for how to solve the above problems , Let's put it below , Let's add raid The relationship between .

Raid 0 and raid 1 What's the relationship ?

​ stay raid In the hard drive , There is a problem with a hard disk , It will be in 1 and 0 Form a data redundancy between , One disk is broken, and there will be data on the other .raid Disk means that every two disks form an architecture that mirrors each other , There are three groups , So it will form 3 Data redundancy for group mirroring .


raid Treatment methods for charging and discharging problems of lithium batteries :

  1. Replace lithium batteries with capacitors , Support transparent punching to bring you discharge , But capacitors age easily ,( Not commonly used
  2. Manual charging and discharging , In the low peak period of business , close raid Automatic charging and discharging , Script triggers **( The usual solution )**
  3. Do not turn off when charging and discharging write back, Do not lower the cache level to write through. ( Use with the second strategy )


​ This section also focuses on theory , A brief introduction mysql Disk random write and disk sequential write , And a brief introduction to linux Hierarchical logical structure of the system , Finally, we learned Raid Storage architecture , as well as RAID Details of lithium battery .

At the end

​ This article is also more basic , If you have any comments or suggestions, welcome guidance .

Article history :

Chapter one :Mysql special column - mysql、innodb Storage engine 、binlog workflow - Nuggets (

Second articles :Mysql special column - Online tuning and stress testing - Nuggets (

Last one :Mysql special column - Buffer pool replenishment 、 Data pages 、 Table space description

本文为[A Dong lazy]所创,转载请带上原文链接,感谢

  1. tomcat常见面试题,聊聊你对分布式锁技术方案的理解,
  2. spring源码视频教程,我是如何收割多家大厂offer的?
  3. spring框架教程推荐,2021最新爱奇艺Java社招面试题目,
  4. Spring事務擴展機制,2021大廠Java知識點總結 面試題解析!
  5. Java Development Interview Question with Answers, 2021 Big Factory Java Knowledge point Summary Interview Question Analysis!
  6. Java + SSM Maotai Liquor e - commerce Platform for Computer Graduation DesignVente d'alcool
  7. spring框架教程推薦,2021最新愛奇藝Java社招面試題目,
  8. Recommandé pour le tutoriel Spring Framework, 2021 dernière question d'entrevue d'embauche de la société aiqiyi Java,
  9. Spring source Video tutoriel, comment récolter plusieurs grandes usines?
  10. Springcloud learning notes (I)
  11. Basic knowledge of hive (III) complete collection of functions in hive
  12. spring源碼視頻教程,我是如何收割多家大廠offer的?
  13. 【微信小程序】,看看最新BTA大厂的Java程序员的招聘技术标准,
  14. 【工作经验分享】,2021最新百度、头条等公司Java面试题目,
  15. 【微信小程序】,看看最新BTA大廠的Java程序員的招聘技術標准,
  16. [Widget Wechat] pour voir les dernières normes techniques de recrutement des programmeurs Java de BTA.
  17. Video | Tencent cloud enterprise MySQL (CDB) - dynamic thread pool function
  18. Upgrade your Linux PC hardware using open source tools
  19. RTFM! How to read (and understand) the magic man pages in Linux
  20. [partage d'expérience de travail], 2021 les dernières questions d'entrevue Java de Baidu, Headlines, etc.
  21. Java 16 is coming online. You can't even use the features of Java 8, can you?
  22. Java (3) basic data types and their type conversion
  23. 一位Java大牛的BAT面试心得与经验总结,2021年Java春招面试经历,
  24. 一举拿下腾讯美团滴滴offer,快来瞧瞧这份Spring面试小抄!
  25. 一个Java应届生从上海离职,万字长文总结Java多进程,
  26. Seven design principles of java object-oriented design pattern
  27. 一個Java應届生從上海離職,萬字長文總結Java多進程,
  28. Un nouvel étudiant Java a quitté Shanghai et a résumé plusieurs processus Java.
  29. Prenez d'un coup l'offre de Tencent meituan et jetez un coup d'oeil à cette copie de l'entrevue de printemps!
  30. L'expérience et l'expérience d'un Maverick Java en matière d'entrevue sur les MTD, l'expérience de l'entrevue d'embauche du printemps Java en 2021,
  31. Interface graphique X: mise à jour des tables de rendu et optimisation du Code (compilation intégrée 10)
  32. Widget de vérification de fichiers personnalisés Linux
  33. Java thread pool - work queue
  34. [Part 7] - Summary of B2B2C e-commerce technology of spring cloud live mall managed by git branch
  35. Reflection type of Java Foundation
  36. Want to get started with data structures, but always fall under the pomegranate skirt of the linked list?
  37. Interviewer: do redis transactions satisfy atomicity?
  38. 一篇文章教你搞定计算机网络面试,350道Java面试真题分享,
  39. 一条正确的Java职业生涯规划,揭秘今年Java春招面试必问问题有哪些?
  40. 一名毕业三年的女程序媛面试头条经验,关于Redis主从节点数据过期一致性的思考,
  41. Huawei cloud guassdb (for redis) released a new version: Lua script and SSL connection encryption
  42. Java thread pool - work queue
  43. 一名畢業三年的女程序媛面試頭條經驗,關於Redis主從節點數據過期一致性的思考,
  44. Trois ans d'expérience d'entrevue avec une femme de programmation diplômée, une réflexion sur la cohérence de l'expiration des données de redis Master slave Node,
  45. Un plan de carrière Java correct, découvrez les questions que vous devez poser lors de l'entrevue d'embauche du printemps Java de cette année.
  46. 万字详解微服务的哨兵机制,架构师带你玩转Redis高性能缓存设计实战,
  47. Un article vous a appris à gérer les entrevues sur le Web, à partager 350 vraies questions d'entrevue Java,
  48. Jquery Tools Methodology collation, Sharing a little interview Experience
  49. Tutoriel de développement Java, compétences de base nécessaires pour interviewer un programmeur Java dans une grande usine,
  50. "Anti Mafia storm" has just finished, another criminal investigation play has come, and the supporting actor is more popular than the protagonist
  51. 萬字詳解微服務的哨兵機制,架構師帶你玩轉Redis高性能緩存設計實戰,
  52. L'architecte vous guidera dans la conception de cache haute performance de redis.
  53. Linux驱动开发: 杂项字符设备
  54. Jquery plug - in urianchor, app front end Development
  55. Linux驅動開發: 雜項字符設備
  56. Développement de pilotes Linux: périphériques de caractères divers
  57. Utilisez @ async dans Spring boot pour effectuer des appels asynchrones et accélérer l'exécution des tâches!
  58. $in jquery, Visualized Web Development Tool
  59. Le développement Java doit être fait. Les entrevues https demandent souvent une analyse complète.
  60. 不断提升自己创造溢价的能力,字节跳动 阿里 华为 腾讯等大厂Java面试题,