InnoDb + таблица 1Gb + Timestamp = тормоза

[newARTix]

Вопрос для гуру:
Есть MySQL на винде (денвер), там база данных и в ней табличка InnoDb.
В табличке картинки в поле Blob, и дата их закачки в поле Timestamp. Картинок около 24000, вся таблица весит около 1 гигабайта.
Проблема: при выборке по условию Timestamp >= 'какая-то дата' сервак тупо вешается. Пытаюсь сделать индекс по этому полю, тот же результат - зависание службы.
В чем может быть причина, и как исправить с минимальными затратами? Машинка слабенькая, дампить туда-сюда гигабайты по полчаса, в поисках истины, не охота.

Более подробно конфиг таблицы и сервера сейчас не скажу, машина опять висит

 

[c0dex]

newARTix
НЕ надо хранить картинки в базе. Соответственно при попытке сделать ALTER TABLE не мудрено что слабенький комп вешается. Постеменно скриптом попытаться вынесли картинки на диск, если получится.

 

[newARTix]

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

Так-то в принципе понятно, что надо просто сделать новую таблицу уже с индексом, да перенести туда "построчно" записи. Вот только будет ли толк?

 

[c0dex]

newARTix
Нет, не поможет. Точнее поможет, но весьма ограниченно. Рано или поздно у тебя все встанет раком.

 

[newARTix]

понятно, ну выбора у меня все-равно нет... даже если не поможет, это надо доказать.

 

[prolis]

праймари кей есть в таблице? можно сначала узнать минимальный ид записи для таймстампа, а потом по ид>макс(ид) тащить блобы

 

[c0dex]

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

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

 

[grigori]

это авторам mysql надо руки отрывать за то что запрос по большой таблице, которая не помещается в память, может повесить сервер. Другие субд просто выполняют его за пол-часа.

у ТСа рабочая проблема, виновные давно уволены, че вы придираетесь?
Надо дебажить - смотреть на запросы, размер оперативки, размер данных, индекса.
Может, таблицу можно вынести на 2й винт, партицировать как-то, заменить на view и через хранимую процедуру оптимизировать.

Сначала посчитайте что дешевле: терпеть сбои, переписать приложение, перенести на сервер с большей оперативкой, или неделю потратить на оптимизацию базы.

 

[newARTix]

А почему никто не вспоминает про MyISAM? Я не сказал, но возможности InnoDb в данном случае фактически не используются, так может ну его...

 

[grigori]

c0dex
в объемной таблице InnoDb, установка индекса на поле типа timestamp поможет ли вообще?

Зависит от нескольких факторов. Если индекс будет в оперативке, если количество передаваемых данных небольшое - возможно.
Если результат запроса - куча данных, которые перед отдачей будут писаться в временную таблицу - ботлнек будет в винте.
Если на сервере еще че-то крутится и индексы в оперативку не влезут - жопа mysql не умеет жить с индексами вне оперативки.

А почему никто не вспоминает про MyISAM? Я не сказал, но возможности InnoDb в данном случае фактически не используются, так может ну его...

не используются - и разницы не будет

 

[MiksIr]

Может попробовать создать вторую таблицу с id, timestamp, туда insert ... select и потом индекс по таймстампу.

 

[newARTix]

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

 

[grigori]

а что, размер 2х индексов (timestamp+id) будет меньше, или запрос по полю с названием id будет быстрее?
timestamp хранится как int4

 

[newARTix]

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

 

[grigori]

по сравнению с выборкой по индексу, который составлен по полю timestamp - нет, rtfm по работе индексов

 

[newARTix]

да, совсем уже крыша поехала к концу недели, всем спасибо

 

[MiksIr]

а что, размер 2х индексов (timestamp+id) будет меньше, или запрос по полю с названием id будет быстрее?

Оно будет понятнее и читабельнее.

 

[grigori]

Chusha MiksIr
чукчинечитатели? разве у человека проблема с пониманием? структура таблицы из 3х полей - слишком сложная?

Вы когда-нибудь работали с таблицей, размер которой в разы больше, чем объем свободной оперативки?
У вас умирал сервер от одного запроса? Вы часто открыто посылаете начальника потому что не можете предложить решение?
У всех еще много хороших идей, ога.

 

[MiksIr]

Да не печалься, и у тебя когда-нибудь появятся хорошие идеи. ОГА. Пока, правда, в этой теме ни одной не замечено.

 

[newARTix]

Да ребят, вопрос простейший на самом деле, извиняюсь что вообще побеспокоил, устал просто за неделю Я сразу же сказал (может не сильно четко), что индекса на поле timestamp нет. И естественно запрос вешал базу ибо поиск по всему гигу шел. Теперь я индекс сделал и всё шоколадно Вот структура таблицы (которая была):

CREATE TABLE IF NOT EXISTS `photo` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`photo` mediumblob,
`size` int(10) unsigned DEFAULT NULL,
`name` varchar(45) DEFAULT NULL,
`idObject` int(10) unsigned DEFAULT NULL,
`datemodify` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`datecreate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `idObject` (`idObject`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=21549 ;

По автоинкременту нетрудно понять сколько строк. Объем таблицы - 1Гбайт. Примерный размер строки ~50кб. Запрос:
SELECT * FROM `photo` WHERE `datemodify`>='2010-01-01 00:00:00'
Такому условию удовлетворяло примерно 100 строк. Но так как поиск велся вообще без индекса, то база вешалась.
Теперь индекс есть, и запрос хотя бы выполняется Да медленно, одна-две десятые секунды, но это терпимо, все-равно он будет выполняться по крону раз в пять минут, там больше будет проблем с выгрузкой этих фоток уже на сайт... Да и потом еще чего-нибудь покрутим и вообще можно будет про нее забыть.

Всем хороших выходных