Cloud SQL 查詢洞察簡介

1. 事前準備

Cloud SQL 查詢洞察可協助您偵測、診斷及預防 Cloud SQL 資料庫的查詢效能問題。這項功能提供自助式直覺化監控工具和診斷資訊，協助您在偵測到效能問題後，進一步找出根本原因。

在本程式碼研究室中，您將瞭解如何設定 PostgreSQL 適用的 Cloud SQL 執行個體、部署 Node.js 應用程式，將 Cloud SQL 執行個體做為後端儲存空間，然後使用查詢洞察功能查看及監控查詢。

必要條件

• 對 Node.js 程式設計語言和工具有基本的瞭解

執行步驟

• 在 Node.js 應用程式中使用 Cloud SQL。
• 在 Node.js 應用程式中啟用 SQL Commenter。
• 使用 Cloud SQL 的查詢洞察功能，監控及調查查詢效能。

軟硬體需求

• 您有權啟用 API 和建立服務的 Google Cloud 帳戶

2. 設定和需求條件

自修實驗室環境設定

1. 登入 Cloud 控制台，然後建立新專案或重複使用現有專案。(如果沒有 Gmail 或 Google Workspace 帳戶，請先建立帳戶)。

請記住您使用的專案 ID。本程式碼研究室稍後會將其稱為 PROJECT-ID。

2. 接著，您必須在 Cloud 控制台中啟用帳單，才能使用 Google Cloud 資源。

完成本程式碼研究室的費用應該不高，甚至完全免費。請務必按照「清除並瞭解詳情」一節的指示操作，瞭解如何停用資源，避免在本教學課程結束後繼續產生帳單費用。Google Cloud 新使用者可參加價值$300 美元的免費試用計畫。

啟用 Cloud Shell

1. 在 Cloud 控制台，點選「啟用 Cloud Shell」圖示。

如果您是首次啟動 Cloud Shell，系統會顯示中繼畫面 (摺疊式螢幕下方)，說明這個指令列環境。點選「繼續」後，這則訊息日後就不會再出現。以下是這個初次畫面的樣子：

佈建並連至 Cloud Shell 預計只需要幾分鐘。

這部虛擬機器搭載各種您需要的開發工具，並提供永久的 5GB 主目錄，而且可在 Google Cloud 運作，大幅提升網路效能並強化驗證功能。

2. 在 Cloud Shell 中執行下列指令，確認您使用的專案正確無誤：

連線至 Cloud Shell 後，您應會發現自己通過驗證，且專案已設為您的專案 ID。

注意：gcloud 指令列工具是 Google Cloud 中功能強大的整合式指令列工具，並已預先安裝於 Cloud Shell。你會發現它支援 Tab 鍵自動完成功能。詳情請參閱 gcloud 指令列工具總覽。

執行下列指令，確認您使用的專案正確無誤。

gcloud config list project

如要使用與開啟 Cloud Shell 時所選專案不同的專案，請執行下列指令來設定新專案：

gcloud config set project <PROJECT-ID>;

3. 設定已啟用查詢洞察的 PostgreSQL 適用的 Cloud SQL 執行個體

1. 啟動 Cloud Shell 後，您可以使用指令列建立名為 my-instance 的新 Cloud SQL 執行個體，並啟用查詢洞察：

注意：在本程式碼研究室中，您將透過指令列啟用查詢深入分析功能，但您也可以在執行個體的「查詢深入分析」選單中，透過控制台啟用這項功能。

gcloud sql instances create my-instance --tier db-f1-micro --database-version=POSTGRES_12 --region=us-central --root-password=<PASSWORD> --insights-config-query-insights-enabled --insights-config-record-application-tags --insights-config-record-client-address

以下簡要說明各個標記及其意義：

• --tier db-f1-micro 旗標會指定資源最少的機器類型，因為這是為了開發用途，您不需要大量資源來完成程式碼研究室。如要進一步瞭解等級，請參閱這篇文章。
• --database-version=POSTGRES_12 旗標會建立 PostgreSQL 12 版的執行個體。
• --region=us-central 旗標會指定要建立執行個體的區域。
• 您可以使用 --root-password=<PASSWORD> 旗標指定超級使用者 postgres 的密碼。請務必將 <PASSWORD> 替換為您選擇的密碼。
• --insights-config-query-insights-enabled 旗標可啟用執行個體的查詢洞察功能。
• --insights-config-record-application-tags 旗標可讓系統記錄應用程式標記。我們會在後續章節中進一步說明應用程式標記。
• --insights-config-record-client-address 旗標可讓查詢洞察記錄用戶端 IP 位址。

系統可能會提示您為專案啟用 API sqladmin.googleapis.com。如果系統提示您啟用 API，請選取 y。

建立執行個體需要幾分鐘的時間。這項作業完成後，執行個體即可使用。

2. 現在請建立要用於範例應用程式的資料庫：

gcloud sql databases create votesdb --instance my-instance

您也可以透過 Cloud 控制台存取及設定執行個體。

3. 執行下列指令，取得格式為 PROJECT-ID:ZONE-ID:INSTANCE-ID 的執行個體連線名稱。稍後設定 Node.js 應用程式時會用到這個值。

gcloud sql instances describe my-instance | grep connectionName

4. 建立服務帳戶，供應用程式使用

服務帳戶用於授予權限，以便在 GCP 專案中使用不同服務。在本程式碼研究室中，您需要一個服務帳戶，才能授予 Cloud SQL Proxy 連線至 Cloud SQL 執行個體的權限。

在控制台中建立服務帳戶

1. 前往 IAM 服務帳戶頁面，然後按一下頁面頂端的 按鈕。
2. 為服務帳戶命名並指派專屬 ID，然後按一下「建立」。
3. 在下一頁中，按一下「選取角色」的下拉式選單。篩選「Cloud SQL」，然後選取 Cloud SQL 用戶端角色。依序點選「繼續」和「完成」。
4. 建立服務帳戶後，按一下新服務帳戶「動作」下方的三點圖示，然後選擇「管理金鑰」。在下一個頁面中，依序選取「新增金鑰」和「建立新的金鑰」。系統會選取 JSON，請保留預設值，然後按一下「建立」。系統會下載 .json 私密金鑰檔案。按一下「關閉」。
5. 在 Cloud Shell 中，按一下「More」(更多) 選單的三點圖示，然後選擇「Upload File」(上傳檔案)。瀏覽至您在本機下載的 .json 檔案，然後選取該檔案。這會將 .json 檔案上傳至 Cloud Shell 的主目錄。

5. 安裝並啟動 Cloud SQL Proxy

您將使用 Cloud SQL Proxy 在應用程式和資料庫執行個體之間進行通訊。

1. 下載 Cloud SQL Proxy。在 Cloud Shell 中，您可以執行：

wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy && chmod +x cloud_sql_proxy

2. 將 <INSTANCE_CONNECTION_NAME> 替換成從 Cloud SQL 執行個體「總覽」頁面複製的執行個體連線名稱，然後執行 Proxy，如下所示。

./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:5432 &

如果成功，您應該會看到幾行輸出內容，最後是 Ready for new connections 訊息。

6. 複製並在本機測試應用程式

1. 複製範例應用程式的存放區，並安裝執行應用程式所需的套件。

git clone https://github.com/GoogleCloudPlatform/nodejs-docs-samples/

cd nodejs-docs-samples/cloud-sql/postgres/knex

npm install

2. 請設定下列環境變數：

export INSTANCE_CONNECTION_NAME='<PROJECT-ID>:<ZONE-ID>:<INSTANCE-ID>'
export DB_HOST='127.0.0.1:5432'
export DB_USER='postgres'
export DB_PASS='<PASSWORD>'
export DB_NAME='votesdb'

3. 啟動範例應用程式。

npm start

4. 在 Cloud Shell 中按一下「Web Preview」(網頁預覽) ，然後選取「Preview on port 8080」(透過以下通訊埠預覽：8080)。

您應該會在瀏覽器中看到「Tabs vs Spaces」投票應用程式，如下所示：

5. 點選按鈕進行投票，並將資料儲存到資料庫。

7. 新增頁面即可查看所有票數

由於這個範例應用程式非常簡單，因此您會新增一個頁面，顯示所有投票。這麼做的主要原因，是為了在稍後使用查詢深入分析時，有更多資料可供參考。

1. 在 Cloud Shell 中輸入 Ctrl+c，停止範例應用程式。
2. 在 Cloud Shell 中，按一下 按鈕啟動 Cloud Shell 編輯器。
3. 在檔案總管中找出 nodejs-docs-samples/cloud-sql/postgres/knex/server.js，然後點選該檔案，在編輯器中載入 server.js 檔案。

在定義 getVotes 函式的位置後方，新增下列程式碼：

/**
 * Retrieve all vote records from the database.
 *
 * @param {object} pool The Knex connection object.
 * @returns {Promise}
 */
const getAllVotes = async pool => {
  return await pool
    .select('candidate', 'time_cast')
    .from('votes')
    .orderBy('time_cast', 'desc');
};

4. 在定義其他路徑的位置下方，新增 '/getAllVotes' 路徑的下列程式碼：

app.get('/getAllVotes', async (req, res) => {
  pool = pool || createPool();
  try {
    // Query all votes from the database.
    const votes = await getAllVotes(pool);

    res.render('allvotes.pug', {
      votes: votes,
    });
  } catch (err) {
    console.error(err);
    res
      .status(500)
      .send('Unable to load page; see logs for more details.')
      .end();
  }
});

5. 在 nodejs-docs-samples/cloud-sql/postgres/knex/views 目錄中新建名為 allvotes.pug 的檔案。貼上下列程式碼：

doctype html
html(lang="en")
  head
    title Tabs VS Spaces

    link(rel="stylesheet", href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css")
    link(rel="stylesheet", href="https://fonts.googleapis.com/icon?family=Material+Icons")
    script(src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js")
  body

    nav(class="red lighten-1")
      div(class="nav-wrapper")
        a(href="#" class="brand-logo center") Tabs VS Spaces

    div(class="section")

      h4(class="header center") Recent Votes
      ul(class="container collection center")
        each vote in votes
          li(class="collection-item avatar")
            if vote.candidate.trim() === 'TABS'
              i(class="material-icons circle green") keyboard_tab
            else
              i(class="material-icons circle blue") space_bar
            span(class="title") A vote for <b>#{vote.candidate}</b>
            p was cast at #{vote.time_cast}.

6. 按一下 按鈕返回 Cloud Shell，然後執行下列指令：

npm start

7. 從網頁預覽開啟應用程式，確認是否正常運作。在瀏覽器的網址中加入 /getAllVotes，即可查看新增的頁面。

8. 在應用程式中啟用 SQL Commenter

現在要安裝並啟用 SQL Commenter，這是一項開放原始碼程式庫，可讓 ORM 在執行前，為 SQL 陳述式加上註解。SQLcommenter 支援多種 ORM 和架構，包括範例應用程式使用的 Knex.js。查詢深入分析會使用這些註解中的資訊，提供以應用程式為中心的資料庫效能檢視畫面，並找出導致問題的應用程式程式碼。預期效能負擔不會太大。請參閱查詢洞察說明文件。

1. 在 Cloud Shell 中輸入 Ctrl+c，停止範例應用程式。
2. 執行下列指令，安裝 SQLcommenter 必要的套件：

  npm install @google-cloud/sqlcommenter-knex @opencensus/nodejs @opencensus/propagation-tracecontext @opentelemetry/api @opentelemetry/core --save

3. 在 Cloud Shell 中，按一下 按鈕啟動 Cloud Shell 編輯器。
4. 在檔案總管中找出 nodejs-docs-samples/cloud-sql/postgres/knex/server.js，然後點選該檔案，在編輯器中載入 server.js 檔案。
5. 在檔案中找出下列程式碼：

const process = require('process');

在下方新增下列程式碼：

const {wrapMainKnexAsMiddleware} = require('@google-cloud/sqlcommenter-knex');

6. 在檔案中找出下列程式碼：

// Set Content-Type for all responses for these routes.
app.use((req, res, next) => {
  res.set('Content-Type', 'text/html');
  next();
});

在下方新增下列程式碼：

app.use(wrapMainKnexAsMiddleware(Knex, {
    traceparent: true,
    tracestate: true,
    route: true,
    db_driver: true
}));

完成後，程式碼應如下所示：

...
// Require process, so we can mock environment variables.
const process = require('process');

const {wrapMainKnexAsMiddleware} = require('@google-cloud/sqlcommenter-knex');
const express = require('express');
const Knex = require('knex');
const fs = require('fs');

const app = express();
app.set('view engine', 'pug');
app.enable('trust proxy');

// Automatically parse request body as form data.
app.use(express.urlencoded({extended: false}));
// This middleware is available in Express v4.16.0 onwards
app.use(express.json());

// Set Content-Type for all responses for these routes.
app.use((req, res, next) => {
  res.set('Content-Type', 'text/html');
  next();
});

app.use(wrapMainKnexAsMiddleware(Knex, {
    traceparent: true,
    tracestate: true,
    route: true,
    db_driver: true
}));
...

7. 按一下 按鈕返回 Cloud Shell，然後執行下列指令：

npm start

8. 在「Tabs vs Spaces」應用程式中，按一下按鈕即可投下更多票數，為資料庫新增更多資料。

9. 使用「洞察」查看查詢效能和端對端追蹤記錄

「查詢洞察」資訊主頁可協助您排解 Cloud SQL 查詢問題，找出效能問題。如要存取「洞察」，請在 Cloud SQL 執行個體的左側導覽中選取「查詢洞察」。

資料庫負載 - 所有查詢圖表

頂層的「查詢洞察」資訊主頁會顯示「資料庫負載 - 所有查詢」圖表。

圖表包含 CPU 容量、CPU 和 CPU 等待、IO 等待和鎖定等待的資訊。如要進一步瞭解這些指標的意義、指標的儲存位置，以及問題查詢的圖表範例，請參閱說明文件。以這個範例應用程式來說，資料庫查詢負載偏低，因此圖表上不會出現大幅尖峰。

哪些查詢造成最多負載？

圖表下方會顯示「查詢」表格，其中包含所選時間範圍的正規化查詢。表格中的查詢會依總執行時間排序。

您可以點選個別查詢，查看查詢的詳細資訊，例如特定查詢的資料庫負載、查詢延遲、查詢計畫範例和頂尖使用者。如果應用程式是使用 ORM 建構而成 (如範例應用程式)，您可能不知道應用程式的哪個部分負責哪個查詢。「熱門標記」部分可協助您找出原因。

應用程式中查詢負載的來源為何？

在「查詢」表格和「標記」表格之間切換，即可查看依業務邏輯標記的查詢清單，以應用程式為中心檢視資料。

在「標記」表格中，您可以查看資料庫負載，並依據產生負載的路徑細分。從上方的螢幕截圖中，您可以看到 '/getAllVotes' 路線的平均執行時間較長，且平均傳回的資料列數較多。雖然表格中顯示的執行時間在這個案例中並無問題，但我們還是點選 '/getAllVotes' 的資料列，進一步查看資料。

為什麼查詢執行速度緩慢？

按一下「查詢計畫範例」圖表中的點，即可查看查詢計畫。

查詢方案會顯示 PostgreSQL 在幕後執行查詢的方式，方便您判斷是否有導致速度變慢的作業。

是哪個應用程式程式碼導致速度變慢？

查詢洞察也提供端對端追蹤的脈絡內視覺化，有助於進一步調查應用程式的哪些部分會產生緩慢的查詢。

按一下「END TO END」(端對端) 分頁標籤，即可查看相關追蹤記錄。

注意：如要進一步瞭解如何使用及解讀查詢計畫和端對端追蹤，請參閱說明文件。

10. 清理並瞭解詳情

您已學會如何使用查詢洞察，透過 Node.js 應用程式和 Cloud SQL PostgreSQL 資料庫監控及調查查詢效能！

清除

如果不想讓 Cloud SQL 執行個體繼續運作，可以立即刪除。

gcloud sql instances delete my-instance

瞭解詳情

• 查詢洞察說明文件
• 啟用 Sqlcommenter 的查詢標記功能，瞭解應用程式對資料庫效能的影響
• SQL Commenter 說明文件
• SQL Commenter GitHub 存放區