4、GaussDB数据库性能调优

### 1. 连接数据库与准备数据

```bash
# 登录服务器
ssh root@ECS_01_IP

# 切换用户并下载解压样本
su - Ruby
wget https://sandbox-experiment-files.obs.cn-north-4.myhuaweicloud.com/20001942/hr_dataset.zip
unzip hr_dataset.zip

# 刷新环境变量
source /home/Ruby/gauss_env_file

# 连接数据库
gsql -h xxx.xxx.x.xx -d postgres -p 8000 -U root -W password -r
```

```sql
-- 创建表
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL,
    manager_id INT,
    location VARCHAR(100),
    budget DECIMAL(10, 2),
    description VARCHAR(200)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    gender CHAR(1) CHECK (gender IN ('M', 'F')),
    age INT,
    hire_date DATE,
    department_id INT,
    job_title VARCHAR(100),
    contact_number VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE attendance (
    attendance_id INT PRIMARY KEY,
    employee_id INT,
    attendance_date DATE,
    check_in_time TIME,
    check_out_time TIME,
    working_hours INT,
    overtime_hours INT,
    status VARCHAR(20)
);

CREATE TABLE salary_payments (
    payment_id INT PRIMARY KEY,
    employee_id INT,
    payment_date DATE,
    base_salary DECIMAL(10, 2),
    bonus DECIMAL(10, 2),
    deductions DECIMAL(10, 2),
    total_payment DECIMAL(10, 2),
    payment_method VARCHAR(20)
);

CREATE TABLE project_participation (
    participation_id INT PRIMARY KEY,
    employee_id INT,
    project_id INT,
    start_date DATE,
    end_date DATE,
    role VARCHAR(50),
    contribution VARCHAR(200),
    performance_rating DECIMAL(3, 1)
);

-- 导入数据
\COPY employees FROM '/home/Ruby/employees.csv' DELIMITER ',' CSV HEADER;
\COPY departments FROM '/home/Ruby/departments.csv' DELIMITER ',' CSV HEADER;
\COPY attendance FROM '/home/Ruby/attendance.csv' DELIMITER ',' CSV HEADER;
\COPY salary_payments FROM '/home/Ruby/salary_payments.csv' DELIMITER ',' CSV HEADER;
\COPY project_participation FROM '/home/Ruby/project_participation.csv' DELIMITER ',' CSV HEADER;

-- 数据检查
select count(*) from employees;
select count(*) from departments;
select count(*) from attendance;
select count(*) from salary_payments;
select count(*) from project_participation;
```

---

### 2. 系统性能指标查询

```sql
-- CPU高消耗查询
SELECT e.employee_name, e.job_title, sp.base_salary, sp.bonus, sp.total_payment
FROM employees e
JOIN salary_payments sp ON e.employee_id = sp.employee_id
WHERE sp.payment_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY e.employee_name;

-- 查看系统负载
SELECT id,name,value FROM dbe_perf.os_runtime;

-- 系统时间消耗
SELECT * FROM dbe_perf.instance_time;

-- 共享内存使用情况
SELECT * FROM dbe_perf.shared_memory_detail WHERE level=2 ORDER BY usedsize DESC LIMIT 10;

-- 会话级负载
SELECT * FROM dbe_perf.session_stat LIMIT 10;

-- 会话级时间细分
SELECT * FROM dbe_perf.session_time ORDER BY value DESC LIMIT 10;

-- 线程等待情况
SELECT * FROM dbe_perf.THREAD_WAIT_STATUS LIMIT 5;

-- 各模块等待事件
SELECT * FROM dbe_perf.WAIT_EVENTS ORDER BY total_wait_time DESC LIMIT 5;

-- 数据库级状态
SELECT * FROM dbe_perf.STAT_DATABASE LIMIT 2;
```

---

### 3. WDR 报告生成

```sql
-- 查看配置
SELECT name,setting FROM pg_settings WHERE name LIKE '%wdr%';

-- 查看WDR表
SELECT relname FROM pg_class WHERE relname LIKE '%snap_%';

-- 查看节点信息
\x
select * from pg_node_env;
\x

-- 查看快照
SELECT * FROM snapshot.snapshot ORDER BY start_ts DESC LIMIT 10;

-- 创建snapshot
SELECT create_wdr_snapshot();

-- 生成报告
\a      
\t 
\o /home/Ruby/wdr_report.html
SELECT generate_wdr_report(1, 2, 'all', 'cluster');
\o 
\t 
\a
```

---

### 4. 执行计划干预 - 索引优化

```sql
-- 原始查询
\timing on
SELECT e.employee_name, a.attendance_date, a.status
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
WHERE a.status = 'Late';

-- 查看计划
EXPLAIN SELECT e.employee_name, a.attendance_date, a.status
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
WHERE a.status = 'Late';

-- 创建索引
CREATE INDEX idx_attendance_status ON attendance(status);

-- 再次执行
EXPLAIN SELECT e.employee_name, a.attendance_date, a.status
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
WHERE a.status = 'Late';

SELECT e.employee_name, a.attendance_date, a.status
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
WHERE a.status = 'Late';
```

---

### 5. 执行计划干预 - 统计信息

```sql
-- 执行计划
EXPLAIN ANALYZE 
SELECT e.employee_id, e.employee_name, d.department_name,
       (SELECT AVG(base_salary) FROM salary_payments sp 
        WHERE sp.employee_id = e.employee_id) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.hire_date > '2010-01-01'
ORDER BY avg_salary DESC;

-- 收集统计信息
ANALYZE employees;
ANALYZE departments;
ANALYZE salary_payments;

-- 再次执行
EXPLAIN ANALYZE 
SELECT e.employee_id, e.employee_name, d.department_name,
       (SELECT AVG(base_salary) FROM salary_payments sp 
        WHERE sp.employee_id = e.employee_id) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.hire_date > '2010-01-01'
ORDER BY avg_salary DESC;
```

---

### 6. 调优案例分析

```sql
-- 系统状态
SELECT * FROM dbe_perf.os_runtime LIMIT 10;
SELECT * FROM dbe_perf.instance_time;

-- 查询语句
SELECT e.employee_name, d.department_name, a.attendance_date, a.check_in_time, a.check_out_time
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN attendance a ON e.employee_id = a.employee_id
WHERE a.attendance_date BETWEEN '2024-01-01' AND '2024-12-31';

-- 执行计划
EXPLAIN SELECT e.employee_name, d.department_name, a.attendance_date, a.check_in_time, a.check_out_time
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN attendance a ON e.employee_id = a.employee_id
WHERE a.attendance_date BETWEEN '2024-01-01' AND '2024-12-31';

-- 创建索引
CREATE INDEX idx_attendance_attendance_date ON attendance(attendance_date);
CREATE INDEX idx_attendance_employee_id ON attendance(employee_id);
CREATE INDEX idx_employees_department_id ON employees(department_id);
CREATE INDEX idx_employees_employee_id ON employees(employee_id);
CREATE INDEX idx_departments_department_id ON departments(department_id);

-- 更新统计信息
ANALYZE attendance;
ANALYZE employees;
ANALYZE departments;

-- 查看等待事件
SELECT * FROM dbe_perf.wait_events WHERE total_wait_time > 1000 ORDER BY total_wait_time DESC LIMIT 5;

-- 验证优化效果
EXPLAIN ANALYZE 
SELECT e.employee_name, d.department_name, a.attendance_date, a.check_in_time, a.check_out_time
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN attendance a ON e.employee_id = a.employee_id
WHERE a.attendance_date BETWEEN '2024-01-01' AND '2024-12-31';
```

FYF

导航1234

最近发表

友情链接