1. Введение
В этой лаборатории вы узнаете, как развернуть AlloyDB Omni и использовать Columnar Engine для повышения производительности запросов.
Предварительные условия
- Базовое понимание Google Cloud, консоли.
- Базовые навыки работы с интерфейсом командной строки и Google Shell.
Что вы узнаете
- Как развернуть AlloyDB Omni на виртуальной машине GCE в облаке Google
- Как подключиться к AlloyDB Omni
- Как загрузить данные в AlloyDB Omni
- Как включить Columnar Engine
- Как проверить Columnar Engine в автоматическом режиме
- Как заполнить Columnar Store вручную
Что вам понадобится
- Учетная запись Google Cloud и проект Google Cloud
- Веб-браузер, например Chrome
2. Настройка и требования
Самостоятельная настройка среды
- Войдите в Google Cloud Console и создайте новый проект или повторно используйте существующий. Если у вас еще нет учетной записи Gmail или Google Workspace, вам необходимо ее создать .
- Имя проекта — это отображаемое имя для участников этого проекта. Это строка символов, не используемая API Google. Вы всегда можете обновить его.
- Идентификатор проекта уникален для всех проектов Google Cloud и является неизменяемым (невозможно изменить после его установки). Cloud Console автоматически генерирует уникальную строку; обычно тебя не волнует, что это такое. В большинстве лабораторий кода вам потребуется указать идентификатор проекта (обычно идентифицируемый как
PROJECT_ID). Если вам не нравится сгенерированный идентификатор, вы можете создать другой случайный идентификатор. Альтернативно, вы можете попробовать свой собственный и посмотреть, доступен ли он. Его нельзя изменить после этого шага и он сохраняется на протяжении всего проекта. - К вашему сведению, есть третье значение — номер проекта , которое используют некоторые API. Подробнее обо всех трех этих значениях читайте в документации .
- Затем вам необходимо включить выставление счетов в Cloud Console, чтобы использовать облачные ресурсы/API. Прохождение этой кодовой лаборатории не будет стоить много, если вообще что-то стоить. Чтобы отключить ресурсы и избежать выставления счетов за пределами этого руководства, вы можете удалить созданные вами ресурсы или удалить проект. Новые пользователи Google Cloud имеют право на участие в программе бесплатной пробной версии стоимостью 300 долларов США .
Запустить Cloud Shell
Хотя Google Cloud можно управлять удаленно с вашего ноутбука, в этой лаборатории вы будете использовать Google Cloud Shell , среду командной строки, работающую в облаке.
В Google Cloud Console щелкните значок Cloud Shell на верхней правой панели инструментов:
Подготовка и подключение к среде займет всего несколько минут. Когда все будет готово, вы должны увидеть что-то вроде этого:
Эта виртуальная машина оснащена всеми необходимыми инструментами разработки. Он предлагает постоянный домашний каталог объемом 5 ГБ и работает в Google Cloud, что значительно повышает производительность сети и аутентификацию. Всю работу в этой лаборатории кода можно выполнять в браузере. Вам не нужно ничего устанавливать.
3. Прежде чем начать
Включить 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.
4. Развертывание AlloyDB Omni на GCE
Для развертывания AlloyDB Omni на GCE нам необходимо подготовить виртуальную машину с совместимой конфигурацией и программным обеспечением. Вот пример того, как развернуть AlloyDB Omni на виртуальной машине на базе Debian.
Создайте виртуальную машину GCE
Нам необходимо развернуть виртуальную машину с приемлемой конфигурацией процессора, памяти и хранилища. Мы собираемся использовать образ 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.
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. Раздельное хранение позволяет вам управлять контейнерами независимо от ваших данных и при необходимости размещать их в хранилище с лучшими характеристиками ввода-вывода.
Вот команда, создающая каталог в домашнем каталоге пользователя, где будут размещены все данные:
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 и создайте базу данных.
В сеансе 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:~$
Мы загрузили 210898 записей о страховых компаниях в нашу базу данных и можем провести некоторое тестирование.
Запуск тестовых запросов
Подключитесь к базе данных faststart_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. Сравнение производительности
Теперь мы можем заполнить хранилище столбчатого движка и проверить производительность.
Автоматическое заполнение столбчатого магазина
По умолчанию задание по заполнению хранилища выполняется каждый час. Мы собираемся сократить это время до 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:~$
Проверьте объекты в Columnar Store. Оно должно быть пустым.
В виртуальной машине 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)
Теперь мы можем проверить, использует ли план выполнения запроса 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 никогда не выполнялась, а вместо нее использовалось «Выборочное сканирование (сканирование по столбцам)». Это помогло нам улучшить время отклика с 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 Store вручную с помощью функций 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
И после этого мы видим, что столбцы были добавлены в Columnar Store автоматически после запуска.
Подождите 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.
Удалить виртуальную машину 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. Поздравления
Поздравляем с завершением работы над кодом.
Что мы рассмотрели
- Как развернуть AlloyDB Omni на виртуальной машине GCE в облаке Google
- Как подключиться к AlloyDB Omni
- Как загрузить данные в AlloyDB Omni
- Как включить Columnar Engine
- Как проверить Columnar Engine в автоматическом режиме
- Как заполнить Columnar Store вручную
Подробнее о работе с Columnar Engine вы можете прочитать в документации .
10. Опрос
Выход: