Этот сайт существует на доходы от рекламы.
Пожалуйста, выключите AdBlock.
Вернуться   Форум об интернет-маркетинге > >
Ответ
 
Опции темы
Старый 20.10.2011, 13:20   #1
Super Moderator
 
Аватар для euhenio
 
Регистрация: 21.09.2001
Адрес: Москва, Россия
Сообщений: 4,472
Репутация: 587849
Социальные сети Профиль в ВКонтакте Профиль на Хабрахабре

По умолчанию нужны помощь или советы: как побороть медленную заливку mysql

Привет всем.

Есть много данных, и они заливаются. Но с некоторых пор все стало тормозить. Как бы это побороть?

Что имеется.

* Имеются таблицы, в каждой примерно 1М строк на сейчас, рассчитываю, что будет в разы больше. Но плохо уже сейчас - заливается медленно.

* средняя таблица сейчас:
Цитата:
Данные 436,174 KB
Индекс 79,469 KB
* таблица такая по ключам:
Цитата:
CREATE TABLE `sell_urls` (
`su_id` bigint(20) unsigned NOT NULL auto_increment,
`f_sd_id` bigint(20) NOT NULL default '-1',
`su_url` varchar(255) NOT NULL,
****много полей
PRIMARY KEY (`su_id`),
UNIQUE KEY `f_sd_id` (`f_sd_id`,`su_url`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 ROW_FORMAT=FIXED;
* в конфиге mysql, как я понимаю, не должно быть ничего интересного? Key_buffer_size=4G

* mysqld при работе насасывает памяти до 30% (от 24G!!!) - не знаю, важно это или нет.

* заливка происходит из файлов через выполнение команд mysql -u -p dbname < file.sql
В этих файлах лежат запросы вида:
Цитата:
INSERT INTO sell_urls(f_sd_id, su_url, список полей) VALUES (список наборов значений от 1 до 100), (), ()... ON DUPLICATE KEY UPDATE параметр1=IF(параметр2>=VALUES(параметр2),параметр1,IF(параметр1=0,разные значения)), параметр=VALUES(параметр), ..... ;
Много используется этих IF-ов при обновлении.
Т.е. в зависимости от того, что лежит в таблице, происходит перезапись других полей либо нет.
Перезаписываются поля, по которым индексов нет.
Запросы в одном файле полностью относятся к одной таблице, не к разным.
Причем если одни insert, то летает очень быстро, а если update - медленно

* select запросов одновременных к этим таблицам нет вообще.

И все это медленно работает.
Причем на маленьких таблицах работает быстро. Я сначала думал, что это из-за всяких этих IF-ов, но раз на маленьких быстро - наверное, не из-за них?

Сначала еще я сменил ROW_FORMAT на FIXED, потерял на размере, вроде побыстрее стало, но все равно таблицы увеличились и стало медленно.

Что посоветуете сделать?
Разбивать таблицы на маленькие уже не хочется - эта таблица с 1М записей и так одна из 100 после разнесения.
__________________
с ув., Евгений Трофименко --- seo блог Trofimenko.ru --- Tools.Promosite.ru - анализатор апдейтов Яндекса (пожертвуйте лимиты на Яндекс.XML!) --- Promosite.ru - консультации и продвижение сайтов
--- Konvr.ru - увеличение конверсии сайта на 81% за 4 недели (своя технология, MVT, десятки изменяемых параметров)
+++ официально заявляю - автор с ником promosite НИКАКОГО ОТНОШЕНИЯ к tools.promosite.ru и promosite.ru и ко мне НЕ ИМЕЕТ И НЕ ИМЕЛ (и не будет)
euhenio вне форума   Ответить с цитированием

Реклама
Старый 20.10.2011, 13:44   #2
nonSmoker
Студент
 
Регистрация: 30.09.2008
Сообщений: 31
Репутация: 10

По умолчанию Re: нужны помощь или советы: как побороть медленную заливку mysql

А сколько наборов в каждом инсерте? Может имеет смысл поднять max_allowed_packet ? и слать инсерты пачками размером чуть меньше max_allowed_packet?
nonSmoker вне форума   Ответить с цитированием
Старый 20.10.2011, 13:54   #3
euhenio
Super Moderator
 
Аватар для euhenio
 
Регистрация: 21.09.2001
Адрес: Москва, Россия
Сообщений: 4,472
Репутация: 587849
Социальные сети Профиль в ВКонтакте Профиль на Хабрахабре

ТопикСтартер Re: нужны помощь или советы: как побороть медленную заливку mysql

nonSmoker, там от 1 до 100 в пачке. Можно и больше, конечно. А должно помочь?

Просто число 1-100 оно случайным образом получается, по обстоятельствам - сколько скачалось, столько и кладется.

Или может все-таки в IF-ах проблема?
euhenio вне форума   Ответить с цитированием
Старый 20.10.2011, 14:07   #4
nonSmoker
Студент
 
Регистрация: 30.09.2008
Сообщений: 31
Репутация: 10

По умолчанию Re: нужны помощь или советы: как побороть медленную заливку mysql

Цитата:
Сообщение от euhenio Посмотреть сообщение
nonSmoker, там от 1 до 100 в пачке. Можно и больше, конечно. А должно помочь?
Должно. если по 20К в пачке - то это всего 50 запросов. вместо 200К.
Цитата:
Сообщение от euhenio Посмотреть сообщение
Или может все-таки в IF-ах проблема?
Может и в них. Но тут, что-то в голову ничего простого не идёт.
nonSmoker вне форума   Ответить с цитированием
Старый 20.10.2011, 14:21   #5
sokol_jack
Академик
 
Регистрация: 16.03.2008
Сообщений: 1,288
Репутация: 120244
Отправить сообщение для sokol_jack с помощью ICQ

По умолчанию Re: нужны помощь или советы: как побороть медленную заливку mysql

LOCK TABLES table WRITE;
UPDATE/INSERT
UNLOCK TABLES;

У меня для краулера помогло. Раз в 20 шустрее стало.
__________________
Любимый хостинг
How can we grow old when the soundtrack of our lives is rock-n-roll?
sokol_jack вне форума   Ответить с цитированием
Старый 20.10.2011, 15:10   #6
euhenio
Super Moderator
 
Аватар для euhenio
 
Регистрация: 21.09.2001
Адрес: Москва, Россия
Сообщений: 4,472
Репутация: 587849
Социальные сети Профиль в ВКонтакте Профиль на Хабрахабре

ТопикСтартер Re: нужны помощь или советы: как побороть медленную заливку mysql

sokol_jack, попробовал. Изменения очень маленькие.

У вас, наверное, основное торможение было из-за массированной вставки и пересчета новых индексов.
А у меня таблицы маленькие, вставка идет быстро. А при апдейте поля с индексами не изменяются.
euhenio вне форума   Ответить с цитированием
Старый 20.10.2011, 15:27   #7
Ankheg
Студент
 
Регистрация: 14.03.2007
Сообщений: 48
Репутация: 601

По умолчанию Re: нужны помощь или советы: как побороть медленную заливку mysql

Несколько идей:
* Попробовать таблицу перевести в innodb.
* Посмотреть отчет "status" в phpmyadmin, там красным выделены проблемы.
* Включить slow query log и binlog, потом посмотреть отчеты mysqlsla.
* Прогнать tuning-primer или tuner.pl
Ankheg вне форума   Ответить с цитированием
Старый 20.10.2011, 15:53   #8
netwind
Академик
 
Регистрация: 06.05.2007
Сообщений: 6,010
Репутация: 490997

По умолчанию Re: нужны помощь или советы: как побороть медленную заливку mysql

euhenio, ну зачем же сразу бросаться перебирать все советы ? сначала найдите каких ресурсов не хватает mysql, а потом уже прицельно снижайте их потребление. Прежде всего нужен мониторинг сервера. Хотя обычно все и так ясно - максимально нагружен диск.

все-таки побольше информации хотя бы из slow log можете предоставить?
mysqltuner.pl тоже неплохо суммирует информацию о сервере mysql.

if просто трансформируется в два разных запроса в зависмости от ситуации. само использование if не может быть причиной медленного обновления.
netwind вне форума   Ответить с цитированием
Старый 20.10.2011, 16:21   #9
edogs
Писать: search@ник_тут.ru
 
Аватар для edogs
 
Регистрация: 16.12.2005
Адрес: St.Petersburg, Russia
Сообщений: 7,616
Репутация: 91029
Отправить сообщение для edogs с помощью Skype™

По умолчанию Re: нужны помощь или советы: как побороть медленную заливку mysql

1) решение грубой силы: поскольку row format уже fixed и text полей вроде нет, и инсерт по сути обрабатывает всю таблицу - есть определённый смысл загнать все данные в в память ( 1) memory table или 2) создать таблицу на временном диске - в памяти), залить новые данные, после этого выгрузить таблицу обратно на диск. во варианте (2) можно даже проще - load data infile или даже copy table.myd . но понятно что на время обработки прийдется все приостановить. при текущем размере это вам обойдется мегабайт в 500 памяти, но учитывая что вы только под ключи 4Гб оставили - по идее напрягать не должно особо.
2) хотя вы и сказали что на мелкие разбивать не хочется, но все же те поля которые проверяются тут "IF(параметр2>=VALUES(параметр2),параметр1" - есть смысл вынести в отдельную таблицу.
3) самое очевидное, возможно самое легкое, но для чего недостаточно информации сейчас - перестроить сам запрос на апдейт таблицы. on duplicate if parameter - это по любому медленно, так как ему приходится прошерстить практически все эти 500мб данных.
4) и разумеется попробовать: disable keys; update; update; update;.... enable keys; при апдейтах у Вас перестройка ключей может больше времени занимать, чем все остальное.

Цитата:
Сообщение от netwind Посмотреть сообщение
а это еще надо доказать. например, с помощью slow_log.
если там обычные вычисления в зависимости от полей той же записи, с чего бы ему считывать все остальные данные? возможно, эти вычисления сделаны с ошибками и действительно подгребает все остальные записи ради элементарной операции.
Речь не об @остальных@. Речь о том, что исходя из предполагаемого контекста задачи, скорее всего апдейтов очень много (старые данные апдейтятся, новые вставляются, старых должно быть больше - реально много - логично?) и идут они в случайном порядке (сортировки тут не видно, а ведь каждую запись надо считать, обсчитать, записать), что вполне логично будет давать тормоза.
Перемещение таблицы в память - по крайней мере избавит от проблем с тормозами из-за "случайного порядка" - а это самое критичное, и разумеется даже если порядок там не слишком рандомный - все равно придаст скорости изрядно. А если зацепляются ключи все-таки так или иначе (при вставке по любому зацепляются), то запрет ключей во время апдейта таблицы так же поможет.
__________________
Разработка проектов связанных с криптовалютой. Разумные цены. Хорошее качество. Адекватный подход.

Последний раз редактировалось edogs; 20.10.2011 в 16:55..
edogs вне форума   Ответить с цитированием
Старый 20.10.2011, 16:28   #10
netwind
Академик
 
Регистрация: 06.05.2007
Сообщений: 6,010
Репутация: 490997

По умолчанию Re: нужны помощь или советы: как побороть медленную заливку mysql

Цитата:
Сообщение от edogs Посмотреть сообщение
это по любому медленно, так как ему приходится прошерстить практически все эти 500мб данных.
а это еще надо доказать. например, с помощью slow_log.
если там обычные вычисления в зависимости от полей той же записи, с чего бы ему считывать все остальные данные? возможно, эти вычисления сделаны с ошибками и действительно подгребает все остальные записи ради элементарной операции.

netwind добавил 20.10.2011 в 18:05
Цитата:
Сообщение от edogs Посмотреть сообщение
ечь не об @остальных@. Речь о том, что исходя из предполагаемого контекста задачи, скорее всего апдейтов очень много (старые данные апдейтятся, новые вставляются, старых должно быть больше - реально много - логично?) и идут они в случайном порядке (сортировки тут не видно, а ведь каждую запись надо считать, обсчитать, записать), что вполне логично будет давать тормоза.
Перемещение таблицы в память - по крайней мере избавит от проблем с тормозами из-за "случайного порядка" - а это самое критичное, и разумеется даже если порядок там не слишком рандомный - все равно придаст скорости изрядно. А если зацепляются ключи все-таки так или иначе (при вставке по любому зацепляются), то запрет ключей во время апдейта таблицы так же поможет.
да и хватит уже пропагандировать этот унылый engine=memory. там можно нарваться на совершенно другие проблемы.
innodb с большим буфером и innodb_flush_log_at_trx_commit=0 почти так же хорошо работает.
есть еще секретные недокументированные опции innodb, так там вообще ничего не пишется на диск до переполнения буфера

Последний раз редактировалось netwind; 20.10.2011 в 17:06.. Причина: Добавлено сообщение
netwind вне форума   Ответить с цитированием
Ответ




Опции темы

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.

Быстрый переход



Текущее время: 07:19. Часовой пояс GMT +3.

Регистрация Справка Календарь Поддержка Все разделы прочитаны