Приветствую, комрады.
Вчера столкнулся со странной проблемой:
Есть база данных, состоящая из идентификатора, города (text), набора числовых полей (в каких-то только 2 разных значения, в каких-то 3 (tiny int, small int), в одном поле может быть 10 000 000 разных значений - соответственно тип инт). + один столбец с длинным текстом.
Всего в базе 9 000 000 строк и весит она 1,4 Гб.

Выборка из такой базы по какому-нибудь полю (или по их совокупности) занимает порядка 10-20 сек (vps 1 ядро, 1гб ram)
Я хотел ускорить выборки путём добавления индекса.
Однако после добавления хотя бы одного индекса время выборки увеличивается до 20-40 минут. При этом я понимаю, что индекс не обязан обязательно ускорять работу, если выбран неправильно. Я специально создавал иднекс по одному только полю и по нему же делал выборку - результат ужасный - 20-40 минут.
Также создавал индекс на поле которое int и делал по нему order by - то же самое, индекс не только не ускоряет, но и замедляет выборку с 20 секунд до 40 минут. При этом наблюдается 100% загрузка ядра (50 процесс, 50 ввод/вывод).
Таблицы Inno DB.
Я думал, что дело в настройках mysql, но я уже там перепробовал всякие разные значения для всевозможных буферов, результат одинаков.
В чем может быть дело, ума не приложу. Есть какие-нибудь идеи?

SELECT * FROM ms WHERE `type2` = "1" ORDER BY `count` DESC LIMIT 0, 100000

Сейчас индексов нет, только первичный ключ. Потому что с ними невозможно работать.
Пробовал для запроса выше ставить индекс на поле type2 только. Результат - 40 минут запрос.
Пробовал задавать только индекс для поля count. - то же самое.
Когда индексы убираешь - скорость возвращается к 20-40 секундам на запрос.

innodb_buffer_pool_size был сначала 128М. Потом поставил 256М, сейчас стоит 512М - разницы никакой. Точнее есть незначительная разница без индексов. При использовании хотя бы одного индекса - время более 20 минут сразу.


CREATE TABLE `ms` (
`g_id` int(11) NOT NULL,
`name` text NOT NULL,
`city` text NOT NULL,
`count` int(11) NOT NULL,
`type1` text NOT NULL,
`type2` smallint(6) NOT NULL,
`url` text NOT NULL,
`type3` tinyint(4) NOT NULL,
`type4` tinyint(4) NOT NULL,
`type5` tinyint(4) NOT NULL,
`contacts` text NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Индексы сохранённых таблиц
--

--
-- Индексы таблицы `ms`
--
ALTER TABLE `ms`
ADD PRIMARY KEY (`g_id`);

miksoft,

да, там значений мало. Я предполагал, что индекс может не помочь, но я никак не ждал ухудшения производительности в 50 раз...
Такое ощущение, что у меня не прописан в конфиге какой-нибудь буфер, который работает с индексами и для них тупо нет памяти. Хотя вроде как я читал, что для inno db всё решает этот самый innodb_buffer_pool_size.
Но должна же быть какая-то причина.
В принципе, я может быть вообще оставил бы только один индекс - по полю count, упорядоченный по desc, потому что у меня ORDER BY `count` DESC LIMIT 0, 100000 - в каждом запросе. А остальное может меняться, уловие where может быть разным.
И значений в count много. Так что индекс должен был бы помочь, но там точно такая же ситуация - только я его устанавливаю, запрос длится полчаса.....
Да, кстати, когда индексы еще были, я делал EXPLAIN - там всё было "нормально" - количество рядов уменьшалось, индекс типа использовался и все такое.... Да только это только в EXPLAINE все было красиво.....

Alex_Ustinov,

Я пробовал эту таблицу наращивать до 6 ГБ. И тогда без индексов на той же ВПСке она со скрипом но работала. Запросы шли по 2 минуты, но они выполнялись. Естественно она не влезала, даже свопа столько нет, чтобы засунуть туда ее всю.
Но вот таблица на 1.4 с индексом весит где-то 1.7 и время запроса 40 минут, тут явно не только в памяти дело.

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

.

miksoft,

Кажется я начинаю понимать:
Сейчас скопировал всю базу на бесплатную впску на амазоне, там стоит веста с дефолтными настройками ну и ограничения всякие по времени ядра и т.п., но памяти тоже 1 Гб.
Так вот, сделал запрос - а он выполнился за одну секунду.
Я было обрадовался, однако как мне видится всё дело в LIMIT:
На базе без индексов я выполняю запрос с любым LIMIT за одинаковое количество времени - 20 сек, допустим.
А если есть индекс, то запросы с LIMIT 1 выполняются вообще мгновенно... и так до LIMIT 100 примерно. LIMIT 1000 выполняется уже все те же 20 сек.... А дальше....
короче индексы не ускоряют работу, если нужно выбирать много значений за раз...
Может быть я не прав, но пока такой вывод напрашивается.

Хех, как я и говорил, индекс используется, но когда нужно сделать выборку в 100 000 записей, быстрее получается без индекса.
Здесь я оставил только индекс count.

Alex_Ustinov,

куда это озу напихать?
исключительно в innodb_buffer_pool_size?
могу попробовать на тестовой ВПСке

Короче, если кому интересно: потестировал я производительность mysql на другой концигурации.
Можете не соглашаться, но я сразу к выводам:
1. mysql может распараллеливать по ядрам только разные запросы. Один запрос будет выполняться на одном ядре.
2. директива innodb_flush_method = O_DIRECT - зло. Она замедляет работу (тогда как пишут много где, что она предотвращает кэширование того что уже закэшировано). - я её у себя удалил.
3. память в innodb_buffer_pool_size можно не выделять (и не писать этот пункт вообще) - если память есть, всё закэшируется в ОС.
4. вот этот конфиг я взял себе (на свою ВПСку) по итогам и уменьшил тем самым время запроса с 20с. до 13с.:
авторskip-external-locking
key_buffer_size = 16M
max_allowed_packet = 16M
table_open_cache = 1024
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
innodb_file_per_table

Этот конфиг предлагает Vesta CP на 2Гб конфигурацию. После того как я избавился от ненужных строк в своем и поставил этот, стало работать быстрее, даже на моем 1Гб, при этом памяти много не выделяется. По итогам нескольких дней работы, я подкручу потом некоторые параметры (чтобы было годно и для параллельной работы обычного сайта на CMS).

Ну вот. Больше уже похоже ничего улучшыть нельзя в моем случае.
Тестовая ВПСка показывала результат 7с (там где у меня сейчас 13, а было 20+) - скорее всего изза того что база полностью закэшировалась + там сам процессор случился пошустрее.

Может быть, у вас innodb_flush_log_at_trx_commit =1 ?
стояло 2. теперь убрал этот пункт, по умолчанию что - не знаю, но стало лучше.

автор`type2` smallint(6) NOT NULL,

кстати, будет ли разница если в запросе `type2` = "1" заменить `type2` = 1 ?
проверил, разницы в производительности никакой.