האצת שאילתות ניתוח באמצעות מנוע עמודות ב-AlloyDB Omni.

1. מבוא

ב-codelab הזה תלמדו איך לפרוס את AlloyDB Omni ואיך להשתמש ב-Columnar Engine כדי לשפר את הביצועים של שאילתות.

דרישות מוקדמות

הבנה בסיסית של Google Cloud Console
מיומנויות בסיסיות בממשק שורת הפקודה וב-Google Shell

מה תלמדו

איך פורסים את AlloyDB Omni במכונה וירטואלית של GCE ב-Google Cloud
איך מתחברים ל-AlloyDB Omni
איך טוענים נתונים ל-AlloyDB Omni
איך מפעילים את מנוע העמודות
איך בודקים את Columnar Engine במצב אוטומטי
איך מאכלסים את Columnar Store באופן ידני

מה תצטרכו

חשבון Google Cloud ופרויקט Google Cloud
דפדפן אינטרנט כמו Chrome

2. הגדרה ודרישות

הגדרת סביבה בקצב אישי

נכנסים ל-מסוף Google Cloud ויוצרים פרויקט חדש או משתמשים בפרויקט קיים. אם עדיין אין לכם חשבון Gmail או Google Workspace, אתם צריכים ליצור חשבון.

שם הפרויקט הוא השם המוצג של המשתתפים בפרויקט הזה. זו מחרוזת תווים שלא נמצאת בשימוש ב-Google APIs. תמיד אפשר לעדכן את המיקום.
מזהה הפרויקט הוא ייחודי לכל הפרויקטים ב-Google Cloud, והוא קבוע (אי אפשר לשנות אותו אחרי שהוא מוגדר). מסוף Cloud יוצר באופן אוטומטי מחרוזת ייחודית, ובדרך כלל לא צריך לדעת מה היא. ברוב ה-Codelabs, תצטרכו להפנות למזהה הפרויקט (בדרך כלל מסומן כ-PROJECT_ID). אם אתם לא אוהבים את המזהה שנוצר, אתם יכולים ליצור מזהה אקראי אחר. אפשר גם לנסות שם משתמש משלכם ולבדוק אם הוא זמין. אי אפשר לשנות את ההגדרה הזו אחרי השלב הזה, והיא תישאר לאורך הפרויקט.
לידיעתכם, יש ערך שלישי, מספר פרויקט, שחלק מממשקי ה-API משתמשים בו. במאמרי העזרה מפורט מידע נוסף על שלושת הערכים האלה.

בשלב הבא, תצטרכו להפעיל את החיוב במסוף Cloud כדי להשתמש במשאבי Cloud או בממשקי API של Cloud. השלמת ה-codelab הזה לא תעלה לכם הרבה, אם בכלל. כדי להשבית את המשאבים ולמנוע חיובים נוספים אחרי שתסיימו את המדריך הזה, תוכלו למחוק את המשאבים שיצרתם או למחוק את הפרויקט. משתמשים חדשים ב-Google Cloud זכאים לתוכנית תקופת ניסיון בחינם בשווי 300$.

הפעלת Cloud Shell

אפשר להפעיל את Google Cloud מרחוק מהמחשב הנייד, אבל ב-codelab הזה תשתמשו ב-Google Cloud Shell, סביבת שורת פקודה שפועלת בענן.

ב-מסוף Google Cloud, לוחצים על סמל Cloud Shell בסרגל הכלים שבפינה הימנית העליונה:

יחלפו כמה רגעים עד שההקצאה והחיבור לסביבת העבודה יושלמו. בסיום התהליך, אמור להופיע משהו כזה:

המכונה הווירטואלית הזו כוללת את כל הכלים שדרושים למפתחים. יש בה ספריית בית בנפח מתמיד של 5GB והיא פועלת ב-Google Cloud, מה שמשפר מאוד את הביצועים והאימות ברשת. אפשר לבצע את כל העבודה ב-codelab הזה בדפדפן. לא צריך להתקין שום דבר.

3. לפני שמתחילים

הפעלת ה-API

פלט:

ב-Cloud Shell, מוודאים שמזהה הפרויקט מוגדר:

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

אם הוא לא מוגדר בהגדרות של Cloud Shell, מגדירים אותו באמצעות הפקודות הבאות

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 במכונה וירטואלית מבוססת Debian.

יצירת מכונה וירטואלית ב-GCE

אנחנו צריכים לפרוס מכונה וירטואלית עם הגדרה מקובלת של מעבד (CPU), זיכרון ואחסון. אנחנו נשתמש בתמונת ברירת המחדל של Debian עם גודל דיסק המערכת שהוגדל ל-20GB כדי לאחסן קבצים של מסד הנתונים AlloyDB Omni.

אנחנו יכולים להשתמש ב-Cloud Shell שהופעל או בטרמינל עם Cloud 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:~$

מריצים את הפקודה הבאה בטרמינל המחובר.

מתקינים את Docker במכונה הווירטואלית:

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 נמחק. אם מאחסנים אותו בנפרד, אפשר לנהל את הקונטיינרים באופן עצמאי מהנתונים, ואפשר גם למקם אותו באחסון עם מאפייני קלט/פלט טובים יותר.

זו פקודה שיוצרת ספרייה בספריית הבית של המשתמש, שבה ימוקמו כל הנתונים:

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. הכנת מסד נתונים לבדיקה

כדי לבדוק את מנוע העמודות, צריך ליצור מסד נתונים ולמלא אותו בנתוני בדיקה.

יצירת מסד נתונים

התחברות למכונה הווירטואלית של AlloyDB Omni ויצירת מסד נתונים

בסשן Cloud Shell, מריצים את הפקודה:

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

מתחברים ל-VM של 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:~$

טעןנו למסד הנתונים שלנו 210898 רשומות לגבי סוכני ביטוח, ואנחנו יכולים לבצע בדיקות מסוימות.

הרצת שאילתות בדיקה

מתחברים ל-quickstart_db באמצעות psql ומפעילים את התזמון כדי למדוד את זמן הביצוע של השאילתות.

במכונה הווירטואלית של 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=#

בואו נאתר את 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 למצב on ב-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)

7. השוואת ביצועים

עכשיו אפשר לאכלס את החנות של מנוע העמודות ולאמת את הביצועים.

Automatic Columnar Store Population

כברירת מחדל, המשימה שמאכלסת את החנות פועלת כל שעה. אנחנו נצמצם את הזמן הזה ל-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=#

ממתינים 10 דקות ובודקים אם העמודות של הטבלה 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

זמן הביצוע ירד מ-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 מעולם לא בוצעה, ובמקומה נעשה שימוש ב-Custom Scan (סריקה עמודתית). השיפור הזה עזר לנו לקצר את זמן התגובה מ-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=#

אכלוס ידני של חנות עמודות

אפשר להוסיף עמודות למאגר של Columnar Engine באופן ידני באמצעות פונקציות SQL, או לציין את הישויות הנדרשות בדגלי המופע כדי לטעון אותן באופן אוטומטי כשהמופע מתחיל.

נוסיף את אותן עמודות כמו קודם באמצעות פונקציית ה-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

ממתינים 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

אחרי זה אפשר לראות שהעמודות נוספו באופן אוטומטי למאגר העמודות אחרי ההפעלה.

ממתינים 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. ניקוי הסביבה

עכשיו אפשר להשמיד את מכונת ה-VM של 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

9. מזל טוב

כל הכבוד, סיימתם את ה-Codelab.

הנושאים שנדון בהם

איך פורסים את AlloyDB Omni במכונה וירטואלית של GCE ב-Google Cloud
איך מתחברים ל-AlloyDB Omni
איך טוענים נתונים ל-AlloyDB Omni
איך מפעילים את מנוע העמודות
איך בודקים את Columnar Engine במצב אוטומטי
איך מאכלסים את Columnar Store באופן ידני

מידע נוסף על עבודה עם Columnar Engine זמין במאמרי העזרה.

10. סקר

פלט:

איך תשתמשו במדריך הזה?

רק קוראים את הטקסט

קוראים את הטקסט ומבצעים את התרגילים

האצת שאילתות ניתוח באמצעות מנוע עמודות ב-AlloyDB Omni. קל לארגן דפים בעזרת אוספים אפשר לשמור ולסווג תוכן על סמך ההעדפות שלך.

1. מבוא

דרישות מוקדמות

מה תלמדו

מה תצטרכו

2. הגדרה ודרישות

הגדרת סביבה בקצב אישי

הפעלת Cloud Shell

3. לפני שמתחילים

הפעלת ה-API

4. פריסת AlloyDB Omni ב-GCE

יצירת מכונה וירטואלית ב-GCE

התקנת AlloyDB Omni

5. הכנת מסד נתונים לבדיקה

יצירת מסד נתונים

יצירת טבלה עם נתונים לדוגמה

הרצת שאילתות בדיקה

6. הפעלת מנוע עמודות

עדכון פרמטרים של AlloyDB Omni

אימות של מנוע מבוסס-עמודות

7. השוואת ביצועים

Automatic Columnar Store Population

אכלוס ידני של חנות עמודות

8. ניקוי הסביבה

מחיקת מכונה וירטואלית של GCE

9. מזל טוב

הנושאים שנדון בהם

10. סקר

איך תשתמשו במדריך הזה?

האצת שאילתות ניתוח באמצעות מנוע עמודות ב-AlloyDB Omni.