September 8, 2008

Аналог \dT в SQL (mustread!!!)

Очень полезная вещь, я считаю...

Перевод SQL equivalent to \dT с pgsql-general

Bram Kuijper для pgsql-general

Всем привет,

Если я хочу получить список типов (т.е., типов данных или перечислений), то я могу выпонить комманду '\dT' в клиенте PostgreSQL.

Однако, я, похоже, не могу выяснить альтернативу \dT комманды в SQL, т.е. как бы я мог получить список типов с помошью SQL скрипта.

Напимер, если я создаю свой собственный ENUM:
CREATE TYPE bird AS ENUM('duck','goose');
то, быстро просмотрев information schema не просто обнаружить где храниться это перечисление. Является ли information schema правильным местом для поиска? Какое SQL выражение мне нужно для получения определённых пользователем типов?

заранее спасибо,
Bram Kuijper

Pavel Stehule для Bram, pgsql-general

Привет

Bram Kuijper:
> Всем привет,
>
> Если я хочу получить список типов (т.е., типов данных или перечислений),
> то я могу выпонить комманду '\dT' в клиенте PostgreSQL.
>

запустите psql с -E параметром. И увидите все SQL запросы используемые для метакомманд.
[pavel@localhost ~]$ psql -E postgres
psql (8.4devel)
Type "help" for help.

postgres=# \dT
********* QUERY **********
SELECT n.nspname as "Schema",
pg_catalog.format_type(t.oid, NULL) AS "Name",
pg_catalog.obj_description(t.oid, 'pg_type') as "Description"
FROM pg_catalog.pg_type t
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM
pg_catalog.pg_class c WHERE c.oid = t.typrelid))
AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid =
t.typelem AND el.typarray = t.oid)
AND pg_catalog.pg_type_is_visible(t.oid)
ORDER BY 1, 2;
**************************

List of data types
Schema | Name | Description
------------+-----------------------------+----------------------------
pg_catalog | abstime | absolute, limited-range date and time (Unix system time)
pg_catalog | aclitem | access control list
pg_catalog | "any" |
pg_catalog | anyarray |
pg_catalog | anyelement |

regards
Pavel Stehule

September 7, 2008

Проблема использования индексов с OFFSET LIMIT

Недавно вёл обсуждение касающееся pagination для очень большого количества записей (кстати, кое-кто обещал мне показать реализацию ;) и пришел к такому-же выводу...

Перевод Indexing problem with OFFSET LIMIT с pgsql-general

Oliver Weichhold для pgsql-general

Привет

У меня проблема в моём приложении и я не знаю как её решить.

Вот таблица и один из индексов:
CREATE TABLE foo
(
id serial NOT NULL,
foo_name character varying(100),
realm_id integer

... и ещё около 50 колонок
)

CREATE INDEX idx_foo_name_realm
ON foo
USING btree
(realm_id, foo_name);
В таблице foo около 8 миллионов строк.

Сама проблема:

Рассмотрим запрос:
SELECT * FROM foo WHERE realm_id = 228 order by foo_name LIMIT 200 OFFSET 15000
И его план выполнения:
Limit  (cost=57527.13..58294.16 rows=200 width=575) (actual time=182.302..184.971 rows=200 loops=1)
-> Index Scan using idx_foo_name_realm on foo (cost=0.00..62159.98 rows=16208 width=575) (actual time=0.085..166.861 rows=15200 loops=1)
Index Cond: (realm_id = 228)
Total runtime: 185.591 ms
А теперь рассмотрим этот:
SELECT * FROM foo WHERE realm_id = 228 order by foo_name LIMIT 200 OFFSET 15999
Limit  (cost=59601.92..59602.42 rows=200 width=575) (actual time=1069.759..1072.310 rows=200 loops=1)
-> Sort (cost=59561.92..59602.44 rows=16208 width=575) (actual time=929.948..1052.620 rows=16199 loops=1)
Sort Key: foo_name
Sort Method: external merge Disk: 8984kB
-> Bitmap Heap Scan on foo (cost=306.69..54270.62 rows=16208 width=575) (actual time=9.612..235.902 rows=21788 loops=1)
Recheck Cond: (realm_id = 228)
-> Bitmap Index Scan on foo_realm_id (cost=0.00..302.64 rows=16208 width=0) (actual time=8.733..8.733 rows=21810 loops=1)
Index Cond: (realm_id = 228)
Total runtime: 1084.706 ms
Время выполнения увеличилось в 10 раз из-за того, что postgres перестал использовать индексы.

Может кто-нибудь объяснить что происходит и как это обойти? Является ли это проблемой памяти?

Merlin Moncure для Oliver, pgsql-general

Oliver Weichhold:
> Hello
>
> I have problem in my applications and don't know how to fix it.
>
> ...

Попробуйте это:
SELECT * FROM foo WHERE realm_id = 228 order by realm_id, foo_name
LIMIT 200 OFFSET
15000
Или, что даже лучше, не используйте 'offset' вообще. Это просто не потребно. Если вы хотите пропустить 200 строк разом, сделайте это отдельным запросом:

1 раз:
select * from foo order by realm_id, foo_name limit 200;
после этого:
select * from foo where (realm_id, foo_name) > (last_realm_id,
last_foo_name) order by realm_id, foo_name limit 200;
и вы будете приятно удивлены :-). Это также будет более правильно по отношению к другим сессиям, которые делают операции добавления/удаления.

merlin

Tom Lane для Merlin, Oliver, pgsql-general

Merlin Moncure:
> Oliver Weichhold:
>> Рассмотрим запрос:
>>
>> SELECT * FROM foo WHERE realm_id = 228 order by foo_name LIMIT 200 OFFSET
>> 15000
> Попробуйте это:
>
> SELECT * FROM foo WHERE realm_id = 228 order by realm_id, foo_name
> LIMIT 200 OFFSET
> 15000
>
> Или, что даже лучше, не используйте 'offset' вообще. Это просто
> не потребно. Если вы хотите пропустить 200 строк разом, сделайте
> это отдельным запросом:

Да, большие значения OFFSET всегда вредны для производительности, т.к. пропуск этих строк конечно же не волшебство - бэкэнд должен прочесть их и потом откинуть. Избегайте OFFSET.

Отступив от темы хочу спросить - вы не рассматривали вариант с использованием курсоров?

regards, tom lane

В ожидании 8.4 - \ef в psql

Перевод Waiting for 8.4 - \ef in psql с select * from depesz;

Сегодня Tom Lane применил патч, написанный Abhijit Menon-Sen, который добавляет интересную возможность в psql. А именно - упрощает изменение определения функций.

Сообщение патча наиболее полно всё объясняет:
Реализация psql комманды "\ef" для редактирования определения функции.
В поддержку этого, создание backend-функции pg_get_functiondef().
Комманда функциональна, но, возможно, потребует небольших улучшений...

Abhijit Menon-Sen
Более подробно.

Ранее, для изменения определения функции, надо было сделать дамп схемы базы данных, найти функцию, сделать изменения и загрузить в psql.

Другим способом было хранение creation.sql файла, и его изменение когда необходимо изменить функцию.

Но сейчас всё стало проще. Достаточно вызвать \ef function_name и предпочитаемый вами редактор ($EDITOR) откроется с полным "CREATE OR REPLACE FUNCTION..." на редактирование.

В случае, когда есть несколько функций с указанным именем, вы получите интересное сообщение об ошибке:
# \ef texticlike
ERROR: more than one function named "texticlike"
LINE 1: SELECT 'texticlike'::pg_catalog.regproc::pg_catalog.oid
^
И тогда вы сможете вызвать \ef с параметрами функции:
# \ef texticlike(citext, text)
Великолепное дополнение. psql становится всё лучше и лучше.

September 6, 2008

Охота на “idle in transactions”

Привет

Я извиняюсь за то, что вот уже несколько дней от меня нет ни строчки, но суета связанная с переездом в МСК отнимает практически всё время, плюс редко удаётся выйти в сеть. Обещаю в ближайшее время исправить ситуацию, а пока вот...

Перевод Hunting "idle in transactions" с select * from depesz;

Если вы когда-нибудь встречались с коннектами в “idle in transaction”, то вы скорее всего ненавидите их. Я знаю это точно, т.к. сам их ненавижу. Они мешают таким "класным штукам" как репликация, вакуум, DDL запросы.

Так что когда я замечал их в в какой-либо базе, я сразу решал принимать меры.

Легко сказать, сложно сделать. Как же устранить проблему?

Я, конечно, мог бы убить все процессы postgres'а в сотоянии “idle in transaction”, но это не безопасное и, определённо, не оптимальное решение.

Лучшим решением было бы исправить те части кода, которые создают коннекты, ожидающие (простаивающие в) транзакции. Но как?

Прежде всего давайте зададим простой вопрос: что же такое соединение в “idle in transaction”?

Всё очень просто: это удерживающий соединение процесс postgres, который начал транзакцию, возможно что-то делал в её рамках, но сейчас ничего уже не делает, и транзакция всё ещё остаётся открытой ожидая COMMIT'а или ROLLBACK'а.

Проверка pg_stat_activity не скажет нам ни чего - соединение просто в ожидании. Мы, конечно, узнаем хост/порт коннекта, какую базу данных он использует и под каким пользователем. Но в большенстве случаев этого не достаточно. Ожидающие в транзакции соединения берут начало в серверах приложений и, не смотря на то что известно какое приложение послужило их причиной, не известно в какой его части искать ошибку.

К счастью, немного манипуляций на Perl, и мы сможем получить некоторую информацию.

Стачала настроим PostgreSQL так, что он будет логировать все запросы с точными "таймстэмпами" и длительностью выполнения запросов. Например, используя такие настройки:
            name            |     setting

----------------------------+-----------------

log_line_prefix | %m %u@%d %p %r

log_min_duration_statement | 0
Логи будут выглядеть так:
2008-08-28 16:58:31.978 CEST some_user@some_database 26001 10.3.5.1(45094) LOG:  duration: 1.387 ms  statement: SELECT ...
Интересующие части это "таймстэмп" вначале и длительность запроса.

Первое что надо знать, это то, что "таймстэмп" показывает время окончания выполнения запроса. Т.к. он равен '2008-08-28 16:58:31.978' и длительнось немного больше 1млс, то можно полагать, что время начала запроса '2008-08-28 16:58:31.977'.

Теперь мы знаем время начала и окончания запроса.

Анализируя весь лог мы можем выяснить разницу между окончанием одного запроса и началом другого.

Основываясь на этом я написал программу, которая делает анализ и выводит интересующую информацию.

Вот таким образом:
=> zcat postgresql-2008-08-28_165831.log.gz | ./find-iit-connections.pl 2>/dev/null

162ms @ 2008-08-28 16:58:55.795 CEST (pid: 8570), queries:

- begin

- some_query

- other_query

- another

- the query *after* long idle in transaction
Она ищет периоды idle-in-transactions больше 100млс. Изменить время можно в строке:
my $MINIMAL_IDLE_TO_REPORT = 100;
Вывод отображает то как долго было ожидание в транзакции (162млс), в какое время оно закончилось, pid процесса, кторый был в этом состоянии, и список всех запросов (с начала транзакции) за которыми это последовало.

С этой информацией найти проблемный участок кода становится намного легче.

Комментарии

David Fetter, 30 августа 2008 в 14:14
Не было бы проще загрузить CVS логи (см. снизу траницы: http://www.postgresql.org/docs/current/static/runtime-config-logging.html) в аналитическую БД и выполнить SQL операцию над ними?
Cheers,
David.

depesz, 30 августа 2008 в 22:28
Определённо, но проблема в том, что баа данных, которую я имел ввиду, работает под 8.2., а CVS логи добавлены только в 8.3.

Robert Treat, 1 сентября 2008 в 15:00
На некоторых системах включение логирования запросов звучит довольно пугающе. Я время от времени пользуюсь dtrace для таких вещей, но когда речь идёт о IIT, надо быть довольно удачливым, чтобы получить всю верную информацию. Однако, одна вешь, которую можно сделать, это посмотреть на pg_locks для того, чтобы определить что блокируется IIT. Вместе с информацией о пользователе/хосте/приложении этого часто бывает достаточно для локализации той самой части приложения и без логирования всех запросов.