mysql - периодическое просаживание производительности - как лечить?

може другие процессы отбирают ресурсы системы

посадил atop в screen, может чего подскажет.

Посмотри, например, при помощи munin, не возрастает ли при этих проблемах нагрузка на жёсткий диск.

sync ?

Прошу в студию конфиг, версию mysql (5.0, 5.1 или 5.5?), сам запрос и 'show create table table_name' структуру таблицы.

Вероятно, там какая-то беда с индексами и иногда оптимизатор может захотеть сделать full table scan, но с чего вдруг - хз.

да, навешиваю мониторингов уже

раз в несколько дней? врядли. да и так долго... не думаю.

да, конечно:

CREATE TABLE `SensorData` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `SensorID` bigint(20) NOT NULL,
  `EventTime` int(11) NOT NULL,
  `ReceiveTime` int(11) NOT NULL,
  `Hour` int(11) NOT NULL,
  `Flags` tinyint(3) unsigned NOT NULL,
  `ADCMask` smallint(5) unsigned NOT NULL,
  `DigitalInput` smallint(6) unsigned NOT NULL,
  `ServerID` tinyint(3) unsigned NOT NULL DEFAULT '1',
  `ADCVal0` smallint(6) unsigned NOT NULL,
  `ADCVal1` smallint(6) unsigned NOT NULL,
  `ADCVal2` smallint(6) unsigned NOT NULL,
  `ADCVal3` smallint(6) unsigned NOT NULL,
  `ADCVal4` smallint(6) unsigned NOT NULL,
  `ADCVal5` smallint(6) unsigned NOT NULL,
  `ADCVal6` smallint(6) unsigned NOT NULL,
  `ADCVal7` smallint(6) unsigned NOT NULL,
  `ADCVal8` smallint(6) unsigned NOT NULL DEFAULT '65535',
  `ADCVal9` smallint(6) unsigned NOT NULL DEFAULT '65535',
  `ADCVal10` smallint(6) unsigned NOT NULL DEFAULT '65535',
  `ADCVal11` smallint(6) unsigned NOT NULL DEFAULT '65535',
  `ADCVal12` smallint(6) unsigned NOT NULL DEFAULT '65535',
  `ADCVal13` smallint(6) unsigned NOT NULL DEFAULT '65535',
  `ADCVal14` smallint(6) unsigned NOT NULL DEFAULT '65535',
  `ADCVal15` smallint(6) unsigned NOT NULL DEFAULT '65535',
  PRIMARY KEY (`ID`),
  KEY `idxSelect` (`SensorID`,`Hour`)
) ENGINE=MyISAM AUTO_INCREMENT=311695355 DEFAULT CHARSET=utf8 MIN_ROWS=25000000 MAX_ROWS=4294967295 PACK_KEYS=1 CHECKSUM=1 ROW_FORMAT=FIXED |

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

конф: http://pastebin.com/xZHxJtrF (SHOW VARIABLES)

запрос - самый обычный insert, оформленный в виде процедуры с параметрами, которые подставляются insert'у в values. подумываю над insert delayed и пересматриваю status в phpmyadmin. есть красные поля:

Innodb_buffer_pool_reads     2.3 M
Handler_read_rnd	37.2 M
Qcache_lowmem_prunes	95
Slow_launch_threads	11
Binlog_cache_disk_use	24.4 k
Created_tmp_disk_tables	6.9 k
Opened_tables	80.7 k	
Table_locks_waited	472.9 k

This MySQL server has been running for 91 days, 7 hours, 53 minutes and 37 seconds

dib2

в таблицу, которая крутится на MyISAM. Таблица соотв. великовата, ~200млн записей. И все бы ничего, работает, в среднем до 5мс запрос укладывается, но иногда, неуловимо и внезапно, примерно раз в 2-3 дня БД встает колом, и запись в эту таблицу занимает до 40секунд, продолжается это несколько минут, и вновь все восстанавливается.

NoWay. Это проблема алгоритма.

утверждаете что большие таблицы с миллионами записей - это NoWay?

dib2

утверждаете что большие таблицы с миллионами записей - это NoWay?

dib2

утверждаете что большие таблицы с миллионами записей - это NoWay?

нет. просто у любого алгоритма есть среднее время выполнения, а есть максимальное. Например та же qsort сортирует записи со скоростью пропорциональной O(N*log(N)), но иногда, очень редко, скорость пропорциональна O(N*N). Считаем:
у нас есть миллион записей и комп, который выполняет миллион операций в секунду (ну сравнить/переставить, как в qsort), нам в среднем понадобится 20 секунд, ибо log2(10e6)=20 (почти точно). Это для сортировки миллиона записей, например для создания индекса с нуля. Проста вставка займёт всего 20мкс (она пропорциональна log2). Но в некоторых случая сортировка займёт не 20 секунд, а примерно 12 дней. Впечатляет? Тоже и со вставкой - не 20мкс, а 1 000 000мкс.

Есть конечно другие алгоритмы, которые в среднем хуже qsort, но за то у них нет худшего случая. Вот только в БД не совсем сортировка, и оптимизируют БД не по вставке, а по выборке... Разве что свою писать... Ну или искать что-то специальное. Самое простое - писать тупо в хвост файла. Конечно выборка сильно пострадает, но я не знаю, важно-ли это для ТС, а если да, то на сколько.

это было бы применимо, в случае предсказуемого проявления этих проблем в течении времени, но они абсолютно рандомны.

писать в файлы уже было - не вариант.

dib2

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

В красно-чёрном (2-3) дереве этот эффект строго детерминирован - там высота дерева может быть от log(N) до 2log(N), и если вставлять узлы с одной стороны (как всегда и бывает), то одна из ветвей(скажем левая) будет тяжелеть. А другая - нет. В конце концов, когда левое поддерево будет ровно вдвое выше правого, нам придётся пересобрать всё дерево. Потому используют несколько усложнённые алгоритмы, у которых есть хитрый план. Вот только это совсем не лечит ситуацию, лишь только затрудняет её строгий математический анализ. А эффект - остаётся. Только несколько размазанный. Вместо 12 дней регулярных тормозов раз в году, мы получаем неделю в апреле, день в ноябре, и 4 дня под новый год. Ну в упрощённом виде.

Вам надо менять алгоритм, конечно не на один файл, но что-то такое в ту сторону. Высота дерева - это ключевой параметр, с одной стороны она может быть равна log(N), и тогда будет самая быстрая выборка, при этом вставка будет пропорциональна N. Можно сделать высоту побольше - выборка станет пропорционально дольше, но и вставка - быстрее. Терминальный случай - дерево высотой N. Линейный список. Вставка мгновенна, но выборка пропорциональна N.

БД ориентированны именно на выборку.

1. key_buffer_size 67108864 - маловато будет для 200м записей в базе. Вполне вероятно, что индекс не помещается в памяти. А может и помещается.

2. пусти в базе mysqltuner.pl, он тоже может что интересного найти.

Вам надо менять алгоритм, конечно не на один файл, но что-то такое в ту сторону

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

БД ориентированны именно на выборку.

я не верю, что для современных БД пара сотен млн. записей и вставка раз в секунду - это какая-то проблема. В независимости от ориентации.

1. аха, попробую поднять
2. да, спасибо, пошел гонять

dib2

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

в ФС тоже используются деревья, однако положение узла в дереве зависит не от имени файла, а от хеша от этого имени. По этой причине дерево в ФС растёт равномерно, и его высота почти равна log(N). Это позволяет быстро вставить эл-т, быстро его удалить, и кроме того - быстро найти.

К сожалению, в БД данный метод не подходит. При запросах к БД почти всегда требуется найти выборку, а не один элемент, т.е. не просто 17, а числа от 17 до 100, причём ещё и в порядке возрастания. Этот самый порядок как раз и нарушен до неузнаваемости хешированием. Именно по этой причине, команде ls приходится сортировать полученный список файлов, что занимает значительное время. Как вы думаете, зачем в ls ключ -U?

Кстати говоря, вы не желаете попробовать в качестве СУБД ФС? Я не говорю о самом примитивном подходе - один файл — одна запись, в одном файле можно уместить несколько записей, например 1024. А 200 000 файлов на ext3 уживаются очень прекрасно. Это сработает в том случае, если вам не нужна выборка, а нужна конкретная запись при запросе на чтение.

я не верю, что для современных БД пара сотен млн. записей и вставка раз в секунду - это какая-то проблема. В независимости от ориентации.

вы можете (не)верить во что угодно. Это никак не влияет на законы природы и фундаментальные свойства алгоритмов.

Может перекинуть ранние записи в архивную таблицу и делать это регулярно?