Shulou(Shulou.com)05/31 Report--

What this article shares with you is about the common architecture design of MySQL database. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

1. MySQL engine

MySQL provides two storage engines: MyISAM and InnoDB,MySQL4 and 5 use the default MyISAM storage engine. Starting with MySQL5.5, MySQL has changed the default storage engine from MyISAM to InnoDB.

MyISAM does not provide transaction support, while InnoDB does.

Second, commonly used MySQL tuning strategies

1. Hardware layer related optimization

Modify server BIOS settings

Choose Performance Per Watt Optimized (DAPC) mode to play CPU*** performance.

Memory Frequency (memory frequency) Select Maximum Performance (* performance)

In the memory settings menu, enable Node Interleaving to avoid NUMA problems

2. Disk Imax O-related

Use a SSD hard drive

For disk array storage, it is recommended that array cards be equipped with both CACHE and BBU modules, which can significantly improve IOPS.

At the raid level, try to choose raid10 instead of raid5.

3. File system layer optimization

Use deadline/noop, both of which are Imax O schedulers, and never use cfq.

Using the xfs file system, do not use ext3;ext4 barely available, but if the volume of business is large, be sure to use the xfs; file system mount parameter to add: noatime, nodiratime, nobarrier several options (nobarrier is unique to the xfs file system).

4. Kernel parameter optimization

Modify vm.swappiness parameters to reduce swap utilization. RHEL7/CentOS7 above is carefully set to 0, and OOM may occur.

Adjust the vm.dirty_background_ratio and vm.dirty_ratio kernel parameters to ensure that dirty data can be continuously flushed to disk to avoid instant write. Wait.

Adjust net.ipv4.tcp_tw_recycle and net.ipv4.tcp_tw_reuse to 1 to reduce TIME_WAIT and improve TCP efficiency.

5. Suggestions for optimizing MySQL parameters.

It is recommended to set up default-storage-engine=InnoDB, and it is strongly recommended that you no longer use the MyISAM engine.

To resize the innodb_buffer_pool_size, if it is a single instance and the vast majority are InnoDB engine tables, consider setting it to about 50%-70% of the physical memory.

Set innodb_file_per_table = 1 to use independent tablespaces.

Adjust innodb_data_file_path = ibdata1:1G:autoextend, do not use the default 10m, in high concurrency scenarios, performance will be greatly improved.

Setting innodb_log_file_size=256M and innodb_log_files_in_group=2 can basically meet most application scenarios.

Adjust the max_connection (* connections) and max_connection_error (* errors) settings, and set them according to the volume of business.

In addition, open_files_limit, innodb_open_files, table_open_cache, table_definition_cache can be set to about 10 times the size of max_connection.

Key_buffer_size is recommended to be reduced to about 32m, and it is also recommended to turn off query cache.

Mp_table_size and max_heap_table_size settings should not be too large, and sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size and other settings should not be too large.

III. Sharing of common application architectures in MySQL

1. Master-slave replication solution

This is a highly available solution provided by MySQL itself, and the data synchronization method uses MySQL replication technology. MySQL replication is to pull the binary log file from the server to the master server, and then parse the log file into the corresponding SQL to re-perform the operation of the master server on the slave server to ensure the consistency of the data.

In order to achieve higher availability, in the actual application environment, MySQL replication technology and high availability cluster software keepalived are generally used to achieve automatic failover, which can achieve 95.000% SLA.

2. MMM/MHA highly available solution

MMM provides a scalable suite of scripts for monitoring, failover, and management of MySQL master replication configurations. In the MMM high availability scheme, the typical application is the double master and multi-slave architecture. Through the MySQL replication technology, the two servers can be master and slave to each other, and only one node can be written at any time, which avoids the data conflict of multiple writes. At the same time, when the writable master node fails, the MMM suite can monitor it immediately, and then automatically switch the service to another master node to continue to provide services, thus achieving high availability of MySQL.

3. Heartbeat/SAN highly available solution

In this scheme, the way to deal with failover is the highly available cluster software Heartbeat, which monitors and manages the network connected between each node, and monitors the cluster service, and automatically starts the cluster service in other nodes when the node fails or the service is unavailable. In terms of data sharing, data is shared through SAN (Storage Area Network) storage, which can achieve 99.990% SLA.

4. Heartbeat/DRBD highly available solution

This scheme still uses Heartbeat in the way of dealing with failover, but in the aspect of data sharing, it uses the data synchronization software DRBD based on block level.

DRBD is a software-implemented, non-shared storage replication solution that mirrors the content of block devices between servers. Unlike the SAN network, it does not share storage, but replicates data over a network between servers.

IV. MySQL classic application architecture

Where:

Dbm157 is the MySQL master, dbm158 is the standby for the MySQL master, and dbs159/160/161 is the MySQL slave.

MySQL write operations generally adopt the scheme of building highly available clusters based on heartbeat+DRBD+MySQL. The state monitoring of the MySQL master is realized by heartbeat, while the dbm157 data is synchronized to dbm158 by DRBD.

The read operation generally adopts the scheme of building high availability and high expansion cluster based on LVS+Keepalived. The front-end AS application makes high availability mode through improved read VIP connection LVS,LVS and keepliaved to achieve mutual backup.

* dbs159/160/161, the slave node of the MySQL master, synchronizes the data of the MySQL master through the MySQL master-slave replication feature, provides read operations to the front-end AS applications through the lvs feature, and implements load balancing.

The above are the common architectural designs of MySQL database, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.