Производительность и надежность MySQL при больших объемах данных

[baldr]

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

Давно использую MySQL по работе в различных небольших проектах.. В основном из-за простоты связки с PHP..
Встала задача сбора и анализа логов с различных серверов.. Из-за некоторых требований было решено реализовать это так - с некоторой периодичностью выделенный сервер собирает порции новых записей из логов удаленный серверов, загоняет их в базу, а потом, по запросу все анализируется и выводится в виде графиков и тп...
По привычке выбрал MySQL, но после нескольких дней засомневался в правильности...

Сейчас база занимает около 5 Гб, в ней содержится около 19 млн записей (И это еще даже не десятая часть от желаемого объема!), каждый день прирастает примерно по 2 млн записей (потом будет еще раза в 2 больше)..

Меня беспокоит вопрос - будет ли база стабильно и достаточно быстро работать на пике, когда объем вырастет раз в 10.. По моим ощущениям, скорость, как минимум, добавления записей, упала раза в 2 по сравнению с пустой базой..
При этом нагрузка процессора (Dual-Core AMD Opteron 1220, 2.8 GHz) приближается к 100% при добавлении в 2 потоках через ODBC-коннектор.
Памяти у серванта 4 Gb, из них более 1.5 Gb отдано для MySQL.. Сервер под Win2003 SE SP2.
Кроме этой базы тут же крутятся базы еще нескольких нужных проектов, но их нагрузка минимальна, однако не хочется, чтоб упавший сервант погреб их под собой..

Собственно вопрос такой - если ставить на этот же сервер другую базу - например MSSQL - будет ли это более выигрышным? В перспективе можно организовать сервер с Oracle 10 (на него лицензия какая-то есть у компании), но также нужно сравнить - будет ли выгода?

Приоритеты в порядке убывания:
* Цена решения
* Надежность работы остальных компонентов на этом же сервере (чтоб не влияло на них и не отбирало 100% CPU)
* Хранение больших объемов (скажем, до 80 Гб и 500 млн записей)
* Скорость добавления новых записей

Временем анализа можно пожертвовать - оно может немного подождать при построении графиков, но также в разумных пределах..

[Dale]

Скажем так, у меня работа на MS SQL V10 с таблицами по 500 млн. записей и базами свыше 150 Gb особых проблем не вызывает. Скорость добавления записей ~3000/сек.

[Джон]

ИМХО решающий фактор всё-таки железо. Память, количество процессоров, быстрые диски.

[RXL]

Лучше Oracle. Он с задачей справится, но место на диске кушает хорошо.

Вопросы:
0. Версия MySQL.
1. Какой тип таблиц использовали?
2. Не думали делать анализ не по запросу, а онлайн (или периодически) и хранить только результат анализа?
3. Каковы настройки MySQL? Буфера и т.п. Лучше весь конфиг покажи.

Добавление данных эффективнее в режиме bulk: сперва накопить N строк, а потом вставлять единым INSERT. Эффект прежде всего в более редком перестроении индексов.

[McZim]

1. Уверен что оптимально организовал хранение данных в таблицах?
2. Уверен что оптимально выбрал набор индексов?
3. Уверен что оптимально строишь SQL?
4. Уверен что оптимально организовал хранение на уровне ОС?
5. Уверен что оптимально работаешь с данными на уровне ОС?

Что касается того что работа с БД заметно снизилась при ее росте. Однозначно можно сказать что оптимальность работы и хранения данных храмает!

RXL, по поводу съедания места ораклом не понял? Заполнения справочников статистикой, по отношению к объему данных, мизерно.

[baldr]

Лучше Oracle. Он с задачей справится, но место на диске кушает хорошо.

Наверное, лучше.. Но под него, видимо, нужен отдельный сервак, причем, скорее всего не на Windows.. Пока же в наличии то что есть.
Проект пока пилотный и если получится что-то интересное - сервак могут дать.. Но администрировать Oracle и *nix я сейчас сам не готов

Вопросы:
1. Какой тип таблиц использовали?

InnoDB.

2. Не думали делать анализ не по запросу, а онлайн (или периодически) и хранить только результат анализа?

Думали.. Проблема в том, что пока хочется загнать в базу логи, например, за месяц, потом поиграть с запросами, посмотреть что интересного можно из этого получить.. Также есть желание показывать не только графики с ошибками, но и куски разных логов за этот промежуток времени.
Если будет сильно тормозить, то наиболее тяжелые анализы сделаю кэшируемыми.

3. Каковы настройки MySQL? Буфера и т.п.

см. ниже кусок из my.ini

Добавление данных эффективнее в режиме bulk: сперва накопить N строк, а потом вставлять единым INSERT. Эффект прежде всего в более редком перестроении индексов.

Спасибо, это *очень* хорошая мысль.. Так и сделаю.

Код: (INI) my.ini

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections = 1000

query_cache_size=0

table_cache = 128

tmp_table_size = 200M

thread_cache_size = 12

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=369M

key_buffer_size=32M

read_buffer_size = 256K
read_rnd_buffer_size = 512K

sort_buffer_size=256K

innodb_additional_mem_pool_size = 50M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size = 40M

innodb_buffer_pool_size = 1200M

innodb_log_file_size=586M

innodb_thread_concurrency = 16

innodb_file_per_table

[baldr]

MySQL 5.1.45
McZim, да, оптимальность хромает. Структуру таблиц и индексов приводить не буду - у вас своей работы хватает, чтобы в моей разбираться.. Структура будет, скjрее всего, меняться.

[RXL]

Версия устарела. Рекомендую апгрейд до 5.1.49.


innodb_flush_log_at_trx_commit=2
Сброс логов будет реже. Это типа должно снизить надежность, но на практике достаточно более-менее надежной машины (хотя бы наличие UPS).

innodb_thread_concurrency = 4
Рекомендуемая формула: 2 * число ядер проца.

Попробуй partitioning. Минимизируй количество индексов.

Ну, а памяти мало никогда не бывает...


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

[McZim]

Макс, я про заполнение блоков - в них много свободного места остается.

В блоках данных таблицы или индексов? Блоки таблицы настраиваются! Блоки индекса заполняются по мере того, поместится туда инфа или нет и можно ли ее в этот блок записать. Всегда можно сделать compress и rebuild.

Это все с точки зрения Oracle.

Меньше блоков - меньше IO

Ну ты даешь

[RXL]

Меньше блоков - меньше IO

Ну ты даешь

Разве нет?...

[McZim]

RXL, нет Ты путаешь, наверно, количество блоков таблицы и количество блоков при чтении!

[Dimka]

В MS SQL главное не Express-версию, там лимит на базу 2 Гб и однопроцессорная она. Это значит, что MS SQL будет платным.

Если база весит 50 Гб и постоянно растёт... по-моему для оперативной работы с такими объямами становится важной регулярная дефрагментация диска. Не вставок ради, но хотя бы ради запросов.