Доброго времени дня. ХЗ с чего начать. Все работает, но не всегда)
И так есть сайтик, с достаточно большой таблицей товаров. Таблица стабильна, на скорость не жалуюсь. Mysql : 5.5.58-0ubuntu0.14.04.1 Таблица InnoDB. В штатном режиме работы все хорошо. Но проблемы начинаются когда происходит обновление товаров. Php (7.1) скрипт кушает добрые 4-6 GB оперативки (доступно 24, так что вполне нормально) и после этого 100% верные SELECT запросы просто перестают давать ответ. Ни ошибки, ни результата запроса не получаю.
Вопросы как установить проблему? Какие средства использовать? Стоит ли включать полное логирование запросов?
Может проблема в конфигах Mysql? где - то что-то добавить?
Или же может проблема в Ubuntu, и какому - то разделу просто не хватает памяти?
В общем я даже не знаю с какой стороны подойти к проблеме...

конфиги вроде стандартные
добавлено:
innodb_file_per_table = 1
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_log_buffer_size = 32M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2

vkleAlexSmithпроблемы начинаются когда происходит обновление товаровА велико ли обновление по объему? Может, оно долго продолжается и всё это время таблицы заблокированы.

AlexSmithPhp (7.1) скрипт кушает добрые 4-6 GB оперативки (доступно 24, так что вполне нормально)Мда, жестоко... А ну как количество товаров впятеро взлетит? ;-)
Скрипт кушает много, потому что хранит в оперативки данные на 700к товаров. По другому никак. После получения всех данных начинается их запись по 1 в БД , тут вряд ли таблица заблокирована.

ScareCrowавтор Стоит ли включать полное логирование запросов?
slow log? error_log?
включил на ночь, посмотрим что там ... наверно с этого и надо было начинать, спасибо

miksoftAlexSmithс достаточно большой таблицей товаровБольшой насколько? Хотя бы пара миллионов есть?
AlexSmithPhp (7.1) скрипт кушает добрые 4-6 GB оперативкиAlexSmithinnodb_buffer_pool_size = 2GХм, для PHP памяти не жалеете, а для InnoDB зажали.
База какого размера?

Судя по объему памяти, занимаемой скриптом, внутри его происходит что-то явно не то.

Попутно, проверьте innodb_flush_log_at_trx_commit.
база ~ 13GB таблица 8GB
Настраивал не я, сейчас добавил. Может еще как то с кешем связано?

miksoftAlexSmithбаза ~ 13GB таблица 8GBНормализации нет?
есть , частичная (есть что оптимизировать, но большая часть сделана), реально данных много.

miksoftAlexSmithСкрипт кушает много, потому что хранит в оперативки данные на 700к товаров.За один раз приходят изменения на 700к товаров?
AlexSmithПо другому никак.Зачем все сразу в память тащить? Неужели нельзя блоками или даже последовательно поштучно?
AlexSmithПосле получения всех данных начинается их запись по 1 в БД , тут вряд ли таблица заблокирована.А как коммиты сделаны?



Там скрипт обновления параметров товаров. Данные по принципу ключ => значение. (вес => 100кг или размер=> 10*10 см). Данные получаются пачками, но при этом один параметр одного товара может быть в первой пачке, второй параметр этого же товара в десятой, а третий в тысячной. Чтобы не гонять каждый раз систему получил->обработал->записал по 1 параметр-товару каждый раз для нового параметра, получаю всю кучу, сортирую, и записываю уже по паку параметров для 1 товара. Отсюда и расход памяти.
PS попробовал последовательно (получил параметр -> записал) - такая же фигня

сейчас поставил innodb_flush_log_at_trx_commit = 2

paverAlexSmithТам скрипт обновления параметров товаров. Данные по принципу ключ => значение. (вес => 100кг или размер=> 10*10 см). Данные получаются пачками, но при этом один параметр одного товара может быть в первой пачке, второй параметр этого же товара в десятой, а третий в тысячной. Чтобы не гонять каждый раз систему получил->обработал->записал по 1 параметр-товару каждый раз для нового параметра, получаю всю кучу, сортирую, и записываю уже по паку параметров для 1 товара. Отсюда и расход памяти.

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

AlexSmithpaverпропущено...

Вопрос от чайника. А почему эти пачки по одной не грузануть в промежуточную/временную таблицу, и уже из нее обновлять основную?
это ты себе ппц как усложнишь задачу ...
А именно, если получить весь стак параметров и хранить их в оперативке, тебе придется выполнить N запросов Select к БД и N запросов Update, где N количество единиц товара (ассортимент);
Если записывать последовательно каждый параметр ты получишь N*K Select и N*K Update, где K количество параметров для каждого товара (минимум 1)
Если делать через промежуточную таблицу ты получишь N*K Select + N*K Update для записи данных в промежуточную, потом N select для получения параметров для 1 товара и еще + N select и N update для занесения параметров в основную + 1 Drop для очистки

paver,

Все сложнее, параметры товара хранятся в текстовом поле записанные в массив и запакованные в JSON формат. По факту строка. В массив данные записываются с разных источников. То есть просто взять и сделать чистый Update не получится. Нужно получить этот массив (строку и распаковать) , обновить значения, и только тогда записать. Данные приходят по запросу с удаленного сервера в том же json формате. Без обработки данных не обойтись. Да и проблема не в том, что не хватает оперативки , а в том что что-то с обычными select запросами.
PS я понимаю что не зная тонкостей сложно что - то сказать наверняка.

vkleAlexSmithВсе сложнее, параметры товара хранятся в текстовом поле записанные в массив и запакованные в JSON формат. По факту строка. В массив данные записываются с разных источников. То есть просто взять и сделать чистый Update не получится. Нужно получить этот массив (строку и распаковать) , обновить значения, и только тогда записать. Данные приходят по запросу с удаленного сервера в том же json формате. Без обработки данных не обойтись.
Ну, так то, MySQL умеет работать с JSON. За версию 5.5 не скажу, на 5.7 работал. По крайней мере, с выборкой проблем не испытывал. Апдейтить отдельные элементы не пробовал - не было в том нужды. Если мускуль умеет апдейтить JSON, то при хорошем раскладе удастся запихать весь алгоритм обновления в хранимку.

AlexSmithчто-то с обычными select запросамиAlexSmithSELECT запросы просто перестают давать ответ. Ни ошибки, ни результата запроса не получаю.
Так до сих пор не понятно, ЧТО с селектами. Подключение есть, сервер принял запрос к исполнению. А дальше что - процесс обработки запроса в SHOW FULL PROCESSLIST появился или нет? Если появился - смотреть, на какой фазе он завис. Может статься, что там уже очередь страждущих, а сервер банально не успевает разгрести её, что вполне возможно при сильно возросшей нагрузке.
Формально, ответ на запрос может вернуться и через час, когда скрипты и, особенно, пользователи сайта потеряют всякую надежду и отвалятся по таймауту. Чтобы отбросить всё лишнее (драйвера, скрипты, вебсервер), можно попробовать в проблемное время выполнить запрос из консоли.
Походу копал не в ту сторону, запросы вообще не доходят да БД. Не отображаются в логах и SHOW FULL PROCESSLIST их тоже не показывает. Есть идеи что еще посмотреть?

i3bepbМожет отпадывает соединение с БД по таймауту, в самом скрипте который делает это какие-то ошибки есть?
Дело сдвинулось с мертвой точки. Уточнение скрипт перед работой БД получает параметры, процесс этот длительный около 2-3 часов, потом сортировка и только после работа с БД. Получается сначала скрипт работает с бд (получает ID, там какие - то параметры и т.п.), потом за 3 часа ни единого запроса к БД, а потом запросы уже не работают. Я во время получения данных добавил select запросы (каждую минуту по 1 ), легенькие, так чтобы чисто коннект был с базой. И когда дело дошло до основной записи данных - все прошло без проблем. Вопрос какой параметр в конфигах ковырять ?)

vkleAlexSmithВопрос какой параметр в конфигах ковырять ?)В конфиге мускуля что-то вроде idle_timeout (или как-то так, там два параметра). По дефолту восемь часов было. Так что, если только специально не крутили, 2-3 часа оно должно перекрывать.

Только тут есть две странности.
1. Что за логика такая у этого скрипта-долгоиграйки, что за эти 2...3 часа мускуль нельзя ни перезагрузить даже - так выходит?
2. И оно ещё более странно, когда PHP умеет по требованию скрипта восстанавливать слетевшее подключение (только не всегда умеет инициализировать как хочется).

Длинную паузу как-то иначе обработать можно. Ушел на обед перешел надолго к вычислениям - закрыл подключение, вернулся с обеда навычислялся - открыл и записал данные.
хорошая мысль, подкорректирую скрипт. Что - то я упустил этот момент. Тогда вопрос можно считать закрытым и разрешенным. Всем огромное спасибо!