Cloud SQL 数据分析简介

Cloud SQL Insights 可帮助您检测、诊断和阻止 Cloud SQL 数据库的查询性能问题。它提供自助式、直观的监控和诊断信息，不仅仅是检测，可帮助您找出导致性能问题的根本原因。

在此 Codelab 中，您将学习如何设置 Cloud SQL for PostgreSQL 实例，部署 Node.js 应用以将 Cloud SQL 实例用作后端存储，然后使用 Cloud SQL Insights 查看和监控查询。

前提条件

  • 基本了解 Node.js 编程语言和工具

您将执行的操作

  • 在 Node.js 应用中使用 Cloud SQL。
  • 在 Node.js 应用中启用 SQL Commenter。
  • 使用 Cloud SQL Insights 监控和调查查询性能。

所需条件

  • 您有权启用 API 和创建服务的 Google Cloud 帐号

自定进度的环境设置

  1. 登录 Cloud Console，然后创建一个新项目或重复使用现有项目。（如果您还没有 Gmail 或 Google 工作区帐号，则必须先创建一个。）

请记住您正在使用的项目的 ID。此 Codelab 稍后将在 PROJECT-ID 中引用它。

  1. 接下来，您需要在 Cloud Console 中启用结算功能才能使用 Google Cloud 资源。

运行此 Codelab 不会产生任何费用（如果有）。请务必按照“清理并了解详情”部分中的说明操作，其中介绍了如何关停资源，这样您就不会在本教程之外产生费用。Google Cloud 的新用户均有资格申请 $300 美元免费试用计划。

激活 Cloud Shell

  1. 在 Cloud Console 中，点击激活 Cloud Shell。

激活 Cloud Shell

如果您以前从未启动过 Cloud Shell，将看到一个中间屏幕（在折叠下面），描述它是什么。如果是这种情况，请点击继续（您将永远不会再看到它）。一次性屏幕如下所示：

Cloud Shell 对话框窗口

预配和连接到 Cloud Shell 只需花几分钟时间。

Cloud Shell 终端

这个虚拟机已加载了您需要的所有开发工具。它提供了一个持久的 5GB 主目录，并且在 Google Cloud 中运行，大大增强了网络性能和身份验证。

  1. 在 Cloud Shell 中运行以下命令，确认您使用的是正确的项目：

在连接到 Cloud Shell 后，您应该会看到自己已通过身份验证，并且相关项目已设置为您的项目 ID：

运行以下命令以确认您使用的项目正确无误。

gcloud config list project

如果您要使用的项目与打开 Cloud Shell 时选择的项目不同，则可以通过运行以下命令来设置新项目：

gcloud config set project <PROJECT-ID>;
  1. Cloud Shell 启动后，您可以使用命令行创建一个名为 my-instance 的新 Cloud SQL 实例，并启用 Cloud SQL Insights：
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 标志指定了具有最少资源的机器类型，因为它用于开发，您无需为 Codelab 投入大量资源。如需详细了解层级，请点击此处。
  • --database-version=POSTGRES_12 标志将创建将使用 PostgreSQL 版本 12 的实例。
  • --region=us-central 标志指定要在其中创建实例的区域。
  • --root-password=<PASSWORD> 标志可用于指定根 postgres 用户的密码。请务必将 <PASSWORD> 替换为您选择的密码。
  • --insights-config-query-insights-enabled 标志会为您的实例启用 Cloud SQL Insights。
  • --insights-config-record-application-tags 标志允许记录应用标记。您可以在后面的部分中了解有关应用标记的详情。
  • --insights-config-record-client-address 标志允许由 Cloud SQL Insights 记录客户端 IP 地址。

系统可能会提示您为项目启用 API sqladmin.googleapis.com。如果出现提示，请选择 y 以启用 API。

创建实例需要几分钟时间。此操作完成后，您的实例即可使用。

  1. 现在，创建用于示例应用的数据库：
gcloud sql databases create votesdb --instance my-instance

您还可以通过 Cloud Console 访问和配置实例。

  1. 运行以下命令，以 PROJECT-ID:ZONE-ID:INSTANCE-ID 格式获取实例连接名称。您稍后将使用这些信息来配置 Node.js 应用。
gcloud sql instances describe my-instance | grep connectionName

服务帐号用于授予在 GCP 项目中使用不同服务的权限。对于此 Codelab，您需要 Cloud SQL 代理连接 Cloud SQL 实例所需的权限。

在控制台中创建服务帐号

  1. 转到 IAM 服务帐号页面，然后点击页面顶部的 -PCvKR3aQ2zKaUcml8w9lW4JNlmYtN5-r2--mC6kMUp6HOXW8wT1wUvLoYEPU-aA-oGskT3XkAqfNwRAKkZkllwTe6ugdrUVFwaeKT0M9Y1RwHA8JPZeGmCWYBfr8d9TSycNMIRsLw 按钮。
  2. 为您的服务帐号指定一个唯一名称和 ID，然后点击创建。
  3. 在下一页中，点击“选择角色”下拉列表。过滤“Cloud SQL”，然后选择 Cloud SQL 客户角色。点击继续，然后点击完成。
  4. 创建服务帐号后，点击新服务帐号的操作下的三个点，然后选择“创建密钥”。选择 JSON；保留该默认设置，然后点击创建。系统会下载一个 .json 私钥文件。点击关闭。
  5. 在 Cloud Shell 中，点击“更多”菜单上的三个点，然后选择上传文件。浏览到您下载到本地机器上的 .json 文件并选择该文件。此操作会将 .json 文件上传到 Cloud Shell 中的主目录。

您将使用 Cloud SQL 代理在应用与数据库实例之间通信。

  1. 下载 Cloud SQL 代理。在 Cloud Shell 中，您可以运行以下命令：
wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy && chmod +x cloud_sql_proxy
  1. 如需运行代理，请使用您从 Cloud SQL 实例详情复制的实例连接名称来替换 <INSTANCE_CONNECTION_NAME>。对于凭据文件，请输入您在上一步中上传的文件的路径。
./cloud_sql_proxy -credential_file=/path/to/credential_file.json -instances=<INSTANCE_CONNECTION_NAME>=tcp:5432 &

如果成功，您应该看到几行输出，以 Ready for new connections 消息结尾。
  1. 克隆示例应用的代码库，并安装运行应用所需的软件包。
git clone https://github.com/GoogleCloudPlatform/nodejs-docs-samples/

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

npm install
  1. 设置以下环境变量： 
export CLOUD_SQL_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'
  1. 运行 createTable.js 以创建应用所需的数据库表，并确保数据库已正确配置，然后启动示例应用。
node createTable.js $DB_USER $DB_PASS $DB_NAME $CLOUD_SQL_CONNECTION_NAME votes $DB_HOST

npm start
  1. 点击 Cloud Shell 中的网页预览网络预览图标，然后选择在端口 8080 上预览。

在端口 8080 菜单项上预览

您应该在浏览器中看到“标签页与空间”投票应用，如下所示：

标签页与空间投票应用屏幕截图

  1. 点击这些按钮进行投票，并将一些数据保存到数据库。

由于此示例应用非常简单，因此您将额外添加一个显示所有投票的网页。这样做的主要目的是让您后续使用 Cloud SQL Insights 时有更多数据可供选择。

  1. 在 Cloud Shell 中输入 Ctrl+c 以停止示例应用。
  2. 在 Cloud Shell 中，点击 打开“编辑器”按钮 按钮启动 Cloud Shell Editor。
  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');
};
  1. 为下方的其他路由定义 '/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();
  }
});
  1. 在 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}.
  1. 点击 打开“终端”按钮 按钮返回 Cloud Shell 并运行：
npm start
  1. 从网页预览中打开应用，确保应用能正常运行。在浏览器中为网址添加 /getAllVotes 可查看您添加的新页面。

现在，您将安装并启用 SQL Commenter，这是一个开源库，它使 ORM 能够在执行之前扩充包含注释的 SQL 语句。SQL Commenter 支持多种 ORM 和框架，包括示例应用使用的 ORM：Knex.js。Cloud SQL Insights 会根据这些注释中的信息，提供以应用为中心的数据库性能详细信息，并确定哪个应用代码会引发问题。性能开销预计应该很小，您可以在文档中了解有关性能的更多信息。

  1. 在 Cloud Shell 中输入 Ctrl+c 以停止示例应用。
  2. 运行以下命令以安装 SQL commenter 所需的软件包：
  npm install @google-cloud/sqlcommenter-knex @opencensus/nodejs @opencensus/propagation-tracecontext @opentelemetry/api @opentelemetry/core --save
  1. 在 Cloud Shell 中，点击 打开“编辑器”按钮 按钮启动 Cloud Shell Editor。
  2. 在文件资源管理器中，找到 nodejs-docs-samples/cloud-sql/postgres/knex/server.js 并点击该文件，以在编辑器中加载 server.js 文件。
  3. 在文件中找到以下代码：
const process = require('process');

在其下方添加以下代码：

const {wrapMainKnexAsMiddleware} = require('@google-cloud/sqlcommenter-knex');
  1. 在文件中找到以下代码：
// 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 bodyParser = require('body-parser');
const Knex = require('knex');

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

// Automatically parse request body as form data.
app.use(bodyParser.urlencoded({extended: false}));
app.use(bodyParser.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
}));
...
  1. 点击 打开“终端”按钮 按钮返回 Cloud Shell 并运行：
npm start
  1. 在“标签页与空间”应用中，点击相应按钮将更多投票项添加到数据库中。

查询数据分析信息中心可帮助您排查 Cloud SQL 查询问题，以查找性能问题。

数据库负载 - 所有查询图

顶级 Query Insights 信息中心显示“数据库负载 - 所有热门查询”图表。

所有查询图表

该图包含 CPU 容量、CPU 和 CPU 等待情况、IO 等待和锁定等待的信息。如需详细了解这些指标的含义，了解指标的存储位置，并参阅文档中有关问题查询的图表示例。在此示例应用中，数据库查询负载较低，因此图表上没有任何大峰值。

哪些查询带来的负载最高？

在图表下方，您可以看到包含选定时间段内的标准化查询的 QUERIES 表。表中的查询按总执行时间排序。

热门查询表

您可以点击单个查询来查看该查询的详细信息，例如这一特定查询的数据库负载、查询延迟时间、查询计划示例和热门用户。如果应用是使用 ORM 构建的，就像示例应用的情况一样，您可能无法知道该应用的哪个部分负责查询。“热门标记”部分可以帮助您判断这一点。

应用的加载查询在哪里？

从 QUERIES 表切换到 TAGS 表，可查看业务逻辑标记的查询列表，从而获得更有针对性的应用视图。

“热门代码”表格

在 TAGS 表中，您可以看到按负载生成的路由的数据库负载。在上面的屏幕截图中，您可以看到 '/getAllVotes' 路由的平均执行时间较长，平均返回更多行。虽然我们在表中看到的执行时间没有问题，但我们仍然点击 '/getAllVotes' 的行以查看更详细的数据。

为什么查询运行缓慢？

点击“查询计划示例”图表中的点可查看查询计划。

查询计划示例

查询计划显示 PostgreSQL 如何在封面内执行查询，从而更轻松地确定是否存在会导致操作速度变慢的操作。

哪种应用代码会导致速度变慢？

Cloud SQL Insights 还提供对端到端跟踪的上下文可视化，有助于进一步调查应用的哪些部分产生慢的查询。

点击端到端标签页以查看上下文跟踪。

端到端跟踪记录

您已经了解了如何使用 Cloud SQL Insights 来监控和调查 Node.js 应用和 Cloud SQL PostgreSQL 数据库的查询性能！

清理

如果您不想让 Cloud SQL 实例保持运行，现在可以将其删除。

gcloud sql instances delete my-instance

了解详情