【Entry 94】21-day openGauss study tour
In 1970, Dr. Edgar F. Codd of IBM’s monograph”A Relational [Model] of Data for Large Shared Data Banks” is considered to be the originator of relational databases and laid a series of principles of relational databases. In 1977, Michael Stonebraker started a project called Ingres, the predecessor of PostgreSQL. He introduced the object-relational model and officially open-sourced Postgresql 6.0 in 1996. In addition to the excellent basic design, it adopts a BSD3.0-like protocol, giving developers the greatest freedom, so a large number of derivative databases are based on its forks. By the way, the two bigwigs won the Turing Award in 1981 and 2014 respectively.
openGauss is carefully built and open sourced by the Huawei development team of China Light, and it is a branch of [PostgreSQL] 9.2.4. According to statistics, when openGauss was open sourced in 2020, there were 515 database parameters, which is far more than the 290 database parameters of PostgreSQL. The function is much stronger than that of PostgreSQL in the same period. The changes include but are not limited to: adding the double write function, increasing the transaction id range, supporting incremental checkpoints, adding performance monitoring dbe_perf schema, etc.
And I, as a database novice, was fortunate to participate in the 21-day check-in activity of “Daily Practice: openGauss Online Training Course” held by Motianlun, to learn and operate this database by myself.
Motianlun’s online system allows me to implement all the commands on the first day of not being able to set up my own environment.
The 21-day content includes basic client-side operations and SQL statement practice.
DDL (Data Definition Language)
CREATE/ALTER/DROP DATABASE/SCHEMA/TABLESPACE/TABLE[ PARTITION]/VIEW/INDEX/PROCEDURE/FUNCTION/PACKAGE/EXTENSION/AGGREGATE/CAST/EXTENSION/OPERATOR/LANGUAGE/TYPE CURSOR/MOVE/FETCH/CLOSE REINDEX
DML (Data Manipulation Language) is used to operate data in database tables. Such as: insert, update, query, delete.
INSERT UPDATE SELECT DELETE/TRUNCATE COPY LOCK CALL ALTER SESSION
DCL (Data Control Language) is
a statement used to create user roles, set or change database user or role permissions.
CREATE/ALTER/DROP USER GRANT/REVOKE
gsqlIt is a client-side command-line tool, and it is also the tool I mainly use in the learning process, but in order to cooperate with the use of opengauss, I also need to learn the server-side tools:
gs_omthe main players of the
gs_ctlserver-side can be used for various server-side commands
gs_gucto set the openGauss configuration document. postgresql.conf, pg_hba.conf and pg_ident.conf), and are stored in the data directory (data). Users can modify the name and storage path of the configuration file.
In addition, there are a series of tools such as data export and recovery (
gs_restore), data backup (
gs_basebackup), working condition query (
gs_collector), etc., which must be mastered in order to become a DBA.
During these 21 days, in addition to completing the courses and assignments on time, I also browsed the main documents of openGauss and praised that the content of the documents is very comprehensive.
As a person who lacks project experience, I am often troubled when I enter the database to study, and I feel that I am blind and touch the elephant, and I lack an understanding of the overall situation.
But these 21 days of study gave me a basic understanding of database granularity:
database -> logical partition information schema, physical partition table space -> table -> row (column).
Through the study of system tables such as
pg_toastI have increased my view of the big picture of the database.
For example, if you want to become a database expert, you must master the functions in pg_proc.
select * from pg_proc;
Returns 3703 built-in
pg_procfunctions ranging from basic types (integers, floats, characters, strings), to complex types (such as dates, geometric transformations, URLs, full text), to adding Decryption, statistics, aggregation, and AI functions are one-stop solutions to analysis problems. If you want to write a stored procedure well, you must work hard to avoid wasting the tools provided by openGauss developers!
If you want to pay attention to the profiling of the database performance side, the parameters in ,
pg_resource_pool, , must be familiar. In particular, openGauss will have a dbe_perf performance monitoring view by default under each library, similar to mysql’s performance_schema, which has hundreds of performance views, although most of these views are available in pg, but it is convenient to do it in a schema alone View and manage. The dbe_perf schema contains parameters that can be fully tuned to the database, such as:
select * from dbe_perf.INSTANCE_TIME
Can return including the time spent by the database in parsing, planning, rewriting, stored procedure compilation, execution, and IO.
select * from dbe_perf.MEMORY_NODE_DETAIL
Able to return the memory usage of database nodes.
Not only do you look down at the code, but also look up and pay attention to the efficiency and resource usage of your own code.
blockchainSchema can ensure that every database change can be recorded in the Ledger. Tide is a feature worth investigating.
21 days was short, but the interactive learning of openGauss laid the groundwork for me.
Whenever I encounter difficulties, the classmates in the group and the teacher of Motianlun always lend a helping hand eagerly, so that I can complete my study and homework on time and publish the article on Motianlun (see appendix). )
In the wave of the information age, Xiaobai also firmly believes that by seizing the opportunity of the new database, there will be times when the wind and waves are broken, and the sails will be lifted to the sea.
I’m lucky to meet you, and the openGauss community is with me in my pursuit of something better.
Appendix: 21 days of openGauss daily practice
openGauss daily practice day 1 | openGauss create table, insert records, query records and delete table basic use
openGauss daily practice day 2 | openGauss query, update and delete basic use
openGauss daily practice day 3 | create Database, modify database properties and delete database
openGauss daily practice Day 4 | Create roles, modify role properties, change role permissions and delete roles
openGauss daily practice Day 5 | Create users, modify user properties, change user permissions and Deleting users
openGauss Daily practice Day 6 | Learn openGauss Create schema, modify schema attributes and delete schema
openGauss Daily practice Day 7 | Tablespace
openGauss Daily practice Day 8 | Learn openGauss partition table
openGauss Daily Monday Day 9 of practice | OpenGauss for common table index
practice Day 10 | Partition table index
openGauss Practice Day 11 | Learning openGauss view
openGauss Practice Day 12 | openGauss data type
openGauss practice day 1 13 days | openGauss import data
openGauss daily practice Day 14 | openGauss export data
openGauss daily practice Day 15 | openGauss define stored procedures and functions
openGauss daily practice Day 16 | openGauss transaction control
openGauss Daily Practice Day 18 | openGauss Triggers
openGauss Daily Practice Day 19 | openGauss Gather Statistics, Print Execution Plans, Garbage Collection and
Checkpoint openGauss Daily Practice Day 20 | openGauss Full Text Search
openGauss Daily Practice Practice Day 21 | openGauss Storage Model – Row Storage and Column Storage