Вступление

intro.jpg

Зачем?!?!

  • обострение дискуссий за последнее время
  • много драмы, неквалифицированных мнений
  • необъективность лидеров PostgreSQL сообщества

why.png

Зачем?!?!

  • Серия статей «Памятка евангелиста PostgreSQL» на Хабре
  • «Даёшь сравнение!!!» – самый частый запрос
  • действительно, зачем использовать MySQL?

innodb_users.png

Холивор?!?!

Нет!

  • нет универсального ответа на вопрос «какая СУБД лучше?»
  • PostgreSQL – замечательная СУБД! Нет, серьёзно.
  • но этот доклад не об этом ;)

О чём доклад?

  • о сильных сторонах MySQL
  • о возможностях MySQL, отсутствующих в PostgreSQL
  • почему крупнейшие веб-проекты используют MySQL?

MySQL сегодня

Что с чем сравнивать?

  • текущие стабильные релизы MySQL и PostgreSQL
  • форки? Да!
  • сторонние решения? Да!
  • только open source, только хардкор!

Репликация

Логическая:

  • изменение данных

Физическая:

  • изменение файлов данных

Репликация

Сильные стороны логической репликации:

  • независимость от физической структуры данных
  • позволяет иметь разные схемы на мастере и реплике (при условии обратной совместимости)
  • нет ограничений на чтение с реплик
  • сложные топологии: каскадная, multi-source, multi-master, и т. д.
  • временные таблицы
  • частичная репликация
  • компактность

Слабые стороны логической репликации:

  • более требовательна к ресурсам

Репликация

Логическая репликация в MySQL:

  • появилась в 3.23 (2001г.)
  • одна из основных причин популярности MySQL
  • длинный эволюционный путь
    • от statement-based до row-based
    • от одно-поточной до параллельной
    • от schema-based параллелизма в 5.6 до logical clock в 5.7 и оптимистичной репликации в MariaDB 10.1
    • PostgreSQL в самом начале этого пути

Репликация

Логическая репликация в MySQL:

  • поддерживает DDL, BLOBы, системные таблицы
  • GTID для упрощения failover, добавления новых реплик
  • серьёзные увеличения производительности/параллельности в 5.7

booking_mts.png

booking_mts.png

Репликация

Логическая репликация в Booking.com:

  • 2 миллиарда запросов в день
  • ~3500 серверов, 85% в репликации
  • 130 мастеров:
    • ~25 реплицируют на >50 реплик
    • 8 реплицируют на >100 реплик
  • очень сложные топологии + binlog серверы
  • активно используют и развивают параллельную репликацию в MySQL/MariaDB
  • много пишут о своём опыте настройки репликации

Репликация

Физическая репликация в PostgreSQL:

  • надёжная, простая в настройке физическая репликация
  • до тех пор, пока возможностей физической репликации хватает
  • теоретически быстрее, чем логическая в MySQL на определённых нагрузках.
  • на каких нагрузках и насколько быстрее? никто не знает…

Репликация

Логическая репликация в PostgreSQL:

  • много решений с разными недостатками
  • logical decoding + PGLogical – наиболее перспективный кандидат
  • но стороннее решение, в beta стадии и с серьёзными ограничениями:
    • нет репликации DDL
    • сложности с репликацией sequences
    • не поддерживает rolling schema upgrade
    • ручной schema-based параллелизм
    • координаты? клонирование? перепозиционирование?

Репликация

Полусинхронная (semi-synchronous) репликация:

  • разработана в Google в 2007г.
  • включена в основное дерево MySQL
  • активно используется и развивается в Facebook
  • коммит на мастере гарантирует получение данных хотя бы одной из реплик
  • позволяет ослабить durability на мастере и репликах
  • нет аналога в PostgreSQL (synchronous_standby_names близко по смыслу, но не то)

Galera Cluster

  • внешняя библиотека от Codership
  • включена в MariaDB, Percona XtraDB Cluster
  • параллельная (виртуально) синхронная multi-master репликация
  • масштабирование чтений (всегда локальны)
  • масштабирование записи (условно)
  • нет централизованного управления / единой точки отказа
  • автоматическое включение/исключения узлов
  • автоматическое создание/пересоздание узлов (node provisioning)
  • нет аналогов в PostgreSQL

Движки хранения

Движки хранения:

  • концепция похожа на VFS в ядре Linux
  • абстрагируют физическое представление данных/индексов от ядра выполнения запросов
  • могут хранить данные:
    • на диске (MyISAM, InnoDB, CSV, Archive, …)
    • в памяти (Memory)
    • на другом MySQL/MariaDB сервере (Federated{X})
    • на других не-MySQL/MariaDB серверах (Cassandra, Connect)
    • в распределённом кластере (NDB, ScaleDB)
    • в специализированном виде (MyRocks, TokuDB, ColumnStore)
    • или вообще не хранить пользовательских данных (PERFORMANCE_SCHEMA, Pinba)

Движки хранения: InnoDB

  • "рабочая лошадка" современного интернета
  • возможно самая обкатанная и оптимизированная реализация B+Tree

innodb_users.png

InnoDB: кластеризованные индексы

storage_format.png

storage_format.png

InnoDB: особенности кластеризованного индекса

Плюсы:

  • запросы по первичному ключу очень быстрые
  • скан первичного ключа обычно приводит к последовательному чтению с диска
  • первичный ключ является покрывающим индексом для любых запросов
  • вторичные индексы являются покрывающими для своих + PK колонок

Минусы:

  • PK нельзя запретить (напр. для импорта)
  • непокрывающие вторичные индексы требуют дополнительную операцию поиска
  • если первичный ключ "толстый", вторичные индексы занимают много места

innodb_format.png

InnoDB: особенности кластеризованного индекса

Кластеризованные индексы в PostgreSQL:

  • отсутствуют
  • CLUSTER делает одноразовую реорганизацию
  • старый и популярный пункт в PostgreSQL TODO

Плюсы:

  • массовый импорт данных можно оптимизировать
  • нет дополнительных расходов от "толстых" первичных ключей
  • нет дополнительной операции поиска по вторичным ключам

Минусы:

  • многие типичные операции с B-Tree индексами медленнее

storage_format.png

InnoDB: компактность данных

Стандартная таблица sysbench, 1M записей:

  • MySQL
CREATE TABLE sbtest1 (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
  c CHAR(120) DEFAULT '' NOT NULL,
  pad CHAR(60) DEFAULT '' NOT NULL
);
CREATE INDEX k_1 ON sbtest1(k);
  • PostgreSQL
CREATE TABLE sbtest1 (
  id SERIAL NOT NULL PRIMARY KEY,
  k INTEGER DEFAULT '0' NOT NULL,
  c CHAR(120) DEFAULT '' NOT NULL,
  pad CHAR(60) DEFAULT '' NOT NULL
);
CREATE INDEX k_1 ON sbtest1(k);

InnoDB: компактность данных

  data index total
InnoDB 214.70 19.55 234.25
PostgreSQL 211.23 50.10 261.33

innodb_datasize.png

innodb_datasize.png

InnoDB: компрессия

MySQL:

  • потабличная компрессия (2008, 5.1+):
    • zlib
    • сжатые + несжатые страницы в buffer pool, только сжатые на диске
    • интенсивное используется интернет-гигантами (Facebook и пр.)
  • постраничная компрессия (transparent page compression) (2015, 5.7):
    • zlib или LZ4
    • несжатые страницы в памяти, сжатые на диске
    • использует "разряжённые" файлы

InnoDB: компрессия

PostgreSQL (TOAST):

  • использует собственный вариант LZ
  • только для длинных (>2 KB) записей
  • только для полей переменной длины
  • только для страниц heap (не для индексов)
  • каждое поле в записи сжимается отдельно
  • каждое сжатое значение расжимается при каждом чтении
  • каждое сжатое значение пережимается при каждом обновлении

InnoDB: компрессия

Та же таблица (sbtest1, 1M записей)

  data index total
InnoDB 214.70 19.55 234.25
PostgreSQL 211.23 50.10 261.33
InnoDB, zlib 115.38 10.27 125.65

innodb_datasize_compress.png

innodb_datasize_compress.png

InnoDB: IO amplification

  • Проблема:
    • при обновлении даже одного байта, нужно записать всю страницу (8/16K)
    • для чтения только одной записи нужно прочитать всю страницу
  • InnoDB:
    • при создании базы: innodb_page_size = 16K (4K, 8K, 16K, 32K, 64K)
    • фиксирован: OS_FILE_LOG_BLOCK_SIZE=512
  • PostgreSQL:
    • при компиляции сервера: BLCKSZ = 8K
    • при компиляции сервера: XLOG_BLCKSZ = 8K (1K, 2K, 4K, 8K, 16K, 32K, 64K)

InnoDB: шифрование

InnoDB:

  • client-side, disk-level или column-level шифрование
  • data at rest шифрование в MariaDB 10.1.3 (2015), патч от Google
  • tablespace шифрование в MySQL 5.7.11 (2016), тот же патч?
  • востребованная функция в облачных, хостинговых и контейнерых окружениях

PostgreSQL:

InnoDB: поддержка O_DIRECT

  • запись данных в обход кэша ядра
  • более рациональное использование памяти
  • нет накладных расходов на двойное кэширование/буферизацию
  • более тонкий контроль над записью на диск
  • появилась в 2003 году (только для файлов данных), используется по умолчанию
  • в 2011 – ALL_O_DIRECT (в Percona Server, MariaDB/XtraDB)

InnoDB: поддержка O_DIRECT

O_DIRECT в PostgreSQL:

  • только для WAL
    • с XLOG_BLCKSZ=8 write amplification взлетает до небес
    • плохо для репликации
  • неэффективное использование памяти (shared_buffers = ~25% RAM)
  • двойная буферизация
  • излишняя работа для контрольных сумм (в будущем: шифрования, компрессии, и т.д.)

InnoDB: поддержка O_DIRECT

MySQL:

  • огромная работа в Percona и Oracle по сглаживанию TPS/latency при интенсивной записи:
    • fuzzy checkpointing, adaptive flushing, parallel flushing, parallel doublewrite

PostgreSQL:

pg_latency_spikes.png

pg_latency_spikes.png

InnoDB: за кадром:

Отсутствуют в PostgreSQL:

  • поддержка асинхронного ввода/вывода
  • поддержка NUMA
  • поддержка сохранения/восстановления состояния кэша
  • change buffer
  • adaptive hash index
  • transportable tablespaces

InnoDB: масштабируемость (RO)

InnoDB: масштабируемость (RW)

Производительность: потоки и процессы

  • MySQL использует потоки, а PostgreSQL процессы
  • fork()/clone() – быстрая операция в Linux
  • но не такая быстрая, как pthread_create()
  • даже если не учитывать thread cache в MySQL

sysbench, connect test, Linux, Xeon E5-2683v4:

  connect/disconnect time, ms
MySQL 5.7.13 с thread cache 0.20
MySQL 5.7.13 без thread cache 0.40
PostgreSQL 9.5.3 3.73
  • Медленнее в ~9 – 18 раз.
  • важно в нагруженных проектах
  • причина популярности pgBouncer

Движки: MyRocks, TokuDB

MyRocks и TokuDB:

  • оптимизированные на запись и SSD устройства движки
  • MyRocks – LSM-деревья, Facebook
  • TokuDB – «фрактальные» индексы, Percona
  • более компактное представление данных на диске
  • продвинутые возможности компрессии
  • низкий write amplification по сравнению с InnoDB
  • оптимизированы для Flash устройств
  • множественные кластеризованные индексы (TokuDB)
  • ничего похожего по характеристикам в PostgreSQL

Движки хранения: NDB

NDB:

  • in-memory хранилище с опциональными чекпойнтингом на диск и хранении неиндексированных данных на диске
  • 99.999% high availability без единой точки отказа
  • масштабируемость на чтение/запись – автоматический шардинг
  • active-active/multi-master репликация
  • несколько NoSQL API (Java, HTTP, Memcached, Node.js)

Движки хранения: NDB

MySQL + NDB:

  • разбор / оптимизация / выполнение SQL
  • несколько SQL API (libmysqlclient, JDBC, ODBC, …)
  • асинхронная репликация

Типичные области применения:

  • телекоммуникации (управление данными абонента)
  • платёжные, финансовые системы
  • PayPal: гео-распределённый кластер на 100 TB для обнаружения мошенничества (fraud detection)
  • Zynga: онлайн игры (сессии)

ndb_users.png

Движки хранения:

  • один алгоритм хранения данных не может подходить всем
  • важная причина популярности MySQL в нагруженных проектах
  • основной источник инноваций в MySQL
  • PostgreSQL тоже к этому придёт

Оптимизатор запросов

Оптимизатор: Loose Index Scan

  • появилась в MySQL 5.0 (2005)
  • оптимизирует определённые типы DISTINCT и GROUP BY запросов
CREATE TABLE t1m10cp(a int, b int);
CREATE INDEX t1m10cp_ab on t1m10cp(a,b);
SELECT MIN(b) FROM t1m10cp GROUP BY a;

loose_index_scan.png

loose_index_scan.png

Оптимизатор: Loose Index Scan

mysql> explain select min(b) from t1m10cp group by a\G
*************************** 1. row ***************************
	   id: 1
  select_type: SIMPLE
	table: t1m10cp
   partitions: NULL
	 type: range
possible_keys: t1m10cp_ab
	  key: t1m10cp_ab
      key_len: 10
	  ref: NULL
	 rows: 11
     filtered: 100.00
         Extra: Using index for group-by 

mysql> select min(b) from t1m10cp group by a;
+--------+
| min(b) |
+--------+
. . .
+--------+
11 rows in set (0.00 sec)

Оптимизатор: Loose Index Scan

PostgreSQL:

  • не поддерживает Loose Index Scan
  • можно эмулировать с помощью рекурсивных (и громоздких) CTE
  • ручная адаптация запросов под разные варианты
  • полезная статья на тему
sbtest=# explain select min(b) from t1m10cp group by a;
			      QUERY PLAN
-----------------------------------------------------------------------
 HashAggregate  (cost=21370.00..21370.11 rows=11 width=8)
   Group Key: a
   ->   Seq Scan on t1m10cp  (cost=0.00..16370.00 rows=1000000 width=8)

sbtest=# select min(b) from t1m10cp group by a;
 min
-----
. . . 
(11 rows)

 Time: 282.493 ms

> в 56 раз медленнее, чем в MySQL

Оптимизатор: подзапросы

  • исторически (до 5.6) слабая сторона MySQL
  • огромная работа по оптимизации подзапросов в MySQL 5.6, MariaDB 5.3
  • дальнейшие улучшения в MySQL 5.7, MariaDB 10.0/1
  • отдельные стратегии оптимизации работают лучше, чем в PostgreSQL

Пример коррелированного подзапроса: 

create table t10 (a int, b int);
insert into t10 select a, 1 from ten;
select count(*) from t1m10cp where pk in (select b from t10 where t10.a=t1m10cp.a);

Оптимизатор: подзапросы

MySQL: 

mysql> select count(*) from t1m10cp where pk in (select b from t10 where t10.a=t1m10cp.a);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Оптимизатор: подзапросы

MySQL: DuplicateWeedout стратегия 

*************************** 1. row ***************************
	   id: 1
  select_type: SIMPLE
        table: t10 
   partitions: NULL
	 type: ALL
	  key: NULL
	 rows: 10
     filtered: 100.00
        Extra: Using where; Start temporary 
*************************** 2. row ***************************
	   id: 1
  select_type: SIMPLE
        table: t1m10cp 
   partitions: NULL
	 type: eq_ref
	  key: PRIMARY
      key_len: 4
	  ref: sbtest.t10.b
	 rows: 1
     filtered: 10.00
        Extra: Using where; End temporary

Оптимизатор: подзапросы

PostgreSQL: «наивное» вычисление подзапроса 

sbtest=# explain analyze select count(*) from t1m10cp where pk in 
				(select b from t10 where t10.a=t1m10cp.a);
						      QUERY PLAN
------------------------------------------------------------------------------------
 Aggregate  (cost=583870.00..583870.01 rows=1 width=0) 
	    (actual time=1716.412..1716.412 rows=1 loops=1)
   ->  Seq Scan on t1m10cp  (cost=0.00..582620.00 rows=500000 width=0) 
			    (actual time=1716.411..1716.411 rows=0 loops=1)
	 Filter: (SubPlan 1)
	 Rows Removed by Filter: 1000000
	 SubPlan 1
	   ->  Seq Scan on t10  (cost=0.00..1.12 rows=1 width=4) 
				       (actual time=0.001..0.001 rows=1 loops=1000000)
		 Filter: (a = t1m10cp.a)
		 Rows Removed by Filter: 9
 Planning time: 0.064 ms
 Execution time: 1716.439 ms 
(10 rows)

> в 343 раза медленнее, чем в MySQL

Оптимизатор: хинты

  • нет идеальных оптимизаторов
  • хинты есть в Oracle, MySQL, SQL Server
  • один из самых популярных запросов от сообщества PostgreSQL

MySQL:

  • index hints
    • USE INDEX FOR [FOR {JOIN|ORDER BY|GROUP BY}]
    • IGNORE INDEX [FOR {JOIN|ORDER BY|GROUP BY}]
    • FORCE INDEX [FOR {JOIN|ORDER BY|GROUP BY}]
  • optimizer hints:
    • MAX_EXECUTION_TIME, BKA, MRR, NO_ICP, NO_RANGE_OPTIMIZATION, SEMIJOIN, SUBQUERY

PostgreSQL:

  • нет аналогов

Оптимизатор: трассировка

SET optimizer_trace="enabled=on";
SELECT ...; # your query here
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
...
	 {
	    "transformation": {
	      "select#": 2,
	      "from": "IN (SELECT)",
	      "to": "semijoin",
	      "chosen": true,
	      "evaluating_constant_semijoin_conditions": [
	      ]
	    }
	  },
  • трассировка процесса принятия решений планировщиком:
    • порядок таблиц в JOIN
    • трансформация запросов, подзапросов
    • применимые методы доступа и т.д.
  • появилась в MySQL 5.6
  • отсутствует в PostgreSQL

Секционирование (partitioning)

  MySQL PostgreSQL комментарий
?   наследование таблиц встроенное, trigger-based, RANGE, LIST, недекларативное, ручное управление, проблемы с большим кол-вом секций
2008 декларативное, 5.1   встроенное, RANGE, LIST, COLUMNS, KEY, HASH, вложенное, …
2012   pg_partman внешнее, наследование, но с автоматизация ручной работы, только time и serial ranges
2016   pg_pathman внешнее, (beta?), недекларативное
???   декларативное встроенное, 9.7?

Резервные копии

Сравнение возможностей утилит физического резервного копирования:

  Percona XtraBackup pg_basebackup barman pg_arman pgBackRest
полные ✔ ✔ ✔ ✔ ✔
кросс-платформенные ✔        
инкрементальные ✔   ✔¹ ✔² ✔¹
частичные ✔   ✔    
потоковые ✔       ✔
параллельные ✔       ✔
сжатые ✔ ✔³     ✔
"компактные" ✔        
зашифрованные ✔        
облачные ✔        

¹ – очень ограниченно и медленно (file-level)

² – на основе WAL файлов

³ – данные сжимаются после передачи

PERFORMANCE_SCHEMA

  • мета-движок для сбора статистики работы сервера
  • не только ожидание, частично перекрывается с pg_stat_*
  • появилась в 5.5 (2010)
  • много оптимизаций, улучшений в 5.6 и 5.7
  • статистика по ожиданиям на блокировках, IO
  • sys schema в 5.7 (user/dba-friendly обёртка)
  • нет аналогов в PostgreSQL (9.5)

Event Scheduler

Планировщик заданий

MySQL:

  • встроенный, начиная с 5.1

PostgreSQL:

  • встроенный отсутствует
  • cron (проблемы с репликация, бэкапами)
  • внешние: pgAgent, jpgAgent

key/value API

Сэкономить время на:

  • разбор SQL
  • открытие, блокировку таблиц
  • построение плана выполнения

MySQL:

  • HandlerSocket (сторонее)
  • memcached (встроенное)

PostgreSQL:

  • нет аналогов

За кадром:

  • работа MVCC для OLTP нагрузок
  • сравнение purge и AUTOVACUUM
  • эффективность транзакционного журналирования
  • встроенный thread pool (MariaDB / Percona)
  • другие особенности оптимизаторов
  • аутентификация (pg_hba.conf)
  • поддержка кодировок
  • поддержка Windows в современных версиях MySQL/MariaDB
  • динамические переменные
  • Document Store, X протокол и MySQL Shell в 5.7.12+
  • виртуальные колонки
  • компрессия соединений
  • MySQL Embedded: сервер в виде встраиваемой библиотеки

Когда PostgreSQL завоюет мир?

when.jpg

Когда PostgreSQL завоюет мир?

PostgreSQL есть чему поучиться у других популярных СУБД

db-engines.png

И PostgreSQL учится!

Когда PostgreSQL завоюет мир?

  • есть много причин использовать MySQL
  • многие важные для крупных веб-проектов возможности MySQL отсутствуют в PostgreSQL до сих пор
  • реализация их может растянуться на годы (если не десятилетия)

Когда PostgreSQL завоюет мир?

  • MySQL скорее всего останется "самой популярной open source СУБД для веб"
  • а PostgreSQL – "самой продвинутой open source СУБД"
  • выбор СУБД – сложный вопрос
  • бегите от людей, которые предлагают простые ответы! :)

Спасибо!

Вопросы, комментарии, истории?

questions.png