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