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

  Опросы
  Статьи

Разбор полетов  

Ошибок опыт трудный

Как часто мы легко повторяем, что не надо бояться совершать ошибки, мол,

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

Принципы проектирования  

Dependency Inversion Principle. Принцип инверсии зависимостей в разработке

Мы подошли к последнему принципу проектирования приложений из серии SOLID – Dependency

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

Рынок труда  

Вакансия: Администратор 1С

Администратор 1С – это специалист, который необходим любой организации, где установлены программы

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

Книжная полка  

Книги для профессионалов, студентов и пользователей

Книги издательства «БХВ» вышли книги для тех, кто хочет овладеть самыми востребованными

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

Принципы проектирования  

Interface Segregation Principle. Принцип разделения интерфейсов в проектировании приложений

Эта статья из серии «SOLID» посвящена четвертому принципу проектирования приложений – Interface

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

1001 и 1 книга  
19.03.2018г.
Просмотров: 10448
Комментарии: 0
Потоковая обработка данных

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

19.03.2018г.
Просмотров: 8633
Комментарии: 0
Релевантный поиск с использованием Elasticsearch и Solr

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

19.03.2018г.
Просмотров: 8738
Комментарии: 0
Конкурентное программирование на SCALA

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

19.03.2018г.
Просмотров: 5510
Комментарии: 0
Машинное обучение с использованием библиотеки Н2О

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

12.03.2018г.
Просмотров: 6189
Комментарии: 0
Особенности киберпреступлений в России: инструменты нападения и защита информации

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

Друзья сайта  

 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