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

Original link: https://www.modb.pro/db/23463

Abstract: Mo Tianlun Database Weekly issue 18, once a week to push this week's database-related hot information, selected articles, practical information documents.

Hot information

1. Database popularity ranking in April 2020: MySQL has become the king of facts, and domestic openGauss is expected.

[abstract] the popularity ranking of DB-Engines database was released in April 2020. In this month's ranking, Oracle edged up 4.78 points from last month, while MySQL edged up 8.62 points, with Oracle the biggest winner. Motianlun's domestic database ranking, TiDB, OceanBase, Dameng database, GaussDB, PolarDB ranked the top five, of which TiDB and GaussDB increased significantly.

PostgreSQL is the best database in the world.

[abstract] PostgreSQL has objective and measurable advantages in design and implementation: transparent security mechanism; multi-version concurrency control; processing of map data; open source; PostgreSQL has built-in logical replication in the kernel to allow cross-version migration; through plug-ins, PostgreSQL is allowed to be extended in any language PostgreSQL can combine indexing with function-driven indexing, partial indexing, overlay indexing and full-text search.

3. Prospect of TiDB 4.0new features: graphical diagnostic interface

[abstract] TiDB 4.0new TiDB Dashboard graphical interface, built-in a variety of powerful tools to facilitate DBA students to diagnose, monitor and manage clusters. One day, Xiao Wang, DBA of PongHat Company, received feedback from the business side: the database query has suddenly become very slow and the business has been suspended. This article introduces the detailed troubleshooting process.

4. Tech | SequoiaDB High availability principle of Giant Sequoia

[abstract] this article will briefly review the implementation of high availability of traditional database, and introduce in detail the high availability implementation of Giant Sequoia distributed database, including: master / standby structure and cluster architecture; learn about the famous RAFT algorithm; then, most importantly, how is the consistency of Giant Sequoia distributed database, and how to ensure that the data is good and not lost in the cluster environment.

Selected articles

1. ORACLE descending query problem

[abstract] there is a table that records a large number of customer data, recording a certain amount of transaction information every year, and now it is necessary to query and display the customer's latest transaction data. This table is designed as a HASH partitioned table by customer ID with an index column starting with customer_id and transaction_date. This article introduces the detailed analysis process and query scenario.

2. The use of trunc function, round function, ceil function and floor function in Oracle

[abstract] this article shares the ways in which the trunc function handles numbers, the trunc function handles dates, the round function (rounded), ceil and floor functions in Oracle. For example, if the date is processed by the trunc function, the trunc function returns the date value truncated in the specified meta-element format. The specific syntax format is as follows: TRUNC (date, [fmt]) (date is a necessary parameter, enter the date value, the fmt parameter can be ignored, it is a date format)

3. Lock tables and unlock tables in MySQL

[summary] MySQL allows client sessions to explicitly acquire table locks to collaborate with other sessions to access tables, or to prevent other sessions from using tables when they need exclusive tables. This capability is achieved through LOCK TABLES and UNLOCK TABLES. LOCK TABLES is used to display the acquisition table lock, and UNLOCK TABLES explicitly releases any table locks held by the current session.

4. Under GaussDB distributed cluster 1.0.2, the grouping is sorted in descending order and top20 is selected. After passing union all, the result set is incorrect.

[abstract] Statistics goods browse top20, join shopping cart top20, purchase goods top20. In the GuassDB T 1.0.2 distributed cluster environment, the grouping is sorted in descending order and the top20 is selected. After passing the union all, the result set is incorrect. The result shows the ascending order of the pre-top20, and there is no problem with the SQL result set on the stand-alone. Huawei reports that it is no problem to test under version 1.0.2 B319, and it is recommended to use version 1.0.2 B319 or above.

Practical information document

1. "interpretation of the new features of Oracle 20C DataGuard"

[summary] several new features of Oracle 20C DG and 19C are introduced in the document. New features related to DataGuard in 20C include: slave library supports canceling log application at PDB level without affecting the synchronization of other CDB and PDB; slave library supports configuring Result Cache;DGMGRL to add PREPARE DATABASE FOR DataGuard command to complete the preparation of DG build with one click.

2. "Oracle_PostgreSQL_GaussDB T _ stand-alone SQL query statistical performance comparison"

[abstract] recently, the SQL performance of three databases oracle\ pg\ gaussdb T is compared. Taking the e-commerce user behavior log as the data source, and based on the same data set, the performance comparison tests are carried out on ten business scenarios such as data import, full table update and SQL query statistics of different stand-alone databases (Oracle, PostgreSQL, GaussDB T).

3. "the development process of common mainstream relational databases and talking about the learning and selection of databases"

[abstract] this paper introduces the development history of international mainstream relational databases, the relationship between databases, the development history of domestic relational databases and cloud databases, as well as the practical application of domestic databases. In the face of the current development situation of database letting a hundred flowers blossom, under the trend of going to IOE and domestic independence, as database practitioners or database enthusiasts, we should choose the direction of learning, how to learn and so on.

4. Yunhe Enmo Technical Newsletter-2020.03

[abstract] in order to share industry cases in time, notify common problems, achieve sharing and prevention in advance, and jointly learn the contents of domestic databases, we have sorted out and edited the Cloud and Enmo Technical Newsletter. Through the knowledge review and fault induction of the past period of time, in order to provide valuable information for your reference.

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.