LINUX.ORG.RU

MySQL vs. PostgreSQL holy war


2

1

Часто возникают споры какая из DBMS лучше. Однако обе они работают достаточно быстро с малыми объёмами данных. Тестирование с большими объёмами данных затруднительно т.к. приходится искусственно создавать данные.

Предлагаю сторонникам одной из этим DBMS посоветовать оптимизации которые только можно применить к этой таблице. Таким образом вы мне очень поможете и будет видно какая DBMS всё таки лучше для больших объёмов данных.

Выслушаю также предложения по другим DBMS в том числе NoSQL.

Имеется таблица

в MySQL:

CREATE TABLE `videos` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `site` varchar(255) NOT NULL,
  `site_id` bigint(20) unsigned NOT NULL,
  `site_url` varchar(255) NOT NULL,
  `title` text NOT NULL,
  `thumbnails` text NOT NULL,
  `duration_txt` varchar(32) NOT NULL,
  `duration` bigint(20) unsigned NOT NULL,
  `embed` text NOT NULL,
  `created_on` datetime NOT NULL,
  `status` enum('active','deleted') NOT NULL DEFAULT 'active',
  `views` bigint(20) unsigned NOT NULL DEFAULT '0',
  `rating` bigint(20) unsigned NOT NULL DEFAULT '0',
  `voted` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `site` (`site`),
  KEY `site_id` (`site_id`),
  KEY `site_url` (`site_url`),
  KEY `status` (`status`),
  KEY `created_on_id` (`created_on`,`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1797645 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED

в PostgreSQL:

                                      Table "public.videos"
    Column    |            Type             |                      Modifiers                      
--------------+-----------------------------+-----------------------------------------------------
 id           | bigint                      | not null default nextval('videos_id_seq'::regclass)
 site         | character varying           | not null
 site_id      | bigint                      | not null
 site_url     | character varying           | not null
 title        | character varying           | not null
 thumbnails   | character varying           | not null
 duration_txt | character varying           | not null
 duration     | bigint                      | not null
 embed        | character varying           | not null
 created_on   | timestamp without time zone | not null
 views        | bigint                      | not null
 rating       | bigint                      | not null
 voted        | bigint                      | not null
 status       | status                      | not null default 'active'::status
Indexes:
    "videos_pkey" PRIMARY KEY, btree (id)
    "videos_created_on" btree (created_on)
    "videos_created_on_id" btree (created_on DESC, id DESC)

Задача как можно лучше оптимизировать запрос «SELECT * FROM videos WHERE status = 'active' ORDER BY created_on DESC, id DESC OFFSET 1050050 LIMIT 30». (OFFSET должен быть большим, таблица содержит 1,782,614 записей. Если это сильно увеличит производительность WHERE status = 'active' можно выкинуть и сделать хотя бы без него.

на sql.ru тебе дорога, там, для таких холиваров даже специальная ветка на форуме есть...

xterro ★★★★★ ()

Убейся, тебе уже всё разжевали про offset в предыдущем треде. Хотя с генами ничего не сделаешь, увы.

anonymous ()

Все говно. Бери Oracle.

xpahos ★★★★★ ()

Что бы получить 1050050 строку через OFFSET нужно просмотреть 1050050 строк, поэтому через OFFSET никто пейджинг не делает.

Eshkin_kot ★★ ()
Ответ на: комментарий от Eshkin_kot

через OFFSET нужно просмотреть 1050050 строк

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

pi11 ★★★★★ ()

1.
В постгресе такие таблицы необходимо резать на партиции по created_on и соответственно в запросе указывать в каких пределах дат тебе интересен результат. Соответственно страницы сделать не по кол-ву а по датам. Тогда запрос будет бегать только по нужным партициям, а не по всей таблице.

2.
SELECT * - тебе точно все поля _всегда_ нужны из запроса? можно к примеру выкинуть не _всегда_ нужные поля в отдельную таблицку и тогда получать нужную часть только когда оно действительно надо.

vtVitus ★★★★★ ()

Добавь поле offset с реальной нумерцией и пересчитывай его в случае удаления записей из середины. Только пересчитывание делай по крону в случае необходимости, а то сделаеш еще в момент удаления.

andrewzvn ()

А тебе точно нужен offset на 1050050? Пользователю можно бы показывать на выбор меню, скажем, из 10 страниц. А потом предлагать выбор диапазоны дат. На маленьких значениях offset должен норм работать.

dizza ★★★★★ ()
Ответ на: комментарий от hidden_4003

Изначально вопрос стоит о производительности.

Если это сильно увеличит производительность

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

oxumorron ()
Ответ на: комментарий от oxumorron

Как уже писал в другой теме, решил проблему путём создания отдельной таблицы.

create table videos_index (
id bigint unsigned not null unique,
created_on datetime not null
);
alter table videos_index add index (created_on desc, id desc);

И использованием таких запросов.

SELECT id FROM videos_index ORDER BY created_on DESC, id DESC LIMIT 1050050, 30;
SELECT * FROM videos WHERE id IN (<ids from previous query>);

В MySQL это отрабатывает мгновенно независимо от offset'а. Поэтому остановился на MySQL. В PostgreSQL первый запрос продолжает сильно тормозить. Получается лучше подходит MySQL если конечно кто то не подскажет как оптимизировать в PostgreSQL.

tyler19 ()
Ответ на: комментарий от tyler19

В PostgreSQL первый запрос продолжает сильно тормозить.

Дай угадаю, настройки буферов не крутились?

anonymous ()
Ответ на: комментарий от namezys

Непонятно что именно там происходит в обеих БД, если бы был EXPLAIN тогда картина была бы яснее. К тому же MyISAM изначально предназначен для подобных вещей, хотя в предыдущие разы когда в этой же таблице была куча text полей он все же тупил.

Есть подозрение что таблица вмещается в память и потому все так радужно, а как только она перестанет влезать в память то результат будет одинаков.

hidden_4003 ()
Ответ на: комментарий от hidden_4003

по идее в данном случае достаточно пройтись по индексу. Но в отличие от условия на интервалы, limit и offset потребует перебор дерева слева (или справа, если инверсирован индекс) от точки.

то есть мы будем перебирать все элементы в дереве, пока не дойдем до нужного offset, а уж потом до тех пор, пока не дойдем до нужного limit. Это быстрее, чем перебирать таблицу. В этом случае стоит добиться максимально компактной формы индексов (возможно, стоит воспользоваться функциональный индексом).

Это все хорошо, если нет условия where на поле, которое не входит индекс. В данном случае status у нас фиксирован, и он должен быть либо в конце, либо в начале списка индекса. Так как это текст, я бы попробовал его засунуть в начало индекса и посмотреть, что будет. Ну если честно, я не очень представляю, как он развернет дерево в случае появления поля status. Но status фиксирован (active) и можно сделать условный индекс. Тогда поиск действительно будет происходить по дереву. Из created_on и id можно получить еще и целое число и сделать условный функциональный индекс, придав анализатору пинком ускорение в правильном направлении. Кроме того, такой подход может позволить снизить объем индекса, что хорошо в любом случае (или он полностью влезет в кэш, либо большая часть).

namezys ★★★★ ()
Ответ на: комментарий от oxumorron

index only scan не всегда хорошая идея, так как увеличивает объем индекса и он может не попасть целиком в кэш.

если в запросе вариант по полю статус ограничен, то это не важно.

namezys ★★★★ ()
Ответ на: комментарий от namezys

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

oxumorron ()
Ответ на: комментарий от namezys

Вроде бы выше уже приводилось где то. Соберу всё в одном месте:

MySQL:

mysql> show create table videos_index;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                   |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| videos_index | CREATE TABLE `videos_index` (
  `id` bigint(20) unsigned NOT NULL,
  `created_on` datetime NOT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `created_on_id` (`created_on`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

mysql> select id from videos_index order by created_on desc, id desc limit 1050050, 30;
+---------+
| id      |
+---------+
| 1184093 |
| 1184092 |
| 1184091 |
| 1184090 |
| 1184089 |
| 1184088 |
| 1184087 |
| 1184086 |
| 1184085 |
| 1184084 |
| 1184083 |
| 1184082 |
| 1184081 |
| 1184080 |
| 1184079 |
| 1184078 |
| 1184077 |
| 1184076 |
| 1184075 |
| 1184074 |
| 1184073 |
| 1184072 |
| 1184071 |
| 1184070 |
| 1184069 |
| 1184068 |
| 1184067 |
| 1184066 |
| 1184065 |
| 1184064 |
+---------+
30 rows in set (0.37 sec)

mysql> 
mysql> explain select id from videos_index order by created_on desc, id desc limit 1050050, 30;
+----+-------------+--------------+-------+---------------+---------------+---------+------+---------+-------------+
| id | select_type | table        | type  | possible_keys | key           | key_len | ref  | rows    | Extra       |
+----+-------------+--------------+-------+---------------+---------------+---------+------+---------+-------------+
|  1 | SIMPLE      | videos_index | index | NULL          | created_on_id | 16      | NULL | 1050080 | Using index |
+----+-------------+--------------+-------+---------------+---------------+---------+------+---------+-------------+
1 row in set (0.04 sec)

mysql> 

PostgreSQL:

b32=# \d
              List of relations
 Schema |     Name      |   Type   |  Owner   
--------+---------------+----------+----------
 public | videos        | table    | postgres
 public | videos_id_seq | sequence | postgres
 public | videos_index  | table    | postgres
(3 rows)

b32=# \d videos_index
             Table "public.videos_index"
   Column   |            Type             | Modifiers 
------------+-----------------------------+-----------
 id         | bigint                      | not null
 created_on | timestamp without time zone | 
Indexes:
    "videos_index_created_on_id" btree (created_on DESC, id DESC)

b32=# select count(*) from videos_index;
  count  
---------
 1782614
(1 row)

b32=# select id from videos_index order by created_on desc, id desc offset 1050050 limit 30;

*** около 5 секунд ***

b32=# explain select id from videos_index order by created_on desc, id desc offset 1050050 limit 30;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Limit  (cost=276107.18..276107.26 rows=30 width=16)
   ->  Sort  (cost=273482.06..277938.59 rows=1782614 width=16)
         Sort Key: created_on, id
         ->  Seq Scan on videos_index  (cost=0.00..27462.14 rows=1782614 width=16)
(4 rows)

b32=# 

Сам бы предпочёл работать с PostgreSQL из-за более полной поддержки транзакций, по крайней мере нет путаницы с этим как в MySQL. Но 5 секунд на такой запрос - неприемлемо т.к. их может поступить несколько одновременно.

tyler19 ()
Ответ на: комментарий от tyler19

Но 5 секунд на такой запрос - неприемлемо т.к. их может поступить несколько одновременно.

Начнем с такого вопроса -
shared_buffers сколько? На дефолтной конфигурации с PostgreSQL можно только играть.

pi11 ★★★★★ ()
Последнее исправление: pi11 (всего исправлений: 1)
Ответ на: комментарий от pi11

На дефолтной конфигурации с PostgreSQL можно только играть.

А в некоторых дистрах даже играть нельзя. sync по умолчанию включен xDD.

// Я бы оставил ТС'а наедине с его дражайшим мускулем.

anonymous ()
Ответ на: комментарий от oxumorron

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

Надо выбрать сравнительно мало данных из сравнительно большого числа. Лучше уж индекс в памяти и чтение из таблицы.

namezys ★★★★ ()
Ответ на: комментарий от dizza

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

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

namezys ★★★★ ()
Ответ на: комментарий от tyler19

тюнинг постгри делали? попробуйте условный индекс. У вас сейчас фактически все считывалось в память. Еще explain analize надо бы

namezys ★★★★ ()
Ответ на: комментарий от dizza

Вы отключаете синк на диск?

synchronous_commit, конечно.

anonymous ()
Ответ на: комментарий от dizza

Лол что? Вы отключаете синк на диск? Окай.

А вы его включаете? ЛОЛ, что?

namezys ★★★★ ()
Ответ на: комментарий от namezys

тюнинг постгри делали?

Никакого тюнинга не делал, просто собрал из gentoo portage.

попробуйте условный индекс.

Пожалуста подробнее или дайте ссылку на мануал.

Еще explain analize надо бы

b32=# explain analyze select id from videos_index order by created_on desc, id desc offset 1050050 limit 30;
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=276107.18..276107.26 rows=30 width=16) (actual time=12256.046..12256.129 rows=30 loops=1)
   ->  Sort  (cost=273482.06..277938.59 rows=1782614 width=16) (actual time=10004.042..11342.404 rows=1050080 loops=1)
         Sort Key: created_on, id
         Sort Method: external merge  Disk: 45336kB
         ->  Seq Scan on videos_index  (cost=0.00..27462.14 rows=1782614 width=16) (actual time=17.524..3097.630 rows=1782614 loops=1)
 Total runtime: 12267.546 ms
(6 rows)

b32=#
tyler19 ()
Ответ на: комментарий от tyler19

http://www.postgresql.org/docs/9.1/static/sql-createindex.html

смотри условие where

Давай я объясню, что тут происходит. Метод merge. Он используется тогда, когда у нас не влазит результат в память. Очень медленный. Как ты видишь, это 43 мб всего, в современных серверах гигабайты памяти. Увеличивай рабочий объем памяти до 100 мб и будет тебе счастье. Я сейчас с ходу не скажу, какие вещи надо увеличить, но прямо в файле конфига написано что за что отвечает.

У тебя всего 2 млн строк. Это очень мало. И попробуй сделать то же самое, но выбрать только один id.

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

namezys ★★★★ ()
Ответ на: комментарий от tyler19

Никакого тюнинга не делал, просто собрал из gentoo portage.

Ну тогда нечего ожидать хоть сколько нибудь приемлемой производительности.

pi11 ★★★★★ ()
Ответ на: комментарий от tyler19
...
| 1184065 |
| 1184064 |
+---------+
30 rows in set (0.37 sec)

...

05:54:34 [local]:5432 ~* => select id from t order by create_on desc, id desc offset 1050050 limit 30;
   id   
--------
 949950
 949949
 949948
 949947
 949946
 949945
 949944
 949943
 949942
 949941
 949940
 949939
 949938
 949937
 949936
 949935
 949934
 949933
 949932
 949931
 949930
 949929
 949928
 949927
 949926
 949925
 949924
 949923
 949922
 949921
(30 строк)

Время: 195,361 мс

Ура! Постгрес победил! :)

explain (analyze,verbose,buffers) select id from t order by create_on desc, id desc offset 1050050 limit 30;
                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=45054.85..45056.14 rows=30 width=16) (actual time=320.509..320.521 rows=30 loops=1)
   Output: id, create_on
   Buffers: shared hit=9703
   ->  Index Only Scan using t_create_on_id_idx on seb.t  (cost=0.00..85814.68 rows=2000000 width=16) (actual time=0.017..250.965 rows=1050080 loops=1)
         Output: id, create_on
         Heap Fetches: 1050080
         Buffers: shared hit=9703
 Total runtime: 320.545 ms

Это всё равно очень много на одну страницу. Так как что бы выдать вам 1050050 строку нужно построить все 1050049 предыдущих, поэтому пейджинг через offset никто не делает :)

Eshkin_kot ★★ ()
Ответ на: комментарий от drBatty

ЩИТО?

На больших OFFSET-ах постгрес сортируют таблицу по полю. На маленьких - нет. (Еще зависит от размера таблицы).

pi11 ★★★★★ ()
Последнее исправление: pi11 (всего исправлений: 1)
Ответ на: комментарий от drBatty

Вот пример -

explain select id from video_video order by id offset 100 limit 10;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Limit  (cost=279.13..307.04 rows=10 width=4)
   ->  Index Scan using video_video_pkey on video_video  (cost=0.00..3491806.14 rows=1250969 width=4)
(2 rows)

explain select id from video_video order by id offset 100000 limit 10;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Limit  (cost=175580.16..175580.19 rows=10 width=4)
   ->  Sort  (cost=175330.16..178457.59 rows=1250969 width=4)
         Sort Key: id
         ->  Seq Scan on video_video  (cost=0.00..65183.69 rows=1250969 width=4)
(4 rows)

pi11 ★★★★★ ()
Ответ на: комментарий от pi11

На больших OFFSET-ах постгрес сортируют таблицу по полю.

а… туплю. Это у вас так LIMIT называется? Т.е. ТС хочет получить выборку из 1050050 строк??

drBatty ★★ ()
Ответ на: комментарий от drBatty

Нет, limit называется limit. А offset - offset. Честно говоря почему он так работает (сортирует а не index scan делает) - я не знаю.

pi11 ★★★★★ ()
Ответ на: комментарий от pi11

Нет, limit называется limit. А offset - offset

в mysql просто LIMIT пишут. А OFFSET только для совместимости.

Честно говоря почему он так работает (сортирует а не index scan делает) - я не знаю.

ну это понятно почему: ты можешь сделать выборку всех эл-тов на LIKE 'XYZ%', но вот десятый ты сможешь найти только выбрав все 10 шт. Потому большие оффсеты никто не юзает - 100500 гигов памяти стоит дорого.

drBatty ★★ ()
Ответ на: комментарий от drBatty

Ну так я не про случай с LIKE, а случай когда order by по полю с индексом. Без индекса он и на маленьких оффсетах сначала сортировать будет.

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