MYSQL Enterprise common architecture and tuning experience sharing _ MySQL

1. choose PerconaServer, MariaDB, MYSQL1, and Mysql storage engines. MySQL provides two storage engines: MyISAM and InnoDB, and MySQL4 and 5 use the default MyISAM storage engine. From MYSQL55, MySQL has changed the default storage engine from MyISAM to In MYSQL Enterprise common architecture and tuning experience sharing _ MySQL

1. select Percona Server, MariaDB or MYSQL

1. three storage engines of Mysql

MySQL provides two storage engines: MyISAM and InnoDB, MySQL4 and 5 using the default MyISAM storage engine. From MYSQL5.5, MySQL has changed the default storage engine from MyISAM to InnoDB.

MyISAM does not provide transaction support, while InnoDB provides transaction support.

XtraDB is an enhanced version of The InnoDB storage engine. it is designed to better use and update the performance of computer hardware systems. It also includes some new features in high-performance environments.

2. Percona Server branch

Percona Server is released by Percona, a leading MySQL consulting company.

Percona Server is an independent database product that is fully compatible with MySQL and can replace the storage engine with XtraDB without changing the code. Is the closest version to the official MySQL Enterprise release.

Percona provides a high-performance XtraDB engine, a PXC high-availability solution, and a DBA management toolkit such as percona-toolkit,

3. MariaDB

Developed by the founder of MySQL, MariaDB aims to be fully compatible with MySQL, including APIs and command lines, so that MariaDB can easily become a substitute for MySQL.

MariaDB provides a standard storage engine provided by MySQL, namely, MyISAM and InnoDB. XtraDB (named Aria) is used in 10.0.9 to replace InnoDB of MySQL.

4. how to choose

Based on years of experience and performance comparison, Percona branch is preferred, followed by MariaDB. if you don't want to take a risk, select the MYSQL official version.

II. common MYSQL tuning policies

1. hardware layer optimization

Modify server BIOS settings

Select Performance Per Watt Optimized (DAPC) mode to maximize CPU Performance.

Memory Frequency (Memory Frequency) select Maximum Performance (optimal Performance)

Enable Node Interleaving in the memory settings menu to avoid NUMA problems

2. disk I/O problems

SSD hard drive

For disk array storage, it is recommended that CACHE and BBU modules be configured for the array card to significantly improve IOPS.

Select raid 10 instead of raid 5.

3. file system layer optimization

Use the deadline/noop I/O schedulers. do not use cfq.

When using xfs file systems, do not use ext3; ext4 is barely available, but xfs must be used if the business volume is large;

The following options are added to the mount parameter of the file system: noatime, nodiratime, and nobarrier (nobarrier is unique to the xfs file system );

4. kernel parameter optimization

Modify the vm. swappiness parameter to reduce swap usage. RHEL7/centos7 and above are carefully set to 0, OOM may occur

Adjust the kernel parameters of vm. dirty_background_ratio and vm. dirty_ratio to ensure that dirty data can be continuously refreshed to the disk to avoid instant I/O writes. Generate a wait.

Set net. ipv4.tcp _ tw_recycle and net. ipv4.tcp _ tw_reuse to 1 to reduce TIME_WAIT and improve TCP efficiency.

5. Mysql parameter optimization suggestions

We recommend that you set default-storage-engine = InnoDB. we strongly recommend that you do not use the MyISAM engine any more.

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

Set innodb_file_per_table = 1 and use an independent tablespace.

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

Set innodb_log_file_size = 256 M and innodb_log_files_in_group = 2 to meet most application scenarios.

Adjust the settings of max_connection and max_connection_error based on the traffic volume.

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

It is recommended that the key_buffer_size be adjusted to about 32 MB. In addition, it is recommended to disable the query cache.

The mp_table_size and max_heap_table_size values must not be too large. In addition, the values include sort_buffer_size, join_buffer_size, read_buffer_size, and read_rnd_buffer_size.

III. sharing common MYSQL application architectures 1. master-slave replication solution

This is a high-availability solution provided by MySQL itself. The data synchronization method adopts the MySQL replication technology. MySQL replication pulls a binary log file from the server to the master server, and then parses the log file into the corresponding SQL statement to re-execute the operations on the slave server, this method ensures data consistency.

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

2. MMM/MHA high availability solution

MMM provides a scalable script suite for monitoring, failover, and management of MySQL master/master replication configurations. In the MMM high-availability solution, a typical application is the dual-master-slave multi-slave architecture. through MySQL replication technology, two servers can be mutually master-slave, and only one node can be written at any time, this avoids data conflicts between multiple write points. At the same time, when the writable master node fails, the MMM suite can immediately monitor the service, and then automatically switch the service to another master node to continue providing services, so as to achieve high availability of MySQL.

3. Heartbeat/SAN high availability solution

In this solution, the failover processing method is high-availability cluster software Heartbeat, which monitors and manages the networks connected to each node and monitors cluster services, when a node fails or the service is unavailable, the cluster service is automatically started on another node. In terms of data sharing, SAN (Storage Area Network) Storage is used to share data. this solution can achieve 99.990% SLA.

4. Heartbeat/DRBD high availability solution

Heartbeat is still used for failover processing. The difference is that DRBD is used for data sharing.

DRBD is a software-implemented, non-shared, storage and replication solution for image block device content between servers. Unlike the SAN network, it does not share storage, but copies data through the network between servers.

IV. MYSQL Classic application architecture

Where:

Dbm157 is the master database of mysql, dbm158 is the slave database of mysql, and dbs159/160/161 is the slave database of mysql.

MySQL write operations generally use the heartbeat + DRBD + MySQL-based high-availability cluster construction solution. The heartbeat function monitors the status of the mysql master, while the DRBD function synchronizes data from dbm157

Read operations generally use the LVS + Keepalived-based high-availability and high-scalability cluster solution. Front-end AS applications connect to LVS through an improved read VIP, and LVS has keepliaved to form a high-availability mode for mutual backup.

Finally, the Slave node dbs159/160/161 of the mysql master node synchronizes the data of the mysql master through the mysql master-slave replication function. The lvs function is provided to the front-end AS application for read operations and load balancing.

The above is the frequently-used MYSQL architecture and tuning experience sharing _ MySQL content. For more information, see PHP Chinese network (www.php1.cn )!