MySQL New Version Selection

In the early days, the company mainly used the mysql5.5 version. This year we built the database configuration center and mainly promoted the mysql5.6 version, which has both performance and functions. With certain improvements, mysql5.6 can also support gtid, but it cannot switch between gtid mode and normal mode online. At the same time, the synchronization performance of 5.6 is still unsatisfactory, and it can only start parallel replication in the case of multiple DBs. It is difficult to have such a guarantee in business, so once the write operation is intensive, slow synchronization will be a serious problem;

So, I have been considering upgrading MySQL recently, and the first thing I face when upgrading MySQL is The problem is to choose a suitable version. First of all, we consider using mysql5.7. 5.7 has been released in multiple official versions this year. It is currently used in a wide range of applications and can be considered for use in a formal environment. So we conducted an online comparison test and found that there was a big gap in performance between mysql5.7 and our online version of mysql5.6 (perhaps it was because some parameters were not adjusted well, 5.7 is indeed much more complicated).

At the same time, the company has frequently seen some log storage recently. Most of them use the innodb engine, which is a waste of capacity. On the other hand, because our company’s standard mysql server capacity is about 1.3T, for some large For businesses with capacity requirements, there are also bottlenecks in capacity. If you want to retain data for a long time, it will be difficult to meet the demand. So we take this opportunity to consider this together. Currently, both Percona and Mariadb support Tokudb, and Mariadb 10.2 or 10.3 is also planning to support Myrocks.

So I decided to do a comparative test and chose Percona 5.7.14, Mariadb 10.1.18 and our online MySQL 5.6 for comparative testing and passed the stress test.

First of all, the Innodb engine is used:

The test results of Mariadb and MySQL5.6 are close

The performance results of Percona 5.7.14 and the official MySQL5.7 are similar, compared with There is a certain gap since MySQL 5.6 (removing performance_schema is better, but there is still a gap).

There is a gap between the test results using the Tokudb engine and the official claims. When using snappy compression, insert is about 1/4 slower than innodb, and update is only about half of innodb. Percona's performance is worse, so it will not be considered.

Finally selected Mariadb 10.1.18, and deployed a business online. After slowly trying out this business, it was gradually promoted.

Some pitfalls encountered when using Mariadb

In the process of using Mariadb, I encountered many problems. Here I mainly mention the two larger problems I encountered for your reference. :

1. Synchronization performance issues:

Our business peak period reached more than 9,000 write operations/second. The first problem we faced was that the synchronization performance could not keep up. On, the number of slave synchronization threads has been increased to 16 threads, which can barely catch up. However, once the database is stopped for a while, it may face the possibility of never being able to catch up. When I was almost desperate, I read the official article of mariadb (https://mariadb.com/kb/en/mariadb/parallel-replication/). Mariadb’s parallel replication supports several modes, including in-order and There are two types of out-of-order, but our business supports in-order, so out-of-order is not considered. In in-order mode, two types are supported: Conservative and Optimistic. By default, Conservative, this Parallel mode will strictly ensure the order of things, which is probably similar to the principle of group commit in 5.7; while in Optimistic mode, as many sessions as possible will be started during replication, and conflicts will not be handled until conflicts are discovered. I tried Optimistic decisively and it was very powerful, with a maximum synchronization speed of 14,000 times/second.

2. "Memory leak"

The system deployment structure is: two MariaDBs are made into master-master replication, and a self-developed distributed database is deployed in front, and the business side is connected to the distributed database. Database process. After the system was online for a few days, it was found that the main database would hang up inexplicably. Fortunately, there is a distributed database and it will automatically switch. If the MariaDB main database hangs up, it will automatically switch to another main database without the business side noticing. Checking the kernel log, I found that it was OOM and the kernel killed MySQL.

So I started various attempts, removed the Tokudb engine configuration, and changed to Mariadb 10.1.19. I tried all of them, but eventually the main database hung up. By chance, I stopped the slave on the main library, and found that the memory of the main library suddenly dropped a lot, and the memory no longer increased. However, once I started the slave on the main library, I found that the memory gradually increased again. This phenomenon is very similar to the memory allocation mechanism in the mysql thread (mem_root-based memory allocation, all will be released when the thread is stopped), so it is initially suspected that this is the cause. I found that as the other MariaDB in the dual master, there will be no memory increase problem.

After discovering the above phenomenon, we started debugging the code. Use gdb to start one mariadb, and the other one with ordinary commands. These two libraries are made into dual masters:

The first method Situation: When testing as a slave library, the received binlog events

Insert a row of data on mariadb started by a common command, and the order of gdb to view the received events is as follows:

### i ) Gtid_log_event
### ii) Table_map_log_event
### iii) Write_rows_log_event
### iv) Xid_log_event

No. Two situations: When testing as the main library, the binlog event received

在gdb启动的mariadb上插入一行记录，然后gdb观察接收到的事件为：

### 1）Rotate_log_event
### 2）Gtid_list_log_event
### 3）Rotate_log_event

Rotate_log_event事件是虚拟出来的，用于让主库跟上从库的同步位置，这基本上是一个空事件，没有做任何处理，所以初步怀疑是在处理Gtid_list_log_event事件的时候，出现了问题。

反复查看Gtid_list_log_event::do_appy_event函数中的调用情况，发现确实有些方法会调用thd->alloc来分配内存，但是没有回收，所以造成内存不断的增大，我考虑了一下，因为是主库对于同步性能要求也不高，所以在Gtid_list_log_event::do_apply_event函数的最后加了一行代码：free_root(thd->mem_root, MYF(MY_KEEP_PREALLOC));  重新编译后，跑了一天，内存终于稳定了。

由于目前发现只有主库有该事件，主库同步处理性能要求不高，所以暂时先这样用着了。不知道mariadb官方版本什么时候会优化一下。

总体来看，Mariadb还是比较适合我们公司的，它有最新的功能、特性能够给我们提供很多解决方案。Tokudb可以解决日志型存储的问题；连接池可以解决大量连接情况下性能地下的问题；审计插件提供安全方面的审核；slave并发模式能够提供高性能的复制能力。除了这些常见功能以外，Mariadb还提供了Cassandra插件、图数据库插件等等，这些都给我们给业务的服务增加了想象力。

【相关推荐】

1. 免费mysql在线视频教程

2. MySQL最新手册教程

3. 数据库设计那些事

The above is the detailed content of Share two problems encountered when using Mariadb. For more information, please follow other related articles on the PHP Chinese website!