1. Wprowadzenie
Z tego ćwiczenia dowiesz się, jak wdrożyć AlloyDB Omni i użyć silnika kolumnowego, aby zwiększyć wydajność zapytań.
Wymagania wstępne
- Podstawowa znajomość konsoli Google Cloud
- Podstawowe umiejętności w zakresie interfejsu wiersza poleceń i powłoki Google
Czego się nauczysz
- Jak wdrożyć AlloyDB Omni w maszynie wirtualnej GCE w Google Cloud
- Jak połączyć się z AlloyDB Omni
- Wczytywanie danych do AlloyDB Omni
- Włączanie silnika kolumnowego
- Sprawdzanie silnika kolumnowego w trybie automatycznym
- Ręczne wypełnianie magazynu kolumnowego
Czego potrzebujesz
- Konto Google Cloud i projekt Google Cloud
- przeglądarka, np. Chrome;
2. Konfiguracja i wymagania
Samodzielne konfigurowanie środowiska
- Zaloguj się w konsoli Google Cloud i utwórz nowy projekt lub użyj istniejącego. Jeśli nie masz jeszcze konta Gmail lub Google Workspace, musisz je utworzyć.
- Nazwa projektu to wyświetlana nazwa uczestników tego projektu. Jest to ciąg znaków, który nie jest używany przez interfejsy API Google. Zawsze możesz ją zaktualizować.
- Identyfikator projektu jest unikalny we wszystkich projektach Google Cloud i nie można go zmienić po ustawieniu. Konsola Cloud automatycznie generuje unikalny ciąg znaków. Zwykle nie musisz się tym przejmować. W większości ćwiczeń z programowania musisz odwoływać się do identyfikatora projektu (zwykle oznaczanego jako
PROJECT_ID). Jeśli wygenerowany identyfikator Ci się nie podoba, możesz wygenerować inny losowy identyfikator. Możesz też spróbować własnej nazwy i sprawdzić, czy jest dostępna. Po tym kroku nie można go zmienić i pozostaje on taki przez cały czas trwania projektu. - Warto wiedzieć, że istnieje jeszcze trzecia wartość, numer projektu, której używają niektóre interfejsy API. Więcej informacji o tych 3 wartościach znajdziesz w dokumentacji.
- Następnie musisz włączyć płatności w konsoli Cloud, aby korzystać z zasobów i interfejsów API Google Cloud. Wykonanie tego laboratorium nie będzie kosztować dużo, a może nawet nic. Aby wyłączyć zasoby i uniknąć naliczania opłat po zakończeniu tego samouczka, możesz usunąć utworzone zasoby lub 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
Z Google Cloud można korzystać zdalnie na laptopie, ale w tym ćwiczeniu użyjesz 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 zakończeniu powinno wyświetlić się coś takiego:
Ta maszyna wirtualna zawiera wszystkie potrzebne narzędzia dla programistów. Zawiera również stały katalog domowy o pojemności 5 GB i działa w Google Cloud, co znacznie zwiększa wydajność sieci i usprawnia proces uwierzytelniania. Wszystkie zadania w tym laboratorium możesz wykonać w przeglądarce. Nie musisz niczego instalować.
3. Zanim zaczniesz
Włącz API
Dane wyjściowe:
W Cloud Shell sprawdź, czy identyfikator projektu jest skonfigurowany:
PROJECT_ID=$(gcloud config get-value project)
echo $PROJECT_ID
Oczekiwane dane wyjściowe
student@cloudshell:~ (test-project-001-402417)$ PROJECT_ID=$(gcloud config get-value project) our active configuration is: [cloudshell-14650] student@cloudshell:~ (test-project-001-402417)$
Jeśli nie jest on 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
Oczekiwane dane wyjściowe
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ą ze zgodną konfiguracją i oprogramowaniem. Oto przykład wdrażania AlloyDB Omni na maszynie wirtualnej opartej na Debianie.
Tworzenie maszyny wirtualnej GCE
Musimy wdrożyć maszynę wirtualną o odpowiedniej konfiguracji 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ć uruchomionej wersji Cloud Shell lub terminala z zainstalowanym pakietem SDK Cloud.
Wszystkie kroki są też opisane w szybkim przewodniku po AlloyDB Omni.
Skonfiguruj zmienne środowiskowe wdrożenia.
export ZONE=us-central1-a
export MACHINE_TYPE=n2-highmem-2
export DISK_SIZE=20
export MACHINE_NAME=omni01
Następnie za pomocą gcloud tworzymy maszynę wirtualną 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:
student@cloudshell:~ (test-project-001-402417)$ export ZONE=us-central1-a
export MACHINE_TYPE=n2-highmem-2
export DISK_SIZE=20
export MACHINE_NAME=omni01
student@cloudshell:~ (test-project-001-402417)$ 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/test-project-001-402417/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
student@cloudshell:~ (test-project-001-402417)$
Instalowanie AlloyDB Omni
Połącz się z utworzoną maszyną wirtualną:
gcloud compute ssh omni01 --zone $ZONE
Oczekiwane dane wyjściowe konsoli:
student@cloudshell:~ (test-project-001-402417)$ 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. student@omni01:~$
Uruchom to polecenie w podłączonym terminalu.
Zainstaluj Dockera na maszynie wirtualnej:
sudo apt update
sudo apt-get -y install docker.io
Oczekiwane dane wyjściowe konsoli(zredagowane):
student@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) ... student@omni01:~$
Zdefiniuj hasło użytkownika postgres:
export PGPASSWORD=<your password>
Utwórz katalog na dane AlloyDB Omni. Jest to podejście opcjonalne, ale zalecane. Domyślnie dane są tworzone przy użyciu tymczasowej warstwy systemu plików Dockera, a wszystko jest usuwane po usunięciu kontenera Dockera. Przechowywanie kontenera oddzielnie umożliwia zarządzanie nim niezależnie od danych i opcjonalne umieszczenie go w pamięci o lepszych parametrach wejścia/wyjścia.
Oto polecenie tworzące w katalogu domowym użytkownika katalog, w którym będą umieszczane 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(zredagowane):
student@omni01:~$ export PGPASSWORD=StrongPassword student@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 student@omni01:~$
Zainstaluj oprogramowanie klienta PostgreSQL na maszynie wirtualnej (opcjonalnie – powinno być już zainstalowane):
sudo apt install -y postgresql-client
Oczekiwane dane wyjściowe konsoli:
student@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:
student@omni01:~$ psql -h localhost -U postgres
WARNING: psql major version 15, server major version 17.
Some psql features might not work.
Type "help" for help.
postgres=#
Odłączanie od AlloyDB Omni:
exit
Oczekiwane dane wyjściowe konsoli:
postgres=# exit student@omni01:~$
5. Przygotowywanie bazy danych testowych
Aby przetestować silnik kolumnowy, musimy utworzyć bazę danych i wypełnić ją danymi testowymi.
Utwórz bazę danych
Nawiązywanie połączenia z maszyną wirtualną AlloyDB Omni i tworzenie bazy danych
W sesji Cloud Shell wykonaj to polecenie:
Połącz się 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)$ 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 ustanowionej sesji SSH wykonaj to polecenie:
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 naszych testów użyjemy publicznych danych o licencjonowanych w stanie Iowa agentach ubezpieczeniowych. Ten zbiór danych znajdziesz na stronie internetowej rządu stanu Iowa – https://data.iowa.gov/catalog/dataset/669 .
Najpierw musimy utworzyć tabelę.
Na 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,
city text,
state text,
zip_code int4,
first_active_date timestamp,
expiry_date timestamp,
business_phone text,
email text,
iowa_resident 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,
latitude text,
longitude text,
physical_location text,
address_line_2 text,
address_line_3 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,
Na maszynie wirtualnej GCE wykonaj te czynności:
curl -L https://idh-be.iowa.gov/api/v1/datasets/669/rows.csv | sed '$d' | 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 -L https://idh-be.iowa.gov/api/v1/datasets/669/rows.csv | sed '$d' | 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 48.0M 0 48.0M 0 0 33.9M 0 --:--:-- 0:00:01 --:--:-- 33.9M
COPY 226772
otochkin@omni01:~$
Załadowaliśmy do naszej bazy danych 226 772 rekordy dotyczące producentów ubezpieczeń i możemy przeprowadzić testy. Liczba wierszy może być inna, ponieważ zbiór danych jest aktualizowany codziennie.
Uruchom zapytania testowe
Połącz się z bazą danych quickstart_db za pomocą klienta psql i włącz pomiar czasu, aby mierzyć czas wykonywania zapytań.
Na 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 (15.18 (Debian 15.18-0+deb12u1), server 17.7)
WARNING: psql major version 15, server major version 17.
Some psql features might not work.
Type "help" for help.
quickstart_db=#
W sesji PSQL wykonaj to polecenie:
\timing
Oczekiwane dane wyjściowe konsoli:
quickstart_db=# \timing Timing is on. quickstart_db=#
Znajdźmy 5 miast z największą liczbą agentów ubezpieczeniowych sprzedających ubezpieczenia od następstw nieszczęśliwych wypadków i ubezpieczenia zdrowotne, których licencja jest ważna co najmniej przez kolejne 6 miesięcy.
W sesji PSQL wykonaj to polecenie:
SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expiry_date > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
Oczekiwane dane wyjściowe konsoli:
quickstart_db=# SELECT city, count(*)
quickstart_db-# FROM insurance_producers_licensed_in_iowa
quickstart_db-# WHERE loa_has_ah ='Yes' and expiry_date > now() + interval '6 MONTH'
quickstart_db-# GROUP BY city ORDER BY count(*) desc limit 5;
city | count
-------------+-------
TAMPA | 2159
OMAHA | 1527
MIAMI | 1293
KANSAS CITY | 1091
DALLAS | 1000
(5 rows)
Time: 90.492 ms
Uruchom zapytanie testowe, najlepiej kilka razy, aby uzyskać wiarygodny czas wykonania. Średni czas zwracania wyniku wynosi około 94 ms. W kolejnych 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 zmienić parametr instancji „google_columnar_engine.enabled” na „on” w przypadku AlloyDB Omni, co wymaga ponownego uruchomienia.
Zaktualizuj plik postgresql.conf w katalogu /var/alloydb/config i ponownie uruchom instancję.
Na 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 restart 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 restart my-omni my-omni student@omni01:~$
Sprawdzanie silnika kolumnowego
Połącz się z bazą danych za pomocą psql i sprawdź silnik kolumnowy.
Nawiązywanie połączenia z bazą danych AlloyDB Omni
W sesji SSH maszyny 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
Możemy teraz wypełnić pamięć silnika kolumnowego i sprawdzić wydajność.
Automatyczne wypełnianie magazynu kolumnowego
Domyślnie zadanie wypełniające sklep jest uruchamiane co godzinę. Skrócimy ten czas do 10 minut, aby uniknąć oczekiwania.
Na 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 restart my-omni
Oto oczekiwane dane wyjściowe:
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 restart my-omni my-omni
Sprawdź ustawienia
Na maszynie wirtualnej GCE wykonaj te czynności:
psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.auto_columnarization_schedule;"
Oczekiwane dane wyjściowe:
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 magazynie kolumnowym. Powinno być puste.
Na 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;"
Oczekiwane dane wyjściowe:
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 kilkakrotnie wykonaj to samo zapytanie, które zostało wykonane wcześniej.
Na maszynie wirtualnej GCE wykonaj te czynności:
psql -h localhost -U postgres -d quickstart_db
W sesji PSQL.
Włączanie pomiaru czasu
\timing
Uruchom zapytanie kilka razy:
SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expiry_date > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
Oczekiwane dane wyjściowe:
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: 81.428 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: 82.608 ms
quickstart_db=#
Odczekaj 10 minut i sprawdź, czy kolumny tabeli insurance_producers_licensed_in_iowa zostały wypełnione w pamięci kolumnowej.
SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;
Oczekiwane dane wyjściowe:
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 | expiry_date 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 expiry_date > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
Oczekiwane dane wyjściowe:
quickstart_db=# SELECT city, count(*)
quickstart_db-# FROM insurance_producers_licensed_in_iowa
quickstart_db-# WHERE loa_has_ah ='Yes' and expiry_date > now() + interval '6 MONTH'
quickstart_db-# GROUP BY city ORDER BY count(*) desc limit 5;
city | count
-------------+-------
TAMPA | 2159
OMAHA | 1527
MIAMI | 1293
KANSAS CITY | 1091
DALLAS | 1000
(5 rows)
Time: 11.757 ms
quickstart_db=# SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expiry_date > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
city | count
-------------+-------
TAMPA | 2159
OMAHA | 1527
MIAMI | 1293
KANSAS CITY | 1091
DALLAS | 1000
(5 rows)
Time: 10.839 ms
Czas wykonania spadł z 82 ms do 11 ms. Jeśli nie widzisz żadnych ulepszeń, możesz sprawdzić, czy kolumny zostały prawidłowo wypełnione w pamięci kolumnowej, sprawdzając widok g_columnar_columns.
SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
Oczekiwane dane wyjściowe:
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 | 637312
insurance_producers_licensed_in_iowa | expiry_date | timestamp | Usable | 228356
insurance_producers_licensed_in_iowa | loa_has_ah | text | Usable | 227608
(3 rows)
Teraz możemy sprawdzić, czy plan wykonywania zapytania korzysta z mechanizmu kolumnowego.
W sesji PSQL wykonaj to polecenie:
EXPLAIN (ANALYZE,SETTINGS,BUFFERS)
SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expiry_date > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
Oczekiwane dane wyjściowe:
quickstart_db=# EXPLAIN (ANALYZE,SETTINGS,BUFFERS)
quickstart_db-# SELECT city, count(*)
quickstart_db-# FROM insurance_producers_licensed_in_iowa
quickstart_db-# WHERE loa_has_ah ='Yes' and expiry_date > now() + interval '6 MONTH'
quickstart_db-# GROUP BY city ORDER BY count(*) desc limit 5;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2398.48..2398.49 rows=5 width=17) (actual time=10.018..10.021 rows=5 loops=1)
-> Sort (cost=2398.48..2411.61 rows=5252 width=17) (actual time=10.017..10.019 rows=5 loops=1)
Sort Key: (count(*)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> HashAggregate (cost=2258.73..2311.25 rows=5252 width=17) (actual time=8.320..9.212 rows=7695 loops=1)
Group Key: city
Batches: 1 Memory Usage: 913kB
-> Append (cost=20.00..1762.13 rows=99320 width=9) (actual time=8.314..8.315 rows=8595 loops=1)
-> Custom Scan (columnar scan) on insurance_producers_licensed_in_iowa (cost=20.00..1758.11 rows=99319 width=9) (actual time=8.311..8.312 rows=8595 loops=1)
Filter: ((loa_has_ah = 'Yes'::text) AND (expiry_date > (now() + '6 mons'::interval)))
Rows Removed by Columnar Filter: 127723
Rows Aggregated by Columnar Scan: 99049
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 (expiry_date > (now() + '6 mons'::interval)))
Planning Time: 0.221 ms
Execution Time: 10.127 ms
(17 rows)
Time: 11.171 ms
Widzimy, że operacja „Seq Scan” na segmencie tabeli business_licenses nigdy nie została wykonana, a zamiast niej użyto operacji „Custom Scan (columnar scan)”. Dzięki temu udało nam się skrócić czas reakcji z 82 ms do 11 ms.
Jeśli chcemy wyczyścić automatycznie wypełnione treści z silnika kolumnowego, możemy to zrobić za pomocą funkcji SQL google_columnar_engine_reset_recommendation.
W sesji PSQL wykonaj to polecenie:
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;
Oczekiwane dane wyjściowe:
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 magazynu kolumnowego
Kolumny do magazynu Columnar Engine Store możemy dodawać ręcznie za pomocą funkcji SQL lub określać wymagane jednostki we flagach instancji, aby ładować je automatycznie po uruchomieniu instancji.
Dodajmy te same kolumny co wcześniej za pomocą funkcji SQL google_columnar_engine_add.
W sesji PSQL wykonaj to polecenie:
SELECT google_columnar_engine_add(relation => 'insurance_producers_licensed_in_iowa', columns => 'city,expiry_date,loa_has_ah');
Wynik możemy zweryfikować za pomocą tego samego widoku g_columnar_columns:
SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
Oczekiwane dane wyjściowe:
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 co wcześniej i zbadaj plan wykonania:
EXPLAIN (ANALYZE,SETTINGS,BUFFERS)
SELECT city, count(*)
FROM insurance_producers_licensed_in_iowa
WHERE loa_has_ah ='Yes' and expiry_date > 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, zobaczymy, że wszystkie informacje kolumnowe zostały utracone.
W sesji powłoki wykonaj to polecenie:
sudo docker restart my-omni
Poczekaj 5–10 sekund i wykonaj to polecenie:
psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"
Oczekiwane dane wyjściowe:
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 ponownie 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 ponownie uruchamiamy kontener.
W sesji powłoki wykonaj to polecenie:
sudo docker exec my-omni /bin/bash -c "echo google_columnar_engine.relations=\'quickstart_db.public.insurance_producers_licensed_in_iowa\(city,expiry_date,loa_has_ah\)\' >>/var/lib/postgresql/data/postgresql.conf" && \
sudo docker restart my-omni
Po tym czasie widzimy, że kolumny zostały automatycznie dodane do magazynu kolumnowego po uruchomieniu.
Poczekaj 5–10 sekund i wykonaj to polecenie:
psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"
Oczekiwane dane wyjściowe:
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 | 637312
insurance_producers_licensed_in_iowa | expiry_date | timestamp | Usable | 228356
insurance_producers_licensed_in_iowa | loa_has_ah | text | Usable | 227608
(3 rows)
8. Czyszczenie środowiska
Teraz możemy usunąć maszynę wirtualną AlloyDB Omni.
Usuwanie maszyny wirtualnej GCE
W Cloud Shell wykonaj to polecenie:
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 w maszynie wirtualnej GCE w Google Cloud
- Jak połączyć się z AlloyDB Omni
- Wczytywanie danych do AlloyDB Omni
- Włączanie silnika kolumnowego
- Sprawdzanie silnika kolumnowego w trybie automatycznym
- Ręczne wypełnianie magazynu kolumnowego
Więcej informacji o pracy z Columnar Engine znajdziesz w dokumentacji.
10. Ankieta
Dane wyjściowe: