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

  Опросы

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

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

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

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

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

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

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

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

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

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

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

28.05.2019г.
Просмотров: 6979
Комментарии: 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) происходит отказ от обслуживания.

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

Удачи!


Комментарии
 
  03.12.2007 - 10:33 |  Plazmid

Не думал, что MySQL так сливает PGSQL

  03.12.2007 - 11:48 |  Amsand

Кроме того, удивило, что InnoDB оказалась быстрее, чем MyISAM.. С детства мы привыкли считать наоборот ;).

  09.12.2007 - 10:17 |  anonymous

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

  17.12.2007 - 05:44 |  stellar

>Скажи это ребятам из SAP.

Для начала - представьтесь, пожалуйста.

  21.12.2007 - 12:21 |  Geol

MaxDB!=MySQL

  26.12.2007 - 09:21 |  anonymous

Почему для MySQL используется такой странный метод вставки нового комментария:

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

В таблице comments нету индекса, начинающегося с comment_id, естественно этот запрос будет просматривать всю таблицу и выполняться крайне медленно.

Если уж используете MySQL-диалект, используйте AUTO_INCREMENT-колонку.

  27.12.2007 - 02:55 |  --

Индекс по comment_id был, просто при копировании - вставке строка потерялась.

AUTO_INCREMENT использовать нет смысла - потому, что при показе поста надо знать его номер.

  28.12.2007 - 01:07 |  Yo.!

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

  28.12.2007 - 12:16 |  --

>а можно увидеть планы запросов mysql ?

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

>да и сколько строк было в таблицах ?

Была сэмулирована текущая БД проекта блогосервиса; конкретные числа и имя блогосервиса я назвать не могу.

  28.12.2007 - 12:50 |  serge

Для этого существует LAST_INSERT_ID()

«  1       »

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

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

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

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