Mysql

Совсем недавно, я стал разработчиком модулей для CS Cart. Случилось это по воле случая: меня взяли на работу в Петербургскую сеть интернет магазинов, торгующих вейпами и всякими интересными штуками для удовлетворения потребностей физического характера страждущих пар и одиночек (кто не понял - еще не дорос ). Оба интернет магазина развернуты на двух витринах с разными доменами, но одной админкой и общей базой данных. Что же с ней не так? Думаю о CMS написано много, но я добавлю свою ложку дегтя в бочку с дегтем .

Путешествие в модуль через лес директорий

В процессе разработки первого модуля для этой платформы, я столкнулся со множеством проблем, которых, как я полагал, имея опыт работы с ООП, а также с CMS MODX Revo, быть не должно. Первое, что бросилось в глаза - это очень сложная и запутанная структура модуля:

root/ app/   addons/                                     <- Модули и расширения     [id_модуля]/                              <- Папка модуля        controllers/                          <- Расширение контроллеров          backend/                           <- Панель администратора            [ваш_контроллер].php            <- Новый контроллер            [контроллер].pre.php            <- Расширение перед контроллером            [контроллер].post.php           <- Расширение после контроллером          common/                            <- Общие контроллеры            [ваш_контроллер].php            [контроллер].pre.php            [контроллер].post.php          frontend/                          <- Контроллеры витрины             [ваш_контроллер].php             [контроллер].pre.php             [контроллер].post.php        database/                             <- MySQL файлы        schemas/                              <- Расширение PHP схем          [папка_схем]/                      <- Папка схемы (тип схемы)             [название_схемы].post.php       <- Расширение после схемы        Tygh/                                 <- Классы          Shippings/                         <- Доставки            Services/                       <- Службы доставки               [СлужбаДоставки].php         <- Ваша служба доставки          [ВашКласс].php                     <- Любой новый класс        addon.xml                             <- Главный файл модуля        config.php                            <- Константы        func.php                              <- Функции и расширения хуков        init.php                              <- Подключение хуков design/   backend/                                    <- Шаблоны панели администратора    css/                                      <- Стили панели администратора     addons/       [id_модуля]/                          <- Ваш модуль         styles.css                          <- Ваши стили         styles.less    mail/                                     <- Email и шаблоны счетов     templates/       addons/                               <- Модули и аддоны         [id_модуля]/                        <- Папка модуля           hooks/                            <- Подключение к хукам            [тип_хука]/                     <- Папка хука              [название_хука].pre.tpl       <- Код перед хуком              [название_хука].post.tpl      <- Код после хука              [название_хука].override.tpl  <- Переписать хук           [шаблон_письма]_subj.tpl/           [шаблон_письма].tpl/    media/                                    <- Статические данные     images/       addons/         [id_модуля]/                        <- Изображения вашего модуля           изображение_1.jpg/           изображение_2.png/    templates/                                <- Шаблоны      addons/        [id_модуля]/          hooks/                              <- Подключение к хукам           index/                            <- Папка хука            scripts.post.tpl                <- Хук подключения вашего скрипта            styles.post.tpl                 <- Хук подключения вашего стиля           [тип_хука]/             [название_хука].pre.tpl         <- Ваш код перед хуком             [название_хука].post.tpl        <- Ваш код после хука             [название_хука].override.tpl    <- Ваш код перепишет хук          views/                              <- Собственная страница           [ваш_контроллер]/                 <- Контроллер             [режим_контроллера].tpl         <- Режим (mode) контроллера          overrides/                          <- Переписать любой шаблон            ...                               <- Создайте нужную структуру     themes/                                     <- Дизайн витрины  темы     [название_темы]/                          <- Название темы       css/                                    <- Стили        addons/          [id_модуля]/            styles.css                        <- Ваш стиль CSS            styles.less                       <- Ваш стиль LESS       mail/                                   <- Шаблоны писем и счетов        templates/          addons/            [id_модуля]/              hooks/                          <- Раширение через хуки               [тип_хука]/                 [название_хука].pre.tpl                 [название_хука].post.tpl                 [название_хука].override.tpl              [шаблон_письма]_subj.tpl/       <- Шаблон темы письма              [шаблон_письма].tpl/            <- Шаблон письма       media/                                  <- Статические данные        images/          addons/                             <- Изображения модуля            [id_модуля]/              изображение_1.jpg/              изображение_2.png/       templates/                              <- Шаблоны         addons/           [id_модуля]/                        <- Ваш модуль             hooks/                            <- Расширение хуков              index/                          <- Папка хука               scripts.post.tpl              <- Хук подключения вашего скрипта               styles.post.tpl               <- Хук подключения вашего стиля              [тип_хука]/                     <- Папка хука                [название_хука].pre.tpl       <- Ваш код перед хуком                [название_хука].post.tpl      <- Ваш код после хука                [название_хука].override.tpl  <- Перезаписать хук целиком             views/                            <- Новая страница              [ваш_контроллер]/               <- Папка вашего контроллера                [режим_контроллера].tpl       <- Шаблон для режима контроллера             overrides/                        <- Переписать любой шаблон темы               ...                             <- Файл который нужно переписать js/                                            <- Скрипты модуля  addons/    [id_модуля]/      func.js/ var/                                           <- Хранилище шаблонов модуля   themes_repository/                           <- Используется при установке     [название_темы]/       ...

Может показаться, что модуль имеет логичную иерархию внутри своей структуры, но, иногда, следуя по документации, случаются баги, которые не должны были появиться. Например: был у меня кейс, когда обращаясь к контроллеру через AJAX функцию, встроенную в класс CMS JS упорно не хотела работать с моим контроллером, хотя сделано всё было четко по документации. Поискав информацию и обратившись к комьюнити, состоящем, в основном из 3-4 активных завсегдатаев-разработчиков, я понял, что даже сами разработчики этой платформы не могут ответить на вопрос о том, почему их функция ведет себя некорректно.

Модуль в результате был закончен, но в обход документации, что я считаю своим упущением и продолжаю искать пути решения данной проблемы, с надеждой выпустить апдейт в соответствии с документацией.

То что мертво - труп, но потыкать палкой нужно

Второй задачей, которую поставило руководство, являлась оптимизация сайтов этого магазина. Я взялся за нее без энтузиазма, понимая, что это мертворожденное существо, и мы с коллегой путем мучений и отключения всего того зоопарка модулей, что были установлены до моего появления со словами: "А че бы нет?!" - добились улучшенных показателей Google и в LightHouse, но прироста, в 20 единиц на одном сайте и 10 на другом, было не достаточно. Тогда я полез смотреть БД более детально. Поняв, что БД у данной CMS - набор несвязанных друг с другом таблиц, я понял, что все взаимодействия с базой и связки данных проходят через PHP, что, как я считаю, неправильно. Почему сделано именно так? - всё просто: CMS создавалась в 2003-2004 годах, и в качестве движка для СУБД использовался MyISAM.

MyISAM - сам по себе, довольно медленный движок и он не рассчитан на 50 000 (!) товаров (о количестве поговорим позже). Более того связывание таблиц этом движке реализовано не так хорошо как, скажем, в том же InnoDB. Из-за этого сервер начинает очень страдать при одновременном обращении 500 - 1000 пользователей.

Теперь поговорим о количестве товаров. Откуда 50 000 спросите вы? "Потому что" - отвечу я. Дело в том, что одну из витрин отдали на SEO какому то подозрительному фрилансеру из Беларуси. Странность его суждений заключается в разнообразных уловках и ухищрениях. Например: для улучшения видимости сайта он просил коллегу создать несуществующую номенклатуру и каждый день подгружать несуществующий товар. Аргументировал он это тем, что пользователи будут искать товары из этого несуществующего списка и попадать к нам на сайт, на этот товар. Понятно, что пользователь уйдет сразу же после этого, так как товара в наличии нет и никогда не было и не будет. Сайт ни капельки не продвигается, а руководство с упорством продолжает считать мнение данного "спеца" авторитетней мнения штатного программиста и контент-менеджера.

Такое количество номенклатуры - аномально и это, в свою очередь, также подвергает оба сайта зависаниям, лагам и другим неприятным вещам.

Нужна скрепка? Плати 100 баксов

Последняя проблема, которую я освещу в данной статье - это плата за любое мелкое допиливание этого "зомби". Хочешь стандартный функционал cron в панеле админа - плати. Подключить метрику не через утиную гуску, а так, чтобы она не нагружала клиент - плати. И другие малозначимые, но иногда важные изменения - стоят денег. Ценник, как правило, начинается от 100$ за модуль. Да, разработчикам, как и мне, хочется кушац, но у меня сложилось впечатление, что CMS и её стандартные модули специально не доведены до нормального состояния. А так как структуру всех классов и методов знают только создатели данной CMS, то они и являются, по сути, монополистами на рынке, так как любой фрилансер или штатный проггер, не сможет нормально сделать модуль с первого раза, используя недописанную и костыльную документацию, что предлагается на данный момент.

Заключение

Я не верю, что ситуация с данной CMS когда нибудь изменится и мне, действительно, жаль тех, кто имея огромную номенклатурную базу сидит на ней и платит за все доделки баснословные деньги. Но хочется верить, что, хотя бы в моем магазине, я сумею побороть некоторые её недостатки.

Надеюсь, статья Вам была интересна. Я буду писать еще о своих изысканиях в этой CMS или о разработке модулей для неё.

Лирическое отступление

Уже более года в качестве медиацентра использую KODI. Начиналось всё с каких-то базовых вещей, но даже от такого функционала я был в полном восторге. Просмотр ТВ, фильмов и сериалов теперь не выглядит каким-то квестом, а все мои домашние также полюбили KODI, и я даже не представляю, как раньше могли справляться без него.

Но я все больше погружался в его возможности и постепенно наращивал функционал. Реализовал, казалось бы, простые вещи, но, если сложить все мелкие детали воедино понимаешь, насколько все комфортно, логично и продумано, а главное - функционально. Именно такую оценку я даю всему конечному результату. Для кого-то это будет вовсе не так, конечно, ведь у всех свои фломастеры. Но после первых публикаций я получил массу положительных откликов от аудитории и гостей хабра, и понял, что кому-то пригодился мой опыт. Это приятно. Поэтому, хочется продолжать.

Если вы пропустили предыдущие публикации обязательно загляните в них, возможно и для вас найдется что-то интересное. Если в двух словах установили и настроили с нуля ОС и KODI, настроили просмотр торрент-контента, YouTube, IPTV. Поговорили об управлении с других устройств, резервном копировании, анализе трафика и даже научили KODI запускать ретро-игры.

Все предыдущие публикации:

KODI: собираем удобный и функциональный медиацентр для дома. Часть 1
KODI: собираем удобный и функциональный медиацентр для дома. Часть 2
KODI: собираем удобный и функциональный медиацентр для дома. Часть 3. Ретро-игры
KODI: собираем удобный и функциональный медиацентр для дома. Часть 4. Архив IPTV
KODI: собираем удобный и функциональный медиацентр для дома. Часть 5. Яндекс.Музыка

Зачем это все затевалось?

Среди медиаустройств, в моем домашнем обиходе два телевизора. Основной в гостиной работает с KODI, второй на кухне под управлением Tizen OS. Мысль о том, что последнего также нужно посадить на KODI возникла спустя пару недель после запуска медиацентра на основном телевизоре. Но руки все никак не доходили

Каждый раз, переключая ужасного качества каналы кабельного ТВ на кухне, я вспоминал о своей затее. И вот пришло время воплотить ее в жизнь. Для этой задачи в поднебесной был заказан Raspberry Pi 3 и все та же аэромышь, как и для основного телевизора (подробнее о ней в первой части).

Итого, в гостиной KODI на базе неттопа с Kubuntu 20.04 на борту, на кухне LibreELEC на базе третьей малинки.

Кухонный медиацентр должен выполнять всего две задачи:
просмотр IPTV. Буду использовать все тот же сервис ilook и дополнение PVR IPTV Simple Client. К слову, сервис позволяет использовать плейлист на двух устройствах без дополнительной платы за тариф.
просмотр торрент-видео. Потому как локальной библиотеки фильмов и сериалов у меня нет.

О том, как установить LibreELEC на малинку описывать, думаю, не стоит. А как подключить IPTV и Elementum для просмотра торрентов я рассказывал в другой публикации.

Общее понимание архитектуры есть, причем же здесь MariaDB в заголовке? Представьте, вы смотрите фильм в гостиной, идете обедать на кухню и продолжаете просмотр ровно с того места, на котором остановились. Здорово, не правда ли?

KODI позволяет реализовать синхронизацию медиатеки на разных устройствах. Для этого потребуется MySQL-сервер, на котором и будет храниться эта медиатека. Сервер может быть поднят на совершенно сторонней машине, хоть под управлением Windows. В моем случае, основной медиацентр работает 24/7, аппаратные ресурсы позволяют сервером назначает его, на нем и будем поднимать базу данных.

Надеюсь, суть и прелести такой архитектуры домашних медиацентров понятна.

Подготовка серверной части. MariaDB

Прочитав Wiki на официальном сайте KODI, решил, что все довольно просто и понятно бери и делай. Но на практике оказалось все иначе БД не наполняется моей медиатекой, в логах ошибки не поддаются пониманию или записей нет вообще, от того понять, что сделано не так достаточно трудно. Второе устройство подключать к БД смысла нет синхронизировать нечего.

В комментариях к предыдущей публикации был затронут этот вопрос и @vyacheslavteplyakov дал понять, что Wiki не обновляется и информация, вероятно, устарела. Но и указал на основные подводные камни и как их обойти.

Особенности:

• Использовать MariaDB;

• Медиатека должна быть размещена в сетевом каталоге;

• Все пути к медиатеке на всех устройствах должны быть абсолютные;

• Сетевые каталоги NFS или SMB, если вынуждены использовать NTFS, то только с авторизацией по учетной записи с паролем;

• Версии KODI на всех устройствах должны быть одинаковы.

С задачей и подводными камнями разобрались приступаем к работе. Напомню, сервером у нас будет KODI на неттопе с Kubuntu 20.04.

Устанавливаем сервер MariaDB

sudo apt updatesudo apt install mariadb-server

Cледующим шагом является запуск встроенного скрипта безопасности. Этот скрипт меняет ряд наименее защищенных опций, используемых по умолчанию, для таких функций, как, например, удаленный вход для пользователя root и тестовые пользователи.

Запускаем скрипт безопасности

sudo mysql_secure_installation

Откроется серия диалогов, где можно внести некоторые изменения в параметры безопасности установки MariaDB. Параметры установите, исходя из собственной безопасности. Учитывая, что моя БД будет наполняться лишь медиатекой и находится она в изолированной домашней сети сделал так:

root@kodi-pc:/# sudo mysql_secure_installationNOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!In order to log into MariaDB to secure it, we'll need the currentpassword for the root user.  If you've just installed MariaDB, andyou haven't set the root password yet, the password will be blank,so you should just press enter here.Enter current password for root (enter for none):

Пароль не задаем, нажимаем ENTER.

Setting the root password ensures that nobody can log into the MariaDBroot user without the proper authorisation.Set root password? [Y/n]

Отклоняем (N).

By default, a MariaDB installation has an anonymous user, allowing anyoneto log into MariaDB without having to have a user account created forthem.  This is intended only for testing, and to make the installationgo a bit smoother.  You should remove them before moving into aproduction environment.Remove anonymous users? [Y/n]

Удаляем (Y).

Normally, root should only be allowed to connect from 'localhost'.  Thisensures that someone cannot guess at the root password from the network.Disallow root login remotely? [Y/n]

Отклоняем (N).

By default, MariaDB comes with a database named 'test' that anyone canaccess.  This is also intended only for testing, and should be removedbefore moving into a production environment.Remove test database and access to it? [Y/n]

Удаляем (Y).

Reloading the privilege tables will ensure that all changes made so farwill take effect immediately.Reload privilege tables now? [Y/n]

Соглашаемся (Y).

Теперь создадим пользователя, из-под которого будут работать с БД наши медиацентры. Для создания пользователя kodi с паролем kodi запускаем оболочку MariaDB и выполняем команду

sudo mariadbGRANT ALL ON *.* TO 'kodi'@'localhost' IDENTIFIED BY 'kodi' WITH GRANT OPTION;

Разрешаем доступ с любого хоста ко всем базам данных на сервере для пользователя kodi

GRANT ALL PRIVILEGES ON *.* TO kodi@'%' IDENTIFIED BY 'kodi';

Очищаем привилегии, чтобы они были сохранены и доступны в текущем сеансе

FLUSH PRIVILEGES;

Оболочку MariaDB можно закрывать, выполнив команду

exit

Для организации доступа вне локального хоста, необходимо указать порт 3306 и bind-address 0.0.0.0. Открываем конфигурационный файл MariaDB

sudo mcedit /etc/mysql/mariadb.conf.d/50-server.cnf

Раскомментировать параметр

port = 3306

Для параметра bind-address установить 0.0.0.0 (вместо 127.0.0.1)

bind-address = 0.0.0.0

Для применения изменений перезапускаем MySQL-сервер

sudo service mysql restart

На данном этапе можно проверить, верно ли мы все сделали. Для этого лучше всего подключиться к нашему серверу со сторонней машины. Это гарантирует работоспособность доступа к БД вне локального хоста.

Как вариант - MySQL Workbench.

Создаем новое подключение:
Connection Method - Standart (TCP/IP)
Hostname 192.168.0.50 (заменить на адрес вашего сервера)
Port 3306
Username kodi (имя пользователя, если создавали своего)

Нажимаем Test Connection, вводим пароль и в случае, если все корректно получаем соответствующее сообщение:

Подключение к серверу также должно быть успешным. Вы должны увидеть ничего. Это тоже хороший знак. Сейчас мы и займемся наполнением БД.

Подготовка серверной части. KODI

Как и говорил выше, медиатека должна быть размещена в сетевом каталоге, так как она будет одна на все устройства. В моем случае библиотека фильмов и сериалов состоит не из локальных медиафайлов, а наполняется из торрент-трекеров дополнения Elementum.

Библиотеку мы настраивали еще в первой части. Никуда ее не переносим, пути оставляем прежние:
/mnt/kodi/library/Movies библиотека фильмов
/mnt/kodi/library/Shows библиотека сериалов

Необходимо лишь расшарить каталог /mnt/kodi/library. Конфигурируем samba

sudo mcedit /etc/samba/smb.conf

В конец конфигурационного файла вставляем:

[library]comment = librarypath = /mnt/kodi/library/browsable = yeswritable = yesguest ok = yesread only = noforce user = nobodyforce group = nogroupforce create mode = 0777force directory mode = 0777

И перезапускаем сервис samba

sudo /etc/init.d/smbd restart

Теперь наша библиотека будет доступна всем устройствам в домашней сети.

Важно! В источниках медиатеки также необходимо указать абсолютный путь к директории с файлами библиотеки! Хоть она и находится локально на этом устройстве (сервере).

Переходим в Настройки/Медиа/Медиатека/Видео и в существующих источниках Movies и Shows изменяем пути на нужные, выбрав Добавить сетевой адрес. В качестве логина и пароля используйте данные вашей учетной записи в Kubuntu.

После изменения путей, источники будут отображаться и в KODI, как сетевые.

Теперь дадим понять KODI, что мы хотим работать с базой данных. Для этого в файле advancedsettings.xml (/home/имя_пользователя/.kodi/userdata/) добавить:

<advancedsettings>  <videodatabase>    <type>mysql</type>    <host>192.168.0.50</host>    <port>3306</port>    <user>kodi</user>    <pass>kodi</pass>  </videodatabase>  <videolibrary>    <importwatchedstate>true</importwatchedstate>    <importresumepoint>true</importresumepoint>  </videolibrary></advancedsettings>

Если файл advancedsettings.xml отсутствует создайте его вручную. Параметры изменить в соответствии с вашими настройками, где:
Host IP-адрес вашего MySQL-сервера;
User имя пользователя MariaDB;
Pass пароль пользователя MariaDB.

На стороне сервера всё готово. Можно проверить. Перезапускаем KODI и, в зависимости от объема вашей медиатеки, ждем какое-то время, пока KODI ее обработает.

Информация о моей медиатеке:
Фильмов 322
Сериалов 68

Размер - 380 Кб

Общее количество файлов (nfo и strm) 3826

Моя медиатека обрабатывалась порядка 10 минут. По завершении обновления медиатеки давайте посмотрим на нашу БД. Снова подключаемся к серверу с помощью MySQL Workbench.

Как видим, KODI самостоятельно создал БД MyVideos119 и наполнил ее всеми нашими фильмами и сериалами. Например, в таблице Movie - фильмы. Значит, мы все сделали правильно.

После завершения импорта медиатеки в БД, можно еще оценить и ее ресурсопотребление. Моя медиатека заняла в ОЗУ чуть более 100 Мб. Это дает понять, что, даже значительный рост количества фильмов и сериалов, не скажется на производительности основного моего медиацентра.

Настройка клиентской части

Настройки других устройств, которые необходимо синхронизировать с сервером, очень просты:

• advancedsettings настраиваем, также как на сервере;

• добавляем источники в Настройки/Медиа/Медиатека/Видео также, как и на сервере, указывая абсолютные сетевые пути;

• если вы, как и я используете дополнение Elementum в его настройках также изменить пути на абсолютные до сервера (путь к библиотеке и путь к торрент-файлам). Таким образом, где бы вы не начали смотреть (скачивать) фильм, сохранится он на сервере. Да и в моем случае, не рационально было бы для малинки иметь свое файловое хранилище.

Для упрощения настройки своей малинки я просто перенес конфигурационные файлы с основного медиацентра:

/home/kodi/.kodi/userdata/advancedsettings.xml
/home/kodi/.kodi/userdata/sources.xml
/home/kodi/.kodi/userdata/addon_data/plugin.video.elementum/settings.xml
/home/kodi/.kodi/userdata/addon_data/script.elementum.burst/settings.xml

По завершении настройки, перезапускаем KODI и наблюдаем, как пустая библиотека начинает наполняться и полностью соответствует библиотеке на основном медиацентре, вплоть до просмотренных или не просмотренных серий, а также просматриваемые сейчас и даже с временной меткой, на которой был остановлен просмотр.

Проделав этот путь, понимаешь, что все было так просто, но в тоже время не так и очевидно с первого взгляда. Надеюсь, кому-то мой опыт поможет избежать зря потраченных сил и времени.

Если мой медиацентр обзаведется еще какими-то интересными возможностями - обязательно сделаю отчет в следующих публикациях.

Всем хорошего времяпрепровождения с медиацентром KODI!

Введение

Zabbix поддерживает несколько баз данных, но под рассмотрение попали только MySQL и PostgreSQL, как наиболее подходящие под мою установку. PostgreSQL с его repomgr и pgbouncer или каким-нибудь stolon с одной стороны и MySQL Group Replication с другой. Из-за использования MySQL в текущей конфигурации и тяге к стандартной комплектации, выбор пал на второй вариант.

Так что же такое MySQL Group Replication. Как видно из названия, это группа серверов, хранящая одинаковый набор данных. Максимальное количество узлов в группе ограничивается 9-ю. Может работать в режиме single-primary или multi-primary. Но самое интересное всё работает автоматически, будь то выборы нового ведущего сервера, определение поломанного узла, Split-brain или восстановление БД. Поставляется данный функционал в качестве плагинов group_replication и mysql_clone, связь происходит по Group Communication System протоколу в основе которого лежит алгоритм Паксос. Поддерживается данный тип репликации с версий 5.7.17 и 8.0.1.

Моя текущая установка работает на Zabbix 5.0 LTS и MySQL 5.7, миграцию будем проводить с повышением версии MySQL на 8.0, так интереснее ).

Мониторинг репликации

Да да. Это как TDD, только в администрировании, сначала нужно подготовить мониторинг, чтобы новый кластер сразу попал на радары нашей системы мониторинга и не одна проблема не ускользнула от её зоркого взгляда. Так как у вас еще нет групповой репликации (ГР), то вывод команд указанных ниже будет пустым, поэтому я привожу пример выводов с работающего кластера.

Основным источником информации о статусе узлов служит команда:

+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+| CHANNEL_NAME              | MEMBER_ID                           | MEMBER_HOST  | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+| group_replication_applier | 500049c2-99b7-11e9-8d36-e4434b5f9d0c | example1.com |      3306   | ONLINE       | SECONDARY   | 8.0.13         || group_replication_applier | 50024be2-9889-11eb-83da-e4434ba03de0 | example2.com |      3306   | ONLINE       | PRIMARY     | 8.0.13         || group_replication_applier | 500b2035-986e-11eb-a9f8-564d00018ad1 | example3.com |      3306   | ONLINE       | SECONDARY   | 8.0.13         |+---------------------------+--------------------------------------+--------------+-------------+--------------+-------------+----------------+

Значение колонки MEMBER_STATE может быть разное. Статусы можно посмотреть на странице официальной документации https://dev.mysql.com/doc/refman/8.0/en/group-replication-server-states.html. Если сервер к примеру корректно перезагружен или выключен, он исчезнет из этой таблицы, поэтому желательно знать общее количество узлов в вашей схеме и следить за их количеством.

Дополнительно нужно следить за производительностью каждого узла:

*************************** 1. row ***************************                              CHANNEL_NAME: group_replication_applier                                   VIEW_ID: 16178860996821458:41                                 MEMBER_ID: 500049c2-99b7-11e9-8d36-e4434b5f9d0c               COUNT_TRANSACTIONS_IN_QUEUE: 0                COUNT_TRANSACTIONS_CHECKED: 75715997                  COUNT_CONFLICTS_DETECTED: 0        COUNT_TRANSACTIONS_ROWS_VALIDATING: 1957048        TRANSACTIONS_COMMITTED_ALL_MEMBERS: 500049c2-99b7-11e9-8d36-e4434b5f9d0c:1-1821470279,500293cf-594c-11ea-aafd-e4434ba03de0:1-622868371,5000d25c-059e-11e8-822b-564d00018ad1:1-140221041,c9aae4fb-97a6-11eb-89d1-e4434b5f9d0c:1-125382195            LAST_CONFLICT_FREE_TRANSACTION: c9aae4fb-97a6-11eb-89d1-e4434b5f9d0c:125471159COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0         COUNT_TRANSACTIONS_REMOTE_APPLIED: 5664         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 75710337         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0*************************** 2. row ***************************                              CHANNEL_NAME: group_replication_applier                                   VIEW_ID: 16178860996821458:41                                 MEMBER_ID: 50024be2-9889-11eb-83da-e4434ba03de0               COUNT_TRANSACTIONS_IN_QUEUE: 0                COUNT_TRANSACTIONS_CHECKED: 75720452                  COUNT_CONFLICTS_DETECTED: 0        COUNT_TRANSACTIONS_ROWS_VALIDATING: 1955202        TRANSACTIONS_COMMITTED_ALL_MEMBERS: 500049c2-99b7-11e9-8d36-e4434b5f9d0c:1-1821470279,500293cf-594c-11ea-aafd-e4434ba03de0:1-622868371,5000d25c-059e-11e8-822b-564d00018ad1:1-140221041,c9aae4fb-97a6-11eb-89d1-e4434b5f9d0c:1-125377993            LAST_CONFLICT_FREE_TRANSACTION: c9aae4fb-97a6-11eb-89d1-e4434b5f9d0c:125470919COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0         COUNT_TRANSACTIONS_REMOTE_APPLIED: 75711354         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 9105         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0*************************** 3. row ***************************                              CHANNEL_NAME: group_replication_applier                                   VIEW_ID: 16178860996821458:41                                 MEMBER_ID: 500b2035-986e-11eb-a9f8-564d00018ad1               COUNT_TRANSACTIONS_IN_QUEUE: 38727                COUNT_TRANSACTIONS_CHECKED: 49955241                  COUNT_CONFLICTS_DETECTED: 0        COUNT_TRANSACTIONS_ROWS_VALIDATING: 1250063        TRANSACTIONS_COMMITTED_ALL_MEMBERS: 500049c2-99b7-11e9-8d36-e4434b5f9d0c:1-1821470279,500293cf-594c-11ea-aafd-e4434ba03de0:1-622868371,5000d25c-059e-11e8-822b-564d00018ad1:1-140221041,c9aae4fb-97a6-11eb-89d1-e4434b5f9d0c:1-125382195            LAST_CONFLICT_FREE_TRANSACTION: c9aae4fb-97a6-11eb-89d1-e4434b5f9d0c:125430975COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 47096         COUNT_TRANSACTIONS_REMOTE_APPLIED: 49908155         COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0         COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 03 rows in set (0.00 sec)

Тут нас интересуют в первую очередь COUNT_TRANSACTIONS_IN_QUEUE, похож на Seconds_Behind_Master в асинхронной репликации. Как видно на третьем сервере количество транзакций в очереди слишком большое, а это повод начать разбираться что же здесь не так.

Резервное копирование

Я надеюсь, у вас делается регулярное резервное копирование базы данных (БД) и конечно на специально выделенный сервер. Так же желательно убедиться перед миграцией, что на всех узлах с БД есть запас по месту, превышающий в два с половиной раза размер каталога с базой. Это нам понадобится для создания локальной резервной копии, на случай если что-то пойдёт не так, и позволит нам вернуть всё как было. Если у вас файловая система с поддержкой моментальных снимков, не в чём себе не отказывайте, но свободного места должно быть много, так как в самые тяжёлые таблицы будет добавлен дополнительный столбец, а это повлечёт за собой их пересоздание.

План миграции

В идеальном варианте, нужно провести тестовую миграцию на каком-нибудь тестовом кластере. Но если всё делать правильно и не пренебрегать созданием резервных копий, то всегда можно будет откатиться назад. План действий на случай непредвиденных обстоятельств, тоже желательно написать.

Если всё идёт гладко:

1. Пропиливаем необходимые дырки в файрволле (для общения узлов между собой нужно открыть TCP 33061 порт). Выписываем необходимые сертификаты;

2. Собираем репозиторий с MySQL 8.0 (FreeBSD, Poudriere - у каждого свои причуды);

3. В системе мониторинга переводим серверы в режим обслуживания, уведомляем всех пользователей Zabbix (чтобы никто не удивлялся);

4. Выключаем репликацию на всех узлах, которые были Secondary для этого сервера (мы же не хотим, чтобы какие-нибудь наши изменения улетели на действующие сервера). Выключаем репликацию на текущем узле;

5. Выключаем MySQL 5.7 сервер на первом подопытном узле;

6. Делаем резервное копирование БД с сохранением атрибутов файлов (сохраняем рядом, чтобы быстро восстановить базу);

7. Обновляем пакеты на новую версию с новыми зависимостями;

8. Запускаем MySQL 8.0 сервер (mysql_upgrade не нужен, с 8 версии это действо происходит автоматически);

9. Добавляем первичные ключи в таблицы, в которых их нет (требования групповой репликации, иначе операции добавления, удаления и т. д. работать не будут). Операция длительная, зависит от размера таблиц и производительности сервера;

10. Включаем репликацию на этом сервере, отреплецируем все изменения, выключаем репликацию (подтянем изменённые данные с действующего сервера, накопившееся за время наших манипуляций);

11. Сбрасываем все упоминания об асинхронной репликации на данном сервере (команда RESET SLAVE ALL;);

12. Настраиваем групповую репликацию и проверяем всё ли работает;

13. Переключаем Zabbix сервер и Zabbix фронтенд на БД с ГР;

14. Настраиваем групповую репликацию на других узлах (делаем шаги с 4 по 8, только с удалением каталога с БД перед 8 шагом, т. к. нам нужна чистая установка);

15. Перенастраиваем мониторинг;

16. Переделываем Ansible Playbook'и и конфигурационные файлы;

17. Меняем скрипты и задачи по переключению мастера;

18. Настраиваем HADNS;

19. Обновляем документацию;

На непредвиденный случай:

1. Останавливаем MySQL сервер;

2. Возвращаем предыдущие версии пакетов;

3. Удаляем каталог с БД и восстанавливаем из локальной резервной копии, запускаем MySQL сервер;

4. Настраиваем асинхронную репликацию;

Откатываемся бесконечное количество раз, пока всё не пройдёт гладко.

Далее подробно рассмотрим 9, 12 и 14 шаги.

Шаг 9: Добавление первичных ключей

Наличие первичных ключей является одним из основных моментов для правильного функционирования групповой репликации. Все требования можно посмотреть здесь.

Отсутствие первичных ключей можно выяснить запросом взятым вот отсюда.

SELECT tables.table_schema , tables.table_name , tables.engine FROM information_schema.tables LEFT JOIN ( SELECT table_schema , table_name FROM information_schema.statistics GROUP BY table_schema, table_name, index_name HAVING SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puksON tables.table_schema = puks.table_schema and tables.table_name = puks.table_nameWHERE puks.table_name is null AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB";

Какой тип ключей и для каких колонок его создать я подсмотрел на форуме Zabbix, вот тут. Чтобы не поломалось обновление Zabbix, в таблице dbversion добавляем первичный ключ по существующему столбцу. Ниже необходимые запросы.

ALTER TABLE history ADD COLUMN `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;ALTER TABLE history_uint ADD COLUMN `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;ALTER TABLE history_text ADD COLUMN `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;ALTER TABLE history_str ADD COLUMN `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;ALTER TABLE history_log ADD COLUMN `id` BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT;ALTER TABLE dbversion ADD PRIMARY KEY (mandatory);

Напоминаю действие занимает большое количество времени на больших таблицах. Надеюсь, когда-нибудь это внесут в схему БД для Zabbix.

Шаг 12: Запуск групповой репликации

Все настройки, относящиеся к групповой репликации, я собрал в одном месте в конфигурационном файле.

server-id=[номер сервера в кластере по порядку]gtid_mode=ONenforce_gtid_consistency=ONlog_bin=binloglog_slave_updates=ONbinlog_format=ROWmaster_info_repository=TABLErelay_log_info_repository=TABLEtransaction_write_set_extraction=XXHASH64disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"plugin_load_add='group_replication.so;mysql_clone.so'ssl-ca=/usr/local/etc/ssl/mysql/ca.crtssl-cert=/usr/local/etc/ssl/mysql/server.crtssl-key=/usr/local/etc/ssl/mysql/server.keygroup_replication_ssl_mode=VERIFY_IDENTITYgroup_replication_group_name="[одинаковое на всех узлах, генерируем один раз командой SELECT UUID();]"group_replication_start_on_boot=off # включаем после добавления всех узлов в группуgroup_replication_local_address="[полное имя текущего сервера].com:33061"group_replication_group_seeds="example1.com:33061,example2.com:33061,example3.com:33061"group_replication_ip_allowlist="2.2.2.2/32,3.3.3.3/32,4.4.4.4/32"group_replication_member_weight=50group_replication_recovery_use_ssl=ONgroup_replication_recovery_ssl_verify_server_cert=ONgroup_replication_recovery_ssl_ca=/usr/local/etc/ssl/mysql/ca.crtgroup_replication_recovery_ssl_cert=/usr/local/etc/ssl/mysql/server.crtgroup_replication_recovery_ssl_key=/usr/local/etc/ssl/mysql/server.key

Добавляем это всё в my.cnf, дубликаты переменных, которые встречаются в файле до этого, необходимо удалить. Теперь можно перезапустить сервер, либо проверить значение переменных как написано ниже и поменять их вручную. Обратите внимание на переменную group_replication_start_on_boot, она выключена, поэтому при рестарте репликация не запустится.

Проверяем значение переменных командой SHOW VARIABLES LIKE 'binlog_format'; меняем с помощью команды SET GLOBAL binlog_format = RAW; это относится к переменным в верхней части конфига, остальные настройки подтянутся при активации групповой репликации.

Переменные group_replication_ssl_mode и group_replication_recovery_ssl_verify_server_cert установлены в максимально безопасный режим с проверкой сертификата сервера, так что при выписывании сертификата укажите в Subject Alternative Name (SAN) полные имена всех улов кластера, которые есть в group_replication_group_seeds.

В переменной group_replication_member_weight можно указать вес узла. Полезно, когда у вас один сервер, это виртуалка, для него можно указать вес поменьше и при следующих выборах мастера он победит в последнюю очередь.

Создаём пользователя для работы репликации:

SET SQL_LOG_BIN=0;CREATE USER 'replication'@'%' IDENTIFIED BY '[придумайте пароль]' REQUIRE SSL;GRANT replication slave ON *.* TO 'replication'@'%';GRANT BACKUP_ADMIN ON *.* TO 'replication'@'%';FLUSH PRIVILEGES;SET SQL_LOG_BIN=1;

Устанавливаем плагины и проверяем статус:

INSTALL PLUGIN group_replication SONAME 'group_replication.so';INSTALL PLUGIN clone SONAME 'mysql_clone.so';SHOW PLUGINS;

Настраиваем пользователя, используемого при восстановлении БД с работающего сервера:

CHANGE REPLICATION SOURCE TO SOURCE_USER='replication', SOURCE_PASSWORD='[придуманный пароль]' \\  FOR CHANNEL 'group_replication_recovery';

Первый запуск группы. Переменную group_replication_bootstrap_group включаем только на первом сервере, на остальных, просто запускаем групповую репликацию:

SET GLOBAL group_replication_bootstrap_group=ON; # выполняем только на первом сервереSTART GROUP_REPLICATION;SET GLOBAL group_replication_bootstrap_group=OFF; # выполняем только на первом сервере

Если никаких ошибок команда не вернула, то можно посмотреть информацию о вновь созданной группе:

mysql> SELECT * FROM performance_schema.replication_group_members;+---------------------------+--------------------------------------+-------------+-------------+---------------+| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE  |+---------------------------+--------------------------------------+-------------+-------------+---------------+| group_replication_applier | ce9be252-2b71-11e6-b8f4-00212844f856 |example1.com |       3306  | ONLINE        |+---------------------------+--------------------------------------+-------------+-------------+---------------+

Проверьте дополнительно логи MySQL сервера на содержание в них ошибок.

Шаг 14: Добавление узла в группу

После того как вы переключили Zabbix сервер и фронтенд на сервер с ГР, можно добавить оставшиеся узлы в кластер. Для этого выключаем MySQL сервер, делаем локальную копию, обновляем пакеты и удаляем текущий каталог с БД.

Запускаем чистую базу данных и проделываем всё тоже самое как в 12-ом шаге, с добавлением специфичных для этого сервера настроек (server-id, group_replication_local_address). Так как группа уже запущена, использовать переменную group_replication_bootstrap_group не нужно.

На этом этапе и будет использоваться так называемый Distributed Recovery механизм в который входит mysql_clone плагин. При подключении к узлу донору в первую очередь он попытается использовать бинарный лог, если информации в нём будет недостаточно, он полностью скопирует базу, включая созданного для репликации пользователя.

Проверьте статус группы и что сервер добавился в неё, а так же его производительность, успевает ли узел применять все транзакции.

После добавления оставшихся серверов, поменяйте в конфиге my.cnf значения переменной group_replication_start_on_boot с off на on и перезагрузите MySQL сервер на любом ведомом сервере и проверьте что он остался в группе.

Полезные команды

SELECT * FROM performance_schema.replication_group_members; - показывает статус всех узлов в группе.

SELECT * FROM performance_schema.replication_group_member_stats\G - показывает производительность каждого отдельного узла.

SELECT group_replication_set_as_primary('[uuid узла]'); - переключение ведущего узла.

Безотказный Zabbix сервер

А что же с Zabbix сервером спросите вы, если дочитаете до этого момента, а всё просто. Я сделал так чтобы он постоянно следовал за ведущим сервером групповой репликации. В кроне на каждом сервере запускается скрипт, который проверяет что узел сейчас Primary в ГП, если да, то запускает Zabbix сервер, если нет, то останавливает его. Дальше включается в работу HADNS, он проверяет на каком сервере запущен Zabbix и отдает нужный IP адрес для DNS записи.

Заключение

Возможно, сделано не всё так элегантно как хотелось бы. Вы наверно захотите использовать mysql-shell, mysqlrouter и преобразовать Group Replication в InnoDB Cluster, а может добавить HAProxy, особенно это полезно, когда разворачиваешь Zabbix с нуля. Надеюсь, этот рассказ послужит неплохой отправной точкой и будет полезен. Спасибо за внимание!

Дополнительная литература

https://dev.mysql.com/doc/refman/8.0/en/group-replication.html

https://blog.zabbix.com/scaling-zabbix-with-mysql-innodb-cluster/8472/

https://en.wikipedia.org/wiki/Paxos_(computer_science)

В рамках набора группы учащихся на курс "Highload Architect" подготовили перевод интересной статьи.

Приглашаем также посетить вебинар на тему Выбор архитектурного стиля. На этом открытом уроке участники вместе с экспертом рассмотрят различия между микросервисным и монолитным подходами, преимущества и недостатки подходов, обсудят принципы выбора архитектурного стиля.

У одного из наших клиентов возникла проблема с большой, постоянно растущей, таблицей в MySQL с более чем 2 миллиардами записей. Без модернизации инфраструктуры была опасность исчерпания дискового пространства, что потенциально могло сломать все приложение. С такой большой таблицей были и другие проблемы: низкая производительность запросов, плохая схема, и, из-за огромного количества записей, не было простого способа анализировать эти данные. Также нам нужно было решить эти проблемы без простоев в работе приложения.

В этом посте я хотел рассказать о нашем подходе к данной проблеме, но сразу хочу оговориться, что это не универсальное решение: каждый случай индивидуален и требует разных подходов. Но, возможно, вы найдете здесь некоторые полезные идеи для себя.

Спасение в облаках

После оценки нескольких альтернативных решений мы решили отправлять данные в какое-нибудь облачное хранилище. И наш выбор пал на Google Big Query. Мы выбрали его, потому что клиент предпочитал облачные решения от Google, а также данные были структурированными, предназначались для аналитики и нам не требовалась низкая задержка передачи данных (low latency). Поэтому BigQuery, казалась, идеальным решением (см. диаграмму ниже).

После тестов, о которых вы можете прочитать в посте Анджея Людвиковски (Andrzej Ludwikowski), мы убедились, что Big Query достаточно хорошее решение, отвечающее потребностям наших клиентов и легко позволяет использовать аналитические инструменты для анализа данных. Но, как вы, возможно, уже знаете, большое количество запросов в BigQuery может привести к увеличению стоимости, поэтому мы хотели избежать запросов в BigQuery напрямую из приложения и использовать его только для аналитики и как что-то вроде резервной копии.

https://cloud.google.com/solutions/infrastructure-options-for-data-pipelines-in-advertising#storing_data

Передача данных в облако

Для передачи потока данных есть много разных способов, но наш выбор был очень прост. Мы использовали Apache Kafka просто потому, что она уже широко использовалась в проекте и не было смысла внедрять другое решение. Использование Kafka дало нам еще одно преимущество мы могли передавать все данные в Kafka и хранить их там в течение необходимого времени, а затем использовать для миграции в выбранное решение, которое справилось бы со всеми проблемами без большой нагрузки на MySQL. С таким подходом мы подготовили себе запасной вариант в случае проблем с BigQuery, например, слишком высокой стоимости или сложностей и с выполнением необходимых запросов. Как вы увидите ниже, это было важное решение, которое дало нам много преимуществ без каких-то серьезных накладных расходов.

Потоковая передача из MySQL

Итак, когда речь заходит о передаче потока данных из MySQL в Kafka, вы, вероятно, думаете о Debezium или Kafka Connect. Оба решения отличный выбор, но в нашем случае не было возможности их использовать. Версия сервера MySQL была настолько старой, что Debezium ее не поддерживал, а обновление MySQL было невозможным. Мы также не могли использовать Kafka Connect из-за отсутствия автоинкрементного столбца в таблице, который мог бы использоваться коннектором для запроса новых записей без потери каких-либо из них. Мы знали, что можно использовать timestamp-столбцы, но при этом подходе могли быть потери строк из-за того, что запрос использовал более низкую точность timestamp, чем указано в определении столбца.

Конечно, оба решения хороши, и если нет никаких препятствий для их использования, то я могу рекомендовать их для передачи данных из вашей базы данных в Kafka. В нашем случае нам нужно было разработать простого Kafka Producer, который запрашивал данные без потери каких-либо записей и передавал их в Kafka. И Kafka Consumer, отправляющего данные в BigQuery, как показано на диаграмме ниже.

Секционирование как способ экономии места

Итак, мы отправили все данные в Kafka (сжимая их для уменьшения полезной нагрузки), а затем в BigQuery. Это помогло нам решить проблемы с производительностью запросов и быстро анализировать большой объем данных. Но осталась проблема с доступным местом. Мы хотели найти решение с заделом на будущее, которое справилось бы с проблемой сейчас и могло быть легко использовано в будущем. Мы начали с разработки новой таблицы. Мы использовали serial id в качестве первичного ключа и секционирование по месяцам. Секционирование этой большой таблицы дало нам возможность создавать резервные копии старых секций и усекать (truncate) / удалять (drop) их, чтобы освободить место, когда секция больше не нужна. Итак, мы создали новую таблицу с новой схемой и использовали данные из Kafka для ее заполнения. После переноса всех записей мы развернули новую версию приложения, которая для INSERT использовала новую таблицу с секционированием и удалили старую, чтобы освободить место. Конечно, вам понадобится достаточно свободного места для переноса старых данных в новую таблицу, но в нашем случае во время миграции мы постоянно делали резервные копии и удаляли старые разделы, чтобы быть уверенными, что у нас хватит места для новых данных.

Сжатие данных как еще один способ освободить пространство

Как я уже упоминал, после передачи данных в BigQuery мы могли легко анализировать их, и это дало нам возможность проверить несколько новых идей, которые могли бы позволить нам уменьшить пространство, занимаемое таблицей в базе данных.

Одна из идей была посмотреть, как различные данные распределены по таблице. После нескольких запросов выяснилось, что почти 90% данных никому не нужны. Поэтому мы решили их сжать, написав Kafka Consumer, который отфильтровал бы ненужные записи и вставлял только нужные в еще одну таблицу. Назовем ее сжатой таблицей (compacted table), что показано на приведенной ниже диаграмме.

После сжатия (строки со значением "A" и "B" в колонке type были отфильтрованы во время миграции).

После этого мы обновили наше приложение и теперь выполняли чтение из новой таблицы (compacted table), а запись делали в секционированную таблицу (partitioned table), из которой мы непрерывно передавали данные с помощью Kafka в сжатую таблицу (compacted table).

Итак, как видите, мы устранили проблемы, с которыми столкнулся наш клиент. Благодаря секционированию была устранена проблема нехватки места. Сжатие и правильное проектирование индексов решили некоторые проблемы с производительностью запросов из приложения, и, наконец, передача всех данных в облако дала нашему клиенту возможность легко анализировать все данные.

Так как мы используем BigQuery только для аналитических запросов, а остальные запросы, отправляемые пользователями через приложение, по-прежнему выполняются в MySQL, то затраты оказались не такие и большие, как можно было бы ожидать. Еще одна важная деталь все было выполнено без простоев, ни один клиент не пострадал.

Резюме

Итак, подведем итоги. Мы начали с использования Kafka в качестве инструмента для потоковой передачи данных в BigQuery. Но так как все данные были в Kafka, это дало нам возможность легко решить другие проблемы, которые были важны для нашего клиента.

Узнать подробнее о курсе "Highload Architect".

Смотреть вебинар на тему Выбор архитектурного стиля.

Как устроены индексы в MySql, чем отличается индексирование в двух наиболее популярных движках MyISAM и InnoDb, чем первичные ключи отличаются от простого индекса, что такое кластерные индексы и покрывающие индексы, как с помощью них можно ускорить запросы. Вот как мне кажется наиболее интересные темы которые раскрою в этой статье. Тут же постараюсь подробно раскрыть тему с позиции того как работает этот механизм внутри. Буквально на пальцах и с позиции абстракций а не конкретики. В общем чтоб было минимум текста и максимум понятно.

Небольшое оглавление:

• Вводная информация

• Что представляет из себя индекс в MySql

• Скорость чтения из индекса

• Отличия в индексах MyISAM и InnoDb

• Кластерный индекс

• Первичные и "вторичные" индексы в чем отличия

• Покрывающие индексы

Вводная информация

В MySql существует несколько типов индексов и каждый из них хорош для выполнения своих специализированных задач. Например Hash индекс хорош для хранения данных в виде ключ - значение в оперативной памяти, FULLTEXT индекс для поиска по текстовым документам, SPATIAL для хранения информации о гео-объектах, UNIQUE для уникальных значений. Но все же в подавляющем большинстве случаев мы используем индекс на основе B-дерева (BTREE) или Balanced-Tree. Сбалансированное оно потому что высота каждого поддерева с общим корневым элементом может отличаться, но всегда не более чем на константную величину. Далее в статье речь пойдет именно про BTREE индексы, по умолчанию под индексами буду подразумевать именно их.

Что представляет из себя индекс в MySql

На рисунке изобразил схематично как устроен индекс. Имеются узловые элементы (квадраты) и листья (круги). Предположим у нас есть таблица с колонками "Val" и "ID" как на рисунке. В этой таблице индекс построен по числовому полю "ID". Тогда получается что в узловых элементах находятся значения индекса и ссылки на другой более нижний узел или лист. В листовых же элементах точно так же лежат значения индекса которые уже ссылаются непосредственно на данные из таблицы.

Процесс поиска происходит примерно следующим образом. Например нужно найти строку с индексом 11.

• начинаем просмотр корневого (верхнего) узла

• первое значение в нем 10

• идем к следующему 19, оно уже больше чем нам нужно

• по ссылке слева от 19 переходим к следующему нижнему узлу

• там первое значение 13, оно больше чем нам нужно

• опять по ссылке слева переходим к более нижнему элементу

• это уже будет листовой элемент, в нем уже лежат непосредственно данные

• просматриваем данные по порядку

• находим 11

• переходим по ссылке непосредственно к строке в таблице.

Скорость чтения из индекса

Такое устройство индекса позволяет обеспечить логарифмическую скорость поиска O(log n). Это очень быстро. Вот таблица где для наглядности посчитал сколько сравнений нужно сделать для поиска записи в таблице с разным количеством данных:

Получается для поиска по миллиону значений нужно всего около 20 сравнений. Очень быстро. Одна небольшая ремарка - эти вычисления конечно же правдивы только для индексов с уникальными значениями. Построив индекс по значению, которое почти во всех строках одинаково, эффекта не будет так как все равно придется перебирать все эти строки с одинаковыми значениями.

Отличия в индексах MyISAM и InnoDb

MyIsam это более старый движок чем InnoDb и все описанное выше хорошо описывает устройство индекса именно в MyIsam. Более того для MyIsam можно сказать что первичный индекс и просто обычный индекс ни чем между собой не отличаются. В целом таблицы построенные на движке MyIsam вполне себе могут существовать даже без первичного ключа и без всякого индекса в целом. А вот InnoDb уже более свежий и продвинутый движок, и тут как раз есть отличия первичного ключа и просто индекса. Создать таблицу InnoDb тоже можно не указав первичный ключ, но в этом случае первичный ключ все равно создастся. Это называется суррогатный первичный ключ. InnoDb сам выберет поле по которому нужно этот ключ создать, если ни одно поле не подходит, то создаст новое числовое поле, которое конечно же будет скрыто и в структуре его не увидеть. Для разбора индексов InnoDb первым делом нужно начать с кластеризации.

Кластерный индекс

Кластерный индекс отличается тем, что в отличии от предыдущей картинки, где от листьев шли ссылки непосредственно на строки в таблице, тут все данные строк хранятся непосредственно в самом индексе. Проиллюстрировал это на примере листьев 10, 11, 12. Это хорошо тем что позволяет избежать лишнего чтения диска при переходе по ссылке от листа на данные в строке. Тут непосредственно вся строка лежит в индексе. То есть получается что в InnoDb при создании таблицы и указании первичного ключа будет построено такое дерево, в котором все данные таблицы будут продублированы в листья индекса. Если первичный ключ не задать то колонка для него будет выбрана или создана автоматически и все равно по ней будет построен кластерный индекс.

Более того, если мы говорим о таблицах на основе движка InnoDb, то в целом понятие таблица довольно абстрактное. На картинке она нарисована просто для наглядности. На самом деле ни какой таблицы по сути не существует, а все данные просто хранятся в кластерном индексе.

Первичные и "вторичные" индексы в чем отличия

Выше было оговорено что для MyIsam нет разницы между первичными и "вторичными" ключами.

На картинке нарисован первичный и вторичный ключ в MyIsam. Первичный ключ построен по полю "ID", вторичный по полю "Val". Видно что их структура одинакова. И в том и в другом в листьях расположены значения индекса и ссылки на строки в таблице.

В InnoDb это устроено немного по другому.

Как уже говорил, таблица тут просто для наглядности. Все ее данные хранятся в первичном (кластерном ключе). Тут первичный ключ построен по полю "Id", вторичный по полю "Val". Видно что в листьях первичного ключа лежат значения индекса + все данные из строк таблицы. Во вторичном же ключе, в листьях лежат значения ключа + первичный ключ.

Можно резюмировать что для MyIsam нет различий между первичным и вторичными индексами. Для InnoDb первичный ключ содержит в себе все данные таблицы, вторичный же ключ содержит значения ключа плюс значение первичного ключа. Получается что при поиске по вторичному ключу, поиск будет произведен дважды. Первый раз непосредственно по самому индексу, будет найдено значение первичного индекса. И уже второй раз по найденому первичному индексу для поиска данных всей строки.

Покрывающие индексы

Последнее о чем хотелось бы упомянуть - это покрывающие индексы и как с их помощью можно немного ускорить производительность.

Смысл покрывающих индексов в том, что MySql может вытаскивать данные непосредственно из самого индекса, не читая при этом всю строку и вовсе не читая строку. Для такой оптимизации нужно чтобы все поля указанные в SELECT имелись в индексе. То есть например у нас имеется таблица с полями "id", "name", "surname", "age", "address". И мы проиндексировали ее по полю "id". В запросе мы хотим получить например "id" и "name". При таком условии MySql найдет по первичному ключу нужную строку, прочитает ее и отбросит все поля не указанные в SELECT. Если же мы немного оптимизируем этот запрос и построим индкес по двум полям "id" и "name", то в таком случае MySql найдя нужную строку по этому индексу не пойдет читать всю эту строку, а просто возьмет данные, которые нужны непосредственно из индекса. Правда есть обратная сторона такого подхода, а именно размер индекса в этом случае будет больше, по этому нужно грамотно подходить к построению покрывающих индексов.

Более подробно можно почитать в очень хорошей книге "MySQL по максимуму" Бэрон Шварц, Петр Зайцев, Вадим Ткаченко

Я люблю ввязываться в авантюры, и за последний месяц об одной из них я пару раз рассказывал друзьям, что вызывало восторг, поэтому решил поделиться с хабравчанами! Эта история про отважные пет-проекты, мощь опен-сорса и саморазвитие, а также основные технические детали. Надеюсь, вас это вдохновит :)

1. Предыстория

В первой половине 2016-го в свет вышел нашумевший FindFace, позволяющий загрузить фото человеческого лица и находить соответствующий профиль Вк. Он вызвал большой общественный резонанс, привёл как к поиску девушек по скрытым фоткам на эскалаторах в метро и к обнародованию порноактрис с неприятными последствиями для их близких, так и к находкам потерянных близких, к нереально быстрому поиску преступников по случайным кадрам с камер, прям как в американских фильмах, только наяву.

В то время мне об этом сервисе говорили и ленты новостей, и друзья, я отвечал "ну да, прикольно", и только. Но спустя пару лет, в начале октябре 2018 на каком-то айтишном форуме я захотел связаться с одним пользователем по специфическому вопросу, вот только он туда уже давно не заходил. Зато там было его хорошее фото, и тут-то я вспомнил про крутой сервис! Побежал на их сайт и разочаровался в сентябре 2018, буквально за месяц, они перестали предоставлять свои услуги физ.лицам, и бесплатно, и даже за деньги, перейдя в сегмент b2b и b2g. Оно и понятно, пиар уже сработал, а этических вопросов так возникает куда меньше. Но меня, законопослушного гражданина, это огорчило. И не только меня: фан-группы ФайндФейса пестрили сообщениями о том, что люди готовы заплатить в 10 раз больше, лишь бы им помогли найти нужного человека.

Я стал искать аналоги, но они все были либо точным поиском, но по крайне ограниченной выборке вроде нескольких сотен знаменитостей из Википедии, либо обширным, но почти бесполезным по точности поиском через Гугл/Яндекс.

Пару рабочих дней я размышлял, что же сложного в создании такого сервиса, который бы и точно искал людей, и по всему Вк? Решил, что ничего, ведь у меня тогда уже были базовые познания в data science, разработке и администрировании. Поэтому в пятницу, приехав с работы домой, я взялся за дело. За вечер я накидал скрипт, который парсит профили Вк, находит фото, индексирует по ним лица и сохраняет в БД. Потом просидел ещё пару суток почти без сна, заставил это дело безостановочно работать на своём сервере. Началась новая трудовая неделя, я был очень уставший, но ещё больше довольный и полный энтузиазма! Ведь мой скрипт медленно, но безостановочно бежал по всему Вк.

2. Техническое устройство

2.1. Индексирование

Как вы считаете, что происходит после того, как вы отправляете запрос в любую крупную поисковую систему? Не важно, поиск текста в Яндексе, Google или поиск лиц в FindFace или моём сервисе. Многие, особенно не-айтишники, с трудном представляют внутренние механики технических процессов, а они бывают нетривиальны даже казалось бы в простых задачах. В случае поисковых систем магия заключается в том, что при получении запроса они не начинают обегать все страницы в интернете, ища там ваш текст, или весь Вк, сравнивая вашу фотку со всеми подряд, это бы занимало астрономические объёмы времени. Вместо этого, поисковые системы сперва индексируют нужные данные. В случае текста (и подобных тексту данных вроде ДНК) в ближайшем приближении могут использоваться хэш-таблицы или префиксные деревья. В случае фоток тоже нужны индексы, которые сильно сократят время поиска. Для этого я использовал библиотеку face_recognition, которая позволяет преобразовать фото лица, если правильно помню, в 128-мерный вектор признаков со значениями от -1 до 1 (далее буду называть его просто хэш). Для поиска человека по фото, нам нужно просто пробежаться по всем фото из коллекции, считая евклидово расстояние между векторами-хэшами из запроса и набора подобный пример, реализованный на Питоне, доступен на сайте упомянутой библиотеки. Да, такая операция поиска тоже не дешёвая, но об этом позже.

В ближайшие недели я стал прикидывать темпы роста и понимать, что надо масштабироваться. API Вк работает на токенах доступа, и каждый токен имеет ограничения по числу запросов на единицу времени. Чтобы увеличить их число, я создал несколько приложений Вк с формами получения токенов и попросил пару десятков друзей выполнить там простой клик, что в итоге дало мне сотню токенов, которые я бережно сложил в файлик.

Конечно, не только лимиты АПИ повышать надо, но и объёмы CPU. Изначально я развернул скрипт на маленьком VPS, который создавался для простого личного сайта. В подмогу ему, я взял ещё один VPS, в несколько раз мощнее. Потом я решил, что и этого мало, взял ещё и целый выделенный сервер, который сильнее моего собственного рабочего компьютера :D Не энтерпрайз-левел, но производительность стала меня устраивать, хотя расходы и выросли до 15 тысяч руб/месяц, что для меня тогда было весьма ощутимой тратой.

2.2. Подобие архитектуры и DevOps'а

Если у вас есть опыт в бэкэнд разработке, то сразу встаёт вопрос, как я заставил несколько серверов работать в тандеме, а не независимо, дублируя одну и ту же работу? На самом деле, решение классическое: один микросервис брал на себя роль ведущего, мастера, хранил состояние всей системы, и выдавал задания объёмом в тысячу профилей Вк воркерам, которые быстрее или медленнее их индексировали, а результаты, в том числе промежуточные, возвращали мастеру; если же мастер за определённый промежуток времени не получал ответа от воркера, он давал это задание другому, с учётом промежуточного прогресса.

Кстати, воркеры работали в несколько потоков. Да, Питон, благодаря Global Interpreter Lock, не умеет в полный параллелизм, но много времени уходило на выгрузку фоток, а IO-операции хорошо параллелятся. Вдобавок, это позволило легко назначить каждому потоку свой токен доступа и гибко настраивать загруженность каждой машины.

Для автоматизации настройки окружения, токенов и т.п были написаны скрипты на Питоне, которые подключались к целевой машине по SSH и ставили всё что нужно. Позже я узнал, что у меня костыльный велосипед, есть качественные решения, но всё равно было интересно посмотреть подноготные детали. Из прикольного, пришлось также разобраться, что есть разные ВМ и средства виртуализации, что некоторое ПО не работает в определённых конфигурациях, благодаря чему виртуалки на Xen и OpenVZ с казалось бы одинаковыми ресурсами могут отличаться в цене на 40%.

2.3. Поиск

Помимо ролей мастера и воркера, есть роль поискового микросервиса. Проиндексированные фото Вк и айдишники их профилей сохраняются в БД, точнее, MySQL v5.7 и алгоритм поиска я переписал с Python на SQL, что позволило сильно ускорить вычисления и выйти на больший масштаб. Но с ростом данных этого всё равно было очень мало, я думал над оптимизациями, старался переиспользовать свой опыт big data аналитики с работы, экспериментировал с разными структурами запросов и генерацией SQL-запросов Питоном, это позволило ускорить вычисления в несколько раз, что мило, но всё равно мало.

Потом я решил сделать поиск двух-этапным: преобразовывать хэши-дробные-векторы в небольшой массив байт, сохраняя каждый признак в два бита: v>0.1 и v<-0.1 (здесь), затем сравнивая число совпавших бит такого хэша у целевого лица и всех лиц в БД, а потом фильтруя записи в БД по какому-то трешхолду, отправляя на более точное и медленное сравнение только потенциальных кандидатов. Пришлось повозиться и переехать на MySQL v8, т.к в 5.7 бинарных операций нет. Но это позволило ускорить поиск ещё почти в 30 раз а это уже клёво ^_^

Вообще, поиск можно было бы улучшать и дальше возможно подобрав более эффективные коэффициенты в операции выше, или переделав этот хэш, ещё было бы круто добавить параллелизм, которого в MySQL из коробки нет, партицирование, шардирование, и искать многопоточно; для этого в планах был переезд на PostgreSQL.

2.4. Другие механики

Когда у меня накопился большой объём данных, и мой сервис научился находить некоторую долю людей по случайным фоткам за приемлемое время, я стал мечтать дальше и задумываться о развитии функционала.

Ускорять время поиска можно не только ускорением самого поискового алгоритма, но и снижением выборки, например, ища профили только среди участников каких-то групп Вк. Ясное дело, здесь встаёт вопрос ограничения размера этой выборки, т.к делать запрос с "ISIN (десятки тысяч айдишников)" такое себе, а вот на паре сотен и даже тысяч работает в разы быстрее, чем полный проход БД.

Помимо прочего, я немного помешан на математике, множествах графах, а соц.сети это прекрасные данные и множеств, и графов! Я подумал, что можно двигаться в этом направлении, позволяя задавать сложные запросы с И-ИЛИ-деревьями. Притом, искать можно не только по фото, но и по ник-неймам или ссылкам на профили в других соц.сетях к сожалению, поисковики интернета и Вк эту инфу не очень индексируют, и я попробовал делать это сам.

Если идти ещё дальше, то можно индексировать не только Вк, но и ВотсАп, Тг перебрав все русские номера, возможно частично FB, Twi, Ig. Но это уже совсем будущее, я решил двигаться в сторону скорейшей апробации и монетизации того, что есть уже.

3. Заключение

3.2. Happy ли end?

Перед разработкой ЛК, интеграции платежей и т.п декора, я решил больше разобраться в общественных настроениях, т.к некоторые мои знакомые выражали сомнения, что им было быы приятно пользоваться, а ещё серьёзнее быть доступными в таком сервисе. Помимо этого, примерно тогда же Вк ввёл закрытые профили, чтобы переживающее о своей безопасности люди могли спокойно скрываться, и анализируя статистику своих воркеров я видел, что число таких профилей всё растёт.

Я написал в тех поддержку Вк (тогда они ещё отвечали, ахах), аккуратно представился студентом, что хочу проводить социологические исследования скандируя большие объёмы данных Вк, в т.ч фото, ФИО и описание. Что на самом деле было правдой, с учётом моего интереса к аналитике и психологии. Они ответили, что ради статистики и небольших выборок в целом не против, но точно против какой-либо идентификации. А ещё "порадовали" тем, что будут и палки в колёса АПИ вставлять таким сервисам, и участвовать в разработке/внедрению законов, регулирующих эту деятельность. А недавно, уже в наше время, вышел законопроект, запрещающий автоматизированную обработку данных с сайтов, что по сути полностью блокирует подобные сервисы с парсингом.

В связи с этим, я принял решение о закрытии проекта, хоть это и было печально: в феврале 2019 у меня уже было проиндексировано 25% всего Вк в гигабайтах БД, притом не за бесплатно. Но у меня уже тогда был опыт различных проектов, поэтому я не жил розовыми мечтами об успешном успехе, а старался извлечь другую пользу и просто фан (:

Кстати, только в процессе написания этой статьи я понял, что принявшись за эту работу, я совсем забыл про исходный мотив, про того человека с какого-то сайта и свой вопрос к нему xD

После завершения описанной истории, я решил опубликовать исходники, но т.к там в истории коммитов засветились токены, то перезалил в новый репозиторий. Но код действительно такой, что мне самому туда страшно заглядывать :D
https://github.com/AivanF/ID-Detective-public

3.2. Польза

Здесь, как и в других своих пет-проектах и стартапах, я набрался много опыта:

• Разобрался с многопоточностью в Питоне.

• Покопался в специфических вопросах оптимизации MySQL запросов.

• Научился строить организацию приложений, файлов и комментов на будущее, чтобы оно не превращалось в спагетти.

• Освоил работу из кода с SSH для настройки окружения, понял, насколько чудесен Ansible.

• Разработал микросервисную архитектуру из клея и палок, что затем позволило легко понять концепции Kubernetes.

И всё это мне очень пригодилось в последующих работах и проектах.

3.3. Мораль

Выводы каждый сделает свои, но главное не бойтесь пробовать, учиться и искать себя! Надеюсь, вам было интересно :)

В современном мире всё большую популярность приобретает методика под названием customer development для тестирования идей и гипотез о будущем продукте. Методику придумал "крёстный отец Кремниевой долины" Стив Бланк.
Одним из числа сильных инструментов в "разработке клиентов" является интервью, когда вы можете побеседовать с респондентом. Однако им не всегда можно воспользоваться ввиду разных причин, которые условно можно свести к объёму бюджета и имеющемуся времени. Но во многих ситуациях можно воспользоваться опросом. Причём опросом, который можно автоматизировать за счёт применения чат-бота и нейронной сети для определения смысла слов, которые написал респондент в ответ на заданный вопрос.

В этой статье сконцентрируюсь на алгоритме работы чат-бот для проведения опроса. Как сделать чат-бота для VK писал в отдельной статье на Хабре. Использовал: Python, MySQL, API VK и готовую нейросеть от RusVectores.

Статья будет полезна для тех, кто только начинает погружаться в тему и хотел бы получить информацию, изложенную в более простой, упрощённой форме.

Дисклеймер. Не претендую на академическую точность изложения, говорю лишь о том, что удалось понять на текущий момент по итогу изучения различных материалов. Не рекламирую какие-либо технологии и сервисыодержание

• Нейросеть для понимания смысла текста, вводимого пользователем

• База данных для хранения вопросов интервью

• Описание алгоритма работы чат-бота

Нейросеть для понимания смысла текста, вводимого пользователем

Сделать бота, который распознаёт только ключевые слова в фразе пользователя - не очень жизненно. Проверку всех ключевых слов можно постараться прописать в коде, но это трудоёмко и негибко. Поэтому стоит воспользоваться возможностями нейросетей для определения смысла введённых пользователем слов.

В данном решении была использована готовая нейросеть от сервиса RusVectores, обученная на корпусе НКРЯ с использованием алгоритма word2vec CBOW с длиной вектора 300.

НКРЯ это совокупность русскоязычных текстов, Национальный Корпус Русского Языкав полном объёме. Содержит 270 миллионов слов, объём словаря 189 193 слова.

Word2vec CBOW алгоритм, благодаря которому слово на естественном языке представляется в виде числового вектора. Т.е. определяет координату слова в смысловом пространстве. CBOW это аббревиатура Continuous Bag of Words. Она обозначает алгоритм, который есть в word2vec. Данный алгоритм называют моделью мешка слов, он предсказывает слово по контексту. Ещё один алгоритм в word2vec - Skip-gram предсказывает контекст по слову.

С помощью данных алгоритмов генерируют близкие по смыслу слова при запросе в поисковой системе, сравнивают документы по смыслу, определяют смысловую близость слов и предложений.

Более подробно о word2vec можно почитать в статье "Немного про word2vec: полезная теория".

О векторном представлении слов (эмбеддинге) хорошо и с примерами описано в статье "Что такое эмбеддинги и как они помогают машинам понимать тексты".

Представление слова в виде вектора позволяет оценивать его смысловую близость с другими словами, так же представленными в виде вектора. Для оценки близости слов можно вычислить косинус угла между их векторами. Чем ближе к 1 будет косинус угла между векторами слов, тем они ближе по смыслу. Единице будет соответствовать косинус угла 0 градусов, т.е. когда векторы слов совпадают.

Чтобы создать такой набор векторов почти для 200 000 слов и постоянно иметь к ним доступ, нужно располагать вычислительными мощностями. Т.к. у меня таких мощностей нет, я воспользовался доступным онлайн сервисом RusVectores.

База данных для хранения вопросов

Для проведения опроса я подготовил табличку с вопросами и разместил её в базе данных MySQL. Как видно из рисунка ниже, вопросы имеют ответвления, похожие на древовидную структуру или структуру графа.

В базе данных таблица с вопросами выглядит так (фрагмент):

Поле question_num служит для того, чтобы определить порядок вопросов и ответвления. Это путь от корневого элемента дерева вопросов до листового, разделённый точкой.

Описание алгоритма работы чат-бота

Начало опроса

По договорённости с пользователем он заходит на страницу сообщества в ВК и инициирует диалог, нажав кнопку Сообщение.

Бот здоровается и спрашивает разрешения начать опрос. Текст приветствия задавал в разделе "Управление" "Сообщения" на странице сообщества в ВК.

Если пользователь ответил что-то близкое по смыслу со словом да, то бот начинает задавать вопросы. Как определить, что пользователь одобрил старт интервью? Для этого как раз нужна нейросеть, чтобы определить смысловую близость введённых пользователем слов к словам: да, можем, можно, начинай, ок. Для этого воспользуемся API сервиса RusVectores.

Далее приведён код функции, которая определяет начинать интервью или нет. Если интервью уже было начато, то функция определяет какой вопрос задать следующим.

фрагменты кода из bot_methods.py
_{модуля, в котором реализованы все методы бота}

def _identify_phrase(user_id, user_message):    """    identify start question or greeting    return number of phrase in database    """    # identification variable, on start set "I don't know"    identi = 'I dont know'    # find in database current position in conversation between user and chatbot    identi = get_current_position_in_conversation(user_id)    if identi != 'err':        # if the conversation has just begun        if identi == '0':            # define greetings            similarity = _get_similarity(user_message, u'привет здравствуйте добрый')            if similarity > 0.5:                identi = "greetings"            else:                # define start interview or not                identi = _start_or_not(user_message)        # if the conversation continues        elif identi == '1':            # define start interview or not            identi = _start_or_not(user_message)        else:            pass                return identi

Вначале определим возможность начать опрос исходя из ответа пользователя с помощью метода _start_or_not():

def _start_or_not(user_message):    """    define <identi>: start or don't start interview    """    if user_message != 'старт' or user_message != 'Старт':        _identi = 'I dont know'        # define if user agree to start interview        start = _get_similarity(user_message, u'да можем можно начинай ок')        # define if user don't agree to start interview        later = _get_similarity(user_message, u'нет позже потом завтра')        if start > later and start > 0.15:            _identi = 'start'        elif later > start and later > 0.15:            _identi = 'later'    else:        _identi = "start"    return _identi

Если пользователь решил сначала поприветствовать бота, то нужно понять это и поприветствовать в ответ. Для этого проверим на смысловую близость сообщения от пользователя со словами приветствия с помощью метода _get_similarity(). Данный метод будет использоваться и далее для определения смысловой близости сообщений пользователя.

def _get_similarity(text1, text2):    """    Function return similarity between text1 and text2    text1 - user message    text2 - key words    """    text1.strip()  # delete empty space on start and end of string    text2.strip()    text1_words = text1.split(' ')    text2_words = text2.split(' ')    similarity = 0.0 # init variable    try:        for word1 in text1_words:            if word1 != '':                for word2 in text2_words:                    if word2 != '':                        # prepare url for request to API rusvectores.org                        # url example https://rusvectores.org/ruscorpora_upos_cbow_300_20_2019/дело__папка/api/similarity/                        url = '/'.join(['https://rusvectores.org/ruscorpora_upos_cbow_300_20_2019',                                         word1 + '__' + word2, 'api', 'similarity/'])                        # GET request to API rusvectores.org                        r = requests.get(url, stream=True)                        # sum similarity of couple of words                        similarity = similarity + float(r.text.split('\t')[0])    except Exception as e:        log_exception = str(e)    # average similarity    similarity = similarity/len(text2_words)    # return similarity between text1 and text2    return similarity

Переменная similarity содержит числовое обозначение смысловой близость фраз text1 и text2. Чем ближе similarity к 1, тем ближе фразы по смыслу.

Метод _identify_phrase() используется для обработки всех фраз, которые пользователь пишет в чат. Поэтому необходимо заложить в него логику проверки как начального этапа - приветствие и старт интервью, так и последующее движение по вопросам. Для этого необходимо определять на каком вопросе и на какой развилке находится диалог. Текущую позицию в беседе определяем с помощью метода get_current_position_in_conversation()

фрагмент кода из mysqldb_methods.py
_{модуля, в котором реализованы все методы для работы с MySQL базой данных}

def get_current_position_in_conversation(user_id):    """    find in database current position in conversation between user and chatbot    using in bot_methods.py    """    try:        conn = MySQLdb.connect(host=HOST, user=USER, passwd=PASSWORD,                                db=DATABASE, charset='utf8', init_command='SET NAMES UTF8')        cursor = conn.cursor()        query = "SELECT `question_num` FROM `conversations` WHERE `user_id`=%(user_id)s LIMIT 1"        cursor.execute(query, {'user_id': user_id})        result = cursor.fetchone()        if result is None:            identi = '0'        else:            identi = result[0]        conn.close()    except Exception as e:        identi = 'err'        return identi

Таким образом мы обрабатываем три сценария взаимодействия с чат-ботом:
- старт опроса (понимаем согласен пользователь начать опрос или нет с помощью функции _start_or_not()),
- обмен приветствиями, если пользователь поздоровался (понимаем по смысловой близости к словам приветствия с помощью функции _get_similarity());
- движение по структуре вопросов с помощью функции get_current_position_in_conversation() для определения текущего положения в структуре вопросов.

Давайте рассмотрим движение по структуре вопросов более подробно.

Стоп-слова

Одним из важных моментов является удаление "стоп-слов", т.е. слов, которые можно с лёгкостью удалить из предложения и при этом его смысл не потеряется. Вот набор стоп-слов, которые я использовал в данном проекте:

stop_words = [u'а',u'большой',u'бы',u'быть',u'в',u'весь',u'вот',u'всей',u'вы',u'говорить',u'год',u'для',u'до',u'еще',u'если',u'же',u'знать',u'и',u'из',u'или',u'к',u'как',u'который',u'мочь',u'мы',u'мне',u'на',u'наш',u'него',u'нее',u'них',u'но',u'о',u'один',u'она',u'они',u'оно',u'оный',u'от',u'ото',u'по',u'с',u'свой',u'себя',u'сказать',u'та',u'такой',u'такое',u'только',u'тот',u'ты',u'то',u'у',u'что',u'это',u'этот',u'я']stop_characters = [u'.',u',',u' - ',u'- ',u' -',u':',u';',u'?',u'',u'!',u'_',u'(',u')',u'=',u'+',u"#",u'$',u'@',u'%',u'*',u'   ',u'<',u'>','1','2','3','4','5','6','7','8','9','0']

С помощью метода _clear_text() очищаю предложение от стоп-слов:

Движение по структуре вопросов

Для определения в каком направлении опроса двигаться исходя из ответов респондента воспользуемся функцией _define_conversation_way():

def _define_conversation_way(user_message, identi):    """    define in which way we are goin to?    """    # all questions, unless  3 has two ways: 'yes' (positive) or 'no' (negative)    if identi != '3' and identi != '6':        yes = _get_similarity(user_message, u'да заказывал просить')        no = _get_similarity(user_message, u'нет никогда')    elif identi == '6':        # the question number 6 has different ways: 'delivery' or 'self-delivery'        yes = _get_similarity(user_message, u'заказываю доставку')        no = _get_similarity(user_message, u'еду сам ищу аналог')    elif identi == '3':        # the question number 3 has different ways: 'from store' or 'delivery'        yes = _get_similarity(user_message, u'магазин сам')        no = _get_similarity(user_message, u'доставка почта все перечисленное курьер дом')    if yes > no and yes > 0.15:        _way = 'yes'    elif no > yes and no > 0.15:        _way = 'no'    else:        _way = 'I dont know'    return _way

Для удобства использования я сгруппировал всю логику по определению того что бот должен ответить в функцию _get_bot_answer(). Для удобства восприятия приведу ниже не только данный метод, а модуль с методами бота в целом:

bot_methods.py
_{полный код модуля, в котором реализованы все методы бота}

# -*- coding: utf-8 -*-"""Bot methods.Realizes all what bot can do."3. Использование API сервиса RusVectores"https://github.com/akutuzov/webvectors/blob/master/preprocessing/rusvectores_tutorial.ipynb"""import re  # for work with regular expressionsimport requests  # for using HTTP requestsfrom bot_config import stop_wordsfrom bot_config import stop_charactersfrom mysqldb_methods import get_current_position_in_conversationfrom mysqldb_methods import get_question_from_DBfrom mysqldb_methods import write_current_question_number_for_userdef get_bot_answer(user_id, user_message):    """    using in views.py    make answer to user    """    answer = ''    # delete stop-words and punctuation characters in sentence    user_message = _clear_text(user_message)    # identify what to do: start or continue conversation    identi = _identify_phrase(user_id, user_message)    if identi == 'greetings':        answer = get_question_from_DB('1')        write_current_question_number_for_user(user_id, '1')    elif identi == 'start':        answer = get_question_from_DB('2')        write_current_question_number_for_user(user_id, '2')    elif identi == 'later':        answer = "Когда у вас будет возможность пройти интервью напишите мне 'старт'."    elif identi == 'I dont know':        answer = "Я не совсем вас понимаю...\nУточните, пожалуйста."    elif identi == 'end':        answer = "Спасибо за ваше участие в интервью!"    else:        # if top-level question: 1, 2 or 3 etc.        if len(identi) == 1:            # define in which way we are goin to?            way = _define_conversation_way(user_message, identi)            if way == 'yes' or way == 'no':                if way == 'yes':                    # going to positive way                    question_num = '.'.join([identi,'1','1'])                if way == 'no':                    # going to negative way                    question_num = '.'.join([identi,'2','1'])                answer = get_question_from_DB(question_num)                if answer != 'None':                    write_current_question_number_for_user(user_id, question_num)                else:                    question_num = str(int(identi) + 1)                    answer = get_question_from_DB(question_num)                    write_current_question_number_for_user(user_id, question_num)            else:                # if way='I dont know'                answer = "Я не совсем вас понимаю...\nУточните, пожалуйста."        else:            # if subquestion: e.g. identi=2.1.1 or 3.2.2 etc.            identi_numbers = identi.split('.')            next_num = str(int(identi_numbers[2]) + 1)            question_num = '.'.join([identi_numbers[0],identi_numbers[1],next_num])            answer = get_question_from_DB(question_num)            # if we get end of subquestions in this top-level-question            if answer == 'None':                # going to the next top-level question                question_num = str(int(identi_numbers[0]) + 1)                # checking that the question is the last                if _is_the_last_question(question_num):                    answer = get_question_from_DB(question_num)                    question_num = 'end'                else:                    # is not the last question                    answer = get_question_from_DB(question_num)                        write_current_question_number_for_user(user_id, question_num)            return answerdef _is_the_last_question(question_num):    """    define is the last question?    by the condition (len(identi) == 1) of the function "get_bot_answer"    question_num has lenght 1    """    is_the_last = True    question_num = str(int(question_num) + 1)    question = get_question_from_DB(question_num)    if question != 'None':        is_the_last = False    return is_the_lastdef _define_conversation_way(user_message, identi):    """    define in which way we are goin to?    """    # all questions, unless  3 has two ways: 'yes' (positive) or 'no' (negative)    if identi != '3' and identi != '6':        yes = _get_similarity(user_message, u'да заказывал просить')        no = _get_similarity(user_message, u'нет никогда')    elif identi == '6':        # the question number 6 has different ways: 'delivery' or 'self-delivery'        yes = _get_similarity(user_message, u'заказываю доставку')        no = _get_similarity(user_message, u'еду сам ищу аналог')    elif identi == '3':        # the question number 3 has different ways: 'from store' or 'delivery'        yes = _get_similarity(user_message, u'магазин сам')        no = _get_similarity(user_message, u'доставка почта все перечисленное курьер дом')    if yes > no and yes > 0.15:        _way = 'yes'    elif no > yes and no > 0.15:        _way = 'no'    else:        _way = 'I dont know'    return _waydef _identify_phrase(user_id, user_message):    """    identify start question or greeting    return number of phrase in database    """    # identification variable, on start set "I don't know"    identi = 'I dont know'    # find in database current position in conversation between user and chatbot    identi = get_current_position_in_conversation(user_id)    if identi != 'err':        # if the conversation has just begun        if identi == '0':            # define greetings            similarity = _get_similarity(user_message, u'привет здравствуйте добрый')            if similarity > 0.5:                identi = "greetings"            else:                # define start interview or not                identi = _start_or_not(user_message)        # if the conversation continues        elif identi == '1':            # define start interview or not            identi = _start_or_not(user_message)        else:            pass                return identidef _start_or_not(user_message):    """    define <identi>: start or don't start interview    """    if user_message != 'старт' or user_message != 'Старт':        _identi = 'I dont know'        # define if user agree to start interview        start = _get_similarity(user_message, u'да можем можно начинай ок')        # define if user don't agree to start interview        later = _get_similarity(user_message, u'нет позже потом завтра')        if start > later and start > 0.15:            _identi = 'start'        elif later > start and later > 0.15:            _identi = 'later'    else:        _identi = "start"    return _identidef _clear_text(sentence):    """    delete stop-words and punctuation characters in sentence    """    try:        # sentence to low-case        sentence = sentence.lower()        # delete stop-characters        for char in stop_characters:            sentence = sentence.replace(char, '')        # delete stop-words        words_of_sentence = sentence.split(' ')        result = ''        for word in words_of_sentence:            if word not in stop_words:                result = result + ' ' + word    except Exception as e:        result = str(e)    return resultdef _get_similarity(text1, text2):    """    Function return similarity between text1 and text2    :param text1: user message    :param text2: key words    """    text1.strip()  # delete empty space on start and end of string    text2.strip()    text1_words = text1.split(' ')    text2_words = text2.split(' ')    similarity = 0.0 # init variable    try:        for word1 in text1_words:            if word1 != '':                for word2 in text2_words:                    if word2 != '':                        # prepare url for request to API rusvectores.org                        # url example http://rusvectores.org/araneum_none_fasttextcbow_300_5_2018/дело__папка/api/similarity/                        url = '/'.join(['http://rusvectores.org/araneum_none_fasttextcbow_300_5_2018',                                         word1 + '__' + word2, 'api', 'similarity/'])                        # GET request to API rusvectores.org                        r = requests.get(url, stream=True)                        # sum similarity of couple of words                        similarity = similarity + float(r.text.split('\t')[0])    except Exception as e:        log_exception = str(e)    # average similarity    similarity = similarity/len(text2_words)    # return similarity between text1 and text2    return similarity

Как видно из кода, с помощью метода write_current_question_number_for_user() бот сохраняет в базу данных текущую позицию в диалоге с пользователем. Это необходимо для того, чтобы бот понимал какой следующий вопрос нужно задать респонденту.
Функция get_question_from_DB() возвращает текст вопроса из базы данных для того, чтобы бот задал его в чате.

Для удобства приведу полный код модуля с методами для работы с базой данных:

mysqldb_methods.py
_{полный код модуля, в котором реализованы все методы для работы с MySQL базой данных}

# -*- coding: utf-8 -*-"""Methods for work with MySQL database."""import MySQLdb  # before using it do in ssh: pip install mysqlclient""" import configuration variables for connect to MySQL database:"""from mysqldb_config import HOSTfrom mysqldb_config import USERfrom mysqldb_config import PASSWORDfrom mysqldb_config import DATABASEdef write_current_question_number_for_user(user_id, question_num):    """    write question number to database for this user    """    try:        conn = MySQLdb.connect(host=HOST, user=USER, passwd=PASSWORD,                                db=DATABASE, charset='utf8', init_command='SET NAMES UTF8')        cursor = conn.cursor()        if question_num == '2':            query = (                "INSERT INTO `conversations`(`user_id`, `question_num`) "                "VALUES (%s, %s)"            )            data = (user_id, question_num)        else:            query = (                "UPDATE `conversations` "                "SET `question_num`=%s "                "WHERE `user_id`=%s "            )            data = (question_num, user_id)        cursor.execute(query,data)        conn.commit()  # commit transaction        conn.close()    except Exception as e:        exception = str(e)def get_current_position_in_conversation(user_id):    """    find in database current position in conversation between user and chatbot    using in bot_methods.py    """    try:        conn = MySQLdb.connect(host=HOST, user=USER, passwd=PASSWORD,                                db=DATABASE, charset='utf8', init_command='SET NAMES UTF8')        cursor = conn.cursor()        query = "SELECT `question_num` FROM `conversations` WHERE `user_id`=%(user_id)s LIMIT 1"        cursor.execute(query, {'user_id': user_id})        result = cursor.fetchone()        if result is None:            identi = '0'        else:            identi = result[0]        conn.close()    except Exception as e:        identi = 'err'        return identidef get_question_from_DB(question_num):    """    return question text from database    """    try:        conn = MySQLdb.connect(host=HOST, user=USER, passwd=PASSWORD,                                db=DATABASE, charset='utf8', init_command='SET NAMES UTF8')        cursor = conn.cursor()        query = "SELECT `question_text` FROM `questions` WHERE `question_num`=%(num)s LIMIT 1"        cursor.execute(query, {'num': question_num})        result = cursor.fetchone()        if result is not None:            question_text = result[0]        else:            question_text = "None"        conn.close()    except Exception as e:        question_text = str(e)        return question_text

Теперь для полноты картины приведу код скрипта, который резюмирует всю логику работы чат-бота.

скрипт views.py
_{"точка входа" для приёма сообщений пользователя и отправки ответов бота в чат}

# -*- coding: utf-8 -*-from __future__ import unicode_literalsimport jsonimport threading  # for async executing tasks with VK APIimport vk  # vk is library from VKfrom django.views.decorators.csrf import csrf_exemptfrom django.shortcuts import renderfrom django.http import HttpResponsefrom bot_config import *  # import token, confirmation_token and over constants from bot_config.pyfrom bot_methods import get_bot_answer@csrf_exempt  # exempt index() function from built-in Django protectiondef index(request):  # requested url    if (request.method == "POST"):        data = json.loads(request.body)  # take POST request from auto-generated variable <request.body> in json format        if (data['secret'] == secret_key):  # if json request contain secret key and it's equal my secret key            if (data['type'] == 'confirmation'):  # if VK server request confirmation                """                For confirmation my server (webhook) it must return                confirmation token, which issuing in administration web-panel                your public group in vk.com.                Using <content_type="text/plain"> in HttpResponse function allows you                response only plain text, without any format symbols.                Parameter <status=200> response to VK server as VK want.                """                # confirmation_token from bot_config.py                return HttpResponse(confirmation_token,                                     content_type="text/plain",                                     status=200)            if (data['type'] == 'message_new'):  # if VK server send a message                # t - is new thread to async execute answer_to_message()                t = threading.Thread(target=_answer_to_message, args=(data,))                t.start()                return HttpResponse('ok', content_type="text/plain", status=200)    else:        return HttpResponse('see you :)')# send anser to user messagedef _answer_to_message(data):    session = vk.Session()    api = vk.API(session, v=5.5)    user_id = data['object']['user_id']    user_message = data['object']['body']    # get bot answer    answer = get_bot_answer(user_id, user_message)    # token from bot_config.py    api.messages.send(access_token = token, user_id = str(user_id), message = answer)

О том как устроена структура файлов приложения, его настройка описывал в подробностях в отдельной статье на Хабре.

Успехов!

Уверен, что для решения изложенной задачи существуют более элегантные решения и подходы. Несмотря на это надеюсь что изложенный материал найдёт своего читателя и будет полезен как в целом, так и в каких-то аспектах.
Желаю всем интересных проектов и успехов в их реализации!

Одни из самых интересных задач в работе администратора приложений, на мой взгляд, это осуществление миграции данных при переходе на новую систему. Сегодня я хочу поделится собственным опытом переноса данных с не очень известной helpdesk системы VisionFlow в более известную систему ServiceNow.

Что хотел заказчик

• Перенести все данные из VisionFlow в ServiceNow с сохранением даты регистрации / закрытия тикетов

• Перенести всю историю переписки по каждому тикету (достаточно было объединить все комментарии в один тред, но мы пошли чуть дальше)

• Перенести все прикреплённые к тикетам файлы

Что мы имели

• Серверную версию Helpdesk системы VisionFlow развёрнутую на виртуальной линукс машине с БД MySQL для хранения данных.

• ServiceNow инстанс, с подготовленной заранее таблицей для заказчика.

  На данном этапе были обговорены все нюансы, такие как:

• Статусная модель

• Требуемые поля

• Логика автоматического назначения тикетов на исполнителя

• Данные требующие переноса

Перенос данных

ServiceNow позволяет использовать excel файлы в качестве ресурса для импорта данных. Не буду подробно расписывать процесс импорта данных в систему (процесс неплохо описан в документации к продукту), но в общих чертах он выглядит так:

Так как было решено использовать xlsx файл в качестве источника данных, файл требовалось подготовить. Требуемые данные VisionFlow хранит в разных таблицах своей базы данных. Для получения данных был написан запрос к БД:

SELECTprojectissue.projectIssueId,projectissue.ticketId as 'Number',    reporter.email as 'Reporter',    projectissue.name as 'Short Description',    projectissue.Description as 'Description',    projectissue.companycustomfield15 as 'Product',    projectissue.companycustomfield13 as 'Document',    issuestatus.name as 'Status',    assignee.name as 'Assignee',    ADDTIME(projectissue.CreateDate, '-01:00') as 'Created',    ADDTIME(projectissue.completionDate, '-01:00') as 'Closed',    issuehistory.EventText as 'Comment',    author.name as 'commentAuthor'FROMprojectissueINNER JOIN issuestatus    ON projectissue.IssueStatusId = issuestatus.IssueStatusIdINNER JOIN systemuser assigneeON projectissue.ResponsibleSystemUserId = assignee.SystemUserIdINNER JOIN systemuser reporterON projectissue.CreatedBySystemUserId = reporter.SystemUserIdINNER JOIN issuehistory ONissuehistory.ProjectIssueId = projectissue.ProjectIssueIdINNER JOIN systemuser author ON issuehistory.SystemUserId = author.SystemUserIdWHEREprojectissue.ProjectId = 54 AND (issuehistory.IssueEventTypeId = 5 OR issuehistory.IssueEventTypeId = 10 OR issuehistory.IssueEventTypeId = 2)    #projectissue.ProjectId = 54ORDER BY projectissue.TicketId ASC, issuehistory.EventDate ASC

Выполнение запроса позволило нам получить данные по всем тикетам из определённого проекта, включая историю комментариев по каждому отдельному элементу, с сохранением авторства и даты добавления. Вторым шагом данные были выгружены в JSON и залиты в Excel документ. После загрузки документа в ServiceNow в качестве Data Source была проведена обработка записей и создание / обновление тикетов в системе.

Результат: В системе ServiceNow зарегистрированы все записи из VisionFlow, включая дату открытия и закрытия тикетов, комментариев (и их авторов) с соблюдением исходного порядка и всех ключевых полей. Т.к. таблица на момент переноса была пуста, проблем с подменой даты создания тикетов не возникло (ничего такого, чтобы могло повлиять на нумерацию).

Перенос вложений

Если первая часть миграции (требующая переноса исключительно текстовой информации) не потребовала больших усилий, то над переносом вложений пришлось попотеть. Сначала требовалось выяснить, а как VisionFlow, собственно, эти аттачи хранит.

Выполняем запрос к БД для получения всех данных по вложениям переносимого проекта:

SELECT document.documentId,document.name,    document.FullPath,SUBSTRING_INDEX(SUBSTRING_INDEX(document.FullPath, '/', -2), '/', 1) as 'projectIssueId',    projectissue.ticketId as 'Number' FROM visionflow.document INNER JOIN projectissue ON projectissue.ProjectIssueId = SUBSTRING_INDEX(SUBSTRING_INDEX(document.FullPath, '/', -2), '/', 1) WHERE document.FullPath like '%/54/issuedocuments/%'ORDER BY projectissueid

Данный запрос позволил нам получить информацию о том, как и где VisionFlow хранит вложения. К нашему счастью, оказалось, что VF создаёт отдельную папку для каждого проекта, в которой создаёт набор папок для тикетов, в которых вложения присутствуют. Папки имеют в качестве названия issueId, позволяющее однозначно идентифицировать принадлежность к тикету. Собственно, запрос выше позволят нам получить наименование папки, в которой лежит вложение и TicketId (его мы использовали для переноса данных в ServiceNow).

Выгрузив архив с вложениями, встал вопрос о сопоставлении файлов с тикетами в ServiceNow и их проливке в систему. Т.к. я недавно начал изучать Python, я подумал, что решение данной задачи будет неплохой практикой в языке.

Для добавления вложений в ServiceNow было решено использовать API attachments. Для этого на стороне SN был создан endpoint для получения временного токена с доступами к нужной таблице.

ServiceNow предоставляет code samples для их API. По документации мы видим, что нам потребуются следующие параметры для нашего запроса:

file_name (Required) - имя добавляемого файла

table_name (Required) - имя таблицы, в которой запись хранится

table_sys_id (Required) - ID записи, в которую требуется добавить вложение

Content-Type (Header) - mime type передаваемого контента

Как мы видим, вложение имеет связку с sys_id записи, к которой он принадлежит ( как и в VisionFlow). Следовательно, нам достаточно переименовать папки, которые мы загрузили из VisionFlow в sys_id записей, к которым мы будем их крепить. Для этого был выгружен список sys_id + ticketId из ServiceNow + список issueId + ticketId из VisionFlow. С помощью VLOOKUP функции Excel списки были сопоставлены и создан новый список с полями:

• old_folder_name

• ticket_id

• new_folder_name

На Python был написан скрипт для переименования папок и удаления тех, в которых не было найдено файлов (прогрессбар в данном случае был добавлен только для тренировки):

import pandas as pd, os from tqdm import tqdmdef renameFolders():    df = pd.read_csv('/Downloads/folder_rename.csv')    pbar = tqdm(total=len(df))    for _ , row in df.iterrows():        old_name = row['old_folder_name']        new_name = row['new_folder_name']        try:            os.rename(f'/Downloads/home/tomcat/vflowdocs/54/issuedocuments/{old_name}', f'/Downloads/home/tomcat/vflowdocs/54/issuedocuments/{new_name}')            pbar.update(1)        except:            pbar.update(1)def removeEmptyFolders():    folder_list = os.listdir('/Downloads/home/tomcat/vflowdocs/54/issuedocuments/')    for folder in folder_list:        path = f'/Downloads/home/tomcat/vflowdocs/54/issuedocuments/{folder}'        try:            os.rmdir(path)        except:            if len(os.path.basename(path)) < 6 and os.path.basename(path) != 'nan':                print(f'ServiceNow SysId not found for item: {os.path.basename(path)}')renameFolders()removeEmptyFolders()

Не буду расписывать скрипт для заливки вложений, опишу лишь несколько моментов:

• В скрипт добавлена проверка размера вложений, для того, чтобы отсеять всё то, что имеет вес менее 3000 kb (различные иконки, картинки из подписей и другой мусор) def getSize()

• Добавлен метод для удаления дубликатов аттачей. В VisionFlow каждое повторно пересылаемое вложение создавало новый файл документа def removeDuplicates()

• Добавлена обработка файлов с mime типом None. По какой-то причине mimetypes не возвращает типы для формата *msg, *txt, *eml

• Реализован финальный лог по операциями на основе ответов от сервера

• Ну и последнее (но мне, как любителю всё смотреть визуально, не менее важное) - прогрессбар для отслеживания процесса загрузки

import os, glob, filetype, requests, mimetypesfrom tqdm import tqdmimport pandas as pddef number_of_files():    files_number = 0    folder_list = os.listdir('/Downloads/home/tomcat/vflowdocs/54/issuedocuments/')    for folder in folder_list:       files_number += len(os.listdir(f'/Downloads/home/tomcat/vflowdocs/54/issuedocuments/{folder}/'))    return files_number#Progress Barpbar = tqdm(total=1297)log_messages_status = []log_messages_filepath = []log_messages_filename = []log_messages_target = []def uploadAllFiles(folder_name):    #Variables    entire_list = glob.glob(f'/Downloads/home/tomcat/vflowdocs/54/issuedocuments/{folder_name}/*')    my_list_updated = []    #Get Files Size    def getSize(fileobject):        fileobject.seek(0,2)        size = fileobject.tell()        return size    #Upload Files    def uploadFunc(filename, sys_id, path_to_file, content_type):        url = f'https://instance.service-now.com/api/now/attachment/file?file_name={filename}&table_name=table_name&table_sys_id={sys_id}'        payload=open(path_to_file, 'rb').read()        headers = {            'Accept': 'application/json',            'Authorization': 'Bearer ',            'Content-Type': content_type,            }                    response = requests.request("POST", url, headers=headers, data=payload)        if response.status_code == 201:            #print(f'Success: {filename} was uploaded to the incident with sys_id {sys_id}')            pbar.update(1)            log_messages_status.append('Success')            log_messages_filename.append(filename)            log_messages_filepath.append(path_to_file)            log_messages_target.append(sys_id)        else:            pbar.update(1)            #print(f'Error: {filename} was not uploaded to the incident with sys_id {sys_id}')            log_messages_status.append('Error')            log_messages_filename.append(filename)            log_messages_filepath.append(path_to_file)            log_messages_target.append(sys_id)    #Remove Duplicates    def removeDuplicatesByName(list_of_elements):        list_of_elements.sort()        if len(list_of_elements) > 1:            for item in list_of_elements:                item_to_compare = item.split('.')[0]                for element in list_of_elements:                    if item_to_compare in element:                        entire_list.remove(element)                    else:                        pass            return list_of_elements        else:            return list_of_elements    my_list = removeDuplicatesByName(entire_list)    for item in my_list:        file_size = open(item, 'rb')        if getSize(file_size) > 3000:            my_list_updated.append(item)        else:            pass    for attach in my_list_updated:        kind = filetype.guess_mime(attach)        if kind != None:            uploadFunc(os.path.basename(attach), os.path.dirname(attach).split('/')[-1], attach, kind)        elif kind == None and attach.split('.')[-1] == 'txt':            uploadFunc(os.path.basename(attach), os.path.dirname(attach).split('/')[-1], attach, 'text/plain')        else:            uploadFunc(os.path.basename(attach), os.path.dirname(attach).split('/')[-1], attach, 'application/octet-stream')        def getFolders():    folder_list = os.listdir('/Downloads/home/tomcat/vflowdocs/54/issuedocuments/')    for folder in folder_list:        if folder != '.DS_Store':            uploadAllFiles(folder)            getFolders()data_to_write = pd.DataFrame({    'status': log_messages_status,    'file_name' : log_messages_filename,    'file_path' : log_messages_filepath,    'target' : log_messages_target})data_to_write.to_csv('/Downloads/results_log.csv')

Заключение

У нас было 2 пакетика.. У нас было 6000 тысяч записей к переносу (не так много, старая система работала не долго), 2000 вложений и немного времени. Процесс подготовки занял у меня около 14 часов (изучение, попытки и т.д.) неспешной работы, а процесс переноса занимает около 30 минут суммарно.

Конечно, можно было бы многое улучшить, полностью автоматизировать процесс (начиная с выгрузки данных, заканчивая их проливкой), но, к сожалению, данная задача одноразовая. Было интересно попробовать Python для реализации проекта, и могу сказать, что с такой задачей он помог справится на ура.

В конечном счёте, основная задача переезда - сделать это максимально незаметно для заказчика, что и было сделано с моей стороны.

Всем привет! Меня зовут Александр и я пишу код для 2ip.ru. За добрую половину сервисов можно пинать меня, готов отбиваться. Cегодня я хочу немного рассказать про переделку одного нашего старого сервиса. Это конечно не "big data", но всё равно довольно большие объемы информации, поэтому думаю будет интересно.

Речь пойдет про Сайты на одном IP, который как вы уже догадались, позволяет узнать все домены зарегистрированные на одном IP. Довольно удобно посмотреть кто присосался к вашему серверу (да, есть и такие), ну или чужому (например shared хостинг).

Как это всегда работало? Мы ходили в Bing с большого пула адресов и парсили выдачу по специальному запросу. Да, решение так себе, но что было то было. Было, потому что бинг прикрутил гайки и мы решили всё это сделать по человечески.

Своя база

Что если взять и спарсить весь интернет? В общем то не проблема, но мы не Google и больших ресурсов для кролинга не имеем. Или имеем?

Есть сервер с 12 ядрами и 64 гигами памяти, а в арсенале MySQL, PHP, golang и куча всяких фреймворков. Очевидно, что благодаря горутинам можно достичь неплохих результатов. Golang быстр и требует минимум ресурсов. По базе вопросы, потянет ли это все обычный MySQL?

Пробуем.

Делаем прототип

Собирать все домены дело неблагодарное, поэтому мы купили базу доменов размером в 260 миллионов записей. Сервисов, которые предоставляют свои услуги довольно много и стоит это копейки.

Итак на моем диске CSV файл размером 5 ГБ, дело за малым, написать масс ресолвер, который будет читать строку за строкой, а на выход в STDOUT, отдавать пару "домен - IP адрес"

Вопрос только в производительности, нужно делать это очень и очень быстро, мы не можем ждать результата месяц.

Несколько часов работы и мой демон на гоу готов. Мэйн получился примерно такой:

func main() {    file, err := os.Open("domains.txt")    if err != nil {        log.Fatal(err)    }    defer file.Close()    maxGoroutines := 500    guard := make(chan struct{}, maxGoroutines)    scanner := bufio.NewScanner(file)    for scanner.Scan() {        guard <- struct{}{}        host := scanner.Text()        go func(host string) {            resolve(host)            <-guard        }(host)    }    if err := scanner.Err(); err != nil {        log.Fatal(err)    }}

Суть в том, что мы можем набрать пул в 500 горутинов и непрерывно обрабатывать большое количество данных в единицу времени, подгружая равномерно все 12 ядер сервера.

Функция resolve опущена, но кратко это обычный ресолвер IP с выдачей результата в STDOUT. Обращаемся к DNS, получаем A записи, выдаем результат.

DNS

Прогуглив немного я понял, что большие DNS особо не лимитируют количество запросов с одного IP, но кто знает. Поэтому было принято решение поднять undbound из Docker.

В несколько кликов я получил рекурсивный DNS, попробовал запустить и разочаровался. Так как DNS рекурсивный и кеша у него никакого нет, обращения сначала идут к корневым, а дальше разбегаются по зонам. Происходит это очень и очень медленно, порядка десятка доменов в секунду.

Второй вариант Google DNS, тот который четыре восьмерки, оказался гораздо быстрее. У меня были опасения по лимитам в 500 запросов в секунду но по факту их нет.

Тестируем в localhost и на проде

Нельзя сказать что на тестовом ноутбуке граббер работал быстро. 500 горутинов машина не потянула, процесс падал через несколько секунд. Зато все кардинально поменялось на боевом сервере.

1000 горутинов упали на 12 ядрах, а вот 500 практически не грузили проц и работали стабильно. Мощность получилась на уровне ~2000 доменов в секунду.

Это приемлемо, при таком раскладе всю базу можно спарсить за пару суток. На практике все оказалось немного хуже, очень сильно тупит TLD .bar, о которой вероятно нормальный человек и не слышал.

В конечном счёте я оставил процесс в tmux и через трое суток получил CSV размером 10 Гб. Идём дальше.

Ура! Переходим к следующему шагу.

База данных

Я создал таблицу domain_ip, в которой всего два столбца домен и IP. Оба не уникальны, на один домен может приходиться несколько IP адресов.

IP - это обычный BIGINT domain - VARCHAR 255

Индексы

Очевидно, что выборка из 260 млн записей это довольно большая работа. Поэтому без индексов не обойтись, поиск у нас по IP адресу, значит его и индексируем.

20 минут импорта на тестовой машине и я понял что это фиаско, выборка работает медленно несмотря на индексы. 260 млн записей это очень много. Переходим к плану Б.

Партиципирование

Это метод разделения больших таблиц на мелкие и в дальнейшем уже обращение по нужному адресу сразу к конкретной таблице.

Я разделил весь пул IP адресов на 20 таблиц с шагом 200 млн. Получилось примерно так:

ALTER TABLE domain_ip PARTITION BY RANGE COLUMNS (ip)  (    PARTITION p0 VALUES LESS THAN (200000000),    PARTITION p1 VALUES LESS THAN (400000000),    PARTITION p2 VALUES LESS THAN (600000000),    PARTITION p3 VALUES LESS THAN (800000000),    PARTITION p4 VALUES LESS THAN (1000000000),    PARTITION p5 VALUES LESS THAN (1200000000),    PARTITION p6 VALUES LESS THAN (1400000000),    PARTITION p7 VALUES LESS THAN (1600000000),    PARTITION p8 VALUES LESS THAN (1800000000),    PARTITION p9 VALUES LESS THAN (2000000000),    PARTITION p10 VALUES LESS THAN (2200000000),    PARTITION p11 VALUES LESS THAN (2400000000),    PARTITION p12 VALUES LESS THAN (2600000000),    PARTITION p13 VALUES LESS THAN (2800000000),    PARTITION p14 VALUES LESS THAN (3000000000),    PARTITION p15 VALUES LESS THAN (3200000000),    PARTITION p16 VALUES LESS THAN (3400000000),    PARTITION p17 VALUES LESS THAN (3600000000),    PARTITION p18 VALUES LESS THAN (3800000000),    PARTITION p19 VALUES LESS THAN (4000000000),    PARTITION p20 VALUES LESS THAN (MAXVALUE) );

И как вы поняли это сработало, иначе зачем эта статья? :)

Импорт

Кто работал с MySQL знает, что вливать большие дампы данных это довольно долгая операция. За долгие годы работы я не нашел ничего лучше, чем импорт данных из CSV. Выглядит это примерно так:

LOAD DATA INFILE '/tmp/domains.csv' IGNORE INTO TABLE domain_ipFIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'

Машина переваривает CSV размером ~10 Гб за 30 минут.

Финал

Как результат получился вот такой милый сервис. Выборка из ~300 миллионов записей происходит мгновенно на довольно скромном по нынешним меркам сервере. Оперативной памяти нужно под это всё порядка 8 Гб.

Теперь можно узнать например, что к IP 8.8.8.8 человечество прицепило 8194 домена, ну или придумайте сами ... ;-)

Спасибо за внимание.

Всем привет!

Оказалось, что PowerBI не имеет встроенной возможности настроить доступ к БД, защищённой SSH-туннелем. Приходится выкручиваться. Мне очень помогла эта статья - спасибо тебе добрый и компетентный в написании инструкций человек, без тебя я бы впала в отчаяние.

И тем не менее, в ней раскрыты не все нюансы. В своём материале я добавлю следующее:

• Два уникальных совета, как сделать так, чтобы установленный туннель не падал после авторизации

• Дополнительная инструкция для подключения к SSH при помощи приватного ключа, а не логина и пароля

• Скрины из самого PowerBI с настройкой БД и советы о том, как работает выборка из подключенной БД и как обновлять данные, полученные по SQL-запросам.

• Плюс я ориентирую свой материал на продуктовых аналитиков и аналитиков данных, то есть на тех, у кого нет доступа на редактирование БД и кто может не знать, что такое проброс портов и SSH-tunnel в принципе.

Итак, поехали.

Вам понадобится(этап подготовки):

1. Установленный Putty. Можно взять здесь - https://www.putty.org/

2. Данные от вашего бекенда или девопса по списку:

   1. IP-адрес SSH-сервера;

   2. порт SSH-сервера;

   3. username для доступа на SSH-сервер;

   4. пароль для доступа или связка приватного и публичного ключа*

   5. IP-адрес самой БД (обычно 127.0.0.1);

   6. порт самой БД;

   7. название БД;

   8. логин доступа к БД (не то же самое, что username для доступа на SSH-сервер);

   9. пароль для доступа к БД.

_{*Выбирать пароль или ключ не вам, а Хранителю сервера, пароль проще, ключ - безопаснее. В статье будут инструкции на оба случая. Если у вас кейс с ключами, то на этапе подготовки вам надо сгенерить оба ключа, разместить их в нужную папку на вашем компьютере и отдать внешний ключ Хранителю сервера, чтобы он его туда добавил. На эту тему есть много инструкций в интернете, я не буду повторяться.}

Поднимаем SSH-туннель

1. Открываем Putty

2. В Category/Session вводим IP-адрес SSH-сервера, порт SSH-сервера и выставляем радио-баттон Close window on exit на позицию Never

   

3. Переходим в Category/Connection/SSH и ставим галочку на Dont start a shell or command at all

   

4. Переходим в Category/Connection/SSH/Tunnels, в поле Source port вбиваем порт самой БД, в поле Destination IP-адрес самой БД:порт самой БД. Жмём Add.

   

5. _{*пункт для тех, кто подключается с приватным ключом, если у вас случай с логином и паролем, то переходите сразу к 6 пункту инструкции}

   1. Запустите PuttyGen (установился на ваш компьютер вместе с Putty)

   2. Выберите в верхнем меню Conversions/Import Key

   3. В открывшемся окне Проводника откройте папку, куда вы сохранили файлы приватного и публичного ключа (пункт 2d списка Вам понадобится) и выберите файл приватного ключа. Иногда Windows делает этот файл скрытым. Возможно, вам надо будет включить отображение скрытых файлов в Проводнике, нажав на Вид и поставив галочку напротив Скрытые элементы

      

   4. Нажимаем Save private key. Даём ключу любое имя на латинице и сохраняем в папку с ключами.

   5. Возвращаемся в Putty. Переходим в Category/Connection/SSH/Auth и нажимаем Browse рядом с Private key file for authentication

      

   6. В открывшемся окне Проводника выбираем сохранённый в пункте 5d файл приватного ключа.

6. Переходим в Category/Session, в поле Saved Session вводим имя нашего туннеля (любое), жмём Save. Это позволит нам не вводить все настройки каждый раз заново. После чего жмём Open

   

7. В открывшемся окне Терминала рядом с Login as вводим username для доступа на SSH-сервер и жмём Enter

   

8. _{*пункт для тех, у кого авторизация по паролю, если вы авторизовались по связке ключей, то пропускайте этот пункт и переходите сразу к 9.}

   1. Вводим пароль для доступа на SSH-сервер и жмём Enter

      

Настройка PowerBI

SSH-туннель настроен, не закрывайте окно терминала Putty. Теперь переходим в PowerBI. Жмём Получить данные и выбираем База данных MySQL или База данных PostgreSQL в зависимости от того, что у вас. Интерфейс будет одинаковым, а вот вероятность успеха - разной, потому что MySQL И PostgreSQL используют разные драйвера. Убедитесь, что выбрали свою БД правильно.

1. В поле Сервер вводим IP-адрес самой БД:порт самой БД

2. В поле База данных вводим название БД

3. Жмём Расширенные настройки и в поле Инструкция SQL вставляем запрос, по которому нужно импортировать данные. Если вы его не напишете, PowerBI приконнектится ко всей БД, но не позволит вам вытаскивать из неё данные запросами и не позволит построить модели (или я не нашла как, если у вас есть успешный опыт, с удовольствием прочитаю его в комментах)

4. Жмём ok

   

5. Вводим логин доступа к БД и пароль для доступа к БД, жмём Подключение

   

6. Возможна вот такая ошибка, это ok

   

Как обновить данные из БД в PowerBI

1. Поднимаем SSH-туннель в Putty

2. Переходим в PowerBI и жмём Обновить. Все созданные запросы ещё раз отправятся на сервер и выгрузят свежую информацию

   

Привет!

Сегодня мы выпустили DataGrip 2021.1: наш самый мощный релиз за последние годы. И это не шутка!

Самое важное:

• Интерфейс для работы с правами доступа

• Контекстные шаблоны Live Templates

• Упрощенная навигация

• Легкое копирование источников данных

• Улучшенная сортировка

• Редактирование данных в MongoDB

• Поддержка Azure MFA

Редактирование прав

В окне редактирования объекта теперь можно изменять права на объект.

Также права можно добавлять и изменять в окне редактирования пользователя или роли. Напомним, что вызываются окна редактирования нажатием Cmd/Ctrl+F6.

Это работает в PostgreSQL, Redshift, Greenplum, MySQL, MariaDB, DB2, SQL Server и Sybase.

Контекстные шаблоны

Нас давно просили сделать как у других: чтобы для таблицы в проводнике можно было быстро сгенерировать простой запрос, например SELECT TOP 100 FROM %tableName%.

Мы всегда отвечали, что как только вы научитесь пользоваться шаблонами кода Live Templates, ваша жизнь изменится навсегда, и не надо будет тыкать мышкой во вложенные меню.

И это правда. Но бывает, что у пользователя открыт проводник и он находится в контексте нужного объекта в таком случае сгенерировать запрос к этому объекту на месте будет проще, чем переключаться в консоль, а потом находить этот объект, используя автодополнение. К тому же, многие все еще часто пользуются мышкой :)

В итоге мы сделали новый вид шаблонов кода контекстные шаблоны. Работают они так:

Посмотрим шаблон Select first N rows from a table. Найдите его в настройках:

Он выглядит, как обычный шаблон, и его можно использовать в таком качестве. Но у переменной $table$ есть специальное выражение dbObjectName(). Вы увидите это, нажав на Edit Variables. Так вот, именно это выражение делает шаблон контекстным, то есть значение в эту переменную можно подставить автоматически кликнув на любой объект в проводнике базы данных.

Важно отметить, что синтаксис этого шаблона работает не во всех базах, в нижней части окна перечислены диалекты, для которых такой код можно сгенерировать.

В настройках можно выбрать, будет ли запрос генерироваться в новую консоль или в активную.

Редактор данных

Редактирование данных в MongoDB

Этого нам не хватало для полноценной поддержки Монги, но теперь все на месте: вы можете редактировать данные а апдейт-запрос просматривать до того, как данные отправятся в базу.

<img src="http://personeltest.ru/aways/habrastorage.org/webt/1t/ux/8y/1tux8yvfyrs2eiha3byoj0ss2tu.png" />

Еще можно менять тип ячейки: из контекстного меню или панели инструментов при просмотре больших значений.

Сортировка

Сортировка стала более удобной:

• Мы добавили поле ORDER BY, а <Filter> переименовали в WHERE. Впишите в ORDER BY условия сортировки, чтобы получился работающий запрос.

• По умолчанию сортировка не накапливается: каждый клик по новой колонке сбрасывает сортировку по другим колонкам. Если хочется сортировать по нескольким колонкам сразу, используйте клик с зажатой клавишей Alt.

Если хотите отсортировать данные на стороне DataGrip, отключите Sort via ORDER BY. Конечно, в этом случае, сортируются данные только на текущей странице.

Теперь вы можете по умолчанию открывать таблицы отсортированными по числовому первичному ключу.

Панель инструментов

Мы немного обновили панель инструментов в редакторе данных: добавили кнопки Revert Changes и Find. Кнопки Rollback и Commit в режиме автоматического подтверждения транзакций скрываются.

Транспонирование однострочных результатов

Если в полученном результате одна строка, его удобно сразу смотреть в транспонированном виде. Мы сделали для этого настройку:

Навигация

Предсказуемые действия

Мы удалили эти настройки:

Если вы никогда не меняли их значения по умолчанию, то главное изменение в новой версии для вас такое: действие Go to declaration (Ctrl/Cmd+B) теперь открывает DDL этого объекта. Раньше оно подсвечивало объект в проводнике базы данных.

Для перемещения к объекту в проводнике мы представили новое сочетание клавиш: Alt+Shift+B для Windows/Linux и Opt+Shift+B для macOS.

Теперь нет сложной логики, и каждое нажатие горячих клавиш ведет в туда, куда вы ожидаете попасть:

• Ctrl/Cmd+B открывает DDL.

• F4 открывает данные.

• Alt/Opt+Shift+B подсвечивает объект в проводнике.

Удаление настроек всегда ломает привычки некоторому количеству людей. Мы постарались учесть это. Вот наши советы таким пользователям:

• Все сочетания клавиш можно менять. Если, например, вы не хотите отвыкать от того, что Ctrl/Cmd+B подсвечивает объект в проводнике, назначьте это сочетание клавиш действию Select in database tree.

• В то же время, если вам нравится, что внутри скрипта Ctrl/Cmd+B и Ctrl/Cmd+Click открывает определение CREATE, не убирайте эти сочетания с действия Go to declaration, если вы последовали предыдущему совету.

• Если вам нравилось, что при отключенной настройке Preview data editor over DDL editor двойной клик по таблице открывал DDL, это можно вернуть через ключ в реестре. Он называется database.legacy.navigate.to.code.from.tree. Но мы не советуем менять значения в реестре и надеемся, что те полпроцента людей, у которых эта галочка была снята, быстро привыкнут к новому поведению :)

Если мы про что-то забыли, пишите, пожалуйста, в комментах.

Вкладка Database

Тут мы ничего особо не сделали просто переименовали вкладку Tables в Database. Этим мы напоминаем, что по сочетанию клавиш Cmd+O/Ctrl+N можно искать не только таблицы, но и процедуры, функции, схемы.

Соединение

Поддержка Azure MFA

Мы поддержали интерактивную аутентификацию через Azure Active Directory. Если она включена, при соединении у вас автоматически откроется браузер, где вы сможете завершить аутентификацию.

Версия 2.x драйвера для Redshift

Этот драйвер можно скачать в DataGrip, начиная с версии 2021.1. Главное изменение состоит в том, что теперь запросы можно останавливать.

Полная поддержка Google Big Query

Подсветку языка мы поддержали в предыдущей версии, а в этой добавили интроспекцию. То есть, информацию об объектах IDE берет не из драйвера, а выкачивает ее сама при помощи специальных запросов.

Поддержка диалекта CockroachDB

Теперь DataGrip правильно подсвечивает запросы и показывает ошибки в скриптах для CockroachDB. Соответствующую интроспекцию мы сделаем в одном из следующих релизов.

Улучшения в окне соединения

Сделали это окно чуть более дружелюбным:

• Источники данных и драйверы разделены на две вкладки.

• На странице каждого драйвера появилась кнопка Create data source.

• Кнопка Test Connection переехала вниз теперь ее видно из всех вкладок, а не только из General и SSH/SSL.

• Для источников данных на основе файлов (так называемых DDL Data Sources) теперь можно явно задать диалект.

• Поле JDBC URL расширяется, потому что адреса для подключения бывают очень длинными.

Проводник базы данных

Легкое копирование источников данных

Возможность копировать и вставлять источники данных мы сделали давно. Но с этого релиза вы можете использовать самые знаменитые сочетания клавиш в мире Ctrl/Cmd+C/V/X.

• Напоминаем, что когда вы копируете источник данных, в буфер обмена сохраняется XML. Его можно послать коллеге в мессенджере, а он вставит его в свою IDE все сработает.

• Если копирование и вставка происходит в одном проекте, вам не понадобится заново вводить пароль.

• Источник данных можно не только копировать, но и вырезать. Вырезание отменяется при помощи Ctrl/Cmd+Z.

Новый интерфейс

Объекты второстепенной важности (роли, пространства имен, внешние источники и др.) мы поместили в папки Server Objects и Database Objects.

Если хотите чтобы было, как раньше, включите настройку Group Database and Schemas.

[Oracle] Скрытие сгенерированных объектов

Если отключить Show generated objects, то из проводника пропадут:

• Таблицы материализованных представлений

• Логи материализованных представлений

• Вторичные таблицы

[SQLite] Новые типы объектов

В SQLite теперь отображаются функции, модули и виртуальные столбцы.

Улучшения для неподдерживаемых баз

Шаблоны для источников данных

Начиная с этой версии стало легче создавать источники данных для баз, которые мы не поддерживаем. Вам не придется самим качать драйвер мы предложим сделать это за вас. Драйверы мы кладем к себе на сервер и обновляем, когда нужно. При создании источников данных ищите шаблоны в секции Other.

Еще раз напомним, что поддержка для таких источников данных базовая. Скрипты подсвечиваются на основе стандарта SQL:2016, а информация об объектах берется из драйвера.

Написание запросов

Инспекция про избыточные имена в CTE

Если запрос не запустится из-за избыточных имен в общем табличном выражении, DataGrip сообщит об этом.

[SQL Server] Системные функции можно использовать без имени схемы

Использовать системные функции без имени схемы можно было и раньше, но DataGrip считал это ошибкой :) Теперь они и подсвечиваются правильно, и для них работает навигация с автодополнение.

Поддержка JSON Lines

Формат JSON Lines используется для хранения данных и логов. И новая версия правильно подсвечивает файлы этого формата.

Толщина шрифта

Теперь вы можете настраивать толщину шрифта.

Импорт / Экспорт

Незагруженные данные

Если бинарные данные не были загружены полностью, вы увидите такое сообщение:

Настройка, которая определяет, какое количество данных DataGrip загружает по умолчанию, находится здесь: Settings/Preferences | Database | Data Views | Maximum number of bytes loaded per value.

Запрос в файле Excel

Когда вы экспортируете данные в Excel, на отдельном листе сохраняется изначальный запрос.

First row is header в контекстном меню импорта

Раньше было неудобно находить эту настройку, поэтому мы добавили ее в контекстное меню заголовков.

Интерфейс

Прикрепление папки при помощи drag-n-drop

Прикрепить папку, то есть открыть ее в панели Files, теперь можно, перетащив её.

Открытие вкладок в режиме разделенного редактора

Если редактор разделен вертикально на несколько вкладок, вы можете расширить окно редактора для любой из вкладок, кликнув по ней дважды. Чтобы вернуться в прежнее состояние, снова кликните дважды по окну.

Длинные названия вкладок

В одной из предыдущих версий мы укоротили названия вкладок. Это не всем понравилось, поэтому мы сделали настройку:

На этом все!

Скачать триал на месяц

Фидбек принимаем в комментариях к посту и здесь:

Трекер (это если точно нашли проблему)

Телеграм-канал

Твиттер

Почта

Команда DataGrip

Если вы работаете с большим количеством виртуальных машин, то Vagrant, вероятно, будет лучшим инструментом для их создания, автоматизации и управления, повышая эффективность и производительность развертывания (особенно для dev-окружений). ClusterControl также может быть развернут через Vagrant.

Vagrant поддерживает различные платформы виртуализации: VirtualBox, KVM, Hyper-V, контейнеры Docker, VMware и AWS. Мы в качестве провайдера виртуальных машин будем использовать VirtualBox, а для их провижининга Vagrant.

Скачать VirtualBox можно отсюда, а Vagrant отсюда. Установите их и можете двигаться дальше.

Развертывание ClusterControl через Vagrant

Для развертывания виртуальной машины с ClusterControl создайте следующие файлы:

• Vagrantfile определение и спецификация виртуальной машины.

• deploy-cc.sh bash-скрипт для установки ClusterControl.

Создайте каталог для Vagrantfile.

$ mkdir ~/vagrant/clustercontrol$ cd ~/vagrant/clustercontrol

Создайте Vagrantfile:

$ vim Vagrantfile

Добавьте в него следующие строки:

Vagrant.configure("2") do |config|  config.vm.box = "centos/7"  config.vm.network "forwarded_port", guest: 80, host: 8080  config.vm.network "forwarded_port", guest: 443, host: 8443  config.vm.network :private_network, ip: "192.168.11.11"  config.vm.hostname = "cc.local"  config.vm.provision "shell" do |s|    s.inline = "yum install -y vim wget"  end  config.vm.provision "shell", path: "deploy-cc.sh"   config.vm.provider :virtualbox do |vb|    vb.customize [      "modifyvm", :id,      "--memory", "4096",    ]  endend

Создайте сценарий развертывания deploy-cc.sh в этом же каталоге. Этот скрипт будет выполняться автоматически при каждом запуске виртуальной машины:

$ vim deploy-cc.sh

И добавьте в него следующие строки:

Vagrant.configure("2") do |config|  config.vm.box = "centos/7"  config.vm.network "forwarded_port", guest: 80, host: 8080  config.vm.network "forwarded_port", guest: 443, host: 8443  config.vm.network :private_network, ip: "192.168.11.11"  config.vm.hostname = "cc.local"  config.vm.provision "shell" do |s|    s.inline = "yum install -y vim wget"  end  config.vm.provision "shell", path: "deploy-cc.sh"   config.vm.provider :virtualbox do |vb|    vb.customize [      "modifyvm", :id,      "--memory", "4096",    ]  endend

Теперь мы готовы запустить нашу первую виртуальную машину. Состояние виртуальной машины можно проверить с помощью следующей команды:

$ vagrant status

Для создания и запуска виртуальной машины, выполните следующую команду:

$ vagrant up

На экране должно появиться много строк, говорящих о том, что Vagrant подготавливает виртуальную машину. Дождитесь завершения этого процесса. После завершения вы можете получить доступ к пользовательскому интерфейсу ClusterControl по адресу http://127.0.0.1:8080/ или https://127.0.0.1:8443/. Порт 8080 предназначен для HTTP-соединения, а 8443 для HTTPS, как определено в Vagrantfile для forwarded_port.

Развертывание кластера баз данных

Теперь у нас есть ClusterControl, готовый к управлению и мониторингу нашим кластером. С помощью Vagrant очень легко создать несколько виртуальных машин. В этом примере мы развернем Galera Cluster из трех узлов, работающий на Percona XtraDB Cluster 8.0, как показано на диаграмме ниже:

Кластер состоит из трех узлов баз данных и двух узлов ProxySQL для балансировки нагрузки MySQL. Весь этот набор можно рассматривать как одну группу узлов данного кластера. Поэтому определим спецификацию этих виртуальных машин в одном Vagrantfile. Сначала создайте каталог для нашего кластера баз данных:

$ mkdir ~/vagrant/clustercontrol/percona-xtradb-cluster-80/$ cd ~/vagrant/clustercontrol/percona-xtradb-cluster-80/

Затем создайте Vagrantfile.

$ vim ~/vagrant/clustercontrol/percona-xtradb-cluster-80/Vagrantfile

И добавьте в него следующие строки:

nodes = [  { :hostname => 'db1', :ip => '192.168.11.21', :ram => 1024, :guestport => 3306, :hostport => 13306 },  { :hostname => 'db2', :ip => '192.168.11.22', :ram => 1024, :guestport => 3306, :hostport => 23306 },  { :hostname => 'db3', :ip => '192.168.11.23', :ram => 1024, :guestport => 3306, :hostport => 33306 },  { :hostname => 'proxy1', :ip => '192.168.11.26', :ram => 512, :guestport => 6033, :hostport => 16033 },  { :hostname => 'proxy2', :ip => '192.168.11.27', :ram => 512, :guestport => 6033, :hostport => 26033 }] Vagrant.configure("2") do |config|  nodes.each do |node|    config.vm.define node[:hostname] do |nodeconfig|      nodeconfig.vm.box = 'centos/7'      nodeconfig.vm.hostname = node[:hostname] + ".local"      nodeconfig.vm.network :private_network, ip: node[:ip]      nodeconfig.vm.network "forwarded_port", guest: node[:guestport], host: node[:hostport]       memory = node[:ram] ? node[:ram] : 512;      nodeconfig.vm.provider :virtualbox do |vb|        vb.customize [ "modifyvm", :id, "--memory", memory.to_s ]        vb.customize [ "modifyvm", :id, "--audio", "none" ]      end       nodeconfig.vm.provision "shell" do |s|        s.inline = "yum install -y wget vim"      end    end  end   (1..3).each do |i|    config.vm.define "db#{i}" do |dbnode|      dbnode.vm.provision "shell", path: "authorize.sh"    end  end   (1..2).each do |i|    config.vm.define "proxy#{i}" do |dbnode|      dbnode.vm.provision "shell", path: "authorize.sh"    end  end end

В том же каталоге создайте скрипт authorize.sh и добавьте в него следующее содержимое:

CC_IP=192.168.11.11curl -s http://${CC_IP}/cc.pub >> /home/vagrant/.ssh/authorized_keys

Данный скрипт скачивает открытый ключ с сервера ClusterControl, созданный в deploy-cc.sh и сохраняет его в списке авторизованных ключей для пользователя vagrant. Это нужно для использования SSH без пароля, что является обязательным для узлов, управляемых ClusterControl.

Для создания и запуска виртуальных машин кластера выполните следующую команду:

$ vagrant up

Подождите, пока все виртуальные машины запустятся. Их статус можно проверить с помощью команды vagrant status:

$ vagrant status

Теперь пришло время развернуть Percona XtraDB Cluster, используя ClusterControl. Откройте ClusterControl по адресу http://127.0.0.1:8080/clustercontrol, перейдите в Deploy -> MySQL Galera и введите следующие данные:

В качестве пользователя SSH (SSH User) указываем vagrant, так как мы авторизовали его открытый ключ в списке authorized_keys на каждом из узлов при провижининге виртуальных машин с помощью Vagrant через authorize.sh.

Обратите внимание, что у всех виртуальных машин есть как минимум два сетевых интерфейса. Vagrant при работе с VirtualBox требует, чтобы первое сетевое устройство, подключенное к виртуальной машине, было устройством NAT. Данное NAT-устройство используется для перенаправления портов. И именно через этот механизм Vagrant получает SSH-доступ к виртуальной машине. Наш IP-адрес, определенный в Vagrantfile, будет использоваться на интерфейсах eth1, eth2 и т.д., что важно при их настройке на странице "Define MySQL Servers", показанной ниже:

При указании IP-адреса на втором шаге используйте IP-адрес, который мы указали в Vagrantfile (эквивалентен IP-адресу eth1 созданной виртуальной машины). Зеленая галочка рядом с IP-адресом указывает на то, что данный узел доступен через беспарольный SSH с сервера ClusterControl. Наконец, нажмите "Deploy" и дождитесь завершения развертывания.

После завершения вы должны увидеть следующее:

Развертывание балансировщиков нагрузки

Похожим образом готовим виртуальные машины для балансировщиков нагрузки с соответствующими IP-адресами и беспарольным SSH. Для развертывания ProxySQL перейдите в ClusterControl -> Manage -> Load Balancers -> ProxySQL -> Deploy ProxySQL и укажите первый балансировщик (192.168.11.26), как показано ниже:

Не забудьте включить "Use Native Clustering", так как у нас будет несколько хостов ProxySQL. Для начала развертывания нажмите кнопку "Deploy ProxySQL". За ходом выполнения задания можно следить в ClusterControl -> Activity -> Jobs. Повторите вышеуказанные шаги для второго балансировщика (192.168.11.27). После этого мы можем связать эти балансировщики с виртуальным IP-адресом (плавающий IP-адрес для единой конечной точки) с помощью Keepalived, перейдя в ClusterControl -> Manage -> Load Balancers -> Keepalived -> Deploy Keepalived и выбрав оба балансировщика нагрузки с виртуальным IP-адресом (192.168.11.20) на интерфейсе eth1, как показано ниже:

Мы развернули отказоустойчивый кластер баз данных, повторяющий продакшн-окружение.

Остановить все узлы кластера можно одной командой:

$ cd ~/vagrant/clustercontrol/percona-xtradb-cluster-80$ vagrant halt

Для остановки виртуальной машины с ClusterControl перейдите в каталог ClusterControl и выполните такую же команду:

$ cd ~/vagrant/clustercontrol$ vagrant halt

Для удаления виртуальных машин замените команду vagrant halt на vagrant destroy. Позже вы всегда сможете воссоздать их с помощью команды vagrant up.

Перевод статьи подготовлен в преддверии старта курса Базы данных.

Также приглашаем всех желающих посетить бесплатный демо-урок по теме: MySQL NDB Cluster шардинг.

Многим, кто работает с MySQL, известно, что команда EXPLAIN используется для оптимизации запросов, получения информации об использованных и возможных индексах. Большинство разработчиков и администраторов СУБД этой информацией и ограничивается. Я же предлагаю изучить команду EXPLAIN максимально подробно.

Логическая архитектура MySQL

Чтобы понять, как работает EXPLAIN, стоит вспомнить логическую архитектуру MySQL.

Её можно разделить на несколько уровней:

1. Уровень приложения или клиентский уровень. Он не является уникальным для MySQL. Обычно здесь находятся утилиты, библиотеки или целые приложения, которые подключаются к серверу MySQL.

2. Уровень сервера MySQL. Его можно разделить на подуровни:
   
   A. Пул соединений. Сюда относятся аутентификация, безопасность и обработка соединений/потоков. Всякий раз, когда клиент подключается к серверу MySQL, тот выполняет аутентификацию по имени пользователя, хосту клиента и паролю. После того, как клиент успешно подключился, сервер проверяет, имеет ли этот клиент привилегии для выполнения определенных запросов, и, если да, то он получает для своего соединения отдельный поток. Потоки кешируются сервером, поэтому их не нужно создавать и уничтожать для каждого нового соединения.

   B. Сервер MySQL. Этот подуровень во многих источниках называют мозгами MySQL. К нему относятся такие компоненты, как кеши и буферы, парсер SQL, оптимизатор, а также все встроенные функции (например, функции даты/времени и шифрования).

3. Уровень подсистем хранения. Подсистемы хранения отвечают за хранение и извлечение данных в MySQL.

Нас интересует второй уровень, точнее подуровень сервер MySQL, ведь именно здесь выполняет свои прямые обязанности оптимизатор запросов. Набор операций, которые оптимизатор выбирает для выполнения эффективного запроса, называется Планом выполнения запроса, также известного как EXPLAIN-план.

Команда EXPLAIN

Выражение EXPLAIN предоставляет информацию о том, как MySQL выполняет запрос. Оно работает с выражениями SELECT, UPDATE, INSERT, DELETE и REPLACE.

Для того, чтобы воспользоваться командой, достаточно поставить ключевое слово EXPLAIN перед запросом, и MySQL пометит запрос специальным флагом. Он заставит сервер возвращать информацию о каждом шаге, вместо выполнения. Однако, если в запросе встречается подзапросы в разделе FROM, то сервер будет вынужден выполнить все подзапросы и поместить их результаты во временную таблицу.

Стандартный вывод команды EXPLAIN покажет колонки:

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra

Представим, что у нас есть база данных нашей небольшой фирмы такси, в, которой хранятся водители, автомобили, клиенты и заказы.

Для начала выполним простой запрос:

EXPLAIN SELECT 1

id: 1select_type: SIMPLEtable: NULLpartitions: NULLtype: NULLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: NULLfiltered: NULLExtra: No tables used

Столбец ID

Этот столбец можно назвать идентификатором или порядковым номером каждого SELECT- запроса. Все выражения SELECT нумеруются последовательно, их можно отнести к простым (без подзапросов и объединений) и составным. Составные запросы SELECT можно отнести к:

A. Простым подзапросам

EXPLAIN SELECT (SELECT 1 from Orders) from Drivers

B. Подзапросам с производными таблицами, то есть с подзапросом в разделе FROM

EXPLAIN SELECT * FROM (SELECT 1, 2) AS tmp (a, b)

Как я уже писал выше, этот запрос создаст временную таблицу и MySQL будет ссылаться на неё по псевдониму tmp. В более сложных запросах этот псевдоним будет указан в столбце ref. В первой строке, в столбце table можно увидеть название таблицы , которое формируется по правилу , где N ID запроса.

C. Подзапросам с объединением UNION

EXPLAIN SELECT id FROM Cars UNION SELECT id FROM Drivers

Здесь есть несколько отличий от примера c FROM-подзапросом. Во-первых, MySQL помещает результат объединения во временную таблицу, из которой, затем, считывает данные. К тому же эта временная таблица отсутствует в исходной SQL-команде, поэтому в столбце id для неё будет null. Во-вторых, временная таблица, появившаяся в результате объединения, показана последней, а не первой.

Точно по такому же правилу формируется название таблица в столбце table <unionN,M>, где N ID первого запроса, а M второго.

Столбец select_type

Показывает тип запроса SELECT для каждой строки результата EXPLAIN. Если запрос простой, то есть не содержит подзапросов и объединений, то в столбце будет значение SIMPLE. В противном случае, самый внешний запрос помечается как PRIMARY, а остальные следующим образом:

• SUBQUERY. Запрос SELECT, который содержится в подзапросе, находящимся в разделе SELECT (т.е. не в разделе FROM).

• DERIVED. Обозначает производную таблицу, то есть этот запрос SELECT является подзапросом в разделе FROM. Выполняется рекурсивно и помещается во временную таблицу, на которую сервер ссылается по имени derived table.
  
  Обратите внимание: все подзапросы в разделе FROM являются производной таблицей, однако, не все производные таблицы являются подзапросами в разделе FROM.

• UNION. Если присутствует объединение UNION, то первый входящий в него запрос считается частью внешнего запроса и помечается как PRIMARY (см. пример выше). Если бы объединение UNION было частью подзапроса в разделе FROM, то его первый запрос SELECT был бы помечен как DERIVED. Второй и последующий запросы помечаются как UNION.

• UNION RESULT. Показывает результата запроса SELECT, который сервер MySQL применяет для чтения из временной таблицы, которая была создана в результате объединения UNION.

Кроме того, типы SUBQUERY, UNION и DERIVED могут быть помечены как DEPENDENT, то есть результат SELECT зависит от данных, которые встречаются во внешнем запросе SELECT.

Также типы SUBQUERY и UNION могут быть помечены как UNCACHABLE. Это говорит о том, что результат SELECT не может быть закеширован и должен быть пересчитан для каждой строки внешнего запроса. Например, из-за функции RAND().

Начиная с версии MySQL 5.7 в столбце select_type появилось еще одно значение MATERIALIZED, которое говорит о том, что результат подзапроса был сохранен как виртуальная временная таблица и не будет выполняться для каждой следующей строки внешнего запроса.

Столбец table

Показывает, к какой таблице относится эта строка. В самом простом случае это таблица (или её псевдоним) из вашей SQL- команды.

При объединении таблиц стоит читать столбец table сверху вниз.

EXPLAIN SELECT Clients.id        FROM Clients        JOIN Orders ON Orders.client_id = Clients.id        JOIN Drivers ON Orders.driver_id = Drivers.id

Здесь мы видим порядок объединения таблиц, который выбрал оптимизатор. Однако, порядок не всегда будет таким, в каком объединяются таблицы в вашем запросе.

Если запрос содержит подзапрос FROM или объединение UNION, то столбец table читать будет не так просто, потому что MySQL будет создавать временные таблицы, на которые станет ссылаться.

О столбце table для подзапроса FROM я уже писал выше. Ссылка derived.N является- опережающей, то есть N ID запроса ниже. А ссылка UNION RESULT (union N,M) является обратной, поскольку встречается после всех строк, которые относятся к объединению UNION.

Попробуем, для примера, прочитать столбец table для следующего странного запроса:

EXPLAIN SELECT id, (SELECT 1 FROM Orders WHERE client_id = t1.id LIMIT 1)       FROM (SELECT id FROM Drivers LIMIT 5) AS t1       UNION       SELECT driver_id, (SELECT @var1 FROM Cars LIMIT 1)       FROM (           SELECT driver_id, (SELECT 1 FROM Clients)           FROM Orders LIMIT 5       ) AS t2

Не так просто разобраться в этом, но, тем не менее, мы попробуем.

1. Первая строка является опережающей ссылкой на производную таблицу t1, помеченную как <derived3>.

2. Значение идентификатора строки равно 3, потому что строка относится к третьему по порядку SELECT. Поле select_type имеет значение DERIVED, потому что подзапрос находится в разделе FROM.

3. Третья строка с ID = 2 идет после строки с бОльшим ID, потому что соответствующий ей подзапрос выполнился позже, что логично, ведь нельзя получить значение t1.id, не выполнив подзапрос с ID = 3. Признак DEPENDENT SUBQUERY означает, что результат зависит от результатов внешнего запроса.

4. Четвертая строка соответствует второму или последующему запросу объединения, поэтому она помечена признаком UNION. Значение <derived6> означает, что данные будут выбраны из подзапроса FROM и добавятся во временную таблицу для результатов UNION.

5. Пятая строка - это наш подзапрос FROM, помеченный как t2.

6. Шестая строка указывает на обычный подзапрос в SELECT. Идентификатор этой строки равен 7, что важно, потому что следующая строка уже имеет ID = 5.

7. Почему же важно, что седьмая строка имеет меньший ID, чем шестая? Потому что каждая строка, помеченная как DERIVED , открывает вложенную область видимости. Эта область видимости закрывается, когда встречается строка с ID меньшим, чем у DERIVED (в данном случае 5 < 6). Отсюда можно понять, что седьмая строка является частью SELECT, в котором выбираются данные из <derived6>. Признак UNCACHEABLE в колонке select_type добавляется из-за переменной @var1.

8. Последняя строка UNION RESULT представляет собой этап считывания строк из временной таблицы после объединения UNION.

При чтении с помощью EXPLAIN больших запросов часто приходится читать результат сверху вниз и наоборот. Например, в этом примере имеет смысл начать чтение с последней строки, чтобы понять, что первая строка является частью UNION.

Столбец partitions

Показывает, какой партиции соответствуют данные из запроса. Если вы не используете партиционирование, то значение этой колонки будет null.

Столбец type

Показывает информацию о том, каким образом MySQL выбирает данные из таблицы. Хотя в документации MySQL это поле описывается как The join type, многих такое описание смущает или кажется не до конца понятным. Столбец type принимает одно из следующих значений, отсортированных в порядке скорости извлечения данных:

• ALL. Обычно речь идет о полном сканировании таблицы, то естьт.е. MySQL будет просматривать строчку за строчкой, если только в запросе нет LIMIT или в колонке extra не указано Distinct/not exists, к чему мы вернемся позже.

• index. В этом случае MySQL тоже просматривает таблицу целиком, но в порядке, заданном индексом. В этом случае не требуется сортировка, но - строки выбираются в хаотичном порядке. Лучше, если в колонке extra будет указано using index, что означает, что вместо полного сканирования таблицы, MySQL проходит по дереву индексов. Такое происходит, когда удалось использовать покрывающий индекс

• range. Индекс просматривается в заданном диапазоне. Поиск начинается в определенной точке индекса и возвращает значения, пока истинно условие поиска. range может быть использован, когда проиндексированный столбец сравнивается с константой с использованием операторов =, <>, >, >=, <, <=, IS_NULL, <=>, BETWEEN, LIKE или IN.

• index_subquery. Вы увидите это значение, если в операторе IN есть подзапрос, для которого оптимизатор MySQL смог использовать поиск по индексу.

• unique_subquery. Похож на index_subquery, но, для подзапроса используется уникальный индекс, такой как Primary key или Unique index.

• index_merge. Если оптимизатор использовал range-сканирование для нескольких таблиц, он может объединить их результаты. В зависимости от метода слияния, поле extra примет одно из следующих значений: Using intersect пересечение, Using union объединение, Using sort_union объединение сортировки слияния (подробнее читайте здесь)

• ref_or_null. Этот случай похож на ref, за исключением того, что MySQL будет выполнять второй просмотр для поиска записей, содержащих NULL- значения.

• fulltext. Использование FULLTEXT-индекса.

• ref. Поиск по индексу, в результате которого возвращаются все строки, соответствующие единственному заданному значению. Применяется в случаях, если ключ не является уникальным, то есть не Primary key или Unique index , либо используется только крайний левый префикс ключа. ref может быть использован только для операторов = или <=>.

• eq_ref. Считывается всего одна строка по первичному или уникальному ключу. Работает только с оператором =. Справа от знака = может быть константа или выражение.

• const. Таблица содержит не более одной совпадающей строки. Если при оптимизации MySQL удалось привести запрос к константе, то столбец type будет равен const. Например, если вы ищете что-то по первичному ключу, то оптимизатор может преобразовать значение в константу и исключить таблицу из соединения JOIN.

• system. В таблице только одна строка. Частный случай const.

• NULL. Означает, что оптимизатор смог сам найти нужные данные для запроса и обращаться к таблице не требуется. Например, для выборки минимального значения проиндексированного, в порядке возрастания, столбца достаточно выбрать первое значение из индекса.

Столбец possible_keys

Показывает, какие индексы можно использовать для запроса. Этот столбец не зависит от порядка таблиц, отображаемых EXPLAIN, поскольку список создается на ранних этапах оптимизации. Если в столбце значение NULL, то соответствующих индексов не нашлось.

Столбец keys

Указывает на ключ (индекс), который оптимизатор MySQL решил фактически использовать. Может вообще отсутствовать в столбце possible_keys. Так бывает в случаях, когда ни один из ключей в possible_keys не подходит для поиска строк, но все столбцы, выбранные запросом, являются частью какого-то другого индекса. Тогда оптимизатор может задействовать эти столбцы для поиска по покрывающему индексу, так как сканирование индекса эффективнее, чем сканирование таблицы целиком.

Столбец key_len

Показывает длину выбранного ключа (индекса) в байтах. Например, если у вас есть primary key id типа int, то, при его использовании, key_len будет равен 4, потому что длина int всегда равна 4 байта. В случае составных ключей key_len будет равен сумме байтов их типов. Если столбец key равен NULL, то значение key_len так же будет NULL.

EXPLAIN SELECT * FROM OrdersWHERE client_id = 1

EXPLAIN SELECT * FROM OrdersWHERE client_id = 1 AND driver_id = 2

Столбец ref

Показывает, какие столбцы или константы сравниваются с указанным в key индексом. Принимает значения NULL, const или название столбца другой таблицы. Возможно значение func, когда сравнение идет с результатом функции. Чтобы узнать, что это за функция, можно после EXPLAIN выполнить команду SHOW WARNINGS.

EXPLAIN SELECT * FROM Drivers

EXPLAIN SELECT * FROM DriversWHERE id = 1

EXPLAIN SELECT * FROM DriversJOIN Orders ON Drivers.id = Orders.driver_id

Столбец rows

Показывает количество строк, которое, по мнению MySQL, будет прочитано. Это число является приблизительным и может оказаться очень неточным. Оно вычисляется при каждой итерации плана выполнения с вложенными циклами. Часто это значение путают с количеством строк в результирующем наборе, что неверно, потому что столбец rows показывает количество строк, которые нужно будет просмотреть. При вычислении значения не учитываются буферы соединения и кеши (в том числе кеши ОС и оборудования), поэтому реальное число может быть намного меньше предполагаемого.

Столбец filtered

Показывает, какую долю от общего количества числа просмотренных строк вернет движок MySQL. Максимальное значение 100, то есть будет возвращено все 100 % просмотренных строк. Если умножить эту долю на значение в столбце rows, то получится приблизительная оценка количества строк, которые MySQL будет соединять с последующими таблицами. Например, если в строке rows 100 записей, а значение filtered 50,00 (50 %), то это число будет вычислено как 100 x 50 % = 50.

Столбец Extra

В этом столбце собрана различная дополнительная информация. Ниже я разберу все возможные значения. Наиболее важные из них будут помечены (!!!). Такие значения не всегда означают, что это плохо или хорошо, но точно стоит обратить на них внимание.

• const row not found. Для запроса, вида SELECT FROM table, таблица table оказалась пустая.

• Deleting all rows. Некоторые движки MySQL, такие как MyISAM, поддерживают методы быстрого удаления всех строк из таблицы. Если механизм удаления поддерживает эту оптимизацию, то значение Deleting all rows будет значением в столбце Extra.

• Distinct. Если в запросе присутствует DISTINCT, то MySQL прекращает поиск, после нахождения первой подходящей строки.

• FirstMatch (table_name). Если в системной переменной optimizer_switch есть значение firstmatch=on, то MySQL может использовать для подзапросов стратегию FirstMatch, которая позволяет избежать поиска дублей, как только будет найдено первое совпадение. Представим, что один и тот же водитель возил клиента с id = 10 больше, чем один раз, тогда для этого запроса:
  
  EXPLAIN
  SELECT id FROM Drivers
  WHERE Drivers.id IN (SELECT driver_id FROM Orders WHERE client_id = 10)
  
  MySQL может применить стратегию FirstMatch, поскольку нет смысла дальше искать записи для этого водителя.

• Full scan on NULL key. Обычно такая запись идет после Using where как запасная стратегия, если оптимизатор не смог использовать метод доступа по индексу.

• Impossible HAVING. Условие HAVING всегда ложно.

• Impossible WHERE. Условие WHERE всегда ложно.

• Impossible WHERE noticed after reading const tables. MySQL просмотрел все const (и system) таблицы и заметил, что условие WHERE всегда ложно.

• LooseScan(m..n). Стратегия сканирования индекса при группировке GROUP BY. Подробнее читайте здесь.

• No matching min/max row. Ни одна строка не удовлетворяет условию запроса, в котором используются агрегатные функции MIN/MAX.

• No matching rows after partition pruning. По смыслу похож на Impossible WHERE для выражения SELECT, но для запросов DELETE или UPDATE.

• No tables used. В запросе нет FROM или есть FROM DUAL.

• Not exists. Сервер MySQL применил алгоритм раннего завершения. То есть применена оптимизация, чтобы избежать чтения более, чем одной строки из индекса. Это эквивалентно подзапросу NOT EXISTS(), прекращение обработки текущей строки, как только найдено соответствие.

• Plan isnt ready yet. Такое значение может появиться при использовании команды EXPLAIN FOR CONNECTION, если оптимизатор еще не завершил построение плана.

• Range check for each record (!!!). Оптимизатор не нашел подходящего индекса, но обнаружил, что некоторые индексы могут быть использованы после того, как будут известны значения столбцов из предыдущих таблиц. В этом случае оптимизатор будет пытаться применить стратегию поиска по индексу range или index_merge.

• Recursive.Такое значение появляется для рекурсивных (WITH) частей запроса в столбце extra.

• Scanned N databases. Сколько таблиц INFORMATION_SCHEMA было прочитано. Значение N может быть 0, 1 или all.

• Select tables optimized away (!!!). Встречается в запросах, содержащих агрегатные функции (но без GROUP BY). Оптимизатор смог молниеносно получить нужные данные, не обращаясь к таблице, например, из внутренних счетчиков или индекса. Это лучшее значение поля extra, которое вы можете встретить при использовании агрегатных функций.

• Skip_open_table, Open_frm_only, Open_full_table. Для каждой таблицы, которую вы создаете, MySQL создает на диске файл .frm, описывающий структуру таблицы. Для подсистемы хранения MyISAM так же создаются файлы .MYD с данными и .MYI с индексами. В запросах к INFORMATION_SCHEMA Skip_open_table означает, что ни один из этих файлов открывать не нужно, вся информация уже доступна в словаре (data dictionary). Для Open_frm_only потребуется открыть файлы .frm. Open_full_table указывает на необходимость открытия файлов .frm, .MYD и .MYI.

• Start temporary, End temporary. Еще одна стратегия предотвращения поиска дубликатов, которая называется DuplicateWeedout. При этом создаётся временная таблица, что будет отображено как Start temporary. Когда значения из таблицы будут прочитаны, это будет отмечено в колонке extra как End temporary. Неплохое описание читайте здесь.

• unique row not found (!!!). Для запросов SELECT FROM table ни одна строка не удовлетворяет поиску по PRIMARY или UNIQUE KEY.

• Using filesort (!!!). Сервер MySQL вынужден прибегнуть к внешней сортировке, вместо той, что задаётся индексом. Сортировка может быть произведена как в памяти, так и на диске, о чем EXPLAIN никак не сообщает.

• Using index (!!!). MySQL использует покрывающий индекс, чтобы избежать доступа к таблице.

• Using index condition (!!!). Информация считывается из индекса, чтобы затем можно было определить, следует ли читать строку целиком. Иногда стоит поменять местами условия в WHERE или прокинуть дополнительные данные в запрос с вашего бэкенда, чтобы Using index condition превратилось в Using index.

• Using index for group-by (!!!). Похож на Using index, но для группировки GROUP BY или DISTINCT. Обращения к таблице не требуется, все данные есть в индексе.

• Using join buffer (Block nested loop | Batched Key Access | hash join). Таблицы, получившиеся в результате объединения (JOIN), записываются в буфер соединения (Join Buffer). Затем новые таблицы соединяются уже со строками из этого буфера. Алгоритм соединения (Block nested loop | Batched Key Access | hash join) будет указан в колонке extra.

• Using sort_union, Using union, Using intersect. Показывает алгоритм слияния, о котором я писал выше для index_merge столбца type.

• Using temporary (!!!). Будет создана временная таблица для сортировки или группировки результатов запроса.

• Using where (!!!). Сервер будет вынужден дополнительно фильтровать те строки, которые уже были отфильтрованы подсистемой хранения. Если вы встретили Using where в столбце extra, то стоит переписать запрос, используя другие возможные индексы.

• Zero limit. В запросе присутствует LIMIT 0.

Команда SHOW WARNINGS

Выражение EXPLAIN предоставляет расширенную информацию, если сразу после его завершения выполнить команду SHOW WARNINGS. Тогда вы получите реконструированный запрос.

EXPLAIN SELECT              Drivers.id,              Drivers.id IN (SELECT Orders.driver_id FROM Orders)FROM Drivers;SHOW WARNINGS;

/* select#1 */ select `explain`.`Drivers`.`id` AS `id`,<in_optimizer>(`explain`.`Drivers`.`id`,<exists>(<index_lookup>(<cache>(`explain`.`Drivers`.`id`) in Orders on Orders_Drivers_id_fk))) AS `Drivers.id IN (SELECT Orders.driver_id FROM Orders)` from `explain`.`Drivers`

Отображаемое сообщение получено напрямую из плана запроса, а не из исходной SQL- команды. Сервер генерирует информацию, исходя из плана выполнения. Поэтому отображённый запрос будет иметь ту же семантику, что и исходный, но не тот же текст (в большинстве случаев).

SHOW WARNINGS содержит специальные маркеры, которые не являются допустимым SQL -выражением. Вот их список:

• <auto_key>. Автоматически сгенерированный ключ для временной таблицы.

• <cache> (expr). Выражение expr выполняется один раз, значение сохраняется в памяти. Если таких значений несколько, то вместо <cache> будет создана временная таблица с маркером <temporary table>.

• <exists> (query fragment). Предикат подзапроса был преобразован в EXISTS -предикат, а сам подзапрос был преобразован таким образом, чтобы его можно было использовать совместно с EXISTS.

• <in_optimizer> (query fragment). Внутренний объект оптимизатора, не обращаем внимания.

• <index_lookup> (query fragment). Этот фрагмент запроса обрабатывается с помощью поиска по индексу.

• <if> (condition, expr1, expr2). Если условие истинно, то выполняем expr1, иначе expr2.

• <is_not_null_test> (expr). Тест для оценки того, что выражение expr не преобразуется в null.

• <materialize> (query fragment). Подзапрос был материализован.

• materialized-subquery.col_name. Ссылка на столбец col_name была материализована.

• <primary_index_lookup> (query fragment). Фрагмент запроса обрабатывается с помощью индекса по первичному ключу.

• <ref_null_helper> (expr). Внутренний объект оптимизатора, не обращаем внимания.

• /* select # N */. SELECT относится к строке с номером id = N из результата EXPLAIN.

• <temporary table>. Представляет собой временную таблицу, которая используется для кеширования результатов.

Читаем EXPLAIN

Учитывая всё вышесказанное, пора дать ответ на вопрос - так как же стоит правильно читать EXPLAIN?

Начинаем читать каждую строчку сверху вниз. Смотрим на колонку type. Если индекс не используется плохо (за исключением случаев, когда таблица очень маленькая или присутствует ключевое слово LIMIT). В этом случае оптимизатор намеренно предпочтет просканировать таблицу. Чем ближе значение столбца type к NULL (см. пункт о столбце type), тем лучше.

Далее стоит посмотреть на колонки rows и filtered. Чем меньше значение rows и чем больше значение filtered,- тем лучше. Однако, если значение rows слишком велико и filtered стремится к 100 % - это очень плохо.

Смотрим, какой индекс был выбран из колонки key , и сравниваем со всеми ключами из possible_keys. Если индекс не оптимальный (большая селективность), то стоит подумать, как изменить запрос или пробросить дополнительные данные в условие выборки, чтобы использовать наилучший индекс из possible_keys.

Наконец, читаем колонку Extra. Если там значение, отмеченное выше как (!!!), то, как минимум, обращаем на это вниманием. Как максимум, пытаемся разобраться, почему так. В этом нам может хорошо помочь SHOW WARNINGS.

Переходим к следующей строке и повторяем всё заново.

Если не лень, то в конце перемножаем все значения в столбце rows всех строк, чтобы грубо оценить количество просматриваемых строк.

При чтении всегда помним о том, что:

• EXPLAIN ничего не расскажет о триггерах и функциях (в том числе определенных пользователем), участвующих в запросе.

• EXPLAIN не работает с хранимыми процедурами.

• EXPLAIN не расскажет об оптимизациях, которые MySQL производит уже на этапе выполнения запроса.

• Большинство статистической информации всего лишь оценка, иногда очень неточная.

• EXPLAIN не делает различий между некоторыми операциями, называя их одинаково. Например, filesort может означать сортировку в памяти и на диске, а временная таблица, которая создается на диске или в памяти, будет помечена как Using temporary.

• В разных версиях MySQL EXPLAIN может выдавать совершенно разные результаты, потому что оптимизатор постоянно улучшается разработчиками, поэтому не забываем обновляться.

EXPLAIN TREE FORMAT и EXPLAIN ANALYZE

Если вы счастливый обладатель восьмой версии MySQL, то в вашем арсенале появляются очень полезные команды, которые позволяют читать план выполнения и информацию о стоимости запроса без использования SHOW WARNINGS.

С версии 8.0.16 можно вывести план выполнения в виде дерева, используя выражение FORMAT=TREE:

EXPLAIN FORMAT = TREE select * from Drivers   join Orders on Drivers.id = Orders.driver_id   join Clients on Orders.client_id = Clients.id

-> Nested loop inner join  (cost=1.05 rows=1)   -> Nested loop inner join  (cost=0.70 rows=1)       -> Index scan on Drivers using Drivers_car_id_index  (cost=0.35 rows=1)       -> Index lookup on Orders using Orders_Drivers_id_fk (driver_id=Drivers.id)  (cost=0.35 rows=1)   -> Single-row index lookup on Clients using PRIMARY (id=Orders.client_id)  (cost=0.35 rows=1)

Удобство такого формата в том, что мы можем видеть план запроса в виде вложенного дерева. Каждая вложенная строка означает новый цикл. В скобках указана информация об оценочной стоимости и расчетное количество прочитанных строк. Стоимость или стоимость запроса это некая внутренняя оценка того, насколько дорого для MySQL выполнять этот запрос, основанная на различных внутренних метриках.

Еще более подробную информацию можно получить, заменив FORMAT = TREE на выражение ANALYZE, которое предоставляет MySQL с версии 8.0.18.

EXPLAIN ANALYZE select * from Drivers   join Orders on Drivers.id = Orders.driver_id   join Clients on Orders.client_id = Clients.id

-> Nested loop inner join  (cost=1.05 rows=1) (actual time=0.152..0.152 rows=0 loops=1)   -> Nested loop inner join  (cost=0.70 rows=1) (actual time=0.123..0.123 rows=0 loops=1)       -> Index scan on Drivers using Drivers_car_id_index  (cost=0.35 rows=1) (actual time=0.094..0.094 rows=0 loops=1)       -> Index lookup on Orders using Orders_Drivers_id_fk (driver_id=Drivers.id)  (cost=0.35 rows=1) (never executed)   -> Single-row index lookup on Clients using PRIMARY (id=Orders.client_id)  (cost=0.35 rows=1) (never executed)

В дополнение к стоимости и количеству строк можно увидеть фактическое время получения первой строки и фактическое время получения всех строк, которые выводятся в формате actual time={время получения первой строки}..{время получения всех строк}. Также теперь появилось еще одно значение rows, которое указывает на фактическое количество прочитанных строк. Значение loops это количество циклов, которые будут выполнены для соединения с внешней таблицей (выше по дереву). Если не потребовалось ни одной итерации цикла, то вместо расширенной информации вы увидите значение (never executed).

Как видите, обновлять MySQL полезно не только с точки зрения производительности и улучшения оптимизатора, но и для получения новых инструментов для профилирования запросов.

Заключение

Команда EXPLAIN станет отличным оружием в вашем арсенале при работе с БД. Зная несложные правила, вы можете быстро оптимизировать ваши запросы, узнавать различную статистическую информацию, пусть и приближенную. Расширенный вывод подскажет, что было закешировано и где создаются временные таблицы.

Слишком большие запросы могут генерировать пугающие результаты EXPLAIN, но тут, как и в любом деле, важна практика. Переходите от простых запросов к сложным.

Пытайтесь, даже просто так, читать различные виды запросов, содержащие FROM, UNION и JOIN , и сами не заметите, как станете мастером оптимизации.

Литература и источники

1. High Performance MySQL (by Baron Schwartz, Peter Zaitsev, Vadim Tkachenko)

2. https://dev.mysql.com/

3. https://stackoverflow.com/

4. http://highload.guide/

5. https://taogenjia.com/2020/06/08/mysql-explain/

6. https://www.eversql.com/mysql-explain-example-explaining-mysql-explain-using-stackoverflow-data/

7. https://dba.stackexchange.com/

8. https://mariadb.com/

9. https://andreyex.ru/bazy-dannyx/baza-dannyx-mysql/explain-analyze-v-mysql/

10. https://programming.vip/docs/explain-analyze-in-mysql-8.0.html

11. А также много страниц из google.com

Высоконагруженные приложения финансовой сферы предъявляют жесткие требования ко всем компонентам, особенно к системе хранения данных.

Нужно постоянно и стабильно держать нагрузку, несмотря, а иногда и вопреки отказам, поломкам и внезапным миграциям. О том, как приходится жить DBA в мире стремительного повышения нагрузок и высоких требованиях стабильности, в своем докладе на конференции Saint HighLoad++ Online 2020 рассказал эксперт по базам данных ECOMMPAY IT Владимир Федорков.

Работа DBA очень похожа на работу уборщицы: пока везде вымыто, все довольны. Но как только появляется проблема с чистотой, возникают вопросы, и все сразу вспоминают о том, что есть DBA недобрым, иногда матерным, словом. Почему так происходит? Дело в том, что системы хранения и поиска данных на сегодняшний день являются фундаментом приложений. Нет быстрого доступа к данным нечего обрабатывать приложению нечего показать пользователю. И весь код с архитектурой становятся бесполезными.

Поэтому работа современного DBA обеспечить стабильный фундамент активно строящемуся зданию приложения. Часто здание не просто строится, но активно эксплуатируется, местами ремонтируется, кое-где перестраивается, где-то расширяется, а иногда разрезается и вывозится вместе с жильцами, без их выселения, в другое место. И все это нужно сделать так, чтобы не создать населяющим его людям неудобств. А в идеале провести все работы так, чтобы жильцы их просто не заметили. Конечно, с обычным здание выполнить эти действия не получится. А вот в IT, особенно в случае разработки по принципу гибких методологий, такое вполне возможно. Конечно, если уделять должное внимание деталям.

Как же построить надежный фундамент для приложения? Компонентов успешной работы два: реактивный и проактивный. По их соотношению в ежедневной работе можно судить и о стабильности приложения, в целом, и об эффективности работы DBA.

Реактивность это реакция на происходящие в данный момент проде события, которые каким-либо образом влияют на работу БД, а значит и приложения ( в основном, речь идет об алертах от системы мониторинга). Если вы получаете информацию об отказах от своего руководства или, тем более, от клиентов система для вас полностью непрозрачна. А это значит, что вы не в состоянии ей управлять без риска что-то сломать на уровне базовых операций.

Задача реактивной работы: отработать событие как можно быстрее в соответствии с его приоритетом. Согласно методологии работы департамента эксплуатации ECOMMPAY IT, в случае аффекта клиентов сотрудники обязаны за минимально возможное время снять этот аффект. А дальше, в рабочем порядке, ликвидировать последствия инцидента. В этом случае бизнес несет минимальные потери как в финансовом, так и в репутационном выражении.

У реактивной работы есть множество методологий, которые несмотря на свою увлекательность, не входят в описательные цели этой статьи. Но однозначно нужно помнить одно утверждение: если критические отказы не начинают обрабатываться в течении трех минут с момента появления, деньги бизнеса не отрабатываются должным образом.

При всей своей важности, видимости и понятности для начальства и бизнеса, реактивная работа сама по себе не является залогом стабильности работы системы. За это ответственна менее заметная, но гораздо более важная часть работы DBA: проактивная или созидательная работа.

Она заключается в анализе текущей ситуации, поиске узких мест и нахождении потенциальных проблем, которые могут выстрелить завтра, на следующей неделе, через месяц или даже через год. Чем большую проблему удается обнаружить заранее, тем больше денег сэкономит бизнес на прямых потерях, и тем больше времени персонал будет занят производительным трудом, а не разгребанием последствий инцидентов.

В чем заключается проактивная работа в случае баз данных? Как и в любых других случаях, она делится на поиски ответов к двум вопросам: Где мы сейчас находимся? и Куда нам нужно двигаться?. Оба вопроса одинаково важны, но без ответа на первый мы не сможем ответить на второй, поэтому с него и начнем.

Метрики

Для того, чтобы производить анализ работы БД нужны метрики: они делают работу системы прозрачной, визуализируют состояние системы и дают возможность понимания того, что происходит здесь и сейчас.

Метрики обеспечивают крайне важную составляющую работы системного администратора ситуационную осведомленность. Ситуационная осведомленность это набор параметров, знание которых позволяет оценить работу основных компонентов системы и определить состояние системы? в целом. Без визуализации этих параметров? система не может считаться прозрачной и будет невозможна не только проактивная, но и реактивная работа.

Для систем на основе MySQL абсолютно критичными параметрами являются:

• Метрики базы: Threads_running и Threads_connected;

• Загрузка CPU (Load Average), context switches;

• Загрузка дисковой подсистемы (read/s, writes/s, avg write time, avg read time);

• Количество запросов в статусе блокировки.

Threads_running показывает, сколько запросов в настоящее время находятся в активной фазе. Это может быть фаза исполнения запроса или ожидание блокировки: речь идет о результате исполнения, который прямо сейчас ожидает приложение. Эта метрика важна еще и потому, что внутри MySQL каждый запрос (точнее, подключение) обрабатывается одним потоком, а значит, одним ядром CPU. Поэтому приближение количества активных потоков к количеству ядер на машине можно считать тревожным сигналом. В ситуации, когда количество активных запросов в разы превышает количество ядер, операционная система вынуждена делить время процессора между запросами, что приводит к менее эффективному их выполнению, увеличению Load Average, росту context switches, метрики Threads_connected и последовательной деградации производительности системы. Такое состояние системы характеризуется экспоненциальным ростом времени отклика, падением пропускной способности севера и, с точки зрения приложения и пользователей, это равнозначно отказу.

Загрузка дисковой подсистемы показывает, как быстро записываются изменения и насколько эффективно читаются незакешированные в памяти MySQL данные. Приближение метрик read/s, writes/s (они же Read IOPS и Write IOPS) к предельным значением для текущего сервера (они определяются бенчмарками) означает рост очереди активных запросов, и далее нас ждет рост Threads_running.

Отдельно можно выделить количество запросов в статусе блокировки. Блокировки бывают разными, и несмотря на то, что каждый конкретный запрос в состоянии блокировки практически не отъедает ресурс у системы, заблокированный запрос может влиять на время ответа клиенту, тормозить синхронные операции, удерживать ценные ресурсы, как на стороне базы данных, так и на стороне приложения. И, в конечном итоге, это может привести к тем же последствиям, что и большое количество активных запросов. Радости от блокировок тоже никакой. Поэтому если SELECT FOR UPDATE кажется Вам отличной идеей, у меня для вас плохие новости.

Запросы

Одними метриками сыт не будешь. Недостаточно знать, что нагрузка на базу приемлема и перегруза нет. Если в базу данных кто-то пишет, то объемы данных будут расти. Это значит, что в какой-то момент плавный и часто незаметный глазу рост времени выполнения запросов, повинуясь правилу материалистической диалектики, перейдет из количественного в качественный, и резким скачком времени ответа сообщит о том, что мирные времена закончились.

Поэтому DBA каждый день готовится к войне. А для этого нужно смотреть сами запросы, даже если прямо сейчас все хорошо.

Какие запросы смотрим:

• Те, которые нагружают CPU;

• Те, которые нагружают диск;

• Все запросы, длиннее ста миллисекунд.

Как узнать, что нагружает CPU? Процессор нагружают все запросы, но смотреть в первую очередь нужно те, у которых большое суммарное время выполнения. Причем отдельный запрос может быть очень быстрым, но большое количество очень быстрых запросов может отъедать значительное количество ресурсов системы. И это может внезапно и очень неприятно аукнуться в момент легкой перегрузки. Идентифицировать такие запросы можно по большим значениям Exec time в выводе pt-query-digest, в PMM, через Performance_schema или по значению sum_time в ProxySQL.

Какие запросы нагружают диск? Этот вопрос чуть более сложный. Запись нагружает диск всегда, а вот запросы на чтение могут выполнятся только в памяти, если ее (в случае InnoDB за кэширование данных отвечает настройка innodb_buffer_pool_size) достаточно для хранения всех горячих данных. Интересно, что чтения с диска требуют не только SELECTы, но и команды записи данных. INSERTу, чтобы записать данные, нужна страничка с тем местом таблицы, в которую эти данные пишутся. UPDATE и DELETE также будут запрашивать с диска данные, которые нужно поменять (удалить), а ALTERу вообще может потребоваться вся таблица.

Запись почти всегда медленнее чтения. Исключение составляет запись в RAM-кэш дискового контроллера и в кэш-память сетевого диска. За этим и некоторыми другими редкими исключениями мы можем смело считать, что нагрузку на запись нужно смотреть в первую очередь. Особенно это касается систем виртуализации, где настройки драйвера могут сильно сказаться на производительности базы.

В целом, присутствие на боевой базе запросов длиннее ста миллисекунд это хороший повод задуматься либо о добавлении необходимого индекса, либо об оптимизации схемы, либо об изменении логики приложения. В запущенных случаях может потребоваться переселение запроса на отдельную проклятую реплику, которая обычно называется аналитической.

Исторические данные

Моментальный снапшот метрик и запросов позволяет увидеть текущее состояние системы, однако современные системы не просто работают 24/7. В каждый момент нагрузка на них может плавать, и моментальный снапшот эти изменения не покажет. Поэтому основную информацию о производительности нужно собирать на постоянной основе. Эту информацию крайне удобно визуализировать в виде графиков.

Такую возможность предоставляют сейчас многие системы мониторинга: OkMeter, Prometheus + Grafana, графики в Zabbix и многое другое. Напишите, пожалуйста, в комментариях, какую систему визуализации метрик вы можете порекомендовать. Моей персональной любовью является PMM крайне удобная штука, которая показывает все необходимые метрики от уровня операционной системы до внутренних метрик MySQL и ProxySQL.

Какую гранулярность выбрать для графиков? Короткие интервалы опроса (до 5 секунд) позволяют увидеть единичные моментальные всплески, но серьезно увеличивают количество хранимых исторических данных и создают видимую нагрузку на исследуемую систему. Длинные периоды опроса (больше минуты) комфортны для хранения, но появляется риск пропустить существенные всплески нагрузки.

Мы остановились на гранулярности в 20-30 секунд. Такой интервал сбора данных позволяет увидеть все более-менее серьезные всплески, при этом не нагружая клиентские системы и базы мониторингом.

Графики нагрузки позволяют решить несколько ключевых проблем:

• Посмотреть сезонность;

• Увидеть всплески от единичных запусков скриптов и запросов;

• Увидеть работу кронов;

• Оценить влияние деплоя;

• Оценить общий рост нагрузки.

Сезонность позволяет как минимум планировать работы. Для высоконагруженных систем попытка выполнения отдельных миграций БД в момент суточного пика равнозначна отказу систем. Бизнесу это точно не понравится. Такую же службу могут сослужить и некоторые крон задачи, запущенные в момент экстремальной нагрузки.

Особенно хороши исторические данные для быстрой визуальной оценки качества нового кода. Они помогут принять решение об откате неудачного деплоя. Это касается не только метрик базы, но и бизнес метрик, без которых мониторинг сейчас не может считаться полноценным (но этот вопрос выходит за рамки статьи).

Один из важнейших параметров это ретроспективный рост нагрузки. Насколько выросла (или упала) нагрузка за последний месяц, квартал, полугодие? Этот вопрос является ключевым для долговременного планирования, особенно в сфере баз данных, где масштабирование является многогранным процессом и часто требует серьезной поддержки со стороны приложения.

Важность коммуникаций и открытости

Если посмотреть на список вариантов, можно увидеть, что немногие из них могут быть реализованы силами только DBA. Большая и самая эффективная часть решений может быть реализована только на уровне кода и логики приложения. Здесь мы подходим к самой важной идее всего нашего повествования: парадигме один в поле не воин.

В современном мире, особенно с увеличением количества людей в компании, наблюдается тенденция к изоляции разработки от эксплуатации и наоборот. Такая ситуация может привести к потере осведомленности разработки о нюансах функционирования приложения в проде. В то же время эксплуатация может недополучать знания о функциональных взаимодействиях внутри приложения.

Проблемы в коммуникации могут сказываться как на скорости решения отказов функционального и нагрузочного характера, так и в решениях по архитектуре, что приводит к долговременным негативным последствиям как для эксплуатации, так и для разработки.

Частично с такими проблемами борется продуктовая трансформация, создавая центры компетенций и делая кросс-командные связи более выраженными. В этом смысле администраторы баз данных и SRE являются точкой, где концентрируется информация о производительности приложений. Это делает необходимым их совместную работу с командами разработки по выявлению проблем производительности на как можно более ранней стадии, в идеале еще на этапе концепта. Такой подход помимо удешевления стоимости эксплуатации ПО, за счет оптимизации, на ранней стадии позволяет избежать архитектурных проблем и существенно ускорить разработку.

Говоря простым языком прозрачность коммуникаций между разработкой и эксплуатацией позволяет на ранней стадии решить значительное количество проблем и относительно спокойно жить с ростом нагрузки и количества данных.

Заключение

Производительность, устойчивость и стабильность не единичные задачи. Это целый комплекс ежедневных рутин и аналитики, направленный на выявление как текущих, так и потенциальных узких мест, которые могут появиться через месяц, два, шесть и даже через год.

Основа этих мер прозрачность приложений для всех команд, коммуникация и налаженное взаимодействие между всеми заинтересованными лицами. Разработка знает, как система должна работать, эксплуатация знает как она работает по факту. Обмен знаниями и взаимная осведомленность ключи к успеху в работе любой IT компании.

Конференция HighLoad++ 2020 пройдет 20 и 21 мая 2021 года.Приобрести билетыможно уже сейчас.

Хотите бесплатно получить материалы конференции мини-конференции Saint HighLoad++ 2020?Подписывайтесьна нашу рассылку.

Прим. перев.: в этой статье сербский инженер по масштабируемости нагруженного онлайн-проекта в подробностях рассказывает о своем опыте оптимизации большой БД на базе MySQL. Проведена она была для того, чтобы выдержать резкий рост трафика на сайт, случившийся из-за пандемии.

Примечание: первоначально эта статья была опубликована в блоге моего хорошего друга unstructed.tech.

База данных становится слишком большой или старой? Ее тяжело обслуживать? Что ж, надеюсь, я смогу немного помочь. Текст, который вы собираетесь прочитать, содержит реальный опыт масштабирования монолитной базы данных, лежащей в основе одного из сайтов Топ-250 (согласно alexa.com). На момент написания этой статьи chess.com занимал 215 место в мире по популярности. Ежедневно к нам заглядывали более 4 млн уникальных пользователей, а наши MySQL-базы обрабатывали в общей сложности более 7 млрд запросов. Год назад сайт ежедневно посещали 1 млн уникальных пользователей; в марте прошлого года их число увеличилось до 1,3 млн; сегодня более 4 млн человек заходят на chess.com ежедневно, а число сыгранных партий превышает 8 млн. Я, конечно, знаю, что это не сопоставимо с самыми крупными игроками на рынке, однако наш опыт все же может помочь в такой сложной задаче, как исправление монолитной базы данных и ее вывод на новый уровень производительности.

Примечание: Это моя первая статья, и она довольно длинная (и это при том, что мне пришлось вырезать примерно половину текста, чтобы сделать ее читаемой). Так что некоторые вещи могут оказаться не слишком понятными и недостаточно объясненными, и я прошу за это прощения. Свяжитесь со мной в LinkedIn, и мы сможем обсудить все вопросы более подробно.

Обновление: прочитав массу комментариев [к оригинальной публикации прим. перев.], я хотел бы добавить/уточнить несколько моментов. Мы широко используем кэширование иначе не продержались бы и дня. И да, мы используем Redis (зачастую выжимая из него максимум). Мы пробовали MongoDB и Vitess, но они нам не подошли.

Состояние, в котором мы находились пару лет назад

Где-то в середине 2019-го мы начали замечать, что основной кластер БД потихоньку становится чрезмерно громоздким. У нас также имелись три меньших по размеру и менее загруженных базы данных, но все данные в конечном итоге всегда оказывались в основной БД. Удивительно, но она была в довольно приличном состоянии для базы, которая начала свою работу более 12 лет назад. Не так много неиспользуемых/лишних индексов (существующие были преимущественно хороши). Мы постоянно отслеживали и оптимизировали тяжелые/медленные запросы. Значительная часть данных была денормализована. И речь идет не о каких-то посторонних ключах многие вещи были реализованы в самом коде (фильтрация, сортировка и т.п. с тем, чтобы БД работала только с самыми эффективными индексами), работающем на последней версии MySQL, и т.д., и т.п. Мы о ней не забывали, и в результате со временем она эволюционировала во вполне хороший инструмент.

Предостережение: я никого не призываю заниматься подобными микро-оптимизациями. Они работают для chess.com, для его масштаба. Любое решение перед реализацией тщательно тестируется, результаты внимательно оцениваются. Так что мы знаем, что оно работает для нас.

Самая большая проблема, с которой мы столкнулись в тот момент, состояла в том, что для изменения почти любой таблицы требовалось вывести половину хостов из ротации, провести ALTER, вернуть их в ротацию. А затем все это повторить для другой половины. Нам приходилось проводить эти операции в часы затишья, поскольку исключение половины хостов из работы в пиковое время, скорее всего, обрушило бы вторую половину. С ростом сайта старые функции получали новые воплощения, и нам часто приходилось проводить ALTER'ы (те, кто в теме, поймут, о чем я). Процесс был бы гораздо менее напряженным, если бы мы могли исключить из ротации только небольшой набор таблиц, а не всю БД. Поэтому мы разработали 5-летний план для основного кластера (ох, какими наивными мы были...), в котором прописали шаги по разбиению базы на множество более мелких. Это должно было упростить и облегчить обслуживание (ну, хоть с этим угадали...). План исходил из годовых темпов роста в ~25% (именно такие темпы наблюдались на тот момент).

РЕАЛЬНАЯ проблема вносит коррективы в наш план

Все вы наверняка знаете о COVID-19 и суматохе, с ним связанной. Легко догадаться и о том, что мы вовсе не были ко всему этому готовы (не ожидали того воздействия, которое локдаун окажет на трафик). Интерес к шахматам взлетел до небес, как только (большая) часть Европы отправилась на самоизоляцию. Забавно, но можно было сказать, какая страна ввела локдаун, просто глядя на количество регистрирующихся пользователей по странам это было так очевидно... И все наши показатели взлетели до небес. Как ни странно, базы данных работали нормально (не супер, конечно, но вполне справлялись с трафиком). Но в то же время мы заметили, что хост reports не поспевал за хостами production (то есть частенько он отставал на 30-60 секунд в репликации), что заставило обратить внимание на поток репликации и его доступную пропускную способность. И она была практически полностью исчерпана (на пике потребления оставалось не более 5%). В тот момент мы уже понимали, что в США (откуда основная часть наших игроков) скоро также введут самоизоляцию. Это означало бы, что наши реплики не смогут обработать все операции записи в мастер (впрочем, это случилось бы, даже если мы просто продолжали медленно, но верно расти). Это был бы конец chess.com, поскольку код не готов к значительным задержкам репликации при чтении данных с реплик, а отправка всех SELECT'ов на мастер привела бы к его падению. Цель стала ясна: снизить число операций записи в главный кластер, и сделать это как можно скорее. На самом деле это входило в наш изначальный план, только тот был растянут на пять лет. А у нас на все была пара месяцев...

Решение

Как снизить количество операций записи в БД? На первый взгляд все просто: определить самые нагруженные в этом смысле таблицы и выкинуть их из базы данных. Так мы просто разбиваем операции записи на два отдельных потока, не меняя их число. Это могут быть таблицы с большим числом INSERT'ов, или таблицы без множества INSERT'ов, но с записями, которые часто обновляются. Их просто надо определить в какое-нибудь другое место. Но как сделать это без простоя? Как вы догадываетесь, не все так просто.

Сначала мы выделили таблицы с наибольшим числом обновлений (INSERT, DELETE или UPDATE). Основная их часть была красиво сгруппирована в зависимости от функции, для которой использовалась (в большинстве таблиц, связанных с игрой, запись велась примерно с одинаковой скоростью и т. п.). В итоге мы получили список из 10-15 таблиц для 3 различных функций сайта. При их изучении проявилась еще одна проблема: поскольку мы не можем делать JOIN'ы между базами на разных хостах, надо перенести все возможные таблицы, отвечающие за определенный функционал, чтобы упростить проект. Впрочем, этот момент был нам известен заранее, поскольку когда план только появился, мы уже имели успешный опыт похожей миграции для трех небольших, слабо задействованных таблиц в рамках проверки концепции (PoC).

Три упомянутых выше нагруженных функции это logs (не совсем функция и не совсем логи просто неудачное название), games (чего и следовало ожидать от шахматной платформы) и puzzles (шахматные задачи-пазлы). В случае логов мы выделили три сильно изолированных таблицы (что означало минимальные изменения в запросах/коде). То же самое и для игр. Но в пазлах было задействовано более 15 таблиц, и запросы к ним включали массу JOIN-операций с таблицами, которые должны были остаться в основной БД. Мы мобилизовали свои войска, привлекли более половины бэкенд-разработчиков, разбили их на команды и совместными усилиями приступили к миграции.

Одна неделя ушла на перемещение логов в выделенную базу данных, работающую на двух хостах, что дало нам время на передышку, поскольку основная часть операций записи касалась именно их. Еще месяц потребовался на перемещение игровых баз (было очень страшно: любая ошибка привела бы к катастрофе, учитывая, что вся суть сайта именно в шахматных партиях). Два месяца ушло на перемещение пазлов. После всех этих действий загруженность канала репликации снизилась до 80% на пике то есть у нас появилось время перегруппироваться и распланировать предстоящие проекты немного тщательнее.

Исполнение

Итак, как же нам это удалось?

Как вы догадываетесь, у подобного предприятия имеются два аспекта: на стороне кода и на стороне базы данных, и оба требуют большой работы. На стороне кода есть несколько предварительных условий. Прежде всего, нужна система feature flag (она же feature toggle) либо внутренняя, либо сторонняя. Наша разработана на заказ и весьма обстоятельна (пожалуй, это отличная тема для новой статьи). Абсолютный минимум, который должен быть у такой системы, возможность открывать или запрещать доступ к тестовой функции в диапазоне от 0 до 100 процентов. Другая полезная вещь хорошее покрытие тестами. Несколько лет назад вся наша кодовая база была переписана с нуля, так что нам повезло (пару раз этот факт сильно выручал).

Что касается изменений кода и базы, кое-что можно делать параллельно, другие операции приходится делать последовательно. Проще всего начать с создания новой БД. Все наши новые базы данных извлекаются из основной: мы называем их partitions (партициями или разделами), а сам процесс partitioning (партиционированием, разбиением на разделы). Для размещения используется 2-хостовая схема (мастер и failover-мастер, т.е. реплика), но в принципе схема может быть любой.

В новом кластере создается база данных со схемой, идентичной схеме той базы, которую мы пытаемся разделить (именуя ее в соответствии с потребностями; в нашем случае первая называлась logs). Затем импортируется резервная копия основной базы данных, после чего мы делаем мастер partition-кластера репликой основного мастера (именно поэтому копируем схему и импортируем резервную копию). В результате новый кластер становится очередной репликой основного кластера, только база данных называется иначе. На некоторое время оставляем все как есть новая база просто реплицирует трафик и обновляется в связке с остальным кластером, пока мы работаем над кодовой частью проекта.

До начала работы над данным проектом у нас, по сути, было открыто два подключения к базе данных из кода: read-only для работы с репликами и read/write для работы с мастером. Оба подключения проходили через HAProxy, чтобы попасть туда, куда требовалось. Первое, что мы сделали, создали параллельный набор подключений. В нем read/write идет к Partition Master, а read-only к Partition Replica.

Chess.com написан на PHP, так что я буду использовать PHP-примеры для иллюстрации необходимых изменений. При этом постараюсь максимально приблизить его к псевдокоду, чтобы все смогли понять, что именно происходит (вы будете удивлены, узнав, как много веб-сайтов из Топ-1000 написаны на PHP и насколько легко масштабировать PHP до этих высот. Пожалуй, хорошая тема для еще одной статьи).

Изменения кода сводятся к 3 вещам:

• Удаление JOIN-запросов к таблицам, которые теперь обитают в разных базах данных.

• Агрегирование, слияние и сортировка данных в коде (поскольку теперь не получится проводить некоторые из этих операций в БД)

• Использование feature flag-системы для определения того, на какие хосты идут запросы.

Чтение данных

Хотя удаление JOIN'ов и кажется простым, на самом деле таким не является. В следующих примерах я предполагаю, что таблицы games и additional_game_data перемещаются в базу данных в новом partition (примеры на псевдокоде, так что не ожидайте, что они будут идеальными). Итак, запрос, который выглядел следующим образом:

SELECT u.user_id, u.username, u.rating, ugd.start_rating, ugd.end_rating, gd.resultFROM user_game_data ugd INNER JOIN game_data gd on ugd.game_id = g.game_idINNER JOIN users u on u.user_id = ugd.user_id WHERE     g.finished = 1 AND     g.tournament_id = 1234 AND     u.banned = 0ORDER BY u.rating DESCLIMIT 5;

теперь будет выглядеть так (поскольку мы больше не можем сделать JOIN с таблицей users):

SELECT ugd.user_id, ugd.start_rating, ugd.end_rating, gd.resultFROM user_game_data ugdINNER JOIN game_data gd on ugd.game_id = g.game_idWHERE    g.finished = 1 AND    g.tournament_id = 1234;

Мы убрали JOIN с таблицей users, выбор столбцов из users, условие для столбца из users в выражении с WHERE, сортировку и LIMIT по очевидным причинам. Теперь все это надо реализовать в коде. Прежде всего, нужна информация о пользователях. Давайте ее получим:

SELECT u.user_id, u.username, u.ratingFROM users uWHERE     u.user_id IN (:userIDs) AND     u.banned = 0ORDER BY u.rating DESCLIMIT 5;

Здесь :userIDs это список идентификаторов пользователей, которые были получены в первом запросе. Теперь остается только объединить два набора данных и поместить их в проверочное условие feature flag. В конечном итоге мы получим нечто вроде приведенного ниже (псевдо-)кода. Оба return возвращают один и тот же результат:

if ($this->features->hasAccess('read_logs_partition')) {    // предположим, что этот результирующий набор сопоставлен по user_id; на самом деле не имеет значения, как именно это сделано    $partitionData = $this->partitionConnection->query('        SELECT ugd.user_id, ugd.start_rating, ugd.end_rating, gd.result        FROM user_game_data ugd        INNER JOIN game_data gd on ugd.game_id = g.game_id        WHERE            g.finished = 1 AND            g.tournament_id = 1234;    ');    $mainDbData = $this->mainConnection->query('        SELECT u.user_id, u.username, u.rating        FROM users u        WHERE             u.user_id IN (:userIDs) AND             u.banned = 0        ORDER BY u.rating DESC        LIMIT 5;    ');    $result = [];    foreach ($mainDbData as $singleRecord) {        $result[] = array_merge($singleRecord, $partitionData[$singleRecord['user_id']]);    }    return $result;}return $this->mainConnection->query('    SELECT u.user_id, u.username, u.rating, ugd.start_rating, ugd.end_rating, gd.result    FROM user_game_data ugd    INNER JOIN game_data gd on ugd.game_id = g.game_id    INNER JOIN users u on u.user_id = ugd.user_id    WHERE        g.finished = 1 AND        g.tournament_id = 1234 AND        u.banned = 0    ORDER BY u.rating DESC    LIMIT 5;');

Пурист может возразить, что это не самый оптимальный код, поскольку он может требовать многократной итерации, объединения и фильтрации данных в приложении и т.п. Все это правильно, но удар по базе данных все же сильнее. В целом, это гораздо более дешевый способ добиться нужного результата. То, что мы перенесли кучу сортировок, слияний, фильтраций из БД в код, очень помогло масштабированию БД.

Запись данных

Этот параграф мог бы оказаться совсем тривиальным, если бы не чертовы автоинкременты. Пожалуйста, еще раз взгляните на изображение с потоками данных после того, как мы все настроили.

Начну с решения проблемы автоинкрементов: все операции записи идут либо в основную БД, либо в новый partition-кластер (то есть feature flag разом принимает значение 0% или 100%). Никаких промежуточных вариантов. Если произойдет сбой и хотя бы один INSERT попадет в новый partition, partition-хосты полностью прекратят репликацию, а ряд запросов придется пропускать вручную или же начинать всю работу на уровне БД заново (удалять и воссоздавать partition-БД).

Например, если автоинкремент в мигрируемой таблице дошел до 1000 и случился сбой, из-за которого INSERT'ы попали в новую partition, автоинкремент там будет принимать значения 1001, 1002 и т.д. Теперь предположим, что сбой устранен, и записи снова поступают в главную БД с инкрементами 1001, 1002... При репликации данных записей на новые хосты MySQL обнаружит, что там уже есть записи с такими инкрементами, и выдаст ошибку unique constraint violation. И нам придется все исправлять.

Все это не проблема, если используются UUID. Подробнее об этом речь пойдет далее в статье.

Пример кода (он еще проще, если используется обычный SQL просто надо использовать разные объекты для подключения к БД в if/else):

$game = new Game($user, $result);if ($this->features->hasAccess('write_logs_partition')) {    $this->logsEntityManager->persist($game);    $this->logsEntityManager->flush();} else {    $this->mainEntityManager->persist($game);    $this->mainEntityManager->flush();}

Само переключение

Это как раз та ситуация, когда хороший инструмент для мониторинга БД (вроде PMM), мониторинг ошибок (типа Sentry) и feature toggling творят чудеса. Начинаем с простого: посылаем минимально возможный процент SELECT'ов на новые partition-хосты (процент зависит от возможностей feature flag-системы). Смотрим на ошибки/исключения. Повторяем процесс, пока процент не вырастет до 100 (обычно на это уходит более недели). С помощью инструментов для мониторинга БД проверяем, что SELECT'ы больше не затрагивают таблицы в основном кластере БД.

Если мигрируемые таблицы базируются на UUID считайте, что вам повезло. Мы просто повторяем процесс, описанных выше, для записи. Это сработает, поскольку к этому моменту все SELECT'ы будут направляться на новые partition-хосты. И если мы будем писать прямиком в них (независимо от процента), то данные будут полными (одна часть попадет туда напрямую, другая будет реплицирована с мастера основной БД). При этом в главной БД не будет данных, напрямую записанных на новые хосты (это проблему можно было бы решить с помощью двунаправленной репликации, но это просто повысило бы сложность всего проекта). Это означает, что мы уже не можем перенаправить туда SELECT'ы, поскольку данные неполны (ниже поговорим подробнее о том, как отыграть все назад). И аналогично тому, что сделано с чтением, продолжайте увеличивать процент запросов, пока он не достигнет 100%, а инструменты мониторинга не подтвердят, что переключение завершено. Теперь просто отключаем репликацию между мастером нового partition-кластера и основным кластером. Вот и все.

Увы, процесс не так прост, если используются первичные ключи с автоинкрементами. Или же прост в зависимости от того, как на него посмотреть. Но он определенно более напряженный. Нужно мгновенно переключить feature flag с нуля до 100%. В результате все либо будет работать, как ожидалось, либо нет: наличие нескольких запасных хостов для тестирования процесса переключения очень помогает; настоятельно рекомендую. Если все выглядит отлично (или хотя бы приемлемо), то репликация останавливается (как и в случае с UUID), а любые незначительные проблемы исправляются позже. Если же все пошло не по сценарию, придется вернуть все назад.

Как вернуть все назад

За последние пару лет мы создали 6 таких partitions, и только однажды наш план провалился (на самом деле, это была вина feature flag-системы вкупе с автоинкрементами). Опять же, трудоемкость отката зависит от того, используете вы автоинкременты или UUID.

В случае UUID отмена довольно проста. Просто перенаправьте операции записи и чтения (с помощью feature flag) обратно в основной кластер. Остановите репликацию на partition-хосты. Теперь, в зависимости от трафика, есть два пути: можно вручную установить, какие данные отсутствуют в главном кластере, и скопировать их туда, либо закопаться в бинлоги (если они включены), засучить рукава и скопировать недостающие данные. Могло быть и хуже (просто прочитайте следующий абзац).

Этот абзац про автоинкременты. Как и выше, перенаправьте операции записи и чтения в основной кластер. В данном случае просто скопировать данные не получится, поскольку основной кластер уже начал заполняться данными с теми же PK (автоинкрементами), которые существуют в новом partition. Так что придется копнуть поглубже. О простом копировании данных, вероятно, не может быть и речи, если PK хранятся в другом месте в качестве внешних ключей (FK). Возможно, вам больше повезет найти все это в бинлогах (если они включены).

К сожалению, хотя мы всегда были готовы отыграть любой потенциальный бардак еще до того, как у нас появился шанс познакомиться с ним впервые, когда он все-таки наступил, мы просто решили его проигнорировать. Нам повезло. Поскольку feature flag-система прокололась только на одном хосте, после переключения всех запросов на главный кластер потерялось несколько десятков тысяч автоматических сообщений, отправленных системой пользователям. И мы посчитали, что нет никакого смысла восстанавливать все эти записи, поскольку они использовали автоинкременты. Сбой продолжался минут пять, и пользователи от него не пострадали. Так что да, нам реально очень сильно повезло!

Результаты

А теперь о вкусняшках. Мы смогли настроить все новые partitions (я полагаю) таким образом, чтобы 95% запросов шли в мастер, а оставшиеся 5% в реплику/резервный (failover) мастер просто для того, чтобы держать MySQL наготове (на случай, если резерву внезапно придется заработать в полную силу). Это означает, что при мониторинге кластера нам достаточно наблюдать за состоянием мастера. Проводить операции ALTER очень просто, так как не нужно переживать о том, сможет ли один хост (пока на другом работает ALTER) обработать весь трафик.

С кодом не так все радужно необходимо удалить все условия, разбросанные по кодовой базе. На стороне БД нужно провести DROP всех мигрированных таблиц из основной базы данных, а в новом partition'е, наоборот, оставить только их. Теперь это безопасно, ведь репликация между ними не работает.

Закончить хочу комментарием от нашего легендарного сисадмина после того, как минисериал Ход королевы (The Queens Gambit), снятый по заказу Netflix, вызвал еще одну огромную волну трафика (подробнее об этом здесь):

Бонус 1: Тонкости на что обратить внимание

• Необходимо продолжать отправлять запросы SELECT на мастер главного кластера (если они изначально отправлялись на него) до тех пор, пока операции записи не будут переключены на мастер partition-кластера. Это связано с задержкой репликации, которая могла возникнуть между двумя мастерами.

• Всегда проверяйте, что feature flag'и переключились на каждом из хостов. Как я упоминал выше, однажды случилось так, что на одном хосте они не сработали во время переключения записи на одном из partition'ов. Это причинило нам немало страданий.

• Нам пришлось дублировать сущности Doctrine, чтобы убрать зависимость от сущностей в других базах данных.

• Дублирование было необходимо, поскольку мы хотели оставить старый (legacy) код за feature flag'ом и в то же время использовать новые сущности без JOIN'ов.

• Из-за специфики работы Doctrine нам пришлось поместить новые сущности в отдельное пространство имен.

• Кэширование может стать проблемой, если кэширующий код завязан на соединение/базу данных и т.д.

Бонус 2: Немного статистики

Наверное, вы, как и я, обожаете графики. Что ж, наслаждайтесь (все они получены на мастере в основном кластере):

P.S. от переводчика

Читайте также в нашем блоге:

• Обновление MySQL (Percona Server) с 5.7 до 8.0;

• Больше разработчиков должны знать это о базах данных.

Алексей Миловидов делает ClickHouse и, конечно, знает его вдоль и поперек. В том числе и о том, как его можно использовать дополнительно к его штатным и всем хорошо известным функциям.

И сегодня расскажет про эти необычные способы его использования и, может быть, даже не для хранения и обработки данных.

ClickHouse для тестов железа

Самое простое, что можно сделать с ClickHouse, если есть свободные серверы это использовать его для тестов оборудования. Потому что его тестовый dataset содержит те же данные с production Яндекса, только анонимизированные и они доступны снаружи для тестирования. Про то, как подготовить хорошие анонимизированные данные, я рассказывал на Saint HighLoad++ 2019 в Санкт-Петербурге.

Ставим ClickHouse на любой Linux (x86_64, AArch64) или Mac OS. Как это сделать? мы собираем его на каждый коммит и pull request. ClickHouse Build Check покажет нам все детали всех возможных билдов:

Отсюда можно скачать любой бинарник с gcc и clang в релизе, в debug, со всякими санитайзерами или без, для x86, ARM или даже Mac OS. ClickHouse использует все ресурсы железа: все ядра CPU, шины памяти и грузит все диски. Какой сервер ему не дай проверит полностью, хорошо или плохо тот работает.

По этой инструкции можно скачать бинарник, тестовые данные и запустить запросы. Тест займёт около 30 минут и не требует установки ClickHouse. И даже если на сервере уже установлен другой ClickHouse, вы все равно сможете запустить тест.

В результате мы получаем публичный список протестированного железа:

Все результаты мы получаем от пользователей через краудсорсинг и выглядят они примерно так:

Вы можете выбрать серверы для сравнения для каждого можно посмотреть разницу в производительности. Конечно, и других тестов железа существует немало, например, SPECint и вообще куча тестов из организации SPEC. Но ClickHouse позволяет не просто тестировать железо, а тестировать рабочую СУБД на настоящих данных на реальных серверах откуда угодно.

ClickHouse без сервера

Конечно, обычно ClickHouse это сервер + клиент. Но иногда нужно просто обработать какие-то текстовые данные. Для примера я взял все исходники ClickHouse, собрал их в файл и сконкатенировал в файл под названием code.txt:

И, например, я хочу проверить, какие строчки в коде на C++ самые популярные. С помощью типичной shell-команды удалим из каждой строчки кода начальные пробелы и пустые строки, отсортируем и посчитаем количество уникальных. После сортировки видим, что, конечно, самая популярная строчка это открывающая фигурная скобка, за ней закрывающая фигурная скобка, а еще очень популярно return false.

Этот результат я получил за 1,665 секунд. Потому что все это было сделано с учетом сложной локали. Если локаль заменить на простую, выставив переменную окружения LC_ALL=C, то будет всего лишь 0,376 с, то есть в 5 раз быстрее. Но это всего-лишь шел скрипт.

Можно ли быстрее? Да, если использовать clickhouse-local, будет еще лучше.

Это как-будто одновременно и сервер и клиент, но на самом деле ни то, и ни другое clickhouse-local может выполнять SQL запросы по локальным файлам. Вам достаточно указать запрос, структуру и формат данных (можно выбрать любой из форматов, по умолчанию TabSeparated), чтобы обработать запрос на входном файле. За 0.103 секунд то есть в 3,716 раз быстрее (в зависимости от того, как вы запускали предыдущую команду).

Для демонстрации чего-то более серьезного давайте посмотрим на те данные, которые собирает GitHub Archive это логи всех действий всех пользователей, которые происходили на GitHub, то есть коммиты, создание и закрытие issue, комментарии, код-ревью. Все это сохраняется и доступно для скачивания на сайте https://www.gharchive.org/ (всего около 890 Гб):

Чтобы их как-нибудь обработать, выполним запрос с помощью ClickHouse local:

Я выбрал все данные из табличной функции file, которая берет файлы вида *.json.gz то есть все файлы в формате TSV, интерпретируя их как одно поля типа string. С помощью функции для обработки JSON я вытащил из каждой JSONины сначала поле 'actor', а потом поле 'login' в случае, когда оно равно Алексей Миловидов и выбрал таких первых 10 действий на GitHub.

Может возникнуть впечатление, что 890 Гб данных смогли обработаться за 1,3 секунды. Но на самом деле запрос работает потоково. После того, как находятся первые 10 строк, процесс останавливается. Теперь попробуем выполнить более сложный запрос, например, я хочу посчитать, сколько всего действий я совершил на GitHub. Используем SELECT COUNT... и через полторы секунды кажется, что ничего не происходит. Но что происходит на самом деле, мы можем посмотреть в соседнем терминале с помощью программы dstat:

И мы видим, что данные читаются с дисков со скоростью примерно 530 Мб/с и все файлы обрабатываются параллельно почти с максимальной скоростью насколько позволяет железо (на сервере RAID из нескольких HDD).

Но можно использовать ClickHouse local даже без скачивания этих 980 Гб. В ClickHouse есть табличная функция url то есть можно вместо file написать адрес https://.../*.json.gz, и это тоже будет обрабатываться.

Чтобы можно было выполнять такие запросы в ClickHouse, мы реализовали несколько вещей:

1. Табличная функция file.

2. Поддержка glob patterns. В качестве имени файлов можно использовать шаблон с glob patterns (звёздочка, фигурные скобки и пр.)

3. Поддержка сжатых файлов в формате gzip, xz и zstd из коробки. Указываем gz и всё работает.

4. Функции для работы с JSON. Могу утверждать, что это самые эффективные функции для обработки JSON, которые мы смогли найти. Если вы найдёте что-нибудь лучше, скажите мне.

5. Параллельная обработка файлов. Там несколько тысяч файлов, и будут использоваться все процессорные ядра. И даже один файл будет обрабатываться параллельно, потому что у нас есть параллельный парсинг форматов данных.

6. Тот самый параллельный парсинг.

Применять можно, само собой, для обработки текстовых файлов. Еще для подготовки временной таблицы и партиций для MergeTree. Можно провести препроцессинг данных перед вставкой: читаете в одной структуре, преобразовываете с помощью SELECT и отдаете дальше в clickhouse-client. Для преобразования форматов тоже можно например, преобразовать данные в формате protobuf с разделителями в виде длины в JSON на каждой строке:

clickhouse-local --input-format Protobuf --format-schema такая-то --output format JSONEachRow ...

Serverless ClickHouse

ClickHouse может работать в serverless-окружении. Есть пример, когда ClickHouse засунули в Лямбда-функцию в Google Cloud Run: https://mybranch.dev/posts/clickhouse-on-cloud-run/ (Alex Reid). Там на каждый запрос запускается маленький ClickHouse на фиксированных данных и эти данные обрабатывает.

Текстовые форматы

Для обработки текстовых данных, естественно, есть поддержка форматов tab separated (TSV) и comma separated (CSV). Но еще есть формат CustomSeparated, с помощью которого можно изобразить и тот, и другой в качестве частных случаев.

CustomSeparated:

format_custom_escaping_rule

format_custom_field_delimiter

format_custom_row_before/between/after_delimiter

format_custom_result_before/after_delimiter

Есть куча настроек, которые его кастомизируют. Первая настройка это правило экранирования. Например, вы можете сделать формат CSV, но в котором строки экранированы как в JSON, а не как CSV. Разница тонкая, но довольно важная. Можно указать произвольный разделитель типа | и пр. между значениями, между строками и т.п.

Более мощный формат это формат Template:

format_template_resultset

format_template_row

format_template_rows_between_delimiter

С его помощью можно указать произвольный шаблон, где в строке есть подстановки, и в этих подстановках тоже указывается, какой столбец в каком формате отформатирован. Можно даже парсить XML, если очень надо.

Есть формат Regexp:

format_regexp

format_regexp_escaping_rule

format_regexp_skip_unmatched

И тут clickhouse-local превращается в настоящий awk. Указываете регулярные выражения, в Regexp есть subpatterns, и каждый subpattern теперь парсится как столбец. Его содержимое обрабатывается согласно некоторому правилу экранирования. И конечно можно написать пропускать строки, для которых регулярное выражение сработало, или нет.

ClickHouse для полуструктурированных данных

Первая идея в этом случае не использовать ClickHouse. Берите Mongo, которая отлично подходит именно для этого. Но если вдруг вы так любите ClickHouse, что просто не можете им не пользоваться можно и с его помощью.

Допустим, у вас есть таблица с логами, в ней есть столбец с датой и временем, а вот всё остальное вообще непонятно что. Очень соблазнительно всю эту кучу данных записать в один столбец 'message' с типом String. Если эта куча в формате JSON, функции для работы с JSON будут работать. Но неэффективно каждый раз, когда нам будет нужно только одно поле, например 'actor.login', читать придется весь JSON не будет преимущества столбцовой базы данных. С помощью ClickHouse мы легко это исправим прямо на лету, добавив с помощью запроса ALTER материализованный столбец:

После того, как материализованный столбец actor_login начнет записываться для новых данных в таблице, SELECT для новых данных будет читаться эффективно, а для старых будет вычисляться. Для материализации старых данных можно использовать несколько странный запрос для материализации всех данных в прошлом:

ALTER TABLE logs UPDATE actor_login = actor_login

И вы можете добавлять столбцы по мере необходимости, чтобы ускорить запросы.

Ускорение MySQL

В ClickHouse можно создать таблицу на основе табличной функции MySQL. Это просто: указываете хост: порт, БД, таблицу, имя пользователя и пароль (прямо так, как есть), делаем SELECT и всё выполняется за 15 секунд:

Работает это тоже без всякой магии: табличная функция MySQL переписывает запрос, отправляет его в MySQL и читает все данные назад, на лету на всё 15 секунд. Но что будет, если я тот же самый запрос выполню в MySQL как есть?

5 минут 41 секунда это позор! У ClickHouse тут как-будто нет преимуществ данные нужно переслать из MySQL в ClickHouse и потом уже обработать. А MySQL обрабатывает сам у себя локально почему же он так медленно работает?

Еще одна проблема результаты расходятся. У ClickHouse две строки счетчик (20577 и 13772), у MySQL один (44744), потому что он здесь учитывает collation (правила сравнения строк в разном регистре) при GROUP BY. Чтобы это исправить, можно перевести имя в нижний регистр, сгруппировать по нему и выбрать любой вариант:

Теперь счетчик один, данные почти совпадают и запрос даже ускорился. То есть ClickHouse быстрый независимо от того, какие выражения я напишу. Попробуем ускорить запрос еще больше. На основе данных из MySQL в ClickHouse можно создать словарь. Указываем в словаре ключ и источник MySQL со всеми этими данными:

Словарь будет периодически загружать таблицу в оперативку, она будет кэшироваться. Можно применять SELECT:

Получилось уже 6 секунд, хотя основное предназначение словаря использование для джойнов, когда, например, нам нужно получить данные динамически через другие столбцы. Можно также создать словари MySQL на каждом сервере в кластере ClickHouse и быстро с ними работать. Но если MySQL не выдержит нагрузки при обновлении словаря на каждом сервере в кластере, то можно создать из MySQL словарь на двух ClickHouse-серверах, и они будут доступны в виде таблиц в ClickHouse. Если с помощью движка Distributed создать таблицу, которая смотрит на эти два сервера как на реплики, то можно на всех ClickHouse-серверах создать словари из ClickHouse, которые смотрят на таблицу, которая смотрит на словарь MySQL.

Словари еще можно использовать для шардирования, если схема расположена во внешней мета-базе (и не обязательно в ClickHouse). Это тоже будет работать:

Есть вариант как радикально ускорить запрос, и для этого не нужны словари надо всего лишь переложить данные в полноценную таблицу типа MergeTree такой же структуры. Вставляем туда данные и делаем SELECT:

Видим, что SELECT выполняется за 0,6 с. Вот это настоящая скорость, какая должна быть это скорость ClickHouse!

В ClickHouse можно даже создать базу данных типа MySQL. Движок БД MySQL создает в ClickHouse базу данных, которая содержит таблицы, каждая из которых представляет таблицу, расположенную в MySQL. И все таблицы будут видны прямо в ClickHouse:

А вообще в ClickHouse много табличных функций. Например, с помощью табличной функции odbc можно обратиться к PostgreSQL, а с помощью url к любым данным на REST-сервере. И все это можно поджойнить:

Примечение: в свежих релизах ClickHouse появилась табличная функция postgresql, движок баз данных PostgreSQL и даже поддержка бинарного протокола PostgreSQL. Кажется это даже слишком круто.

Машинное обучение в ClickHouse

В ClickHouse можно использовать машинное обучение с помощью подключения готовых моделей CatBoost. После объявления модели в конфигурационном файле, она доступна для применения в виде функции modelEvaluate.

Это можно использовать для заполнения пропусков в данных. Пример: компания, занимающаяся недвижимостью, публикует объявления о квартирах с разными параметрами: количество комнат, цена, метраж. Часто некоторые параметры не заполнены например, квадратные метры есть, а количества комнат нет. В этом случае мы можем использовать ClickHouse с моделью CatBoost, чтобы заполнить пропуски в данных.

Более простые модели можно обучать прямо в ClickHouse. Модель машинного обучения у нас будет представлена как агрегатная функция например, стохастическая логистическая регрессия. Задаются гиперпараметры, значение предсказываемой функции и значения фич, причем обучение будет независимым для каждого ключа агрегации, если в запросе указан GROUP BY:

А еще мы можем добавить к агрегатной функции суффикс State:

SELECT stochasticLogisticRegressionState(...

Так можно обучить логистическую регрессию для каждого k и получить состояние агрегатной функции. Состояние имеет полноценный тип данных AggregateFunction(stochasticLogisticRegression(01, 00, 10, 'Adam'), ...), который можно сохранить в таблицу. Достать его из таблицы и применить обученную модель можно функцией applyMLModel:

Но и это еще не все. Мы можем обучить тысячи маленьких простых моделей, записать их в таблицу и сделать джойн из этой таблицы, чтобы применять свою модель для каждого ключа:

Более развернуто описано в этой презентации.

ClickHouse как графовая база данных

Конечно, ClickHouse это не графовая БД и никогда даже не планировался для этого, но тем не менее. Представьте, что таблицы представляют собой ребра графа. С помощью табличной функции можно записывать алгоритмы на графах, например, реализовать алгоритм pagerank:

Это работает, и говорят, даже быстрее, чем некоторые другие графовые базы данных. Разработал его наш друг, один из ведущих контрибьюторов Amos Bird. Правда, эта разработка не доступна в open-source. Но мы не обижаемся.

UDF в ClickHouse

Казалось бы, в ClickHouse нет возможности написать пользовательские функции (user defined functions). Но на самом деле есть. Например, у вас есть cache-словарь с источником executable, который для загрузки выполняет произвольную программу или скрипт на сервере. И в эту программу в stdin передаются ключи, а из stdout в том же порядке мы будем считывать значения для словаря. Словарь может иметь кэширующий способ размещения в памяти, когда уже вычисленные значения будут кэшированы.

И если вы пишете произвольный скрипт на Python, который вычисляет, что угодно пусть те же модели машинного обучения, и подключаете его в ClickHouse, то получаете вы как раз аналог user defined function.

Примечание: полноценная реализация UDF находится в roadmap на 2021 год.

ClickHouse на GPU и как Application Server

Это ещё два необычных примера. В компании nVidia ClickHouse заставили работать на графических ускорителях, но рассказывать я про это не буду.

А наш друг Zhang2014 превратил ClickHouse почти в Application Server. У Zhang2014 есть pull request, где можно определить свои HTTP-хэндлеры и этим хэндлерам приписать подготовленный запрос (SELECT с подстановками или INSERT). Вы делаете POST на какой-то хэндлер для вставки данных, или делаете вызов какой-то GET ручки, передаете параметры, и готовый SELECT выполнится.

Вывод

ClickHouse очень интересная система, в которой всегда можно найти что-то новое, причем, что интересно, что-то новое там всегда могу найти даже я. Многие разработчики удивляют меня тем, что они могут реализовывать внутри ClickHouse всего лишь чуть-чуть поменяв его код. И эти штуки будут работать и в production!

Мало того, что многие не доверяют общедоступным облакам, так они еще и предлагают непозволительно малые объемы дискового пространства.

Однажды мне понадобилось 1Tb облачного хранилища и выбор пал на Nextcloud, который и было решено развернуть на собственном домашнем сервере

В данной статье я опишу как быстро и безболезненно установить и настроить облако Nextcloud и облачный редактор Onlyoffice

Статья предполагает, что у вас уже установлен и настроен Ubuntu.

Все действия были проверены на Ubuntu Server 20.04

Что будем делать:
1. Установим Nginx, PHP и MariaDB
2. Добавим бесплатный SSL-сертификат Let's Encrypt
3. Развернем NextCloud
4. Произведем тонкие настройки сервера
5. Установим Onlyoffice

Бесплатные доменные имена в домене .tk можно получить на www.freenom.com

Первым делом, устанавливаем вспомогательные утилиты

sudo apt-get install nano mc zip -y

Этот пункт можно пропустить, если настраиваете облако на локальный диск, а не на отдельную машину с доступом по nfs, мне понадобилось сделать это именно на nfs

# Ставим nfs-clientsudo apt install nfs-common -y# -------------------# Монтируем папку nfs# Ставим nginxsudo mkdir -p /nfs/ncsudo mount your_host_ip:/папка_шары_nfs/ /nfs/ncsudo ls -l /nfs/nc/sudo df -hsudo du -sh /nfs/nc/# -------------------# Монтируем nfs при загрузкеsudo nano /etc/fstab# Добавим такую строку в конец файлаyour_host_ip:/папка_шары_nfs/  /nfs/nc  nfs auto,nofail,noatime,nolock,intr,tcp,actimeo=1800 0 0

Ставим nginx

sudo apt install nginx -ysudo nginx -Vsudo systemctl enable nginxsudo systemctl start nginx

Ставим php 7.4

sudo apt install php7.4-fpm php7.4-mysql php7.4 php7.4-curl php7.4-gd php7.4-json php7.4-mbstring php7.4-common php7.4-xml php7.4-zip php7.4-opcache php-apcu php-imagick -y

Настраиваем php 7.4

sudo nano /etc/php/7.4/fpm/pool.d/www.conf

снимаем комментарии со строк

env[HOSTNAME] = $HOSTNAME
env[PATH] = /usr/local/bin:/usr/bin:/bin
env[TMP] = /tmp
env[TMPDIR] = /tmp
env[TEMP] = /tmp

# Настраиваем php.ini:

sudo nano /etc/php/7.4/fpm/php.ini

opcache.enable=1
opcache.enable_cli=1
opcache.interned_strings_buffer=8
opcache.max_accelerated_files=10000
opcache.memory_consumption=128
opcache.save_comments=1
opcache.revalidate_freq=1

Разрешаем автозапуск php-fpm и перезапускаем его:

sudo systemctl enable php7.4-fpmsudo systemctl restart php7.4-fpm

Устанавливаем MariaDB:

sudo apt install mariadb-serversudo systemctl enable mariadbsudo systemctl start mariadb

Запуск сценария безопасности (здесь можно поменять пароль рута, убрать ненужные разрешения):

sudo mysql_secure_installation

Создаем базу данных для Nextcloud (в примере указан пароль nextcloud, его лучше заменить на свой) :

sudo mysql -u root -p

Вводим пароль рута для MariaDB

 >CREATE DATABASE nextcloud;CREATE USER 'nextcloud'@'localhost' IDENTIFIED BY 'nextcloud';GRANT ALL ON nextcloud.* TO 'nextcloud'@'localhost' IDENTIFIED BY 'nextcloud' WITH GRANT OPTION;FLUSH PRIVILEGES;EXIT;

Теперь надо создать файл конфигурации Nginx для Nextcloud

sudo nano /etc/nginx/sites-enable/nextcloud.conf

И вставляем в него следующий текст, естественно, заменив nc.myhost.com на свои сервера

server {    listen 80;    server_name nc.myhost.com;    return 301 https://$server_name$request_uri;}server {    listen 443 ssl;    server_name nc.myhost.com;    ssl_certificate /etc/nginx/ssl/cert.pem;    ssl_certificate_key /etc/nginx/ssl/cert.key;    root /var/www/nextcloud;    add_header Strict-Transport-Security "max-age=31536000; includeSubDomains" always;    client_max_body_size 10G;    fastcgi_buffers 64 4K;    rewrite ^/caldav(.*)$ /remote.php/caldav$1 redirect;    rewrite ^/carddav(.*)$ /remote.php/carddav$1 redirect;    rewrite ^/webdav(.*)$ /remote.php/webdav$1 redirect;    index index.php;    error_page 403 = /core/templates/403.php;    error_page 404 = /core/templates/404.php;    location = /robots.txt {      allow all;      log_not_found off;      access_log off;    }    location ~ ^/(data|config|\.ht|db_structure\.xml|README) {        deny all;    }    location / {        rewrite ^/.well-known/host-meta /public.php?service=host-meta last;        rewrite ^/.well-known/host-meta.json /public.php?service=host-meta-json last;        rewrite ^/.well-known/carddav /remote.php/carddav/ redirect;        rewrite ^/.well-known/caldav /remote.php/caldav/ redirect;        rewrite ^(/core/doc/[^\/]+/)$ $1/index.html;        try_files $uri $uri/ index.php;    }    location ~ ^(.+?\.php)(/.*)?$ {        try_files $1 = 404;        include fastcgi_params;        fastcgi_param SCRIPT_FILENAME $document_root$1;        fastcgi_param PATH_INFO $2;        fastcgi_param HTTPS on;        fastcgi_pass unix:/run/php/php7.4-fpm.sock;    }    location ~* ^.+\.(jpg|jpeg|gif|bmp|ico|png|css|js|swf)$ {        expires modified +30d;        access_log off;    }}

Теперь необходимо получить сертификаты для ssl

Устанавливаем Certbot и его плагин для Nginx:

sudo apt install certbot python3-certbot-nginx

Получаем сертификаты для всего (я получал для всех поддоменов, которые прописал на freenom, в т.ч. для почты, заббикса, и т.п., у вас могут быть и должны быть свои)

Сначала с ключом --dry-run проверяем все ли в порядке

sudo certbot certonly --agree-tos --email you@mail -d nc.myhost.com-d www.myhost.com -d zabbix.myhost.com --nginx --dry-run --d

Если все хорошо, то получаем сертификаты

sudo certbot certonly --agree-tos --email почта@администратора -d myhost.com-d nc.myhost.com-d cloud.myhost.com-d zabbix.myhost.com-d www.myhost.com-d mail.myhost.com sudo certbot certonly --agree-tos --email your@mail -d nc.myhost.com-d www.33rus.com -d zabbix.33rus.com --nginx n 

Сертификаты появятся в папке /etc/letsencrypt/live/myhost.com cert.pem chain.pem fullchain.pem privkey.pem

Подключаем сертификаты к сайту

sudo nano /etc/nginx/sites-available/nextcloud.conf

ssl_certificate /etc/letsencrypt/live/myhost.com/fullchain.pem;
ssl_certificate_key /etc/letsencrypt/live/myhost.com/privkey.pem;

Устанавливаем Nextcloud:

Скачиваем последнюю версию с сайте Nextcloud:

cd /tmp/sudo wget https://download.nextcloud.com/server/releases/nextcloud-21.0.0.zipsudo unzip nextcloud-21.0.0.zipsudo cp -R nextcloud /var/www/nextcloud/cd /var/www/sudo chown -R www-data:www-data nextcloud/sudo chown -R www-data:www-data /nfs/nc

Обратите внимание, в данном случае я использую папку на nfs, вам необходимо использовать папку в соответствии с вашими настройками

Почти все. Заходим на https://nc.myhost.com
Создаем пользователя, пароль, прописываем доступ к каталогу /nfs/nc/
Прописываем созданную ранее базу данных и пароль к ней.

Теперь тонкая настройка Nextcloud и установка Onlyoffice

Ставим Redis и APCu

sudo apt install memcached php-memcached -ysudo apt install php-apcu redis-server php-redis -ysudo nano /var/www/nextcloud/config/config.php

И добавляем следующие строки перед закрывающей скобкой )

'memcache.local' => '\OC\Memcache\APCu',
'memcache.distributed' => '\OC\Memcache\Redis',
'redis' =>
array (
'host' => '127.0.0.1',
'port' => 6379,
),
'memcache.locking' => '\OC\Memcache\Redis',

Переиндексация файлов (если вдруг вы скопировали файлы в папку nexcloud не через интерфейсы nextcloud, то их надо переиндексировать)

sudo -u www-data php /var/www/nextcloud/occ files:scan --all

Устанавливаем OnlyOffice DocumentServer

Первым делом устанавливаем версию PostgreSQL, включенную в вашу версию Ubuntu:

sudo apt install postgresql -y

После установки PostgreSQL создайте базу данных и пользователя PostgreSQL:

Пользователем и паролем для созданной базы данных должны быть onlyoffice.

sudo -i -u postgres psql -c "CREATE DATABASE onlyoffice;"sudo -i -u postgres psql -c "CREATE USER onlyoffice WITH password 'onlyoffice';"sudo -i -u postgres psql -c "GRANT ALL privileges ON DATABASE onlyoffice TO onlyoffice;"

Установка rabbitmq и nginx-extras:

sudo apt install rabbitmq-server -ysudo apt install nginx-extras -y

Установка ONLYOFFICE Docs

Добавьте GPG-ключ:

sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys CB2DE8E5

Добавьте репозиторий ONLYOFFICE Docs:

sudo echo "deb https://download.onlyoffice.com/repo/debian squeeze main" | sudo tee /etc/apt/sources.list.d/onlyoffice.list sudo apt update

Устанавливаем mariadb-client!

sudo apt install mariadb-client -y

Устанавливаем ONLYOFFICE Docs. Не ошибитесь с вводом пароля. Это должен быть onlyoffice

sudo apt install onlyoffice-documentserver -y

Переводим onlyoffice на https

sudo cp -f /etc/onlyoffice/documentserver/nginx/ds-ssl.conf.tmpl /etc/onlyoffice/documentserver/nginx/ds.confsudo nano /etc/onlyoffice/documentserver/nginx/ds.conf 

Меняем порт ssl не забыв пробросить его в роутере

listen 0.0.0.0:7443 ssl;
listen [::]:7443 ssl default_server;

Перезапускаем nginx

sudo service nginx restart

Настраиваем cron

sudo crontab -u www-data -e

# Добавляем строчку

*/5 * * * * php -f /var/www/nextcloud/cron.php

Ну, вот и все, останется через веб-интерфейс установить плагин ONLYOFFICE в вашем Nextcloud и прописать сервер https://myhost.com:7443

Технологические гиганты при помощи денег инвестиционных фондов контролируют всё большую часть новых разработчиков и продуктов, перекрывая тем самым путь для новых программ и новых технологий в строительной отрасли.

Сегодняшние лидеры САПР-индустрии: Autodesk, Hexagon, Nemetschek, Bentley, Trimble - хорошо готовятся к будущим угрозам: стандартной тактикой больших корпораций стал агрессивный захват новых рынков и поглощение возможных конкурентов на ранних стадиях развития.

В результате вся САПР-индустрия стала похожа на олигополию, в которой доминирует группа из нескольких компаний. И их положение на вершине становится всё более непоколебимым.

Содержание:

1. BlackRock - хозяин всех технологий

2. Олигополия на рынке САПР

3. Autodesk идёт по стопам Oracle

4. Почему программы не развиваются

5. Борьба корпораций с open source

6. Autodesk и формат данных IFC

7. Проблема Autodesk - организация открытых стандартов - ODA

8. В заключение

В этой статье мы рассмотрим:

• кто сегодня руководит развитием новых технологий в строительстве

• как происходит синхронизация политики между САПР-вендорами

• почему компании не развивают программы, написанные в 90-е

• как большие корпорации контролируют open source разработчиков

• почему IFC и Dynamo - это непрозрачный open source

• как благодаря Autodesk появился альянс ODA

• как велась борьба за формат DWG между ODA и Autodesk

• как сегодня развивается борьба за открытость форматов RVT и RFA

• как BlackRock и Vangaurd создали плановую экономику на мировом уровне

BlackRock - хозяин всех технологий

Основа олигополистической структуры в строительном проектировании - это большая пятёрка САПР-вендоров: Autodesk, Hexagon, Trimble, Bentley, Nemetschek.

Эти компании выходили с середины 90-х на IPO (initial public offering), распродав большую часть своих акций среди крупнейших инвестиционных фондов Соединённых Штатов и распределив тем самым ответственность за будущее компаний среди держателей акций.

Главными бенефициарами (и держателями акций) от выхода САПР-компаний на биржу стали три американских инвестиционных фонда: The Capital Group, Vanguard и BlackRock, которые на протяжении последних 30 лет скупали акции технологических компаний напрямую или через свои банковские филиалы.

Мало кто слышал о BlackRock, но эта фирма является крупнейшей инвестиционной компанией, под управлением которой находятся активы стоимостью в $8,7 трлн в 2021 году (в 2015 году - $4,6 трлн). На 2021 год BlackRock, штаб-квартира которой расположена на Манхэттене, имеет отделения в 100 странах, и она тесно связана с крупнейшими финансовыми регуляторами, такими как министерство финансов США, Федеральная резервная система (ФРС) США и Европейский центральный банк (ЕЦБ).

Как только какой-либо крупный экономический субъект попадает в поле зрения таких фондов, как Vanguard и BlackRock, они предлагают ему возможности выгодных инвестиций. Пользуясь своей надёжной репутацией, связями и огромными средствами, Vanguard и BlackRock скупают активы субъекта и начинают заниматься его управлением.

Например, только в Германии в 2014 году BlackRock принадлежало 4% акций BMW, 5,2% акций Adidas,7% акций Siemens, 6% акций Daimler. В целом BlackRock является акционером (почти всегда самым крупным) всех предприятий, входящих в индекс DAX (важнейший фондовый индекс Германии).Гигантская платформа Aladdin, созданная BlackRock для управления активами, ежесекундно отслеживает стоимость акций, облигаций и других активов, находящихся под управлением компании, а также рассчитывает, как влияют на их котировки различные события, такие как рост цен на нефть или алюминий.

BlackRock является мажоритарным акционером почти во всех технологических компаниях мира. Какое отношение к строительству и проектированию имеют инвестиционные корпорации BlackRock и Vanguard (Vanguard/Windsor)?

BlackRock и Vanguard принадлежат почти все САПР-компании и технологии в мире строительного проектирования и BIM решений.

На графике владения акциями заметно, что особым смыслом здесь обладает цифры - 7-10%. До таких цифр инвестфонды доводят долю владения любой компанией, чего хватает, чтобы полностью контролировать целые корпорации.

На следующем графике видно похожее распределение владения акциями крупнейших компаний мира - Big 5.

Из-за того, что 70% акций компании после выхода на биржу распределены между тысячами инвесторов, контрольный пакет составляет всего 720% акций, что даёт держателю небольшого пакета полномочия по принятию всех решений в компании.

Таким образом, если у вас есть 7-20% акций нескольких основных компаний-монополистов, то вы почти полностью влияете на развитие всей отрасли.

При таком многократном перекрестном владении и при наличии долей практически во всех крупных корпорациях - как этим инвестиционным фондам удаётся следить за темой BIM или, например, развитием AR в строительстве, да и вообще за всеми технологиями мира одновременно?

Олигополия на рынке САПР

Олигополия в первую очередь означает скоординированность и взаимосвязь в действиях на рынке, что осуществляется Vanguard и BlackRock через глав компаний, утверждаемых на собрании акционеров.

С выходом на биржу большие САПР-компании фактически убили конкуренцию на рынке САПР тем, что основные стратегические решения теперь принимаются не агрессивными руководителями компаний, которые борются за выживание и место на рынке, а пролоббированным советом директоров, который контролируется извне финансовыми специалистами, интересующимися отраслью строительства только как цифрой в годовом отчёте Annual Revenue.

Политолог Jan Fichtner из Амстердамского университета, исследуя этих основных финансовых игроков, утверждает: Влияние в основном осуществляется через фоновые дискуссии. Вы ведете себя на поводу у BlackRock и Vanguard, потому что знаете, что однажды вам может понадобиться их благосклонность. Для руководства компаний и корпораций рационально действовать в интересах основных держателей акций.

"Там, где есть договор, есть перочинный нож."

Шарль-Морис де Талейран-Перигор

Все, кто работает в развитии технологий строительной отрасли, постепенно попадают в жернова этой плановой экономики, созданной акулами капитализма при поддержке богатых династий запада, пенсионных фондов и печатного станка ФРС.

BlackRock управляет активами централизованно и строго следит за соблюдением всех формальных предписаний. Но сверх этого BlackRock и Vanguard практически ничего не делают. А между тем для функционирования рыночной экономики чрезвычайно важно, чтобы акционеры постоянно держали руку на пульсе событий в своих предприятиях и критически оценивали политику менеджмента.

В итоге сегодня все мировые САПР (и вообще все технологические) компании принадлежат инвестиционным фондам, которые на бумаге занимаются управлением стратегией компании, а де-факто - только соблюдением формальных предписаний для отчётов, которые позже попадают в красивый PDF документ для ежегодного собрания совета директоров.

"Плановая экономика в масштабах сколько-нибудь крупной промышленной системы требует значительной централизации, а следовательно, и бюрократического аппарата, способного управлять этой централизованной машиной. Если планирование сверху не будет сочетаться с активным участием снизу, если поток общественной жизни не будет постоянно восходить снизу вверх, плановая экономика приведет к новой форме манипулирования народом."

Бегство от свободы, Эрих Фромм

Как сегодня Trimble будет соперничать с Autodesk или Hexagon на одном рынке, если CEO этих компаний отчитываются ежеквартально перед одними и теми же инвестиционными фондами с WallStreet, которые, в свою очередь, не заинтересованы в резких изменениях на тех рынках, от которых зависят их годовые отчёты и, как следствие, их личные бонусы.

Нет здоровой конкуренции - не будет и новых технологий. Отчасти поэтому все крупные корпорации в мире строительного проектирования фактически не имели отношения к разработке новых продуктов в последние 20 лет.

Если ещё десять лет назад такие крупные компании, как Autodesk, Nemetschek, Trimble имели репутацию уже не новаторов, а скорее имитаторов, когда было достаточно просто скопировать новый популярный продукт и выпустить свой клон, то сегодня необходимость в клонировании продуктов отпала. Сегодня стартапы и целые компании скупаются за любые деньги, которые предоставляют своим подопечным BlackRock и Vanguard.

Чтобы не конкурировать друг с другом на одном рынке, каждая компания из big5 САПР выбирает для себя отдельные технологические ниши и скупает постепенно программы и любые технологии в этом направлении.

Для примера John Walker (будущий CEO Autodesk) предлагал Mike Riddle купить Autocad в 1982 году всего за $15 тыс. (изначально за $8 тыс.), но Mike настоял на условиях продажи Autocad за 1$ и 10% c продаж продукта. В 2020 году Revit стал самым продаваемым продуктом Автодеск, который она купила в 2002 году за 133 млн долларов. А уже в 2018 году Autodesk заплатил за Plangrid (стартап-приложение для планшета) почти миллиард долларов ($875 млн), где одним членом из совета директоров, за несколько лет до покупки, стала бывшая CEO Autodesk - Carol Ann Bartz.

Таким образом, на данный момент весь мир проектирования и вообще все технологии мира разделились на тех, кто принадлежит Blackrock и Vanguard, и тех, кого эти фонды через свои САПР-филиалы ещё купить не успели. Ни одна организация с высокими денежными показателями не имеет возможности избежать встречи с такими финансовыми гигантами, в какой бы стране она ни находилась.

Сегодня уже невозможно построить новый Autodesk или Trimble в нашем мире, где с такими ресурсами, которыми располагают сегодня инвестфонды, можно купить целые страны, не говоря уже о талантливых командах разработчиков.

!Нельзя ни в коем случае демонизировать структуры, которые были построены BlackRock и Vanguard: любая организация и скорее всего каждый человек на земле, имея такие бесплатные ресурсы и доступ к печатному станку, воспользовались бы этим и поступили бы на месте Blackrock точно так же, скупая всё на своём пути.

В противовес монополиям, только мир open source решений мог бы сегодня составить конкуренцию таким олигопольным конгломератам. Посмотрим дальше на то, как большие корпорации пытаются контролировать open source движение.

Для чего мировым вендорам САПР нужны тысячи специалистов по базам данных, oб open source решениях, а также о решениях, над которыми работают основные вендоры САПР, поговорим в седьмой части.

Чтобы к седьмой части немного лучше понимать нового игрока на рынке САПР - Oracle, базы данных и open source, а также из-за схожести поведения двух компаний - сравним историю Oracle с историей развития Autodesk и посмотрим на то, как эти две корпорации боролись с open source движениями на своём рынке.

Autodesk идёт по стопам Oracle

История Autodesk, одного из лидеров САПР-мира, начинает всё больше походить на историю такого лидера своего времени, как Oracle. Так же, как Autodesk с Autocad была последние 30 лет мировым лидером 2D-проектирования, Oracle, в это же время, была мировым лидером баз данных - DBMS систем (database management system).

Прибыль Oracle от бизнеса, построенного на хранении данных, примерно в 10 раз больше, чем продажи лидеров САПР-отрасли: годовой оборот Oracle сегодня - $20 mlrd., а у Autodesk - всего $1,7 mlrd.. Как и во всех технологических компаниях мира, ведущими акционерами Oracle (как и Autodesk, Trimble, Apple, Microsoft и почти всех компаний на земле) являются две крупнейшие в мире инвестиционные компании - Vanguard и BlackRock.

Во многих статьях говорится, что Oracle была основана в конце 1970-х и что Oracle продает линейку программных продуктов, которые помогают крупным и средним компаниям управлять своей деятельностью. Но везде каким-то образом игнорируется тот факт, что Oracle всегда была значительным игроком в индустрии национальной безопасности. И что его основатель не заработал бы свои миллиарды, если бы не помогал создавать инструменты современной государственной и мировой слежки.

Как многие основные компании, выросшие после холодной войны, обязаны своим развитием федеральным и оборонным контрактам, так и Oracle на самом деле берет свое название от кодового названия проекта ЦРУ 1977 года. И именно ЦРУ было первым заказчиком Oracle.

C середины 2000-х Oracle начинает активно интересоваться строительной отраслью. Примечательно, что Oracle стал стратегическим партнёром buildingSMART в 2019 году, за один год до того, как главный САПР-производитель Autodesk присоединился к buildingSMART в 2020 году.У Oracle есть все шансы, чтобы стать лидером BIM-технологий. Об этом и почему BIM - это базы данных, поговорим подробнее в седьмой части.

В какой-то момент большие технологические компании выросли в большие структуры, которые, как и все крупные и сложные структуры, однажды перестали активно заниматься разработкой новых технологий.

Как в открытых письмах лучших архитекторов мира, адресованных Autodesk, (подробнее об этом в Часть 4: Борьба CAD и BIM. Монополии и лоббисты в строительной отрасли), так и в подкасте с Еленой Слеповой - мы все отмечаем, что Autodesk почти не развивает основной продукт Revit, а занимается только дизайном и украшательством интерфейса, попутно покупая новые стартапы (Navisworks, Assemble, Vault, Civil 3D) и технологии.

Раздражение проектировщиков продуктами Autodesk растёт от версии к версии, но большие корпорации: Autodesk, Nemetschek и другие крупные покупатели стартапов - не могут привнести в код купленных программ значительных изменений.

Почему программы не развиваются

Любой большой программный продукт, разрабатываемый больше пяти лет, напоминает длинный "спагетти-код". При этом только несколько главных разработчиков в компании знают, как работает (максимум) 70% кода в такой программе, как Oracle Database, Autocad, Revit или Archicad.Новые инструменты от Autodesk, Nemetschek или Oracle сегодня являются лишь попыткой скрыть накопленные проблемы от разработчиков, потому что справиться с ними стало невозможно.

Затухание активного роста в техническом плане начинается с ухода разработчиков на более высокие должности или, что происходит чаще, с Exit основных разработчиков, которые забирают деньги (после продажи стартапа или выхода компании на IPO) и сами становятся венчурными инвесторами.

После Exit главных разработчиков большое количество костылей, из которых состоят эти программы, в последующих проектах создают трудно выявляемые баги, которые заваливают всю дальнейшую разработку продукта. Такой продукт, или, точнее, код, из которого он состоит, обозначают как legacy code.

Legacy code (от Legacy (англ.) наследство) устойчивое выражение, обозначающее старый код без каких-либо пояснений; главным его признаком является отсутствие возможности в нём разобраться.

На примере Oracle можно представить, в чём же состоит сложность Legacy кода и почему программы из 90-х почти не развиваются в последнее время.

Объем кодовой базы Oracle Database в период разработки версии 12.2 в 2017 году составлял 25 миллионов строк на языке C и стоит вам изменить лишь одну из этих строк, как ломаются тысячи написанных ранее тестов. За прошедшие годы над кодом Oracle Database успело потрудиться несколько поколений программистов, которых регулярно преследуют жесткие дедлайны, благодаря чему код превратился в настоящий кошмар.

Сегодня код любой программы из 90-х состоит из сложных кусков кода, отвечающих за логику, управление памятью, переключение контекстов и многое другое; они связаны друг с другом при помощи тысяч различных костылей. В итоге, у разработчика может уйти день или два только на то, чтобы разобраться, чем же в действительности занимается та или иная строка кода.

Возникает вопрос: каким же образом при всем этом Oracle Database (или Revit, Archicad) до сих пор удается держаться на ногах? Секрет в миллионах тестов. Их полное выполнение может занимать от 20 до 30 часов (при этом выполняются они распределенно на тестовом кластере из 100-200 серверов), поэтому сегодня процесс исправления одного единственного бага в Oracle Database занимает от нескольких недель до нескольких месяцев.

Подобным образом, любой закрытый продукт, который разрабатывался больше 5 лет - автоматически становится кодом спагетти, который к своей следующей версии сможет передвигаться только на костылях.

Autodesk разрабатывает Revit проще: разработчики просто не занимаются темой производительности, а от версии к версии создают только такие инструменты, которые работают поверх основного функционала.

Так как основной код был написан в конце 90-х, в Revit используется (исключая визуализацию) только одно ядро процессора, что сдерживает производительность проектирования. Например, у программы Revit уже нет возможности работать в многопоточном режиме, используя все ядра в hardware. И даже если у вас через 10 лет будет тысяча ядер в процессоре - прироста производительности в программе Revit вы не получите.

Благодаря постоянной текучке кадров в дополнение к проблеме закрытой разработки кода, вокруг нас полно старых Legacy продуктов, которые написаны ужасно плохо, но при этом невероятно сложно и дорого поддерживаются сегодня на плаву. Причём переписать их уже никогда не получится, потому что они взаимодействуют с таким же ужасным кодом у пользователей продукта, и поэтому необходимо соблюдать совместимость с багами и не документированными особенностями. По этой причине многие продукты стали заложниками обратной совместимости. В том числе Windows, Microsoft Office и основные программы на рынке САПР.

У таких программ, как Revit, ArchiCAD, Microstation уже нет будущего, и возможно, сами вендоры это понимают. Поэтому все силы корпораций сегодня тратятся не на увеличение производительности геометрического ядра, а на поиск нового успешного продукта или новых уникальных разработчиков, как Leonid Raiz (Revit), Mike Riddle (AutoCAD) или Gabor Bojar (ArchiCAD).

Из-за отсутствия новых успешных продуктов, основные усилия таких больших корпораций начинают концентрироваться на навязывании своих продуктов, агрессивном маркетинге и на том, чтобы поддерживать текущее ресурсоёмкое жизнеобеспечение сложной и забюрократизированной структуры.

Исследование Campaign for Clear Licensing, показало, что аудиты, проводимые такими поставщиками программного обеспечения, как Oracle и Autodesk (Microsoft, SAP, IBM) блокируют конкуренцию и препятствуют инновациям в ИТ-отделах компаний, где эти аудиты проводились.

К началу 2000-х основные корпорации заняли свои ниши в отрасли и каждый год спокойно распределяли прибыли среди акционеров. Но с началом эры open source на горизонте появляется новый конкурент - разработчики открытых и прозрачных продуктов, совсем не похожие на специалистов по контракту, переехавших в Калифорнию или Бостон из Азии или Европы.

Основной головной болью руководителей больших корпораций Oracle, Microsoft, SAP, Autodesk, IBM к середине 2000-х становится не миллион строк спагетти кода, а новые open source продукты, стремительно набирающие популярность: Linux, GitHub, Spark, Mozilla, Gimp, PHP, WordPress, Android, Blender и тысячи других.

В случае с Oracle таким конкурентом стал молодой open source стартап из Швеции - MySQL.

Борьба корпораций с open source

Обычно любые стартапы, стоящие на пути корпораций, уже к началу 2000-х можно было купить при поддержке BlackRock, но как купить Оpen source-идею, за которой стоят сотни разработчиков со всего мира?

MySQL - open source проект, который одновременно разрабатывали сотни специалистов со всего мира. При этом код полностью бесплатен и прозрачен для дальнейшей разработки (или написания инструментов для него), благодаря чему MySQL быстро завоевал популярность на рынке баз данных.

В борьбе за рынок Oracle начала снижать цены и предоставлять большие скидки тем компаниям, которые уже использовали или ещё только собирались использовать MySQL. Одновременно с этим Oracle, осознавая угрозу своему бизнесу, всеми способами пытался купить это быстрое, бесплатное и прозрачное DBMS решение.

В течение всего 2006 года Ларри Эллисон (глава Oracle с одесскими корнями) неоднократно предлагал выкупить MySQL, но трое друзей-шведов, основателей MySQL, постоянно отвергали предложение от империи зла Oracle, взлёт которой произошёл благодаря сотрудничеству с ЦРУ.

В итоге акционеры MySQL продали свой продукт компании Sun (отцы-основатели МySQL получили от сделки по $10 mln.) и уже в 2009 году Oracle сумел опередить IBM и заключил соглашение о покупке компании Sun (вместе с продуктом MySQL) за $7.4 миллиардов.

В 2009 MySQL ждало большое обновление: в версии MySQL 5.4 появилась поддержка 16-процессорных серверов x86, и, по некоторым тестам, производительность MySQL должна была вырасти в десятки раз. Выход MySQL 5.4 был запланирован на 21 апреля, но, по случайности, за день до того произошла сделка с Oracle.

Уже после покупки, чтобы не давать развития своему непокорному приёмному open source сыну, с 2009 г., Oracle (которая теперь являлась владельцем прав на MySQL) пыталась изнутри расшатать и замедлить открытую модель разработки знаменитой СУБД.

Таким образом, Oracle, при помощи BlackRock, расчистил рынок своему основному закрытому спагетти продукту Oracle Database на следующие 10 лет, а акционеры инвестиционных фондов теперь могли ожидать выплаты дивидендов на ожидаемом уровне в ближайшие годы.

Но open source MySQL-идею убить уже невозможно, и разработчики из комьюнити MySQL начали перетекать в другие open source проекты. В итоге все основные разработчики MySQL махнули рукой на Oracle и перешли на более быстрые и современные open source базы данных - MariaDB (форк копия MySQL), PostgreSQL и SQL-lite.

Подобные покупки постоянно происходят и на рынке САПР-решений. Все те же тренды ждут любых разработчиков САПР и BIM-решений, а сама история с MySQL похожа на историю с IFC, изначально открытым форматом.

Oracle, по сути, сделал c MySQL то же самое, что сделал Autodesk c IFC в середине 90-х, но гораздо быстрее и проще.

Autodesk и формат данных IFC

Идея открытого формата IFC родилась у визионера строительных технологий Leonard Obermeyer, который перед олимпиадой в Мюнхене во время строительства мюнхенского аэропорта взаимодействовал с поставщиками и фирмами со всего мира. Во время проектирования олимпийских проектов Leonard Obermeyer понял, что нужен один общий язык, на котором будут общаться строители и производители строительных элементов со всего мира.

Так как сам Leonard Obermeyer не был программистом, эту идею он передал своему родному факультету мюнхенского университета, где дальнейшей разработкой нового открытого формата занимались Richard Junge и Thomas Liebich с командой студентов.

Идеей создателей IFC-формата было создать всемирный и свободный язык обмена данными в строительстве (несмотря на военные корни формата IFC-STEP, протокола Application Protocol 225), но в начале 90-х ещё мало было известно об open source.

Благодаря другу Leonard Obermeyer - Patrick MacLeamy (CEO of HOK) - Autodesk активно включается в развитие всемирной идеи IFC. Проходит всего несколько лет, и регистрацию и разработку IFC берёт полностью под свой контроль Autodesk.

Об этом подробнее в Часть 3: Отцы BIM технологий. Кто стоит за успехом Autodesk и openBIM?

Но к концу 90-х, не имея нормальных 3D-продуктов, сам Autodesk не смог применять нормально этот продукт в своих программах - и идея мирового 3D формата уходит под контроль картельной организации IAI (которая позже переименовывается в buildingSMART).

Для контроля над развитием IFC у руля buildingSMART остаётся Patrick MacLeamy, партнёр корпорации Autodesk по совместным проектам с компанией HOK (где McLeamy был CEO). К слову, в некоторых странах ещё сегодня представителями Autodesk и представителями buildingSMART являются зачастую одни и те же специалисты.

Исходя из этих фактов, можно предположить, что американская организация buildingSMART держит Autodesk в курсе возможных вариантов развития, для того чтобы Autodesk смог вовремя перехватить инициативу в мире форматов обмена данными и заниматься новыми технологиями без оглядки на конкурентов.

Подобным образом Autodesk выпустил на рынок open source решение Dynamo, которое используется в Revit и Civil 3D для расширения функционала. Dynamo изначально представлялся как open source продукт, который внезапно возник в 2011 году в недрах Autodesk (или точнее был разработан по заказу Autodesk). Так же, как и buildingSMART, к рождению которого приложила руку корпорация Autodesk, - код Dynamo тоже не отличается прозрачностью. Многие разработчики указывают на то, что пакеты, завернутые в библиотеки DLL, часто запрещают доступ к коду, что не позволяет пользователям исследовать ни код, ни то, как отдельные куски кода обрабатываются или анализируются в инструменте Dynamo. Что также не позволяет использовать Dynamo в продуктах, по-настоящему открытых, таких как BlenderBIM (для визуального программирования в Blender сегодня используется Sverchok).

Таким образом, Autodesk в контроле над открытыми проектами IFC и Dynamo создала конструкцию, похожую на ту, что Oracle создал с MySql. Только в мире баз данных - Oracle открыто, через Sun, купила open source - MySql, а Autodesk на расстоянии контролирует движение проектов Dynamo и buildingSMART (который, в свою очередь, старается на расстоянии общаться с европейскими вендорами openBIM движения - Nemetschek).

И если сегодня образуется по-настоящему новый open source формат или у Revit появится достойный open source конкурент, - то Autodesk пойдёт проверенным путём Oracle и, возможно, попытается выкупить права на любой продукт на самых ранних стадиях, что, скорее всего, опять продлит жизнь продуктам Autodesk ещё на одно десятилетие.

Сегодня buildingSMART неспешно пытается устанавливать правила на рынке САПР, при этом интересы buildingSMART в основном диктуются потребностями основных игроков в этой нише - Autodesk, Bentley Systems (с 2019 Oracle).

Единственной более-менее свободной от лоббирования организацией остаётся альянс ODA (Open Design Alliance).

Проблема Autodesk - организация открытых стандартов - ODA

ODA круто изменил ситуацию для пользователей CAD во всём мире, когда позволил разработчикам CAD-приложений существенно сократить затраты на поддержку формата DWG, ставшего де-факто отраслевым стандартом для обмена CAD-данными.

ODA создала продукты (основным из которых является Teigha) с возможностью для более чем 2000 организаций-членов работать без всякой оглядки на Autodesk, чем фактически убрал искусственные барьеры, которые сдерживали развитие всей отрасли САПР.

Альянс по Открытому Проектированию (Open Design Alliance) является некоммерческим консорциумом, нацеленным на распространение открытых форматов для обмена данными САПР. Технологическая платформа ODA обеспечивает пользователей средствами для создания широкого спектра приложений, включая средства визуализации и даже полномасштабные САПР. Платформа поддерживает файлы DWG, DGN, RFA, IFC, RVT с возможностями редактирования, импорта и экспорта в другие файловые форматы.

История ODA началась с попытки Autodesk купить компанию SoftDesk, которая в середине 90-х разрабатывала свою САПР-платформу с целью полностью заменить AutoCAD. После антимонопольного расследования суд запретил Autodesk покупать SoftDesk, который позже (после передачи прав VISIO) был выкуплен компанией Microsoft.

На основе технологий компании Softdesk и InteliCADD (позже InteliCAD) в 1998 году образуется новый альянс OpenDWG. Альянс ODA (OpenDWG Alliance) поставил своей целью уравнивать шансы всех производителей САПР-решений, вне зависимости от того, в каких отношениях с компанией Autodesk находятся эти разработчики.

Первый конфликт между ODA и Autodesk возник в начале 2000-х вокруг формата DWG.

В 1998 году ODA предоставила новые инструменты миллионам пользователей программного обеспечения с возможностью продолжать использовать формат файла DWG без привязки к единственному поставщику продуктов - Autodesk.

Примечательно, что сам формат файла DWG и программа Autocad были разработаны Mike Riddle и что DWG использовался в конце 1970-х в его InteractCAD, и только в 1982 году John Walker выкупает полностью продукт Autocad вместе с форматом DWG, основывая таким образом фирму Autodesk для массовой продажи DWG продукта.

Сам Autodesk после покупки программы Autocad (20 лет) не предоставлял API для доступа к файлам DWG, поэтому и ответом на такую закрытость стало появление на рынке САПР первой открытой инициативы: GNU LibreDWG, который мог читать формат DWG. Но, к сожалению, LibreDWG не мог редактировать информацию в DWG файле.

Этот барьер по изменению файла DWG смог преодолеть альянс ODA с продуктом Teigha, который позволял работать с форматом DWG в любой САПР-программе. Этот продукт уравнял шансы всех САПР-разработчиков, и именно ему обязаны своим успехом такие ныне известные компании, как бельгийская Bricsys, немецкая Graebert, французская DraftSight, китайская ZWSoft, российская NanoSoft и ряд других.

В ответ на открытые разработки Autodesk создал для сторонних разработчиков САПР инструмент - RealDWG (настоящий истинный DWG) в виде модулей экспорта и импорта, что позволяло сторонним разработчикам САПР-программ наконец встраивать в свой функционал экспорт и импорт чертежей в формате DWG.

С 2006 года на ODA посыпались судебные иски от Autodesk, которая пыталась защитить купленный ими в 1982 году продукт DWG. И с момента противостояния с ODA, чтобы усложнить жизнь конкурентам и искусственно ограничить конкуренцию, Autodesk создавала каждые несколько лет новую версию самого правильного DWG формата (при этом вставляя в формат водяные знаки Autodesk).

В 2006 году компания Autodesk внедрила в обновленный формат DWG 2007 технологию TrustedDWG, которая позволяла определить, создан ли файл формата DWG в одной из программ Autodesk или в программе, использующей RealDWG. В случае, если файл DWG 2007 создан в нелицензированной программе, AutoCAD показывал сообщение, предупреждающее пользователя о возможных проблемах совместимости. Компания мотивировала своё решение заботой о пользователях.

Из-за резкого интереса конкурентов к инструментам ODA, спустя 25 лет после создания DWG, Autodesk в 2006 году решила зарегистрировать DWG как свою торговую марку. Захватив рынок CAD, Autodesk утверждала, что самый правильный DWG файл могут делать только продукты Autodesk.

Autodesk выиграл суды, и на первый взгляд показалось, что Autodesk отстоял свои права на формат DWG, но судебные запреты не стали концом света для альянса ODA.

В 2007 году иск был окончательно отозван. Стороны заключили мировое соглашение. ODA исключил код TrustedDWG из библиотек DirectDWG, а Autodesk изменил предупреждающие сообщение в AutoCAD 2008. В итоге в решениях суда речь шла только о торговой марке "DWG", при этом формат файла DWG остался общедоступным.

Возможно, уже сегодня подобная борьба разворачивается за форматы RVT и RFA.

Revit (и похожий продукт форк PTC - Solidworkds) и форматы RFA и RVT были разработаны Леонидом Райцем (и его учителем по PTC - Самуилом Гайзбергом) в 90-е. После покупки при помощи патентов и регистрации прав на торговые марки Autodesk пытался закрепить своё право над 3D САПР-программой Revit и её форматом.

В мае 2020 года, на пике интереса к продукту Revit и его BIM решениям (а не на закате популярности, как это было с Autocad), ODA официально выходит на рынок со своим продуктом BimRv SDK. BimRv SDK от ODA предлагает работать с данными Revit без зависимости от приложения Revit. Инструмент позволяет, например, создавать семейства (геометрию типов), не используя Revit.

За несколько лет до публикации релиза BimRv ODA становится первым вендором, который позволяет не просто читать информацию из IFС, а дает возможность создавать IFC геометрию, чего ещё ни одна САПР-компания сделать не смогла. Полный открытый IFC-набор инструментов от ODA будет представлять собой доступ к данным и к созданию объектов, интегрированный с высокоскоростной визуализацией и возможностями конвертирования IFC-данных в другие форматы, такие как .dwg.

Логично, что если инструмент, редактирующий Revit или IFC файлы от ODA, получит реальное распространение, то прибыли, которые Autodesk сегодня делает на своих продуктах, могут резко упасть.

На опыте борьбы с Softdesk и после судебных тяжб с ODA по продукту Teigha-DWG Autodesk понимает, что ODA c BimRv SDK уже продвинулась на порядок дальше в своих возможностях, и теперь ODA уже не допустит ошибок, которые были допущены в борьбе за открытость формата DWG.

Поэтому сегодня Autodesk остаётся массово скупать новые технологии на рынке стартапов или пробовать "усложнить" работу альянса ODA, который пытается незаконно (по мнению Autodesk) распространять форматы RVT и DWG и, по пути, стать тем самым возрождённым SoftDesk, который Autodesk не получилось до конца убить в самом начале развития.

Самый простой и верный способ обмануть человека это прикинуться другом.

Публий Овидий Назон

Наблюдая за успехами ODA со стороны, через несколько месяцев после того, как ODA опубликовал BimRV, Autodesk вынес волевое решение присоединиться к альянсу ODA, чтобы, возможно ближе понять все возможности альянса, который ещё не удалось купить. Autodesk указал причину присоединения к ODA: интерес к набору инструментов IFC от ODA (который обеспечивает полную и гибкую совместимость с IFC для любого настольного или веб-приложения). При этом в саму организацию buildingSMART, которая занимается не только инструментами IFC, но и разработкой непосредственно формата IFC, Autodesk вступил на один месяц позже, в октябре 2020 года.

Из-за активности альянса ODA по раскрытию форматов RVT, RFA, начиная с 2020 года Autodesk стал активно возвращаться к управлению своим брошенным приемным сыном - форматом IFC, встраивая с версии Revit-2020 все те возможности экспорта в IFC формат, которые до этого (видимо, искусственно) тормозились или просто не развивались.

Возможно, Autodesk понимает, что закрытые форматы сегодня больше не смогут быть драйвером той бизнес-модели, которая успешно работала в 90-е годы, и поэтому сегодня Autodesk ставит для себя целью отказаться от форматов и продавать главным образом облачные решения. Одновременно с этим Autodesk спешит возглавить разработку открытых технологий в САПР-мире вслед за своими друзьями: IBM, Microsoft, Oracle, SAP, которые также заявили свои права на лидерство в open source-разработках (об этом подробнее в следующей части).

Но открытость open source плохо сочетается с той политикой, которую фирмы силиконовой долины проводили последние 30 лет.

В заключение

Времена настоящей конкуренции благодаря финансовым фондам уходят в историю. Отсутствие конкуренции сказывается на развитии технологий в нашей, и без того отсталой, отрасли строительства.

Старые корпорации, такие как Autodesk, Hexagon, Nemetschek Group, Trimble, построившие отличную инфраструктуру в 90-е, пытаются подстраиваться под быстрые изменения рынка. Разработка Legacy программ, например, Revit и Archicad, тормозится. Небольшие CAD и BIM стартапы или уничтожаются, или покупаются за пару миллионов (или как Plngrid, Aconex уже миллиардов) долларов - и уходят в бюрократическую систему дедлайнов и костылей; а к самостоятельной разработке новых идей и инструментов у корпораций, кажется, нет ни интереса, ни творческих способностей.

Кризис программного обеспечения в САПР и в других технологиях носит также системный и поколенческий характер. Проблема в том, что сейчас у руля BlackRock, Vanguard, Oracle, Autodesk и других компаний стоит поколение X или Baby-Boomers, которые ещё помнят деколонизацию доминионов и карибский кризис в 60е.

И, конечно, мы не можем не испытывать уважения к этому поколению (наших родителей), но очевидно, что технологии развиваются слишком быстро, чтобы отдавать управление новыми технологиями людям из BlackRock или Autodesk, которые родились в то время, когда человек ещё не изобрёл пульт от телевизора, а компьютеры были размером с дом.

В целом укрупнение капитала и монополизация рынка с дальнейшими построениями олигополий или прямых монополистов это естественный ход капитализма. Таким образом, капитализм на стероидах создал бюрократию управления или, другими словами, плановую экономику на мировом уровне.

Только если в Советском союзе при плановой экономике получаемая прибыль принадлежала всем членам общества, то в мировой плановой экономике прибыль получают только акционеры основных инвестиционных фондов.

Ни в коем случае нельзя обвинять в алчности структуры, построенные BlackRock и Vanguard: на их месте любая организация и возможно любой человек поступил бы точно так же. Мы можем быть благодарны им за то, что конкретно этот тупиковый путь выбрали именно они.

Успокаивает только тот факт, что могущество теневых банков и бюрократия в масштабах всей планеты - недавнее явление, которое наука и политика только начинают понимать. Это тренд всего лишь последних 20 лет и, благодаря истории, мы уже знаем, что плановая экономика нежизнеспособна в рамках одной страны, и такая модель ручного управления уж точно не имеет смысла в долгосрочной перспективе в масштабах всей планеты.

Нам помогают проблемы и кризисы. Большие корпорации, благодаря постоянной поддержке мамы BlackRock и папы Vanguard, не способны сами создать для себя трудные времена, которые могли бы помочь компаниями и технологиям выйти на новый уровень развития.

Тот уже не хитрый, о ком все говорят, что он хитер.

Александр Суворов

Логично, что BlackRock и Vanguard уйдут в историю со своей бюрократией и сложными закрытыми конструкциями, точно так же, как эта бюрократия и вертикальная система связей исчезла из плановой экономики Советского Союза.Для поражения BlackRock и Vanguard не хватает только сильного соперника, которым может стать сегодня децентрализированная экономика.

Благодаря прозрачности информации: журналистам, появлению камер и фотоаппаратов - в конце 20 века люди перестали убивать массово других людей. А в 21 веке, благодаря интернету и децентрализованным приложениям, на наших глазах при помощи open source кода создаётся новая децентрализованная экономика, за которой пытаются угнаться старые корпорации со своим другом BlackRock.

Эти многие западные компании и структуры в будущем ещё ждут своя перестройка и своя гласность.

Настоящий показатель вашего богатства это то, чего вы будете стоить, если потеряете все свои деньги.

Неизвестный автор

Остаётся надеяться на кризисы и чёрных лебедей, вроде COVID-19, благодаря которым специалисты со всего мира после 2021 года, возможно, перестанут торопиться переезжать в другие страны и искать своё место в этой, хоть и денежной, но очень забюрократизированной и устаревшей структуре. Такие новые уникальные специалисты, вроде Gabor Bojar, Mike Riddle, Samuel Geisberg, могут уже сегодня заниматься разработкой новых САПР-BIM технологий из любого доступного места.

Новый мир программного обеспечения и технологий будет намного прозрачней, способствуя тем самым увеличению количества разработчиков, созданию новых удалённых рабочих мест и открытию новых возможностей монетизации.

Про решения Оpen source, монетизацию про то почему IFC это не open source поговорим в следующей части.

Если Вам интересна тема openSource и BIM (продукты с открытым кодом в строительстве) подключайтесь к обсуждению в группе телеграмм - bimopensource: https://t.me/bimopensource

Буду благодарен за репост в социальных сетях и рад вашим комментариям, уточнениям и критике.

Схема связей BIM программ указанных в статье в хорошем качестве доступна по ссылке.

Предыдущие статьи по теме:

Сравнение технологий в строительстве и 5D проектирование в Азии и Европе: Казахстан, Австрия, Германия, Китай, Украина

Войны лоббистов и развитие BIM. Часть 4: Борьба CAD и BIM. Монополии и лоббисты в строительной отрасли