使用SQL診斷功能最佳化執行個體 - Hologres

Hologres提供慢Query日誌，預設採集大於100ms的DML和所有DDL。基於慢Query日誌的資料，Hologres提供SQL診斷的能力，通過對不同維度趨勢、明細分析，可以輔助您瞭解執行個體的使用方式並做相應的最佳化，以達到更好的效果。本文為您介紹如何使用SQL診斷。

功能介紹與建議

當前SQL診斷支援的診斷內容如下：

診斷項	說明
Query總數	所選時間段的Query總數。
成功Query	所選時間段成功的Query總數。
失敗Query	所選時間段失敗的Query總數。
失敗Query明細	展示錯誤碼和對應的失敗次數，方便治理錯誤的Query，同時展示出典型的Query和錯誤明細。您可以跳轉至Query洞察查看更詳細的內容，也可以通過錯誤碼對照表查看詳細的錯誤原因，以提升執行個體穩定性。
成功和失敗Query趨勢	展示所選時間段的成功和失敗的Query次數比例，方便瞭解當前執行個體的Query整體運行情況。
Query耗時佔比趨勢	展示所選時間段的Query耗時佔比趨勢。預設只分析`SELECT`、`INSERT`、`UPDATE`、`DELETE`這四類Query的耗時佔比情況。
DML趨勢	展示DML Query的執行次數趨勢。預設只分析`SELECT`、`INSERT`、`UPDATE`、`DELETE`這四類Query。
DDL趨勢	展示DDL Query的執行次數趨勢。僅包含`CREATE TABLE`、`DROP TABLE`、 `TRUNCATE TABLE`、`ALTER TABLE`、`CALL`、`CREATE EXTENSION`、`CREATE FOREIGN TABLE`、`ALTER FOREIGN TABLE`、`IMPORT FOREIGN SCHEMA`、`DROP FOREIGN TABLE`、`CREATE SCHEMA`、`CREATE VIEW`、`DROP VIEW`、`GRANT`、`CREATE ROLE`、`ALTER ROLE`、`COMMENT`這些DDL。
Query應用來源佔比情況或趨勢	展示`application_name`維度Query佔比情況或趨勢。可以從佔比中分析佔比較多的應用，同時也建議給不同的任務類型設定相關的`application_name`，這樣可以更好的應用來源佔比圖和趨勢圖，觀察異常任務。
使用者維度Query佔比情況或趨勢	展示`usename`維度Query佔比情況或趨勢。可以更加明顯地分析出使用者Query執行情況，同時也建議不同的任務使用不同的使用者名稱，而不是使用同一個帳號，以便更好地排查異常。
執行引擎的Query趨勢	展示所選時間段內，不同執行引擎的Query執行趨勢。 Hologres根據Query的特徵，會有不同的執行引擎，包括HQE、PQE、SDK或FixedQE等，詳情請參見產品架構。建議您盡量避免PQE的查詢，而是改寫成HQE的Query，以便獲得更好的效能。

注意事項

SQL診斷的資料去除了系統相關的SQL，篩選條件如下：

WHERE
usename != 'system'
AND client_addr != '127.0.0.1' 
AND (application_name IS NULL 
OR application_name NOT IN ('AutoPartition', 'holoweb_system', 'HgGenInQuery'))

hologres.hg_query_log表預設只能查詢大於1s的DML和所有DDL，SQL診斷預設分析大於100ms的所有DML和DDL，如果您發現hologres.hg_query_log統計的資料少於SQL診斷時，一般是因為hologres.hg_query_log只統計了大於1s的Query，您可以通過修改log_min_duration_statement參數採集大於100ms的Query，詳情請參見log_min_duration_statement。
SQL診斷同慢Query日誌，只能查看近一個月的資料。
資料是T+1更新。因此只能查T-1（昨天）及之前一個月的資料，可以根據業務情況進行時間維度的篩選。預設展示前一天的資料。可以根據業務情況進行時間維度的篩選。
SQL診斷的查詢許可權同慢Query日誌，授予查看許可權請參見授予查看許可權。

操作步驟

您可以通過HoloWeb可視化查看SQL診斷。

登入HoloWeb控制台，詳情請參見串連HoloWeb並執行查詢。
單擊頂部導覽列的診斷與最佳化。
在左側導覽列，選擇執行個體診斷>SQL診斷。

在SQL診斷頁面頂部，編輯查詢條件。

查詢條件參數：

參數	是否必選	說明
執行個體名	是	需要查詢慢Query的執行個體名稱，預設是當前登入的執行個體。
時間範圍	是	慢Query的時間範圍，預設選擇昨天，最多隻能選擇過去一個月的資料。

單擊提交，展示查詢結果。

SQL錯誤智能診斷

對於失敗的Query，HoloWeb中的SQL編輯器會展示SQL診斷的資訊，並且Query洞察支援SQL智能診斷，也會將失敗的原因和解決方案自動返回SQL編輯器並進行展示，從而協助業務更便捷地處理失敗Query。樣本如下圖。

holo1

錯誤碼對照表

錯誤碼	說明	常見的完整報錯	解決方案
HG_ERRCODE_FDW_ERROR	MaxCompute外部表格的中繼資料匯入至Hologres時產生報錯，通常是由於不支援某種類型的表所導致。	`failed to import foreign schema from odps: Can't find file system factory`	詳情請參見HG_ERRCODE_FDW_ERROR。
ERRCODE_FDW_ERROR	外部表格查詢出現報錯。	`failed to import foreign schema from odps: Authorization Failed：xxx` `failed to import foreign schema from odps:Table not found -xxx`	根據具體的報錯解決，詳情請參見ERRCODE_FDW_ERROR。
ERRCODE_UNIQUE_VIOLATION pk violates	違反唯一性限制式，常出現在寫入時主鍵重複的情境。	`duplicate key value violates unique constraint DETAIL: xxx already exists.`	處理主鍵重複的資料。若是INSERT文法報錯，可以改寫成`insert into xx on conflict`的文法，實現主鍵去重，詳情請參見INSERT ON CONFLICT(UPSERT)。若是`insert into xx on conflict`文法報錯，為資料來源有重複資料導致，解決方案請參見INSERT ON CONFLICT(UPSERT)。
ERRCODE_CHECK_VIOLATION partition constraint	違反檢查約束，常發生在寫入Hologres分區表時，寫入的分區值與設定的分區值不一致。例如`20240301`的分區寫入了`0240229`的分區資料。	`new row for relation xx violates partition constraint DETAIL: Failing row contains (column1)=(xxxx).`	需要檢查分區資料和設定的分區值是否一致，並修改為一致。
ERRCODE_NOT_NULL_VIOLATION not-null constraint UsageProblem	違反非空約束。常發生在非空（not null）欄位寫入了空（null）資料。	`null value in column xxx violates not-null constraint DETAIL: Failing row contains (null).`	處理髒資料。
ERRCODE_UNDEFINED_TABLE	表不存在，一般出現在表剛剛建立未更新中繼資料或者Query執行過程中，表有TRUNCATE或DROP的情境。	`Dispatch query failed: Table not found`	可以使用Query洞察排查是否有同時TRUNCATE或DROP任務，然後重試任務，詳情請參見Query洞察。
ERRCODE_INTERNAL_ERROR ERPC_ERROR_CONNECTION_CLOSED	內部非預期錯誤，執行個體可能出現過宕機或者Query被意外中斷。	`Transaction xx is not found or it was expired and cancelled.` `Query is cancelled` `ERPC_ERROR_CONNECTION_CLOSED`	暫無。
ERRCODE_QUERY_CANCELED User canceled CANCELLED Query Is Cancelled InternalQueryIsClosed	查詢被取消，一般是因為設定了用戶端逾時，或者表被TRUNCATE或DROP了。	`ERROR: canceling statement due to statement timeout` `canceling statement due to user request`	解決方案請參見Query管理排查。
ERRCODE_FEATURE_NOT_SUPPORTED Unsupported Feature	有某個功能不支援。	`Dynamic partition selector is not supported` `ALTER TABLE CHANGE OWNER is not supported in SPM (Simple Permission Mode)` `Feature not supported: insert into parent table`	解決方案請參見Hologres SQL語句的常見問題。
ERRCODE_UNDEFINED_OBJECT	存在未定義的對象，一般是列不存在、Table Group不存在。	`column xxx does not exist` `Table group xxx does not exist.`	請先建立提示不存在的對象。確認報錯不存在的對象在SQL中是否填寫了正確名稱。
ERRCODE_INSUFFICIENT_PRIVILEGE permission denied	當前帳號許可權不足，需要授權。	`ERROR: permission denied for schema xxx` `ERROR: permission denied for foreign table table_info`	解決方案請參見Hologres許可權相關。
ERRCODE_OUT_OF_MEMORY OOM	Query因為記憶體不足，出現了OOM。	`Total memory used by all existing queries exceeded memory limitation`	解決方案請參見OOM常見問題排查指南。
ERRCODE_DATATYPE_MISMATCH Unmatched Data Row Schema Number Dataset Schema Not Match	類型不符，通常為運算式需要的類型與欄位的實際類型不符。	`unmatched data row schema number` `Datasets has different schema`	檢查SQL的列是否匹配。
ERRCODE_DIVISION_BY_ZERO division by zero	SQL中存在除數為`0`的情況。	`division by zero`	處理髒資料，或者使用GUC使除以0不報錯，詳情請參見函數使用。
ERRCODE_STRING_DATA_RIGHT_TRUNCATION	字串右截斷，多發生於VARCHAR欄位的實際值超過了建表時VARCHAR指定的長度。	`value too long for type character varying(xx)`	重建立表修改VARCHAR欄位的長度，或者將欄位類型設定為TEXT。
ERRCODE_PROGRAM_LIMIT_EXCEEDED Exceed Odps Scan Limit	超過Hologres允許的上限，通常發生在掃描外部表格分區表數量、讀的行數、讀的位元組等超過上限。	`number of read rows (xxxxx) exceeds limit (xxxxxxx)` `number of partitions (xxx) scanned for "xxxx" exceeds the maximum allowed (xxx)` `scan (xxx GB) for "xxxxx" exceeds the maximum allowed (xxx GB)`	外部表格查詢超過了限制，解決方案請參見對接MaxCompute常見問題與診斷。
ERRCODE_SYNTAX_ERROR	SQL語法錯誤。	`syntax error at or near "xxxxx"`	請您重新檢查SQL文法。
ERRCODE_UNDEFINED_FUNCTION	一般為不支援的函數功能，可能是因為函數文法使用錯誤，或者未建立Extension等，也可能是不支援某個函數。	`function xxxxx does not exist` `operator does not exist: xxxxxx`	請根據函數的文法進行操作，避免語法錯誤或者Extension未建立等，詳情請參見函數參考。
ERRCODE_E_R_E_READING_SQL_DATA_NOT_PERMITTED	沒有外部表格的讀許可權。	`check permission for foreign table scan failed: failed to check permission:MaxCompute error,Authorization Failed [4019], You have NO privilege 'odps:Select' on {xxxxxxxxxx}`	解決方案請參見MaxCompute許可權相關。
ERRCODE_DUPLICATE_OBJECT already exist	存在重複的對象，通常發生於建立重複的Extension、Publication、Role等。	`publication "xxxxx" already exists` `extension "xxxxx" already exists` `role "xxxxxxxx" already exists`	如果對象已經存在，不需要重複建立。
ERRCODE_INVALID_TEXT_REPRESENTATION invalid input	非法的文本運算式，常發生於字串轉換為其他類型時，字串的資料非法，比如Null 字元串（""）轉INT。	`invalid input syntax for integer: xxx`	處理髒資料。
ERRCODE_BAD_COPY_FILE_FORMAT	執行copy命令時檔案或資料的格式不正確，多發生於資料本身就包含了copy指定的分割符（比如空格），導致列的數量對不上。	`extra data after last expected column. failed to query next` `missing data for column "xxx". failed to query next`	處理髒資料。
ERRCODE_UNDEFINED_COLUMN	Query中有不存在的列。	`column xxxxx does not exist`	重新檢查SQL文法。
ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE	數實值型別超過範圍： numeric類型資料超過了numeric定義的範圍，比如類型為`decimal(4,2)`或`numeric(4,2)`時，整數部分最多兩位，但實際資料為100。 int或bigint超過了表示範圍。	`value "xxxxx" is out of range for type bigint` `numeric field overflow` `bigint out of range` `integer out of range`	檢查是否有髒資料或者類型定義錯誤，重新修改列類型。
ERRCODE_DATETIME_FIELD_OVERFLOW	timestamp、timestamptz、date、time、timetz等時間相關的欄位存在值溢出，比如`"877411-01-01 00:00:00+08"`寫入至timestamptz類型。	`date/time field value out of range: "xxxxxx"` `date out of range: "xxxxxx"`	處理髒資料。
ERRCODE_INVALID_PARAMETER_VALUE	非法參數值，一般是各種情況下的參數不符合要求。根據具體報錯資訊改進。例如報錯：`column "col" with type "float4" cannot be set as "bitmap_columns"` 表示float4類型不能設定為bitmap columns。	`mismatched properties: table orientation is "column" but storage format is "sst"` `resharding insert select table data failed : Dispatch query failed: internal error: Failed to get available shards for query` `InsertOverwrite insert select table data failed : column a.unsign_type does not exist`	重新檢查SQL文法。
ERRCODE_INVALID_DATETIME_FORMAT	非法的日期格式，日期資料不符合格式要求，比如`SELECT TO_DATE('aa', 'YYYY-MM-DD');`，請檢查資料。	`invalid input syntax for type timestamp: ""` `invalid input syntax for type date: ""` `invalid value "" for "yyyy"，Value must be an integer.`	處理髒資料。
ERRCODE_CHARACTER_NOT_IN_REPERTOIRE	字元不在編碼範圍，常見於出現了UTF-8編碼之外的非法字元。	`invalid byte sequence for encoding "UTF8": 0xe9 0x80`	處理髒資料。
ERRCODE_DUPLICATE_TABLE	重複的表，常見表已經存在時，又重複建同名表。	`relation "xxxx" already exists`	如果表已經存在，則不需要重複建立。
ERRCODE_UNTRANSLATABLE_CHARACTER	存在字元無法轉化為目標格式。	`character with byte sequence 0xe4 0x9e 0xab in encoding "UTF8" has no equivalent in encoding "GBK"` 說明表示UTF-8編碼的字元在目標GBK格式中無等價項。	處理髒資料。
ERRCODE_GROUPING_ERROR	分組錯誤，group by相關的錯誤。	`column "xxx" must appear in the GROUP BY clause or be used in an aggregate function`	重新檢查SQL文法，彙總函式的欄位需要包含在group by內。
ERRCODE_INVALID_TRANSACTION_STATE Usage Problem	非法的事務狀態。涉及事務的相關操作非法。比如CALL SET_TABLE_PROPERTY建立Distribution Key時和建表不在一個事務中。	`SET_TABLE_PROPERTY and CREATE TABLE statement are not in the same transaction`	`create table`語句需要和CALL SET_TABLE_PROPERTY在一個事務裡（使用`begin;`和`commit;`）。
ERRCODE_AMBIGUOUS_COLUMN	模稜兩可的列。一般是SQL中同一列名可能是不同列時報錯，例如`select id from t1 inner join t2 on t1.id = t2.id`語句中，查詢的`id`欄位沒有指明是屬於t1還是t2。	`column reference "xxx" is ambiguous`	重新檢查SQL文法。
ERRCODE_DUPLICATE_COLUMN	重複列，常發生在建表時同一欄位聲明了多次。	`column "xxx" specified more than once`	重新檢查SQL文法。
ERRCODE_AMBIGUOUS_FUNCTION	模稜兩可的函數。一般是函數支援多種類型的入參，但傳參的類型沒有指定清楚。	如函數to_char有`to_char(timestamp/timestamptz, text)`、 `to_char(double precision, text)`或`to_char(int, text)`三種形式，當輸入為`to_char('2024-02-22', 'YYYY-MM-DD')`時由於`'2024-02-22'`非以上三種形式類型，就會報錯。需要顯式指定類型轉換方可成功：`to_char('2024-02-22'::timestamptz, 'YYYY-MM-DD')`。	重新檢查SQL文法。
ERRCODE_INVALID_COLUMN_DEFINITION	非法的列定義，在Hologres中多是Numeric或Decimal類型未指明精度。	`invalid definition of a numeric type`	處理髒資料。
ERRCODE_INVALID_CATALOG_NAME ERRCODE_UNDEFINED_DATABASE	指定的資料庫不存在。	暫無	檢查資料庫是否存在。
ERRCODE_INVALID_CATALOG_NAME ERRCODE_UNDEFINED_DATABASE	指定的資料庫不存在。	暫無	檢查資料庫是否存在。
ERRCODE_CANNOT_COERCE	兩個類型資料之間無法轉化時報錯。	`cannot cast type date to integer`	重新檢查SQL文法。
ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST	依賴的對象仍存在，常見於刪除一個對象時，依賴其的對象仍然存在。比如刪除一個Schema時，該Schema下的表還未刪除。	暫無	處理相關依賴，詳情請參見刪除帳號。
ERRCODE_UNDEFINED_SCHEMA 或 ERRCODE_INVALID_SCHEMA_NAME	指定的Schema不存在。	`schema "xxxx" does not exist`	檢查Schema是否存在，不存在需要先建立。
ERRCODE_DUPLICATE_DATABASE	重複的資料庫，建立一個已存在的資料庫時報錯。	暫無	如果資料庫已經存在，無需重複建立。
AutoAnalyze-Failed	Auto Analyze因為某些原因失敗。	`query row count from analyze table` `query from analyze table`	Auto Analyze失敗，一般為後端原因，請提工單排查。
Import Foreign Table Not Found	找不到外部表格。	`failed to get foregin table split:Table not found` `Failed to get odps table:Not enable acid table` `failed to get foregin table split:% not found`	請檢查訪問的外部表格的是否存在。
Cannot Acquire Lock In Time	這個異常通常是由於拿鎖失敗，高並發查詢和刪除（Drop）同一張表時，後端節點出現死結，導致有關這張表的操作都卡住，從而報錯。	`internal error: Cannot acquire lock in time, current owners`	解決方案請參見鎖以及排查鎖。
OTHER QueryNextFTEFailed QueryNextPQEFailed ForeignSplitOrSchemaConnectionClosed ConnectionRefused ERPC_ERROR_TIMEOUT ERPC_ERROR_CONNECTION_CLOSED	非預期的報錯。	`kConnectError: channel is empty` `ERPC_ERROR_CONNECTION_CLOSED` `internal error: Connect timeout, err: std_exception: Connection refused`	非預期的報錯，可以提工單排查。

功能介紹與建議

Query總數

成功Query

失敗Query

失敗Query明細

成功和失敗Query趨勢

Query耗時佔比趨勢

DML趨勢

DDL趨勢

Query應用來源佔比情況或趨勢

使用者維度Query佔比情況或趨勢

執行引擎的Query趨勢

注意事項

操作步驟

SQL錯誤智能診斷

錯誤碼對照表