Accelerazione delle query analitiche con il motore colonnare in AlloyDB Omni.

1. Introduzione

In questo codelab imparerai a eseguire il deployment di AlloyDB Omni e a utilizzare il motore colonnare per migliorare le prestazioni delle query.

7da257ba067ed1b1.png

Prerequisiti

  • Conoscenza di base della console Google Cloud
  • Competenze di base nell'interfaccia a riga di comando e in Google Shell

Cosa imparerai a fare

  • Come eseguire il deployment di AlloyDB Omni su VM GCE in Google Cloud
  • Come connettersi ad AlloyDB Omni
  • Come caricare i dati in AlloyDB Omni
  • Come attivare il motore colonnare
  • Come controllare il motore colonnare in modalità automatica
  • Come compilare manualmente il Datastore a colonne

Che cosa ti serve

  • Un account Google Cloud e un progetto Google Cloud
  • Un browser web come Chrome

2. Configurazione e requisiti

Configurazione dell'ambiente a tuo ritmo

  1. Accedi alla console Google Cloud e crea un nuovo progetto o riutilizzane uno esistente. Se non hai ancora un account Gmail o Google Workspace, devi crearne uno.

fbef9caa1602edd0.png

a99b7ace416376c4.png

5e3ff691252acf41.png

  • Il nome del progetto è il nome visualizzato per i partecipanti al progetto. Si tratta di una stringa di caratteri non utilizzata dalle API di Google. Puoi sempre aggiornarlo.
  • L'ID progetto è univoco per tutti i progetti Google Cloud ed è immutabile (non può essere modificato dopo essere stato impostato). La console Cloud genera automaticamente una stringa univoca; di solito non ti interessa quale sia. Nella maggior parte dei codelab, dovrai fare riferimento al tuo ID progetto (in genere identificato come PROJECT_ID). Se l'ID generato non ti piace, puoi generarne un altro casuale. In alternativa, puoi provare il tuo e vedere se è disponibile. Non può essere modificato dopo questo passaggio e rimane invariato per tutta la durata del progetto.
  • Per tua informazione, esiste un terzo valore, un Numero progetto, utilizzato da alcune API. Scopri di più su tutti e tre questi valori nella documentazione.
  1. Successivamente, dovrai abilitare la fatturazione nella console Cloud per utilizzare le API/risorse Cloud. La partecipazione a questo codelab non ha costi, o quasi. Per arrestare le risorse ed evitare di incorrere in fatturazione al termine di questo tutorial, puoi eliminare le risorse che hai creato o il progetto. I nuovi utenti di Google Cloud sono idonei al programma Prova senza costi di 300$.

Avvia Cloud Shell

Sebbene Google Cloud possa essere utilizzato da remoto dal tuo laptop, in questo codelab utilizzerai Google Cloud Shell, un ambiente a riga di comando in esecuzione nel cloud.

Nella console Google Cloud, fai clic sull'icona di Cloud Shell nella barra degli strumenti in alto a destra:

55efc1aaa7a4d3ad.png

Dovrebbe richiedere solo pochi istanti per eseguire il provisioning e connettersi all'ambiente. Al termine, dovresti vedere qualcosa di simile a questo:

7ffe5cbb04455448.png

Questa macchina virtuale contiene tutti gli strumenti di sviluppo di cui avrai bisogno. Offre una home directory permanente da 5 GB e viene eseguita su Google Cloud, migliorando notevolmente le prestazioni e l'autenticazione di rete. Tutto il lavoro in questo codelab può essere svolto in un browser. Non devi installare nulla.

3. Prima di iniziare

Attiva l'API

Output:

In Cloud Shell, assicurati che l'ID progetto sia configurato:

PROJECT_ID=$(gcloud config get-value project)
echo $PROJECT_ID

Se non è definito nella configurazione di Cloud Shell, configuralo utilizzando i seguenti comandi

export PROJECT_ID=<your project>
gcloud config set project $PROJECT_ID

Attiva tutti i servizi necessari:

gcloud services enable compute.googleapis.com

Risultato previsto

student@cloudshell:~ (test-project-001-402417)$ PROJECT_ID=$(gcloud config get-value project)
Your active configuration is: [cloudshell-14650]
student@cloudshell:~ (test-project-001-402417)$ gcloud config set project test-project-001-402417
Updated property [core/project].
student@cloudshell:~ (test-project-001-402417)$ gcloud services enable compute.googleapis.com
Operation "operations/acat.p2-4470404856-1f44ebd8-894e-4356-bea7-b84165a57442" finished successfully.

4. Esegui il deployment di AlloyDB Omni su GCE

Per eseguire il deployment di AlloyDB Omni su GCE, dobbiamo preparare una macchina virtuale con configurazione e software compatibili. Ecco un esempio di come eseguire il deployment di AlloyDB Omni su una VM basata su Debian.

Crea una VM GCE

Dobbiamo eseguire il deployment di una VM con una configurazione accettabile per CPU, memoria e spazio di archiviazione. Utilizzeremo l'immagine Debian predefinita con le dimensioni del disco di sistema aumentate a 20 GB per ospitare i file del database AlloyDB Omni.

Possiamo utilizzare Cloud Shell o un terminale con Cloud SDK installato

Tutti i passaggi sono descritti anche nella guida rapida per AlloyDB Omni.

Configura le variabili di ambiente per il deployment.

export ZONE=us-central1-a
export MACHINE_TYPE=n2-highmem-2
export DISK_SIZE=20
export MACHINE_NAME=omni01

Poi utilizziamo gcloud per creare la VM GCE.

gcloud compute instances create $MACHINE_NAME \
    --project=$(gcloud info --format='value(config.project)') \
    --zone=$ZONE  --machine-type=$MACHINE_TYPE \
    --metadata=enable-os-login=true \
    --create-disk=auto-delete=yes,boot=yes,size=$DISK_SIZE,image=projects/debian-cloud/global/images/$(gcloud compute images list --filter="family=debian-12 AND family!=debian-12-arm64" \
    --format="value(name)"),type=pd-ssd

Output della console previsto:

gleb@cloudshell:~ (gleb-test-short-001-415614)$ export ZONE=us-central1-a
export MACHINE_TYPE=n2-highmem-2
export DISK_SIZE=20
export MACHINE_NAME=omni01
gleb@cloudshell:~ (gleb-test-short-001-415614)$ gcloud compute instances create $MACHINE_NAME \
    --project=$(gcloud info --format='value(config.project)') \
    --zone=$ZONE  --machine-type=$MACHINE_TYPE \
    --metadata=enable-os-login=true \
    --create-disk=auto-delete=yes,boot=yes,size=$DISK_SIZE,image=projects/debian-cloud/global/images/$(gcloud compute images list --filter="family=debian-12 AND family!=debian-12-arm64" \
    --format="value(name)"),type=pd-ssd
Created [https://www.googleapis.com/compute/v1/projects/gleb-test-short-001-415614/zones/us-central1-a/instances/omni01].
WARNING: Some requests generated warnings:
 - Disk size: '20 GB' is larger than image size: '10 GB'. You might need to resize the root repartition manually if the operating system does not support automatic resizing. See https://cloud.google.com/compute/docs/disks/add-persistent-disk#resize_pd for details.

NAME: omni01
ZONE: us-central1-a
MACHINE_TYPE: n2-highmem-2
PREEMPTIBLE: 
INTERNAL_IP: 10.128.0.3
EXTERNAL_IP: 35.232.157.123
STATUS: RUNNING
gleb@cloudshell:~ (gleb-test-short-001-415614)$ 

Installa AlloyDB Omni

Connettiti alla VM creata:

gcloud compute ssh omni01 --zone $ZONE

Output della console previsto:

gleb@cloudshell:~ (gleb-test-short-001-415614)$ gcloud compute ssh omni01 --zone $ZONE
Warning: Permanently added 'compute.5615760774496706107' (ECDSA) to the list of known hosts.
Linux omni01.us-central1-a.c.gleb-test-short-003-421517.internal 6.1.0-20-cloud-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.85-1 (2024-04-11) x86_64

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
gleb@omni01:~$

Esegui il seguente comando nel terminale collegato.

Installa Docker sulla VM:

sudo apt update
sudo apt-get -y install docker.io

Output della console previsto(oscurato):

gleb@omni01:~$ sudo apt update
sudo apt-get -y install docker.io
Get:1 file:/etc/apt/mirrors/debian.list Mirrorlist [30 B]
Get:5 file:/etc/apt/mirrors/debian-security.list Mirrorlist [39 B]
Get:7 https://packages.cloud.google.com/apt google-compute-engine-bookworm-stable InRelease [5146 B]
Get:8 https://packages.cloud.google.com/apt cloud-sdk-bookworm InRelease [6406 B]        
Get:9 https://packages.cloud.google.com/apt google-compute-engine-bookworm-stable/main amd64 Packages [1916 B]
Get:2 https://deb.debian.org/debian bookworm InRelease [151 kB]
...
Setting up binutils (2.40-2) ...
Setting up needrestart (3.6-4+deb12u1) ...
Processing triggers for man-db (2.11.2-2) ...
Processing triggers for libc-bin (2.36-9+deb12u4) ...
gleb@omni01:~$

Definisci la password per l'utente postgres:

export PGPASSWORD=<your password>

Crea una directory per i dati di AlloyDB Omni. Si tratta di un approccio facoltativo, ma consigliato. Per impostazione predefinita, i dati vengono creati utilizzando il livello del file system temporaneo di Docker e tutto viene distrutto quando il contenitore Docker viene eliminato. Separarli ti consente di gestire i container indipendentemente dai dati e, facoltativamente, di posizionarli in uno spazio di archiviazione con caratteristiche di I/O migliori

Ecco un comando che crea una directory nella home directory dell'utente in cui verranno posizionati tutti i dati:

mkdir -p $HOME/alloydb-data

Esegui il deployment del contenitore AlloyDB Omni:

sudo docker run --name my-omni \
  -e POSTGRES_PASSWORD=$PGPASSWORD \
  -p 5432:5432 \
  -v $HOME/alloydb-data:/var/lib/postgresql/data \
  -v /dev/shm:/dev/shm \
  -d google/alloydbomni

Output della console previsto(oscurato):

gleb@omni01:~$ export PGPASSWORD=StrongPassword
gleb@omni01:~$ sudo docker run --name my-omni \
  -e POSTGRES_PASSWORD=$PGPASSWORD \
  -p 5432:5432 \
  -v $HOME/alloydb-data:/var/lib/postgresql/data \
  -v /dev/shm:/dev/shm \
  -d google/alloydbomni
Unable to find image 'google/alloydbomni:latest' locally
latest: Pulling from google/alloydbomni
71215d55680c: Pull complete 
...
2e0ec3fe1804: Pull complete 
Digest: sha256:d6b155ea4c7363ef99bf45a9dc988ce5467df5ae8cd3c0f269ae9652dd1982a6
Status: Downloaded newer image for google/alloydbomni:latest
56de4ae0018314093c8b048f69a1e9efe67c6c8117f44c8e1dc829a2d4666cd2
gleb@omni01:~$ 

Installa il software client PostgreSQL nella VM (facoltativo, dovrebbe essere già installato):

sudo apt install -y  postgresql-client

Output della console previsto:

gleb@omni01:~$ sudo apt install -y  postgresql-client
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
postgresql-client is already the newest version (15+248).
0 upgraded, 0 newly installed, 0 to remove and 4 not upgraded.

Connettiti ad AlloyDB Omni:

psql -h localhost -U postgres

Output della console previsto:

gleb@omni01:~$ psql -h localhost -U postgres
psql (15.6 (Debian 15.6-0+deb12u1), server 15.5)
Type "help" for help.

postgres=# 

Disconnettiti da AlloyDB Omni:

exit

Output della console previsto:

postgres=# exit
gleb@omni01:~$ 

5. Preparare un database di test

Per testare il motore colonnare, dobbiamo creare un database e riempirlo con alcuni dati di test.

Crea database

Connettiti alla VM AlloyDB Omni e crea un database

Nella sessione Cloud Shell, esegui:

gcloud config set project $(gcloud config get-value project)

Connettiti alla VM AlloyDB Omni:

ZONE=us-central1-a
gcloud compute ssh omni01 --zone $ZONE

Output della console previsto:

student@cloudshell:~ (gleb-test-short-001-416213)$ gcloud config set project $(gcloud config get-value project)
Updated property [core/project].
student@cloudshell:~ (gleb-test-short-001-416213)$ ZONE=us-central1-a
gcloud compute ssh omni01 --zone $ZONE
Linux omni01.us-central1-a.c.gleb-test-short-003-421517.internal 6.1.0-20-cloud-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.85-1 (2024-04-11) x86_64

The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Mon Mar  4 18:17:55 2024 from 35.237.87.44
student@omni01:~$

Nella sessione SSH stabilita, esegui:

export PGPASSWORD=<your password>
psql -h localhost -U postgres -c "CREATE DATABASE quickstart_db"

Output della console previsto:

student@omni01:~$ psql -h localhost -U postgres -c "CREATE DATABASE quickstart_db"
CREATE DATABASE
student@omni01:~$

Creare una tabella con dati di esempio

Per i nostri test utilizzeremo dati pubblici relativi ai produttori di assicurazioni autorizzati in Iowa. Puoi trovare questo set di dati sul sito web del governo dell'Iowa all'indirizzo https://data.iowa.gov/Regulation/Insurance-Producers-Licensed-in-Iowa/n4cc-vqyk/about_data .

Per prima cosa, dobbiamo creare una tabella.

Nella VM GCE, esegui:

psql -h localhost -U postgres -d quickstart_db -c "DROP TABLE if exists insurance_producers_licensed_in_iowa;
CREATE TABLE insurance_producers_licensed_in_iowa (
        npn int8,
        last_name text,
        first_name text,
        address_line_1 text,
        address_line_2 text,
        address_line_3 text,
        city text,
        state text,
        zip int4,
        firstactivedate timestamp,
        expirydate timestamp,
        business_phone text,
        email text,
        physical_location text,
        iowaresident text,
        loa_has_crop text,
        loa_has_surety text,
        loa_has_ah text,
        loa_has_life text,
        loa_has_variable text,
        loa_has_personal_lines text,
        loa_has_credit text,
        loa_has_excess text,
        loa_has_property text,
        loa_has_casualty text,
        loa_has_reciprocal text
);"

Output della console previsto:

otochkin@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "DROP TABLE if exists insurance_producers_licensed_in_iowa;
CREATE TABLE insurance_producers_licensed_in_iowa (
        npn int8,
        last_name text,
        first_name text,
        address_line_1 text,
        address_line_2 text,
        address_line_3 text,
        city text,
        state text,
        zip int4,
        firstactivedate timestamp,
        expirydate timestamp,
        business_phone text,
        email text,
        physical_location text,
        iowaresident text,
        loa_has_crop text,
        loa_has_surety text,
        loa_has_ah text,
        loa_has_life text,
        loa_has_variable text,
        loa_has_personal_lines text,
        loa_has_credit text,
        loa_has_excess text,
        loa_has_property text,
        loa_has_casualty text,
        loa_has_reciprocal text
);"
NOTICE:  table "insurance_producers_licensed_in_iowa" does not exist, skipping
DROP TABLE
CREATE TABLE
otochkin@omni01:~$

Carica i dati nella tabella.

Nella VM GCE, esegui:

curl https://data.iowa.gov/api/views/n4cc-vqyk/rows.csv | psql -h localhost -U postgres -d quickstart_db -c "\copy insurance_producers_licensed_in_iowa from stdin csv header"

Output della console previsto:

otochkin@omni01:~$ curl https://data.iowa.gov/api/views/n4cc-vqyk/rows.csv | psql -h localhost -U postgres -d quickstart_db -c "\copy insurance_producers_licensed_in_iowa from stdin csv header"
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 39.3M    0 39.3M    0     0  1004k      0 --:--:--  0:00:40 --:--:-- 1028k
COPY 210898
otochkin@omni01:~$

Abbiamo caricato nel nostro database 210898 record relativi a produttori di assicurazioni e possiamo eseguire alcuni test.

Esegui query di test

Connettiti a quickstart_db utilizzando psql e abilita il monitoraggio del tempo per misurare il tempo di esecuzione delle query.

Nella VM GCE, esegui:

psql -h localhost -U postgres -d quickstart_db

Output della console previsto:

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db
psql (13.14 (Debian 13.14-0+deb11u1), server 15.5
WARNING: psql major version 13, server major version 15.
         Some psql features might not work.
Type "help" for help.

quickstart_db=#

Nella sessione PSQL, esegui:

\timing

Output della console previsto:

quickstart_db=# \timing
Timing is on.
quickstart_db=# 

Troviamo le 5 città principali in base al numero di produttori di assicurazioni che vendono polizze di assicurazione contro gli infortuni e sanitarie e la cui licenza è valida almeno per i prossimi 6 mesi.

Nella sessione PSQL, esegui:

SELECT city, count(*) 
FROM insurance_producers_licensed_in_iowa 
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH' 
GROUP BY city ORDER BY count(*) desc limit 5;

Output della console previsto:

quickstart_db=# SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
    city     | count
-------------+-------
 TAMPA       |  1885
 OMAHA       |  1656
 KANSAS CITY |  1279
 AUSTIN      |  1254
 MIAMI       |  1003
(5 rows)

Time: 94.965 ms

Esegui una query di test, preferibilmente più volte, per ottenere un tempo di esecuzione affidabile.Possiamo vedere che il tempo medio per restituire il risultato è di circa 94 ms. Nei passaggi successivi attiveremo il motore colonnare AlloyDB e vedremo se può migliorare le prestazioni.

Esci dalla sessione psql:

exit

6. Abilita motore colonnare

Ora dobbiamo attivare il motore colonnare su AlloyDB Omni.

Aggiornare i parametri di AlloyDB Omni

Dobbiamo impostare il parametro dell'istanza "google_columnar_engine.enabled" su "on" per il nostro AlloyDB Omni e richiede il riavvio.

Aggiorna postgresql.conf nella directory /var/alloydb/config e riavvia l'istanza.

Nella VM GCE, esegui:

sudo docker exec my-omni /bin/bash -c "echo google_columnar_engine.enabled=true >>/var/lib/postgresql/data/postgresql.conf"
sudo docker exec my-omni /bin/bash -c "echo shared_preload_libraries=\'google_columnar_engine,google_job_scheduler,google_db_advisor,google_storage\' >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni

Output della console previsto:

student@omni01:~$ sudo docker exec my-omni /bin/bash -c "echo google_columnar_engine.enabled=true >>/var/lib/postgresql/data/postgresql.conf"
sudo docker exec my-omni /bin/bash -c "echo shared_preload_libraries=\'google_columnar_engine,google_job_scheduler,google_db_advisor,google_storage\' >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni
my-omni
my-omni
student@omni01:~$

Verificare il motore colonnare

Connettiti al database utilizzando psql e verifica il motore colonnare.

Connettiti al database AlloyDB Omni

Nella sessione SSH della VM, connettiti al database:

psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.enabled"

Il comando dovrebbe mostrare il motore colonnare abilitato.

Output della console previsto:

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.enabled"
 google_columnar_engine.enabled 
--------------------------------
 on
(1 row)

7. Confronto rendimenti

Ora possiamo compilare il repository dell'engine colonnare e verificare il rendimento.

Popolazione automatica dei negozi in colonna

Per impostazione predefinita, il job che popola il negozio viene eseguito ogni ora. Per evitare attese, ridurremo questo tempo a 10 minuti.

Nella VM GCE, esegui:

sudo docker exec my-omni /bin/bash -c "echo google_columnar_engine.auto_columnarization_schedule=\'EVERY 10 MINUTES\' >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni

Ecco l'output previsto:

student@omni01:~$ sudo docker exec my-omni /bin/bash -c "echo google_columnar_engine.auto_columnarization_schedule=\'EVERY 5 MINUTES\' >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni
my-omni
my-omni
student@omni01:~$

Verifica le impostazioni

Nella VM GCE, esegui:

psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.auto_columnarization_schedule;"

Risultato previsto:

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.auto_columnarization_schedule;"
 google_columnar_engine.auto_columnarization_schedule 
------------------------------------------------------
 EVERY 10 MINUTES
(1 row)

student@omni01:~$ 

Controlla gli oggetti nel magazzino colonnare. Deve essere vuoto.

Nella VM GCE, esegui:

psql -h localhost -U postgres -d quickstart_db -c "SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;"

Risultato previsto:

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;"
 database_name | schema_name | relation_name | column_name 
---------------+-------------+---------------+-------------
(0 rows)

student@omni01:~$

Connettiti al database ed esegui più volte la stessa query eseguita in precedenza.

Nella VM GCE, esegui:

psql -h localhost -U postgres -d quickstart_db 

Nella sessione PSQL.

Attiva la temporizzazione

\timing

Esegui la query un paio di volte:

SELECT city, count(*) 
FROM insurance_producers_licensed_in_iowa 
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH' 
GROUP BY city ORDER BY count(*) desc limit 5;

Risultato previsto:

quickstart_db=# SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
    city     | count
-------------+-------
 TAMPA       |  1885
 OMAHA       |  1656
 KANSAS CITY |  1279
 AUSTIN      |  1254
 MIAMI       |  1003
(5 rows)

Time: 94.289 ms
quickstart_db=# SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
    city     | count
-------------+-------
 TAMPA       |  1885
 OMAHA       |  1656
 KANSAS CITY |  1279
 AUSTIN      |  1254
 MIAMI       |  1003
(5 rows)

Time: 94.608 ms
quickstart_db=#

Attendi 10 minuti e controlla se le colonne della tabella insurance_producers_licensed_in_iowa sono state compilate nell'archivio colonnare.

SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;

Risultato previsto:

quickstart_db=# SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;
 database_name | schema_name |            relation_name             | column_name
---------------+-------------+--------------------------------------+-------------
 quickstart_db | public      | insurance_producers_licensed_in_iowa | city
 quickstart_db | public      | insurance_producers_licensed_in_iowa | expirydate
 quickstart_db | public      | insurance_producers_licensed_in_iowa | loa_has_ah
(3 rows)

Time: 0.643 ms

Ora possiamo eseguire di nuovo la query per la tabella insurance_producers_licensed_in_iowa e verificare se il rendimento è migliorato.

SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;

Risultato previsto:

quickstart_db=# SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
    city     | count
-------------+-------
 TAMPA       |  1885
 OMAHA       |  1656
 KANSAS CITY |  1279
 AUSTIN      |  1254
 MIAMI       |  1003
(5 rows)

Time: 14.380 ms
quickstart_db=# SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
    city     | count
-------------+-------
 TAMPA       |  1885
 OMAHA       |  1656
 KANSAS CITY |  1279
 AUSTIN      |  1254
 MIAMI       |  1003
(5 rows)

Time: 13.279 ms

Il tempo di esecuzione è sceso da 94 ms a 14 ms. Se non noti alcun miglioramento, puoi controllare se le colonne sono state completate correttamente nello spazio dati colonnare controllando la visualizzazione g_columnar_columns.

SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;

Risultato previsto:

quickstart_db=# select relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
            relation_name             | column_name | column_type | status | size_in_bytes
--------------------------------------+-------------+-------------+--------+---------------
 insurance_producers_licensed_in_iowa | city        | text        | Usable |        664231
 insurance_producers_licensed_in_iowa | expirydate  | timestamp   | Usable |        212434
 insurance_producers_licensed_in_iowa | loa_has_ah  | text        | Usable |        211734
(3 rows)

Ora possiamo verificare se il piano di esecuzione della query utilizza il motore colonnare.

Nella sessione PSQL, esegui:

EXPLAIN (ANALYZE,SETTINGS,BUFFERS)
SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;

Risultato previsto:

quickstart_db=# EXPLAIN (ANALYZE,SETTINGS,BUFFERS)
SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
                                                                                      QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2279.72..2279.73 rows=5 width=17) (actual time=12.248..12.252 rows=5 loops=1)
   ->  Sort  (cost=2279.72..2292.91 rows=5277 width=17) (actual time=12.246..12.248 rows=5 loops=1)
         Sort Key: (count(*)) DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  HashAggregate  (cost=2139.30..2192.07 rows=5277 width=17) (actual time=10.235..11.250 rows=7555 loops=1)
               Group Key: city
               Batches: 1  Memory Usage: 1169kB
               ->  Append  (cost=20.00..1669.24 rows=94012 width=9) (actual time=10.231..10.233 rows=94286 loops=1)
                     ->  Custom Scan (columnar scan) on insurance_producers_licensed_in_iowa  (cost=20.00..1665.22 rows=94011 width=9) (actual time=10.229..10.231 rows=94286 loops=1)
                           Filter: ((loa_has_ah = 'Yes'::text) AND (expirydate > (now() + '6 mons'::interval)))
                           Rows Removed by Columnar Filter: 116612
                           Rows Aggregated by Columnar Scan: 94286
                           Columnar cache search mode: native
                     ->  Seq Scan on insurance_producers_licensed_in_iowa  (cost=0.00..4.02 rows=1 width=9) (never executed)
                           Filter: ((loa_has_ah = 'Yes'::text) AND (expirydate > (now() + '6 mons'::interval)))
 Planning Time: 0.216 ms
 Execution Time: 12.353 ms

Inoltre, possiamo vedere che l'operazione "Scansione sequenziale" sul segmento della tabella business_licenses non è mai stata eseguita ed è stata utilizzata invece la "Scansione personalizzata (scansione colonnare)". Ciò ci ha aiutato a migliorare il tempo di risposta da 94 a 12 ms.

Se vogliamo cancellare i contenuti compilati automaticamente dal motore colonnare, possiamo farlo utilizzando la funzione SQL google_columnar_engine_reset_recommendation.

Nella sessione PSQL, esegui:

SELECT google_columnar_engine_reset_recommendation(drop_columns => true);

Le colonne completate verranno cancellate e potrai verificarlo nelle visualizzazioni g_columnar_columns e g_columnar_recommended_columns, come mostrato in precedenza.

SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;
SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;

Risultato previsto:

quickstart_db=# SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;
 database_name | schema_name | relation_name | column_name
---------------+-------------+---------------+-------------
(0 rows)

Time: 0.447 ms
quickstart_db=# select relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
 relation_name | column_name | column_type | status | size_in_bytes
---------------+-------------+-------------+--------+---------------
(0 rows)

Time: 0.556 ms
quickstart_db=#

Popolazione dei negozi a colonne manuale

Possiamo aggiungere colonne al Columnar Engine Store manualmente utilizzando le funzioni SQL o specificare le entità richieste nei flag dell'istanza per caricarle automaticamente all'avvio dell'istanza.

Aggiungiamo le stesse colonne di prima utilizzando la funzione SQL google_columnar_engine_add.

Nella sessione PSQL, esegui:

SELECT google_columnar_engine_add(relation => 'insurance_producers_licensed_in_iowa', columns => 'city,expirydate,loa_has_ah');

Possiamo verificare il risultato utilizzando la stessa visualizzazione g_columnar_columns:

SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;

Risultato previsto:

quickstart_db=# SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
            relation_name             | column_name | column_type | status | size_in_bytes
--------------------------------------+-------------+-------------+--------+---------------
 insurance_producers_licensed_in_iowa | city        | text        | Usable |        664231
 insurance_producers_licensed_in_iowa | expirydate  | timestamp   | Usable |        212434
 insurance_producers_licensed_in_iowa | loa_has_ah  | text        | Usable |        211734
(3 rows)

Time: 0.692 ms
quickstart_db=#

Puoi verificare che lo spazio dati colonnare venga utilizzato eseguendo la stessa query di prima ed esaminando il piano di esecuzione:

EXPLAIN (ANALYZE,SETTINGS,BUFFERS)
SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;

Esci dalla sessione psql:

exit

Se riavviamo il contenitore AlloyDB Omni, possiamo vedere che tutte le informazioni sulle colonne vengono perse.

Nella sessione di shell, esegui:

sudo docker stop my-omni
sudo docker start my-omni

Attendi 5-10 secondi ed esegui:

psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"

Risultato previsto:

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"
 relation_name | column_name | column_type | status | size_in_bytes
---------------+-------------+-------------+--------+---------------
(0 rows)

Per reimpostare automaticamente le colonne durante il riavvio, possiamo aggiungerle come flag di database ai nostri parametri AlloyDB Omni. Aggiungiamo il flag google_columnar_engine.relations=‘quickstart_db.public.insurance_producers_licensed_in_iowa(city,expirydate,loa_has_ah)' e riavviamo il contenitore.

Nella sessione di shell, esegui:

sudo docker exec my-omni /bin/bash -c "echo google_columnar_engine.relations=\'quickstart_db.public.insurance_producers_licensed_in_iowa\(city,expirydate,loa_has_ah\)\' >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni

Dopodiché possiamo vedere che le colonne sono state aggiunte automaticamente al Columnar Store dopo l'avvio.

Attendi 5-10 secondi ed esegui:

psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"

Risultato previsto:

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"
            relation_name             | column_name | column_type | status | size_in_bytes
--------------------------------------+-------------+-------------+--------+---------------
 insurance_producers_licensed_in_iowa | city        | text        | Usable |        664231
 insurance_producers_licensed_in_iowa | expirydate  | timestamp   | Usable |        212434
 insurance_producers_licensed_in_iowa | loa_has_ah  | text        | Usable |        211734
(3 rows)

8. Ripulire l'ambiente

Ora possiamo eliminare la VM AlloyDB Omni

Eliminare la VM GCE

In Cloud Shell, esegui:

export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
    --zone=$ZONE \
    --quiet

Output della console previsto:

student@cloudshell:~ (test-project-001-402417)$ export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
    --zone=$ZONE \
    --quiet
Deleted 

9. Complimenti

Complimenti per aver completato il codelab.

Argomenti trattati

  • Come eseguire il deployment di AlloyDB Omni su VM GCE in Google Cloud
  • Come connettersi ad AlloyDB Omni
  • Come caricare i dati in AlloyDB Omni
  • Come attivare il motore colonnare
  • Come controllare il motore colonnare in modalità automatica
  • Come compilare manualmente il Datastore a colonne

Per scoprire di più sull'utilizzo del motore colonnare, consulta la documentazione.

10. Sondaggio

Output:

Come utilizzerai questo tutorial?

Leggi solo Leggi e completa gli esercizi