Wprowadzenie do statystyk zapytań dla Cloud SQL

1. Zanim zaczniesz

Statystyki zapytań dla Cloud SQL ułatwiają wykrywanie i diagnozowanie problemów z wydajnością zapytań dotyczących baz danych Cloud SQL, a także zapobieganie im. Udostępnia samoobsługowe, intuicyjne funkcje monitorowania i informacje diagnostyczne, które wykraczają poza wykrywanie, by pomóc w znalezieniu głównej przyczyny problemów z wydajnością.

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

Wymagania wstępne

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

Co trzeba zrobić

• Użyj Cloud SQL w aplikacji Node.js.
• Włącz komentatora SQL w aplikacji Node.js.
• Użyj statystyk zapytań dla Cloud SQL do monitorowania i badania 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 Cloud i utwórz nowy projekt lub wykorzystaj istniejący. Jeśli nie masz jeszcze konta Gmail ani Google Workspace, musisz je utworzyć.

Zapamiętaj identyfikator projektu, którego używasz. W dalszej części tego ćwiczenia w Codelabs będzie ona określana jako PROJECT-ID.

2. Następnie musisz włączyć płatności w Cloud Console, aby korzystać z zasobów Google Cloud.

Ukończenie tego ćwiczenia z programowania nie powinno kosztować zbyt wiele. Postępuj zgodnie z instrukcjami podanymi w sekcji „Wyczyść dane i więcej informacji” W tym samouczku znajdziesz wskazówki, jak wyłączyć zasoby, aby uniknąć naliczania opłat. Nowi użytkownicy Google Cloud mogą skorzystać z programu bezpłatnego okresu próbnego o wartości 300 USD.

Aktywowanie Cloud Shell

1. W konsoli Cloud kliknij Aktywuj Cloud Shell.

Jeśli dopiero zaczynasz korzystać z Cloud Shell, wyświetli się ekran pośredni (w części strony widocznej po przewinięciu) z opisem tej funkcji. W takim przypadku kliknij Dalej (nie zobaczysz go więcej). Tak wygląda ten jednorazowy ekran:

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

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

2. Uruchom to polecenie w Cloud Shell, aby sprawdzić, czy korzystasz z właściwego projektu:

Po nawiązaniu połączenia z Cloud Shell powinno pojawić się informacja, że użytkownik jest już uwierzytelniony i że projekt jest już ustawiony na identyfikator Twojego projektu.

Uwaga: narzędzie wiersza poleceń gcloud to zaawansowane, ujednolicone narzędzie wiersza poleceń w Google Cloud. Jest ona wstępnie zainstalowana w Cloud Shell. Zauważysz, że obsługuje ona funkcję autouzupełniania. Więcej informacji znajdziesz w artykule Omówienie narzędzia wiersza poleceń gcloud.

Uruchom to polecenie, aby sprawdzić, czy korzystasz z właściwego projektu.

gcloud config list project

Jeśli chcesz użyć innego projektu niż ten wybrany podczas otwierania Cloud Shell, możesz ustawić nowy projekt, uruchamiając polecenie:

gcloud config set project <PROJECT-ID>;

3. Konfigurowanie instancji Cloud SQL for PostgreSQL z włączonymi statystykami zapytań

1. Po uruchomieniu Cloud Shell możesz w wierszu poleceń utworzyć nową instancję Cloud SQL o nazwie my-instance z włączonymi statystykami zapytań:

Uwaga: z tego ćwiczenia z programowania włączasz statystyki zapytań za pomocą wiersza poleceń. ale możesz je też włączyć w konsoli, korzystając z menu Statystyki zapytań danej 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 objaśnienie niektórych flag i ich znaczenia:

• Flaga --tier db-f1-micro określa typ maszyny z minimalną ilością zasobów, ponieważ jest ona wykorzystywana do celów programistycznych, a do wykonania ćwiczeń z programowania nie potrzebujesz dużej ilości zasobów. Więcej informacji o poziomach znajdziesz tutaj.
• Flaga --database-version=POSTGRES_12 powoduje utworzenie instancji 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 roota postgres. Pamiętaj, aby zastąpić <PASSWORD> za pomocą wybranego hasła.
• Flaga --insights-config-query-insights-enabled włącza statystyki zapytań w instancji.
• Flaga --insights-config-record-application-tags umożliwia rejestrowanie tagów aplikacji. Więcej informacji na temat tagów aplikacji znajdziesz w kolejnych 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 interfejsu API sqladmin.googleapis.com w projekcie. Jeśli pojawi się prośba o włączenie interfejsu API, wybierz y.

Tworzenie instancji może potrwać kilka minut. Po zakończeniu tej operacji instancja będzie gotowa do użycia.

2. Teraz utwórz bazę danych, której użyjesz na potrzeby przykładowej aplikacji:

gcloud sql databases create votesdb --instance my-instance

Możesz też uzyskać dostęp do instancji i ją skonfigurować za pomocą konsoli Google Cloud.

3. Uzyskaj nazwę połączenia instancji w formacie PROJECT-ID:ZONE-ID:INSTANCE-ID, uruchamiając następujące polecenie. Użyjesz go później podczas konfigurowania aplikacji Node.js.

gcloud sql instances describe my-instance | grep connectionName

4. Utwórz konto usługi do używania z aplikacją

Konta usługi służą do przyznawania uprawnień do korzystania z różnych usług w projekcie GCP. Na potrzeby tego ćwiczenia w programie jest ono potrzebne, aby przyznać serwerowi proxy Cloud SQL uprawnienia do nawiązywania połączenia z instancją Cloud SQL.

Tworzenie konta usługi w konsoli

1. Otwórz stronę kont usługi uprawnień i kliknij przycisk u góry strony.
2. Nadaj kontu usługi unikalną nazwę i identyfikator, a potem kliknij UTWÓRZ.
3. Na następnej stronie kliknij menu Wybierz rolę. Ustaw filtr „Cloud SQL” i wybierz rolę Klient Cloud SQL. Kliknij DALEJ, a następnie 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 wybierz DODAJ KLUCZ, a następnie Utwórz nowy klucz. wybrany zostanie plik JSON; zachować tę wartość domyślną i kliknąć UTWÓRZ. Spowoduje to pobranie pliku .json z kluczem prywatnym. Kliknij ZAMKNIJ.
5. W Cloud Shell kliknij 3 kropki, aby wyświetlić menu Więcej, i wybierz Prześlij plik. Otwórz plik .json pobrany na komputer lokalny i go wybierz. Spowoduje to przesłanie pliku .json do katalogu głównego w Cloud Shell.

5. Instalowanie i uruchamianie serwera proxy Cloud SQL

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 następujący sposób po zastąpieniu <INSTANCE_CONNECTION_NAME> nazwą połączenia instancji skopiowaną ze strony przeglądu instancji Cloud SQL.

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

Jeśli się uda, powinno wyświetlić się kilka wierszy danych wyjściowych, które zakończą się komunikatem Ready for new connections.

6. Klonowanie i testowanie aplikacji lokalnie

1. Skopiuj 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 te 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. Kliknij Podgląd w przeglądarce w Cloud Shell i wybierz Podejrzyj na porcie 8080.

Aplikacja do głosowania w kartach i pokojach powinna wyglądać w przeglądarce jak poniżej:

5. Klikaj przyciski, aby oddać głosy i zapisać niektóre dane w bazie danych.

7. Dodaj stronę, aby zobaczyć wszystkie głosy

Ponieważ ta przykładowa aplikacja jest bardzo prosta, trzeba dodać dodatkową stronę ze wszystkimi głosami. Główną przyczyną jest to, aby mieć więcej danych do sprawdzenia podczas późniejszego użycia statystyk zapytań.

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

Dodaj ten kod po zdefiniowaniu funkcji 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 poniżej kod trasy '/getAllVotes', gdzie są zdefiniowane 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. W katalogu nodejs-docs-samples/cloud-sql/postgres/knex/views utwórz 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ć polecenie:

npm start

7. Otwórz aplikację w podglądzie w przeglądarce, aby sprawdzić, czy działa. Aby wyświetlić dodaną stronę, dodaj /getAllVotes pod adresem URL w przeglądarce.

8. Włącz komentator SQL w aplikacji

Teraz zainstalujesz i włączysz SQL Commenter – bibliotekę open source, która umożliwia systemom ORM uzupełnianie instrukcji SQL o komentarze przed wykonaniem. SQLcommenter obsługuje kilka platform ORM i platform, w tym tę, z której korzysta przykładowa aplikacja: Knex.js. Statystyki zapytań wykorzystują informacje z tych komentarzy, aby wyświetlać skoncentrowany na aplikacji widok wydajności bazy danych i określić, który kod aplikacji powoduje problemy. Przewidujemy, że narzut będzie niewielki. Zobacz dokumentację Statystyk zapytań.

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

  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 odszukaj plik nodejs-docs-samples/cloud-sql/postgres/knex/server.js i kliknij go, aby wczytać plik server.js do edytora.
5. Znajdź w pliku ten kod:

const process = require('process');

Dodaj pod nim 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();
});

Dodaj pod nim ten kod:

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

Gdy to zrobisz, Twój 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ć polecenie:

npm start

8. W aplikacji Karty a pokoje kliknij przyciski, aby oddać więcej głosów i dodać więcej danych do bazy danych.

9. Używaj Statystyk do wyświetlania wydajności zapytań i kompleksowego śledzenia

Panel statystyk zapytań ułatwia rozwiązywanie problemów z zapytaniami Cloud SQL w poszukiwaniu problemów z wydajnością. Aby uzyskać dostęp do Statystyk, wybierz Statystyki zapytań w panelu nawigacyjnym po lewej stronie instancji Cloud SQL.

Obciążenie bazy danych – wykres wszystkich zapytań

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

Wykres zawiera informacje o pojemności procesora, liczbie procesorów i czasie oczekiwania na procesor, oczekiwanie na operacje wejścia-wyjścia oraz oczekiwanie na blokadę. Więcej informacji o tym, co oznaczają te wskaźniki i gdzie są przechowywane, a także przykłady tego, jak ten wykres przedstawia w przypadku problematycznych zapytań znajdziesz w dokumentacji. W przypadku tej przykładowej aplikacji obciążenie baz danych jest niewielkie, więc na wykresie nie ma żadnych dużych skoków.

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

Poniżej wykresu znajduje się tabela ZAPYTAŃ, która zawiera znormalizowane zapytania dla wybranego zakresu czasu. Zapytania w tabeli są posortowane według łącznego czasu wykonywania.

Możesz kliknąć konkretne zapytanie, aby wyświetlić szczegółowe informacje o nim, takie jak obciążenie bazy danych dla tego konkretnego zapytania, czas oczekiwania na zapytanie, przykłady planów zapytań i najwięksi użytkownicy. Jeśli aplikacja została skompilowana przy użyciu ORM (tak jak w przypadku przykładowej aplikacji), możesz nie wiedzieć, która jej część odpowiada za dane zapytanie. Pomoże Ci w tym sekcja Najczęstsze tagi.

Skąd bierze się wczytywanie zapytań w aplikacji?

Przełącz się z tabeli ZAPYTANIA na tabelę TAGI, aby wyświetlić listę zapytań otagowanych przez logikę biznesową, co zapewnia bardziej koncentrowany na aplikacjach widok.

W tabeli TAGS można zobaczyć obciążenie bazy danych z podziałem na trasy, które wygenerowały obciążenie. Na zrzucie ekranu powyżej możesz zobaczyć, że trasa '/getAllVotes' ma dłuższy średni czas wykonywania i średnio więcej zwróconych wierszy. Czas wykonania widoczny w tabeli nie stanowi w tym przypadku problemu, ale mimo to kliknij wiersz dotyczący pozycji '/getAllVotes', aby przyjrzeć się danym bardziej szczegółowo.

Dlaczego zapytania działają wolno?

Kliknij kropkę na wykresie Przykłady planów zapytań, aby wyświetlić plan zapytania.

Plany zapytań pokazują, jak PostgreSQL wykonuje zapytanie w tle, dzięki czemu łatwiej określić, czy istnieją operacje, które powodują spowolnienie.

Który kod aplikacji powoduje spowolnienie działania?

Statystyki zapytań udostępniają też kontekstową wizualizację całego śledzenia, co może być pomocne podczas dalszych badań nad tym, które części aplikacji generują powolne zapytania.

Kliknij kartę KONIEC DO KONIEC, aby wyświetlić ślad ujęty w kontekście.

Uwaga: więcej informacji o używaniu i interpretowaniu planów zapytań i pełnego śledzenia znajdziesz w dokumentacji.

10. Wyczyść dane i dowiedz się więcej

Wiesz już, jak korzystać ze statystyk zapytań do monitorowania i badania wydajności zapytań z użyciem aplikacji w Node.js i bazy danych PostgreSQL w Cloud SQL.

Czyszczenie

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

gcloud sql instances delete my-instance

Więcej informacji

• Korzystanie ze Statystyk zapytań
• Włącz tagowanie zapytań za pomocą narzędzia Sqlcommenter, aby poznać wpływ aplikacji na wydajność bazy danych
• Dokumentacja SQL Komentator
• Repozytorium GitHub komentatora SQL