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

This article mainly gives you a brief talk about Percona Server, MariaDB, MYSQL differences and common tuning MYSQL method analysis, related professional terms you can check online or find some related books to supplement, here will not dabble, we will go straight to Percona Server, MariaDB, MYSQL differences and common tuning MYSQL method analysis topic, I hope to bring you some practical help.

Difference between Percona Server, MariaDB and MYSQL

Mysql three storage engines

MySQL provides two storage engines: MyISAM and InnoDB, MySQL4 and 5 use the default MyISAM storage engine. Starting with MySQL 5.5, MySQL has changed the default storage engine from MyISAM to InnoDB. MyISAM does not provide transaction support, whereas InnoDB does. XtraDB is an enhanced version of the InnoDB storage engine designed to better utilize updated computer hardware systems and includes new features for high-performance environments.

2. Percona Server branch

Percona Server is published by leading MySQL consulting firm Percona.

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

Percona provides a high-performance XtraDB engine, PXC high-availability solutions, and comes with DBA management toolkits such as percona-toolkit.

3、MariaDB

MariaDB was developed by MySQL's founders and is intended to be fully compatible with MySQL, both API and command line, making it an easy replacement for MySQL.

MariaDB provides the standard storage engines provided by MySQL, namely MyISAM and InnoDB, and XtraDB (code-named Aria) has been used since version 10.0.9 to replace MySQL's InnoDB.

4. How to choose

Generally speaking, combined with years of experience and performance comparison, Percona branch is preferred, followed by MariaDB, if you don't want to take a little risk, then choose MYSQL official version.

Common MYSQL tuning strategies

1. Hardware layer related optimization

Modify BIOS settings for Cloud Virtual Machine

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

Memory Frequency Select Maximum Performance

In the Memory Settings menu, enable Node Interleaving to avoid NUMA problems.

2. Disk I/O related

Use SSD hard drive

For disk array storage, it is recommended that the array card be equipped with CACHE and BBU modules at the same time, which can significantly increase IOPS.

Choose RAID 10 instead of RAID 5.

3. File system layer optimization

Use Deadline/Noop I/O schedulers, never cfq

xfs file system, do not use ext3;ext4 barely usable, but heavy traffic, then be sure to use xfs;

Add noatime, noditime, nobarrier to the file system mount parameter (nobarrier is specific to xfs file system).

4. Kernel parameter optimization

Modify vm. swap parameter to reduce swap usage. RHEL7/centos7 and above are carefully set to 0, and OOM may occur. Adjust the vm.dirty_background_ratio, vm.dirty_ratio kernel parameters to ensure that dirty data is continuously flushed to disk and to avoid instantaneous I/O writes. wait for generation. Adjust 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

It is recommended to set default-storage-engine=InnoDB, and it is strongly recommended not to use MyISAM engine again.

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

Set innodb_file_per_table = 1 to use a separate table space.

Adjust innodb_data_file_path = ibdata1:1G:autoextend instead of the default 10M. In high concurrency scenarios, performance will be greatly improved.

Set innodb_log_file_size=256M, set innodb_log_files_in_group=2, which can basically meet most application scenarios.

Adjust max_connection (maximum number of connections) and max_connection_error (maximum number of errors) settings according to traffic volume.

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. It is also recommended to close 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 settings should not be too large.

Percona Server, MariaDB, MYSQL differences and common tuning MYSQL method analysis will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section content captures some industry news and expertise to share with you every day.

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.