SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 24.01.2016 10:13:42

generale
Участник
Зарегистрирован: 24.01.2016
Сообщений: 8

Как ускорить INSERT?

Добрый день.
Есть БД, в которой около 40 таблиц. Большинство из них – справочники, а в одной (`main`) хранятся данные, с которыми предполагается работать пользователям. Эта таблица весит около 40 ГБ и в дальнейшем будет увеличиваться (+8 ГБ в год, ежедневными вставками по 20-30 МБ (20-30 тыс. строк)).
Есть пользователи, для каждого из которых создается отдельная БД, в которую копируется часть таблицы `main` и все справочники. В дальнейшем, пользователи работают со своей БД, выводя данные кусками, формируя запросы через веб-интерфейс. Для вывода результатов запроса на экран, эти результаты копируются в еще одну таблицу (`display_data`).
Эта таблица выводится на экран с помощью плагина Datatables, который позволяет делать различные сортировки, применять фильтры к любым полям, и т.д.
Вопрос, как обычно, в скорости операций. Интересует, прежде всего, скорость копирования данных из таблицы `main` в таблицу `display_data`. Если пользователь делает выборку не очень большого объема, все происходит довольно шустро, но, если он выбирает большой объем (например, около 1,5 млн. записей), INSERT длится порядка 60-65 сек., что долго.
Тут стоит отметить, что этот INSERT включает в себя 18 JOIN’ов, необходимых для подстановки данных из справочников.
Сразу добавлю, что SELECT отрабатывает довольно быстро (меньше 0,01 сек.), все время уходит на INSERT и JOIN’ы.
Я пробовал идти по пути денормализации, когда у пользователей таблица `main` изначально имеет структуру `display_data`. Это позволило уменьшить время INSERT’а до 25 сек. Однако, хотелось бы быстрее, и не хотелось бы отказываться от справочников, так как время от времени есть необходимость их обновлять. Да и раздувать БД пользователей тоже не хочется.
Причем, сейчас таблица `display_data` не имеет индексов (кроме PK), а для нормального функционирования предполагается, что пользователь будет иметь нормальную скорость контекстного поиска по полям и различных фильтров/сортировок. То есть, будут индексы. А на полях типа VARCHAR они еще и будут типа fulltext. Пока даже не пытался их добавлять.
Все это пока тестируется на localhost’e. Машина Intel core i5 (2 ядра по 2,5 GHz), 6GB RAM. Время от времени я тестирую на VPS 1 ядро, 4Gb RAM, SSD. Скорость практически всех операций получается почти одинаковой. Если для этой задачи увеличить ресурсы VPS-сервера, поможет ли это, и если да, то насколько? Или, может быть, можно как-то увеличить скорость INSERT’a с помощью изменения структуры БД, или настроек MySQL?

Таблица `main`:
CREATE TABLE `main` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`tr_type_code` tinyint(1) NOT NULL,
`send_date` date NOT NULL,
`arrival_date` date DEFAULT NULL,
`product_code` char(5) COLLATE utf8_unicode_ci DEFAULT NULL,
`group_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
`send_country_code` int(11) DEFAULT NULL,
`send_foriegn_port_code` char(5) COLLATE utf8_unicode_ci DEFAULT NULL,
`send_ port_rf_code` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
`send_region_rf_code` int(11) DEFAULT NULL,
`sender_code` char(8) COLLATE utf8_unicode_ci DEFAULT NULL,
`target_country_code` int(11) DEFAULT NULL,
`target_foreign_port_code` char(5) COLLATE utf8_unicode_ci DEFAULT NULL,
`target_port_rf_code` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
`target_region_rf_code` int(11) DEFAULT NULL,
`reciever_code` char(8) COLLATE utf8_unicode_ci DEFAULT NULL,
`transport_type_code` int(11) DEFAULT NULL,
`transport_comp_code` int(11) DEFAULT NULL,
`payer_code` int(11) DEFAULT NULL,
`volume` smallint(6) DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1521368 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Таблица партицирована по полю `send_date` (помесячно), если это важно.

Таблица `display_data`:
CREATE TABLE `display_data` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`tr_type` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`tr_type_code` tinyint(1) unsigned NOT NULL,
`send_date` date NOT NULL,
`arrival_date` date DEFAULT NULL,
`product_name` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
`product_code` char(5) COLLATE utf8_unicode_ci NOT NULL,
`send_country` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`send_foreign_port` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`send_region_rf` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`send_city` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`send_port_rf` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`sender` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
`sender_phone` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`sender_email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`sender_website` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`sender_address` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
`sender_CEO` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
`sender_inn` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
`sender_code` char(8) COLLATE utf8_unicode_ci NOT NULL,
`target_country` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`target_foreign_port` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`target_port_rf` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`target_city` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
`target_port_rf` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`reciever` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
`reciever_phone` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`reciever_email` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`reciever_website` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`reciever_address` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
`reciever_CEO` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
`reciever_inn` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
`reciever_code` char(8) COLLATE utf8_unicode_ci NOT NULL,
`transport_type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`transport_company` varchar(1000) COLLATE utf8_unicode_ci NOT NULL,
`payer` varchar(1000) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Конфигурация my.cnf:
key_buffer_size = 50M
max_allowed_packet = 1M
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
query_cache_size = 50M
myisam_sort_buffer_size = 16M
log_error = "mysql_error.log"
tmp_table_size = 500M
max_heap_table_size = 500M
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 3M
innodb_log_file_size = 500M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=20000

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Неактивен

 

#2 24.01.2016 10:18:08

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Как ускорить INSERT?

Вы уверены, что дело в INSERT? Сколько времени занимает SELECT со всеми JOIN, но без INSERT? Покажите EXPLAIN для этого запроса.

Неактивен

 

#3 24.01.2016 10:51:08

generale
Участник
Зарегистрирован: 24.01.2016
Сообщений: 8

Re: Как ускорить INSERT?

Отображение строк 0 - 24 (1400317 всего, Запрос занял 0.0468 сек.)
Тут, правда, есть странность. Не знаю, может это нормально. Визуально этот запрос занял около 10-15 сек., а phpmyadmin выдал результат 0,04.

Запрос:

SELECT
`t`.`tr_type_fullname` AS `tr_type`,
`m`.`tr_type_code` AS `tr_type_code`,
`m`.`send_date` AS `send_date`,
`m`.`arrival_date` AS `arrival_date`,
`e`.`product_fullname` AS `product_name`,
`c`.`country_fullname` AS `send_country`,
`ss`.`port_fullname` AS `send_foreign_port`,
`r`.`region_fullname` AS `send_region_rf`,
`ro`.`city_shortname` AS `send_city`,
`sr`.`port_fullname` AS `send_port_rf`,
`co`.`company_name` AS `sender`,
`co`.`company_phone` AS `sender_phone`,
`co`.`company_email` AS `sender_email`,
`co`.`company_website` AS `sender_website`,
`co`.`company_address` AS `sender_address`,
`co`.`company_CEO` AS `sender_CEO`,
`co`.`company_inn` AS `sender_inn`,
`c1`.`country_fullname` AS `target_country`,
`ss1`.`port_fullname` AS `target_foreign_port`,
`r1`.`region_fullname` AS `target_region_rf`,
`ro1`.`city_shortname` AS `target_city`,
`sr1`.`port_fullname` AS `target_port_rf`,
`co1`.`company_name` AS `reciever`,
`co1`.`company_phone` AS `reciever_phone`,
`co1`.`company_email` AS `reciever_email`,
`co1`.`company_website` AS `reciever_website`,
`co1`.`company_address` AS `reciever_address`,
`co1`.`company_CEO` AS `reciever_CEO`,
`co1`.`company_inn` AS `reciever_inn`,
`wt`.`transport_type_fullname` AS `transport_type`,
`wo`.`transport_company_name` AS `transport_company`,
`m`.`volume` AS `volume`,

FROM `main` AS `m`
LEFT JOIN `tr_types` AS `t` ON `t`.`tr_type_code` = `m`.`tr_type_code`
LEFT JOIN `products` AS `e` ON `e`.`product_code` = `m`.`product_code`
LEFT JOIN `countries` AS `c` ON `c`.`country_code` = `m`.`send_country_code`
LEFT JOIN `ports` AS `ss` ON `ss`.`port_code` = `m`.`send_foreign_port_code`
LEFT JOIN `regions` AS `r` ON `r`.`region_code` = `m`.`send_region_rf_code`
LEFT JOIN `ports` AS `sr` ON `sr`.`port_code` = `m`.`send_port_rf_code`
LEFT JOIN `cities` AS `ro` ON `ro`.`city_code` = `sr`.`city_code`
LEFT JOIN `companies` AS `co` ON `co`.`company_code` = `m`.`sender_code`
LEFT JOIN `countries` AS `c1` ON `c1`.`country_code` = `m`.`target_country_code`
LEFT JOIN `ports` AS `ss1` ON `ss1`.`port_code` = `m`.`target_foreign_port_code`
LEFT JOIN `regions` AS `r1` ON `r1`.`region_code` = `m`.`target_region_rf_code`
LEFT JOIN `ports` AS `sr1` ON `sr1`.`port_code` = `m`.`target_port_rf_code`
LEFT JOIN `cities` AS `ro1` ON `ro1`.`city_code` = `sr1`.`city_code`
LEFT JOIN `companies` AS `co1` ON `co1`.`company_code` = `m`.`reciever_code`
LEFT JOIN `transport_types` AS `wt` ON `wt`.`transport_type_code` = `m`.`transport_type_code`
LEFT JOIN `transport_companies` AS `wo` ON `wo`.`transport_company_code` = `m`.`transport_company_code`

WHERE (`m`.`send_date` BETWEEN "2012-01-01" AND "2012-12-31");


EXPLAIN:

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    m    ALL    NULL    NULL    NULL    NULL    1389129    Using where
1    SIMPLE    t    ALL    PRIMARY    NULL    NULL    NULL    2    Using where; Using join buffer (Block Nested Loop)
1    SIMPLE    e    eq_ref    PRIMARY    PRIMARY    15    login_name.m.product_code    1    NULL
1    SIMPLE    c    eq_ref    PRIMARY    PRIMARY    4    login_name.m.send_country_code    1    NULL
1    SIMPLE    ss    eq_ref    PRIMARY    PRIMARY    15    login_name.m.send_foreign_port_code    1    NULL
1    SIMPLE    r    eq_ref    PRIMARY    PRIMARY    4    login_name.m.send_region_rf_code    1    NULL
1    SIMPLE    sr    eq_ref    PRIMARY    PRIMARY    15    login_name.m.send_port_rf_code    1    Using where
1    SIMPLE    ro    eq_ref    PRIMARY    PRIMARY    4    login_name.sr.city_code    1    NULL
1    SIMPLE    co    eq_ref    PRIMARY    PRIMARY    24    login_name.m.sender_code    1    NULL
1    SIMPLE    c1    eq_ref    PRIMARY    PRIMARY    4    login_name.m.target_country_code    1    NULL
1    SIMPLE    ss1    eq_ref    PRIMARY    PRIMARY    15    login_name.m.target_foreign_port_code    1    NULL
1    SIMPLE    r1    eq_ref    PRIMARY    PRIMARY    4    login_name.m.target_region_rf_code    1    NULL
1    SIMPLE    sr1    eq_ref    PRIMARY    PRIMARY    15    login_name.m.target_port_rf_code    1    Using where
1    SIMPLE    ro1    eq_ref    PRIMARY    PRIMARY    4    login_name.sr1.city_code    1    NULL
1    SIMPLE    co1    eq_ref    PRIMARY    PRIMARY    24    login_name.m.reciever_code    1    NULL
1    SIMPLE    wt    eq_ref    PRIMARY    PRIMARY    4    login_name.m.transport_type_code    1    NULL
1    SIMPLE    wo    eq_ref    PRIMARY    PRIMARY    4    login_name.m.transport_company_code    1    NULL

Неактивен

 

#4 24.01.2016 10:55:34

generale
Участник
Зарегистрирован: 24.01.2016
Сообщений: 8

Re: Как ускорить INSERT?

Может быть, время, которое выдал phpmyadmin, это время последнего джоина?

Неактивен

 

#5 24.01.2016 12:00:26

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Как ускорить INSERT?

У Вас нет индексов на `m`.`send_date` и, вероятно, на `t`.`tr_type_code`

ALTER TABLE `main` ADD KEY(`send_date`);
ALTER TABLE `tr_types` ADD KEY(`tr_type_code`);


Про время в phpmyadmin сказать не могу. Это должно быть время всего запроса. Более надежно выполнять запрос в консоли и мимо кэша, то есть SELECT SQL_NO_CACHE

Неактивен

 

#6 24.01.2016 12:53:33

generale
Участник
Зарегистрирован: 24.01.2016
Сообщений: 8

Re: Как ускорить INSERT?

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

Например, WHERE может выглядеть так:

WHERE ((`m`.`tr_type_code` = 1 AND (`m`.`send_region_rf_code` IN (123, 48) OR `m`.`send_port_rf_code` IN (43600, 43850)) AND (`m`.`target_region_rf_code` IN (94, 31))) OR (`m`.`tr_type_code` = 2 AND (`m`.`send_region_rf_code` IN (123, 48) OR `m`.`send_port_rf_code` IN (43600, 43850)) AND (`m`.`target_country_code` IN (7, 8))) OR (`m`.`tr_type_code` = 3 AND (`m`.`send_country_code` IN (12, 16)) AND (`m`.`target_port_rf_code` IN (94, 31)))) AND (`m`.`send_date` BETWEEN "2012-01-01" AND "2012-12-31");

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

Вообще, с этим запросом я экспериментировал по-разному: например, делал много составных индексов и убирал OR из запроса, из-за чего приходилось делать много UNION ALL, хотя индексы работали, и запрос выполнялся быстрее, но вот INSERT становился ОЧЕНЬ долгим.

Тем не менее, добавил индексы, ничего не изменилось, за исключением:
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra   
1    SIMPLE    m    ALL    send_date    NULL    NULL    NULL    1389129    Using where

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

А как, все-таки насчет INSERT?

Неактивен

 

#7 24.01.2016 14:59:16

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Как ускорить INSERT?

Попробуйте такой параметр: http://dev.mysql.com/doc/refman/5.7/en/ … uffer_size

Попробуйте вставлять сначала в новую таблицу, а потом из нее уже без JOIN.

CREATE TABLE `bulk_028` SELECT ...;

Неактивен

 

#8 24.01.2016 16:28:02

generale
Участник
Зарегистрирован: 24.01.2016
Сообщений: 8

Re: Как ускорить INSERT?

Попробовал.
CREATE TABLE ... SELECT:
MySQL вернула пустой результат (т.е. ноль строк). (Запрос занял 51.6093 сек.)

INSERT ... SELECT:
Идентификатор вставленной строки: 1400317 (Запрос занял 22.5614 сек.)

Суммарно получается на 10 сек. дольше.

Уважаемый Григорий, у меня на рабочем столе сохранена ваша презентация по работе с экстремально большими таблицами. Там, если мне не изменяет калькулятор, у вас получилась скорость пакетной вставки 1 млн. записей в секунду.
В моем случае - 63 500 / сек., а если в сумме (CREATE TABLE - SELECT - INSERT) - 18 800 / сек.
Правильно ли я понимаю, что это, во многом, связано с тем, что у вас были только tinyint и smallint поля, то есть объем самой записи был небольшим, а все 30 мл. записей занимали чуть меньше 350 МБ? И, если это так, может эти 22 сек. и не так плохо? И, если это так, то поможет ли более мощное железо?

Да, под bulk_insert_buffer_size отдал 2 ГБ.

Отредактированно generale (24.01.2016 16:29:33)

Неактивен

 

#9 24.01.2016 19:03:48

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Как ускорить INSERT?

generale, у меня в докладе быстрая загрузка достигалась за счет LOAD DATA INFILE.

Неактивен

 

#10 25.01.2016 10:53:35

Александр Трофимов
Завсегдатай
Откуда: Юрмала
Зарегистрирован: 19.09.2011
Сообщений: 95

Re: Как ускорить INSERT?

Здравствуйте, generale, выше rgbeast подал вам отличную идею — LOAD DATA INFILE.
Я пользуюсь таким подходом для обноления данных в большой таблице.
Выглядит это так:

SELECT
    *
FROM
    table1
INTO
    OUTFILE 'file.txt'


LOAD DATA INFILE
    'file.txt'
REPLACE INTO TABLE
    table2


Разница ощутима в разы и даже десятки раз.

ЗЫ. Но для этого нужно, чтобы и в одной и другой таблице был однаковый уникальный ключ, на который будет опираться REPLACE.

Отредактированно Александр Трофимов (25.01.2016 10:58:12)

Неактивен

 

#11 25.01.2016 11:20:14

generale
Участник
Зарегистрирован: 24.01.2016
Сообщений: 8

Re: Как ускорить INSERT?

Спасибо, Александр.
Я пытался тестировать этот способ. Действительно, вставка данных происходит на порядок быстрее, хуже с SELECT INTO OUTFILE. На локальной машине сброс данных объемом около 1ГБ происходит примерно за 40-50 сек., что практически нивелирует ускорение вставки.
Сегодня попробую протестировать на VPS. По идее, с SSD это должно быть быстрее.
Вообще, проблема в том, что я только полгода занимаюсь веб-программированием, и именно сейчас сказывается отсутствие опыта.
Я вижу это так, что есть:
1. Сам запрос. Нужно понять, оптимален ли он. Опять таки, этого не сделать в отрыве от параметров конфигурации. Все, что я нашел на эту тему, попытался настроить. Сейчас профилирую запрос (хотя это почти ничего не дает - там 99% времени sending data), и смотрю изменения в STATUS VARIABLES, может быть там найду решение.
2. Структура БД. Если не удастся оптимизировать запрос, придется, видимо, думать над изменением логики самого веб-приложения и алгоритмов его взаимодействия с БД, для того, чтобы изменить структуру. Не хотелось бы.
3. Железо. Опять-таки, нет опыта. Первый раз в жизни имею дело с VPS и вообще впервые вижу линукс))) Вот если я увеличу ресурсы, скажем до 16 или 32 ГБ RAM, и 4-8 ядер, что произойдет? Запрос, который выполняется 60-70 сек. будет выполняться быстрее, или нет? Все равно, для полноценного использования этих ресурсов нужно будет правильно настроить конфигурацию MySQL. Но, если это так, то возможно, при правильной настройке и не нужны такие ресурсы. Короче, возврат к п. 1 )))))
Сорри за сумбур, вопросов пока намного больше, чем ответов.

Неактивен

 

#12 25.01.2016 12:07:42

Александр Трофимов
Завсегдатай
Откуда: Юрмала
Зарегистрирован: 19.09.2011
Сообщений: 95

Re: Как ускорить INSERT?

Понятное дело, что там целая череда шагов.
1. Очевидно, что стационарный компьютер работает несколько иначе, чем сервер. И часто менее производителен для таких задач.
2. Настройка MySQL имеет большое значение.
3. Индексы вообще почти самое важное. Без них ничего быстро работать не будет.
4. Объем памяти конечно тоже влияет.

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

Короче, главный вопрос: зачем вообще создавать таблицу с 1,5млн записей.

CREATE TABLE ... SELECT не может возвращать «пустой» результат, если в селекте есть хоть одна строка. Таблица будет создана на основании выбранных данных. И при работе в пределах MySQL вообще стоит забыть про INSERT на больших объемах данных.
В данный момент я пользуюсь исключительно CREATE TABLE ... SELECT и LOAD DATA INFILE. Если надо обновить таблицу, то я создаю новую через CREATE TABLE ... SELECT и подменяю ею старую. Старую удаляю.

ЗЫ.Если нет времени разбираться, для настройки MySQL обратитесь к Григорию и Павлу. +)) Это того стОит.

Неактивен

 

#13 25.01.2016 14:15:39

generale
Участник
Зарегистрирован: 24.01.2016
Сообщений: 8

Re: Как ускорить INSERT?

Но я все понять не могу, зачем одному человеку выбирать 1,5 млн записей за раз?

У пользователей могут быть очень разные потребности в анализе данных. Поэтому нет возможности заранее знать, какие поля им будут необходимы. Что же касается количества записей, то тут тоже также потребность в больших объемах вполне вероятна. То есть, запросы, типа "хочу все, за весь период" могут быть довольно часто, и эту возможность хочется предусмотреть. Конечно, можно попытаться над этим поработать, но это уже относится к п. 2 моего предыдущего поста (нужно будет менять логику работы веб-интерфейса). Если не получится сделать так, как задумано изначально, то, конечно, придется этим заняться.

Но, все-таки, не могли бы вы, на основании своего опыта, как бы "на глаз", дать оценку: те запросы, которые я приводил ранее, с их временем выполнения, они "быстрые", "медленные" или "нормальные"? Просто, чтобы мне понимать, получится ли их ускорить за счет каких-то настроек MySQL.

К примеру, меня смущают значения этих переменных, после выполнения запроса:
created_tmp_tables: 6
created_tmp_disk_tables: 2

Я попытался увеличить tmp_table_size и max_heap_table_size до 1,5 ГБ, но ничего не изменилось. Вообще, не уверен, в том, что в моем случае эти значения могут говорить о чем-нибудь.

ЗЫ.Если нет времени разбираться, для настройки MySQL обратитесь к Григорию и Павлу. +)) Это того стОит.

Пока не хочется сдаваться))) Хочу, все-таки, разобраться с этим, раз уж занялся, но если не получится, то, конечно, буду искать помощи экспертов!

Отредактированно generale (25.01.2016 14:16:37)

Неактивен

 

#14 25.01.2016 23:10:03

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Как ускорить INSERT?

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

Неактивен

 

#15 26.01.2016 12:28:34

generale
Участник
Зарегистрирован: 24.01.2016
Сообщений: 8

Re: Как ускорить INSERT?

Всем огромное спасибо за помощь! Попробовал масштабировать VPS сервер до 4-х ядер и 32 ГБ ОЗУ, что практически ничего не дало (время вставки упало до 40 секунд). Придется идти по пути доработки логики веб-приложения и сильно усложнить php-скрипт, который получает данные из БД, но, с другой стороны, такой путь, видимо будет более правильным, поскольку будет задействовать значительно меньше серверных ресурсов.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson