LINUX.ORG.RU

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


0

0

User Load (4.364266) SELECT * FROM `users` LIMIT 990900, 100

4.364266 секунды.

Есть ли способ ускорить? Индекс на id есть.

Если нет, то какие возможны варианты? Другая СУБД или более мощный сервер?

Сейчас core2duo 1.83, 2gb ram

anonymous

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

>LIMIT 990900, 100

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

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

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

KRoN73 ★★★★★ ()
Ответ на: Re: mysql и 2 500 000 записей от KRoN73

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

Да, но по сути тут идет сортировка по 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

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

anonymous ()
Ответ на: Re: mysql и 2 500 000 записей от anonymous

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

>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).

KRoN73 ★★★★★ ()
Ответ на: Re: mysql и 2 500 000 записей от KRoN73

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

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

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

anonymous ()
Ответ на: Re: mysql и 2 500 000 записей от KRoN73

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

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

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

Legioner ★★★★★ ()
Ответ на: Re: mysql и 2 500 000 записей от anonymous

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

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

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

KRoN73 ★★★★★ ()
Ответ на: Re: mysql и 2 500 000 записей от Legioner

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

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

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

KRoN73 ★★★★★ ()
Ответ на: Re: mysql и 2 500 000 записей от anonymous

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

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

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

anonymous ()
Ответ на: Re: mysql и 2 500 000 записей от KRoN73

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

Ну и что?

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

anonymous ()

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

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

anonymous ()
Ответ на: Re: mysql и 2 500 000 записей от anonymous

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

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

anonymous ()
Ответ на: Re: mysql и 2 500 000 записей от anonymous

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

Алсо

>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

anonymous ()
Ответ на: Re: mysql и 2 500 000 записей от KRoN73

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

> LIMIT 990900, 100

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

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

anonymous ()
Ответ на: Re: mysql и 2 500 000 записей от anonymous

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

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

anonymous ()
Ответ на: Re: mysql и 2 500 000 записей от anonymous

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

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

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

anonymous ()
Ответ на: Re: mysql и 2 500 000 записей от KRoN73

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

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

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

true_admin ★★★★★ ()
Ответ на: Re: mysql и 2 500 000 записей от true_admin

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

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

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

KRoN73 ★★★★★ ()
Ответ на: Re: mysql и 2 500 000 записей от KRoN73

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

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

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

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

anonymous ()
Ответ на: Re: mysql и 2 500 000 записей от anonymous

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

>А может причина в 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)

KRoN73 ★★★★★ ()
Ответ на: Re: mysql и 2 500 000 записей от Pi

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

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

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

KRoN73 ★★★★★ ()

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

> 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;

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

anonymous ()

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

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

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

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

Anoxemian ★★★★★ ()
Ответ на: Re: mysql и 2 500 000 записей от Anoxemian

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

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

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

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

Anoxemian ★★★★★ ()

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

> SELECT * FROM `users` LIMIT 990900, 100

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

gods-little-toy ★★★ ()
Ответ на: Re: mysql и 2 500 000 записей от anonymous

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

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

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

gods-little-toy ★★★ ()
Ответ на: Re: mysql и 2 500 000 записей от anonymous

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

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

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

WHERE primary_key >= 990999

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

gods-little-toy ★★★ ()
Ответ на: Re: mysql и 2 500 000 записей от gods-little-toy

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

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

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

// wbr

klalafuda ★☆☆ ()

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

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

ovax ★★ ()
Ответ на: Re: mysql и 2 500 000 записей от klalafuda

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

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

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

gods-little-toy ★★★ ()

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

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

true_admin ★★★★★ ()

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

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

// wbr

klalafuda ★☆☆ ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.