20070329 phpconf2007 training
TRANSCRIPT
![Page 1: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/1.jpg)
Использование PostgreSQL в веб-приложениях
Николай СамохваловИван Золотухин
компания Postgresmen
29 марта 2007, Москва, PHPCenter
![Page 2: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/2.jpg)
План действий
1100 Первый кофе-брейк1215 Второй кофе-брейк1330 Обед1530 Третий кофе-брейк1645 Четвёртый кофе-брейк1800 Завершение программы
![Page 3: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/3.jpg)
План работ
● Вводные темы● Установка. Инструментарий● Устройство PostgreSQL● Оптимизация● Программирование● Полнотекстовый поиск и многое другое
![Page 4: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/4.jpg)
Что такое PostgreSQL?
PostgreSQL – свободно распространяемая объектно-реляционная система управления базами данных (ORDBMS), наиболее развитая из открытых СУБД в мире и являющаяся реальной альтернативой коммерческим базам данных.
![Page 5: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/5.jpg)
Что такое PostgreSQL?
PostgreSQL произносится как post-gress-Q-L
В разговоре часто употребляется postgres (пост-гресс).
Также, употребляется сокращение pgsql (пэ-жэ-эс-ку-эл).
![Page 6: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/6.jpg)
Что такое PostgreSQL?
![Page 7: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/7.jpg)
Функциональность
![Page 8: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/8.jpg)
Некоторые ограничения
![Page 9: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/9.jpg)
PostgreSQL сегодня
Текущая версия – 8.2 (8.2.3)
Релиз версии 8.3 ожидается в июне 2007
![Page 10: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/10.jpg)
PostgreSQL сегодня● Надежность
ACID, MVCC, WAL, PITR, Slony
● Безопасность данныхroot, SSL, pg_hba.conf, ROLE
● ПроизводительностьB-tree, hash, R-tree, GiST, Gin; geqo; partitioning; Slony, pgpool
● Расширяемостьpg_catalog, наследование, GiST, Gin, contribs
![Page 11: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/11.jpg)
PostgreSQL сегодня
● ISO/ANSI SQL (SQL:200x)схемы, представления, триггеры, rules, 2PC...
● Типы данныхvarlena, массивы, GIS, композитные, GiST
● ИнтерфейсыC, C++, C#, python, perl, ruby, php, Lisp и т.д.
● Процедурные языкиPL/pgSQL, pl/Tcl, Pl/Perl и pl/Python; PHP, Java, Ruby, R, shell
![Page 12: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/12.jpg)
PostgreSQL сегодня
● Простота
![Page 13: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/13.jpg)
Кто использует
● Sony Entertainment (EnterpriseDB)● Skype● Cisco● Fujitsu● NTT● Apple● SUN Microsystems (Solaris, 24x7 support)
![Page 14: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/14.jpg)
Кто использует
● SourceForge● LAMP: Linux/Apache/ Middleware(Perl,PHP,Python,Ruby)/
PostgreSQL● New Zealand's Electoral Enrolment Centre● .ORG, .INFO domain registry ● Многотерабайтные архивы астрономических данных
![Page 15: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/15.jpg)
Кто использует: Россия
● Рамблер● 1С:Предприятие (наряду с MS SQL)● Многие крупные проекты Рунета● Beeline
![Page 16: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/16.jpg)
Интерфейсы абстракции (РНР)
✔ Good Thing
Примеры:
● PDO● PEAR::DB● ADOdb● Creole● DbSimple
![Page 17: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/17.jpg)
ПО на РНР с поддержкой PostgreSQL
● Blog: Serendipity, http://s9y.org/● Wiki: Mediawiki, http://www.mediawiki.org/● CMS: Drupal, http://drupal.org/● CMF: CakePHP, http://www.cakephp.org/● Bug-tracker: Mantis, http://www.mantisbt.org/● Forum: FUDforum, http://fudforum.org/
![Page 18: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/18.jpg)
Установка в связке с PHP
● cd postgresql-8.2.3
./configure
make
make install
● cd php-5.2.1
./configure –with-pgsql [--with-apxs]
make
make install
![Page 19: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/19.jpg)
Начальная настройка
Всего 2 файла:● postgresql.conf (основные настройки)
– listen_addresses (сетевые интерфейсы)
– shared_buffers (общая память)
– work_mem (память для сортировок)
● pg_hba.conf (настройки доступа)– host all all 127.0.0.1/32 md5
![Page 20: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/20.jpg)
Стартовые скрипты
● Linux: contrib/start-scripts/linux
● FreeBSD: contrib/start-scripts/freebsd
● Windows: обычная служба● Любая другая ОС: не изобретайте, все
придумано до вас. Google it!
![Page 21: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/21.jpg)
Терминал psql
● отличный терминал● многострочные команды и история (Сергей
Копосов) \n
● autocompletion Tab
● встроенная помощь \h
● \i, \o, psql -c, echo | psql
● psql < file, etc
![Page 22: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/22.jpg)
GUI средства: pgAdmin III
![Page 23: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/23.jpg)
GUI средства: phpPgAdmin
![Page 24: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/24.jpg)
Устройство PostgreSQL
![Page 25: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/25.jpg)
Устройство PostgreSQL
![Page 26: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/26.jpg)
Устройство PostgreSQL
● ACID-совместимая база данных– atomicity (атомарность)
– consistency (непротиворечивость)
– isolation (изоляция)
– durability (надежность)
![Page 27: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/27.jpg)
Устройство PostgreSQL
● MVCC: Multiversion Concurrency Control– xid – transaction id
– каждая запись имеет xid_start и xid_end
– каждая транзакция видит версию базы в момент xid_start
– записи не удаляются, а просто помечаются xid_end
![Page 28: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/28.jpg)
Устройство PostgreSQL
● MVCC – накапливаются старые версии данных
● требует пылесоса – VACUUM● VACUUM: re-use мертвых данных● VACUUM FULL: физическое удаление
мертвых данных и дефрагментация базы● autovacuum
![Page 29: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/29.jpg)
Устройство PostgreSQL
WAL – Write Ahead Log● механизм протоколирования всех
транзакций● позволяет восстановить систему после
возможных сбоев● все изменения данных записываются на
диск только после их гарантированного журналирования в WAL
● PITR – Point In Time Recovery
![Page 30: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/30.jpg)
Устройство PostgreSQL
![Page 31: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/31.jpg)
postgresql.conf● shared_buffers
~ ¼ RAM, старайтесь положить всю базу в память!
● work_mem
свободная RAM / макс. число соединений
● effective_cache_size
средний размер дискового кеша
● checkpoint_timeout
несколько минут
● checkpoint_segments
N * 16MB
![Page 32: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/32.jpg)
Производительность PostgreSQL
Hardware● Диски > RAM > CPU● Чем больше дисков, тем лучше● Отделяйте pg_xlog от данных● RAID 1+0 / 0+1 > RAID 5● Не ставьте на сервер другие приложения
![Page 33: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/33.jpg)
Индексы в PostgreSQL
● B-tree● Hash● R-tree● GiST (обобщенное поисковое дерево)● GIN (обратный индекс)
![Page 34: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/34.jpg)
Обработка запроса в PostgreSQL
1. Parser (синтаксический анализатор)
2. Planner (выбор оптимального пути)
3. Executor (непосредственное выполнение)
SQL – декларативный язык. СУБД решает, как именно будет выполняться запрос.
![Page 35: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/35.jpg)
Обработка запроса в PostgreSQL
1. Parser (синтаксический анализатор)
2. Planner (выбор оптимального пути)
3. Executor (непосредственное выполнение)
SQL – декларативный язык. СУБД решает, как именно будет выполняться запрос.
![Page 36: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/36.jpg)
EXPLAIN ANALYZE
● План запроса – дерево● Узлы – действия
– соединения (join)
– сортировка
– просмотр таблицы
● Выполнение происходит от листьев к корню● Оценка количества строк и стоимости
![Page 37: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/37.jpg)
EXPLAIN ANALYZE
● Способы просмотра таблицы– Seq Scan
– Index Scan
● Способы подготовки данных– Sort
– Hash
● Способы соединения (join)– Nested Loop
– Merge Join
– Hash Join
![Page 38: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/38.jpg)
pgFouine: анализ логов
![Page 39: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/39.jpg)
pgFouine: анализ логов
![Page 40: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/40.jpg)
Наследование
CREATE TABLE obj ( obj_id INTEGER, obj_created TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE person( obj_id INTEGER PRIMARY KEY, person_name VARCHAR(32) NOT NULL, CHECK(obj_id >= 1000000000 AND obj_id < 2000000000)) INHERITS(obj);
CREATE TABLE car( obj_id INTEGER PRIMARY KEY, obj_type_id INT2 NOT NULL DEFAULT 2, car_model VARCHAR(16) NOT NULL, CHECK(obj_id >= 2000000000 AND obj_id < 3000000000)) INHERITS(obj);
![Page 41: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/41.jpg)
Функции / хранимые процедуры
● SQL
● PL/pgSQL
● PL/Perl
● PL/Tcl
● PL/Python
● PL/R
● PL/Java
● plPHP
● plRuby
● ...и даже больше!
![Page 42: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/42.jpg)
Функции: SQL
CREATE OR REPLACE FUNCTION hello(text)RETURNS textAS $BODY$ SELECT 'Hello ' || $1 || '!';$BODY$ LANGUAGE sql IMMUTABLE;
![Page 43: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/43.jpg)
Функции: PL/pgSQL
CREATE OR REPLACE FUNCTION hello(text)RETURNS textAS $BODY$BEGIN RETURN 'Hello ' || $1 || '!';END;$BODY$ LANGUAGE plpgsql IMMUTABLE;
![Page 44: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/44.jpg)
Триггеры
CREATE OR REPLACE FUNCTION ts2_page_title()RETURNS "trigger"AS $BODY$BEGIN
IF TG_OP = 'INSERT' THENNEW.titlevector = to_tsvector('default',NEW.page_title);
ELSIF NEW.page_title <> OLD.page_title THENNEW.titlevector := to_tsvector('default',NEW.page_title);
END IF;RETURN NEW;END;$BODY$ LANGUAGE plpgsql VOLATILE;
CREATE TRIGGER ts2_page_title BEFORE INSERT OR UPDATEON page FOR EACH ROW EXECUTE PROCEDURE ts2_page_title();
![Page 45: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/45.jpg)
Rules и представления
CREATE VIEW myview AS SELECT * FROM mytab;
CREATE TABLE myview(...);CREATE RULE "_RETURN" AS ON SELECT TO myview
DO INSTEADSELECT * FROM mytab;
![Page 46: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/46.jpg)
GiST – Generalized Search Tree
● R-tree● tsearch2● intarray● pg_trgm● ltree● hstore● pg_sphere
![Page 47: 20070329 Phpconf2007 Training](https://reader033.vdocuments.us/reader033/viewer/2022060109/5558e9aad8b42ad7138b5224/html5/thumbnails/47.jpg)
The End
Спасибо за внимание!