Тупиковый BLOB INSERT MySQL 8.0 InnoDB Cluster
При вставке файла большего размера в базу данных MySQL 8.0 с настроенной репликацией кластера InnoDB запрос попадает в тупик таблицы. Для файла меньшего размера 6 КБ работает INSERT. Проблема возникает при работе через маршрутизатор MySQL, а также при запуске INSERT через прямое соединение с хостом "R / W". Соответствующие лимиты транзакций вряд ли будут достигнуты. См. Настройки MySQL ниже.
Таблица
CREATE TABLE `onlineorder_attachments` (
`AttachmentGUID` varchar(36) NOT NULL,
`Filename` varchar(80) DEFAULT NULL,
`File` mediumblob,
PRIMARY KEY (`AttachmentGUID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
TestfilesM-02-012.jpg 4.813KB => Deadlock!M-05-055.jpg 6KB => Работает
Команда
INSERT INTO `onlineorder_attachments`
(`AttachmentGUID`,`Filename`,`File`)
VALUES
('00a2b54f-b0cf-4f3a-9bed-02dba853b505', 'M-02-012.jpg', LOAD_FILE('/var/lib/mysql-files/tmp/M-02-012.jpg'));
Вывод настроек MySQL
group_replication_communication_max_message_size 10485760
group_replication_components_stop_timeout 31536000
group_replication_compression_threshold 1000000
group_replication_group_seeds 10.29.169.13:33561
group_replication_local_address 10.29.169.12:33561
group_replication_member_expel_timeout 0
group_replication_message_cache_size 1073741824
group_replication_transaction_size_limit 150000000
slave_max_allowed_packet 1073741824
slave_net_timeout 60
Статус установки InnoDB Cluster:
{
"clusterName": "AppCluster",
"defaultReplicaSet": {
"GRProtocolVersion": "8.0.16",
"groupName": "3afe628e-bdd1-11e9-8bbe-ac1f6bd3521c",
"name": "default",
"primary": "10.29.169.12:3356",
"ssl": "REQUIRED",
"status": "OK_NO_TOLERANCE",
"statusText": "Cluster is NOT tolerant to any failures.",
"topology": {
"10.29.169.12:3356": {
"address": "10.29.169.12:3356",
"fenceSysVars": [],
"memberId": "a715990f-bdc2-11e9-8ec6-ac1f6bd3521c",
"memberRole": "PRIMARY",
"memberState": "ONLINE",
"mode": "R/W",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.18"
},
"10.29.169.13:3356": {
"address": "10.29.169.13:3356",
"fenceSysVars": [
"read_only",
"super_read_only"
],
"memberId": "74c57dda-bdbb-11e9-94f8-ac1f6bd350ce",
"memberRole": "SECONDARY",
"memberState": "ONLINE",
"mode": "R/O",
"readReplicas": {},
"replicationLag": null,
"role": "HA",
"status": "ONLINE",
"version": "8.0.18"
}
},
"topologyMode": "Single-Primary"
},
"groupInformationSourceMember": "10.29.169.12:3356"
}
Вывод оболочки MySQL при ошибке
mysql> show open tables where in_use>0;
+----------+-------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------------------------+--------+-------------+
| appws30 | onlineorder_attachments | 1 | 0 |
mysql> show processlist;
+------+-----------------------------+---------------------------------+----------+---------+------+--------------------------------------------------------+----------------------- -------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------------------+---------------------------------+----------+---------+------+--------------------------------------------------------+----------------------- -------------------------------------------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 472 | Waiting on empty queue | NULL |
| 9 | system user | | NULL | Connect | 472 | waiting for handler commit | Group replication appl ier module |
| 14 | system user | | NULL | Query | 472 | Slave has read all relay log; waiting for more updates | NULL |
| 344 | remoteuser | 10.29.169.12:56834 | NULL | Sleep | 351 | | NULL |
| 350 | remoteuser | 10.29.169.12:56842 | NULL | Sleep | 388 | | NULL
|
| 497 | remoteuser | 10.29.169.12:56996 | NULL | Sleep | 351 | | NULL |
| 615 | root | localhost | appws30 | Query | 255 | waiting for handler commit | INSERT INTO `onlineord er_attachments` (`AttachmentGUID`,`Filename`,`File`) VALUES ('44a2b54f-b0cf-4 |
| |
+------+-----------------------------+---------------------------------+----------+---------+------+--------------------------------------------------------+----------------------- -------------------------------------------------------------------------------+
Тесты:
Для нормальной установки MySQL 8.0 без конфигурации InnoDB Cluster, INSERT работает.
For MySQL 8.0 with InnoDB Cluster configuration but only one host in the cluster, the INSERT also works.
Only MySQL 8.0 with InnoDB Cluster Configuration and more hosts INSERT runs in deadlock.
Have we forgotten a setting or is this a bug?
1 ответ
Между серверами Mysql была локальная сеть со скоростью 10 Мбит / с. В результате репликация базы данных MySQL для больших файлов, казалось, истекала по тайм-ауту, что приводило к откату транзакции, что, в свою очередь, приводило к блокировке таблиц.
При LAN со скоростью 100 Мбит / с ошибка не возникает после нескольких тестов. Только это помогло. Не помогло и увеличение slave_net_timeout.
На мой взгляд, не имеет смысла, почему при асинхронной репликации тайм-аут может привести к блокировке таблицы на главном сервере.
ОБНОВЛЕНИЕ: при LAN 100 Мбит / с ошибка возникает для файлов размером более 20 МБ. Поскольку мы в любом случае не хотим хранить в базе данных файлы размером более 16 МБ, мы просто установили максимальный размер пакета SQL равным 16 МБ. Это приводит к тому, что код ошибки 1301 "... больше, чем max_allowed_packet ..." выводится непосредственно в INSERT большего файла, тем самым предотвращая блокировки таблицы.
max_allowed_packet = 16777216
ОБНОВЛЕНИЕ 2:
cluster.setOption("expelTimeout", 3600);
и установка group_replication_transaction_size_limit в /etc/mysql/mysql.conf.d/mysqld.cnf частично помогает.
group_replication_transaction_size_limit = 0 #0=Maximum=2147483647=2GB
ЗАКЛЮЧИТЕЛЬНОЕ РЕШЕНИЕ. Кажется, что репликация MySQL обычно вызывает проблемы, когда в таблицах хранятся файлы большего размера. Поэтому мы изменили наше приложение так, что все файлы сохраняются в файловой системе, а таблица MySQL хранит только ключ файла и другую информацию о файле. Мы создали собственное решение для репликации файлов на несколько машин.