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插件、图数据库插件等等,这些都给我们给业务的服务增加了想象力。
【相关推荐】
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!

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

WebStorm Mac version
Useful JavaScript development tools

Atom editor mac version download
The most popular open source editor

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software