GBase 8a Operations in Practice: Load Monitoring, Audit L...

This guide covers three core areas of daily GBase 8a operations: tracking data loads and collecting error details, configuring audit logs and analysing slow queries, and hierarchically tuning memory parameters. It also provides a standard daily and weekly inspection checklist for your gbase database.

1. Data Load Monitoring

1.1 Load Methods

GBase 8a supports two main load methods: gload for large‑scale offline imports (recommended), and LOAD DATA INFILE for single‑file loads with MySQL‑like syntax.

1.2 Checking Load Progress

Monitor running and historical loads through system tables:

-- Currently executing load tasks
SELECT
    task_id, table_name, status, start_time,
    loaded_rows, error_rows,
    TIMESTAMPDIFF(SECOND, start_time, NOW()) AS elapsed_sec
FROM gclusterdb.load_task
WHERE status IN ('RUNNING', 'PENDING')
ORDER BY start_time DESC;

-- Last 50 load history records
SELECT
    task_id, table_name, status,
    start_time, end_time, loaded_rows, error_rows,
    TIMESTAMPDIFF(SECOND, start_time, end_time) AS duration_sec
FROM gclusterdb.load_task
ORDER BY start_time DESC LIMIT 50;

1.3 Retrieving the Last Load Task ID

SELECT @@gbase_loader_last_task_id;

Then query error details with that ID:

SELECT * FROM gclusterdb.load_error_log
WHERE task_id = 'your_task_id' LIMIT 100;

1.4 Error Data Collection

Enable error collection in the gcluster configuration file (gbase.cnf) for production:

gbase_loader_logs_collect = ON

1.5 Load Performance Parameters

Parameter	Scope	Description	Recommended
gcluster_loader_max_data_processors	gcluster	Max concurrent load processing threads	CPU cores / 2
gcluster_loader_min_chunk_size	gcluster	Chunk size sent to gnode (bytes)	64 MB
gbase_loader_parallel_degree	gnode	Parallel write threads on gnode	4 – 8
gbase_loader_buffer_count	gnode	Number of load buffers	4

2. Audit Log Configuration and Analysis

2.1 Enabling Audit Logs

Configure in both gcluster and gnode gbase.cnf files:

audit_log       = ON
log_output      = FILE          # or TABLE
long_query_time = 5             # seconds

2.2 Querying When log_output = TABLE

-- Recent slow queries
SELECT
    start_time, user_host, query_time, lock_time,
    rows_sent, rows_examined, db,
    SUBSTR(sql_text, 1, 200) AS sql_snippet
FROM gclusterdb.slow_log
ORDER BY start_time DESC LIMIT 50;

-- Top SQL patterns by average execution time
SELECT
    SUBSTR(sql_text, 1, 100) AS sql_pattern,
    COUNT(*) AS exec_count,
    AVG(query_time) AS avg_time,
    MAX(query_time) AS max_time,
    SUM(rows_examined) AS total_rows_scanned
FROM gclusterdb.slow_log
WHERE start_time >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY sql_pattern
ORDER BY avg_time DESC LIMIT 20;

2.3 Node‑Level SQL Execution Time Monitoring

Set the threshold in gcluster gbase.cnf:

gcluster_dql_statistic_threshold = 3000   # milliseconds

Query per‑node execution times:

SELECT
    sql_id, node_name, exec_time, rows_processed
FROM gclusterdb.dql_statistic
WHERE exec_time > 3000
ORDER BY sql_id, exec_time DESC;

If one node's exec_time is far higher than the others, suspect data skew or a hardware issue.

3. Memory Parameter Tuning

3.1 Memory Hierarchy

The gnode process memory is governed by gbase_memory_pct_target (percentage of system memory). Beneath it, heap memory is split into gbase_heap_data (normal operations) and gbase_heap_large (heavy operations like sorts/joins), plus multiple operation‑level buffers.

3.2 Key Parameters

Parameter	Scope	Description	Typical Value
gbase_memory_pct_target	gnode	% of system memory for gnode	70 – 80
gbase_heap_data	gnode	Heap for normal ops (MB)	30% of total memory
gbase_heap_large	gnode	Heap for large ops (MB)	30% of total memory
gbase_buffer_hj	gnode	Hash Join buffer (MB)	512 – 2048
gbase_buffer_sort	gnode	Sort buffer (MB)	512 – 2048
gbase_buffer_hgrby	gnode	Hash Group By buffer (MB)	512 – 1024

3.3 Example Configuration (64 GB Physical RAM Node)

# gnode gbase.cnf
gbase_memory_pct_target     = 75      # gnode uses 48 GB
gbase_heap_data             = 16384   # 16 GB
gbase_heap_large            = 16384   # 16 GB
gbase_buffer_hj             = 2048
gbase_buffer_hgrby          = 1024
gbase_buffer_distgrby       = 1024
gbase_buffer_sort           = 1024
gbase_buffer_rowset         = 256
gbase_buffer_result         = 512
gbase_buffer_insert         = 256

3.4 Monitoring Actual Memory Usage

Enable session memory statistics:

_gbase_session_memory_stat = 1

Query per‑session memory consumption:

SELECT
    session_id, user, db,
    ROUND(memory_used / 1024 / 1024, 2) AS memory_mb,
    state, SUBSTR(info, 1, 100) AS sql_snippet
FROM gclusterdb.session_memory_stat
ORDER BY memory_used DESC LIMIT 20;

3.5 Hot Data Eviction Under Memory Pressure

In gnode gbase.cnf:

_gbase_cache_drop_hot_data           = 1
_gbase_cache_drop_unlock_cell_count  = 1000
_gbase_cache_drop_delay_time        = 100

4. Connection and Timeout Quick Reference

Key timeout parameters in gcluster gbase.cnf include connect_timeout (handshake), read/write timeouts, internal reconnect settings, gcluster_lock_timeout, and Wait_timeout for idle sessions. JDBC clients should also specify connectTimeout and socketTimeout in the URL.

5. Daily Operations Checklist

Daily checks:

-- 1. Node status
SELECT node_name, status, last_heartbeat_time
FROM gclusterdb.node_info
ORDER BY node_name;

-- 2. Yesterday's load failure rate
SELECT
    table_name,
    COUNT(*) AS total_tasks,
    SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END) AS failed_tasks,
    SUM(error_rows) AS total_error_rows
FROM gclusterdb.load_task
WHERE DATE(start_time) = CURDATE() - INTERVAL 1 DAY
GROUP BY table_name
HAVING failed_tasks > 0 OR total_error_rows > 0;

-- 3. Long‑running active transactions
SELECT * FROM information_schema.processlist
WHERE time > 300
ORDER BY time DESC;

Weekly checks:

-- 4. Data volume balance across nodes
SELECT
    node_name,
    ROUND(SUM(data_size) / 1024 / 1024 / 1024, 2) AS data_gb
FROM gclusterdb.segment_info
GROUP BY node_name
ORDER BY data_gb DESC;

-- 5. Top 10 slow queries of the week
SELECT
    SUBSTR(sql_text, 1, 150) AS sql,
    COUNT(*) AS cnt,
    ROUND(AVG(query_time), 2) AS avg_sec,
    MAX(query_time) AS max_sec
FROM gclusterdb.slow_log
WHERE start_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY sql
ORDER BY avg_sec DESC
LIMIT 10;

Regularly inspecting system tables under gclusterdb helps you spot potential issues before they impact your gbase database.

推荐订阅源

DEV Community