Cloud SQL 查詢洞察簡介

1. 事前準備

Cloud SQL 查詢洞察可協助您偵測、診斷及預防 Cloud SQL 資料庫的查詢效能問題。它的自助式服務、直覺式監控與診斷資訊，超越了偵測技術，可協助您找出效能問題的根本原因。

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

必要條件

• 熟悉 Node.js 程式設計語言和工具的基本知識

執行步驟

• 在 Node.js 應用程式中使用 Cloud SQL。
• 在 Node.js 應用程式中啟用 SQL 註解工具。
• 使用 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 只需幾分鐘的時間。

這部虛擬機器都裝載了您需要的所有開發工具。提供永久的 5 GB 主目錄，而且在 Google Cloud 中運作，大幅提高網路效能和驗證能力。

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

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

注意：gcloud 指令列工具是 Google Cloud 中功能強大的整合式指令列工具。已預先安裝於 Cloud Shell 中。您會發現系統支援分頁完成功能。如要瞭解詳情，請參閱 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。如果出現提示訊息，請選取 y 以啟用 API。

執行個體建立作業需要幾分鐘才能完成。這項作業完成後，您的執行個體就可以開始使用。

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 中，按一下「更多」選單的三點圖示，然後選擇「上傳檔案」。瀏覽至您在本機下載的 .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 中的「網頁預覽」圖示 ，然後選取「透過以下通訊埠預覽：8080」。

瀏覽器應會顯示分頁與 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 加註者

現在，請安裝並啟用 SQL Commenter；有了這個開放原始碼程式庫，ORM 就能在執行前利用註解來擴增 SQL 陳述式。SQLcommenter 支援多種 ORM 和架構，包括範例應用程式使用的 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. 在分頁與 Spaces 應用程式中，按一下按鈕進行更多投票，即可將更多資料新增至資料庫。

9. 透過「洞察」功能查看查詢效能和端對端追蹤記錄

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

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

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

圖表中包含 CPU 容量、CPU 和 CPU 等待、IO 等待及鎖定等待的相關資訊。您可以在說明文件進一步瞭解這些指標的意義、儲存指標的位置，並查看圖表中一些問題查詢示例。在這個範例應用程式中，資料庫的查詢負載偏低，因此圖表不會出現任何大幅的高峰。

哪些查詢的負載量最高？

圖表下方會顯示 QUERIES 資料表，當中含有所選時間範圍的正規化查詢。表格中的查詢會按照總執行時間排序。

只要按一下個別查詢，即可查看該查詢的詳細資訊，例如這項特定查詢的資料庫負載、查詢延遲時間、查詢計畫樣本，以及熱門使用者。如果使用 ORM 建構應用程式 (如同範例應用程式的情況)，您可能不知道應用程式由哪個部分負責執行哪一個查詢。「熱門標記」部分可協助您找出原因。

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

從「QUERIES」資料表切換至「TAGS」表格，即可查看依商業邏輯標記的查詢清單，以便查看以應用程式為中心的檢視畫面。

在「TAGS」表格中，您可以看到資料庫負載依產生負載的路徑細分。在上方的螢幕截圖中，您可以看到 '/getAllVotes' 路徑的平均執行時間較高，且傳回更多資料列。雖然在這個例子中可以看到執行時間並沒有造成問題，但我們還是要點選「'/getAllVotes'」的資料列，進一步查看資料。

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

按一下「查詢計畫樣本」圖表中的圓點，即可查看查詢計畫。

查詢計畫會顯示 PostgreSQL 如何在封面下執行查詢，讓您更輕鬆判斷是否有會導致速度變慢的作業。

哪個應用程式程式碼造成速度變慢？

查詢洞察也會以內容視覺化的方式呈現端對端追蹤記錄，這有助於深入分析應用程式的哪些部分產生緩慢的查詢。

點選「終點」分頁標籤，查看情境追蹤記錄。

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

10. 清理及瞭解詳情

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

清除所用資源

如果不想讓 Cloud SQL 執行個體保持運作，您現在可以將其刪除。

gcloud sql instances delete my-instance

瞭解詳情

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