PostgreSQL и MySQL глазами Oracle DBA

1. PostgreSQL и MySQL глазами Oracle DBA Юрий Адамёнок adamenok@devexperts.com 15.03.2013

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

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

4. Схема. Oracle HR HR scott scott

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

6. Схема. Mysql app1 joe scott app2

7. Объекты БД

8. Объекты БД. Индексы

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

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

11. Различия в SQL

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

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

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

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

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

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

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

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

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

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

22. Резервное копирование и восстановление. Основы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

38. Undo. Summary.

39. MVCC.Transaction isolation levels

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

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

42. MVCC. MySQL. Locks Select * from employees where employee_id < 5 and employee_id != 2 for update; 1 1 2 3 4

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

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

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

46. Секционирование

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

48. Кластеризация и масштабируемость. Oracle. Shared everything 1 2 3 4

49. Кластеризация и масштабируемость. PostgreSQL and MySQL Shared nothing 1 2 3 4

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