MySQL Enterprise common architecture and tuning experience sharing

One, choose Percona Server, mariadb or MySQL

1, MySQL three kinds of storage engine

MySQL provides two storage engines: MyISAM and Innodb,mysql4, and 5 using the default MyISAM storage engine. Starting with MYSQL5.5, MySQL has changed the default storage engine from MyISAM to InnoDB.

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

XTRADB is an enhanced version of the InnoDB storage engine, designed to better use the performance of updating computer hardware systems, while also incorporating some new features in high-performance environments.

2. Percona Server Branch

Percona Server was released by Percona, a leading MySQL consulting firm.

Percona Server is a standalone database product that can be fully compatible with MySQL and can replace the storage engine to xtradb without changing the code. is the closest edition to the official MySQL enterprise release.

The Percona provides a high-performance xtradb engine, a PXC high-availability solution, and a DBA management toolbox such as Percona-toolkit,

3, MariaDB

MARIADB, developed by the founder of MySQL, MARIADB is designed to be fully compatible with MySQL, including APIs and command lines, making it easy to be a replacement for MySQL.

MARIADB provides the standard storage engine provided by MySQL, the MyISAM and innodb,10.0.9 versions, using XTRADB (named aria) instead of MySQL InnoDB.

4. How to Choose

Combined with years of experience and performance comparisons, the preferred Percona branch, followed by MARIADB, if you don't want to take a risk, choose the official MySQL version.

Second, the common MySQL tuning strategy

1, hardware layer related optimization

Modify Server BIOS settings

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

Memory Frequency (RAM frequency) Select Maximum performance (best performance)

In the Memory settings menu, enable node interleaving to avoid NUMA issues

2. Disk I/O related

Using SSD drives

In the case of disk array storage, it is recommended that the array card be equipped with both a cache and a BBU module to significantly increase IOPS.

RAID level try to choose RAID10 instead of RAID5.

3. File System Layer Optimization

Use the Deadline/noop two I/O schedulers, and never use CFQ

Use XFS file system, do not use EXT3;EXT4 reluctantly, but the volume of business is very large, you must use XFS;

File system mount parameter added: Noatime, Nodiratime, nobarrier several options (Nobarrier is XFS file system specific);

4. Kernel parameter optimization

Modify the vm.swappiness parameter to reduce swap utilization. Rhel7/centos7 above is carefully set to 0, may occur oom

Adjust the vm.dirty_background_ratio, vm.dirty_ratio kernel parameters to ensure that dirty data is continuously flushed to disk, avoiding instantaneous I/O writes. Produce wait.

Adjust net.ipv4.tcp_tw_recycle, net.ipv4.tcp_tw_reuse are set to 1, reduce time_wait, improve TCP efficiency.

5, MySQL parameter optimization recommendations

It is recommended to set DEFAULT-STORAGE-ENGINE=INNODB, it is strongly recommended not to use the MyISAM engine.

Adjust the size of the innodb_buffer_pool_size, if it is a single instance and most of the InnoDB engine table, consider setting it to about 50%-70% of physical memory.

Set innodb_file_per_table = 1, using a stand-alone tablespace.

Adjust Innodb_data_file_path = Ibdata1:1g:autoextend, do not use the default 10M, in high concurrency scenarios, performance will be greatly improved.

Setting up the innodb_log_file_size=256m, setting the innodb_log_files_in_group=2, basically satisfies most of the application scenarios.

Adjust the max_connection (maximum number of connections), Max_connection_error (maximum number of errors) settings, set according to the size of the business volume.

In addition, Open_files_limit, Innodb_open_files, Table_open_cache, Table_definition_cache can be set about 10 times times the size of max_connection.

Key_buffer_size recommended small, 32M or so, it is also recommended to close the query cache.

Mp_table_size and max_heap_table_size set not too large, in addition Sort_buffer_size, Join_buffer_size, Read_buffer_size, Read_rnd_buffer_ Size and so on are not too large.

Third,MysqlCommon Application Architecture sharing1, master-slave replication Solutions

this is MySQL itself provides a highly available solution, 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 from the server, in this way to ensure the consistency of the data.

In order to achieve higher availability, in the actual application environment, it is generally used MySQL replication technology with high-availability cluster software keepalived to achieve automatic failover, this way can achieve 95.000% SLA.

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M01/7D/68/wKioL1bnzqXTpra9AAA4W5Syh94610.jpg "title=" 11.jpg "alt=" Wkiol1bnzqxtpra9aaa4w5syh94610.jpg "/>

2,Mmm/mhaHighly Available Solutions

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 dual master multi-slave architecture, through the MySQL replication technology can realize two servers mutual primary from, and at any time only one node can be written, avoids the multi-point writes the data conflict. At the same time, when a writable master node fails,the MMM Suite can be immediately monitored, then automatically switch the service to the other master node, continue to provide services, to achieve high availability of MySQL.

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M00/7D/6B/wKiom1bnzinxyNbJAAAwafwrvME471.jpg "title=" 22.jpg "alt=" Wkiom1bnzinxynbjaaawafwrvme471.jpg "/>

3,Heartbeat/sanHighly Available Solutions

In this scenario, the failover is handled in a highly available cluster software Heartbeat, which monitors and manages the networks connected between nodes and monitors the Cluster service, when a node fails or the service is unavailable, Automatically start the Cluster service on other nodes. In terms of data sharing , data is shared through SAN(Storage area Network) storage, a scenario that enables 99.990% SLAs.

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M02/7D/68/wKioL1bnzsbSfpPAAAAtC9WNeBM583.jpg "title=" 33.jpg "alt=" Wkiol1bnzsbsfppaaaatc9wnebm583.jpg "/>

4,Heartbeat/drbdHighly Available Solutions

This scheme still uses Heartbeat in the way of handling failover, but it is implemented byusing the block-level data synchronization software DRBD in data sharing .

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

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M00/7D/6B/wKiom1bnzkrB-vhLAAAxkEsaZh8093.jpg "title=" 44.jpg "alt=" Wkiom1bnzkrb-vhlaaaxkesazh8093.jpg "/>

Four,MysqlClassic Application Architecture

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M00/7D/68/wKioL1bnzuahF40iAABcNZGGaqI867.jpg "title=" 55.jpg "alt=" Wkiol1bnzuahf40iaabcnzggaqi867.jpg "/>

which

Dbm157 is the MySQL master,dbm158 is the MySQL master's standby machine,dbs159/160/161 is MySQL from.

MySQL write operations typically use a heartbeat+drbd+mysql-based approach to building high-availability clusters. the MySQL master is monitored for status by heartbeat, while DRBD implements dbm157 data synchronization to dbm158.

read operations are commonly used based on Lvs+keepalived to build high-availability, high-scale cluster solutions. Front-end as applications through improved read VIP connection LVs,LVs have keepliaved made of high-availability mode, to achieve mutual preparation.

Finally,the MySQL master's slave node dbs159/160/161 synchronizes MySQL master data via the master- slave copy function via the LVS functionality provided to the front end as applications for read operations and load balancing.

This article is from the "South African Ant Linux" blog, make sure to keep this source http://ixdba.blog.51cto.com/2895551/1751377

MySQL Enterprise common architecture and tuning experience sharing