Enabling and Querying Slow Logs in GBase 8c

Cong Li - Jul 4 - - Dev Community

GBase 8c allows you to locate issues and diagnose problems using slow logs. Here is how to configure and use slow logs in GBase 8c:

1. Slow Log Configuration

(1) Relevant GUC Parameters

The main configuration parameters for GBase 8c slow logs are:

  • enable_stmt_track

    • on: Default value, enables capture of Full/Slow SQL.
    • off: Disables capture of Full/Slow SQL.

  • track_stmt_stat_level

    • Composite parameter in the form of 'full sql stat level, slow sql stat level'.
    • The first part is the full SQL tracking level, with values: OFF, L0, L1, L2.
    • The second part is the slow SQL tracking level, with values: OFF, L0, L1, L2.
    • Recommended value: OFF,L0.

  • log_min_duration_statement

    • Time threshold: when a statement's execution time exceeds this value, its execution information is logged.
    • Value range: -1 to 2147483647 milliseconds. Default value: 30min.

  • instr_unique_sql_count

    • When the number of unique SQL entries generated in the system exceeds instr_unique_sql_count, automatic cleanup occurs or logging stops.
    • Recommended value: 200000.

  • track_stmt_details_size

    • Maximum size of a single statement's collected SQL, default value: 4096 byte.

  • track_stmt_retention_time

    • Composite parameter controlling the retention time for full/slow SQL records, read every 60 seconds.
    • Format: 'full sql retention time, slow sql retention time'.
    • Full SQL retention time range: 0 to 86400.
    • Slow SQL retention time range: 0 to 604800.
    • Default value: 3600,604800.

(2) Method to Configure Slow Logs

First, log in as the database installation user:

su - gbase
Enter fullscreen mode Exit fullscreen mode

Then execute:

gs_guc reload -Z coordinator -N all -I all -c "enable_stmt_track = ON"
gs_guc reload -Z coordinator -N all -I all -c "track_stmt_stat_level = 'OFF,L0'"
gs_guc reload -Z coordinator -N all -I all -c "log_min_duration_statement = 1000"
gs_guc reload -Z coordinator -N all -I all -c "instr_unique_sql_count = 200000"
gs_guc reload -Z coordinator -N all -I all -c "track_stmt_retention_time = '3600,10800'"
Enter fullscreen mode Exit fullscreen mode

2. Querying Slow Logs

Log in to the database to query slow logs and full logs within the GBase 8c database. Note that queries need to be performed in the postgres system library.

Slow Log Query Views: 

dbe_perf.get_global_slow_sql_by_timestamp(start_timestamp timestamp with time zone, end_timestamp timestamp with time zone);
Enter fullscreen mode Exit fullscreen mode

Slow Log Query: 

select * from dbe_perf.get_global_slow_sql_by_timestamp(start_timestamp, end_timestamp);
Enter fullscreen mode Exit fullscreen mode

Full Log Query Views: 

dbe_perf.get_global_full_sql_by_timestamp(start_timestamp timestamp with time zone, end_timestamp timestamp with time zone);
Enter fullscreen mode Exit fullscreen mode

Full Log Query: 

select * from dbe_perf.get_global_full_sql_by_timestamp(start_timestamp, end_timestamp);
Enter fullscreen mode Exit fullscreen mode

3. Common SQL Optimization Techniques in GBase 8c

GUC Parameter Optimization

Optimize execution plans by setting GUC parameters.

Table Definition Optimization

Choose appropriate table definitions based on business needs, including storage models, table distribution keys, partition tables, suitable data types, and indexes.

Table definition optimization includes:

  • Storage Mode: GBase8c supports row storage, column storage, and in-memory storage. Choose the appropriate mode based on the business scenario.
  • Distribution Key: In a distributed structure, data is split according to the specified distribution key (column). If data needed for a join or operation is on different nodes, redistribution may be required, reducing efficiency. Keeping related data on the same node reduces data interaction during SQL execution, improving efficiency.
  • Field Types and Lengths: Design appropriate field types and lengths; create suitable indexes; use partition tables where necessary.

Example: Efficiency difference due to different distribution keys:

-- When join field is a distribution key, the execution is pushed down to DN nodes for higher efficiency.
postgres=# EXPLAIN SELECT * FROM td1,td2 WHERE td1.a=td2.c ORDER BY a;
                                 QUERY PLAN
------------------------------------------------------------------------------
Remote Subquery Scan on all (dn1,dn2,dn3)  (cost=2.04..2.05 rows=1 width=16)
  ->  Sort  (cost=2.04..2.05 rows=1 width=16)
        Sort Key: td1.a
        ->  Nested Loop  (cost=0.00..2.03 rows=1 width=16)
              Join Filter: (td1.a = td2.c)
              ->  Seq Scan on td1  (cost=0.00..1.01 rows=1 width=8)
              ->  Seq Scan on td2  (cost=0.00..1.01 rows=1 width=8)
(7 rows)

-- When join field is not a distribution key, data is pulled to CN node for execution, resulting in lower efficiency.
postgres=# EXPLAIN SELECT * FROM td1,td2 WHERE td1.b=td2.b ORDER BY a;
                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------
Remote Subquery Scan on all (dn1,dn2,dn3)  (cost=2.04..2.05 rows=1 width=16)
  ->  Sort  (cost=2.04..2.05 rows=1 width=16)
        Sort Key: td1.a
        ->  Nested Loop  (cost=0.00..2.03 rows=1 width=16)
              Join Filter: (td1.b = td2.b)
              ->  Remote Subquery Scan on all (dn1,dn2,dn3)  (cost=100.00..101.02 rows=1 width=8)
                    Distribute results by H: b
                    ->  Seq Scan on td1  (cost=0.00..1.01 rows=1 width=8)
              ->  Materialize  (cost=100.00..101.03 rows=1 width=8)
                    ->  Remote Subquery Scan on all (dn1,dn2,dn3)  (cost=100.00..101.02 rows=1 width=8)
                          Distribute results by H: b
                          ->  Seq Scan on td2  (cost=0.00..1.01 rows=1 width=8)
(12 rows)
Enter fullscreen mode Exit fullscreen mode

Statistical Information Optimization

GBase 8c generates optimal execution plans based on cost estimation using analyze collected statistics. Statistics are crucial for optimizer estimations and cost calculations. Analyze execution plans to find inefficient operators and optimize or force plan changes. Typically, GBase8c chooses the best plan during execution, with manual adjustments needed only if they improve efficiency.

Operator-Level Optimization

Queries go through multiple operator steps to produce the final result. Performance issues often arise from bottleneck operators. Use EXPLAIN ANALYZE or PERFORMANCE commands to identify bottlenecks and optimize accordingly.

SQL Query Rewriting

Adjust SQL queries following specific rules to improve execution efficiency while ensuring correct results.

Example: Using exists instead of in:

-- Inefficient query using IN
select st.id,st.name from stu st where st.age=5 and st.id not in (
select stu_id from stu_info where code in ('a','b','c','d')
);
test=# explain select st.id,st.name from stu st where st.age=5 and st.id not in (
test(# select stu_id from stu_info where code in ('a','b','c','d')
test(# );
                                           QUERY PLAN
--------------------------------------------------------------------------------------------------
LightProxy  (cost=0.00..0.00 rows=1000 width=222)
  Node/s: All datanodes
  ->  Nested Loop Anti Join  (cost=0.00..2.22 rows=1 width=10)
        Join Filter: ((st.id = stu_info.stu_id) OR (st.id IS NULL) OR (stu_info.stu_id IS NULL))
        ->  Seq Scan on stu st  (cost=0.00..1.06 rows=1 width=10)
              Filter: (age = 5)
        ->  Materialize  (cost=0.00..1.09 rows=4 width=4)
              ->  Seq Scan on stu_info  (cost=0.00..1.07 rows=4 width=4)
                    Filter: ((code)::text = ANY ('{a,b,c,d}'::text[]))
(9 rows)


-- Efficient query using EXISTS
select st.id,st.name from stu st where st.age=5 and not exists (
select 1 from stu_info si where si.stu_id=st.id and si.code in ('a','b','c','d')
);
test=# explain select st.id,st.name from stu st where st.age=5 and not exists (
test(# select 1 from stu_info si where si.stu_id=st.id and si.code in ('a','b','c','d')
test(# );
                              QUERY PLAN
-------------------------------------------------------------------------
LightProxy  (cost=0.00..0.00 rows=1000 width=222)
  Node/s: All datanodes
  ->  Hash Right Anti Join  (cost=1.07..2.15 rows=1 width=10)
        Hash Cond: (si.stu_id = st.id)
        ->  Seq Scan on stu_info si  (cost=0.00..1.07 rows=4 width=4)
              Filter: ((code)::text = ANY ('{a,b,c,d}'::text[]))
        ->  Hash  (cost=1.06..1.06 rows=1 width=10)
              ->  Seq Scan on stu st  (cost=0.00..1.06 rows=1 width=10)
                    Filter: (age = 5)
(9 rows)
Enter fullscreen mode Exit fullscreen mode

Other optimizations include avoiding SELECT *, using LIMIT to restrict rows returned, avoiding functions in WHERE conditions, and combining multiple inserts into batch inserts.

Image
Magia Aranżacji Wnętrz: Twórz Przestrzeń, Która Inspirowałaby
Image
"TypeScript vs JavaScript: Which One Should You Choose?"

typescript, javascript, webdev, beginners
Image
12 Car Key Cutting Manchester Facts To Bring You Up To Speed The Cooler. Cooler
Image
Understanding the this Keyword in JavaScript: A Comprehensive Guide for Developers

webdev, javascript, beginners, programming
Image
Expert Home Maintenance Services in Dubai: Ensuring Comfort and Efficiency

javascript, tutorial, ai, productivity
Image
The AI Interview Revolution: Can Robots Really Hire Better?
Image
https://www.facebook.com/share/p/rdd2JuAWbZUVaKLp/
Image
Mastering the Basics: Capturing Wi-Fi Handshakes with Airodump-ng
Image
20 Tools That Will Make You More Efficient With Folding Wheelchairs Uk
Image
Generative Adversarial Networks
Image
Mastering Encapsulation in Java: A Comprehensive Guide with Examples

webdev, java, programming, beginners
Image
Internet Connection in Batlagundu | Broadband Connection in Batlagundu
Image
The Little Known Benefits Of Black Sectional With Chaise
Image
6 Best Social Media Tools to Keep Using in 2024
Image
Cat Flap Installation Cost
Image
My Journey into Embedded Software Engineering #4: Tackling Legacy Code and Learning on the Fly

programming, softwaredevelopment, softwareengineering, raspberrypi
Image
Installing a Cat Flap in a UPVC Door Cost
Image
Cat Flap Fitting Near Me
Image
Pay Attention: Watch Out For How Sectional With Chaise Is Taking Over And What To Do About It
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Terabox Video Player