【參賽作品21】opengauss場景使用和日常管理

案例一 常用管理SQL陳述句應用實踐

用戶初次獵鮮openGauss，沒有明確的需求，只想體驗一下增加洗掉，前提不用openGauss默認自帶例如表空間和默認schema，關鍵技術涉及創建表空間，創建資料庫，創建資料模式，創建表 插入資料 修改資料 查詢資料等等，

創建表空間

創建表空間tpch_ts 
openGauss=# create  tablespace  tpch_ts  relative  location  'tablespace/tpch_ts';
CREATE TABLESPACE
openGauss=# select  spcname  from  pg_tablespace;
spcname   
------------
pg_default
pg_global
tpch_ts
(3 rows) 

創建資料庫

創建資料庫tpchdb，并與表空間tpch_ts系結
openGauss=# create database   tpchdb   with  tablespace=tpch_ts;
CREATE DATABASE

openGauss=# select datname from pg_database;
datname

template1
tpchdb
template0
postgres
(4 rows)

查詢資料庫tpchdb與哪一個表空間的ID系結
openGauss=# select datname,dattablespace from pg_database where datname=‘tpchdb’;
datname | dattablespace
---------±--------------
tpchdb | 16384
(1 row)

查詢資料庫tpchdb對應的表空間
openGauss=# select oid,spcname from pg_tablespace where oid=16384;
oid | spcname
-------±--------
16384 | tpch_ts
(1 row)

創建資料模式

查看相關資料庫有哪些模式
tpchdb=# select   catalog_name,schema_name,schema_owner  from  	information_schema.schemata;
 catalog_name |    schema_name     | schema_owner 
--------------+--------------------+--------------
tpchdb       | pg_toast           | omm
tpchdb       | cstore             | omm
tpchdb       | pkg_service        | omm
tpchdb       | dbe_perf           | omm
tpchdb       | snapshot           | omm
tpchdb       | blockchain         | omm
tpchdb       | pg_catalog         | omm
tpchdb       | public             | omm
tpchdb       | sqladvisor         | omm
tpchdb       | dbe_pldebugger     | omm
tpchdb       | information_schema | omm
tpchdb       | db4ai              | omm

創建模式scha
tpchdb=# create schema scha;
CREATE SCHEMA

tpchdb=# select catalog_name,schema_name,schema_owner from information_schema.schemata;
catalog_name | schema_name | schema_owner
--------------±-------------------±-------------
tpchdb | pg_toast | omm
tpchdb | cstore | omm
tpchdb | pkg_service | omm
tpchdb | dbe_perf | omm
tpchdb | snapshot | omm
tpchdb | blockchain | omm
tpchdb | pg_catalog | omm
tpchdb | public | omm
tpchdb | sqladvisor | omm
tpchdb | dbe_pldebugger | omm
tpchdb | information_schema | omm
tpchdb | db4ai | omm
tpchdb | scha | omm
(13 rows)

創建資料表

tpchdb=# create table scha.test1(v1 varchar(20));
CREATE TABLE
tpchdb=# insert into scha.test1 values(‘scha test data’);
INSERT 0 1
tpchdb=# select * from scha.test1;
v1
scha test data
(1 row)

案例二 開發一個應用

基于openGauss初步了解后，用戶準備開發一個CMS應用，后端 用的就是openGauss，關系涉及表索引、創建表磁區等，

磁區表索引分為LOCAL索引與GLOBAL索引，一個LOCAL索引對應一個具體磁區，而GLOBAL索引則對應整個磁區表

在java中實作OpenGauss資料庫的連接

https://opengauss.obs.cn-south-1.myhuaweicloud.com/2.0.1/x86_openEuler/openGauss-2.0.0-JDBC.tar.gz

 

  package HospitalDatabase;

 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.*;

public class PostgreSqlJdbcConnAddDatas {
public static void main(String args[]) {
Connection c = null;
Statement stmt = null;
try {
Class.forName(“org.postgresql.Driver”);
/*
* @para:url,usename,password
* @return: null
*/
c = DriverManager.getConnection(“jdbc:postgresql://192.168.159.129:26000/postgres”,“jack”,“gauss@111”);
c.setAutoCommit(false);

		System.out.println(<span class="hljs-string">"連接資料庫成功！"</span>);
		stmt = c.createStatement();


		<span class="hljs-comment">/*
		 * @功能:插入資料
		 * @引數:表名稱（列名1，列名2···列名n）+（資料1，資料2，···資料n）
		 */</span>

// String sql = "INSERT INTO Course (Cno,Cname,Cpno,Ccredit) "
// + “VALUES (1,‘資料庫’,5,4);”;
// stmt.executeUpdate(sql);
// System.out.println(“新增資料成功！”);

		<span class="hljs-comment">/*
		 * @功能:查詢資料
		 * @引數:表名
		 */</span>

// ResultSet rs = stmt.executeQuery(“select * from sc”);
// while(rs.next()){
// int sno = rs.getInt(“Sno”);
// int cno = rs.getInt(“cno”);
// int grade = rs.getInt(“Grade”);
// System.out.println(cno + “,” + cno + “,” + grade);
// }
// System.out.println(“查詢資料成功！”);
//
/*
* @功能:更新資料
* @引數:
*/

		String sql = <span class="hljs-string">"UPDATE sc set grade = 250 where cno=1 "</span>;
		stmt.executeUpdate(sql);
		c.commit();

		ResultSet rs1 = stmt.executeQuery(<span class="hljs-string">"select * from sc order by cno"</span>);
		<span class="hljs-keyword">while</span>(rs1.next()){
			<span class="hljs-keyword">int</span> sno = rs1.getInt(<span class="hljs-string">"Sno"</span>);
			<span class="hljs-keyword">int</span> cno = rs1.getInt(<span class="hljs-string">"Cno"</span>);
			<span class="hljs-keyword">int</span> grade = rs1.getInt(<span class="hljs-string">"Grade"</span>);
			System.out.println(sno + <span class="hljs-string">","</span> + cno + <span class="hljs-string">","</span> + grade);
		}
		System.out.println(<span class="hljs-string">"更新資料成功！"</span>);


		<span class="hljs-comment">/*
		 * @功能:洗掉資料
		 * @引數:
		 */</span>

// String sql = "Delete from sc where Cno=2 ";
// stmt.executeUpdate(sql);
// c.commit();
//
// ResultSet rs1 = stmt.executeQuery(“select * from sc order by cno”);
// while(rs1.next()){
// int sno = rs1.getInt(“Sno”);
// int cno = rs1.getInt(“Cno”);
// int grade = rs1.getInt(“Grade”);
// System.out.println(sno + “,” + cno + “,” + grade);
// }
// System.out.println(“洗掉資料成功！”);

		stmt.close();
		c.commit();
		c.close();

	} <span class="hljs-keyword">catch</span> (Exception e) {
		e.printStackTrace();
		System.err.println(e.getClass().getName() + <span class="hljs-string">": "</span> + e.getMessage());
		System.exit(<span class="hljs-number">0</span>);
	}

// System.out.println(“新增資料成功！”);
}
}

在go中實作OpenGauss資料庫的連接

import (
“database/sql”
)

創建表磁區

同樣表結構欄位，同樣1000萬資料，分成三組測驗，一個有磁區，一個沒磁區沒索引，一個有索引，通過explain看消耗時間，暫時沒有測驗磁區表索引，openGauss磁區表索引分為LOCAL索引與GLOBAL索引，一個LOCAL索引對應一個具體磁區，而GLOBAL索引則對應整個磁區表

加表磁區

沒有磁區沒有索引

CREATE TABLE scha.customer_address
(
ca_address_sk serial ,
ca_address_id character(16) NOT NULL ,
ca_street_number character(10) ,
ca_street_name character varying(60) ,
ca_street_type character(15) ,
ca_suite_number character(10) ,
ca_city character varying(60) ,
ca_county character varying(30) ,
ca_state character(2) ,
ca_zip character(10) ,
ca_country character varying(20) ,
ca_gmt_offset numeric(5,2) ,
ca_location_type character(20)
);

insert into scha.customer_address values(generate_series(1,10000000),‘ca_ad’,‘street’,‘street_name’,‘street_type’,‘suite’,‘city’,‘county’,‘cn’,‘zip’,‘country’,100.00,‘location_type’);

沒有磁區沒有索引
tpchdb=# explain analyze select * from scha.customer_address where ca_address_sk=3000 ;
QUERY PLAN

Seq Scan on customer_address (cost=0.00…333502.95 rows=1 width=131) (actual time=3.236…23640.419 rows=1 lo
ops=1)
Filter: (ca_address_sk = 3000)
Rows Removed by Filter: 9999999
Total runtime: 23640.698 ms
(4 rows)

加磁區

CREATE TABLE scha.customer_address
(
ca_address_sk serial ,
ca_address_id character(16) NOT NULL ,
ca_street_number character(10) ,
ca_street_name character varying(60) ,
ca_street_type character(15) ,
ca_suite_number character(10) ,
ca_city character varying(60) ,
ca_county character varying(30) ,
ca_state character(2) ,
ca_zip character(10) ,
ca_country character varying(20) ,
ca_gmt_offset numeric(5,2) ,
ca_location_type character(20)
)
PARTITION BY RANGE (ca_address_sk)
(
PARTITION P1 VALUES LESS THAN(5000),
PARTITION P2 VALUES LESS THAN(10000),
PARTITION P3 VALUES LESS THAN(15000),
PARTITION P4 VALUES LESS THAN(20000),
PARTITION P5 VALUES LESS THAN(25000),
PARTITION P6 VALUES LESS THAN(30000),
PARTITION P7 VALUES LESS THAN(40000),
PARTITION P8 VALUES LESS THAN(MAXVALUE)
)
ENABLE ROW MOVEMENT;

tpchdb=# explain analyze select * from scha.customer_address where ca_address_sk=3000 ;
QUERY PLAN

Partition Iterator (cost=0.00…92728.70 rows=26887 width=280) (actual time=2.845…4.628 rows=1 loops=1)
Iterations: 1
-> Partitioned Seq Scan on customer_address (cost=0.00…92728.70 rows=26887 width=280) (actual time=2.835
…4.618 rows=1 loops=1)
Filter: (ca_address_sk = 3000)
Rows Removed by Filter: 4998
Selected Partitions: 1
Total runtime: 4.900 ms
(7 rows)

加索引

create index index_ca_address_sk on scha.customer_address(ca_address_sk);
tpchdb=# explain analyze select * from scha.customer_address where ca_address_sk=3000 ;
QUERY PLAN

Bitmap Heap Scan on customer_address (cost=760.62…75391.54 rows=26886 width=280) (actual time=0.192…0.193
rows=1 loops=1)
Recheck Cond: (ca_address_sk = 3000)
Heap Blocks: exact=1
-> Bitmap Index Scan on index_ca_address_sk (cost=0.00…753.89 rows=26886 width=0) (actual time=0.110…0.
110 rows=1 loops=1)
Index Cond: (ca_address_sk = 3000)
Total runtime: 0.326 ms
(6 rows)

案例三 開發資料產品方向

用戶準備開發一個基于OLAP基礎之上的分析應用，后端 用的就是openGauss，關系涉及表索引、創建表磁區、列式表等等，

列存表為：YES/NO/LOW/MIDDLE/HIGH，默認值為LOW

CREATE TABLE scha.customer_address_column
(
ca_address_sk serial ,
ca_address_id character(16) NOT NULL ,
ca_street_number character(10) ,
ca_street_name character varying(60) ,
ca_street_type character(15) ,
ca_suite_number character(10) ,
ca_city character varying(60) ,
ca_county character varying(30) ,
ca_state character(2) ,
ca_zip character(10) ,
ca_country character varying(20) ,
ca_gmt_offset numeric(5,2) ,
ca_location_type character(20)
)WITH (ORIENTATION = COLUMN,compression=high);

tpchdb=# \dt+ scha.
List of relations
Schema | Name | Type | Owner | Size | Storage
| Description
--------±-----------------------------±------±------±-----------±----------------------------------------
±------------
scha | customer_address_column_middle | table | omm | 97 MB | {orientation=column,compression=middle}
|
scha | customer_address_column_high | table | omm | 97 MB | {orientation=column,compression=high}
|
scha | customer_address_column_low | table | omm | 97 MB | {orientation=column,compression=low}

這里測驗下發現列式生成特別慢，而且middle、high、low壓縮比都是一樣，

案例四 開發資料監控

案例五 備份與恢復實踐應用

物理備份

[omm@hybriddb03 ~]$gs_basebackup -D ./backupop -p 26000 -P -l backup20211130
INFO: The starting position of the xlog copy of the full build is: 1/D7000028. The slot minimum LSN is: 1/D7000148.
[2021-11-30 15:19:58]:begin build tablespace list
[2021-11-30 15:19:58]:finish build tablespace list
[2021-11-30 15:19:58]:begin get xlog by xlogstream
[2021-11-30 15:19:58]: check identify system success
[2021-11-30 15:19:58]: send START_REPLICATION 1/D7000000 success
[2021-11-30 15:19:58]: keepalive message is received
[2021-11-30 15:19:58]: keepalive message is received
[2021-11-30 15:20:01]: keepalive message is received
671193/671193 kB (100%), 2/2 tablespaces
[2021-11-30 15:20:09]:gs_basebackup: base backup successfully

物理恢復

模擬故障發生后，進行物理資料恢復，下面把服務停掉，并把物理檔案洗掉掉
[omm@hybriddb03 ~]$ gs_om -t stop
[root@hybriddb03 cluster]# rm db1/* -rf

還原資料檔案
[omm@hybriddb03 ~]$ cp -rf backupop/* /data/opengauss/cluster/db1/
[omm@hybriddb03 ~]$ gs_om -t start

邏輯備份

[omm@hybriddb03 ~]$ gs_dump -p 26000 tpchdb -f ./backup/tpchdb.sql
gs_dump[port=‘26000’][tpchdb][2021-11-30 15:43:50]: The total objects number is 411.
gs_dump[port=‘26000’][tpchdb][2021-11-30 15:43:50]: [100.00%] 411 objects have been dumped.
gs_dump[port=‘26000’][tpchdb][2021-11-30 15:43:51]: dump database tpchdb successfully
gs_dump[port=‘26000’][tpchdb][2021-11-30 15:43:51]: total time: 1751 ms

邏輯恢復

[omm@hybriddb03 ~]$ gs_dump -p 26000 tpchdb -f ./backup/tpchdb.sql
gs_dump[port=‘26000’][tpchdb][2021-11-30 15:43:50]: The total objects number is 411.
gs_dump[port=‘26000’][tpchdb][2021-11-30 15:43:50]: [100.00%] 411 objects have been dumped.
gs_dump[port=‘26000’][tpchdb][2021-11-30 15:43:51]: dump database tpchdb successfully
gs_dump[port=‘26000’][tpchdb][2021-11-30 15:43:51]: total time: 1751 ms

案例六 資料庫日志與企業應用實踐

openGauss運行時資料庫節點以及openGauss安裝部署時產生的日志統稱為系統日志，如果openGauss在運行時發生故障，可以通過這些系統日志及時定位故障發生的原因，根據日志內容制定恢復openGauss的方法，

安裝布署是 /data/opengauss

資料庫節點的運行日志
[root@hybriddb02 omm]# ll /data/opengauss/log/omm/pg_log/dn_6002/
total 9563872
-rw-------. 1 omm dbgrp 20971637 Nov 29 17:29 postgresql-2021-11-29_165730.log
-rw-------. 1 omm dbgrp 16889281 Nov 29 17:55 postgresql-2021-11-29_172930.log
-rw-------. 1 omm dbgrp 466061 Nov 29 23:59 postgresql-2021-11-29_175554.log
-rw-------. 1 omm dbgrp 1118029 Nov 30 15:23 postgresql-2021-11-30_000000.log
-rw-------. 1 omm dbgrp 20971633 Nov 30 15:57 postgresql-2021-11-30_152502.log
-rw-------. 1 omm dbgrp 20971676 Nov 30 16:29 postgresql-2021-11-30_155716.log
-rw-------. 1 omm dbgrp 20971706 Nov 30 17:01 postgresql-2021-11-30_162924.log

OM openGauss安裝卸載時產生的日志
[root@hybriddb02 omm]# ll /data/opengauss/log/omm/om/
total 72
-rw-------. 1 omm dbgrp 66009 Dec 15 09:20 gs_local-2021-11-29_163542.log
-rw-------. 1 omm dbgrp 1943 Dec 10 14:34 gs_om-2021-12-10_143432.log

預寫式日志WAL（Write Ahead Log，也稱為Xlog）是指如果要修改資料檔案，必須是在這些修改操作已經記錄到日志檔案之后才能進行修改，即在描述這些變化的日志記錄重繪到永久存盤器之后，在系統崩潰時，可以使用WAL日志對openGauss進行恢復操作，

[root@hybriddb02 omm]# ll /data/opengauss/cluster/db1/pg_xlog
total 2408452
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:01 0000000100000001000000C7
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:03 0000000100000001000000C8
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:04 0000000100000001000000C9
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:05 0000000100000001000000CA
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:06 0000000100000001000000CB
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:07 0000000100000001000000CC
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:07 0000000100000001000000CD
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:08 0000000100000001000000CE
-rw-------. 1 omm dbgrp 16777216 Nov 30 15:09 0000000100000001000000CF

案例七 主從復制知識與應用實踐

案例八 系統表及系統視圖應用實踐

案例九 oracle遷移到openGauss應用實踐

資料庫物件遷移：大部分資料庫系統主要用到表、索引、序列、存盤程序、觸發器等物件，不同資料庫的資料庫物件定義不一樣，這部分作業主要涉及表重定義、函式或存盤程序代碼改造等作業，

• 應用代碼改造：不同資料庫的SQL語法有差異，盡管OpenGauss的語法和Oracle很相似，在SQL語法和函式方面仍然存在一定差異，因此SQL和應用代碼的改寫不可避免，
• 資料遷移測驗：當資料庫物件遷移作業完成之后，需進行資料遷移測驗，具體為遷移Oracle資料庫資料到OpenGauss，同時驗證遷移后資料的準確性，例如遷移后資料量是否和Oracle庫中的資料量一致？是否存在亂碼？中文是否能正常顯示？
• 功能測驗：前三步作業完成之后需要對新系統進行功能測驗，這塊作業主要由測驗人員進行，開發人員、DBA配合，
• 性能測驗：前四步作業完成之后需要對新系統進行性能測驗，包含業務代碼的性能和資料庫性能，這塊作業主要由測驗人員進行，開發人員、DBA配合，性能測驗對系統的最高業務吞吐量進行模擬測驗，
• 生產割接：以上步驟完成之后，基本具備生產割接的條件，正式割接前建議至少做兩次割接演練，重點記錄資料遷移測驗時間、停服務時間，以及驗證整個遷移步驟是否有問題，

在以上六項改造作業中，DBA都承擔著重要的角色，

案例十 mysql遷移到openGauss應用實踐

案例十 openGauss性能優化應用實踐