مقدمه ای بر Query Insights برای Cloud SQL

۱. قبل از شروع

بینش‌های پرس‌وجو برای Cloud SQL به شما کمک می‌کند تا مشکلات عملکرد پرس‌وجو را برای پایگاه‌های داده Cloud SQL شناسایی، تشخیص و پیشگیری کنید. این ابزار، نظارت شهودی و اطلاعات تشخیصی سلف سرویس را ارائه می‌دهد که فراتر از تشخیص است و به شما در شناسایی علت اصلی مشکلات عملکرد کمک می‌کند.

در این آزمایشگاه کد، شما یاد خواهید گرفت که چگونه یک Cloud SQL را برای نمونه PostgreSQL راه‌اندازی کنید، یک برنامه Node.js را برای استفاده از نمونه Cloud SQL به عنوان فضای ذخیره‌سازی backend خود مستقر کنید و سپس از Query Insights برای مشاهده و نظارت بر کوئری‌ها استفاده کنید.

پیش‌نیازها

• آشنایی اولیه با زبان برنامه‌نویسی Node.js و ابزارهای آن

کاری که انجام خواهید داد

• استفاده از Cloud SQL در یک برنامه Node.js.
• فعال کردن SQL Commenter در یک برنامه Node.js.
• از Query Insights for Cloud SQL برای نظارت و بررسی عملکرد پرس‌وجو استفاده کنید.

آنچه نیاز دارید

• یک حساب Google Cloud که در آن مجوزهایی برای فعال کردن APIها و ایجاد سرویس‌ها دارید

۲. تنظیمات و الزامات

تنظیم محیط خودتنظیم

1. وارد Cloud Console شوید و یک پروژه جدید ایجاد کنید یا از یک پروژه موجود دوباره استفاده کنید. (اگر از قبل حساب Gmail یا Google Workspace ندارید، باید یکی ایجاد کنید .)

شناسه پروژه‌ای که استفاده می‌کنید را به خاطر بسپارید. بعداً در این آزمایشگاه کد به آن PROJECT-ID گفته خواهد شد.

2. در مرحله بعد، برای استفاده از منابع گوگل کلود، باید پرداخت را در Cloud Console فعال کنید .

اجرای این آزمایشگاه کدنویسی نباید هزینه زیادی داشته باشد، اگر اصلاً هزینه‌ای داشته باشد. حتماً دستورالعمل‌های بخش «پاکسازی و کسب اطلاعات بیشتر» را دنبال کنید، که به شما توصیه می‌کند چگونه منابع را خاموش کنید تا پس از این آموزش متحمل هزینه نشوید. کاربران جدید Google Cloud واجد شرایط برنامه آزمایشی رایگان ۳۰۰ دلاری هستند.

فعال کردن پوسته ابری

1. از کنسول ابری، روی فعال کردن پوسته ابری کلیک کنید.

اگر قبلاً Cloud Shell را شروع نکرده‌اید، یک صفحه میانی (در زیر صفحه) به شما نمایش داده می‌شود که توضیح می‌دهد چیست. در این صورت، روی ادامه کلیک کنید (و دیگر هرگز آن را نخواهید دید). آن صفحه یکبار مصرف به این شکل است:

آماده‌سازی و اتصال به Cloud Shell فقط چند لحظه طول می‌کشد.

این ماشین مجازی مجهز به تمام ابزارهای توسعه مورد نیاز شماست. این ماشین یک دایرکتوری خانگی دائمی ۵ گیگابایتی ارائه می‌دهد و در فضای ابری گوگل اجرا می‌شود که عملکرد شبکه و احراز هویت را تا حد زیادی افزایش می‌دهد.

2. برای تأیید اینکه از پروژه صحیح استفاده می‌کنید، دستور زیر را در Cloud Shell اجرا کنید:

پس از اتصال به Cloud Shell، باید ببینید که از قبل احراز هویت شده‌اید و پروژه از قبل روی شناسه پروژه شما تنظیم شده است.

توجه: ابزار خط فرمان gcloud ، ابزار خط فرمان قدرتمند و یکپارچه در Google Cloud است. این ابزار به صورت پیش‌فرض در Cloud Shell نصب شده است. متوجه پشتیبانی آن از تکمیل تب خواهید شد. برای اطلاعات بیشتر، به نمای کلی ابزار خط فرمان gcloud مراجعه کنید.

برای تأیید اینکه از پروژه صحیح استفاده می‌کنید، دستور زیر را اجرا کنید.

gcloud config list project

اگر می‌خواهید از پروژه‌ای متفاوت از پروژه‌ای که هنگام باز کردن Cloud Shell انتخاب کرده‌اید استفاده کنید، می‌توانید با اجرای دستور زیر یک پروژه جدید تنظیم کنید:

gcloud config set project <PROJECT-ID>;

۳. یک نمونه Cloud SQL برای PostgreSQL با قابلیت فعال‌سازی Query Insights راه‌اندازی کنید.

1. پس از راه‌اندازی Cloud Shell، می‌توانید از خط فرمان برای ایجاد یک نمونه جدید Cloud SQL با نام my-instance و فعال بودن Query Insights استفاده کنید:

توجه: در این آزمایشگاه کد، شما Query Insights را از طریق خط فرمان فعال می‌کنید؛ با این حال، می‌توانید آن را در کنسول از منوی Query 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 نوع ماشینی با حداقل منابع را مشخص می‌کند، زیرا این برای اهداف توسعه است و شما به منابع زیادی برای آزمایشگاه کد نیاز ندارید. می‌توانید اطلاعات بیشتری در مورد لایه‌ها را اینجا کسب کنید.
• پرچم --database-version=POSTGRES_12 ‎ نمونه‌ای ایجاد می‌کند که PostgreSQL نسخه ۱۲ خواهد بود.
• پرچم --region=us-central منطقه‌ای را که نمونه در آن ایجاد خواهد شد، مشخص می‌کند.
• پرچم --root-password=<PASSWORD> به شما امکان می‌دهد رمز عبور را برای کاربر root postgres مشخص کنید. حتماً <PASSWORD> را با رمز عبور دلخواه خود جایگزین کنید.
• پرچم --insights-config-query-insights-enabled قابلیت Query Insights را روی نمونه شما فعال می‌کند.
• فلگ --insights-config-record-application-tags ‎ امکان ثبت تگ‌های برنامه را فراهم می‌کند. در بخش‌های بعدی درباره تگ‌های برنامه بیشتر خواهید آموخت.
• پرچم --insights-config-record-client-address ‎ به Query Insights اجازه می‌دهد تا آدرس‌های IP کلاینت را ثبت کند.

ممکن است از شما خواسته شود که API sqladmin.googleapis.com را برای پروژه خود فعال کنید. در صورت درخواست، برای فعال کردن API، y را انتخاب کنید.

ایجاد نمونه چند دقیقه طول خواهد کشید. پس از اتمام این عملیات، نمونه شما آماده استفاده خواهد بود.

2. حالا یک پایگاه داده ایجاد کنید که برای برنامه نمونه از آن استفاده خواهید کرد:

gcloud sql databases create votesdb --instance my-instance

همچنین می‌توانید از طریق Cloud Console به نمونه دسترسی پیدا کرده و آن را پیکربندی کنید.

3. با اجرای دستور زیر، نام اتصال نمونه را در قالب PROJECT-ID:ZONE-ID:INSTANCE-ID دریافت کنید. بعداً در پیکربندی برنامه Node.js خود از این استفاده خواهید کرد.

gcloud sql instances describe my-instance | grep connectionName

۴. یک حساب کاربری سرویس برای استفاده با برنامه ایجاد کنید

حساب‌های سرویس برای اعطای مجوز استفاده از سرویس‌های مختلف در پروژه GCP شما استفاده می‌شوند. برای این آزمایشگاه کد، به یکی از آنها نیاز دارید تا بتوانید به Cloud SQL Proxy اجازه اتصال به نمونه Cloud SQL خود را بدهید.

یک حساب کاربری سرویس در کنسول ایجاد کنید

1. به صفحه حساب‌های سرویس IAM بروید و روی دکمه در بالای صفحه.
2. به حساب کاربری سرویس خود یک نام و شناسه منحصر به فرد بدهید و روی CREATE کلیک کنید.
3. در صفحه بعد، روی منوی کشویی «انتخاب یک نقش» کلیک کنید. «Cloud SQL» را فیلتر کنید و نقش Cloud SQL Client را انتخاب کنید. روی «ادامه» کلیک کنید و سپس روی «انجام شد» کلیک کنید.
4. پس از ایجاد حساب سرویس، روی سه نقطه در زیر اقدامات مربوط به حساب سرویس جدید خود کلیک کنید و مدیریت کلیدها را انتخاب کنید. در صفحه بعد، ADD KEY و سپس Create new key را انتخاب کنید. JSON انتخاب خواهد شد؛ آن را به صورت پیش‌فرض نگه دارید و روی CREATE کلیک کنید. با این کار یک فایل کلید خصوصی .json دانلود می‌شود. روی CLOSE کلیک کنید.
5. در Cloud Shell، روی سه نقطه برای منوی More کلیک کنید و Upload File را انتخاب کنید. فایل .json که در دستگاه محلی خود دانلود کرده‌اید را پیدا کرده و آن را انتخاب کنید. با این کار فایل .json در دایرکتوری خانگی شما در Cloud Shell آپلود می‌شود.

۵. پروکسی Cloud SQL را نصب و راه‌اندازی کنید

شما از Cloud SQL Proxy برای ارتباط بین برنامه و نمونه پایگاه داده استفاده خواهید کرد.

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

2. پس از جایگزینی <INSTANCE_CONNECTION_NAME> با نام اتصال نمونه‌ای که از صفحه نمای کلی نمونه Cloud SQL کپی کرده‌اید، پروکسی را به صورت زیر اجرا کنید.

./cloud_sql_proxy -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

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، سپس Preview را روی پورت ۸۰۸۰ انتخاب کنید.

شما باید برنامه رأی‌گیری Tabs vs Spaces را همانطور که در اینجا در مرورگر شما نشان داده شده است، ببینید:

5. برای رأی دادن و ذخیره برخی داده‌ها در پایگاه داده، روی دکمه‌ها کلیک کنید.

۷. یک صفحه برای مشاهده همه آرا اضافه کنید

از آنجا که این برنامه نمونه بسیار ساده است، شما یک صفحه اضافی اضافه خواهید کرد که تمام آرا را نمایش می‌دهد. دلیل اصلی انجام این کار این است که وقتی بعداً از Query Insights استفاده می‌کنید، داده‌های بیشتری برای بررسی داشته باشید.

1. برای متوقف کردن برنامه نمونه، Ctrl+c را در Cloud Shell خود وارد کنید.
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 را به آدرس اینترنتی (URL) در مرورگر اضافه کنید.

۸. فعال کردن SQL Commenter در برنامه

اکنون SQL Commenter را نصب و فعال خواهید کرد، یک کتابخانه متن‌باز که ORMها را قادر می‌سازد تا دستورات SQL را قبل از اجرا با کامنت‌ها تقویت کنند. SQLcommenter از چندین ORM و چارچوب پشتیبانی می‌کند، از جمله چارچوبی که برنامه نمونه از آن استفاده می‌کند: Knex.js. Query Insights از اطلاعات موجود در این کامنت‌ها برای ارائه یک دیدگاه برنامه‌محور به عملکرد پایگاه داده و شناسایی کد برنامه‌ای که باعث ایجاد مشکل می‌شود، استفاده می‌کند. انتظار می‌رود سربار عملکرد اندک باشد. به مستندات Query Insights مراجعه کنید.

1. برای متوقف کردن برنامه نمونه، Ctrl+c را در Cloud Shell خود وارد کنید.
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، روی دکمه‌ها کلیک کنید تا رأی‌های بیشتری برای افزودن داده‌های بیشتر به پایگاه داده ثبت شود.

۹. از Insights برای مشاهده عملکرد پرس‌وجو و ردیابی سرتاسری استفاده کنید

داشبورد Query Insights به شما کمک می‌کند تا کوئری‌های Cloud SQL را عیب‌یابی کنید تا مشکلات عملکرد را بررسی کنید. برای دسترسی به Insights، در منوی سمت چپ برای نمونه Cloud SQL خود، گزینه Query insights را انتخاب کنید.

بارگذاری پایگاه داده - نمودار همه پرس و جوها

داشبورد سطح بالای Query Insights نمودار بار پایگاه داده - همه پرس و جوها را نشان می‌دهد.

این نمودار حاوی اطلاعاتی برای ظرفیت پردازنده (CPU Capacity)، انتظار پردازنده و پردازنده (CPU wait)، انتظار ورودی/خروجی (IO Wait) و انتظار قفل (Lock Wait) است. می‌توانید در مستندات ، اطلاعات بیشتری در مورد معنای این معیارها، محل ذخیره معیارها و مشاهده نمونه‌هایی از نحوه نمایش این نمودار برای پرس‌وجوهای مشکل‌ساز کسب کنید. در مورد این برنامه نمونه، بار پرس‌وجوی پایگاه داده کم است، بنابراین هیچ جهش بزرگی در نمودار وجود ندارد.

کدام کوئری‌ها بیشترین بار را ایجاد می‌کنند؟

در زیر نمودار، جدول QUERIES را خواهید یافت که شامل پرس‌وجوهای نرمال‌شده برای محدوده زمانی انتخابی شما است. پرس‌وجوهای موجود در جدول بر اساس کل زمان اجرا مرتب شده‌اند.

شما می‌توانید روی یک کوئری کلیک کنید تا اطلاعات دقیقی در مورد آن، مانند بار پایگاه داده برای این کوئری خاص، تأخیر کوئری، نمونه‌های طرح کوئری و کاربران برتر، مشاهده کنید. اگر یک برنامه با استفاده از ORM ساخته شده باشد، همانطور که در مورد برنامه نمونه صدق می‌کند، ممکن است ندانید کدام بخش از برنامه مسئول کدام کوئری است. بخش «برچسب‌های برتر» می‌تواند به شما در تشخیص این موضوع کمک کند.

بار پرس و جو از کجای برنامه شروع می‌شود؟

برای دیدن فهرستی از کوئری‌های برچسب‌گذاری‌شده بر اساس منطق کسب‌وکار، از جدول QUERIES به جدول TAGS بروید و دیدگاهی برنامه‌محورتر به شما ارائه می‌دهد.

در جدول TAGS، می‌توانید بار پایگاه داده را بر اساس مسیری که بار را ایجاد کرده است، مشاهده کنید. در تصویر بالا، می‌توانید ببینید که مسیر '/getAllVotes' میانگین زمان اجرای بالاتری دارد و به طور متوسط ​​ردیف‌های بیشتری را برگردانده است. اگرچه زمان اجرایی که در جدول می‌بینیم در این مورد مشکل‌ساز نیست، اما بیایید روی ردیف '/getAllVotes' کلیک کنیم تا داده‌ها را با جزئیات بیشتری بررسی کنیم.

چرا کوئری‌ها به کندی اجرا می‌شوند؟

برای دیدن یک طرح پرس‌وجو، روی نقطه در نمودار نمونه‌های طرح پرس‌وجو کلیک کنید.

طرح‌های پرس‌وجو نشان می‌دهند که PostgreSQL چگونه یک پرس‌وجو را در خفا اجرا می‌کند و تشخیص اینکه آیا عملیاتی وجود دارد که منجر به کندی می‌شود یا خیر را آسان‌تر می‌کند.

کدام کد برنامه باعث کندی می‌شود؟

Query Insights همچنین تجسم درون متنی از ردیابی سرتاسری را ارائه می‌دهد که می‌تواند برای انجام تحقیقات بیشتر در مورد اینکه کدام بخش‌های یک برنامه، کوئری‌های کند تولید می‌کنند، مفید باشد.

برای مشاهده‌ی ردیابی درون‌متنی، روی تب END TO END کلیک کنید.

توجه: می‌توانید اطلاعات بیشتری در مورد نحوه استفاده و تفسیر طرح‌های پرس‌وجو و ردیابی سرتاسری را در مستندات کسب کنید.

۱۰. تمیزکاری کنید و بیشتر یاد بگیرید

شما یاد گرفتید که چگونه از Query Insights برای نظارت و بررسی عملکرد پرس و جو با یک برنامه Node.js و یک پایگاه داده Cloud SQL PostgreSQL استفاده کنید!

تمیز کردن

اگر نمی‌خواهید نمونه Cloud SQL شما در حال اجرا باشد، می‌توانید اکنون آن را حذف کنید.

gcloud sql instances delete my-instance

اطلاعات بیشتر

• استفاده از مستندات Query Insights
• فعال کردن برچسب‌گذاری کوئری با Sqlcommenter برای درک تأثیر برنامه بر عملکرد پایگاه داده
• مستندات SQL Commenter
• مفسر SQL در مخزن گیت‌هاب