Снижение нагрузки. Разбиение одной большой таблицы на маленькие

[Вася Патриков]

Снижение нагрузки. Разбиение одной большой таблицы на маленькие

Здравствуйте все. Вопрос на самом деле по чистому MySQL.
Однажды, правда очень непродолжительно, работал над проектом, в котором добрый дядя ведущий программист делал с БД следующую интересную вещь - брал, к примеру, таблицу `users` и делал из неё таблицы `users_1`, `uses_2`, ... причём так, что в каждой таблице хранилось не более, скажем, 10 тысяч строк. Ограничение по количеству строк и прозрачное управление всей этой "конструкцией" контролировалось дополнительным программным слоем. Аргументировал он это всё тем, что так, якобы, сервер баз данных "лучше" работает, и если запихать всё это обратно в одну таблицу, то работать он станет
"хуже". Пишу в кавычках, потому что подразумевается снижение/повышени производительности и т.п. Вообщем таким образом он искусственно увеличивал его производительность.
Сейчас у меня возникла такая же необходимость. При запуске в консоли top на серваке, видно что mysqld кипит и занимает в среднем 200% CPU. Приложение, которое он поддерживает жутко тормозит.
В связи с этим имеется ряд вопросов - правда ли всё это ? Отвечая на свой первый вопрос - почему это правда ? )) Связано ли это с каким либо типом таблиц ?

P.S. Из неполноценных и разрозненных знаний - таблицы innoDB поддерживают блокировку на уровне строк, MyISAM - на уровне таблиц. Не решится ли моя проблема трансформированием таблиц MyISAM в innoDB (хотя знаю, что MyISAM быстрее)... или я что то как всегда путаю ?

 

[whirlwind]

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

 

[phprus]

Вася Патриков
Включай лог медленных запросов и вооружившись EXPLAIN'ом смотри что тормозит.

 

[Вася Патриков]

Ммм... хорошо, отложим пока вопрос про разбиение одной большой таблицы.
Как выявлять эти узкие места при работе с MySQL ? Всё что я об этом знаю - это возможность посмотреть лог медленных запросов, который я пока что в глаза не видел. Какие ещё есть способы выяснить, почему mysqld потребляет так много ресурсов ?

P.S. Ваше оптимальное решение не очень гуманно - я ещё так молод... )))

-~{}~ 30.08.09 19:49:

phprus
о, пока писал, вы уже ответили...

 

[whirlwind]

SHOW PROCESSLIST, EXPLAIN, SHOW INDEX FROM table

 

[DiMA]

Саша, чего ты как маленький?

1. смени на инно дб

2. разбивать тебе таблицы, как было с шардингом - не нужно

> посмотреть лог медленных запросов, который я пока что в глаза не видел

3. значит выясни что это и исправь тормозные запросы (просто добавь индексы и грамотно части в WHERE между собой размести)

4. изучай EXPLAIN, если запрос не стал летать

5. кеширование

 

[korchasa]

Если найдутся запросы, которые делают JOIN на все эти таблички, то дядю все таки казнить.

 

[melo]

http://habrahabr.ru/blogs/webdev/66151/

тут есть про разбиение таблиц.

 

[zerkms]

melo
у человека в спине топор, а вы предлагаете обойтись анальгином.

 

[Sawa]

Не совсем верно, изначально не ясно почему разбито по 10к строк, условие так же не оговаривает как реализован был слой управления.
Условлюсь что это абстрактные значение и условие. Да , при больших обьемах данных невозможно работать с большой таблицой. При необходимости апдейтить, добавлять данные, делать сложные выборки, таблица все время будет заблокированной, и нагрузка на процессор будет возрастать. Программный слой может предоставлять прозрачный интерфейс к этим таблицам, который будет реально снижать нагрузку разбивая общую таблицу на более мелкие части, а следовательно и запросы к базе ( которые можно кэшировать ).
Если работает, зачем трогать ? (с)

 

[FractalizeR]

Автор оригинала: Sawa
....таблица все время будет заблокированной, и нагрузка на процессор будет возрастать.

Это касается только MyISAM. InnoDB не лочит таблицу при записи в нее.

 

[korchasa]

Автор оригинала: Sawa
Не совсем верно, изначально не ясно почему разбито по 10к строк, условие так же не оговаривает как реализован был слой управления.

Какие могут быть причины смешивать данные и метаданные?

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

Насколько большой?

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

InnoDB, не?

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

И увеличивать сложность системы. Любые сводные запросы будут приводить к union'ам и временной таблице. Изменение метаданных перестанет быть атомарным.

Если работает, зачем трогать ? (с)

Как пишет ТС:

Сейчас у меня возникла такая же необходимость. При запуске в консоли top на серваке, видно что mysqld кипит и занимает в среднем 200% CPU. Приложение, которое он поддерживает жутко тормозит.

 

[Sawa]

Это касается только MyISAM. InnoDB не лочит таблицу при записи в нее.

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

Насколько большой?

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

И увеличивать сложность системы. Любые сводные запросы будут приводить к union'ам и временной таблице. Изменение метаданных перестанет быть атомарным.

explain говорит что при выполнении union, запрос оптимизируется, при включенном кеше запросов большая часть может просто выгребаться с кеша.
временных таблиц быть не может, если результаты вмещаются в память... оптимизация запросов ?? добавление лимитов ? все это находится в мане ))

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


Разработчик прежде чем что-то делать, думал. Может плохо думал, но на момент реализации это был хороший вариант который работал. Задача стоит в оптимизации, где нет "правильных" решений, есть реализации которые подходят под текущую ситуацию. ИМХО

 

[FractalizeR]

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

http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB

Лучше бойтесь MyISAM. Когда это MyISAM таблицы стали более надежными, чем InnoDB?
А если InnoDb и свалилась так, что никакие утилиты не помогают, то только, разве что, из-за физического дефекта носителя. Но даже в этом случае надо просто погуглить: http://code.google.com/p/innodb-tools/

 

[korchasa]

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

Эрм, MyISAM на критичных к целостности данных? А транзакции видимо реализованы на уровне приложения?
И какие рековер утилиты не спасли?

Автор оригинала: Sawa миллионы записей, по 300-400 мегов таблица. если я правильно понимаю топикстартера, в его варианте обьединив таблицы мы можем получить не приемлемые значения, которые в память сервера не поместятся

Т.е. отдельно все помещаются, а вместе нет?

Автор оригинала: Sawa explain говорит что при выполнении union, запрос оптимизируется, при включенном кеше запросов большая часть может просто выгребаться с кеша.
временных таблиц быть не может, если результаты вмещаются в память... оптимизация запросов ?? добавление лимитов ? все это находится в мане ))

Угу, только гадсткая надпись filesort все портит

mysql> explain (select * from product where id = 1) UNION ALL (select * from product where id = 2) order by id;
+----+--------------+------------+-------+---------------+---------+---------+-------+------+----------------+
| id | select_type  | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra          |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+----------------+
|  1 | PRIMARY      | product    | const | PRIMARY       | PRIMARY | 4       | const |    1 |                | 
|  2 | UNION        | product    | const | PRIMARY       | PRIMARY | 4       | const |    1 |                | 
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL    | NULL    | NULL  | NULL | Using filesort | 
+----+--------------+------------+-------+---------------+---------+---------+-------+------+----------------+
3 rows in set (0,00 sec)

Автор оригинала: Sawa
Согласен что в целом сложность системы увеличивается, увы, простые решения не всегда возможно применять. Мы тут просто рассуждаем над абстрактной задачей =) было бы больше данных... ээх

Смешивание данных и метаданных это хак, которого надо по максимуму избегать. В данном случае этот хак можно было заменить штатными средствами СУБД.

Автор оригинала: Sawa Разработчик прежде чем что-то делать, думал. Может плохо думал, но на момент реализации это был хороший вариант который работал. Задача стоит в оптимизации, где нет "правильных" решений, есть реализации которые подходят под текущую ситуацию. ИМХО

Мы видимо нашли разработчика?

 

[whirlwind]

Вот, а пользовались бы все внешними ключами и половины проблем небыло бы. Индексы бы вам автоматом создавались. А пока неучи предпочитают фантазировать велосипеды, мы без работы не останемсо

 

[korchasa]

Автор оригинала: whirlwind
Вот, а пользовались бы все внешними ключами и половины проблем небыло бы. Индексы бы вам автоматом создавались. А пока неучи предпочитают фантазировать велосипеды, мы без работы не останемсо

Хз, я их до сих пор боюсь. Мне привычнее их в коде держать, чем в схеме БД.

 

[Mols]

korchasa
Да чего бояться? Отличная вещь. Присутствует во всех более менее развитых СУБД. Намного всё удобнее становится. А без внешних ключей иногда таких чудес можно насмотреться.... такие блин выдумывают системы собственных "внешних ключей". Зачем хз. О полноценности таких систем я вообще молчу.

 

[whirlwind]

korchasa не совсем понял чего ты боишься. Самая банальная ситуация где тупо забываешь в бизнес-модели проконтролировать удаление одних элементов, на которые ссылаются другие. При создании тех, других не забываем прописать внешний ключ, после чего любые забывания вылазят sql ексепшенами. Никто не говорит про замену кода, просто средство доп контроля, этакая защита от дурака.

-~{}~ 07.09.09 22:38:

PS. тут логика простая. Тсли таблица с 1м записей, ну вряд-ли эти 1м сразу нужны. Если 1м не нужны, значит на таблицу есть ссылка. Если есть ссылка, значит есть джойн. Джойн без индекса тормозит по дефолту. FK=индекс и +читабельность схемы. Вывод: FK рулезь.

 

[korchasa]

Автор оригинала: whirlwind
korchasa не совсем понял чего ты боишься. Самая банальная ситуация где тупо забываешь в бизнес-модели проконтролировать удаление одних элементов, на которые ссылаются другие. При создании тех, других не забываем прописать внешний ключ, после чего любые забывания вылазят sql ексепшенами. Никто не говорит про замену кода, просто средство доп контроля, этакая защита от дурака.

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