מבוא לתובנות לגבי שאילתות ל-Cloud SQL

1. לפני שמתחילים

בעזרת Query Insights for Cloud SQL, תוכלו לזהות, לאבחן ולמנוע בעיות בביצועים של שאילתות במסדי נתונים של Cloud SQL. היא מספקת שירות עצמי, מעקב אינטואיטיבי ומידע אבחון, מעבר לזיהוי, כדי לעזור לכם לזהות את שורש הבעיות בביצועים.

בשיעור ה-Codelab הזה תלמדו איך להגדיר מכונת Cloud SQL ל-PostgreSQL, לפרוס אפליקציית Node.js כדי להשתמש במכונה של Cloud SQL כאחסון קצה העורפי שלה, ואז להשתמש ב-Query Insights כדי להציג שאילתות ולעקוב אחריהן.

דרישות מוקדמות

• היכרות בסיסית עם שפת התכנות וכלי התכנות של Node.js

מה תעשו

• שימוש ב-Cloud SQL באפליקציית Node.js.
• איך מפעילים את SQL Commenter באפליקציה של Node.js.
• להשתמש ב-Query Insights ל-Cloud SQL כדי לעקוב אחרי ביצועי השאילתות ולחקור אותם.

מה צריך להכין

• חשבון Google Cloud שבו יש לכם הרשאות להפעיל ממשקי API וליצור שירותים

2. הגדרה ודרישות

הגדרת סביבה בקצב עצמאי

1. נכנסים למסוף Cloud ויוצרים פרויקט חדש או משתמשים שוב בפרויקט קיים. (אם אין לכם עדיין חשבון Gmail או חשבון Google Workspace, עליכם ליצור חשבון).

חשוב לזכור את מזהה הפרויקט של הפרויקט. בהמשך ב-Codelab הזה, היא תיקרא PROJECT-ID.

2. בשלב הבא צריך להפעיל את החיוב במסוף Cloud כדי להשתמש במשאבים של Google Cloud.

מעבר ב-Codelab הזה לא אמור לעלות הרבה, אם בכלל. חשוב לפעול לפי כל ההוראות בקטע 'ניקוי ולמידע נוסף' שמסביר איך להשבית משאבים כדי שלא תצברו חיובים מעבר למדריך הזה. משתמשים חדשים ב-Google Cloud זכאים להשתתף בתוכנית תקופת ניסיון בחינם בשווי 1,200 ש"ח.

הפעלת Cloud Shell

1. לוחצים על Activate Cloud Shell במסוף Cloud.

אם זו הפעם הראשונה שאתם מפעילים את Cloud Shell, יוצג לכם מסך ביניים (בחלק הנגלל) שמתאר מהו. במקרה כזה, לוחצים על המשך (וזה לא יקרה שוב). כך נראה המסך החד-פעמי:

ההקצאה וההתחברות ל-Cloud Shell נמשכת כמה דקות.

למכונה הווירטואלית הזו נטען כל כלי הפיתוח הדרושים. יש בה ספריית בית בנפח מתמיד של 5GB והיא פועלת ב-Google Cloud, מה שמשפר משמעותית את ביצועי הרשת והאימות.

2. מריצים את הפקודה הבאה ב-Cloud Shell כדי לוודא שמשתמשים בפרויקט הנכון:

אחרי ההתחברות ל-Cloud Shell, אתם אמורים לראות שכבר בוצע אימות ושהפרויקט כבר מוגדר למזהה הפרויקט שלכם.

הערה: כלי שורת הפקודה gcloud הוא כלי שורת הפקודה המתקדם והמאוחד ב-Google Cloud. הוא מותקן מראש ב-Cloud Shell. תוכלו לראות שהוא תומך בהשלמת כרטיסיות. למידע נוסף, קראו את המאמר סקירה כללית של כלי שורת הפקודה של Google Cloud.

מריצים את הפקודה הבאה כדי לוודא שאתם משתמשים בפרויקט הנכון.

gcloud config list project

אם רוצים להשתמש בפרויקט שונה מזה שבחרתם כשפתחתם את Cloud Shell, תוכלו להגדיר פרויקט חדש באמצעות הרצת הפקודה:

gcloud config set project <PROJECT-ID>;

3. איך מגדירים מכונת Cloud SQL ל-PostgreSQL שמופעלת בה תובנות לגבי שאילתות

1. אחרי ההשקה של Cloud Shell, תוכלו להשתמש בשורת הפקודה כדי ליצור מכונה חדשה של Cloud SQL בשם my-instance, ולהפעיל בה תובנות לגבי שאילתות:

הערה: ב-Codelab הזה, מפעילים את התובנות לגבי שאילתות דרך שורת הפקודה; עם זאת, אפשר להפעיל אותו גם במסוף דרך תפריט 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 מציין סוג מכונה עם משאבים מינימליים, מכיוון שהוא מיועד למטרות פיתוח ואין צורך במשאבים רבים עבור ה-Codelab. כאן תוכלו למצוא מידע נוסף על הרמות.
• הדגל --database-version=POSTGRES_12 יוצר מכונה של PostgreSQL בגרסה 12.
• הדגל --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 מאפשר לתעד כתובות IP של לקוחות על ידי Query Insights.

יכול להיות שתתבקשו להפעיל את ה-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. ב-Codelab הזה צריך להשתמש כדי להעניק ל-Cloud SQL Proxy הרשאה להתחבר למכונה של Cloud SQL.

יצירת חשבון שירות במסוף

1. נכנסים לדף של חשבונות השירות של IAM ולוחצים על הלחצן בחלק העליון של הדף.
2. נותנים לחשבון השירות שם ומזהה ייחודיים ולוחצים על יצירה.
3. בדף הבא, לוחצים על התפריט הנפתח 'בחירת תפקיד'. סינון לפי 'Cloud SQL' ובוחרים את התפקיד 'לקוח Cloud SQL'. לוחצים על המשך ואז על סיום.
4. אחרי שיוצרים את חשבון השירות, לוחצים על תפריט שלוש הנקודות בקטע פעולות בשביל חשבון השירות החדש ובוחרים באפשרות 'ניהול מפתחות'. בדף הבא בוחרים באפשרות הוספת מפתח ואז באפשרות יצירת מפתח חדש. ייבחר JSON; לשמור את ברירת המחדל וללחוץ על יצירה. תתבצע הורדה של קובץ מפתח פרטי מסוג .json לוחצים על סגירה.
5. ב-Cloud Shell, לוחצים על תפריט שלוש הנקודות בתפריט More ובוחרים באפשרות Upload File. עליכם לעיין בקובץ ה- .json שהורדתם למחשב המקומי ולבחור אותו. הפעולה הזו תעלה את קובץ ה- .json לספריית הבית שלכם ב-Cloud Shell.

5. התקנה והפעלה של Cloud SQL Proxy

שרת ה-proxy של Cloud SQL משמש לתקשורת בין האפליקציה לבין המכונה של מסד הנתונים.

1. מורידים את שרת ה-proxy של 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, מריצים את שרת ה-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. לוחצים על Web Preview ב-Cloud Shell, ובוחרים באפשרות Preview on Port 8080.

אפליקציית ההצבעה 'כרטיסיות לעומת מרחבים' אמורה להופיע כאן בדפדפן:

5. לחץ על הלחצנים כדי לבצע הצבעות ולשמור נתונים במסד הנתונים.

7. הוספת דף כדי להציג את כל ההצבעות

מכיוון שהיישום לדוגמה הזה פשוט מאוד, תוסיפו עוד דף שמציג את כל ההצבעות. הסיבה העיקרית לכך היא כדי שיהיו לכם יותר נתונים לבדוק אם תשתמשו ב'תובנות לגבי שאילתות' מאוחר יותר.

1. כדי להפסיק את האפליקציה לדוגמה, מזינים את הערך Ctrl+c ב-Cloud Shell.
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');
};

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. צריך לפתוח את האפליקציה דרך Web Preview כדי לוודא שהיא פועלת. כדי לראות את הדף החדש שהוספת, צריך להוסיף את /getAllVotes לכתובת ה-URL בדפדפן.

8. הפעלת SQL Commenter באפליקציה

עכשיו תתקינו ותפעילו את SQL Commenter – ספריית קוד פתוח שמאפשרת ל-ORM להרחיב הצהרות SQL באמצעות הערות לפני הפעלה. SQLcommenter תומך בכמה מסגרות ORM ו-frameworks, כולל זו שמשמשת את האפליקציה לדוגמה: Knex.js. הכלי 'תובנות לגבי שאילתות' מתבסס על המידע שבהערות האלה כדי לספק תצוגה ממוקדת-אפליקציה לגבי ביצועי מסד הנתונים ולזהות איזה קוד של אפליקציה גורם לבעיות. התקורה של הביצועים צפויה להיות קטנה. מידע נוסף זמין במסמכי התיעוד של 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 Editor.
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. באפליקציה 'כרטיסיות' לעומת 'מרחבים', לוחצים על הלחצנים כדי לבצע הצבעות נוספות ולהוסיף נתונים למסד הנתונים.

9. אפשר להשתמש בתובנות כדי לראות את ביצועי השאילתות ולבצע מעקב מקצה לקצה

בעזרת מרכז הבקרה של Query Insights תוכלו לפתור בעיות בשאילתות של Cloud SQL כדי לאתר בעיות בביצועים. כדי לגשת לתובנות, בוחרים באפשרות Query Insights בתפריט הניווט הימני של המכונה של Cloud SQL.

טעינה של מסד נתונים – תרשים כל השאילתות

במרכז הבקרה של התובנות לגבי שאילתות ברמה העליונה מוצג התרשים עומס מסד נתונים – כל השאילתות.

התרשים מכיל מידע על קיבולת המעבד (CPU), זמן ההמתנה בין המעבד (CPU) למעבד (CPU), ההמתנה ל-IO (בהמתנה) וההמתנה לנעילה. במסמכי התיעוד תוכלו לקבל מידע נוסף על המשמעות של המדדים האלה, על המקומות שבהם המדדים מאוחסנים, וגם לראות כמה דוגמאות לתרשים הזה ביחס לשאילתות בעייתיות. במקרה של האפליקציה לדוגמה הזו, העומס של שאילתת מסד הנתונים נמוך, לכן אין עליות חדות גדולות בתרשים.

אילו שאילתות אחראיות לנפח הטעינה הגבוה ביותר?

מתחת לתרשים מופיעה הטבלה 'שאילתות' שמכילה את השאילתות המנורמלות לפי טווח הזמן שבחרתם. השאילתות בטבלה ממוינות לפי זמן הביצוע הכולל.

אפשר ללחוץ על שאילתה מסוימת כדי להציג מידע מפורט לגבי השאילתה, כמו טעינת מסד הנתונים של השאילתה הספציפית הזאת, זמן האחזור של השאילתה, דגימות של תוכניות שאילתות ומשתמשים מובילים. אם אפליקציה נוצרת באמצעות ORM, כמו במקרה של האפליקציה לדוגמה, ייתכן שלא תדעו איזה חלק באפליקציה אחראי לכל שאילתה. תוכלו להיעזר בקטע 'תגים מובילים'.

מאיפה מגיעה טעינת השאילתה באפליקציה?

עוברים מטבלת שאילתות לטבלה TAGS כדי לראות רשימה של שאילתות שתויגו לפי לוגיקה עסקית, ולקבל תצוגה ממוקדת-אפליקציות.

בטבלה TAGS ניתן לראות את הפירוט של העומס במסד הנתונים לפי הנתיב שבו נוצר הטעינה. בצילום המסך שלמעלה אפשר לראות שזמן הביצוע הממוצע של המסלול '/getAllVotes' גבוה יותר, ומוצגים בו יותר שורות בממוצע. במקרה הזה, זמן הביצוע שמוצג בטבלה לא בעייתי, אבל בכל זאת נלחץ על השורה של '/getAllVotes' כדי לבחון את הנתונים לעומק.

למה השאילתות פועלות לאט?

לוחצים על הנקודה בתרשים דוגמאות של תוכניות שאילתה כדי לראות תוכנית שאילתות.

תוכניות השאילתות מראות איך PostgreSQL מבצע שאילתה בכיסוי, וכך קל יותר לקבוע אם יש פעולות שמובילות לאיטיות.

איזה קוד של אפליקציה גורם לאיטיות?

הכלי 'תובנות לגבי שאילתות' מספק גם תצוגה חזותית בהקשר של מעקב מקצה לקצה, כדי לעזור לכם להמשיך לבדוק אילו חלקים באפליקציה יוצרים שאילתות איטיות.

לוחצים על הכרטיסייה סיום השיחה כדי להציג מעקב אחר הקשר.

הערה: במסמכי התיעוד תוכלו לקרוא מידע נוסף על השימוש בתוכניות השאילתות ובמעקב מקצה לקצה.

10. פינוי נפח אחסון ומידע נוסף

למדתם איך להשתמש ב- Query Insights כדי לעקוב אחר ביצועי שאילתות ולחקור אותם באמצעות אפליקציית Node.js ומסד נתונים של Cloud SQL PostgreSQL!

פינוי מקום

אם לא רוצים שהמכונה של Cloud SQL תמשיך לפעול, אפשר למחוק אותה עכשיו.

gcloud sql instances delete my-instance

מידע נוסף

• שימוש בתיעוד של התובנות לגבי שאילתות
• מפעילים תיוג שאילתות באמצעות Sqlcommenter כדי להבין את ההשפעה של אפליקציה על ביצועים של מסד נתונים
• משאבי עזרה ל-SQL Commenter
• מאגר github ל-SQL Commenter