Каждый день, вероятно, проводится работа по повышению производительности механизма хранения InnoDB и устранению узких мест и проблем масштабируемости. Следовательно, был еще один, который я хотел бы выделить:
Проблемы масштабируемости из-за таблиц без первичных ключей
Эта проблема с масштабируемостью вызвана использованием таблиц без первичных ключей. Эта проблема обычно проявляется как конфликт на мьютексе dict_sys InnoDB. Теперь мьютекс dict_sys контролирует доступ к словарю данных. Этот мьютекс используется в разных местах. Я упомяну только некоторые из них:
- Во время таких операций, как открытие и закрытие ручек стола или
- При доступе к таблицам I_S или
- Во время отмены недавно вставленной строки или
- Во время других операций модификации словаря данных, таких как CREATE TABLE или
- В подсистеме «Постоянная статистика», между прочим.
Конечно, этот список не является исчерпывающим, но должен дать вам хорошее представление о том, насколько интенсивно он используется
Но дело в том, что когда вы в основном отлаживаете конфликт, связанный со структурой элемента управления словаря данных, вы начинаете смотреть на то, что напрямую связано с модификациями словаря данных. Вы ищете выполнение CREATE TABLE, DROP TABLE, TRUNCATE TABLE и т. Д. Но что, если ничего из этого на самом деле не вызывает конфликт на мьютексе dict_sys? Знаете ли вы, что при генерации значений «row-id», для таблиц без явных первичных ключей или без необнуляемых уникальных ключей, получается мьютекс dict_sys. Таким образом, INSERT для таблиц с неявными первичными ключами является предметом спора для всей системы InnoDB.
Давайте также взглянем на соответствующий исходный код.
Во-первых, ниже находится функция, которая выполняет выделение идентификатора строки, который определен в хранилище файлов / innobase / row / row0ins.cc
3060 /***********************************************************//**
3061 Allocates a row id for row and inits the node->index field. */
3062 UNIV_INLINE
3063 void
3064 row_ins_alloc_row_id_step(
3065 /*======================*/
3066 ins_node_t* node) /*!< in: row insert node */ 3067 { 3068 row_id_t row_id; 3069 3070 ut_ad(node->state == INS_NODE_ALLOC_ROW_ID);
3071
3072 if (dict_index_is_unique(dict_table_get_first_index(node->table))) {
3073
3074 /* No row id is stored if the clustered index is unique */
3075
3076 return;
3077 }
3078
3079 /* Fill in row id value to row */
3080
3081 row_id = dict_sys_get_new_row_id();
3082
3083 dict_sys_write_row_id(node->row_id_buf, row_id);
3084 }
Во-вторых, ниже находится функция, которая фактически генерирует идентификатор строки, который определен в хранилище файлов / innobase / include / dict0boot.ic
26 /**********************************************************************//**
27 Returns a new row id.
28 @return the new id */
29 UNIV_INLINE
30 row_id_t
31 dict_sys_get_new_row_id(void)
32 /*=========================*/
33 {
34 row_id_t id;
35
36 mutex_enter(&(dict_sys->mutex));
37
38 id = dict_sys->row_id;
39
40 if (0 == (id % DICT_HDR_ROW_ID_WRITE_MARGIN)) {
41
42 dict_hdr_flush_row_id();
43 }
44
45 dict_sys->row_id++;
46
47 mutex_exit(&(dict_sys->mutex));
48
49 return(id);
50 }
Наконец, я хотел бы поделиться результатами нескольких тестов, которые я провел, чтобы показать вам, как это влияет на производительность.
Сравнительный анализ отсутствия первичных ключей
Прежде всего, позвольте мне поделиться информацией о хосте, который использовался в тестах. Я также поделюсь версией MySQL и используемой конфигурацией InnoDB.
аппаратные средства
Хост был «hi1.4xlarge» экземпляром Amazon EC2. Экземпляр поставляется с 16 виртуальными ЦП и 60,5 ГБ памяти. Хранилище экземпляров состоит из 2 × 1024 томов хранения на SSD-накопителе, и экземпляр подключен к 10-гигабитной сети Ethernet. Так что производительность IO очень приличная. Я создал массив RAID 0 из двух томов хранилища экземпляров и создал файловую систему XFS на результирующем программном томе RAID 0. Эта конфигурация позволит нам получить максимально возможную производительность ввода-вывода из экземпляра.
MySQL
Используемая версия MySQL была 5.5.34 MySQL Community Server, и конфигурация InnoDB выглядела следующим образом:
innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 512M innodb-flush-log-at-trx-commit = 2 innodb-file-per-table = 1 innodb-buffer-pool-size = 42G innodb-buffer-pool-instances = 8 innodb-io-capacity = 10000 innodb_adaptive_hash_index = 1
Я провел два разных типа тестов, и оба они были сделаны с помощью sysbench.
Первый связан с тестированием производительности однострочных INSERT для таблиц с явными первичными ключами и без них. Это то, что я хотел бы показать первым.
Однорядные вставки
Таблицы были сгенерированы следующим образом для теста с таблицами с первичными ключами:
sysbench --test=/root/sysbench/sysbench/tests/db/insert.lua --oltp-tables-count=64 --oltp-table-size=1000000 --mysql-table-engine=innodb --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=test prepare
Это привело к созданию следующей таблицы:
CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB
При этом таблицы без первичных ключей генерировались следующим образом:
sysbench --test=/root/sysbench/sysbench/tests/db/insert.lua --oltp-tables-count=64 --oltp-table-size=1000000 --oltp-secondary --mysql-table-engine=innodb --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=test prepare
В результате были созданы таблицы со следующей структурой:
CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', KEY `xid` (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB
Фактический тест для таблицы с первичными ключами был выполнен следующим образом:
sysbench --test=/root/sysbench/sysbench/tests/db/insert.lua --oltp-tables-count=64 --oltp-table-size=1000000 --oltp-dist-type=uniform --mysql-table-engine=innodb --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=test --max-time=300 --num-threads=16 --max-requests=0 --report-interval=1 run
Фактический тест для таблицы без первичных ключей выполнялся следующим образом:
sysbench --test=/root/sysbench/sysbench/tests/db/insert.lua --oltp-tables-count=64 --oltp-table-size=1000000 --oltp-secondary --oltp-dist-type=uniform --mysql-table-engine=innodb --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=test --max-time=300 --num-threads=16 --max-requests=0 --report-interval=1 run
Обратите внимание, что тесты выполнялись с тремя вариациями количества параллельных потоков, используемых sysbench: 16, 32 и 64.
Ниже показано, как выглядят графики для каждого из этих тестов.
Некоторые ключевые моменты, которые следует отметить из графиков, заключаются в том, что пропускная способность INSERT для таблиц без явных первичных ключей никогда не превышает 87% пропускной способности INSERT для таблиц с определенными первичными ключами. Кроме того, когда мы увеличиваем параллелизм, начинают появляться нисходящие пики. Это становится более очевидным, когда мы переходим к параллельности из 64 потоков. Это ожидается, потому что конкуренция должна увеличиваться по мере увеличения параллелизма операций, которые конкурируют с мьютексом dict_sys.
Теперь давайте посмотрим, как это влияет на производительность массовой загрузки.
Навальные грузы
Основная загрузка таблиц с первичными ключами выполнялась следующим образом:
sysbench --test=/root/sysbench/sysbench/tests/db/parallel_prepare.lua --oltp-tables-count=64 --oltp-table-size=1000000 --mysql-table-engine=innodb --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=test --num-threads=16 run
При этом массовые загрузки в таблицы без первичных ключей выполнялись следующим образом:
sysbench --test=/root/sysbench/sysbench/tests/db/parallel_prepare.lua --oltp-tables-count=64 --oltp-table-size=1000000 --oltp-secondary --mysql-table-engine=innodb --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=test --num-threads=16 run
Обратите внимание, что тесты снова выполнялись с тремя вариациями в количестве одновременных потоков, используемых sysbench: 16, 32 и 64.
Ниже показано, как изображение изображается на графике.
Здесь снова вы можете увидеть, как увеличивается время массовой загрузки при увеличении количества одновременных потоков. Это против указывает на увеличение конкуренции за мьютекс dict_sys. С 16 потоками время массовой загрузки для таблиц без первичных ключей на 107% больше, чем время массовой загрузки для таблиц с первичными ключами. Это увеличивает до 116% с 32 потоками и, наконец, 124% с 64 потоками.
Вывод
Таблицы без первичных ключей вызывают широкий диапазон разногласий, поскольку они полагаются на получение мьютекса dict_sys для генерации значений идентификатора строки. Этот мьютекс используется в критических местах в InnoDB. Следовательно, влияние большого количества INSERT на таблицы без первичных ключей не только изолировано от этой таблицы, но и очень широко видно. Я неоднократно видел таблицы без использования первичных ключей во многих различных сценариях, которые включают в себя простые INSERT для этих таблиц, а также многострочные INSERT в результате, например, INSERT… SELECT для таблицы, которая находится в процессе временно создан. Совет всегда должен иметь первичные ключи в ваших таблицах. Надеюсь, мне удалось подчеркнуть истинное влияние отсутствия первичных ключей.