Как можно увеличить скорость записи в бд mysql?

Question

[maryaTurova]

Структура таблицы

Суть следующая:
От клиента приходит запрос в виде json, в котором значения - это текст base64 (картинки).

От локальных машин идет запрос на получение картинок для последующей ее обработки:

php

$method = $_GET['select'];
$len = $_GET['len'];
$arr = [];
$id_list = [];

mysqli_query($db,"DELETE FROM `api` WHERE (`date` < DATE_SUB(NOW(),INTERVAL 5 MINUTE))");

$result = mysqli_query($db,"
	SELECT `set_json`,`id` FROM `api` 
	WHERE `method`='$method' 
	  AND `result`='WAITING' 
	  AND `date`> NOW() - INTERVAL 2 MINUTE
	ORDER BY `date` DESC LIMIT $len"
);
while ($row = mysqli_fetch_array($result)) {
	$arr[$row[1]] = json_encode(unserialize($row[0]));
	//собираем все id в один список
	$id_list[] = $row[1]; 
}
//апдейтим все api по списку id одним запросом
if(!empty($id_list)) {
	mysqli_query ($db, "
		UPDATE `api` SET `result`='CAPCHA_NOT_READY' 
		WHERE `id` IN('".join("','", $id_list)."')
	");
}
echo json_encode($arr);

Запросов много как от клиента, так и от локальных машин
Периодически всё стопориться.И такое ощущение что mysql не успевает обрабатывать данные.
Логи все чистятся, на жестком диске места предостаточно.
Не судите строго, все писал сам с помощью гугла и форумов.
Подскажите, в чем может быть проблема? Спасибо!!!

Конфигурация сервера:

Answer 1

[rPman]

Что значит стопорится? зачем гадать, расставь по больше вывода в свои логи отладочной информации (когда принял запрос, обработал, отправил транзакцию, когда получил ответ от базы и т.п.) с таймингами и смотри что и где происходит в проблемный момент

Что за пиз...ц с сериализацией картинок в base64 потом это все в json, а в базе хранить php-щным serialize?

http post прекрасно справляется с отправкой файлов, там своя сериализация, никто не мешает миксить в запросе get и post данные

Не храните картинки в базе данных, никак, ни блобом ни сериализацией, кто придумал этот маразм, в каком бредовом туториале это прочитали и каждый второй это реализует?

Файлы храни в файлах на диске, а в базу записывай путь или имя файла (либо сам файл по идентификатору называй), раз в сто лет на обслуживании базы сравнивай наличие файлов на диске и записей в базе и удаляй висяки (образуются если во время обработки произошла ошибка, например скрипт умер, файл записали а транзакция в базе откатилась)

p.s. в php во всех репозитариях есть красивый сериализатор igbinary, если что он в каком то смысле даже быстрее старого serialize и точно компактнее, его потихоньку в стандарт пропихивают, например можно использовать его для хранения сессий

Comments

[maryaTurova]

Все понял....Спасибо!

Answer 2

[ThunderCat]

Подскажите, в чем может быть проблема? Спасибо!!!

Подсказываю: Файлы хранятся в файловой системе. Хранить файлы в бд это палка о двух концах, оба из которых засунуты в ж...
1) Файловые системы работают с файлами гораздо лучше текстовых хранилищ.
2) Для отдачи файлов из файлов не нужно выполнять запросы к бд, десериализацию, декодирование из жсон, а затем из базе64, и гнать как ответ от пыха к серверу. Еще и гонять туда-сюда МНОГО данных, бд такое не любит. Просто отдаешь путь к файлу и его сервер напрямую читает.
3) Проще настраивать кэширование на стороне клиента, когда файл отдается не через скрипт, а "как файл"
4) Хранение в базе64 - это +30% примерно к весу изображения
И еще дохрена проблем...

Единственный более-менее аргумент за хранение в базе картинок - отдавать их по запросу только авторизированным пользователям. И то, тут правильнее хранить путь до них, сами картинки держать опять же в фс, выше документ рута, и отдавать их через readfile();

В остальном хранить файлы в бд - решение такое же здравое, как хранить деньги в акциях Российских компаний...

Comments

[maryaTurova]

Спасибо!

[maryaTurova]

Еще такой вопрос!
Base64 сохранить как картинку или в .txt строкой?

[ThunderCat]

maryaTurova, расшифровать обратно в бинарь, сохранить как картинку с именем либо как у оригинала, либо формировать уникальное имя из микротайма или хэша от (имя картинки + микротайм). В базу записать путь до картинки.

Зачем хранить картинку в txt и как вообще такое пришло в голову малопонятно...

Answer 3

[Ищю в поисковиках]

Посмотрите в slow логи mysql. Угадываю, что вы можете найти запросы, которые можно оптимизировать.

А так - информации очень мало. Для диагностики, лучше:
1. Проанализировать ttfb
2. Если ttfb мал, но запросов много - уменьшить количество запросов.
3. Запросов не много, но большой ttfb - тут нужно профилирование / анализ лога системных вызовов
4. Если из анализа видно, что больше времени тратиться на получение данных с диска - то диск узкое место.
5. Если из анализа видно, что узкое место mysql - то тут нужно смотреть, что за запрос, проверить его explain.
6. Если запрос нельзя оптимизировать, то тут только кеш.

Из того, что вижу, вы можете ускорить скрипт:
1. Обломать руки "программисту" (чтобы он больше не писал такой код)
2. Вынести delete в отдельный скрипт и запускать раз в 10 минут
3. Действительно ли вам нужна капча (если я правильно понимаю), для запросов от локальных машин?
4. Какого либо кеширования запросов у вас не увидел (наймите нормального разработчика), попробуйте (если ещё не используете) redis.

Также прочтите, что написали выше. Про часть, что хранить картинки в базе можно, только затем, чтобы отдавать их авторизованным пользователям - бред, по авторизации можно отдавать и с диска.

Если не хватает места на диске, могу предложить 2 варианта решения:
1. Могу предложить, что у вас дедик, отсюда - "Купи нормальные диски"
2. Если логи вам не нужны, то можеие их в dev null сразу отправлять, а потом гадать, что случилось. Если серьезно - используйте нормальный сбор логов (на отдельный сервер), например, на сервер с бэкапами (надеюсь, они у вас есть).

Comments

[maryaTurova]

Спасибо за развернутый ответ!
Пришел к выводу что самым оптимальным вариантом для меня - это сохранять данные на диск в формате json как есть и давать имена от id полученной строки.
Дело в том что в json имеется до 5 картинок в base64,но и не только картинки в объекте имеются, но метод и задание.
У меня под боком 5 машин с нейронками,ocr-ми и всем прочим.Вот от них идет запрос на получение всех картинок сразу скопом (код php) и периодически вижу аптайм.Иной раз даже в phpmyadmin не могу зайти, приходится комментить код от клиента.Ток не смейтесь....просто администрирование сервера для меня темный лес.
Решил для себя сделать апи по распознаванию капчи, и тут же наступил на грабли.Вроде и сервер не слабый ,а всё уперлось как раз в mysql.
По поводу узкого места - уверен что как раз дело в сохранение этого json в базе.
На счет спецов......так где их найти? В кворке спецов 10 перебрал, давал доступ....ссылаются на ддос или как назло в этот момент все гуд.

Answer 4

[FanatPHP]

Мария Турова, а зачем ты это всё писал?