PostgreSQL и MySQL глазами Oracle DBA Юрий Адамёнок

Введение Oracle Database PostgreSQLMySQL лицензий пользователей инсталяций Oracle Corporation Нет владельца. Множество спонсоров Oracle(MOS) Oracle Corporation 2ndQuadraint, EnterpriseDB support MySQL commertial support Standard: $17500 Enterprise with partitioning: $ $11500 Free

Содержание 1. База данных и схема. 2. Объекты БД. Таблицы. Индексы. 3. Различия в SQL. 4. Логические и физические структуры данных. 5. Механизм восстановления после сбоев. Резервное копирование. 6. Целостность чтения. Многоверсионность. Блокировки. 7. Настройки производительности. Оптимизатор запросов. Секционирование. 8. Средства обеспечения высокой доступности. Репликация. Масштабируемость. 9. Инструменты администратора баз данных.

Схема. Oracle HR scott

Схема. PostgreSQL app1 joe app2 QA app1 app2 DEV app1 app2 QA app1 QA scott

Схема. Mysql app1 joe scott app2

Объекты БД OraclePostgreSQLMysql(InnoDB) Heap-organized table N/A Index-organized tableN/ATablhbe with clustered index Index Secondary index ClusterN/A

Объекты БД. Индексы OraclePostgreSQLMysql(InnoDB) B-tree+++ Bitmap+N/A HashN/A(*)++ Function-based++N/A Local+++ Global+N/A PartialN/A+

Объекты БД. Индексы. PostgreSQL - Gin,Gist(полнотекстовые) индексы - Spatial - Могут быть перестроены online

Объекты БД. Индексы. MysqlSQL PK или первый Unique Key становится ключом IOT Если индексов нет, создаётся hidden индекс Могут существовать несколько идентичных индексов Не могут быть перестроены online до версии 5.6 !

Различия в SQL

Различия в SQL. PostgreSQL - DECODE - NVL - DUAL - CONNECT BY -...

Различия в SQL. MySQL - sql_modes - case sensitive table names(на UNIX) - select name, max(salary) from employees; -...

Логические и физические структуры хранения данных

Структуры хранения. Oracle Tablspace Segment data01.dbf data02.dbf

Структуры хранения. PostgreSQL Tablespace Segment Database Segment Database Segment Tablespace Segment Database Segment /opt/data/sales/ /opt/data/sales/228833/ /opt/data/sales/ /opt/data/sales/228833/ /opt/data/sales/228833/229967_vm /opt/data/sales/228833/229967_fsm

Структуры хранения. MySQL Database Segment Tablespace Segment Tablespace Segment /opt/mysql/hr/ /opt/mysql/hr/employees.ibd /opt/mysql/hr/employees.frm innodb_file_per_table

Резервное копирование. Механизмы восстановления после сбоев

Механизм восстановления экземпляра. Oracle Buffer pool Data file Redo Logs 21

Механизм восстановления экземпляра. PostgreSQL Buffer pool Data file Write Ahead Logs 21

Механизм восстановления экземпляра. MySQL Data file Redo Logs Doublewrite buffer Buffer pool 123

Резервное копирование и восстановление. Основы OraclePostgresMysql(InnoDB) Логическое резервное копирование Exp/Imp, Datapumppg_dump/pg_restoremysqldump/mysql Восстановление на заданный момент времени Базовый бэкап + инкрементальный бэкап + archivelogs Базовый бэкап + WAL Базовый бэкап(или дамп) + binlogs

Резервное копирование и восстановление. Oracle Архивирование логов: Archivelog mode. DB_RECOVERY_FILE_DEST. LOG_ARCHIVE_DEST_N Резервное копирование: RMAN. Возможности: горячий бэкап, политики хранения, инкрементальный бэкап, сжатие, работа с разными несколькими БД.

Резервное копирование и восстановление. PostgreSQL Архивирование логов: archive_mode = on archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' Резервное копирование: Копирование средствами ОС после команды pg_start_backup. Инструмент резервного копирования и восстановления от сторонних Разработчиков: Barman. Возможности Barman: горячий бэкап, политики хранения, сжатие, работа с разными несколькими БД.

Резервное копирование и восстановление. Mysql Сохранение логов: log_bin = /var/log/mysql/mysql-bin.log Создание базового бэкапа: mysqldump --all-databases --single-transaction --flush-logs Встроенного средства для создания физического бэкапа не существует Средства резервного копирования от сторонних разработчиков: Percona Xtrabackup, Zmanda, Mysql Enterprise Backup

Целостность чтения. Многоверсионность. Блокировки

Oracle. Undo XID 1. Старая версия блока помещается в rollback segment Rollback segment Table 2. В заголовке блока transaction id (XID) является указателем на UNDO

TX max = TX min =1 PostgreSQL. Undo. Insert 1. Создаётся запись с TX min новой записи равным идентификатору транзакции и пустым TX max

TX max = TX min =1 PostgreSQL. Undo. Update TX max = TX min = 5 5 FWD PTR 1. Создаётся обновлённая копия записи 2. TX min новой записи и TX max старой записи идентификаторы текущей транзакции 3. Forward pointer указывает на новую версию строки 4. Место в сегменте освобождается процессом VACUUM

PostgreSQL. Undo. Delete TX max = TX min = TX max удаляемой записи и идентификатор текущей транзакции 2. Место в сегменте освобождается процессом VACUUM

PostgreSQL. Undo. VACUUM Освобождает место в сегментах, очищая их от «мёртвых» строк(старых версий строк, которые не видны ни одной транзакции) TX max < TX id самой старой транзакции Собирает статистику оптимизатора (опционально) Выполняется в фоне процессом autovacuum или командой vacuum Может создавать значительный уровень IO

PostgreSQL. Undo. VACUUM Для уменьшения количества строк, которые читает VACUUM используются visibility maps. Visibility map содержит список страниц, которые видимы всем транзакциям. Страницы добавляются в списов только процедурой VACUUM. Удаляются из списка каждым процессом, изменившим данные. Каждый сегмент имеет свою visibility map. Хранится в отдельном файле рядом с сегментом.

PostgreSQL. Undo. Summary Индексы не имеют информации о транзакциях. Не бывает «snapshot too old». БД может разрастаться в размере при наличии долгих транзакций. Существует опасность Transaction id wraparound из-за циркулярности алгоритма выдачи TX id (если более чем 2 млрд транзакций не выполнялся vacuum)

TX id =1 Mysql. Undo. Insert ROLL PTR 1. Запись помещается в таблицу с TX id текущей транзакции 2. Rollback pointer указывает на запись в Rollback сегменте Rollback segment Table

TX id =5 Mysql. Undo. Update ROLL PTR 1. Старая версия записи помещается rollback segment Rollback segment Table TX id = 1 2. Rollback pointer указывает на запись в Rollback сегменте 3. TX id индентификатор текущей транзакции 1

TX id =5 Mysql. Undo. Delete 1. TX id индентификатор текущей транзакции Table 2. Выставляется флаг DELETED 3. Место высвобождается процессом Purge 10 DELETED FLAG

MySQL. Undo. Summary - Не бывает «snapshot too old error» - БД может разростаться в размере - Undo хранится в Undo tablespace начиная с версии 5.6. В более ранних Undo хранится в системном TS.

Undo. Summary. OraclePostgreSQLMysql(InnoDB) Единица UndoБлокСтрока Хранение прошлых версий Undo TablespaceВместе с данными Частично вместе с данными Visibility information в индексах Не требуется: используется Undo на индексы Segment Visibility Maps Только в кластерных индексах Удаление прошлых версий Повторное использование rollback- сегментов Процесс VACUUM Просесс PURGE и повторное использование rollback- сегментов

MVCC.Transaction isolation levels OraclePostgreSQLMysql(InnoDB) Read uncommitted N/A++ Read committeddefault + Repeatable read++default Serializable+++

MVCC. PostgreSQL Вместо Read uncommitted всегда Read committed Фантомные чтения отсутствуют в режиме Repeatable read Транзакционный DDL

MVCC. MySQL Repeatable read в качестве уровня изоляции по умолчанию. Блокировки по принципу «всё, что читаю»

MVCC. MySQL. Locks Select * from employees where employee_id < 5 and employee_id != 2 for update; 12134

Настройки производительности. Оптимизатор запросов. Секционирование

Query optimizer. PostgreSQL - Cost based - Статистика собирается процессом autovacuum или командой ANALYZE - Поддерживает гисторамы - Joins: Nested loops, Merge, Hash join

Query optimizer. MySQL - Cost based - Статистика собирается «на лету». В версии 5.6 ввели сохранение статистики. - Join: только методом Nested loops

Секционирование OraclePostgreSQLMysql(InnoDB) Способ секционирования RANGE, LIST,HASH RANGE, LISTRANGE, LIST,HASH Partition pruning+++ Распределение данных АвтоматическиПользовательским триггером Автоматически Глобальные индексы +N/A

Кластеризация и масштабируемость - Oracle RAC: shared everything архитектура - PostgreSQL PG-XC и другие решения: shared nothing архитектура - Mysql NDB Cluster и другие решения: shared nothing архитектура

Кластеризация и масштабируемость. Oracle. Shared everything 1234

Кластеризация и масштабируемость. PostgreSQL and MySQL Shared nothing 1234

Отказоустойчивые решения - Oracle: RAC, Data Guard - PostgreSQL: встроенная функциональность Hot standby, решения репликации и балансировки сторонних разработчиков - Mysql: встроенная репликация(в т.ч двусторонняя)

Средства администратора БД Oracle: AWR PostgreSQL: вывод проблемных запросов в текстовые лог-файлы. pg_stat_* таблицы. Mysql: вывод проблемных запросов в текстовые лог-файлы. performance_schema

Спасибо за внимание! Вопросы?

References