PostgreSQL vs MySQL::Журнал СА 7.2007
www.samag.ru
     
Поиск   
              
 www.samag.ru    Web  0 товаров , сумма 0 руб.
E-mail
Пароль  
 Запомнить меня
Регистрация | Забыли пароль?
Журнал "Системный администратор"
Журнал «БИТ»
Наука и технологии
Подписка
Где купить
Авторам
Рекламодателям
Магазин
Архив номеров
Вакансии
Контакты
   

  Опросы

Какие курсы вы бы выбрали для себя?  

Очные
Онлайновые
Платные
Бесплатные
Я и так все знаю

 Читать далее...

1001 и 1 книга  
20.12.2019г.
Просмотров: 5399
Комментарии: 0
Dr.Web: всё под контролем

 Читать далее...

04.12.2019г.
Просмотров: 6594
Комментарии: 0
Особенности сертификаций по этичному хакингу

 Читать далее...

28.05.2019г.
Просмотров: 7877
Комментарии: 2
Анализ вредоносных программ

 Читать далее...

28.05.2019г.
Просмотров: 8169
Комментарии: 1
Микросервисы и контейнеры Docker

 Читать далее...

28.05.2019г.
Просмотров: 7165
Комментарии: 0
Django 2 в примерах

 Читать далее...

Друзья сайта  

Форум системных администраторов  

sysadmins.ru

 PostgreSQL vs MySQL

Архив номеров / 2007 / Выпуск №7 (56) / PostgreSQL vs MySQL

Рубрика: Администрирование /  Продукты и решения

Андрей Шетухин

PostgreSQL vs MySQL

Современный блогохостинг – какой он? Какую СУБД выбрать, как спроектировать схему данных и где узкие места при использовании той или иной СУБД? Протестируем производительность СУБД MySQL и PostgreSQL на примере блогохостинга.

О сравнении производительности MySQL и PostgreSQL написана не одна статья, и всезнающий Google выдает больше миллиона документов, посвященных этой теме.

Однако, все тесты, которые доступны в Интернете, – это работа СУБД на синтетических запросах, имеющих малое отношение к конкретным проектам.

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

Схема БД

Схема БД

Сервер и настройки системы

Сервер – HP ProLiant DL380 2xXeon3.2, RAM 5 Гб, SCSI HDD u320.

Операционная система – Linux Debian, kernel 2.6.18.

# cat /proc/meminfo

MemTotal:         5016868 kB

MemFree:          4087256 kB

Buffers:           152704 kB

Cached:            643356 kB

SwapCached:             0 kB

Active:            492940 kB

Inactive:          339252 kB

HighTotal:              0 kB

HighFree:               0 kB

LowTotal:         5016868 kB

LowFree:          4087256 kB

SwapTotal:        7815580 kB

SwapFree:         7815532 kB

Dirty:                 80 kB

Writeback:              0 kB

AnonPages:           6820 kB

Mapped:             15812 kB

Slab:               76636 kB

PageTables:          1084 kB

NFS_Unstable:           0 kB

Bounce:                 0 kB

CommitLimit:     10324012 kB

Committed_AS:      297940 kB

VmallocTotal: 34359738367 kB

VmallocUsed:         4100 kB

VmallocChunk: 34359734091 kB

# ulimit -a

core file size          (blocks, -c) 0

data seg size           (kbytes, -d) unlimited

max nice                        (-e) 0

file size               (blocks, -f) unlimited

pending signals                 (-i) unlimited

max locked memory       (kbytes, -l) unlimited

max memory size         (kbytes, -m) unlimited

open files                      (-n) 65535

pipe size            (512 bytes, -p) 8

POSIX message queues     (bytes, -q) unlimited

max rt priority                 (-r) 0

stack size              (kbytes, -s) 8192

cpu time               (seconds, -t) unlimited

max user processes              (-u) unlimited

virtual memory          (kbytes, -v) unlimited

file locks                      (-x) unlimited

Установленное ПО:

mysql> select version();

+---------------------+

| version()           |

+---------------------+

| 5.0.32-Debian_3-log |

+---------------------+

1 row in set (0.04 sec)

postgres=# select version();

                                                    version

---------------------------------------------------

------------------------------------------------------------

 PostgreSQL 8.1.8 on i486-pc-linux-gnu, compiled by

  GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)

(1 row)

К сожалению, настройки как MySQL, так и PostgreSQL достаточно сложны, и предоставить их в этой статье вместе с достойным описанием не позволяет объем.

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

MySQL:

# InnoDB

sync_binlog                     = 0

innodb_buffer_pool_size         = 2048M

innodb_additional_mem_pool_size = 64M

innodb_log_files_in_group       = 2

innodb_log_file_size            = 512M

innodb_log_buffer_size          = 8M

innodb_flush_log_at_trx_commit  = 0

# MyISAM

key_buffer              = 1024M

table_cache             = 1024

sort_buffer_size        = 16M

read_buffer_size        = 4M

read_rnd_buffer_size    = 4M

myisam_sort_buffer_size = 4M

query_cache_size        = 16M

PostgreSQL:

shared_buffers            = 500000

temp_buffers              = 4000

max_prepared_transactions = 0

work_mem                  = 128000

maintenance_work_mem      = 256000

max_stack_depth           = 8192

max_fsm_pages             = 200000

max_fsm_relations         = 10000

max_files_per_process     = 1000

stats_start_collector     = on

stats_command_string      = off

stats_row_level           = on

autovacuum                = on

Схема БД представлена на рисунке.

Для PostgreSQL:

CREATE TABLE users

(

    user_id    bigint NOT NULL,

    user_name  character varying(100) NOT NULL,

    CONSTRAINT users_pk PRIMARY KEY (user_id)

) WITHOUT OIDS;

CREATE UNIQUE INDEX users_user_name_u ON users USING btree (user_name);

CREATE TABLE friends

(

    user_id    bigint NOT NULL,

    friend_id  bigint NOT NULL,

    CONSTRAINT friends_pk PRIMARY KEY (user_id, friend_id),

    CONSTRAINT user_id_fk1 FOREIGN KEY (user_id) REFERENCES users (user_id),

    CONSTRAINT user_id_fk2 FOREIGN KEY (friend_id) REFERENCES users (user_id)

) WITHOUT OIDS;

CREATE TABLE posts

(

    user_id    bigint NOT NULL,

    post_id    bigint NOT NULL,

    post_date  bigint NOT NULL,

    post_title character varying(256) NOT NULL,

    post_body  text,

    CONSTRAINT posts_pk PRIMARY KEY (user_id, posts_id),

    CONSTRAINT user_id_fk3 FOREIGN KEY (user_id) REFERENCES users (user_id)

)

WITHOUT OIDS;

CREATE TABLE comments

(

    user_id       bigint NOT NULL,

    posts_id      bigint NOT NULL,

    comments_id   bigint NOT NULL,

    from_user_id  bigint NOT NULL,

    comment_date  bigint NOT NULL,

    comment_title character varying(256) NOT NULL,

    comment_body  text,

    CONSTRAINT comments_pk PRIMARY KEY (user_id, posts_id, comments_id),

    CONSTRAINT user_posts_id_fk1 FOREIGN KEY (user_id, posts_id) REFERENCES posts (user_id, posts_id),

    CONSTRAINT from_user_id_fk2  FOREIGN KEY (user_id) REFERENCES users (user_id)

)

WITHOUT OIDS;

Для MySQL:

DROP TABLE IF EXISTS users;

CREATE TABLE users

(

    user_id   int(10) NOT NULL auto_increment,

    user_name varchar(100) NOT NULL,

    PRIMARY KEY  (user_id)

) ENGINE=InnoDB;

DROP TABLE IF EXISTS friends;

CREATE TABLE friends

(

    user_id int(10) NOT NULL,

    friend_id int(10) NOT NULL,

    PRIMARY KEY  (user_id, friend_id)

) ENGINE=InnoDB;

DROP TABLE IF EXISTS posts;

CREATE TABLE posts

(

    user_id    int(10) NOT NULL,

    post_id    int(10) NOT NULL,

    post_date  int(10) NOT NULL,

    post_title varchar(100) NOT NULL,

    post_body  text NOT NULL,

    PRIMARY KEY (user_id, post_id)

) ENGINE=InnoDB;

DROP TABLE IF EXISTS comments;

CREATE TABLE post_cmt

(

    user_id int(10) NOT NULL,

    post_id int(10) NOT NULL,

    comment_id int(10) NOT NULL,

    from_user_id int(10) NOT NULL,

    comment_date int(10) NOT NULL,

    comment_title varchar(250) NOT NULL,

    comment_body text NOT NULL,

    PRIMARY KEY  (user_id, post_id, comment_id)

) ENGINE=InnoDB;

Для тестов с ENGINE=MyISAM отличий в структуре БД нет. Все дампы схем доступны по адресу: http://www.reki.ru/products/blogoservice.

Методика тестирования

Распредение количества запросов на чтение и запись было выбрано в отношении 80% и 20% соответственно. После запуска на тестирование производилась выдержка в течение нескольких десятков секунд для стабилизации результатов. На каждую СУБД производилось пять тестов, результат усреднялся.

Тестировались следующие запросы (в процентах от общего числа):

  • чтение френдленты (95%);
  • чтение постов и комментариев (5%);
  • запись постов и комментариев к ним (95%);
  • создание новых пользователей и изменение свойств уже существующих (5%).

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

Для PostgreSQL запросы выглядят так:

Чтение френдленты:

SELECT user_id, MAX(posts_id)

 FROM posts

 WHERE user_id

 IN (SELECT friend_id

     FROM friends

     WHERE user_id = UserId)

 GROUP BY user_id;

Чтение постов и комментариев:

SELECT *

 FROM posts

 WHERE (posts_id = PostId);

SELECT *

 FROM comments

 WHERE ((user_id = UserId) AND (posts_id = PostId))

 ORDER BY comment_date;

Запись поста:

SELECT NewPost(UserId, Date, Title, Body);

Запись комментария:

SELECT NewComment(UserId, Date, Title, Body);

Создание пользователя:

SELECT NewUser(UserName)

Редактирование свойств пользователя:

SELECT ModifyUser(UserId, UserName);

Для MySQL:

Запрос чтение ленты. Общий вид запроса следующий:

SELECT user_id, MAX(posts_id)

 FROM posts

 WHERE user_id

 IN (SELECT friend_id

     FROM friends

     WHERE user_id = UserId)

 GROUP BY user_id;

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

Однако, поскольку MySQL всех версий не умеет адекватно оптимизировать внешний запрос вне зависимости от созданных индексов и их явного указния через FORCE(key list), разбиваем сложный запрос на два простых: в первом запросе получаем список пользователей, а во втором – выбираем посты.

UsersSet = SELECT friend_id FROM friends WHERE user_id = UserId

и

SELECT user_id, MAX(post_id) FROM posts WHERE user_id in (UsersSet) GROUP BY user_id

Я не утверждаю, что MySQL 5 не умеет делать подзапросы. Утверждается, что скорость работы двух простых запросов выше скорости работы одного сложного ввиду специфических особенностей работы оптимизатора запросов.

Чтение постов и комментариев:

SELECT *

 FROM posts

 WHERE (posts_id = PostId);

SELECT *

 FROM comments

 WHERE ((user_id = UserId) AND (posts_id = PostId))

 ORDER BY comment_date;

Запись поста:

PostID = SELECT MAX(post_id) FROM posts;

INSERT IGNORE INTO posts (user_id, post_id, post_date, post_title, post_body) VALUES (UserID, PostID, Date, Title, Body);

Запись комментария:

SELECT COUNT(*) FROM users WHERE (user_id = iUserId);

SELECT COUNT(*) FROM users WHERE (user_id = iPosterId);

SELECT COUNT(*) FROM posts WHERE (post_id = iPostId);

CommentId = SELECT MAX(comment_id) FROM commnets; INSERT IGNORE INTO \

    comments (user_id, posts_id, comment_id, from_user_id, comment_date, comment_title, comment_body) \

    VALUES (UserId, PostId, CommentId, PosterId, Date, Title, Body);</pre>

Создание пользователя:

SELECT COUNT(1) FROM users WHERE user_name = UserName;

INSERT INTO users(user_name) VALUES (UserName);

GET_LAST_INSERT_ID

Редактирование свойств пользователя:

SELECT COUNT(1) FROM users WHERE user_id = UserId;

UPDATE users SET user_name = UserName WHERE user_id = UserId;

Как мы можем видеть, алгоритмы работы с БД оптимизированны как под MySQL, так и под PostgreSQL.

Важно отметить, что не существует одинаковых архитектурных решений для этих двух СУБД. В каждом случае требуется индивидуальный подход как в проектировании структуры базы данных, так и в реализации механизма вставок и выборок данных.

Именно поэтому в высоконагруженных проектах недопустимо использование «универсальных» фреймворков для БД, поскольку ни один из них не учитывает архитектуру проекта, в котором используется, а большинство их них не учитывает даже порядка использования индексов в СУБД.

Поговорим про оптимизацию кода. Разумеется, количество «служебных» запросов (проверок наличия или отсутствия пользователя, комментариев и т. п.) можно сократить, введя, например, users_id как хэшированное имя пользователя или разместив в памяти хэштаблицу ID и имен пользователей. Однако здесь мы тестируем производительность именно СУБД, а не всего программного комплекса в целом.

Результаты

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

Результаты тестирования СУБД MySQL и PostgreSQL в зависимости от количества подключений

Количество коннектов в пуле

MySQL InnoDB

(read/write per sec)

MySQL MyISAM

(read/write per sec)

PostgreSQL

(read/write per sec)

1

600/60

500/20

4000/1500

5

1100/110

600/50

12000/3000

10

1100/110

600/50

14000/4000

11-17

800/70

400/30

14000/4000

20 и более

Отказ от обслуживания

Отказ от обслуживания

14000/4000

Выводы

Вся описанная в литературе хваленая производительность MySQL достигается только на примитивных запросах на чтение при отсутствии записи в таблицу. Как только требуется пересечь в выборке несколько таблиц, под нагрузкой на запись, MySQL проигрывает PostgreSQL в разы.

Поэтому ниша MySQL – проекты, не требующие какой-либо ссылочной целостности и бизнес-логики внури базы данных и структурой, в которой возможны только простые выборки без сложных пересечений. Даже в проекте типа блогосервиса, MySQL в разы уступает в производительности PostgreSQL, а при достаточно большом количестве одновременных выборок (больше 20) происходит отказ от обслуживания.

Разумеется, не существует «серебряной пули», которая могла бы убить любого зверя. В любом и каждом проекте следует тестировать применимость той или иной технологии и ни в коем случае нельзя при выборе СУБД полагаться на предыдущий «опыт» других разработчиков, ведь вполне может оказаться что их выбор – не более чем дань традиционному заблуждению.

Удачи!


Комментарии
 
  28.12.2007 - 04:57 |  Yo!

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

  29.12.2007 - 10:54 |  DocAl

Что это за "отказ в обслуживании" на 20 коннектах? Вы, хотя бы, выяснили причину этого?

  05.01.2008 - 12:04 |  Андрей Панфилов

прежде чем бросаться громкими фразами в стиле "Даже в проекте типа блогосервиса, MySQL в разы уступает в производительности PostgreSQL" удосужтесь хоть немного изучить SQL и MySQL в частности. А пока я в статье узрел только то, что не "скорость работы двух простых запросов выше скорости работы одного сложного ввиду специфических особенностей работы оптимизатора запросов", а автор вообще ничего не слышал о JOIN в SQL.

  09.01.2008 - 08:41 |  author_a

Id должен быть уникальным даже для удалённых из базы записей. Если использовать MAX(id) для вставки новой записи вместо auto increment, то новая запись может получить тот же id, что и недавно удалённая. Это вызовет проблемы с ссылками, как вне БД (например, с URL-ами), так и, возможно, внутри БД (если нет поддержки ссылочной целостности).
Есть опыт, когда это приводило к очень серьёзным проблемам.

  10.01.2008 - 05:45 |  HORD

Согласен, что показывает весьма нетипичные результаты соотношения для MyISAM / InnoDB. Также то, что настолько велика разница уже на минимуме нагрузки. ИМХО, MySQL здесь используется неоптимально - в этом и причина.

  11.01.2008 - 12:30 |  stellar

>А пока я в статье узрел только то, что не "скорость работы двух простых запросов выше скорости работы одного сложного ввиду специфических особенностей работы оптимизатора запросов",

Ну, что поделать... Я ничем не могу помочь слепому человеку.

>а автор вообще ничего не слышал о JOIN в SQL.

Прежде чем упрекать меня в незнании SQL и неиспользовании JOIN-ов, разберитесь, ПОЧЕМУ не используются JOIN-ы.

Не нравится статья? Есть возражения? Ну так проведите тесты самостоятельно, опубликуйте их в журнале, за это даже гонорар заплатят.

  11.01.2008 - 12:32 |  stellar

>Id должен быть уникальным даже для удалённых из базы записей.

В блогосервисе типа Livejournal, как и в любом другом, где объект параметризуется посредством указания имени блога и ID объекта, это несущественно.

  11.01.2008 - 12:37 |  stellar

>Согласен, что показывает весьма нетипичные результаты соотношения для MyISAM / InnoDB.

Для конкурентного read/write доступа это показывает совершенно ожидаемые результаты для MyISAM и InnoDB.

Сравнивать скорость работы в read-only или write-only режиме работы СУБД лично я не вижу ни малейшего смысла.


>Также то, что настолько велика разница уже на минимуме нагрузки. ИМХО, MySQL здесь используется неоптимально - в этом и причина.

Вы так считаете? Проведите свое исследование, где MySQL будет использоваться оптимально. Причем, исследование не на синтетических задачах вида однотипных выборок из БД, а на реальной моделе.

  12.01.2008 - 07:13 |  Андрей Панфилов

>Не нравится статья? Есть возражения? Ну так проведите тесты самостоятельно, опубликуйте их в журнале, за это даже гонорар заплатят.

предоставьте свои тестовые данные, а я вам предоставлю результаты.

  21.02.2008 - 12:23 |  Nail Kashapov

Андрей Шетухин:
Опубликуйте скрипты которыми это тестилось. Чтобы можно было проверить.

Навскидку замечание:

mysql> explain SELECT * FROM comments WHERE ((user_id = 4) AND (post_id = 2)) ORDER BY comment_date\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: comments
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const,const
rows: 1
Extra: Using where; Using filesort

«    2     »

Добавить комментарий

Комментарии могут оставлять только зарегистрированные пользователи

               Copyright © Системный администратор

Яндекс.Метрика
Tel.: (499) 277-12-41
Fax: (499) 277-12-45
E-mail: sa@samag.ru