Select the latest version of MySQL
In the early stage, the company mainly used mysql5.5. This year, we set up the database configuration center and promoted mysQL5.6, which has improved performance and functions to some extent. 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 not satisfactory, and the parallel replication can only be started in the case of multiple dB. It is difficult to guarantee such a guarantee in services, so once the services with intensive write operations, slow synchronization will be a serious problem.
Therefore, we have been considering upgrading MySQL recently, and the first problem we face is to choose a suitable version. First, we consider using mysql5.7. 5.7 has been issued several official versions this year, and it is widely used now, so we can consider using it in the official environment. So we did an online comparison test and found that mysQL5.7 had a big performance gap with our online version of mysQL5.6 (maybe some parameters were not tuned properly, 5.7 was much more complicated).
At the same time, the company recently frequent some log storage, most are using innodb engine, capacity is very waste, on the other hand, due to our company’s standard mysql server capacity is 1.3 T, for some large capacity requirements of the business, there is also a bottleneck capacity, if you want to keep for a long time it would be difficult to meet the demand of data. So we are going to take this opportunity to take this into account. Currently Percona and Mariadb support Tokudb, Mariadb 10.2 or 10.3 are ready to support Myrocks.
We chose Percona 5.7.14, Mariadb 10.1.18 and MySQL 5.6.
The first is the case of both using the Innodb engine:
Mariadb is close to MySQL5.6
Percona 5.7.14 gives similar performance results to official MySQL5.7, but a bit less than MySQL5.6 (performance_SCHEMA is a bit better, but there are gaps).
The results of the Tokudb test were not as good as the official claims. With SNappy compression, inserts were about 1/4 slower than InnoDB, and updates were about half as good. Percona is worse, so I’m not going to consider it.
Finally, Mariadb 10.1.18 was selected, and a business was deployed online. After trial use of this business, it was gradually promoted.
Some potholes you stepped on with Mariadb
While using Mariadb, I encountered a lot of problems. Here are two major problems I encountered for your reference:
1. Synchronization performance problems:
The peak of our service was more than 9000 write operations per second. The first problem we faced was that the synchronization performance could not keep up. The number of slave synchronization threads increased to 16 threads, barely catching up, but once the database stopped for a while, we might face the possibility of never being able to catch up. When feeling desperate, check out the official Mariadb article (mariadb.com/kb/en/maria…). Mariadb parallel replication supports several modes, including in-order and out-of-order. However, our business supports in-order, so out-of-order is not considered. In the in-order mode, two modes are supported: Conservative and Optimistic. Conservative by default, this parallel mode strictly guarantees the order of things, which is estimated to be similar to the group commit principle in 5.7. In Optimistic mode, the replication will try to start as many sessions as possible, and will not deal with conflicts until they are found. Took a strong shot at Optimistic and achieved a top sync speed of 14,000 beats per second.
2. “Memory leak”
The system deployment structure is as follows: two Mariadb are made into master master replicas, and a self-developed distributed database is deployed in front of them, and the business side is connected to the distributed database process. After the system went online for a few days, I found that the main library would hang up inexplicably. Fortunately, there is a distributed database, and it will automatically switch over. If the Mariadb main library hangs up, it will automatically switch to another main library, and the business side did not realize it. MySQL has been killed by the kernel.
Various attempts were made to remove the Tokudb engine configuration and replace it with Mariadb 10.1.19, and eventually the main library would hang. Once by chance, I stopped the slave on the master library, and found that the memory of the master library suddenly decreased a lot and no longer increased. However, once I started the slave on the master library, I found that the memory gradually increased. Mem_root: mem_root: mem_root: mem_root: mem_root: mem_root: mem_root: mem_root: mem_root Found that as the other Mariadb in the dual master, there is no memory increase problem.
Once you’ve discovered the above, start debugging the code, start one mariadb with GDB, and start the other with a normal command, and make the two libraries double master:
Case one: Test for binlog events received as slave libraries
Inserting a line of data on mariadb started with a normal command, GDB looks at the order of events received as follows:
### i ) Gtid_log_event
### ii) Table_map_log_event
### iii) Write_rows_log_event
### iv) Xid_log_event
Second case: a binlog event received while testing as the master library
Insert a row on gDB-started Mariadb, and GDB observes that the event received is:
Rotate_log_event # # # 1)
# # # 2) Gtid_list_log_event
Rotate_log_event # # # 3)
The Rotate_log_event event is virtualized to keep the master up to date with the slave. This is essentially an empty event and no processing is done, so it is suspected that there was a problem with the Gtid_list_log_event event.
Gtid_list_log_event::do_appy_event: THD ->alloc: THD ->alloc: THD ->alloc: THD ->alloc: THD ->alloc Free_root (THD ->mem_root, MYF(MY_KEEP_PREALLOC)); After recompiling and running for a day, the memory was finally stable.
Because only the master library has this event, the master library synchronization processing performance is not high, so it is used in this way for the time being. I wonder when the official mariadb version will be optimized.
Overall, Mariadb is a good fit for our company. It has the latest features, features and solutions. Tokudb solves the log storage problem; Connection pool can solve the problem of performance underground in the case of a large number of connections. Auditing plug-ins provide security audits; The slave concurrent mode provides high-performance replication capability. In addition to these common features, Mariadb also provides the Cassandra plug-in, graph database plug-in, and so on, which add imagination to our services to the business.