mysql и 2 500 000 записей

>LIMIT 990900, 100

Вот это убивает. Серверу приходится выбирать и сортировать миллион записей.

Нужно как-то уменьшать объём выборки.

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

Да, но по сути тут идет сортировка по id, который уникальный индекс да ещё и первичный ключ.

SQL (3.455904) SELECT count(*) AS count_all FROM `users`

Даже такой запрос производится 3 секунды.

Не может быть такое, что переход на postgres может в разы это ускорить?

> SQL (5.829610) SELECT count(*) AS count_all FROM `users` ORDER BY updated_at

Вот так по дате.

>SELECT count(*) AS count_all FROM `users` ORDER BY updated_at

>Вот так по дате.

Вот. А если ты сделаешь

SELECT .. FROM users WHERE first_letter = 'a';

где этот first_letter будет индексом - получишь приличное ускорение.

Я, вон, вчера только переделал выборку «Всех сообщений пользователя» на форуме с разбивки по страницам на разбивку по датам. Последние страницы (самые свежие) генерировались по минуте (1,5млн. записей), теперь, когда WHERE user_id = $id ORDER BY post_time был заменен на WHERE user_id = $id AND post_time WITHIN $d1 AND $d2 ORDER BY post_time - за десятые доли секунды. Естественно, что индекс двойной нужен в этом случае, (user_id, post_time).

Спасибо, попробую.

А стоит ли попробовать postgres, или заметных ускорений не даст?

> Вот это убивает. Серверу приходится выбирать и сортировать миллион записей.

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

>А стоит ли попробовать postgres, или заметных ускорений не даст?

Попробовать стоит, но на простых запросах, боюсь, большого выигрыша не получить.

>Первичный ключ же проиндексирован

Но там 2.5миллиона значений. А вот по первой букве - будет пара десятков значений.

>А стоит ли попробовать postgres, или заметных ускорений не даст?

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

Ну и что?

Есть индекс, если он вменяемый, то можно быстро спозиционироваться в нужное место и перебрать 100 значений. Даже если 2,5 миллиона ключей нельзя разом загрузить в память, размер элемента индекса известен и можно сразу загрузить нужный блок. Если природа индекса такова, что ключи в нем хранятся не в упорядоченном виде, надо поменять индекс.

Для такого запроса нужна оптимизация БД под тип запросов "выбрать все". Не знаю, можно ли это сделать в рамках MySQL или PostgreSQL, но в принципе это возможно.

>Есть индекс, если он вменяемый

LIMIT 990900, 100

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

Алсо

>User Load (0.045183) SELECT * FROM `users` WHERE (id between 990900 and 991000)

>> User.find(:all, :conditions => "id between 990900 and 991000").map(&:id).join(' ') == User.find(:all, :limit => "990900,100").map(&:id).join(' ') => false

> LIMIT 990900, 100

Если я ничего не напутал, это означает: спозиционироваться за 990900 запись и извлечь следующие 100.

Пусть у нас индекс - B+ дерево. Тогда в листьях лежат ссылки на записи, а листья упорядочены по ключу. Спозиционироваться в такой структуре несложно, размер листьев известен. В чем проблема-то?

В MySQL 5.0 Reference manual насчет LIMIT написано, что он задает смещение в записях. Т.е. 990900 здесь - не значение ключа, а кол-во записей, которые надо пропустить от начала.

> такой запрос производится 3 секунды.

И чем система занимается в течение этих секунд? Читает блоки данных, общается со свопом, грузит ЦПУ? Посторный запрос отрабатывается быстрее первого?

> Вот это убивает. Серверу приходится выбирать и сортировать миллион записей.

Я дико извиняюсь, в базах я ничего не понимаю, но почему ты решил что мускул будет что-то сортировать. LIMIT, как мне кажется, никак с сортировкой не связан. Об этом говорит мой опыт когда я на многомиллионные таблицы делал селект с limit :)

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

В данном случае (в первой теме) - не будет. Только выбирать миллион записей. Но такой вариант крайне редко встречается. Всегда почти упорядочивание то или иное нужно.

В моём случае упорядочивание не нужно. Нужно просто пропустить в дефолтном порядке.

Действительно, я не пойму, почему он три секунды определяет количество записей? Попробую на postgres поднять копию. А может причина в innodb? Как раз думаю на myisam перейти ради fulltext поиска.

Алсо в базе будет в результате около 18 млн записей. Растёт по 500 000 в сутки, страшно представить, что будет дальше.

ну и что? ln 2500000 ~ 14. проблема в другом

>А может причина в innodb?

Возможно. Вот myisam, первое обращение к базе (даже не таблице)
за пару месяцев. На нагруженной машине:

mysql> select count(*) from bors_search_words;
+----------+
| count(*) |
+----------+
|  1480134 | 
+----------+
1 row in set (0.00 sec)

Вот в активной базе:

mysql> select count(*) from posts;
+----------+
| count(*) |
+----------+
|  1578272 | 
+----------+
1 row in set (0.00 sec)

В общем, count - всё по нулям.

А вот конец большой несортированной выборки - уже как у тебя:

mysql> select * from bors_search_words limit 999900, 100;
...
100 rows in set (2.77 sec)

С сортировкой же - труба:

mysql> select * from bors_search_words order by id limit 999900, 100;
...
100 rows in set (17.78 sec)

>ну и что? ln 2500000 ~ 14

И что? Да, даже при выборке по индексу - 14 сравнений. Но это - 14 позиционирований на индексе с миллионами записей.

> SELECT * FROM `users` LIMIT 990900, 100
> Есть ли способ ускорить? Индекс на id есть.

А попробуй вот такое:

set @rownum:=0;
select cccs.* from (select @rownum:=@rownum+1 as rownum, cs.ID as ID FROM `users` cs ) ccs, `users` cccs where ccs.rownum<990900+100 and ccs.rownum>990900 and ccs.ID=cccs.ID;

Что у тебя получится по времени?

На самом деле покажи my.cnf

Так же если используешь innodb - сноси нафиг файловую систему. Зачем она?

Ну и конечно запросы лучше уточнять.

А ещё попробуй запрос типа

SELECT id FROM `users` LIMIT 990900, 100 
      ^^^^

Ну или что у тебя там проиндексировано.

> SELECT * FROM `users` LIMIT 990900, 100

А что это за запрос такой? Вы не забываете что результат ORDER BY без LIMIT не определен?

>А стоит ли попробовать postgres, или заметных ускорений не даст? постгрес - это хорошо. Для такой толстой таблицы он будет получше.

Для такого запроса как этот - cкорее похуже, бо он не имеет 'index only' и просмотрит не записи в индексе а полные записи...

> В моём случае упорядочивание не нужно. Нужно просто пропустить в дефолтном порядке.

То есть упорядочивание задано? А какого-нибудь PRIMARY KEY AUTO_INCREMENT нету? Если есть, да ты еще из таблицы не удаляешь (а если ты удаляешь как бы ты знал сколько надо пропустить?) то вместо LIMIT 990900 может написать

WHERE primary_key >= 990999

Тогда ему не надо будет перебирать миллион записей...

> А что это за запрос такой? Вы не забываете что результат ORDER BY без LIMIT не определен?

не совсем понял. можно подробнее раскрыть суть этого утверждения? как это - "результат ORDER BY без LIMIT не определён"? это почему это не определён? сколько раз пользовал ORDER BY без LIMIT и всегда результат был вполне определён.

// wbr

Автор, у тебя на сервере памяти много ?
Попробуй увеличить tmp_table_size
Например, tmp_table_size = 1024M

> не совсем понял. можно подробнее раскрыть суть этого утверждения? как это - "результат ORDER BY без LIMIT не определён"? это почему это не определён? сколько раз пользовал ORDER BY без LIMIT и всегда результат был вполне определён.

ой. я имел ввиду LIMIT без ORDER BY :-)

короче, table partitioning тут более чем уместен.

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

// wbr