1. Pengantar
Dalam codelab ini, Anda akan mempelajari cara men-deploy AlloyDB Omni dan menggunakan Columnar Engine untuk meningkatkan performa kueri.
Prasyarat
- Pemahaman dasar tentang Konsol Google Cloud
- Keterampilan dasar dalam antarmuka command line dan Google Shell
Yang akan Anda pelajari
- Cara men-deploy AlloyDB Omni di VM GCE di Google Cloud
- Cara terhubung ke AlloyDB Omni
- Cara memuat data ke AlloyDB Omni
- Cara mengaktifkan Columnar Engine
- Cara memeriksa Columnar Engine dalam mode Otomatis
- Cara mengisi Columnar Store secara manual
Yang Anda butuhkan
- Akun Google Cloud dan Project Google Cloud
- Browser web seperti Chrome
2. Penyiapan dan Persyaratan
Penyiapan lingkungan mandiri
- Login ke Google Cloud Console dan buat project baru atau gunakan kembali project yang sudah ada. Jika belum memiliki akun Gmail atau Google Workspace, Anda harus membuatnya.
- Project name adalah nama tampilan untuk peserta project ini. String ini adalah string karakter yang tidak digunakan oleh Google API. Anda dapat memperbaruinya kapan saja.
- Project ID bersifat unik di semua project Google Cloud dan tidak dapat diubah (tidak dapat diubah setelah ditetapkan). Cloud Console otomatis membuat string unik; biasanya Anda tidak mementingkan kata-katanya. Di sebagian besar codelab, Anda harus merujuk Project ID-nya (umumnya diidentifikasi sebagai
PROJECT_ID). Jika tidak suka dengan ID yang dibuat, Anda dapat membuat ID acak lainnya. Atau, Anda dapat mencobanya sendiri, dan lihat apakah ID tersebut tersedia. ID tidak dapat diubah setelah langkah ini dan tersedia selama durasi project. - Sebagai informasi, ada nilai ketiga, Project Number, yang digunakan oleh beberapa API. Pelajari lebih lanjut ketiga nilai ini di dokumentasi.
- Selanjutnya, Anda harus mengaktifkan penagihan di Konsol Cloud untuk menggunakan resource/API Cloud. Menjalankan operasi dalam codelab ini tidak akan memakan banyak biaya, bahkan mungkin tidak sama sekali. Guna mematikan resource agar tidak menimbulkan penagihan di luar tutorial ini, Anda dapat menghapus resource yang dibuat atau menghapus project-nya. Pengguna baru Google Cloud memenuhi syarat untuk mengikuti program Uji Coba Gratis senilai $300 USD.
Mulai Cloud Shell
Meskipun Google Cloud dapat dioperasikan dari jarak jauh menggunakan laptop Anda, dalam codelab ini, Anda akan menggunakan Google Cloud Shell, lingkungan command line yang berjalan di Cloud.
Dari Google Cloud Console, klik ikon Cloud Shell di toolbar kanan atas:
Hanya perlu waktu beberapa saat untuk penyediaan dan terhubung ke lingkungan. Jika sudah selesai, Anda akan melihat tampilan seperti ini:
Mesin virtual ini berisi semua alat pengembangan yang Anda perlukan. Layanan ini menawarkan direktori beranda tetap sebesar 5 GB dan beroperasi di Google Cloud, sehingga sangat meningkatkan performa dan autentikasi jaringan. Semua pekerjaan Anda dalam codelab ini dapat dilakukan di browser. Anda tidak perlu menginstal apa pun.
3. Sebelum memulai
Aktifkan API
Output:
Di dalam Cloud Shell, pastikan project ID Anda sudah disiapkan:
PROJECT_ID=$(gcloud config get-value project)
echo $PROJECT_ID
Output yang diharapkan
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)$
Jika tidak ditentukan dalam konfigurasi Cloud Shell, siapkan menggunakan perintah berikut
export PROJECT_ID=<your project>
gcloud config set project $PROJECT_ID
Aktifkan semua layanan yang diperlukan:
gcloud services enable compute.googleapis.com
Output yang diharapkan
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. Men-deploy AlloyDB Omni di GCE
Untuk men-deploy AlloyDB Omni di GCE, kita perlu menyiapkan virtual machine dengan konfigurasi dan software yang kompatibel. Berikut adalah contoh cara men-deploy AlloyDB Omni di VM berbasis Debian.
Membuat VM GCE
Kita perlu men-deploy VM dengan konfigurasi yang dapat diterima untuk CPU, memori, dan penyimpanan. Kita akan menggunakan image Debian default dengan ukuran disk sistem yang ditingkatkan menjadi 20 GB untuk mengakomodasi file database AlloyDB Omni.
Kita dapat menggunakan Cloud Shell yang sudah dimulai atau terminal dengan Cloud SDK yang sudah diinstal
Semua langkah juga dijelaskan dalam panduan memulai cepat untuk AlloyDB Omni.
Siapkan variabel lingkungan untuk deployment Anda.
export ZONE=us-central1-a
export MACHINE_TYPE=n2-highmem-2
export DISK_SIZE=20
export MACHINE_NAME=omni01
Kemudian, kita menggunakan gcloud untuk membuat VM GCE.
gcloud compute instances create $MACHINE_NAME \
--project=$(gcloud info --format='value(config.project)') \
--zone=$ZONE --machine-type=$MACHINE_TYPE \
--metadata=enable-os-login=true \
--create-disk=auto-delete=yes,boot=yes,size=$DISK_SIZE,image=projects/debian-cloud/global/images/$(gcloud compute images list --filter="family=debian-12 AND family!=debian-12-arm64" \
--format="value(name)"),type=pd-ssd
Output konsol yang diharapkan:
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)$
Menginstal AlloyDB Omni
Hubungkan ke VM yang dibuat:
gcloud compute ssh omni01 --zone $ZONE
Output konsol yang diharapkan:
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:~$
Jalankan perintah berikut di terminal yang terhubung.
Instal docker di VM:
sudo apt update
sudo apt-get -y install docker.io
Output konsol yang diharapkan (disamarkan):
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:~$
Tentukan sandi untuk pengguna postgres:
export PGPASSWORD=<your password>
Buat direktori untuk data AlloyDB Omni. Ini adalah pendekatan opsional, tetapi direkomendasikan. Secara default, data akan dibuat menggunakan lapisan sistem file sementara Docker dan semuanya akan dihancurkan saat container Docker dihapus. Menyimpannya secara terpisah memungkinkan Anda mengelola container secara independen dari data Anda dan secara opsional menempatkannya ke penyimpanan dengan karakteristik IO yang lebih baik
Berikut adalah perintah yang membuat direktori di direktori beranda pengguna tempat semua data akan ditempatkan:
mkdir -p $HOME/alloydb-data
Deploy container AlloyDB Omni:
sudo docker run --name my-omni \
-e POSTGRES_PASSWORD=$PGPASSWORD \
-p 5432:5432 \
-v $HOME/alloydb-data:/var/lib/postgresql/data \
-v /dev/shm:/dev/shm \
-d google/alloydbomni
Output konsol yang diharapkan (disamarkan):
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:~$
Instal software klien PostgreSQL ke VM (opsional - diharapkan sudah terinstal):
sudo apt install -y postgresql-client
Output konsol yang diharapkan:
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.
Terhubung ke AlloyDB Omni:
psql -h localhost -U postgres
Output konsol yang diharapkan:
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=#
Putuskan koneksi dari AlloyDB Omni:
exit
Output konsol yang diharapkan:
postgres=# exit student@omni01:~$
5. Menyiapkan Database Pengujian
Untuk menguji Columnar Engine, kita perlu membuat database dan mengisinya dengan beberapa data pengujian.
Buat Database
Menghubungkan ke VM AlloyDB Omni dan membuat database
Dalam sesi Cloud Shell, jalankan:
Hubungkan ke VM AlloyDB Omni:
ZONE=us-central1-a
gcloud compute ssh omni01 --zone $ZONE
Output konsol yang diharapkan:
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:~$
Dalam sesi SSH yang sudah dibuat, jalankan:
export PGPASSWORD=<your password>
psql -h localhost -U postgres -c "CREATE DATABASE quickstart_db"
Output konsol yang diharapkan:
student@omni01:~$ psql -h localhost -U postgres -c "CREATE DATABASE quickstart_db" CREATE DATABASE student@omni01:~$
Membuat tabel dengan data sampel
Untuk pengujian, kita akan menggunakan data publik tentang Produsen Asuransi Berlisensi di Iowa. Anda dapat menemukan set data ini di situs pemerintah Iowa - https://data.iowa.gov/catalog/dataset/669 .
Pertama, kita perlu membuat tabel.
Di VM GCE, jalankan:
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
);"
Output konsol yang diharapkan:
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:~$
Muat data ke dalam tabel.
Di VM GCE, jalankan:
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"
Output konsol yang diharapkan:
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:~$
Kita telah memuat 226772 data tentang produsen asuransi ke database dan dapat melakukan beberapa pengujian. Jumlah baris untuk Anda dapat berbeda karena set data diperbarui setiap hari.
Menjalankan Kueri Pengujian
Hubungkan ke quickstart_db menggunakan psql dan aktifkan pengaturan waktu untuk mengukur waktu eksekusi kueri kita.
Di VM GCE, jalankan:
psql -h localhost -U postgres -d quickstart_db
Output konsol yang diharapkan:
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=#
Dalam sesi PSQL, jalankan:
\timing
Output konsol yang diharapkan:
quickstart_db=# \timing Timing is on. quickstart_db=#
Mari kita temukan 5 kota teratas berdasarkan jumlah produsen asuransi yang menjual asuransi Kecelakaan dan Kesehatan serta memiliki lisensi yang berlaku setidaknya selama 6 bulan ke depan.
Dalam sesi PSQL, jalankan:
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;
Output konsol yang diharapkan:
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
Jalankan kueri pengujian beberapa kali untuk mendapatkan waktu eksekusi yang andal. Kita dapat melihat bahwa waktu rata-rata untuk menampilkan hasil adalah sekitar 94 md. Pada langkah-langkah berikut, kita akan mengaktifkan Mesin Kolumnar AlloyDB dan melihat apakah performanya dapat ditingkatkan.
Keluar dari sesi psql:
exit
6. Mengaktifkan Columnar Engine
Sekarang kita perlu mengaktifkan Columnar Engine di AlloyDB Omni.
Memperbarui Parameter AlloyDB Omni
Kita perlu mengalihkan parameter instance "google_columnar_engine.enabled" ke "on" untuk AlloyDB Omni dan instance tersebut perlu dimulai ulang.
Perbarui postgresql.conf di direktori /var/alloydb/config dan mulai ulang instance.
Di VM GCE, jalankan:
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
Output konsol yang diharapkan:
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:~$
Memverifikasi Columnar Engine
Hubungkan ke database menggunakan psql dan verifikasi columnar engine.
Menghubungkan ke database AlloyDB Omni
Dalam sesi SSH VM, hubungkan ke database:
psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.enabled"
Perintah akan menampilkan mesin columnar yang diaktifkan.
Output konsol yang diharapkan:
student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.enabled" google_columnar_engine.enabled -------------------------------- on (1 row)
7. Perbandingan Performa
Sekarang kita dapat mengisi penyimpanan columnar engine dan memverifikasi performanya.
Pengisian Otomatis Columnar Store
Secara default, tugas yang mengisi toko berjalan setiap jam. Kita akan mengurangi waktu ini menjadi 10 menit untuk menghindari penantian.
Di VM GCE, jalankan:
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
Berikut output yang diharapkan:
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
Memverifikasi setelan
Di VM GCE, jalankan:
psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.auto_columnarization_schedule;"
Output yang diharapkan:
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:~$
Periksa objek di Columnar Store. Seharusnya kode tersebut kosong.
Di VM GCE, jalankan:
psql -h localhost -U postgres -d quickstart_db -c "SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;"
Output yang diharapkan:
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:~$
Hubungkan ke database dan jalankan kueri yang sama yang kita jalankan sebelumnya beberapa kali.
Di VM GCE, jalankan:
psql -h localhost -U postgres -d quickstart_db
Di sesi PSQL.
Aktifkan pengaturan waktu
\timing
Jalankan kueri beberapa kali:
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;
Output yang diharapkan:
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=#
Tunggu 10 menit dan periksa apakah kolom tabel insurance_producers_licensed_in_iowa telah diisi ke dalam penyimpanan berkolom.
SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;
Output yang diharapkan:
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
Sekarang kita dapat menjalankan kueri untuk tabel insurance_producers_licensed_in_iowa lagi dan melihat apakah performanya meningkat.
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;
Output yang diharapkan:
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
Waktu eksekusi telah turun dari 82 md menjadi 11 md. Jika tidak melihat peningkatan, Anda dapat memeriksa apakah kolom telah berhasil diisi ke penyimpanan kolom dengan memeriksa tampilan g_columnar_columns.
SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
Output yang diharapkan:
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)
Sekarang kita dapat memverifikasi apakah rencana eksekusi kueri menggunakan Columnar Engine.
Dalam sesi PSQL, jalankan:
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;
Output yang diharapkan:
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
Kita dapat melihat bahwa operasi "Seq Scan" pada segmen tabel business_licenses tidak pernah dijalankan dan "Custom Scan (columnar scan)" digunakan sebagai gantinya. Hal itu membantu kami meningkatkan waktu respons dari 82 menjadi 11 md.
Jika ingin menghapus konten yang diisi otomatis dari columnar engine, kita dapat melakukannya menggunakan fungsi SQL google_columnar_engine_reset_recommendation.
Dalam sesi PSQL, jalankan:
SELECT google_columnar_engine_reset_recommendation(drop_columns => true);
Kolom yang terisi akan dihapus dan Anda dapat memverifikasinya di tampilan g_columnar_columns dan g_columnar_recommended_columns seperti yang ditunjukkan sebelumnya.
SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;
SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
Output yang diharapkan:
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=#
Pengisian Penyimpanan Kolumnar Manual
Kita dapat menambahkan kolom ke Columnar Engine Store secara manual menggunakan fungsi SQL atau menentukan entitas yang diperlukan di flag instance untuk memuatnya secara otomatis saat instance dimulai.
Mari kita tambahkan kolom yang sama seperti sebelumnya menggunakan fungsi SQL google_columnar_engine_add.
Dalam sesi PSQL, jalankan:
SELECT google_columnar_engine_add(relation => 'insurance_producers_licensed_in_iowa', columns => 'city,expiry_date,loa_has_ah');
Kita dapat memverifikasi hasilnya menggunakan tampilan g_columnar_columns yang sama:
SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;
Output yang diharapkan:
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=#
Anda dapat memverifikasi bahwa penyimpanan berkolom digunakan dengan menjalankan kueri yang sama seperti sebelumnya dan memeriksa rencana eksekusi:
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;
Keluar dari sesi psql:
exit
Jika kita memulai ulang container AlloyDB Omni, kita dapat melihat bahwa semua informasi kolom hilang.
Dalam sesi shell, jalankan:
sudo docker restart my-omni
Tunggu 5-10 detik, lalu jalankan:
psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"
Output yang diharapkan:
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)
Untuk mengisi ulang kolom secara otomatis selama memulai ulang, kita dapat menambahkannya sebagai flag database ke parameter AlloyDB Omni. Kita menambahkan flag google_columnar_engine.relations=‘quickstart_db.public.insurance_producers_licensed_in_iowa(city,expirydate,loa_has_ah)' dan memulai ulang container.
Dalam sesi shell, jalankan:
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
Setelah itu, kita dapat melihat bahwa kolom telah ditambahkan ke Columnar Store secara otomatis setelah startup.
Tunggu 5-10 detik, lalu jalankan:
psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"
Output yang diharapkan:
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. Membersihkan lingkungan
Sekarang kita dapat menghancurkan VM AlloyDB Omni
Hapus VM GCE
Di Cloud Shell, jalankan:
export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
--zone=$ZONE \
--quiet
Output konsol yang diharapkan:
student@cloudshell:~ (test-project-001-402417)$ export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
--zone=$ZONE \
--quiet
Deleted
9. Selamat
Selamat, Anda telah menyelesaikan codelab.
Yang telah kita bahas
- Cara men-deploy AlloyDB Omni di VM GCE di Google Cloud
- Cara terhubung ke AlloyDB Omni
- Cara memuat data ke AlloyDB Omni
- Cara mengaktifkan Columnar Engine
- Cara memeriksa Columnar Engine dalam mode Otomatis
- Cara mengisi Columnar Store secara manual
Anda dapat membaca selengkapnya tentang cara menggunakan Columnar Engine di dokumentasi.
10. Survei
Output: