CC BY
245
УДК: 338.2+004.2
ББК: 65
Голиков О.И., Панкратов И.А. ИССЛЕДОВАНИЕ СПОСОБОВ ПОВЫШЕНИЯ ЭФФЕКТИВНОСТИ ОБРАБОТКИ ДАННЫХ В РЕЛЯЦИОННЫХ БД НА ПРИМЕРЕ СУБД MYSQL
Golikov O.I., Pankratov I.A.
A STUDY OF WAYS TO IMPROVE THE EFFICIENCY OF DATA PROCESSING IN RELATIONAL DATABASES ON MYSQL DATABASE EXAMPLE
Ключевые слова: база данных, SQL, СУБД MySQL, реляционные СУБД, партицирование базы данных, большие данные.
Keywords: database, SQL, MySQL, relational database, database partitioning, big data.
Аннотация: проведен анализ целесообразности использования партицирования SQL баз данных в контексте эффективной обработки больших данных. Рассмотрены методы увеличения производительности реляционных баз данных. Создан экспериментальный модуль для заполнения баз данных случайной информацией и чтения данных из неё с возможностью измерения времени выполнения запросов. Проведены эксперименты по установлению величины эффективности различных видов партицирования и исследованию влияния степени разбиения таблицы на скорость выполнения связанных с ней запросов.
Abstrakt: this article presents practicability analysis of using SQL database partitioning in effective Big Data processing and examines methods of increasing the performance of relational databases. It resulted in developing of a software module for filling the database with random data and performance analyze. Conducted experiments for partition methods efficiency measurment and data table division influence on data request procession speed.
Введение
В настоящее время самыми распространенными системами управления базами данных (далее СУБД) являются реляционные. Доля мирового рынка реляционных баз данных составляет порядка 80% от всего рынка [1]. Результаты опроса T-Systems показали, что 30% опрошенных компаний выбрали платформы, которые реализуют подход «в памяти» (англ. inmemory), базирующиеся на технологии SQL (в том числе платформы компаний SAP, HANA, Oracle) при работе с большими данными (англ. big data) - понятие, характеризующееся большими объемами данных, децентрализованным способом их хранения, низкой структурированностью и взаимосвязью. Тогда как вторыми по популярности стали NoSQL платформы (18% пользователей). По состоянию на 2014 год, компания IDC оценивает рынок больших данных в России в 340 млн. долл. США, где из них 100 млн. долл. США - решения SAP, а примерно 240 млн. долл. США - аналогичные решения Oracle, IBM, SAS, Microsoft и др. Темп роста российского рынка больших данных составляет не менее, чем 50% в год [2]. В то же время использование реляционных баз данных (MySQL, PostgreSQL, Oracle Database и других) для решения задач хранения больших данных становится проблематичным. Главным преимуществом реляционных баз дан-
ных являются особенности архитектуры, направленные на поддержание целостности данных. Данная особенность достигается за счет хранения связей между элементами данных. Однако хранение и проверка этих связей требует дополнительных временных ресурсов, что при значительных объемах и слабой структурированности данных в результате делает использование реляционных баз данных затруднительным в системах реального времени [3].
В этой статье рассмотрены способы повышения производительности баз данных, касающихся технологии партицирования (англ. Partitioning) - разделение хранимых объектов баз данных на отдельные логические части с раздельными параметрами физического хранения. В качестве объекта исследований в данной статье используется MySQL база данных, заполненная случайным образом сгенерированными записями. Произведен анализ производительности созданной базы данных для различных параметров и способов партицирования при помощи созданного модуля, выполняющего запросы на чтение из базы данных и способного измерять время выполнения запросов.
Целью исследования является увеличение доступности данных для чтения и изменения в системах на основе реляционных баз дан-
ных за счет применения технологии партициро-вания.
Для достижения указанной цели были решены следующие задачи:
1. Изучены особенности архитектуры и способов хранения информации в реляционных базах данных.
2. Рассмотрены различные способы и технологии партицирования, проведено их сравнение.
3. Разработаны алгоритмы формирования случайных тестовых данных для заполнения базы данных MySQL и выполнения запросов к базе данных MySQL, включающий сбор аналитической информации.
4. Создан программный блок заполнения тестируемой базы данных случайными данными, а также программный блок выполнения и обработки запросов к выбранной базе данных с возможностью учета времени выполнения запросов.
5. На основе авторских исследований с учетом найденных закономерностей и недостатков выявлены, систематизированы и доказаны преимущества технологий партицирования в контексте повышения производительности реляционных баз данных.
Исследование возможности увеличения производительности реляционных систем управления базами данных (англ. relational database management system) проводилось с использованием СУБД MySQL. Данный выбор обусловлен открытостью исходного кода базы данных и её лицензированием на условиях GPL (англ. General Public License), наличием реализаций для большинства платформ (включающих Windows2000/XP/Vista/7/8/10, Linux, FreeBSD, Mac OS X и др.) и драйверов для множества языков программирования (включающих Delphi, C, C++, Java, Perl, PHP, платформу .NET и др.), активную поддержку, осуществляемую компанией Oracle, а также гибкостью и простотой использования платформы в рамках поставленной задачи [4]. Обладая средствами поддержки SSL-транзацкий, MySQL становится прекрасным инструментом для ведения малого и среднего бизнеса. [5] Гибкость СУБД MySQL обеспечивается поддержкой большого количества типов таблиц: имеется возможность выбора как таблицы типа MyISAM, поддерживающие полнотекстовый поиск, так и таблицы InnoDB, поддерживающие транзакции на уровне отдельных записей. Более того, СУБД MySQL поставляется со специальным типом таблиц EXAMPLE, демонстрирующим принципы создания новых типов таблиц. Благодаря открытой архитектуре и GPL-лицензированию, в СУБД MySQL постоянно появляются новые типы таблиц [6]. По данным ValueLabs, MySQL
является самой используемой реляционной базой данных с открытым исходным кодом [7].
Исследования производительности систем с большими объемами данных (содержащих более 500 млн. записей), представленные Oracle, показывают, что для баз MySQL, выполнение команды «INSERT» для записи более 350 тысяч строк занимает примерно 6 минут 21 секунду, а выполнение запроса на чтение с помощью команды «SELECT» для баз, содержащих более 2 млн записей, занимает более 4 мин 10 сек [8]. Данные замеры показывают, что использование баз данных на основе SQL, содержащих большие объемы данных и не использующих технологии партицирования, затруднительно.
Тип таблицы играет большую роль в определении итоговой скорости работы базы данных. MySQL поддерживает два различных типа таблиц: транзакционные (InnoDB и BDB) и без поддержки транзакций (HEAP, ISAM, MERGE и MylSAM). Преимущества транзакци-онных таблиц (Transaction-safe tables, TST):
- надежность. Если произойдет сбой в работе MySQL или возникнут проблемы с оборудованием, потерянные данные будут подлежать восстановлению - либо методом автоматического восстановления, либо при помощи резервной копии и журнала транзакций.
возможность сочетать несколько операторов и принимать эти операторы одной командой COMMIT.
возможность запуска команды ROLLBACK, отменяющей внесенные изменения (если работа не производится в режиме автоматической фиксации).
при возникновении сбоя во время обновления, все изменения будут восстановлены (в нетранзакционных таблицах все внесенные изменения не могут быть отменены).
преимущества таблиц без безопасных транзакций (non-transaction-safe tables, NTST):
увеличение скорости выполнения за-
просов за счет отсутствия выполнения дополни-
тельных транзакций.
- таблицы этого типа занимают меньше дискового пространства по причине отсутствия хранения дополнительных транзакций.
- для обновления таблицы используется меньше оперативной и дисковой памяти [9].
В данной статье авторами рассмотрено только горизонтальное парцитирование (т.е. разделение таблиц только по записям, без разбиения таблиц по столбцам), поскольку особенности архитектуры базы данных MySQL не позволяют напрямую проводить вертикальное секционирование. Горизонтальное парцитирова-ние, применительно к СУБД MySQL, подразде-
ляется на следующие виды:
1. По диапазону значений. Данный вид позволяет явно задать границы разделения таблицы по значению числовых либо логических значений столбцов.
2. По точному списку значений. Применяется в случае, когда требуется выделение групп единичных записей в отдельные партиции (подтаблицы).
3. По хэш-функции. В этом случае пользователь не указывает явно границы разбиения. При использовании этого вида указывается только количество партиций, на которые следует разбить таблицу, и столбец, от которого берется хэш-функция. На основании полученной хэш-функции СУБД сама производит разбиение.
4. По ключу. Данный вид практически аналогичен способу разбиения по хэш-функции с тем отличием, что разбиение всегда происходит по ключевому столбцу.
В рамках проведенного исследования авторами была разработана экспериментальная система, включающая в себя модуль заполнения базы данных по случайному принципу с возможностью задания вида используемого секционирования, а также модуль считывания информации из базы данных, осуществляющий учет времени выполнения запроса [10].
Индексация ключевых полей также является одним из главных способов повышения скорости исполнения запросов. Если в базе данных содержится большое количество связанных таблиц, то индексация связанных полей в таблицах разительно повысит скорость исполнения запросов по
данным полям, а также повысит общую скорость исполнения запросов за счет ускоренного поиска по индексированным полям [11].
Принципы работы разработанных авторами программных модулей далее описаны при помощи диаграмм поведения UML (United Modeling Language) - языка графического описания для объектного моделирования в области разработки программного обеспечения, моделирования бизнес-процессов, системного проектирования и отображения организационных структур [12].
Поведенческая реализация разработанного модуля заполнения базы данных включает в себя два потока, один из которых получает на вход количество необходимых записей, второй получает сгенерированные записи для помещения в СУБД. Для простоты и экономии занимаемого дискового пространства, запись состоит из трех генерируемых полей. Фамилия и имя выбираются случайным образом при помощи обращения к специально созданным коллекциям, состоящим из идентификаторов и ответ-ствующим им имен, либо фамилий. Дата рождения выбирается случайным образом из промежутка 01.01.1970-01.01.2016 [13]. Поток оправки в БД получает сформированную запись, а затем создает запрос на помещение записи в базу данных, предварительно проверяя наличие таблицы. При отсутствии таблицы она автоматически создается с заложенными перед запуском модуля параметрами партицирования.
Диаграмма поведения разработанного модуля заполнения базы данных представлена на рисунке 1.
Рисунок 1 - Диаграмма поведения модуля заполнения базы данных
Вестник Волжского университета имени В.Н. Татищева № 2, том 2, 2016
Разработанный авторами программный модуль считывания и замеров принимает на вход идентификаторы таблиц и запрос в СУБД MySQL, время ответа на который будет замеряться в течение теста для каждой их созданных тестовых таблиц. Модуль выполнен в один поток, так как
Авторами статьи проводился эксперимент для нескольких объемов таблиц: 100 млн., 50 млн., 20 млн., 10 млн. Для каждого из объемов были созданы по 4 заполненные случайно сгенерированными данными таблицы, каждая для одного из трех видов секционирования: по диапазону, по хэш-функции и по ключу, а также одна таблица без использования партицирова-ния. Для вида разделения «по точному списку значений» измерений не проводилось по причине малых отличий от типа «по диапазону». Для вида «по диапазону» в качестве диапазонов
В качестве основной платформы была использована машина, предоставляемая в рамках облачных услуг корпорацией «Microsoft» под
для точного проведения замера важно, чтобы СУБД в каждый момент времени выполняла максимум один запрос.
Диаграмма поведения разработанного модуля считывания и замеров представлена на рисунке 2.
использовались года рождения сгенерированных записей - всего 46 партиций для каждого из годов с 1970 по 2016. Для вида «по хэш-функции» хэшированным столбцом указан год рождения. Эксперимент проводился на основе запроса «SELECT» на полный проход по всей базе данных. Для каждого из объемов проводилось три испытания, на графике представлен усредненный вариант трех испытаний.
Авторские эксперименты проводились на системе со следующими характеристиками (таблица 1):
брендом «Azure».
Результаты первого авторского эксперимента представлены на рисунке 3:
Модуль считывания и замеров
Получение параметров запроса
Запуск таймера
Запрос к неопрошенной таблице
остановка таймера
Вывод ответа и значения таймера
Рисунок 2 - Диаграмма поведения модуля считывания и замеров
да
нет
да
Таблица 1 - Конфигурация тестовой системы, использованная в авторских экспериментах
Наименование операционной системы Windows Server 2012 R2
Количество логических процессоров 4 CPU
Объем ОЗУ 7GB
Объем и тип энергонезависимой памяти 80 GB HDD
100 млн
50 млн
20 млн
10 млн
7534
по ключу по хэшу
по точному списку Без партицирования
2000
4000
6000
8000
Рисунок 3 - Результаты первого эксперимента по подсчету времени исполнения команды
«SELECT»
Второй эксперимент касается исследования зависимости скорости исполнения команды «SELECT» от частоты разбиения таблицы и итогового количества результирующих подтаб-лиц - партиций. Эксперимент проводился на заполненной с помощью разработанного модуля случайными данными таблице, содержащей 50 миллионов включений. Данную таблицу последовательно разбивали на количество близких по
объему партиций, равное 1 (отсутствие разбиения), 5, 10, 20, 50, 100, 500, 1000 единиц соответственно. Тип использованных таблиц - My-ISAM. Для каждого из разбиений проводилось три последовательных измерения. В результатах эксперимента указано среднее значение измерений для каждого способа разбиений.
Результаты измерений представлены на следующем изображении (рисунок 4).
2500
2000
1500
1000
500
2015
1402
1 ТС~7
710
891 916_ Время
выполнения запроса, сек.
1— —I— —|— —|— —|— —|
1 5 10 20 50 100 500 1000
Рисунок 4 - Исследование зависимости скорости исполнения запроса к СУБД от частоты разбиения таблицы
Проанализировав полученные данные, можно сделать вывод, что для партицирования существует некий предел эффективности. После достижения определенного количества партиций повышение скорости отклика останавлива-
ется, а затем эффективность разбиения становится отрицательной.
В рамках исследования производительности системы MySQL, проведенного Томасом Келлером (англ. Thomas Keller), для базы дан-
0
0
ных, содержащей 8 миллионов записей, был проведен сравнительный анализ скорости выполнения запросов «SELECT» на извлечение всех данных из разных видов таблиц при использовании различных способов парцитирова-
ния в сочетании с поиском по индексу. В рамках выполнения данной работы результаты сравнительного анализа были обобщены в таблице 2 [14].
Таблица 2 - Результаты анализа времени выполнения запросов
Конфигурация таблицы Время, затраченное на запрос
MylSAM без парцитирования 6.4 сек.
MylSAM без парцитирования (поиск по индексу) 1.2 сек.
MylSAM с парцитированием 0.7 сек.
MylSAM с парцитированием (поиск по индексу) 1.3 сек.
Archive без парцитирования 10.2 сек.
Archive с парцитированием 1.1 сек.
Данный эксперимент проводился на системе со следующими аппаратными характеристиками (таблица 3).
Таблица 3 - Тестовая конфигурация, использованная при сравнении различных типов таблиц
Наименование операционной системы Linux 2.6.34
Версия СУБД MySQL community server 5.1.46
Тип логического процессора Intel Pentium D CPU with 3.2GHz
Объем ОЗУ 2GB RAM
Выводы
Проведенное исследование позволяет заявлять о целесообразности использования технологии секционирования в контексте повышения эффективности взаимодействия с реляционными СУБД. Целесообразность подтверждается тем, что использование секционирования
таблиц приводит к качественному уменьшению времени выполнения запросов к реляционным СУБД, что подтверждается проведенными авторскими испытаниями на примере СУБД MySQL, а также приведенными аналогичными испытаниями других исследователей.
БИБЛИОГРАФИЧЕСКИЙ СПИСОК
1. Анализ рынка СУБД. - URL: http://www.cnews.ru/articles/tri_tolstyaka_subd_ okkupirova-
li_rynok (дата обращения: 05.06.2016).
2. Васильчук, О.И., Васильева, Е.А., Волкова, А.А., Гуськова, Т.Н., Крупина, И.Г., Медведева, Е.В., Насакина, Л.А., Парамонова, Л.А., Романеева, Е.В., Соколова, И.А., Юрина, В.С. Учетно-аналитические подходы к управлению стоимостью бизнеса. - Тольятти: Поволжский государственный университет сервиса, 2014.
3. Федоров, А.Р., Васильчук, К.С., Дорофеев, А.В. Создание масштабируемых средств для решения задач анализа больших объемов данных на основе системы управления базы данных MongoDB // Вестник Поволжского государственного технологического университета. Серия: Радиотехнические и инфокоммуникационные системы. - 2016. - №29(1). - С. 55-63.
4. Официальный сайт MySQL. - URL: http://www.mysql.com/why-mysql/ (дата обращения: 05.06.2016).
5. Васильчук О.И. Средства компенсации угроз и аудита безопасности корпоративной информационной системы // Вестник Поволжского государственного университета сервиса. - 2013. - №4. -127-131.
6. MySQL Wiki URL: https://ru.wikipedia.org/wiki/MySQL (дата обращения: 05.06.2016)
7. Database partitioning with MySQL URL: https://dzone.com/refcardz/database-partitioning (дата обращения: 05.06.2016).
8. Официальный сайт MySQL. - URL: http://www.mysql.it/why-mysql/benchmarks/ (дата обращения: 05.06.2016).
9. Справочное руководство по MySQL. - URL: http://www.mysql.ru/docs/man/Table_types.html / (дата обращения: 05.06.2016).
10. Пьянков, О.В. Апробация алгоритмов кластерного анализа информационно-аналитических систем // Вестник Поволжского государственного технологического университета. Сер.: Радиотехнические и инфокоммуникационные системы. - 2015. - № 1 (25). - С. 41-49.
11. Даденков, С.А., Кон, Е.Л., Южаков, А.А. Математические модели и количественная оценка времени задержки приложения узла в сенсорных сетях со случайным доступом // Вестник Поволжского государственного технологического университета. Сер.: Радиотехнические и инфокоммуникационные системы. - 2015. - № 3 (27). - С. 38-53.
12. Object Management Group Unified Modeling Language (OMG UML) Information technology: ISO/IEC 19505-1:2012.
13. Гагарина, Л.Г. и др. Метод ускоренной разработки прототипов программного обеспечения // Оборонный комплекс - научно-техническомупрогрессу России. - 2013. - № 1. - С. 3-5.
14. MySQL partitioning benchmark URL: https://www.thomaskeller.biz/blog/2010/08/04/mysql-partitioning/ (дата обращения: 05.06.2016).
15. Горбачевская, Е.Н., Краснов, С.С. Анализ структуры системы информационной безопасности предприятия с централизованной авторизацией пользователей // Вестник Волжского университета имени В.Н. Татищева. - 2013. - № 4 (22). - С. 63-74.
16. Гринцевич, Э.В., Леонидов, А.В., Харунов, В.В. Обобщенная методика разработки проектов автоматизации технологических процессов как основа современной организации производства // Вестник Волжского университета им. В.Н. Татищева. - 2015. - № 1 (23). - С. 17-23.
17. Трубачева, С.И., Горбачевская, Е.Н. Основные подходы разработки модели системного управления техническим сбором и обработкой данных в корпоративных информационных системах // Вестник Волжского университета имени В.Н. Татищева. - 2012. - № 4 (20). - С. 109-117.
