1. Introduzione
In questo codelab imparerai a eseguire il deployment di AlloyDB Omni e a utilizzare il motore colonnare per migliorare il rendimento delle query.
Prerequisiti
- Una conoscenza di base di Google Cloud Console
- 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 una VM GCE in Google Cloud
- Come connettersi ad AlloyDB Omni
- Come caricare dati in AlloyDB Omni
- Come attivare il motore colonnare
- Come controllare il motore colonnare in modalità automatica
- Come compilare manualmente Columnar Store
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 autonomo
- 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.
- Il nome del progetto è il nome visualizzato per i partecipanti a questo progetto. È una stringa di caratteri non utilizzata dalle API di Google. Puoi sempre aggiornarlo.
- L'ID progetto è univoco in tutti i progetti Google Cloud ed è immutabile (non può essere modificato dopo l'impostazione). La console Cloud genera automaticamente una stringa univoca, di solito non ti interessa di cosa si tratta. Nella maggior parte dei codelab, dovrai fare riferimento all'ID progetto (in genere identificato come
PROJECT_ID). Se l'ID generato non ti piace, puoi generarne un altro casuale. In alternativa, puoi provare a crearne uno e vedere se è disponibile. Non può essere modificato dopo questo passaggio e rimane per tutta la durata del progetto. - Per tua informazione, esiste un terzo valore, un numero di progetto, utilizzato da alcune API. Scopri di più su tutti e tre questi valori nella documentazione.
- Successivamente, devi abilitare la fatturazione in Cloud Console per utilizzare le risorse/API Cloud. Completare questo codelab non costa molto, se non nulla. Per arrestare le risorse ed evitare addebiti oltre a quelli previsti in questo tutorial, puoi eliminare le risorse che hai creato o il progetto. I nuovi utenti di Google Cloud possono beneficiare del programma prova senza costi di 300$.
Avvia Cloud Shell
Sebbene Google Cloud possa essere gestito 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:
Bastano pochi istanti per eseguire il provisioning e connettersi all'ambiente. Al termine, dovresti vedere un risultato simile a questo:
Questa macchina virtuale è caricata con tutti gli strumenti per sviluppatori 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 della rete. Tutto il lavoro in questo codelab può essere svolto all'interno di un browser. Non devi installare nulla.
3. Prima di iniziare
Abilita 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 nel set di configurazione di Cloud Shell, configurarlo 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
Output 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 di database AlloyDB Omni.
Possiamo utilizzare Cloud Shell avviata 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 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 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 questo comando nel terminale connesso.
Installa Docker sulla VM:
sudo apt update
sudo apt-get -y install docker.io
Output della console previsto(modificato):
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 eliminato quando il container Docker viene eliminato. Se lo mantieni separato, puoi gestire i container indipendentemente dai dati e, se vuoi, puoi posizionarlo in uno spazio di archiviazione con caratteristiche I/O migliori.
Ecco un comando che crea una directory nella directory home dell'utente in cui verranno inseriti tutti i dati:
mkdir -p $HOME/alloydb-data
Esegui il deployment del container 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(modificato):
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 sulla VM (facoltativo, è previsto che sia già installato):
sudo apt install -y postgresql-client
Output 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 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 console previsto:
postgres=# exit gleb@omni01:~$
5. Preparare un database di test
Per testare Columnar Engine, dobbiamo creare un database e riempirlo con alcuni dati di test.
Crea database
Connettiti alla VM AlloyDB Omni e crea un database
Nella sessione di 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 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 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 sui produttori di assicurazioni autorizzati in Iowa. Puoi trovare questo set di dati sul sito web del governo dell'Iowa: https://data.iowa.gov/Regulation/Insurance-Producers-Licensed-in-Iowa/n4cc-vqyk/about_data .
Innanzitutto, 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 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:~$
Caricare 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 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 210.898 record relativi ai produttori di assicurazioni nel nostro database e possiamo eseguire alcuni test.
Esegui query di test
Connettiti a quickstart_db utilizzando psql e attiva la misurazione del tempo per misurare il tempo di esecuzione delle query.
Nella VM GCE esegui:
psql -h localhost -U postgres -d quickstart_db
Output 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 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 assicurazioni contro malattie e infortuni 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 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 di AlloyDB e vedremo se può migliorare le prestazioni.
Esci dalla sessione psql:
exit
6. Abilita motore colonnare
Ora dobbiamo abilitare 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 AlloyDB Omni e richiede il riavvio.
Aggiorna il file 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 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:~$
Verifica 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 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 l'archivio del motore colonnare e verificare il rendimento.
Popolazione automatica dell'archivio colonnare
Per impostazione predefinita, il job che popola il negozio viene eseguito ogni ora. Ridurremo questo tempo a 10 minuti per evitare attese.
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;"
Output 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 nello spazio di archiviazione 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;"
Output 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 che abbiamo eseguito in precedenza.
Nella VM GCE esegui:
psql -h localhost -U postgres -d quickstart_db
Nella sessione PSQL.
Attiva la sincronizzazione
\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;
Output 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 inserite nello store colonnare.
SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;
Output 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;
Output 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 miglioramenti, puoi verificare se le colonne sono state compilate correttamente nell'archivio colonnare controllando la visualizzazione g_columnar_columns.
SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
Output 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;
Output 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
Possiamo notare che l'operazione "Seq Scan" sul segmento della tabella business_licenses non è mai stata eseguita ed è stato utilizzato invece "Custom Scan (columnar scan)". 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);
Verranno cancellate le colonne compilate 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;
Output 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=#
Inserimento manuale di dati nel negozio colonnare
Possiamo aggiungere colonne all'archivio Columnar Engine 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;
Output 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 di archiviazione 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 container AlloyDB Omni, possiamo vedere che tutte le informazioni sulle colonne sono andate perse.
Nella sessione della 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"
Output 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 ripopolare automaticamente le colonne durante il riavvio, possiamo aggiungerle come flag di database ai parametri di 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 container.
Nella sessione della 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 allo spazio di archiviazione colonnare 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"
Output 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. Pulizia dell'ambiente
Ora possiamo eliminare la nostra VM AlloyDB Omni
Elimina VM GCE
In Cloud Shell, esegui:
export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
--zone=$ZONE \
--quiet
Output 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
Congratulazioni per aver completato il codelab.
Argomenti trattati
- Come eseguire il deployment di AlloyDB Omni su una VM GCE in Google Cloud
- Come connettersi ad AlloyDB Omni
- Come caricare dati in AlloyDB Omni
- Come attivare il motore colonnare
- Come controllare il motore colonnare in modalità automatica
- Come compilare manualmente Columnar Store
Per saperne di più sull'utilizzo di Columnar Engine, consulta la documentazione.
10. Sondaggio
Output: