MySQL version survey and mysql Survey

MySQL version survey and mysql Survey
1 Introduction 1.1 Writing Purpose

The main purpose of this article is to compare the databases of various versions used in the current project, analyze their respective features and stability, and finally recommend the appropriate version as the standard database for the future.

1.2 Background

Currently, the departments are responsible for managing and maintaining the databases used on the current network, including Oracle, MySQL, and PostgreSQL.

Because the sizes and versions of the databases used are different, which is not conducive to standardized management, unified standards need to be formulated.

1.3 references

None

2 Usage Analysis 2.1 Overview

Online version	Usage
MySQL5.1	Relatively high
MySQL5.5	Relatively high
MySQL5.6	Relatively high
MySQL5.7	Relatively high
Oracle/PostgreSQL	Low

Table 1

2.2 Common System Analysis

From the statistical results, MySQL is the most widely used, and the selection of standard databases will be selected from MySQL. MySQL is a relational database management system developed by MySQL AB in Sweden. It is currently a subsidiary of Oracle. MySQL is the most popular Relational Database Management System. In terms of WEB applications, MySQL is one of the best applications of RDBMS (Relational Database Management System. MySQL is an associated database management system that stores data in different tables rather than in a large warehouse. This increases the speed and flexibility. The SQL language used by MySQL is the most common standard language for accessing databases. MySQL adopts the dual Authorization Policy (this term "Authorization Policy"). It is divided into community edition and commercial edition. because of its small size, fast speed, and low total cost of ownership, especially open source code, MySQL is generally used as the website database for the development of small and medium-sized websites. Due to its superior performance, the Community edition works with PHP and Apache to form a good development environment.

3. Feature Analysis 3.1 Overview

MySQL has many minor versions. MySQL 5.1/5.5 has more than 40 versions, 5.6 has 24 versions, and 5.7 has 8 versions. The latest version is not recommended for stability. MySQL 5.7 is not considered for the time being, this section mainly analyzes versions 5.1, 5.5, and 5.6.

Release	Last update
5.1.72
5.5.45
5.6.26
5.7.8

Table 2

3.2 main features

OS version	Features
5.1	Partition features of tables and indexes-MySQL 5.1 supports five forms of horizontal data partitions: range, hash, key, list, and composite partitions (subpartitions ). Through the partitioning of tables and index data, you can only scan the required data partitions, rather than scanning the entire table and index, so you can get a faster query response. In addition, with the partition feature, many storage and other maintenance operations become easier and faster. Row-based replication and hybrid replication -- two new replication options provided in MySQL 5.1. "Unit-based replication" refers to the data changes between the master server and the slave server (rather than the actual SQL statement), while "hybrid replication" uses statement-based replication, or unit-based replication, which depends on SQL operations. For applications, this forms and maintains the most effective and secure replication mechanism. Event scheduling feature-this is a new tool that enables developers and business management to automatically schedule the SQL-based recurring tasks to be executed on database servers. This feature provides greater support for database-specific tasks than CRON in Linux or Unix or Microsoft Windows. New MySQL Query Analyzer-this new MySQL Query Analyzer tool from Sun will be provided for MySQL Enterprise subscription projects that enjoy preferential prices, this tool precisely locates and modifies problematic SQL code by monitoring query performance, which can greatly improve the speed and normal running time of MySQL database applications.
5.5	1. Change the default storage engine to InnoDB The data recovery time of InnoDB has been shortened from one or even several hours to several minutes. InnoDB Plugin supports data compression and storage, saves storage, improves memory hit rate, and supports adaptive flush checkpoint, which can avoid unexpected performance bottlenecks in databases in some cases. Multi Rollback Segments: Originally, InnoDB only has one Segment and only supports 1023 concurrent operations. It has now expanded to 128 Segments, thus solving the high concurrency limit. 2. Multi-Core Performance Improvement The replacement of the Metadata Locking (MDL) Framework with LOCK_open mutex (lock) solves the performance bottleneck of MySQL5.1 and previous versions on multi-core processors. The official comments will continue to enhance support for MySQL multi-processor, until MySQL performance is "not limited by the number of processors" 3. Enhanced Replication The MySQL replication feature is widely used by Internet companies. As the most practical and simple extension method of MySQL, the asynchronous replication method in the past has become somewhat inadequate, for some users, "asynchronous replication" means data risks in extreme cases. MySQL5.5 will support semi-sync replication for the first time) more and more reliable solutions will be generated in the high-availability solution of MySQL. In addition, Slave fsync tunning; Relay log resume uption recovery and Replication Heartbeat will also be implemented 4. Enhanced Table Partitioning 5. insert Buffering if no data is found in the buffer pool, it will directly buffer up to avoid extra IO; Delete & Purge Buffering is the same as inserting. If no hit is found in the buffer pool, buffer up first, avoid extra IO. 6. Support for Native AIO on Linux The above features will be included in MySQL 5.5 community edition. in MySQL Enterprise Edition, apart from the above updates, Oracle also enhances more practical enterprise-level functions, including: Online physical hot standby MySQL Enterprise Monitor 2.2 & Oracle Enterprise Monitor MySQL Workbench Important reminder about the future: MySQL will be able to use Oracle management tools.
5.6	Better performance and scalability Improve the InnoDB Storage engine to bring better transaction throughput Improved Optimization results in better query execution time and diagnosis capability Online DDL/architecture operations bring better application availability Using Memcached APIs to access InnoDB with NoSQL results in better development speed High Performance and self-repair cluster deployment brought about by improved replication technology Improved Performance Schema provides better detection and monitoring capabilities Improved security control, so that you do not need to worry about deploying applications Other important enhancements
5.7	Better performance: it provides better optimization for multi-core CPUs, solid state disks, and locks. 100 QPS per second is no longer the pursuit of MySQL, whether or not the next version can reach 200 million QPS is more important to our users; Better InnoDB Storage engine: there are too many content. Let's wait for the MySQL technology Insider: InnoDB Storage engine version 3rd from Inside; More robust replication: Replication brings about a solution that never loses data. Traditional financial customers can also choose to use MySQL databases. In addition, GTID online smooth upgrade is also possible; Better optimizer: the significance of the optimizer code reconstruction will bring huge improvements in this version and later versions. Oracle officially solves the biggest problem of MySQL before; Support for the native JSON type: the document database is already in the toilet; Better Geographic Information Service Support: InnoDB native supports geographic location types, GeoJSON and GeoHash features; New sys Library: This is the favorite feature of Inside Jun (although it can be supported by 5.6), but it seems that there are not many people who know it. In the future, this will be the most frequently accessed library by DBA.

Table 3

4 Conclusion 4.1 Survey Result 4.2 conclusion

1. selection principles:

The production system must use a stable version. Generally, the latest version is not used, and the previous version is preferred.

According to the comprehensive survey results, MySQL 5.7 is relatively new and unstable without special requirements. From the perspective of stability and optimization, MySQL 5.1/5.5/5.6 is recommended, this version combines stability and performance.