درباره این codelab
1. مقدمه
در این کد لبه شما نحوه استقرار AlloyDB Omni و استفاده از Columnar Engine را برای بهبود عملکرد برای پرس و جوها یاد خواهید گرفت.
پیش نیازها
- درک اولیه از Google Cloud، کنسول
- مهارت های اولیه در رابط خط فرمان و پوسته گوگل
چیزی که یاد خواهید گرفت
- نحوه استقرار AlloyDB Omni در GCE VM در Google Cloud
- نحوه اتصال به AlloyDB Omni
- نحوه بارگیری داده ها در AlloyDB Omni
- نحوه فعال کردن Columnar Engine
- نحوه بررسی Columnar Engine در حالت خودکار
- نحوه پر کردن Columnar Store به صورت دستی
آنچه شما نیاز دارید
- یک حساب Google Cloud و پروژه Google Cloud
- یک مرورگر وب مانند کروم
2. راه اندازی و الزامات
تنظیم محیط خود به خود
- به Google Cloud Console وارد شوید و یک پروژه جدید ایجاد کنید یا از یک موجود استفاده مجدد کنید. اگر قبلاً یک حساب Gmail یا Google Workspace ندارید، باید یک حساب ایجاد کنید .
- نام پروژه نام نمایشی برای شرکت کنندگان این پروژه است. این یک رشته کاراکتری است که توسط API های Google استفاده نمی شود. همیشه می توانید آن را به روز کنید.
- شناسه پروژه در تمام پروژههای Google Cloud منحصربهفرد است و تغییرناپذیر است (پس از تنظیم نمیتوان آن را تغییر داد). Cloud Console به طور خودکار یک رشته منحصر به فرد تولید می کند. معمولاً برای شما مهم نیست که چیست. در اکثر کدها، باید شناسه پروژه خود را ارجاع دهید (معمولاً با نام
PROJECT_ID
شناخته می شود). اگر شناسه تولید شده را دوست ندارید، ممکن است یک شناسه تصادفی دیگر ایجاد کنید. از طرف دیگر، میتوانید خودتان را امتحان کنید، و ببینید آیا در دسترس است یا خیر. پس از این مرحله نمی توان آن را تغییر داد و در طول مدت پروژه باقی می ماند. - برای اطلاع شما، یک مقدار سوم وجود دارد، یک شماره پروژه ، که برخی از API ها از آن استفاده می کنند. در مورد هر سه این مقادیر در مستندات بیشتر بیاموزید.
- در مرحله بعد، برای استفاده از منابع Cloud/APIها باید صورتحساب را در کنسول Cloud فعال کنید . اجرا کردن از طریق این کد لبه هزینه زیادی نخواهد داشت. برای خاموش کردن منابع برای جلوگیری از تحمیل صورتحساب فراتر از این آموزش، میتوانید منابعی را که ایجاد کردهاید حذف کنید یا پروژه را حذف کنید. کاربران جدید Google Cloud واجد شرایط برنامه آزمایشی رایگان 300 دلاری هستند.
Cloud Shell را راه اندازی کنید
در حالی که Google Cloud را می توان از راه دور از لپ تاپ شما کار کرد، در این کد لبه از Google Cloud Shell استفاده خواهید کرد، یک محیط خط فرمان که در Cloud اجرا می شود.
از Google Cloud Console ، روی نماد Cloud Shell در نوار ابزار بالا سمت راست کلیک کنید:
تهیه و اتصال به محیط فقط چند لحظه طول می کشد. وقتی تمام شد، باید چیزی شبیه به این را ببینید:
این ماشین مجازی با تمام ابزارهای توسعه که شما نیاز دارید بارگذاری شده است. این یک فهرست اصلی 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 در مستندات بخوانید.