1. Introdução
Neste codelab, você vai aprender a implantar o AlloyDB Omni e usar o mecanismo colunar para melhorar a performance das consultas.
Pré-requisitos
- Conhecimentos básicos sobre o console do Google Cloud
- Habilidades básicas na interface de linha de comando e no Google Shell
O que você vai aprender
- Como implantar o AlloyDB Omni em uma VM do GCE no Google Cloud
- Como se conectar ao AlloyDB Omni
- Como carregar dados para o AlloyDB Omni
- Como ativar o mecanismo colunar
- Como verificar o mecanismo colunar no modo automático
- Como preencher a loja de dados em colunas manualmente
O que é necessário
- Uma conta e um projeto do Google Cloud
- Um navegador da Web, como o Chrome
2. Configuração e requisitos
Configuração de ambiente autoguiada
- Faça login no Console do Google Cloud e crie um novo projeto ou reutilize um existente. Crie uma conta do Gmail ou do Google Workspace, se ainda não tiver uma.
- O Nome do projeto é o nome de exibição para os participantes do projeto. É uma string de caracteres não usada pelas APIs do Google e pode ser atualizada quando você quiser.
- O ID do projeto precisa ser exclusivo em todos os projetos do Google Cloud e não pode ser mudado após a definição. O console do Cloud gera automaticamente uma string exclusiva. Em geral, não importa o que seja. Na maioria dos codelabs, é necessário fazer referência ao ID do projeto, normalmente identificado como
PROJECT_ID. Se você não gostar do ID gerado, crie outro aleatório. Se preferir, teste o seu e confira se ele está disponível. Ele não pode ser mudado após essa etapa e permanece durante o projeto. - Para sua informação, há um terceiro valor, um Número do projeto, que algumas APIs usam. Saiba mais sobre esses três valores na documentação.
- Em seguida, ative o faturamento no console do Cloud para usar os recursos/APIs do Cloud. A execução deste codelab não vai ser muito cara, se tiver algum custo. Para encerrar os recursos e evitar cobranças além deste tutorial, exclua os recursos criados ou exclua o projeto. Novos usuários do Google Cloud estão qualificados para o programa de US$ 300 de avaliação sem custos.
Inicie o Cloud Shell
Embora o Google Cloud e o Spanner possam ser operados remotamente do seu laptop, neste codelab usaremos o Google Cloud Shell, um ambiente de linha de comando executado no Cloud.
No Console do Google Cloud, clique no ícone do Cloud Shell na barra de ferramentas superior à direita:
O provisionamento e a conexão com o ambiente levarão apenas alguns instantes para serem concluídos: Quando o processamento for concluído, você verá algo como:
Essa máquina virtual contém todas as ferramentas de desenvolvimento necessárias. Ela oferece um diretório principal persistente de 5 GB, além de ser executada no Google Cloud. Isso aprimora o desempenho e a autenticação da rede. Neste codelab, todo o trabalho pode ser feito com um navegador. Você não precisa instalar nada.
3. Antes de começar
Ativar API
Saída:
No Cloud Shell, verifique se o ID do projeto está configurado:
PROJECT_ID=$(gcloud config get-value project)
echo $PROJECT_ID
Se não estiver definido na configuração do Cloud Shell, configure-o usando os comandos a seguir.
export PROJECT_ID=<your project>
gcloud config set project $PROJECT_ID
Ative todos os serviços necessários:
gcloud services enable compute.googleapis.com
Resultado esperado
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. Implantar o AlloyDB Omni no GCE
Para implantar o AlloyDB Omni no GCE, precisamos preparar uma máquina virtual com configuração e software compatíveis. Confira um exemplo de como implantar o AlloyDB Omni em uma VM baseada no Debian.
Criar uma VM do GCE
Precisamos implantar uma VM com uma configuração aceitável para CPU, memória e armazenamento. Vamos usar a imagem padrão do Debian com o tamanho do disco do sistema aumentado para 20 GB para acomodar os arquivos de banco de dados do AlloyDB Omni.
Podemos usar o Cloud Shell iniciado ou um terminal com o SDK do Cloud instalado.
Todas as etapas também estão descritas no guia de início rápido do AlloyDB Omni.
Configure as variáveis de ambiente para a implantação.
export ZONE=us-central1-a
export MACHINE_TYPE=n2-highmem-2
export DISK_SIZE=20
export MACHINE_NAME=omni01
Em seguida, usamos o gcloud para criar a VM do 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
Saída esperada do console:
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)$
Instalar o AlloyDB Omni
Conecte-se à VM criada:
gcloud compute ssh omni01 --zone $ZONE
Saída esperada do console:
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:~$
Execute o comando a seguir no terminal conectado.
Instale o Docker na VM:
sudo apt update
sudo apt-get -y install docker.io
Saída esperada do console (editada):
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:~$
Defina a senha do usuário postgres:
export PGPASSWORD=<your password>
Crie um diretório para os dados do AlloyDB Omni. É uma abordagem opcional, mas recomendada. Por padrão, os dados seriam criados usando a camada do sistema de arquivos temporário do Docker e tudo seria destruído quando o contêiner do Docker fosse excluído. Ao manter os dados separados, você pode gerenciar os contêineres de forma independente dos dados e, se quiser, colocá-los em um armazenamento com melhores características de E/S.
Este é um comando que cria um diretório no diretório inicial do usuário, onde todos os dados serão colocados:
mkdir -p $HOME/alloydb-data
Implantar o contêiner do 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
Saída esperada do console (editada):
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:~$
Instale o software do cliente PostgreSQL na VM (opcional, deve estar instalado):
sudo apt install -y postgresql-client
Saída esperada do console:
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.
Conectar-se ao AlloyDB Omni:
psql -h localhost -U postgres
Saída esperada do console:
gleb@omni01:~$ psql -h localhost -U postgres psql (15.6 (Debian 15.6-0+deb12u1), server 15.5) Type "help" for help. postgres=#
Desconecte do AlloyDB Omni:
exit
Saída esperada do console:
postgres=# exit gleb@omni01:~$
5. Preparar um banco de dados de teste
Para testar o Columnar Engine, precisamos criar um banco de dados e preenchê-lo com alguns dados de teste.
Criar banco de dados
Conectar-se à VM do AlloyDB Omni e criar um banco de dados
Na sessão do Cloud Shell, execute:
gcloud config set project $(gcloud config get-value project)
Conecte-se à VM do AlloyDB Omni:
ZONE=us-central1-a
gcloud compute ssh omni01 --zone $ZONE
Saída esperada do console:
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:~$
Na sessão SSH estabelecida, execute:
export PGPASSWORD=<your password>
psql -h localhost -U postgres -c "CREATE DATABASE quickstart_db"
Saída esperada do console:
student@omni01:~$ psql -h localhost -U postgres -c "CREATE DATABASE quickstart_db" CREATE DATABASE student@omni01:~$
Criar uma tabela com dados de amostra
Para nossos testes, vamos usar dados públicos sobre produtores de seguros licenciados em Iowa. Esse conjunto de dados está disponível no site do governo de Iowa: https://data.iowa.gov/Regulation/Insurance-Producers-Licensed-in-Iowa/n4cc-vqyk/about_data .
Primeiro, precisamos criar uma tabela.
Na VM do GCE, execute:
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
);"
Saída esperada do console:
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:~$
Carregar dados na tabela.
Na VM do GCE, execute:
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"
Saída esperada do console:
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:~$
Carregamos 210898 registros sobre produtores de seguros no nosso banco de dados e podemos fazer alguns testes.
Executar consultas de teste
Conecte-se ao quickstart_db usando o psql e ative o tempo para medir o tempo de execução das consultas.
Na VM do GCE, execute:
psql -h localhost -U postgres -d quickstart_db
Saída esperada do console:
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=#
Na sessão PSQL, execute:
\timing
Saída esperada do console:
quickstart_db=# \timing Timing is on. quickstart_db=#
Vamos encontrar as cinco cidades com mais produtores de seguros que vendem seguros de acidentes e de saúde e cuja licença é válida pelo menos nos próximos seis meses.
Na sessão PSQL, execute:
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;
Saída esperada do console:
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
Execute uma consulta de teste várias vezes para ter um tempo de execução confiável.O tempo médio para retornar o resultado é de cerca de 94 ms. Nas próximas etapas, vamos ativar o mecanismo colunar do AlloyDB e verificar se ele pode melhorar o desempenho.
Saia da sessão do psql:
exit
6. Ativar o mecanismo colunar
Agora precisamos ativar o mecanismo colunar no AlloyDB Omni.
Atualizar parâmetros do AlloyDB Omni
Precisamos mudar o parâmetro de instância "google_columnar_engine.enabled" para "on" no AlloyDB Omni, e isso requer uma reinicialização.
Atualize o postgresql.conf no diretório /var/alloydb/config e reinicie a instância.
Na VM do GCE, execute:
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
Saída esperada do console:
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:~$
Verificar o mecanismo colunar
Conecte-se ao banco de dados usando o psql e verifique o mecanismo de colunas.
Conectar-se ao banco de dados do AlloyDB Omni
Na sessão SSH da VM, conecte-se ao banco de dados:
psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.enabled"
O comando vai mostrar o mecanismo de colunas ativado.
Saída esperada do console:
student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.enabled" google_columnar_engine.enabled -------------------------------- on (1 row)
7. Comparação de desempenho
Agora podemos preencher a loja do mecanismo colunar e verificar o desempenho.
População automática de lojas com colunas
Por padrão, o job que preenche a loja é executado a cada hora. Vamos reduzir esse tempo para 10 minutos para evitar espera.
Na VM do GCE, execute:
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
E esta é a saída esperada:
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:~$
Verificar as configurações
Na VM do GCE, execute:
psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.auto_columnarization_schedule;"
Saída esperada:
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:~$
Verifique os objetos na loja de colunas. Esse campo precisa estar vazio.
Na VM do GCE, execute:
psql -h localhost -U postgres -d quickstart_db -c "SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;"
Saída esperada:
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:~$
Conecte-se ao banco de dados e execute a mesma consulta que executamos várias vezes.
Na VM do GCE, execute:
psql -h localhost -U postgres -d quickstart_db
Na sessão PSQL.
Ativar o monitoramento de duração
\timing
Execute a consulta algumas vezes:
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;
Saída esperada:
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=#
Aguarde 10 minutos e verifique se as colunas da tabela insurance_producers_licensed_in_iowa foram preenchidas na loja de colunas.
SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;
Saída esperada:
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
Agora podemos executar a consulta para a tabela insurance_producers_licensed_in_iowa novamente e conferir se a performance melhorou.
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;
Saída esperada:
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
O tempo de execução caiu de 94 ms para 14 ms. Se você não notar melhorias, verifique se as colunas foram preenchidas na loja de colunas. Para isso, verifique a visualização g_columnar_columns.
SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
Saída esperada:
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)
Agora podemos verificar se o plano de execução da consulta usa o mecanismo de colunas.
Na sessão PSQL, execute:
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;
Saída esperada:
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
E podemos ver que a operação "Seq Scan" no segmento da tabela business_licenses nunca foi executada e que a "Custom Scan (columnar scan)" foi usada. Isso nos ajudou a melhorar o tempo de resposta de 94 para 12 ms.
Se quisermos limpar o conteúdo preenchido automaticamente do motor de colunas, podemos usar a função SQL google_columnar_engine_reset_recommendation.
Na sessão PSQL, execute:
SELECT google_columnar_engine_reset_recommendation(drop_columns => true);
Isso vai limpar as colunas preenchidas, e você pode verificar isso nas visualizações g_columnar_columns e g_columnar_recommended_columns, conforme mostrado anteriormente.
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;
Saída esperada:
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=#
População manual de lojas em colunas
É possível adicionar colunas à loja de mecanismo de colunas manualmente usando funções SQL ou especificar as entidades necessárias nas flags de instância para carregá-las automaticamente quando a instância for iniciada.
Vamos adicionar as mesmas colunas de antes usando a função SQL google_columnar_engine_add.
Na sessão PSQL, execute:
SELECT google_columnar_engine_add(relation => 'insurance_producers_licensed_in_iowa', columns => 'city,expirydate,loa_has_ah');
E podemos verificar o resultado usando a mesma visualização g_columnar_columns:
SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
Saída esperada:
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=#
Para verificar se o armazenamento de colunas é usado, execute a mesma consulta e examine o plano de execução:
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;
Saia da sessão do psql:
exit
Se reiniciarmos o contêiner do AlloyDB Omni, vamos notar que todas as informações das colunas são perdidas.
Na sessão do shell, execute:
sudo docker stop my-omni
sudo docker start my-omni
Aguarde de 5 a 10 segundos e execute:
psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"
Saída esperada:
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)
Para repopular automaticamente as colunas durante a reinicialização, podemos adicioná-las como sinalizações de banco de dados aos nossos parâmetros do AlloyDB Omni. Estamos adicionando a flag google_columnar_engine.relations=‘quickstart_db.public.insurance_producers_licensed_in_iowa(city,expirydate,loa_has_ah)' e reiniciando o contêiner.
Na sessão do shell, execute:
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
Depois disso, podemos ver que as colunas foram adicionadas à loja de colunas automaticamente após a inicialização.
Aguarde de 5 a 10 segundos e execute:
psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"
Saída esperada:
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. Limpar o ambiente
Agora você pode destruir a VM do AlloyDB Omni.
Excluir a VM do GCE
No Cloud Shell, execute:
export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
--zone=$ZONE \
--quiet
Saída esperada do console:
student@cloudshell:~ (test-project-001-402417)$ export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
--zone=$ZONE \
--quiet
Deleted
9. Parabéns
Parabéns por concluir o codelab.
O que vimos
- Como implantar o AlloyDB Omni em uma VM do GCE no Google Cloud
- Como se conectar ao AlloyDB Omni
- Como carregar dados para o AlloyDB Omni
- Como ativar o mecanismo colunar
- Como verificar o mecanismo colunar no modo automático
- Como preencher a loja de dados em colunas manualmente
Leia mais sobre como trabalhar com o Motor de colunas na documentação.
10. Pesquisa
Saída: