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

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

День сисадмина  

Учите матчасть! Или как стать системным администратором

Лето – время не только отпусков, но и хорошая возможность определиться с профессией

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

День сисадмина  

Живой айтишник – это всегда движение. Остановка смерти подобна

Наши авторы рассказывают о своем опыте и дают советы начинающим системным администраторам.

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

Виртуализация  

Рынок решений для виртуализации

По данным «Обзора российского рынка инфраструктурного ПО и перспектив его развития», сделанного

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

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

Как стать креативным и востребованным

Издательский дом «Питер» предлагает новинки компьютерной литературы, а также книги по бизнесу

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

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

От создания сайтов до разработки и реализации API

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

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

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

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

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

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

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

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

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

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

Рынок труда  

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

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

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

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

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

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

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

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

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

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

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

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

Секрет успешных людей

Книги издательства «БХВ» по ИТ рассчитаны на разные категории читателей: от новичков

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

Учебные центры  

Карьерные мечты нужно воплощать! А мы поможем

Школа Bell Integrator открывает свои двери для всех, кто хочет освоить перспективную

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

Гость номера  

Дмитрий Галов: «Нельзя сказать, что люди становятся доверчивее, скорее эволюционирует ландшафт киберугроз»

Использование мобильных устройств растет. А вместе с ними быстро растет количество мобильных

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

Прошу слова  

Твердая рука в бархатной перчатке: принципы soft skills

Лауреат Нобелевской премии, специалист по рынку труда, профессор Лондонской школы экономики Кристофер

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

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

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

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

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

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

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

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

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

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

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

12.03.2018г.
Просмотров: 3969
Комментарии: 0
Глубокое обучение с точки зрения практика

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

12.03.2018г.
Просмотров: 2897
Комментарии: 0
Изучаем pandas

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

12.03.2018г.
Просмотров: 3701
Комментарии: 0
Программирование на языке Rust (Цветное издание)

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

19.12.2017г.
Просмотров: 3706
Комментарии: 0
Глубокое обучение

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

19.12.2017г.
Просмотров: 6195
Комментарии: 0
Анализ социальных медиа на Python

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

19.12.2017г.
Просмотров: 3049
Комментарии: 0
Основы блокчейна

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

19.12.2017г.
Просмотров: 3352
Комментарии: 0
Java 9. Полный обзор нововведений

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

16.02.2017г.
Просмотров: 7166
Комментарии: 0
Опоздавших не бывает, или книга о стеке

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

17.05.2016г.
Просмотров: 10542
Комментарии: 0
Теория вычислений для программистов

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

30.03.2015г.
Просмотров: 12260
Комментарии: 0
От математики к обобщенному программированию

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

18.02.2014г.
Просмотров: 13895
Комментарии: 0
Рецензия на книгу «Читаем Тьюринга»

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

13.02.2014г.
Просмотров: 9029
Комментарии: 0
Читайте, размышляйте, действуйте

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

12.02.2014г.
Просмотров: 6991
Комментарии: 0
Рисуем наши мысли

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

10.02.2014г.
Просмотров: 5299
Комментарии: 3
Страна в цифрах

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

18.12.2013г.
Просмотров: 4524
Комментарии: 0
Большие данные меняют нашу жизнь

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

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

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

Удачи!


Комментарии
 
  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()

  28.12.2007 - 04:57 |  Yo!

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

  29.12.2007 - 10:54 |  DocAl

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

«  1     »

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

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

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

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