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

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

Query Insights ב-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 Console כדי להשתמש במשאבים של Google Cloud.

השלמת ה-codelab הזה לא אמורה לעלות לכם הרבה, אם בכלל. הקפידו לפעול לפי ההוראות שבקטע 'ניקוי ומידע נוסף', שבו מוסבר איך להשבית משאבים כדי שלא תחויבו מעבר למה שמוסבר במדריך הזה. משתמשים חדשים ב-Google Cloud זכאים לתוכנית ניסיון חינם בסך 300$.

הפעלת Cloud Shell

1. ב-Cloud Console, לוחצים על Activate Cloud Shell (הפעלת Cloud Shell).

אם זו הפעם הראשונה שאתם מפעילים את Cloud Shell, יוצג לכם מסך ביניים (מתחת לקו התוכן) עם תיאור של 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 עם Query Insights מופעל

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

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

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. נותנים לחשבון השירות שם ומזהה ייחודיים ולוחצים על יצירה.
3. בדף הבא, לוחצים על התפריט הנפתח Select a role (בחירת תפקיד). מסננים לפי Cloud SQL ובוחרים את התפקיד Cloud SQL Client (לקוח Cloud SQL). לוחצים על CONTINUE (המשך) ואז על DONE (סיום).
4. אחרי שיוצרים את חשבון השירות, לוחצים על סמל שלושת הנקודות בקטע Actions של חשבון השירות החדש ובוחרים באפשרות Manage keys (ניהול מפתחות). בדף הבא, בוחרים באפשרות ADD KEY (הוספת מפתח) ואז באפשרות Create new key (יצירת מפתח חדש). האפשרות JSON תהיה מסומנת. משאירים את ברירת המחדל הזו ולוחצים על CREATE. קובץ מפתח פרטי בפורמט JSON יורד למחשב. לוחצים על סגירה.
5. ב-Cloud Shell, לוחצים על סמל שלוש הנקודות של התפריט More (עוד) ובוחרים באפשרות Upload File (העלאת קובץ). עוברים לקובץ ה-‎ .json שהורדתם במחשב המקומי ובוחרים אותו. קובץ ה-‎ .json יועלה לספריית הבית ב-Cloud Shell.

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

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

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. מריצים את ה-proxy באופן הבא אחרי שמחליפים את <INSTANCE_CONNECTION_NAME> בשם החיבור של המכונה שהעתקתם מדף הסקירה הכללית של מכונת Cloud SQL.

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

אפליקציית ההצבעה Tabs vs Spaces אמורה להופיע בדפדפן כמו בתמונה הבאה:

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

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

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

1. מזינים Ctrl+c ב-Cloud Shell כדי לעצור את אפליקציית הדוגמה.
2. ב-Cloud Shell, לוחצים על הלחצן כדי להפעיל את Cloud Shell Editor.
3. בסייר הקבצים, מוצאים את nodejs-docs-samples/cloud-sql/postgres/knex/index.js ולוחצים עליו כדי לטעון את הקובץ index.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 בדפדפן כדי לראות את הדף החדש שהוספתם.

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

עכשיו נתקין ונפעיל את SQL Commenter, ספרייה בקוד פתוח שמאפשרת ל-ORM להוסיף הערות להצהרות SQL לפני ההפעלה. ‫SQLcommenter תומך בכמה ORM ומסגרות, כולל 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. באפליקציה Tabs vs Spaces, לוחצים על הלחצנים כדי להוסיף עוד הצבעות וכך להוסיף עוד נתונים למסד הנתונים.

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

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

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

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

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

אילו שאילתות אחראיות לעומס הכי גדול?

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

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

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

אפשר לעבור בין הטבלה QUERIES (שאילתות) לטבלה TAGS (תגים) כדי לראות רשימה של שאילתות שתויגו לפי הלוגיקה העסקית, וכך לקבל תצוגה ממוקדת יותר באפליקציה.

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

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

לוחצים על הנקודה בתרשים Query plan samples כדי לראות תוכנית שאילתה.

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

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

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

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

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

10. פינוי מקום ומידע נוסף

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

פינוי מקום

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

gcloud sql instances delete my-instance

מידע נוסף

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