Wprowadzenie do statystyk zapytań dla Cloud SQL

1. Zanim zaczniesz

Statystyki zapytań w Cloud SQL pomagają wykrywać, diagnozować i zapobiegać problemom z wydajnością zapytań w bazach danych Cloud SQL. Umożliwia samodzielne, intuicyjne monitorowanie i diagnostykę, która wykracza poza wykrywanie problemów i pomaga zidentyfikować ich główną przyczynę.

Z tego przewodnika dowiesz się, jak skonfigurować instancję Cloud SQL for PostgreSQL, wdrożyć aplikację Node.js, aby używać instancji Cloud SQL jako pamięci backendu, a następnie użyć Statystyk zapytań do wyświetlania i monitorowania zapytań.

Wymagania wstępne

• Podstawowa znajomość języka programowania i narzędzi Node.js

Co musisz zrobić

• Używanie Cloud SQL w aplikacji Node.js
• Włącz SQL Commenter w aplikacji Node.js.
• Używaj statystyk zapytań w Cloud SQL do monitorowania i analizowania wydajności zapytań.

Czego potrzebujesz

• konto Google Cloud, na którym masz uprawnienia do włączania interfejsów API i tworzenia usług;

2. Konfiguracja i wymagania

Samodzielne konfigurowanie środowiska

1. Zaloguj się w konsoli Google Cloud i utwórz nowy projekt lub użyj istniejącego. (Jeśli nie masz jeszcze konta Gmail lub Google Workspace, musisz je utworzyć).

Zapamiętaj identyfikator projektu, którego używasz. W dalszej części tego laboratorium będzie on nazywany PROJECT-ID.

2. Następnie musisz włączyć rozliczenia w konsoli Cloud, aby korzystać z zasobów Google Cloud.

Ukończenie tego laboratorium nie powinno wiązać się z dużymi kosztami, a nawet z żadnymi. Wykonaj instrukcje z sekcji „Czyszczenie i więcej informacji”, w której znajdziesz wskazówki, jak wyłączyć zasoby, aby uniknąć naliczenia opłat po zakończeniu tego samouczka. Nowi użytkownicy Google Cloud mogą skorzystać z programu bezpłatnego okresu próbnego, w którym mają do dyspozycji środki w wysokości 300 USD.

Aktywowanie Cloud Shell

1. W konsoli Cloud kliknij Aktywuj Cloud Shell.

Jeśli uruchamiasz Cloud Shell po raz pierwszy, zobaczysz ekran pośredni (część strony widoczna po przewinięciu) z opisem tego środowiska. W takim przypadku kliknij Dalej, a ten ekran nie będzie się już wyświetlać. Ten wyświetlany jednorazowo ekran wygląda tak:

Uzyskanie dostępu do środowiska Cloud Shell i połączenie się z nim powinno zająć tylko kilka chwil.

Ta maszyna wirtualna zawiera wszystkie potrzebne narzędzia dla programistów. Zawiera również stały katalog domowy o pojemności 5 GB i działa w Google Cloud, co znacznie zwiększa wydajność sieci i usprawnia proces uwierzytelniania.

2. Aby sprawdzić, czy używasz właściwego projektu, uruchom w Cloud Shell to polecenie:

Po połączeniu z Cloud Shell zobaczysz, że uwierzytelnianie zostało już przeprowadzone, a projekt jest już ustawiony na Twój identyfikator projektu.

Uwaga: narzędzie wiersza poleceń gcloud to zaawansowane i ujednolicone narzędzie wiersza poleceń w Google Cloud. Jest już zainstalowane w Cloud Shell. Zauważysz, że obsługuje on uzupełnianie za pomocą klawisza Tab. Więcej informacji znajdziesz w omówieniu narzędzia wiersza poleceń gcloud.

Aby sprawdzić, czy używasz właściwego projektu, uruchom to polecenie.

gcloud config list project

Jeśli chcesz użyć innego projektu niż ten, który był wybrany podczas otwierania Cloud Shell, możesz ustawić nowy, uruchamiając to polecenie:

gcloud config set project <PROJECT-ID>;

3. Konfigurowanie instancji Cloud SQL for PostgreSQL z włączoną funkcją Statystyki zapytań

1. Po uruchomieniu Cloud Shell możesz użyć wiersza poleceń, aby utworzyć nową instancję Cloud SQL o nazwie my-instance z włączoną funkcją Statystyki zapytań:

Uwaga: w tym samouczku włączasz Statystyki zapytań za pomocą wiersza poleceń, ale możesz to też zrobić w konsoli w menu Statystyki zapytań instancji.

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

Oto krótkie wyjaśnienie, co oznaczają poszczególne flagi:

• Flaga --tier db-f1-micro określa typ maszyny z minimalnymi zasobami, ponieważ jest to środowisko deweloperskie i nie potrzebujesz wielu zasobów do wykonania tego ćwiczenia. Więcej informacji o poziomach znajdziesz tutaj.
• Flaga --database-version=POSTGRES_12 tworzy instancję z bazą danych PostgreSQL w wersji 12.
• Flaga --region=us-central określa region, w którym zostanie utworzona instancja.
• Flaga --root-password=<PASSWORD> umożliwia określenie hasła użytkownika root postgres. Pamiętaj, aby zastąpić ciąg <PASSWORD> wybranym hasłem.
• Flaga --insights-config-query-insights-enabled włącza w instancji Statystyki zapytań.
• Flaga --insights-config-record-application-tags umożliwia rejestrowanie tagów aplikacji. Więcej informacji o tagach aplikacji znajdziesz w dalszych sekcjach.
• Flaga --insights-config-record-client-address umożliwia rejestrowanie adresów IP klientów przez statystyki zapytań.

Może pojawić się prośba o włączenie w projekcie interfejsu API sqladmin.googleapis.com. Jeśli pojawi się prośba, kliknij y, aby włączyć interfejs API.

Utworzenie instancji zajmie kilka minut. Po zakończeniu tej operacji instancja będzie gotowa do użycia.

2. Teraz utwórz bazę danych, której będziesz używać w przykładowej aplikacji:

gcloud sql databases create votesdb --instance my-instance

Dostęp do instancji możesz też uzyskać i skonfigurować ją w konsoli Google Cloud.

3. Aby uzyskać nazwę połączenia instancji w formacie PROJECT-ID:ZONE-ID:INSTANCE-ID, uruchom to polecenie. Użyjesz go później podczas konfigurowania aplikacji Node.js.

gcloud sql instances describe my-instance | grep connectionName

4. Tworzenie konta usługi do użycia z aplikacją

Konta usługi służą do przyznawania uprawnień do korzystania z różnych usług w projekcie GCP. W tym samouczku potrzebujesz jednego, aby przyznać serwerowi proxy Cloud SQL uprawnienia do łączenia się z instancją Cloud SQL.

Tworzenie konta usługi w konsoli

1. Otwórz stronę kont usługi w sekcji Uprawnienia i kliknij przycisk  u góry strony.
2. Nadaj kontu usługi unikalną nazwę i identyfikator, a następnie kliknij UTWÓRZ.
3. Na następnej stronie kliknij menu Wybierz rolę. Filtruj według „Cloud SQL” i wybierz rolę Klient Cloud SQL. Kliknij KONTYNUUJ, a potem GOTOWE.
4. Po utworzeniu konta usługi kliknij 3 kropki w sekcji Działania obok nowego konta usługi i wybierz Zarządzaj kluczami. Na następnej stronie kliknij DODAJ KLUCZ, a potem Utwórz nowy klucz. Opcja JSON będzie wybrana domyślnie. Zachowaj to ustawienie i kliknij UTWÓRZ. Spowoduje to pobranie pliku klucza prywatnego w formacie JSON. Kliknij ZAMKNIJ.
5. W Cloud Shell kliknij 3 kropki w menu Więcej i wybierz Prześlij plik. Przejdź do pobranego na komputer lokalny pliku JSON i wybierz go. Spowoduje to przesłanie pliku JSON do katalogu głównego w Cloud Shell.

5. Instalowanie i uruchamianie Cloud SQL Proxy

Do komunikacji między aplikacją a instancją bazy danych będziesz używać serwera proxy Cloud SQL.

1. Pobierz serwer proxy Cloud SQL. W Cloud Shell możesz uruchomić:

wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy && chmod +x cloud_sql_proxy

2. Uruchom serwer proxy w ten sposób, zastępując <INSTANCE_CONNECTION_NAME> nazwą połączenia z instancją skopiowaną ze strony Przegląd instancji Cloud SQL.

./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:5432 &

Jeśli się to uda, zobaczysz kilka wierszy danych wyjściowych, a na końcu komunikat Ready for new connections.

6. Klonowanie i testowanie aplikacji lokalnie

1. Sklonuj repozytorium przykładowej aplikacji i zainstaluj pakiety niezbędne do jej uruchomienia.

git clone https://github.com/GoogleCloudPlatform/nodejs-docs-samples/

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

npm install

2. Ustaw następujące zmienne środowiskowe:

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. Uruchom przykładową aplikację.

npm start

4. W Cloud Shell kliknij Podgląd w przeglądarce, a następnie wybierz Podejrzyj na porcie 8080.

W przeglądarce powinna pojawić się aplikacja do głosowania Tabs vs Spaces (Karty czy spacje) widoczna na tym obrazie:

5. Kliknij przyciski, aby oddać kilka głosów i zapisać dane w bazie danych.

7. Dodawanie strony, na której można wyświetlić wszystkie głosy

Ta przykładowa aplikacja jest bardzo prosta, więc dodasz do niej dodatkową stronę, na której będą wyświetlane wszystkie głosy. Głównym powodem jest to, że później, gdy będziesz korzystać ze statystyk zapytań, będziesz mieć do dyspozycji więcej danych.

1. Aby zatrzymać przykładową aplikację, wpisz w Cloud Shell Ctrl+c.
2. W Cloud Shell kliknij przycisk , aby uruchomić edytor Cloud Shell.
3. W eksploratorze plików znajdź nodejs-docs-samples/cloud-sql/postgres/knex/server.js i kliknij go, aby wczytać plik server.js w edytorze.

Dodaj ten kod po miejscu, w którym zdefiniowana jest funkcja 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. Dodaj ten kod do trasy '/getAllVotes' poniżej miejsca, w którym zdefiniowane są inne trasy:

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. Utwórz w katalogu nodejs-docs-samples/cloud-sql/postgres/knex/views nowy plik o nazwie allvotes.pug. Wklej ten kod:

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. Kliknij przycisk , aby wrócić do Cloud Shell i uruchomić:

npm start

7. Otwórz aplikację z podglądu w przeglądarce, aby sprawdzić, czy działa. Dodaj /getAllVotes do adresu URL w przeglądarce, aby wyświetlić nową stronę.

8. Włączanie SQL Commenter w aplikacji

Teraz zainstalujesz i włączysz SQL Commenter, bibliotekę open source, która umożliwia ORM dodawanie komentarzy do instrukcji SQL przed ich wykonaniem. SQLcommenter obsługuje kilka ORM-ów i frameworków, w tym ten, którego używa przykładowa aplikacja: Knex.js. Statystyki zapytań korzystają z informacji zawartych w tych komentarzach, aby zapewnić widok wydajności bazy danych z perspektywy aplikacji i określić, który kod aplikacji powoduje problemy. Obciążenie wydajności powinno być niewielkie. Zapoznaj się z dokumentacją Statystyk zapytań.

1. Aby zatrzymać przykładową aplikację, wpisz w Cloud Shell Ctrl+c.
2. Aby zainstalować pakiety wymagane przez SQLcommenter, uruchom to polecenie:

  npm install @google-cloud/sqlcommenter-knex @opencensus/nodejs @opencensus/propagation-tracecontext @opentelemetry/api @opentelemetry/core --save

3. W Cloud Shell kliknij przycisk , aby uruchomić edytor Cloud Shell.
4. W eksploratorze plików znajdź nodejs-docs-samples/cloud-sql/postgres/knex/server.js i kliknij go, aby wczytać plik server.js w edytorze.
5. Znajdź w pliku ten kod:

const process = require('process');

Poniżej dodaj ten kod:

const {wrapMainKnexAsMiddleware} = require('@google-cloud/sqlcommenter-knex');

6. Znajdź w pliku ten kod:

// Set Content-Type for all responses for these routes.
app.use((req, res, next) => {
  res.set('Content-Type', 'text/html');
  next();
});

Poniżej dodaj ten kod:

app.use(wrapMainKnexAsMiddleware(Knex, {
    traceparent: true,
    tracestate: true,
    route: true,
    db_driver: true
}));

Gdy to zrobisz, kod powinien wyglądać mniej więcej tak:

...
// 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. Kliknij przycisk , aby wrócić do Cloud Shell i uruchomić:

npm start

8. W aplikacji Tabs vs Spaces (Taby a spacje) kliknij przyciski, aby oddać więcej głosów i dodać więcej danych do bazy danych.

9. Wyświetlanie wydajności zapytań i kompleksowego śledzenia za pomocą statystyk

Panel Statystyki zapytań pomaga rozwiązywać problemy z zapytaniami Cloud SQL i szukać problemów z wydajnością. Aby uzyskać dostęp do statystyk, w panelu nawigacyjnym po lewej stronie instancji Cloud SQL wybierz Statystyki zapytań.

Wykres Obciążenie bazy danych – wszystkie zapytania

W panelu Statystyki zapytań najwyższego poziomu wyświetla się wykres Obciążenie bazy danych – wszystkie zapytania.

Wykres zawiera informacje o pojemności procesora, procesorze i oczekiwaniu na procesor, oczekiwaniu na operacje wejścia-wyjścia oraz oczekiwaniu na blokadę. Więcej informacji o znaczeniu tych danych, miejscu ich przechowywania i przykłady wyglądu tego wykresu w przypadku problematycznych zapytań znajdziesz w dokumentacji. W przypadku tej przykładowej aplikacji obciążenie zapytaniami do bazy danych jest niewielkie, więc na wykresie nie widać dużych skoków.

Które zapytania generują największe obciążenie?

Pod wykresem znajdziesz tabelę ZAPYTANIA, która zawiera znormalizowane zapytania z wybranego przedziału czasu. Zapytania w tabeli są posortowane według łącznego czasu wykonywania.

Możesz kliknąć poszczególne zapytania, aby wyświetlić szczegółowe informacje o nich, takie jak obciążenie bazy danych w przypadku tego konkretnego zapytania, opóźnienie zapytania, przykłady planu zapytania i najlepsi użytkownicy. Jeśli aplikacja jest zbudowana przy użyciu ORM, jak w przypadku aplikacji przykładowej, możesz nie wiedzieć, która część aplikacji odpowiada za które zapytanie. Pomoże Ci w tym sekcja Najpopularniejsze tagi.

Skąd w aplikacji pochodzi obciążenie zapytaniem?

Przełącz się z tabeli ZAPYTANIA na tabelę TAGI, aby wyświetlić listę zapytań otagowanych przez logikę biznesową, co pozwoli Ci uzyskać widok bardziej skoncentrowany na aplikacji.

W tabeli TAGI możesz zobaczyć obciążenie bazy danych z podziałem na ścieżki, które je wygenerowały. Na zrzucie ekranu powyżej widać, że '/getAllVotes' ma dłuższy średni czas wykonywania i zwraca średnio więcej wierszy. Czas wykonania widoczny w tabeli nie jest w tym przypadku problematyczny, ale mimo to kliknijmy wiersz '/getAllVotes', aby przyjrzeć się danym bardziej szczegółowo.

Dlaczego zapytania działają powoli?

Kliknij punkt na wykresie Przykładowe plany zapytań, aby zobaczyć plan zapytania.

Plany zapytań pokazują, jak PostgreSQL wykonuje zapytanie w tle, co ułatwia określenie, czy występują operacje, które powodują spowolnienie.

Który kod aplikacji powoduje spowolnienie?

Statystyki zapytań zapewniają też kontekstową wizualizację śledzenia od początku do końca, co może być przydatne do dalszego badania, które części aplikacji generują wolne zapytania.

Kliknij kartę END TO END (OD POCZĄTKU DO KOŃCA), aby wyświetlić ślad w kontekście.

Uwaga: więcej informacji o korzystaniu z planów zapytań i śledzenia kompleksowego oraz ich interpretowaniu znajdziesz w dokumentacji.

10. Zwolnij miejsce i dowiedz się więcej

Z tego artykułu dowiedziałeś się, jak używać Statystyk zapytań do monitorowania i analizowania wydajności zapytań za pomocą aplikacji Node.js i bazy danych Cloud SQL PostgreSQL.

Czyszczenie

Jeśli nie chcesz, aby instancja Cloud SQL była nadal uruchomiona, możesz ją teraz usunąć.

gcloud sql instances delete my-instance

Więcej informacji

• Dokumentacja Statystyk zapytań
• Włączanie tagowania zapytań za pomocą Sqlcommenter, aby zrozumieć wpływ aplikacji na wydajność bazy danych
• Dokumentacja SQL Commenter
• Repozytorium GitHub SQL Commenter