1. Wprowadzenie
Z tego przewodnika dowiesz się, jak wdrożyć AlloyDB Omni i używać silnika kolumnowego do zwiększania wydajności 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 na maszynie wirtualnej GCE w Google Cloud
- Jak połączyć się z AlloyDB Omni
- Wczytywanie danych do AlloyDB Omni
- Włączanie silnika kolumnowego
- Jak sprawdzić silnik kolumnowy 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 ani 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ę nim 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 też 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 programu bezpłatnego okresu próbnego o wartości 300 USD.
Uruchamianie Cloud Shell
Z Google Cloud można korzystać zdalnie na laptopie, ale w tym module praktycznym 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 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
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
Oczekiwane dane wyjściowe
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ą 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:
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 podłączonym terminalu.
Zainstaluj Dockera na maszynie wirtualnej:
sudo apt update
sudo apt-get -y install docker.io
Oczekiwane dane wyjściowe konsoli(zredagowane):
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 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 go oddzielnie umożliwia zarządzanie kontenerami niezależnie od danych i opcjonalne umieszczanie ich 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):
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 na 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. Przygotowywanie testowej bazy danych
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:
gcloud config set project $(gcloud config get-value project)
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)$ 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 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 agentach ubezpieczeniowych w stanie Iowa. Ten zbiór danych znajdziesz 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ę.
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,
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,
Na 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:~$
Załadowaliśmy do naszej bazy danych 210898 rekordów dotyczących producentów ubezpieczeń i możemy przeprowadzić testy.
Uruchom zapytania testowe
Połącz się z bazą danych quickstart_db za pomocą 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 (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 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 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
Uruchom zapytanie testowe 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 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:~$
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ć sklep 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 stop my-omni
sudo docker start my-omni
Oto 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:~$
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 użyte 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 expirydate > 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: 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 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 | 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;
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: 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 skrócił się z 94 ms do 14 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 | 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żemy sprawdzić, czy plan wykonania 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 expirydate > now() + interval '6 MONTH'
GROUP BY city ORDER BY count(*) desc limit 5;
Oczekiwane dane wyjściowe:
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
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 94 ms do 12 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 wczytywać 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,expirydate,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 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, zobaczymy, że wszystkie informacje kolumnowe zostały utracone.
W sesji powłoki wykonaj to polecenie:
sudo docker stop my-omni
sudo docker start 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,expirydate,loa_has_ah\)\' >>/var/lib/postgresql/data/postgresql.conf"
sudo docker stop my-omni
sudo docker start my-omni
Po tym możemy zobaczyć, ż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 | 664231
insurance_producers_licensed_in_iowa | expirydate | timestamp | Usable | 212434
insurance_producers_licensed_in_iowa | loa_has_ah | text | Usable | 211734
(3 rows)
8. Zwalnianie miejsca
Teraz możemy zniszczyć 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 na maszynie wirtualnej GCE w Google Cloud
- Jak połączyć się z AlloyDB Omni
- Wczytywanie danych do AlloyDB Omni
- Włączanie silnika kolumnowego
- Jak sprawdzić silnik kolumnowy 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: