۱. مقدمه
در این آزمایشگاه کد، نحوهی پیادهسازی AlloyDB Omni و استفاده از Columnar Engine برای بهبود عملکرد کوئریها را خواهید آموخت.
پیشنیازها
- درک اولیه از گوگل کلود، کنسول
- مهارتهای پایه در رابط خط فرمان و گوگل شل
آنچه یاد خواهید گرفت
- نحوه استقرار AlloyDB Omni روی GCE VM در Google Cloud
- نحوه اتصال به AlloyDB Omni
- نحوه بارگذاری دادهها در AlloyDB Omni
- نحوه فعال کردن موتور ستونی
- نحوه بررسی موتور ستونی در حالت اتوماتیک
- نحوه پر کردن دستی فروشگاه ستونی
آنچه نیاز دارید
- یک حساب کاربری گوگل کلود و پروژه گوگل کلود
- یک مرورگر وب مانند کروم
۲. تنظیمات و الزامات
تنظیم محیط خودتنظیم
- وارد کنسول گوگل کلود شوید و یک پروژه جدید ایجاد کنید یا از یک پروژه موجود دوباره استفاده کنید. اگر از قبل حساب جیمیل یا گوگل ورک اسپیس ندارید، باید یکی ایجاد کنید .
- نام پروژه، نام نمایشی برای شرکتکنندگان این پروژه است. این یک رشته کاراکتری است که توسط APIهای گوگل استفاده نمیشود. شما همیشه میتوانید آن را بهروزرسانی کنید.
- شناسه پروژه در تمام پروژههای گوگل کلود منحصر به فرد است و تغییرناپذیر است (پس از تنظیم، قابل تغییر نیست). کنسول کلود به طور خودکار یک رشته منحصر به فرد تولید میکند؛ معمولاً برای شما مهم نیست که چه باشد. در اکثر آزمایشگاههای کد، باید شناسه پروژه خود را (که معمولاً با عنوان
PROJECT_IDشناخته میشود) ارجاع دهید. اگر شناسه تولید شده را دوست ندارید، میتوانید یک شناسه تصادفی دیگر ایجاد کنید. به عنوان یک جایگزین، میتوانید شناسه خودتان را امتحان کنید و ببینید که آیا در دسترس است یا خیر. پس از این مرحله قابل تغییر نیست و در طول پروژه باقی میماند. - برای اطلاع شما، یک مقدار سوم، شماره پروژه ، وجود دارد که برخی از APIها از آن استفاده میکنند. برای کسب اطلاعات بیشتر در مورد هر سه این مقادیر، به مستندات مراجعه کنید.
- در مرحله بعد، برای استفاده از منابع/API های ابری، باید پرداخت صورتحساب را در کنسول ابری فعال کنید . اجرای این آزمایشگاه کد هزینه زیادی نخواهد داشت، اگر اصلاً هزینهای داشته باشد. برای خاموش کردن منابع به منظور جلوگیری از پرداخت صورتحساب پس از این آموزش، میتوانید منابعی را که ایجاد کردهاید یا پروژه را حذف کنید. کاربران جدید Google Cloud واجد شرایط برنامه آزمایشی رایگان ۳۰۰ دلاری هستند.
شروع پوسته ابری
اگرچه میتوان از راه دور و از طریق لپتاپ، گوگل کلود را مدیریت کرد، اما در این آزمایشگاه کد، از گوگل کلود شل ، یک محیط خط فرمان که در فضای ابری اجرا میشود، استفاده خواهید کرد.
از کنسول گوگل کلود ، روی آیکون Cloud Shell در نوار ابزار بالا سمت راست کلیک کنید:
آمادهسازی و اتصال به محیط فقط چند لحظه طول میکشد. وقتی تمام شد، باید چیزی شبیه به این را ببینید:
این ماشین مجازی با تمام ابزارهای توسعهای که نیاز دارید، مجهز شده است. این ماشین مجازی یک دایرکتوری خانگی پایدار ۵ گیگابایتی ارائه میدهد و روی فضای ابری گوگل اجرا میشود که عملکرد شبکه و احراز هویت را تا حد زیادی بهبود میبخشد. تمام کارهای شما در این آزمایشگاه کد را میتوان در یک مرورگر انجام داد. نیازی به نصب چیزی ندارید.
۳. قبل از شروع
فعال کردن API
خروجی:
در داخل Cloud Shell، مطمئن شوید که شناسه پروژه شما تنظیم شده است:
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.
۴. AlloyDB Omni را روی GCE مستقر کنید
برای استقرار AlloyDB Omni روی GCE، باید یک ماشین مجازی با پیکربندی و نرمافزار سازگار آماده کنیم. در اینجا مثالی از نحوه استقرار AlloyDB Omni روی یک ماشین مجازی مبتنی بر دبیان آورده شده است.
ایجاد یک ماشین مجازی GCE
ما به یک ماشین مجازی با پیکربندی قابل قبول برای CPU، حافظه و فضای ذخیرهسازی نیاز داریم. ما قصد داریم از ایمیج پیشفرض دبیان با حجم دیسک سیستم افزایش یافته به 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 استفاده میکنیم.
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
به ماشین مجازی ایجاد شده متصل شوید:
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:~$
دستور زیر را در ترمینال متصل خود اجرا کنید.
نصب داکر روی ماشین مجازی:
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:~$
۵. یک پایگاه داده آزمایشی آماده کنید
برای آزمایش موتور ستونی، باید یک پایگاه داده ایجاد کنیم و آن را با برخی دادههای آزمایشی پر کنیم.
ایجاد پایگاه داده
به ماشین مجازی AlloyDB Omni متصل شوید و یک پایگاه داده ایجاد کنید
در جلسه Cloud Shell دستور زیر را اجرا کنید:
gcloud config set project $(gcloud config get-value project)
به ماشین مجازی AlloyDB Omni متصل شوید:
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 دستور زیر را اجرا کنید:
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 دستور زیر را اجرا کنید:
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:~$
ما ۲۱۰۸۹۸ رکورد مربوط به ارائهدهندگان بیمه را در پایگاه داده خود بارگذاری کردهایم و میتوانیم برخی آزمایشها را انجام دهیم.
اجرای کوئریهای آزمایشی
با استفاده از psql به quickstart_db متصل شوید و timing را برای اندازهگیری زمان اجرای کوئریهایمان فعال کنید.
در ماشین مجازی GCE دستور زیر را اجرا کنید:
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=#
بیایید ۵ شهر برتر را بر اساس تعداد تولیدکنندگان بیمه که بیمههای حوادث و درمان میفروشند و مجوز آنها حداقل برای ۶ ماه آینده معتبر است، پیدا کنیم.
در جلسه 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
یک کوئری آزمایشی را ترجیحاً چندین بار اجرا کنید تا زمان اجرای قابل اعتمادی داشته باشید. میتوانیم ببینیم که میانگین زمان بازگشت نتیجه حدود ۹۴ میلیثانیه است. در مراحل بعدی قصد داریم موتور ستونی AlloyDB را فعال کنیم و ببینیم آیا میتواند عملکرد را بهبود بخشد یا خیر.
خروج از جلسه psql:
exit
۶. فعال کردن موتور ستونی
حالا باید موتور ستونی (Columnar Engine) را روی AlloyDB Omni خود فعال کنیم.
پارامترهای AlloyDB Omni را بهروزرسانی کنید
ما باید پارامتر نمونه "google_columnar_engine.enabled" را برای AlloyDB Omni خود به "روشن" تغییر دهیم و نیاز به راهاندازی مجدد دارد.
فایل postgresql.conf را در دایرکتوری /var/alloydb/config بهروزرسانی کنید و نمونه را مجدداً راهاندازی کنید.
در ماشین مجازی GCE دستور زیر را اجرا کنید:
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
در جلسه 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)
۷. مقایسه عملکرد
اکنون میتوانیم مخزن موتور ستونی را پر کنیم و عملکرد را تأیید کنیم.
جمعیت فروشگاه ستونی خودکار
به طور پیشفرض، عملیات جمعآوری اطلاعات فروشگاه هر ساعت انجام میشود. ما قصد داریم این زمان را به 10 دقیقه کاهش دهیم تا از انتظار جلوگیری شود.
در ماشین مجازی GCE دستور زیر را اجرا کنید:
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 دستور زیر را اجرا کنید:
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:~$
اشیاء موجود در فروشگاه ستونی را بررسی کنید. باید خالی باشد.
در ماشین مجازی GCE دستور زیر را اجرا کنید:
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 دستور زیر را اجرا کنید:
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=#
۱۰ دقیقه صبر کنید و بررسی کنید که آیا ستونهای جدول insurance_producers_licensed_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
زمان اجرا از ۹۴ میلیثانیه به ۱۴ میلیثانیه کاهش یافته است. اگر هیچ بهبودی مشاهده نمیکنید، میتوانید با بررسی نمای 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)
اکنون میتوانیم بررسی کنیم که آیا طرح اجرای پرسوجو از موتور ستونی (Columnar Engine) استفاده میکند یا خیر.
در جلسه 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 هرگز اجرا نشده و به جای آن از "Custom Scan (columnar scan)" استفاده شده است. این به ما کمک کرد تا زمان پاسخگویی را از ۹۴ به ۱۲ میلیثانیه بهبود بخشیم.
اگر بخواهیم محتوای خودکار را از موتور ستونی پاک کنیم، میتوانیم این کار را با استفاده از تابع 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 Store اضافه کنیم یا موجودیتهای مورد نیاز را در پرچمهای نمونه مشخص کنیم تا هنگام شروع نمونه، به طور خودکار بارگذاری شوند.
بیایید همان ستونهای قبلی را با استفاده از تابع SQL google_columnar_engine_add اضافه کنیم.
در جلسه 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
۵ تا ۱۰ ثانیه صبر کنید و اجرا کنید:
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 Store اضافه شدهاند.
۵ تا ۱۰ ثانیه صبر کنید و اجرا کنید:
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)
۸. محیط را تمیز کنید
حالا میتوانیم ماشین مجازی AlloyDB Omni خود را از بین ببریم.
حذف ماشین مجازی GCE
در 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
۹. تبریک
تبریک میگویم که آزمایشگاه کد را تمام کردی.
آنچه ما پوشش دادهایم
- نحوه استقرار AlloyDB Omni روی GCE VM در Google Cloud
- نحوه اتصال به AlloyDB Omni
- نحوه بارگذاری دادهها در AlloyDB Omni
- نحوه فعال کردن موتور ستونی
- نحوه بررسی موتور ستونی در حالت اتوماتیک
- نحوه پر کردن دستی فروشگاه ستونی
میتوانید اطلاعات بیشتر در مورد کار با موتور ستونی را در مستندات مطالعه کنید.
۱۰. نظرسنجی
خروجی: