Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to choose Percona Server, MariaDB and MYSQL. The content of the article is carefully selected and edited by the author. How to choose Percona Server, MariaDB and MYSQL has a certain pertinence, which is of great reference significance to everyone. Let's work with the author to understand the next topic.

1. Mysql three storage engines

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.

XtraDB is an enhanced version of the InnoDB storage engine designed to better use and update the performance of computer hardware systems, as well as some new features in high-performance environments.

2. Percona Server branch

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

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

Percona provides a high-performance XtraDB engine, a highly available PXC solution, and comes with DBA management toolkits such as percona-toolkit

3 、 MariaDB

MariaDB was developed by the founder of MySQL, and MariaDB is designed to be fully compatible with MySQL, including API and the command line, making it an easy replacement for MySQL.

MariaDB provides a standard storage engine provided by MySQL, that is, MyISAM and InnoDB,10.0.9 versions use XtraDB (code name Aria) instead of MySQL's InnoDB.

4. How to choose

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

Second, commonly used MYSQL tuning strategies

1. Hardware layer related optimization

Modify BIOS settings of CVM

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

Memory Frequency (memory Frequency) Select Maximum Performance (Best 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.

Do not use ext3;ext4 to use the xfs file system, but if you have a large amount of business, you must use xfs.

Added to the file system mount parameter: noatime, nodiratime, nobarrier several options (nobarrier is specific to xfs file system)

4. Kernel parameter optimization

Modify vm.swappiness parameters to reduce swap utilization. If RHEL7/centos7 is above, set it to 0 carefully. 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 (maximum number of connections) and max_connection_error (maximum number of 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.

Third, MYSQL common application architecture sharing 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 binary log files from the CVM to the master CVM, and then parse the log files into the corresponding SQL to re-perform the operation of the master CVM on the slave CVM to ensure data consistency.

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 solution, the typical application is dual-master and multi-slave architecture. Through MySQL replication technology, two CVMs 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. 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 and replication solution that mirrors the content of block devices between cloud servers. Unlike the SAN network, it does not share storage, but replicates data through the network between cloud 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.

Finally, the mysql master slave node dbs159/160/161 synchronizes the mysql master data through the mysql master-slave replication function, provides the front-end AS application with read operation through the lvs function, and realizes load balancing.

(2) the explanation of the common configuration of mysql.

Skip-external-locking

In some systems, the lockd lock manager may not work properly, and you can use skip-external-locking to tell mysqld not to use external locks. (this means you can't run two mysqld CVMs in the same data directory, and you need to pay special attention if you use the same memory configuration)

After the Mysql4.0 version, external locking is disabled by default, while external locking (system locking) is enabled, which can easily cause mysqld deadlocks if the lockd on the system does not work fully (such as Linux systems).

You can explicitly disable external locking by configuring skip-external-locking. External locking only affects access to the MyISAM table, which is the storage engine.

Key_buffer_size = 16m

This parameter means that the MyISAM table is used for the buffer size of the index block and can be shared by all threads. The default value is 8388608 (that is, 8m). It is the parameter that has the greatest impact on the performance of the MyISAM table. If the database storage engine is of type InnoDB, this parameter is invalid. You can increase this value so that the index can better handle all read and write operations.

Here are two related articles worth reading: mysql optimization: the key_buffer_size parameter of Key_buffer_size MySQL

Max_allowed_packet = 1m

Represents the maximum size of a packet, or any generated / intermediate string, or parameters passed by any mysql_stmt_send_long_data () API function. The buffer of the packet message is initialized to net_buffer_length bytes (the net_buffer_length default is 16384:16KB, and the maximum can be set to 1048576 max_allowed_packet 1m), but it can grow to max_allowed_packet bytes when needed. The default value of this parameter is 4m. Errors may occur when receiving some large packets, and the maximum can be set to 1GB. When you change the size of the message buffer by modifying the value of this variable, it is recommended that you also modify the buffer size on the client side if the client program allows it. In the client library, the default max_allowed_packet is 1GB, but individual clients may override this value, for example, mysql and mysqldump are 16 MB and 24 MB, respectively. You can change the client's value by setting it on the command line or by modifying the max_allowed_packet parameter in the configuration file, and note that at the session session level, this variable is read-only.

Table_open_cache = 64

Indicates the number of open tables for all threads. Increasing this value increases the number of file descriptors required by mysqld? You can determine whether you need to increase the number of table caches by checking the Opened_tables status variable (Opened_tables represents the number of tables that have been opened). If this value is large and you do not use FLUSH TABLES very often (this command forces all tables to be closed and reopened), you can increase the value of table_open_cache.

The table_open_cache and max_connections system variables affect the maximum number of file cloud servers that remain open. If you increase one or two of these values, you may exceed the limit on the number of file descriptors opened by each process on the operating system. Many operating systems allow you to increase this limit, but you also need to determine whether it is possible for the operating system to increase the limit on opening files and how to do so.

The value of table_open_cache is related to the value of max_connections. For example, for 200 concurrently running connections, specify a value of at least 200N for table_open_cache, where N is the maximum number of associated tables in any join that participates in executing the query. You must also reserve some additional file descriptors for temporary tables and files.

Make sure that your operating system can handle the number of implicitly opened file descriptors set by table_open_cache. If the table_open_ cache value is set too high, MYSQL may run out of file descriptors and reject the connection, unable to execute the query, and become unreliable. It is also important to consider that in the MyISAM storage engine, each unique open table requires two file descriptors. You can increase the number of file descriptors available to mysqld by configuring the open-files-limit parameter in the startup options of MySQL.

Sort_buffer_size = 512K

Indicates that a specified cache size is allocated for each session that needs to be sorted. The value of sort_buffer_size is not specific to any storage engine, it applies to the general way of optimization. Through the SHOW GLOBAL STATUS command, if you find a lot of Sort_merge_passes output per second (Sort_merge_passes represents the number of merge sorting algorithms that have to be done), we need to consider increasing the value of sort_buffer_size to speed up the query performance of operations such as order by or group by (the query efficiency of order by and group by is difficult to provide efficiency by optimizing the query and improving the index).

The optimizer will try to figure out how much space is necessary, but can allocate more until the limit is reached. It is worth noting that if the global setting is greater than the value required by the system, it will slow down the efficiency of most queries involving sorting. It is best to increase its value at the session session level, and the value is for those session sessions that need to increase sort_buffer_size. In Linux systems, there are thresholds for 256KB and 2MB, where larger values can significantly slow down memory allocation, so you should consider the smaller values.

Net_buffer_length = 8K

Each client thread is associated with a connection connection buffer and a result buffer, with a default value of 16K. Both initial sizes are net_buffer_length, but will dynamically expand to the size set by max_allowed_packet as needed, and the resulting buffer will shrink to the set net_buffer_length after each sql statement is executed.

This variable should not normally be changed, but if your system memory is small, you can set it to the desired length of the client statement. If the length of the statement exceeds this value, the connection connection buffer automatically expands. The maximum value of the net_buffer_length parameter can be set to 1m. It is important to note that at the session session level, this variable is read-only.

Read_buffer_size = 256K

Each thread of a sequentially scanned MyISAM table allocates a buffer of a specified size to each table it scans. If you need to do a lot of sequential scans, you may increase this value, which defaults to 131072 (128K). The value of this variable should be a multiple of 4KB. If it is set to a multiple that is not 4KB, its value will be rounded to the nearest multiple of 4KB.

This parameter applies to all search engines in the following situations:

The cached index is in a temporary file (not a temporary table) when rows are sorted using ORDER BY.

When performing a partition bulk insert operation.

Cache the results for nested queries.

If you use another storage engine, you need to determine the memory block size for the MEMORY table. The maximum allowed for read_buffer_size is set to 2GB.

Read_rnd_buffer_size = 512K

This variable is used for multi-range read optimizations, including MyISAM tables and any storage engine. When a row is read from a key sorting operation of a MyISAM table with a sort operation, the row is read through the buffer to avoid disk seek. Setting this value to a higher value can significantly improve the performance of ORDER BY operations, however, this is the buffer allocated to each client, so it should not be set to a larger value at the global level. In contrast, increasing the value of this variable at the session session level is recommended only for clients that require a large number of query operations. The maximum allowed for read_rnd_buffer_size is set to 2GB.

Myisam_sort_buffer_size = 8m

Represents the buffer size allocated when MyISAM index sorting is performed on REPAIR TABLE, or when indexes are created through CREATE INDEX or ALTER TABLE. Accordingly, for the InnoDB engine, there is a setting for InnoDB_sort_buffer_size.

Query_cache_size= 8M

Represents the amount of memory allocated to cached query results. Query caching is disabled by default. This is used by using the default query_cache_size of 1m query cache type 0 (0 means query caching is not enabled), which significantly reduces overhead, because if you set query_cache_size to 0, you also need to set query_cache_type to 0 at startup.

The value allowed to be set is a multiple of 1024, and other settings are rounded to the nearest value. It is important to note that memory is allocated by default even if the query_cache_type is set to 0 bytes of memory queryquery cachesize.

The query cache requires an allocation structure with a minimum size of about 40 kb (depending on the system structure). If the query_cache_size set is too small, some problems may also arise.

The values of query_cache_type are 0, 1, and 2. 0 means no query caching; 1 means caching all cacheable query results except those that start with SELECT SQL_NO_CACHE; 2 means only query results starting with SELECT SQL_CACHE are cached. MySQL official doc is recommended to be set to 2.

Thread_cache_size = 20

Indicates that the number of threads reused by the CVM will be cached. When a client disconnects, if the number of threads in the cache is less than the set thread_cache_size, then the client thread will be put into the cache. The requesting thread, if possible, will be removed from the cache, and a new thread will be created when the cache is empty. Increasing the value of this variable can improve performance if there are many new connections in the system. In general, if you do a good threading implementation in your code, this performance improvement is not significant. However, if your cloud server has hundreds of connections per second, you should usually set the thread_cache_size high enough that most new connections will use cached threads. You can see the efficiency of thread caching by comparing this variable with the state variables Connections (which represents the number of attempts to connect to the Mysql cloud server (regardless of whether the connection is successful or not) and Threads_created (which represents the number of threads created to process connection connections).

The default value of this variable is calculated according to the following formula, with a cap of 100 max_connections / 8 +, but this variable has no effect on embedded cloud servers (libmysqld), and this parameter is no longer visible after MySQL version 5.7.2.

Log-bin=mysql-bin

Indicates that binary logging is enabled, and the cloud server records all binary logs that change data statements for backup and replication.

The transaction isolation level in MySQL before binlog_format=mixed is learned from a little understanding of binlog_format, so I won't repeat it here.

Innodb_flush_log_at_trx_commit = 2

The official definition of this variable is: Controls the balance between strict ACID compliance for commit operations,and higher performance that is possible when commit-related I big O operations are rearranged and done in batches. My own understanding is to control the balance between two relationships: the ACID feature of strict commit operations, the high performance that may occur when submission-related IO operations are rearranged in batches and completed. You can achieve better performance by changing the default value of this variable, but you may lose a second of the transaction in the event of an unexpected crash.

The default value of 1 is fully consistent with the database ACID feature, which means that each time a transaction commits, the contents of the InnoDB log buffer are written to the log file and the log file is flushed to disk.

If the variable value is 0, it means that the contents of the InnoDB log buffer are written to the log file about once per second, and the log file is flushed to disk. What is not written in the log buffer is written to the log file when the transaction commits. Due to process scheduling problems, refresh per second is not 100% guaranteed to occur every second. Since disk refresh occurs only about once a second, you will lose a second of transactions when any mysqld process crashes.

A variable value of 2 means that the contents of the InnoDB log buffer are written to the log file when the transaction commits, and the log file is flushed to disk about once per second. Similarly, due to process scheduling problems, refresh per second is not 100% guaranteed to occur every second. Since disk refresh occurs only about once a second, you will lose one second of transaction data in the event of an operating system crash or a sudden power outage.

In MySQL version 5.6.6, the InnoDB log refresh rate is controlled by the variable innodb_flush_log_at_timeout, which allows you to set the log refresh rate to N seconds (the default is 1, you can set an integer value between 1 and 2700), but any crash of the mysqld process will erase up to N seconds of transaction data.

DDL changes and other internal InnoDB activities are independent innodb_flush_log_at_trx_commit settings for InnoDB log refresh.

InnoDB's crash recovery mechanism is that transactions are either applied or deleted regardless of the setting of the variable innodb_flush_log_at_trx_commit.

According to the persistence and consistency of database application settings, it is recommended to refer to the following ways to set InnoDB transactions:

If binary logging is enabled, set sync_binlog=1.

Always set innodb_flush_log_at_trx_commit=1.

The reason for this suggestion is that many operating systems and some disk hardware fool the flush to disk operation by telling mysqld that the refresh operation has occurred, but it has not. Then even if the persistence of the transaction is set to 1, it cannot be guaranteed. In the worst case, a sudden power outage can even cause damage to InnoDB data. Using a battery-supported disk cache on a SCSI disk controller or on a disk that accelerates file refresh itself makes the operation more secure. You can also try using the Unix command hdparm to disable hardware caching for disk write caching, or use other commands provided by specific hardware vendors.

Sync_binlog

If the value of this variable is greater than 0, sync_binlog MySQL CVM will use the fdatasync () command to synchronize the binary log to disk after the MySQL commit group is written to the binary log. The default value of the sync_binlog variable is 0, which means that it is not synchronized to disk. Mysql CVM relies on the operating system to refresh the contents of binary files from time to time for any other file. A value of 1 is the safest option, because in the event of a crash, you can lose at most one commit group from the binary log. However, it is also the slowest choice (unless you have a battery backup cache on your disk, which makes synchronization very fast).

Innodb_lock_wait_timeout = 20

Represents the length of time that an InnoDB transaction waits for a row lock to be abandoned, with a default value of 50 seconds. A transaction attempts to get the maximum amount of time that a row locked by another InnoDB transaction waits, and the timeout sends the following error message:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction (lock wait timeout, attempt to restart transaction)

When the lock wait times out, the current statement is rolled back (not the entire transaction). If you need to roll back the entire transaction, you need to set the parameter innodb_rollback_on_timeout when the CVM starts.

In highly interactive applications or OTLP systems, you may reduce the value of this variable for better user feedback or to put updates in a queue for subsequent processing. You may increase this value for long-running back-end operations, such as when there are a large number of inserts or updates waiting to be completed in a data warehouse.

Innodb_lock_wait_timeout applies only to row-level locks for InnoDB. An MySQL table lock does not occur in InnoDB, and this parameter does not apply to waiting for a table lock.

The lock wait timeout value does not apply to deadlocks because when a transaction deadlock occurs, InnoDB immediately detects them and the transaction rolls back.

Innodb_lock_wait_timeout can be set at run time through a SET GLOBAL or SET SESSION declaration. Modifying global settings requires SUPER permissions and affects all subsequent operations to connect clients. Any client can set innodb_lock_wait_timeout at the SESSION session level, and it only affects that client.

So far, all the configuration file parameters on the online mysql CVM have been sorted out, and we have some understanding and understanding of these parameters. Next, we will sort out the frequently used connections variables to prepare for the next discussion with colleagues on the optimization and settings of mysql, mainly the following connections variables:

Max_connections

The system variable, which represents the maximum number of concurrent client connections allowed, will affect the number of threads running on the cloud server. The default value is 151. increasing this value will increase the number of file descriptors requested by mysqld. If the number of descriptors requested is not available, the cloud server reduces the value of max_connections. The connection is rejected because the maximum value of max_connections reaches the increment of the Connection_errors_max_connections state variable.

The default value of the thread_cache_size variable is related to max_connections.

Max_user_connections

Represents the maximum number of simultaneous connections allowed for any given MySQL user account. The default value of 0 means there is no limit. This variable can be set to a global value when the CVM starts or runs. It also has a read-only session value that represents the limit value for valid simultaneous connections of the account associated with the current session. The session-level max_user_connections is initialized as follows:

If the user account has a non-zero MAX_USER_CONNECTIONS resource limit (the account's resource limit is specified by the GRANT statement), the session-level MAX_USER_CONNECTIONS value is set to that limit.

Otherwise, the session-level MAX_USER_CONNECTIONS value will be set to the global value.

Connection_errors_max_connections

Indicates the number of connections rejected when the number of connections in the CVM reaches the limit of max_connections.

Connections

Indicates the number of attempts to connect to the mysql CVM, regardless of success or failure.

Max_used_connections

The maximum number of connections that have been used at the same time since the start of the CVM.

Skip-external-locking action

In the release version of Mysql Linux, there is a line of skip-external-locking by default, which means to skip external locking. In contrast, External-locking variable means to enable external locking, which is used to lock MyISAM tables under multithreaded conditions. External locking is disabled by default. In real production, our business environment is a single-CVM environment, so external locking is not required.

Key_buffer_size is not valid in InnoDB engine

In the real production environment, we have upgraded the business library. Although our business library uses the InnoDB engine, there are still several tables left over using the MyISAM storage engine. Setting this parameter is also to provide access performance for these tables, and the index for these tables can better handle read and write operations. In addition, when mysql 5.5 was installed on windows, several my.ini files for different production environments were generated by default, and the configuration of this parameter is also referred to some of these configurations.

Whether the table_open_cache=64 setting is too small

The setting of this parameter is best based on the actual production environment. Two important parameters can be found through show global status like 'open%_tables%', on the mysql command line, as follows:

+-+ +

| | Variable_name | Value |

+-+ +

| | Open_tables | 81 | |

| | Opened_tables | 88 | |

+-+ +

For most CVM settings, it is recommended to refer to the formula: Open_tables / Opened_tables > = 0.85 position OpenSecretLes / table_open_cache

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.