تسریع پرس و جوهای تحلیلی با موتور ستونی در AlloyDB Omni.

تسریع پرس و جوهای تحلیلی با موتور ستونی در AlloyDB Omni.

درباره این codelab

subjectآخرین به‌روزرسانی: فوریه ۲۵, ۲۰۲۵
account_circleنویسنده: Gleb Otochkin

1. مقدمه

در این کد لبه شما نحوه استقرار AlloyDB Omni و استفاده از Columnar Engine را برای بهبود عملکرد برای پرس و جوها یاد خواهید گرفت.

7da257ba067ed1b1.png

پیش نیازها

  • درک اولیه از Google Cloud، کنسول
  • مهارت های اولیه در رابط خط فرمان و پوسته گوگل

چیزی که یاد خواهید گرفت

  • نحوه استقرار AlloyDB Omni در GCE VM در Google Cloud
  • نحوه اتصال به AlloyDB Omni
  • نحوه بارگیری داده ها در AlloyDB Omni
  • نحوه فعال کردن Columnar Engine
  • نحوه بررسی Columnar Engine در حالت خودکار
  • نحوه پر کردن Columnar Store به صورت دستی

آنچه شما نیاز دارید

  • یک حساب Google Cloud و پروژه Google Cloud
  • یک مرورگر وب مانند کروم

2. راه اندازی و الزامات

تنظیم محیط خود به خود

  1. به Google Cloud Console وارد شوید و یک پروژه جدید ایجاد کنید یا از یک موجود استفاده مجدد کنید. اگر قبلاً یک حساب Gmail یا Google Workspace ندارید، باید یک حساب ایجاد کنید .

fbef9caa1602edd0.png

a99b7ace416376c4.png

5e3ff691252acf41.png

  • نام پروژه نام نمایشی برای شرکت کنندگان این پروژه است. این یک رشته کاراکتری است که توسط API های Google استفاده نمی شود. همیشه می توانید آن را به روز کنید.
  • شناسه پروژه در تمام پروژه‌های Google Cloud منحصربه‌فرد است و تغییرناپذیر است (پس از تنظیم نمی‌توان آن را تغییر داد). Cloud Console به طور خودکار یک رشته منحصر به فرد تولید می کند. معمولاً برای شما مهم نیست که چیست. در اکثر کدها، باید شناسه پروژه خود را ارجاع دهید (معمولاً با نام PROJECT_ID شناخته می شود). اگر شناسه تولید شده را دوست ندارید، ممکن است یک شناسه تصادفی دیگر ایجاد کنید. از طرف دیگر، می‌توانید خودتان را امتحان کنید، و ببینید آیا در دسترس است یا خیر. پس از این مرحله نمی توان آن را تغییر داد و در طول مدت پروژه باقی می ماند.
  • برای اطلاع شما، یک مقدار سوم وجود دارد، یک شماره پروژه ، که برخی از API ها از آن استفاده می کنند. در مورد هر سه این مقادیر در مستندات بیشتر بیاموزید.
  1. در مرحله بعد، برای استفاده از منابع Cloud/APIها باید صورتحساب را در کنسول Cloud فعال کنید . اجرا کردن از طریق این کد لبه هزینه زیادی نخواهد داشت. برای خاموش کردن منابع برای جلوگیری از تحمیل صورت‌حساب فراتر از این آموزش، می‌توانید منابعی را که ایجاد کرده‌اید حذف کنید یا پروژه را حذف کنید. کاربران جدید Google Cloud واجد شرایط برنامه آزمایشی رایگان 300 دلاری هستند.

Cloud Shell را راه اندازی کنید

در حالی که Google Cloud را می توان از راه دور از لپ تاپ شما کار کرد، در این کد لبه از Google Cloud Shell استفاده خواهید کرد، یک محیط خط فرمان که در Cloud اجرا می شود.

از Google Cloud Console ، روی نماد Cloud Shell در نوار ابزار بالا سمت راست کلیک کنید:

55efc1aaa7a4d3ad.png

تهیه و اتصال به محیط فقط چند لحظه طول می کشد. وقتی تمام شد، باید چیزی شبیه به این را ببینید:

7ffe5cbb04455448.png

این ماشین مجازی با تمام ابزارهای توسعه که شما نیاز دارید بارگذاری شده است. این یک فهرست اصلی 5 گیگابایتی دائمی را ارائه می دهد و در Google Cloud اجرا می شود و عملکرد و احراز هویت شبکه را تا حد زیادی افزایش می دهد. تمام کارهای شما در این کد لبه را می توان در یک مرورگر انجام داد. شما نیازی به نصب چیزی ندارید.

3. قبل از شروع

فعال کردن API

خروجی:

در داخل Cloud Shell، مطمئن شوید که ID پروژه شما تنظیم شده است:

PROJECT_ID=$(gcloud config get-value project)
echo $PROJECT_ID

اگر در پیکربندی پوسته ابری تعریف نشده باشد، آن را با استفاده از دستورات زیر تنظیم کنید

export PROJECT_ID=<your project>
gcloud config set project $PROJECT_ID

فعال کردن تمام خدمات لازم:

gcloud services enable compute.googleapis.com

خروجی مورد انتظار

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. AlloyDB Omni را در GCE مستقر کنید

برای استقرار AlloyDB Omni در GCE باید یک ماشین مجازی با پیکربندی و نرم افزار سازگار آماده کنیم. در اینجا مثالی از نحوه استقرار AlloyDB Omni در VM مبتنی بر دبیان آورده شده است.

یک GCE VM ایجاد کنید

ما باید یک VM با پیکربندی قابل قبول برای CPU، حافظه و ذخیره سازی مستقر کنیم. ما می‌خواهیم از تصویر پیش‌فرض Debian با حجم دیسک سیستم به 20 گیگابیت برای قرار دادن فایل‌های پایگاه داده AlloyDB Omni استفاده کنیم.

ما می توانیم از پوسته ابری شروع شده یا ترمینال با SDK ابری نصب شده استفاده کنیم

تمام مراحل نیز در شروع سریع برای AlloyDB Omni توضیح داده شده است.

متغیرهای محیطی را برای استقرار خود تنظیم کنید.

export ZONE=us-central1-a
export MACHINE_TYPE=n2-highmem-2
export DISK_SIZE=20
export MACHINE_NAME=omni01

سپس از gcloud برای ایجاد GCE VM استفاده می کنیم.

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

خروجی کنسول مورد انتظار:

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)$ 

AlloyDB Omni را نصب کنید

به VM ایجاد شده متصل شوید:

gcloud compute ssh omni01 --zone $ZONE

خروجی کنسول مورد انتظار:

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:~$

دستور زیر را در ترمینال متصل خود اجرا کنید.

نصب docker در VM:

sudo apt update
sudo apt
-get -y install docker.io

خروجی کنسول مورد انتظار (تدوین شده):

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:~$

تعریف رمز عبور برای کاربر postgres:

export PGPASSWORD=<your password>

یک دایرکتوری برای داده های AlloyDB Omni ایجاد کنید. این یک رویکرد اختیاری است اما توصیه می شود. به‌طور پیش‌فرض، داده‌ها با استفاده از لایه سیستم فایل زودگذر docker ایجاد می‌شوند و با حذف ظرف docker، همه چیز از بین می‌رود. جدا نگه داشتن آن به شما این امکان را می دهد که کانتینرها را مستقل از داده های خود مدیریت کنید و به صورت اختیاری آن را در فضای ذخیره سازی با ویژگی های IO بهتر قرار دهید.

در اینجا دستوری وجود دارد که یک دایرکتوری در فهرست اصلی کاربر ایجاد می کند که در آن همه داده ها قرار می گیرند:

mkdir -p $HOME/alloydb-data

استقرار کانتینر 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

خروجی کنسول مورد انتظار (تدوین شده):

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:~$ 

نرم افزار مشتری PostgreSQL را روی ماشین مجازی نصب کنید (اختیاری - انتظار می رود قبلاً نصب شده باشد):

sudo apt install -y  postgresql-client

خروجی کنسول مورد انتظار:

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.

اتصال به AlloyDB Omni:

psql -h localhost -U postgres

خروجی کنسول مورد انتظار:

gleb@omni01:~$ psql -h localhost -U postgres
psql (15.6 (Debian 15.6-0+deb12u1), server 15.5)
Type "help" for help.

postgres=# 

قطع اتصال از AlloyDB Omni:

exit

خروجی کنسول مورد انتظار:

postgres=# exit
gleb@omni01:~$ 

5. یک پایگاه داده آزمون آماده کنید

برای تست Columnar Engine باید یک پایگاه داده ایجاد کنیم و آن را با داده های آزمایشی پر کنیم.

ایجاد پایگاه داده

به AlloyDB Omni VM متصل شوید و یک پایگاه داده ایجاد کنید

در جلسه Cloud Shell اجرا کنید:

gcloud config set project $(gcloud config get-value project)

اتصال به AlloyDB Omni VM:

ZONE=us-central1-a
gcloud compute ssh omni01
--zone $ZONE

خروجی کنسول مورد انتظار:

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:~$

در جلسه SSH ایجاد شده اجرا کنید:

export PGPASSWORD=<your password>
psql -h localhost -U postgres -c "CREATE DATABASE quickstart_db"

خروجی کنسول مورد انتظار:

student@omni01:~$ psql -h localhost -U postgres -c "CREATE DATABASE quickstart_db"
CREATE DATABASE
student@omni01:~$

یک جدول با داده های نمونه ایجاد کنید

برای آزمایش‌های خود، از داده‌های عمومی درباره تولیدکنندگان بیمه دارای مجوز در آیووا استفاده می‌کنیم. می توانید این مجموعه داده را در وب سایت دولت آیووا بیابید - https://data.iowa.gov/Regulation/Insurance-Producers-Licensed-in-Iowa/n4cc-vqyk/about_data .

ابتدا باید یک جدول بسازیم.

در GCE VM اجرا کنید:

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
);"

خروجی کنسول مورد انتظار:

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:~$

داده ها را روی جدول بارگیری کنید.

در GCE VM اجرا کنید:

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"

خروجی کنسول مورد انتظار:

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:~$

ما 210898 رکورد در مورد تولیدکنندگان بیمه را در پایگاه داده خود بارگذاری کرده ایم و می توانیم آزمایشاتی را انجام دهیم.

کوئری های تست را اجرا کنید

با استفاده از psql به quickstart_db متصل شوید و زمان بندی را برای اندازه گیری زمان اجرا برای درخواست های ما فعال کنید.

در GCE VM اجرا کنید:

psql -h localhost -U postgres -d quickstart_db

خروجی کنسول مورد انتظار:

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=#

در جلسه PSQL اجرا کنید:

\timing

خروجی کنسول مورد انتظار:

quickstart_db=# \timing
Timing is on.
quickstart_db=# 

بیایید 5 شهر برتر را بر اساس تعداد تولیدکنندگان بیمه که بیمه های حوادث و سلامت می فروشند و مجوز آنها حداقل تا 6 ماه آینده معتبر است را پیدا کنیم.

در جلسه PSQL اجرا کنید:

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;

خروجی کنسول مورد انتظار:

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

یک پرس و جو آزمایشی را چندین بار ترجیح دهید تا زمان اجرای قابل اعتماد را بدست آورید. می بینیم که میانگین زمان برای برگرداندن نتیجه حدود 94 میلی ثانیه است. در مراحل زیر می‌خواهیم موتور ستونی AlloyDB را فعال کنیم و ببینیم که آیا می‌تواند عملکرد را بهبود بخشد.

خروج از جلسه psql:

exit

6. موتور ستونی را فعال کنید

اکنون باید Columnar Engine را در AlloyDB Omni خود فعال کنیم.

پارامترهای AlloyDB Omni را به روز کنید

ما باید پارامتر نمونه "google_columnar_engine.enabled" را به "روشن" برای AlloyDB Omni خود تغییر دهیم و نیاز به راه اندازی مجدد دارد.

postgresql.conf را در پوشه /var/alloydb/config به روز کنید و نمونه را مجددا راه اندازی کنید.

در GCE VM اجرا کنید:

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

خروجی کنسول مورد انتظار:

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:~$

موتور ستونی را تأیید کنید

با استفاده از psql به پایگاه داده متصل شوید و موتور ستونی را تأیید کنید.

به پایگاه داده AlloyDB Omni متصل شوید

در جلسه VM SSH به پایگاه داده متصل شوید:

psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.enabled"

دستور باید موتور ستونی فعال را نشان دهد.

خروجی کنسول مورد انتظار:

student@omni01:~$ psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.enabled"
 google_columnar_engine.enabled 
--------------------------------
 on
(1 row)

7. مقایسه عملکرد

اکنون می‌توانیم فروشگاه موتور ستونی را پر کرده و عملکرد را تأیید کنیم.

جمعیت فروشگاه ستونی خودکار

به طور پیش فرض کار پر کردن فروشگاه هر ساعت اجرا می شود. برای جلوگیری از انتظار، این زمان را به 10 دقیقه کاهش می دهیم.

در GCE VM اجرا کنید:

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

و در اینجا خروجی مورد انتظار است:

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:~$

تنظیمات را تایید کنید

در GCE VM اجرا کنید:

psql -h localhost -U postgres -d quickstart_db -c "show google_columnar_engine.auto_columnarization_schedule;"

خروجی مورد انتظار:

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:~$ 

اشیاء موجود در Columnar Store را بررسی کنید. باید خالی باشه

در GCE VM اجرا کنید:

psql -h localhost -U postgres -d quickstart_db -c "SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;"

خروجی مورد انتظار:

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:~$

به پایگاه داده متصل شوید و همان پرس و جوی را که قبلا اجرا کردیم چندین بار اجرا کنید.

در GCE VM اجرا کنید:

psql -h localhost -U postgres -d quickstart_db 

در جلسه PSQL.

زمان بندی را فعال کنید

\timing

پرس و جو را چند بار اجرا کنید:

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;

خروجی مورد انتظار:

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=#

10 دقیقه صبر کنید و بررسی کنید که آیا ستون های جدول بیمه_تولیدکنندگان_مجوز_in_iowa در فروشگاه ستونی پر شده اند یا خیر.

SELECT database_name, schema_name, relation_name, column_name FROM g_columnar_recommended_columns;

خروجی مورد انتظار:

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

اکنون می‌توانیم پرس و جو را برای جدول insurance_producers_licensed_in_iowa دوباره اجرا کنیم و ببینیم آیا عملکرد بهبود یافته است یا خیر.

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;

خروجی مورد انتظار:

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

زمان اجرا از 94 میلی ثانیه به 14 میلی ثانیه کاهش یافته است. اگر هیچ پیشرفتی نمی بینید، می توانید با بررسی نمای g_columnar_columns بررسی کنید که آیا ستون ها با موفقیت در فروشگاه ستونی پر شده اند یا خیر.

SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;

خروجی مورد انتظار:

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)

اکنون می توانیم بررسی کنیم که آیا طرح اجرای پرس و جو از موتور ستونی استفاده می کند یا خیر.

در جلسه PSQL اجرا کنید:

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;

خروجی مورد انتظار:

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

و می‌توانیم ببینیم که عملیات "Seq Scan" در بخش جدول business_licenses هرگز اجرا نشد و به جای آن از "اسکن سفارشی (اسکن ستونی)" استفاده شد. این به ما کمک کرد تا زمان پاسخگویی را از 94 به 12 میلی ثانیه افزایش دهیم.

اگر بخواهیم محتوای پر شده خودکار را از موتور ستونی پاک کنیم، می‌توانیم این کار را با استفاده از تابع SQL google_columnar_engine_reset_recommendation انجام دهیم.

در جلسه PSQL اجرا کنید:

SELECT google_columnar_engine_reset_recommendation(drop_columns => true);

ستون های پر شده را پاک می کند و می توانید همانطور که قبلا نشان داده شد در نماهای g_columnar_columns و g_columnar_recommended_columns آن را تأیید کنید.

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;

خروجی مورد انتظار:

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=#

دستی جمعیت فروشگاه ستونی

می‌توانیم با استفاده از توابع SQL، ستون‌ها را به‌صورت دستی به فروشگاه Columnar Engine اضافه کنیم یا موجودیت‌های مورد نیاز را در پرچم‌های نمونه مشخص کنیم تا با شروع نمونه، به‌طور خودکار بارگذاری شوند.

بیایید همان ستون‌های قبلی را با استفاده از تابع google_columnar_engine_add SQL اضافه کنیم.

در جلسه PSQL اجرا کنید:

SELECT google_columnar_engine_add(relation => 'insurance_producers_licensed_in_iowa', columns => 'city,expirydate,loa_has_ah');

و ما می توانیم نتیجه را با استفاده از همان نمای g_columnar_columns تأیید کنیم:

SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns;

خروجی مورد انتظار:

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=#

می توانید با اجرای همان پرس و جوی قبلی و بررسی طرح اجرا، تأیید کنید که فروشگاه ستونی از آن استفاده می کند:

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;

از جلسه psql خارج شوید:

exit

اگر کانتینر AlloyDB Omni را دوباره راه اندازی کنیم، می بینیم که تمام اطلاعات ستونی از بین رفته است.

در جلسه پوسته اجرا کنید:

sudo docker stop my-omni
sudo docker start
my-omni

5-10 ثانیه صبر کنید و اجرا کنید:

psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"

خروجی مورد انتظار:

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)

برای پر کردن خودکار ستون ها در طول راه اندازی مجدد، می توانیم آنها را به عنوان پرچم های پایگاه داده به پارامترهای AlloyDB Omni خود اضافه کنیم. پرچم google_columnar_engine.relations='quickstart_db.public.insurance_producers_licensed_in_iowa(city,expirydate,loa_has_ah)" را اضافه می کنیم و کانتینر را دوباره راه اندازی می کنیم.

در جلسه پوسته اجرا کنید:

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

و بعد از آن می بینیم که ستون ها پس از راه اندازی به طور خودکار به فروشگاه Columnar اضافه شده اند.

5-10 ثانیه صبر کنید و اجرا کنید:

psql -h localhost -U postgres -d quickstart_db -c "SELECT relation_name,column_name,column_type,status,size_in_bytes from g_columnar_columns"

خروجی مورد انتظار:

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. پاکسازی محیط

اکنون می توانیم AlloyDB Omni VM خود را نابود کنیم

GCE VM را حذف کنید

در Cloud Shell اجرا کنید:

export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
    --zone=$ZONE \
    --quiet

خروجی کنسول مورد انتظار:

student@cloudshell:~ (test-project-001-402417)$ export GCEVM=omni01
export ZONE=us-central1-a
gcloud compute instances delete $GCEVM \
    --zone=$ZONE \
    --quiet
Deleted 

9. تبریک میگم

برای تکمیل کد لبه تبریک می گویم.

آنچه را پوشش داده ایم

  • نحوه استقرار AlloyDB Omni در GCE VM در Google Cloud
  • نحوه اتصال به AlloyDB Omni
  • نحوه بارگیری داده ها در AlloyDB Omni
  • نحوه فعال کردن Columnar Engine
  • نحوه بررسی Columnar Engine در حالت خودکار
  • نحوه پر کردن Columnar Store به صورت دستی

می توانید اطلاعات بیشتری در مورد کار با Columnar Engine در مستندات بخوانید.

10. نظرسنجی

خروجی:

چگونه از این آموزش استفاده خواهید کرد؟