1. Wprowadzenie
Z tego Codelab dowiesz się, jak wdrożyć AlloyDB Omni i używać mechanizmu kolumnowego, aby zwiększyć wydajność zapytań.
Wymagania wstępne
- podstawowa znajomość konsoli Google Cloud;
- podstawowe umiejętności w zakresie interfejsu wiersza poleceń i Google Shell;
Czego się nauczysz
- Jak wdrożyć AlloyDB Omni na maszynie wirtualnej GCE w Google Cloud
- Jak połączyć się z AlloyDB Omni
- Jak wczytywać dane do AlloyDB Omni
- Jak włączyć silnik kolumnowy
- Jak sprawdzić działanie silnika kolumnowego w trybie automatycznym
- Jak ręcznie wypełnić kolumnowy magazyn danych
Czego potrzebujesz
- Konto Google Cloud i projekt Google Cloud
- przeglądarka internetowa, np. Chrome;
2. Konfiguracja i wymagania
Konfiguracja środowiska w samodzielnym tempie
- Zaloguj się w konsoli Google Cloud i utwórz nowy projekt lub użyj istniejącego. Jeśli nie masz jeszcze konta Gmail ani Google Workspace, musisz je utworzyć.
- Nazwa projektu to wyświetlana nazwa uczestników tego projektu. Jest to ciąg znaków, którego nie używają interfejsy API Google. Zawsze możesz ją zaktualizować.
- Identyfikator projektu jest niepowtarzalny w ramach wszystkich projektów Google Cloud i nie można go zmienić (po ustawieniu). Konsola Cloud automatycznie generuje unikalny ciąg znaków. Zwykle nie ma znaczenia, jaki to ciąg. W większości laboratoriów z kodem musisz podać identyfikator projektu (zwykle oznaczony jako
PROJECT_ID). Jeśli nie podoba Ci się wygenerowany identyfikator, możesz wygenerować inny losowy. Możesz też spróbować użyć własnego adresu e-mail, aby sprawdzić, czy jest on dostępny. Nie można go zmienić po wykonaniu tego kroku. Pozostanie on do końca projektu. - Informacyjnie: istnieje jeszcze 3 wartość, numer projektu, której używają niektóre interfejsy API. Więcej informacji o wszystkich 3 wartościach znajdziesz w dokumentacji.
- Następnie musisz włączyć rozliczenia w konsoli Cloud, aby korzystać z zasobów i interfejsów API Cloud. Przejście przez ten samouczek nie będzie kosztowne, a być może nawet bezpłatne. Aby wyłączyć zasoby i uniknąć obciążenia opłatami po zakończeniu samouczka, możesz usunąć utworzone zasoby lub usunąć projekt. Nowi użytkownicy Google Cloud mogą skorzystać z bezpłatnego okresu próbnego, w którym mają do dyspozycji środki w wysokości 300 USD.
Uruchamianie Cloud Shell
Google Cloud można obsługiwać zdalnie z laptopa, ale w tym ćwiczeniu będziesz używać Google Cloud Shell, czyli środowiska wiersza poleceń działającego w chmurze.
W konsoli Google Cloud kliknij ikonę Cloud Shell na pasku narzędzi w prawym górnym rogu:
Uzyskanie dostępu do środowiska i połączenie się z nim powinno zająć tylko kilka chwil. Po jego zakończeniu powinno wyświetlić się coś takiego:
Ta maszyna wirtualna zawiera wszystkie potrzebne narzędzia dla programistów. Zawiera stały katalog domowy o pojemności 5 GB i działa w Google Cloud, co znacznie poprawia wydajność sieci i uwierzytelnianie. Wszystkie zadania w tym CodeLab możesz wykonać w przeglądarce. Nie musisz niczego instalować.
3. Zanim zaczniesz
Włącz interfejs API
Dane wyjściowe:
W Cloud Shell sprawdź, czy identyfikator projektu jest skonfigurowany:
PROJECT_ID=$(gcloud config get-value project)
echo $PROJECT_ID
Jeśli nie jest zdefiniowany w konfiguracji Cloud Shell, skonfiguruj go za pomocą tych poleceń:
export PROJECT_ID=<your project>
gcloud config set project $PROJECT_ID
Włącz wszystkie niezbędne usługi:
gcloud services enable compute.googleapis.com
Oczekiwany wynik
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. Wdrażanie AlloyDB Omni w GCE
Aby wdrożyć AlloyDB Omni w GCE, musimy przygotować maszynę wirtualną z kompatybilną konfiguracją i oprogramowaniem. Oto przykład wdrożenia AlloyDB Omni na maszynie wirtualnej opartej na Debianie.
Tworzenie maszyny wirtualnej GCE
Musimy wdrożyć maszynę wirtualną z akceptowalną konfiguracją procesora, pamięci i miejsca na dane. Użyjemy domyślnego obrazu Debiana z rozmiarem dysku systemowego zwiększonym do 20 GB, aby pomieścić pliki bazy danych AlloyDB Omni.
Możemy użyć uruchomionego Cloud Shell lub terminala z zainstalowanym pakietem SDK Cloud.
Wszystkie kroki są też opisane w krótkim przewodniku AlloyDB Omni.
Skonfiguruj zmienne środowiskowe dla wdrożenia.
export ZONE=us-central1-a
export MACHINE_TYPE=n2-highmem-2
export DISK_SIZE=20
export MACHINE_NAME=omni01
Następnie użyjemy gcloud do utworzenia maszyny wirtualnej 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
Oczekiwane dane wyjściowe konsoli:
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)$
Instalowanie AlloyDB Omni
Połącz się z utworzoną maszyną wirtualną:
gcloud compute ssh omni01 --zone $ZONE
Oczekiwane dane wyjściowe konsoli:
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:~$
Uruchom to polecenie w połączonym terminalu.
Zainstaluj Dockera w maszynie wirtualnej:
sudo apt update
sudo apt-get -y install docker.io
Oczekiwane dane wyjściowe konsoli(ocenzurowane):
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:~$
Zdefiniuj hasło dla użytkownika postgres:
export PGPASSWORD=<your password>
Utwórz katalog danych AlloyDB Omni. Jest to opcjonalne, ale zalecane podejście. Domyślnie dane są tworzone za pomocą warstwy tymczasowego systemu plików Dockera, a wszystko jest usuwane po usunięciu kontenera Dockera. Dzięki temu możesz zarządzać kontenerami niezależnie od danych i opcjonalnie umieszczać je w pamięci o lepszych parametrach we/wy.
Oto polecenie tworzące katalog w katalogu domowym użytkownika, w którym będą przechowywane wszystkie dane:
mkdir -p $HOME/alloydb-data
Wdróż kontener 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
Oczekiwane dane wyjściowe konsoli(ocenzurowane):
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:~$
Zainstaluj oprogramowanie klienta PostgreSQL w maszynie wirtualnej (opcjonalnie – powinno być już zainstalowane):
sudo apt install -y postgresql-client
Oczekiwane dane wyjściowe konsoli:
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.
Połącz się z AlloyDB Omni:
psql -h localhost -U postgres
Oczekiwane dane wyjściowe konsoli:
gleb@omni01:~$ psql -h localhost -U postgres psql (15.6 (Debian 15.6-0+deb12u1), server 15.5) Type "help" for help. postgres=#
Odłącz się od AlloyDB Omni:
exit
Oczekiwane dane wyjściowe konsoli:
postgres=# exit gleb@omni01:~$
5. Przygotowanie testowej bazy danych
Aby przetestować silnik kolumnowy, musimy utworzyć bazę danych i wypełnić ją danymi testowymi.
Tworzenie bazy danych
Łączenie z maszyną wirtualną AlloyDB Omni i tworzenie bazy danych
W sesji Cloud Shell wykonaj te czynności:
gcloud config set project $(gcloud config get-value project)
Nawiązywanie połączenia z maszyną wirtualną AlloyDB Omni:
ZONE=us-central1-a
gcloud compute ssh omni01 --zone $ZONE
Oczekiwane dane wyjściowe konsoli:
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:~$
W nawiązanej sesji SSH wykonaj te czynności:
export PGPASSWORD=<your password>
psql -h localhost -U postgres -c "CREATE DATABASE quickstart_db"
Oczekiwane dane wyjściowe konsoli:
student@omni01:~$ psql -h localhost -U postgres -c "CREATE DATABASE quickstart_db" CREATE DATABASE student@omni01:~$
Tworzenie tabeli z przykładowymi danymi
Do testów użyjemy publicznych danych o producentach ubezpieczeniowych z licencją w stanie Iowa. Ten zbiór danych można znaleźć na stronie internetowej rządu stanu Iowa: https://data.iowa.gov/Regulation/Insurance-Producers-Licensed-in-Iowa/n4cc-vqyk/about_data .
Najpierw musimy utworzyć tabelę.
W maszynie wirtualnej GCE wykonaj te czynności:
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
);"
Oczekiwane dane wyjściowe konsoli:
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:~$
wczytać dane do tabeli,
W maszynie wirtualnej GCE wykonaj te czynności:
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"
Oczekiwane dane wyjściowe konsoli:
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:~$
W naszej bazie danych załadowaliśmy 210898 rekordów dotyczących producentów ubezpieczeń i możemy przeprowadzić testy.
Wykonywanie testowych zapytań
Nawiązujemy połączenie z bazą danych quickstart_db za pomocą psql i włączamy pomiar czasu, aby mierzyć czas wykonywania zapytań.
W maszynie wirtualnej GCE wykonaj te czynności:
psql -h localhost -U postgres -d quickstart_db
Oczekiwane dane wyjściowe konsoli:
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=#
W sesji PSQL wykonaj:
\timing
Oczekiwane dane wyjściowe konsoli:
quickstart_db=# \timing Timing is on. quickstart_db=#
Znajdź 5 największych miast pod względem liczby producentów ubezpieczeń sprzedających ubezpieczenia od wypadków i ubezpieczenia zdrowotne, których licencja jest ważna przez co najmniej 6 miesięcy.
W sesji PSQL wykonaj:
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;
Oczekiwane dane wyjściowe konsoli:
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
Wykonaj testowe zapytanie kilka razy, aby uzyskać wiarygodny czas wykonania.Widzimy, że średni czas oczekiwania na wynik wynosi około 94 ms. W następnych krokach włączymy silnik kolumnowy AlloyDB i sprawdzimy, czy może on poprawić wydajność.
Zakończ sesję psql:
exit
6. Włącz silnik kolumnowy
Teraz musimy włączyć silnik kolumnowy w AlloyDB Omni.
Aktualizowanie parametrów AlloyDB Omni
Musimy przełączyć parametr instancji „google_columnar_engine.enabled” na „on” (włączony) w AlloyDB Omni. Wymaga to ponownego uruchomienia.
Zaktualizuj plik postgresql.conf w katalogu /var/alloydb/config i ponownie uruchom instancję.
W maszynie wirtualnej GCE wykonaj te czynności:
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
Oczekiwane dane wyjściowe konsoli:
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:~$
Weryfikowanie silnika kolumnowego
Połącz się z bazą danych za pomocą psql i sprawdź mechanizm kolumnowy.
Łączenie z bazą danych AlloyDB Omni
W sesji SSH na maszynie wirtualnej połącz się z bazą danych:
psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.enabled"
Polecenie powinno wyświetlić włączony silnik kolumnowy.
Oczekiwane dane wyjściowe konsoli:
student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.enabled" google_columnar_engine.enabled -------------------------------- on (1 row)
7. Porównanie skuteczności
Teraz możemy wypełnić kolumnowy magazyn i sprawdzać wydajność.
Automatyczne wypełnianie zbioru kolumnowego
Domyślnie zadanie wypełniania sklepu jest uruchamiane co godzinę. Aby uniknąć oczekiwania, skrócimy ten czas do 10 minut.
W maszynie wirtualnej GCE wykonaj te czynności:
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
A tak powinny wyglądać oczekiwane dane wyjściowe:
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:~$
Sprawdzanie ustawień
W maszynie wirtualnej GCE wykonaj te czynności:
psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.auto_columnarization_schedule;"
Oczekiwany wynik:
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:~$
Sprawdź obiekty w tabeli słupkowej. Powinien być pusty.
W maszynie wirtualnej GCE wykonaj te czynności:
psql -h localhost -U postgres -d quickstart_db -c "SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;"
Oczekiwany wynik:
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:~$
Połącz się z bazą danych i wykonaj to samo zapytanie, które zostało już kilkakrotnie wykonane wcześniej.
W maszynie wirtualnej GCE wykonaj te czynności:
psql -h localhost -U postgres -d quickstart_db
W sesji PSQL.
Włączanie ustawień czasu
\timing
Wykonaj zapytanie kilka razy:
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;
Oczekiwany wynik:
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=#
Odczekaj 10 minut i sprawdź, czy kolumny tabeli insurance_producers_licensed_in_iowa zostały wypełnione w magazynie kolumnowym.
SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;
Oczekiwany wynik:
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
Teraz możemy ponownie uruchomić zapytanie dotyczące tabeli insurance_producers_licensed_in_iowa i sprawdzić, czy wydajność się poprawiła.
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;
Oczekiwany wynik:
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
Czas wykonania spadł z 94 ms do 14 ms. Jeśli nie widzisz żadnych ulepszeń, możesz sprawdzić, czy kolumny zostały wypełnione w kolumnarnym magazynie danych, korzystając z widoku g_columnar_columns.
SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
Oczekiwany wynik:
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)
Teraz możesz sprawdzić, czy plan wykonywania zapytania korzysta z silnika kolumnowego.
W sesji PSQL wykonaj:
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;
Oczekiwany wynik:
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
Widać też, że operacja „Seq Scan” na segmencie tabeli business_licenses nigdy nie została wykonana, a zamiast niej użyto „Custom Scan (columnar scan)”. Dzięki temu udało nam się skrócić czas odpowiedzi z 94 do 12 ms.
Jeśli chcemy wyczyścić automatycznie wypełnione dane z kolumnarnego silnika, możemy to zrobić za pomocą funkcji SQL google_columnar_engine_reset_recommendation.
W sesji PSQL wykonaj:
SELECT google_columnar_engine_reset_recommendation(drop_columns => true);
Spowoduje to wyczyszczenie wypełnionych kolumn. Możesz to sprawdzić w widokach g_columnar_columns i g_columnar_recommended_columns, jak pokazano wcześniej.
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;
Oczekiwany wynik:
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=#
Ręczne wypełnianie kolumnowego zbioru danych
Możemy dodawać kolumny do magazynu kolumnarnego ręcznie za pomocą funkcji SQL lub określić wymagane elementy w flagach instancji, aby wczytywać je automatycznie po uruchomieniu instancji.
Dodamy te same kolumny za pomocą funkcji SQL google_columnar_engine_add.
W sesji PSQL wykonaj:
SELECT google_columnar_engine_add(relation => 'insurance_producers_licensed_in_iowa', columns => 'city,expirydate,loa_has_ah');
Wynik można sprawdzić, używając tego samego widoku g_columnar_columns:
SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
Oczekiwany wynik:
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=#
Aby sprawdzić, czy używany jest magazyn kolumnowy, uruchom to samo zapytanie i przejrzyj plan wykonania:
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;
Zakończ sesję psql:
exit
Jeśli ponownie uruchomimy kontener AlloyDB Omni, zauważymy, że wszystkie informacje kolumnowe zostały utracone.
W sesji powłoki wykonaj te czynności:
sudo docker stop my-omni
sudo docker start my-omni
Zaczekaj 5–10 sekund i wykonaj:
psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"
Oczekiwany wynik:
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)
Aby automatycznie wypełnić kolumny podczas ponownego uruchamiania, możemy dodać je jako flagi bazy danych do parametrów AlloyDB Omni. Dodajemy flagę google_columnar_engine.relations=‘quickstart_db.public.insurance_producers_licensed_in_iowa(city,expirydate,loa_has_ah)' i ponownym uruchomieniu kontenera.
W sesji powłoki wykonaj te czynności:
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
Następnie widzimy, że po uruchomieniu kolumny zostały automatycznie dodane do Columnar Store.
Zaczekaj 5–10 sekund i wykonaj:
psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"
Oczekiwany wynik:
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. Czyszczenie środowiska
Teraz możesz usunąć maszynę wirtualną AlloyDB Omni.
Usuwanie maszyny wirtualnej GCE
W Cloud Shell wykonaj te czynności:
export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
--zone=$ZONE \
--quiet
Oczekiwane dane wyjściowe konsoli:
student@cloudshell:~ (test-project-001-402417)$ export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
--zone=$ZONE \
--quiet
Deleted
9. Gratulacje
Gratulujemy ukończenia ćwiczenia.
Omówione zagadnienia
- Jak wdrożyć AlloyDB Omni na maszynie wirtualnej GCE w Google Cloud
- Jak połączyć się z AlloyDB Omni
- Jak wczytywać dane do AlloyDB Omni
- Jak włączyć silnik kolumnowy
- Jak sprawdzić działanie silnika kolumnowego w trybie automatycznym
- Ręczne wypełnianie kolumnowego magazynu danych
Więcej informacji o pracy z silnikiem kolumnowym znajdziesz w dokumentacji.
10. Ankieta
Dane wyjściowe: