Блог

У меня есть сервер с такими параметрами:

 OS: Ubuntu 18.04.4 LTS x86_64
Kernel: 4.15.0-112-generic
CPU: Intel Xeon Silver 4214 (48 Cores) @ 2.201GHz
Memory: 96Gb
SSD SAMSUNG MZQLB960HAJR-00007 894.3Gb x 2
  

С установленным 5.5.5-10.4.12-MariaDB-1:10.4.12 maria~bionic . У меня есть написанный на PHP проект с довольно большим количеством запросов к БД (~ 400-500 выборок в секунду ~ 200-300 обновлений в секунду). Проблема в том, что база данных работает довольно медленно при большой активности пользователей, но нормально в любое другое время. Мой my.cfg файл находится ниже:

 # this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
skip-name-resolve
max_connections = 1000000
bind-address = 0.0.0.0

#
# * Fine Tuning
#
key_buffer_size = 96M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 32

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP

lock_wait_timeout = 10
interactive_timeout = 10
wait_timeout = 3

#
# * Query Cache Configuration
#
query_cache_type = 0
query_cache_limit = 0
query_cache_size = 0
open-files-limit = 320000
thread_cache_size = 64

#
# * Logging and Replication
#
log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M

#
# * InnoDB
#

innodb_buffer_pool_size = 10000M
innodb_buffer_pool_instances = 128
max_heap_table_size = 1000M
tmp_table_size = 1000M
sort_buffer_size = 96M
innodb_sort_buffer_size = 96M
read_buffer_size = 96M

#
# * Character sets
#
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci


# this is only for embedded server
[embedded]

[mariadb]
max_statement_time = 15
thread_handling = pool-of-threads
thread_pool_size = 48
  

Недавно я изменил thread_handling = pool-of-threads и wait_timeout = 3 : это дало некоторый прирост производительности, но не решило проблему в целом. Я буду благодарен за любые идеи, как ускорить это или устранить некоторые «узкие места».

Обновить:

Используйте InnoDB, а не MyISAM

Это уже так.

Включите slowlog, чтобы найти самые медленные запросы.

Ну, все мои SELECTs вызывают только одну таблицу (~ 500 тыс. записей, без объединений и / или подзапросов), и большинство из них имеют ОГРАНИЧЕНИЕ 1, поэтому не такой большой объем данных.

Давайте посмотрим, как показать ТАБЛИЦУ СОЗДАНИЯ.

Хорошо, вот SHOW CREATE TABLE для этой основной таблицы:

 CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `order_created_datetime` timestamp NULL DEFAULT current_timestamp(),
  `order_updated_datetime` timestamp NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(),
  `order_last_perform_datetime` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `order_category_id` tinyint(4) DEFAULT NULL,
  `order_url` text CHARACTER SET utf8 DEFAULT NULL,
  `order_social_shortcode` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
  `order_social_id` varchar(128) DEFAULT NULL,
  `order_title` text DEFAULT NULL,
  `order_preview_pic` text DEFAULT NULL,
  `order_custom_data` text DEFAULT NULL,
  `order_price_usd` decimal(13,6) DEFAULT 0.000000,
  `order_daily_limit` int(11) DEFAULT 0,
  `order_hourly_limit` int(11) DEFAULT 0,
  `order_minute_limit` int(11) DEFAULT 0,
  `order_overall_limit` int(11) DEFAULT 0,
  `order_initial_social_counter` int(11) DEFAULT 0,
  `order_daily_counter` int(11) DEFAULT 0,
  `order_hourly_counter` int(11) DEFAULT 0,
  `order_minute_counter` int(11) DEFAULT 0,
  `order_overall_counter` int(11) DEFAULT 0,
  `order_allowed_countries` text CHARACTER SET utf8 DEFAULT NULL,
  `order_forbidden_countries` text CHARACTER SET utf8 DEFAULT NULL,
  `order_api` tinyint(4) DEFAULT 0,
  `order_reported` tinyint(4) DEFAULT 0,
  `order_active` tinyint(4) DEFAULT 1,
  `order_deleted` tinyint(4) DEFAULT 0,
  `order_deleted_datetime` timestamp NULL DEFAULT '0000-00-00 00:00:00',
  `order_deleted_user_id` int(11) DEFAULT NULL,
  `users_log_actions` longtext CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `users_log_hidden` longtext CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `index_user_id` (`user_id`) USING BTREE,
  KEY `index_order_category_id` (`order_category_id`) USING BTREE,
  KEY `index_order_deleted` (`order_deleted`) USING BTREE,
  KEY `index_order_active` (`order_active`) USING BTREE,
  KEY `index_order_social_id` (`order_social_id`) USING BTREE,
  KEY `index_users_log_actions` (`users_log_actions`(1024)) USING BTREE,
  KEY `index_users_log_hidden` (`users_log_hidden`(1024)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=461811 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC
  

innodb_buffer_pool_size может быть слишком маленьким — Сколько у вас данных? Сколько оперативной памяти доступно для MariaDB?

Ну, MariaDB запускается на автономном сервере, без какого-либо другого программного обеспечения (PHP, Apache и т.д.). Итак, у меня есть 96 ГБ оперативной памяти на этом сервере, и все это может использоваться MariaDB.

innodb_buffer_pool_instances слишком велик; он должен быть — buffer_pool_size/1G

Не могли бы вы, пожалуйста, прояснить это? В моем случае это должно быть buffer_pool_size/ 96Gb, или? Я думаю, что buffer_pool_size в моей конфигурации есть значение по умолчанию.

Какое значение будет, если innodb_flush_log_at_trx_commit?

Это 1 , но я пытался 2 без результата.

Поскольку вы используете innodb, сначала необходимо настроить несколько параметров innodb.

innodb_buffer_pool_size слишком мал, поскольку у вас 96 ГБ оперативной памяти. Может дать по крайней мере 50% этим переменным и также уменьшить innodb_buffer_pool_instances.

Если вы можете позволить себе потерю данных на 1-2 секунды, тогда установите значение innodb_flush_log_at_trx_commit равным 2.

Установите max_allowed_packet равным 256 M

Уменьшите max_connections до 1000

Установите для tmp_table_size значение 256M

Установите для sql_binlog и innodb_doublewrite значение 0

ПРИМЕЧАНИЕ: ВЫБЕРИТЕ ( @@key_buffer_size @@query_cache_size @@innodb_buffer_pool_size @@innodb_log_buffer_size @@max_connections * ( @@read_buffer_size @@read_rnd_buffer_size @@sort_buffer_size @@join_buffer_size @@binlog_cache_size @@thread_stack @@tmp_table_size — таблица размеров )) / (1024 * 1024 * 1024) КАК MAX_MEMORY_GB; должно составлять менее 80% от общего объема оперативной памяти.