Présentation de Cloud SQL Insights

Cloud SQL Insights vous permet de détecter, de diagnostiquer et d'éviter les problèmes de performances des requêtes pour les bases de données Cloud SQL. Il fournit des informations en libre-service, une surveillance intuitive et des diagnostics qui vont au-delà de la détection afin de vous aider à identifier l'origine des problèmes de performances.

Dans cet atelier de programmation, vous allez apprendre à configurer une instance Cloud SQL pour PostgreSQL, à déployer une application Node.js afin d'utiliser l'instance Cloud SQL comme backend de stockage, puis à utiliser Cloud SQL Insights pour afficher et surveiller les requêtes.

Prérequis

Connaissances de base du langage et des outils de programmation Node.js

Objectifs de l'atelier

Utiliser Cloud SQL dans une application Node.js.
Activer "SQL Commenter" dans une application Node.js.
Surveiller et analyser les performances des requêtes à l'aide de Cloud SQL Insights.

Prérequis

Un compte Google Cloud dans lequel vous êtes autorisé à activer des API et à créer des services.

Configuration de l'environnement au rythme de chacun

Connectez-vous à Cloud Console, puis créez un projet ou réutilisez un projet existant. (Si vous ne possédez pas encore de compte Gmail ou Google Workspace, vous devez en créer un.)

Mémorisez l'ID du projet que vous utilisez. Il sera désigné par le nom PROJECT-ID tout au long de cet atelier de programmation.

Vous devez ensuite activer la facturation dans Cloud Console pour pouvoir utiliser les ressources Google Cloud.

L'exécution de cet atelier de programmation est très peu coûteuse, voire gratuite. Veillez à suivre les instructions de la section "Nettoyer et en savoir plus" qui indique comment désactiver les ressources afin d'éviter les frais une fois ce tutoriel terminé. Les nouveaux utilisateurs de Google Cloud peuvent participer au programme d'essai gratuit pour bénéficier d'un crédit de 300 $.

Activer Cloud Shell

Dans Cloud Console, cliquez sur Activer Cloud Shell.

Activer Cloud Shell

Si vous n'avez jamais démarré Cloud Shell auparavant, un écran intermédiaire s'affiche en dessous de la ligne de séparation pour décrire de quoi il s'agit. Si tel est le cas, cliquez sur Continuer (cet écran ne s'affiche qu'une seule fois). Voici à quoi il ressemble :

Fenêtre de la boîte de dialogue Cloud Shell

Le provisionnement et la connexion à Cloud Shell ne devraient pas prendre plus de quelques minutes.

Terminal Cloud Shell

Cette machine virtuelle contient tous les outils de développement nécessaires. Elle intègre un répertoire d'accueil persistant de 5 Go et s'exécute sur Google Cloud, ce qui améliore nettement les performances du réseau et l'authentification.

Exécutez la commande suivante dans Cloud Shell pour vérifier que vous utilisez bien le bon projet :

Une fois connecté à Cloud Shell, vous êtes en principe authentifié et le projet est défini avec votre ID de projet.

Exécutez la commande suivante pour vérifier que vous utilisez bien le bon projet.

gcloud config list project

Si vous souhaitez utiliser un projet différent de celui que vous avez sélectionné lors de l'ouverture de Cloud Shell, vous pouvez en créer un en exécutant la commande suivante :

gcloud config set project <PROJECT-ID>;

Après le lancement de Cloud Shell, vous pouvez utiliser la ligne de commande pour créer une instance Cloud SQL nommée my-instance, avec Cloud SQL Insights activé :

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

Voici une brève description des indicateurs et leur signification :

L'indicateur --tier db-f1-micro spécifie un type de machine avec des ressources minimales, car l'instance est utilisée à des fins de développement et vous n'avez pas besoin de beaucoup de ressources pour l'atelier de programmation. Pour en savoir plus sur les niveaux, cliquez ici.
L'indicateur --database-version=POSTGRES_12 crée une instance utilisant PostgreSQL version 12.
L'indicateur --region=us-central spécifie la région dans laquelle l'instance sera créée.
L'indicateur --root-password=<PASSWORD> vous permet de spécifier le mot de passe de l'utilisateur racine postgres. Veillez à remplacer <PASSWORD> par le mot de passe de votre choix.
L'indicateur --insights-config-query-insights-enabled active Cloud SQL Insights sur votre instance.
L'indicateur --insights-config-record-application-tags permet d'enregistrer les tags d'application. Pour en savoir plus sur les tags d'application, consultez les sections suivantes.
L'indicateur --insights-config-record-client-address permet d'enregistrer les adresses IP clientes dans Cloud SQL Insights.

Vous pouvez être invité à activer l'API sqladmin.googleapis.com pour votre projet. Si vous y êtes invité, sélectionnez y pour activer l'API.

La création de l'instance prend plusieurs minutes. Une fois cette opération terminée, votre instance sera prête à être utilisée.

Créez maintenant une base de données que vous utiliserez pour l'exemple d'application :

gcloud sql databases create votesdb --instance my-instance

Vous pouvez également accéder à l'instance et la configurer via Cloud Console.

Obtenez le nom de la connexion à l'instance au format PROJECT-ID:ZONE-ID:INSTANCE-ID en exécutant la commande suivante. Vous l'utiliserez plus tard pour configurer votre application Node.js.

gcloud sql instances describe my-instance | grep connectionName

Les comptes de service permettent d'accorder des autorisations afin d'utiliser différents services dans votre projet GCP. Pour cet atelier de programmation, vous en aurez besoin pour accorder au proxy Cloud SQL l'autorisation de se connecter à l'instance Cloud SQL.

Créer un compte de service dans la console

Accédez à la page Comptes de service IAM puis cliquez sur le bouton en haut de la page.
Attribuez un nom et un identifiant uniques à votre compte de service, puis cliquez sur CREATE (CRÉER).
Sur la page suivante, cliquez sur le menu déroulant "Sélectionner un rôle". Filtrez sur "Cloud SQL" et sélectionnez le rôle "Client Cloud SQL". Cliquez sur CONTINUE (CONTINUER), puis sur DONE (OK).
Une fois le compte de service créé, cliquez sur les trois points sous Actions pour votre nouveau compte de service puis sélectionnez "Create key" (Créer une clé). JSON sera sélectionné. Conservez cette valeur par défaut, puis cliquez sur CREATE (CRÉER). Un fichier de clé privée .json est alors téléchargé. Cliquez sur CLOSE (FERMER).
Dans Cloud Shell, cliquez sur les trois points du menu Plus puis sélectionnez Importer un fichier. Accédez au fichier .json que vous avez téléchargé sur votre ordinateur local et sélectionnez-le. Le fichier .json sera alors importé dans votre répertoire d'accueil dans Cloud Shell.

Vous utiliserez le proxy Cloud SQL pour la communication entre l'application et l'instance de base de données.

Téléchargez le proxy Cloud SQL. Dans Cloud Shell, vous pouvez exécuter :

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

Pour exécuter le proxy, utilisez le nom de connexion de l'instance que vous avez copié à partir des détails de l'instance Cloud SQL pour remplacer <INSTANCE_CONNECTION_NAME>. Pour le fichier d'identifiants, saisissez le chemin d'accès au fichier que vous avez importé à l'étape précédente.

./cloud_sql_proxy -credential_file=/path/to/credential_file.json -instances=<INSTANCE_CONNECTION_NAME>=tcp:5432 &

Si l'opération réussit, vous devriez voir en sortie quelques lignes se terminant par un message Ready for new connections.

Clonez le dépôt de l'exemple d'application, puis installez les packages nécessaires à l'exécution de l'application.

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

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

npm install

Définissez les variables d'environnement suivantes :

export CLOUD_SQL_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'

Exécutez la commande createTable.js pour créer la table de base de données requise par l'application et vous assurer qu'elle est correctement configurée avant de démarrer l'exemple d'application.

node createTable.js $DB_USER $DB_PASS $DB_NAME $CLOUD_SQL_CONNECTION_NAME votes $DB_HOST

npm start

Cliquez sur Aperçu sur le Web dans Cloud Shell, puis sélectionnez Prévisualiser sur le port 8080.

Élément de menu "Prévisualiser sur le port 8080"

L'application de vote "Tabs vs Spaces" devrait apparaître dans votre navigateur comme dans l'illustration ci-dessous :

Capture d'écran de l'application de vote "Tabs vs Spaces"

Cliquez sur les boutons pour ajouter des votes et enregistrer des données dans la base de données.

Comme cet exemple d'application est très simple, vous allez ajouter une page supplémentaire affichant tous les votes. Cela vous permettra principalement de disposer de davantage de données lorsque vous utiliserez Cloud SQL Insights par la suite.

Appuyez sur Ctrl+c dans Cloud Shell pour arrêter l'exemple d'application.
Dans Cloud Shell, cliquez sur le bouton pour lancer l'éditeur Cloud Shell.
Dans l'explorateur de fichiers, recherchez nodejs-docs-samples/cloud-sql/postgres/knex/server.js et cliquez dessus pour charger le fichier server.js dans l'éditeur.

Ajoutez le code suivant après avoir défini la fonction 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');
};

Ajoutez le code suivant pour la route '/getAllVotes' ci-dessous où les autres routes sont définies :

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

Créez un fichier nommé allvotes.pug dans le répertoire nodejs-docs-samples/cloud-sql/postgres/knex/views. Collez le code suivant :

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

Cliquez sur le bouton pour revenir à Cloud Shell et exécutez :

npm start

Ouvrez l'application dans la fonction Aperçu sur le Web pour vérifier qu'elle fonctionne correctement. Ajoutez /getAllVotes à l'URL dans le navigateur pour afficher la nouvelle page que vous avez ajoutée.

Vous allez installer et activer SQL Commenter, une bibliothèque Open Source qui permet aux ORM d'enrichir les instructions SQL avec des commentaires avant l'exécution. SQLcommenter est compatible avec plusieurs ORM et frameworks, y compris celui utilisé par l'exemple d'application : Knex.js. Cloud SQL Insights utilise les informations contenues dans ces commentaires pour afficher une vue de votre application axée sur les performances de la base de données et identifier le code d'application à l'origine des problèmes. L'impact sur les performances devrait être faible. Pour plus d'informations sur les performances, consultez la documentation.

Appuyez sur Ctrl+c dans Cloud Shell pour arrêter l'exemple d'application.
Exécutez la commande suivante pour installer les packages dont SQLcommenter a besoin :

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

Dans Cloud Shell, cliquez sur le bouton pour lancer l'éditeur Cloud Shell.
Dans l'explorateur de fichiers, recherchez nodejs-docs-samples/cloud-sql/postgres/knex/server.js et cliquez dessus pour charger le fichier server.js dans l'éditeur.
Recherchez ce code dans le fichier :

const process = require('process');

En dessous, ajoutez le code suivant :

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

Recherchez ce code dans le fichier :

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

En dessous, ajoutez le code suivant :

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

Une fois cette opération terminée, votre code doit ressembler à ce qui suit :

...
// Require process, so we can mock environment variables.
const process = require('process');

const {wrapMainKnexAsMiddleware} = require('@google-cloud/sqlcommenter-knex');
const express = require('express');
const bodyParser = require('body-parser');
const Knex = require('knex');

const app = express();
app.set('view engine', 'pug');
app.enable('trust proxy');

// Automatically parse request body as form data.
app.use(bodyParser.urlencoded({extended: false}));
app.use(bodyParser.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
}));
...

Cliquez sur le bouton pour revenir à Cloud Shell et exécutez :

npm start

Dans l'application "Tabs vs Spaces", cliquez sur les boutons pour ajouter des votes et ajouter des données à la base de données.

Le tableau de bord Query Insights vous aide à résoudre les problèmes de performances des requêtes Cloud SQL.

Charge de la base de données : graphique de toutes les requêtes

Le tableau de bord de premier niveau de Query Insights affiche le graphique Charge de la base de données – toutes les requêtes.

Graphique de toutes les requêtes

Le graphique contient des informations sur la capacité du processeur, l'attente du processeur, le processeur lui-même, le temps d'attente d'E/S et le temps d'attente de verrouillage. Pour en savoir plus sur la signification de ces statistiques ou sur l'emplacement où les métriques sont stockées et pour voir des exemples de ce graphique pour des requêtes problématiques, consultez la documentation. Dans cet exemple d'application, la charge de requêtes de la base de données est faible. Le graphique ne présente donc aucun pic important.

Quelles sont les requêtes impactant le plus la charge ?

Sous le graphique, vous trouverez un tableau "QUERIES" contenant les requêtes normalisées pour la période sélectionnée. Les requêtes de la table sont triées par durée d'exécution totale.

Tableau des requêtes les plus fréquentes

Vous pouvez cliquer sur une requête pour afficher des informations détaillées telles que la charge de la base de données pour cette requête spécifique, la latence de la requête, les exemples de plan de requête et les principaux utilisateurs. Si une application est créée à l'aide d'un ORM comme c'est le cas pour l'exemple d'application, vous ne saurez peut-être pas quelle partie de l'application est responsable de quelle requête. La section "Principaux tags" peut vous aider à répondre à cette question.

Quelle est l'origine de la charge de requêtes dans l'application ?

Passez du tableau "QUERIES" au tableau "TAGS" pour afficher la liste des requêtes associées à une logique métier, ce qui vous permet d'obtenir une vue plus axée sur les applications.

Tableau des tags les plus fréquents

Dans le tableau "TAGS", vous pouvez voir la charge de la base de données ventilée en fonction de l'itinéraire qui a généré la charge. Dans la capture d'écran ci-dessus, vous pouvez constater que le routage '/getAllVotes' a un temps d'exécution moyen plus élevé et qu'il contient en moyenne plus de lignes. Bien que la durée d'exécution indiquée dans le tableau ne pose pas problème dans le cas présent, cliquez sur la ligne '/getAllVotes' pour consulter les données plus en détail.

Pourquoi les requêtes sont-elles lentes ?

Cliquez sur le point dans le graphique Exemples de plan de requête pour afficher un plan de requête.

Exemples de plans de requêtes

Les plans de requête montrent comment PostgreSQL exécute une requête en arrière-plan, ce qui permet de déterminer plus facilement si certaines opérations entraînent une lenteur.

Quel code de l'application contribue à la lenteur ?

Cloud SQL Insights propose également une visualisation en contexte du traçage de bout en bout, ce qui peut être utile pour étudier plus en détail les parties d'une application qui génèrent des requêtes lentes.

Cliquez sur l'onglet END TO END (DE BOUT EN BOUT) pour afficher une trace contextuelle.

Trace de bout en bout

Vous avez appris à utiliser Cloud SQL Insights pour surveiller et analyser les performances des requêtes avec une application Node.js et une base de données PostgreSQL Cloud SQL.

Effectuer un nettoyage

Si vous ne souhaitez pas conserver votre instance Cloud SQL, vous pouvez la supprimer.

gcloud sql instances delete my-instance

Présentation de Cloud SQL Insights

Prérequis

Objectifs de l'atelier

Prérequis

Configuration de l'environnement au rythme de chacun

Activer Cloud Shell

Créer un compte de service dans la console

Charge de la base de données : graphique de toutes les requêtes

Quelles sont les requêtes impactant le plus la charge ?

Quelle est l'origine de la charge de requêtes dans l'application ?

Pourquoi les requêtes sont-elles lentes ?

Quel code de l'application contribue à la lenteur ?

Effectuer un nettoyage

En savoir plus