Ad Widget

**Alexei** · 22-07-2011, 13:01

Отличная статья, спасибо! PostgreSQL, в отличие от MySQL, считывает данные из всех партиций даже если данные находятся только в одной. Это заметно замедляет любые запросы по истории. Сталкивались с этой проблемой?

На самом деле, это является единственной причиной почему мы всё ещё официально рекомендуем пользоваться MySQL, а не PostgreSQL.

**dotneft** · 22-07-2011, 15:53

Нет таких проблем на наблюдалось.

Code:

explain 
select distinct itemid from history_uint where clock>=1311339600 
and itemid in (3344);

Unique  (cost=0.00..17.98 rows=6 width=8)
  ->  Result  (cost=0.00..17.96 rows=6 width=8)
        ->  Append  (cost=0.00..17.96 rows=6 width=8)
              ->  Index Scan using history_uint_1 on history_uint  (cost=0.00..4.26 rows=1 width=8)
                    Index Cond: ((itemid = 3344) AND (clock >= 1311339600))
              ->  Index Scan using partitions_history_uint_2011_07_22_1 on history_uint_2011_07_22 history_uint  (cost=0.00..8.32 rows=3 width=8)
                    Index Cond: ((itemid = 3344) AND (clock >= 1311339600))
              ->  Bitmap Heap Scan on history_uint_2011_07_23 history_uint  (cost=2.26..5.38 rows=2 width=8)
                    Recheck Cond: ((itemid = 3344) AND (clock >= 1311339600))
                    ->  Bitmap Index Scan on partitions_history_uint_2011_07_23_1  (cost=0.00..2.26 rows=2 width=0)
                          Index Cond: ((itemid = 3344) AND (clock >= 1311339600))

Судя по разбору запроса не наблюдается попыток прочитать партицию за 21 число. У вас есть ссылки где такая информация обсуждалась?

**Alexei** · 22-07-2011, 16:36

Originally posted by dotneft

Судя по разбору запроса не наблюдается попыток прочитать партицию за 21 число. У вас есть ссылки где такая информация обсуждалась?

Видел собственными глазами. Правда, сейчас я вижу, что использовался совсем другой подход. Будем пробовать!

**dotneft** · 24-07-2011, 16:04

Дописал мануал... теперь велкам юзать

**sire** · 03-08-2011, 09:23

Спасибо за мануал, грамотно написано, теория разжёвана. Но мне, как не большому специалисту в СУБД, не понятно, какие конкретно действия надо проделать, чтобы партиционирование заработало.

Я понимаю, что делать надо следующее:
1) сохранить все куски sql-кода с CREATE OR REPLACE FUNCTION из мануала в файл;
2) загрузить получившийся sql-файл в базу Zabbix;
3) для каждой таблицы, которую я хочу разбивать, выполнить sql-запрос "COMMENT ON COLUMN history.clock IS 'for partitioning';", подставляя имя таблицы вместо history;
4) для каждой таблицы, которую я хочу разбивать, создать триггер управления, подставляя имя таблицы вместо history в sql-коде, приведённом в разделе 2.2 мануала;
5) написать скрипт для cron на основании данных из раздела 5.1 мануала.

Правильно ли я понимаю? Прошу поправить, где я неправ.

Если я всё понял правильно, то попробую написать скрипт, который будет проделывать действия 1-5 автоматически для заданных таблиц с заданными диапазонами разбиения и выложить этот скрипт на форум, чтобы пользователи могли настроить партиционирование "одним кликом".

Если база уже существует, и в ней присутствуют данные, надо ли убивать базу и создавать чистую? Что будет с существующими данными, если их не удалить перед настройкой партиционирования?

**sire** · 16-08-2011, 17:33

Сделал по инструкции. В логе Zabbix имею:

Code:

22713:20110816:192636.696 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ОШИБКА:  схема "partitions" не существует
CONTEXT:  SQL-команда: "SELECT   $1 ::regclass"
PL/pgSQL function "partition_every_day" line 15 at PERFORM
SQL-команда: "SELECT  partition_every_day ( $1 , 'partitions.',  $2 )"
PL/pgSQL function "trig_part_day" line 2 at PERFORM

Что я делаю не так?

**dotneft** · 16-08-2011, 17:46

схему partitions создать забыли

**sire** · 16-08-2011, 17:55

Originally posted by dotneft

схему partitions создать забыли

Да, уже разобрался, спасибо. Надо это в статье как-то отразить. Там вроде нет create schema.

Добавление: я имею в виду мануал на вики.

**sire** · 18-08-2011, 10:32

Написал скрипт, который автоматизирует действия, описанные в мануале dotneft. Выкладываю скрипт тут. Уважаемому dotneft огромное спасибо!

Attached Files

zabbix_partitioning_pgsql.sh.gz (4.6 KB, 702 views)

**sire** · 24-08-2011, 09:38

Originally posted by sire

Если база уже существует, и в ней присутствуют данные, надо ли убивать базу и создавать чистую? Что будет с существующими данными, если их не удалить перед настройкой партиционирования?

Отвечу сам на свои вопросы.

Если в базе уже есть данные, они останутся в мастер-таблице, например, history, history_uint, и т.п., в партиции они сами не попадут (очевидно). Если данных много, то система будет тормозить до тех пор, пока они не будут удалены housekeeper'ом или вручную. Поскольку housekeeper рекомендуется отключить при включении партиционирования, старые данные лучше удалить, чтобы система не тормозила.

**novoselov.ai** · 26-08-2011, 09:06

Originally posted by sire

Написал скрипт, который автоматизирует действия, описанные в мануале dotneft. Выкладываю скрипт тут. Уважаемому dotneft огромное спасибо!

Спасибо за скрипт!

Такие вопросы:

KEEP_HISTORY="10"
KEEP_TRENDS="104"
KEEP_OTHER="24"

А если переместить из TABLES_DAILY в TABLES_WEEKLY таблицу history_log? как будет отрабатываться KEEP_HISTORY="10"

в Ваших значениях тренды удаляются через 104 недели?

ну и полный вывод скрипта - может что полезное - система ubuntu 10.04

Code:

/zabbix_partitioning_pgsql.sh: строка 71: внимание: here-document at line 69 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 209: внимание: here-document at line 140 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 280: внимание: here-document at line 211 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 351: внимание: here-document at line 282 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 346: внимание: here-document at line 315 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 335: внимание: here-document at line 326 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 394: внимание: here-document at line 361 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 381: внимание: here-document at line 372 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 442: внимание: here-document at line 408 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 428: внимание: here-document at line 419 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 441: внимание: here-document at line 431 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 453: внимание: here-document at line 443 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 465: внимание: here-document at line 455 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 461: внимание: here-document at line 459 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 469: внимание: here-document at line 465 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 479: внимание: here-document at line 473 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 479: внимание: here-document at line 477 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 483: внимание: here-document at line 481 delimited by end-of-file (wanted `EOF')
CREATE SCHEMA
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMENT
NOTICE:  trigger "t_history_part" for table "history" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER
COMMENT
NOTICE:  trigger "t_history_log_part" for table "history_log" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER
COMMENT
NOTICE:  trigger "t_history_str_part" for table "history_str" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER
COMMENT
NOTICE:  trigger "t_history_text_part" for table "history_text" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER
COMMENT
NOTICE:  trigger "t_history_uint_part" for table "history_uint" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER
COMMENT
NOTICE:  trigger "t_history_sync_part" for table "history_sync" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER
COMMENT
NOTICE:  trigger "t_trends_part" for table "trends" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER
COMMENT
NOTICE:  trigger "t_trends_uint_part" for table "trends_uint" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER
ERROR:  column "clock" of relation "systemevents" does not exist
NOTICE:  trigger "t_systemevents_part" for table "systemevents" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER
COMMENT
NOTICE:  trigger "t_acknowledges_part" for table "acknowledges" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER
COMMENT
NOTICE:  trigger "t_alerts_part" for table "alerts" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER
COMMENT
NOTICE:  trigger "t_auditlog_part" for table "auditlog" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER
COMMENT
NOTICE:  trigger "t_events_part" for table "events" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER
COMMENT
NOTICE:  trigger "t_service_alarms_part" for table "service_alarms" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER
ERROR:  column "clock" of relation "arpwatch" does not exist
NOTICE:  trigger "t_arpwatch_part" for table "arpwatch" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER
./zabbix_partitioning_pgsql.sh: строка 763: внимание: here-document at line 648 delimited by end-of-file (wanted `EOF')
./zabbix_partitioning_pgsql.sh: строка 65: внимание: here-document at line 60 delimited by end-of-file (wanted `EOF')
Партиционирование таблиц БД Zabbix настроено. Включите параметр "DisableHousekeeping" в файле zabbix_server.conf. Проверьте, что параметр "constraint_exclusion" имеет значение "partition" или "on" в файле postgresql.conf. Перезагрузите конфигурацию планировщика cron, выполнив команду "/etc/init.d/cron reload".

**sire** · 26-08-2011, 14:05

Originally posted by novoselov.ai

Спасибо за скрипт!

Пожалуйста. Надеюсь, своё дело он делает?

Такие вопросы:

Keep_history="10"
keep_trends="104"
keep_other="24"

А если переместить из tables_daily в tables_weekly таблицу history_log? как будет отрабатываться keep_history="10"

Будет 10 недель.

в Ваших значениях тренды удаляются через 104 недели?

Да, это два года.

Можно было бы сделать индивидуальное задание периода партиционирования и срока хранения для каждой таблицы, но мне было лень.

ну и полный вывод скрипта - может что полезное - система ubuntu 10.04
[code]/zabbix_partitioning_pgsql.sh: строка 71: внимание: Here-document at line 69 delimited by end-of-file (wanted `eof')
./zabbix_partitioning_pgsql.sh: строка 209: внимание: Here-document at line 140 delimited by end-of-file (wanted `eof')

Это вроде бы ворнинги, их, наверно, можно игнорировать. Попробуйте заменить "eof" на "here_eof" в тексте скрипта.

**sire** · 29-08-2011, 15:51

Это наверно всем известная ошибка Zabbix, не связанная напрямую с партиционированием БД. На всякий случай напишу здесь. Ошибка возникает при создании новых партиций. Помогает перезапуск сервера Zabbix.

PHP Code:


 26744:20110819:140001.301 [Z3005] query failed: [0] PGRES_FATAL_ERROR:&#1054;ШИБКА:  обнаружена взаимная блокировка (deadlock)

DETAIL:  &#1055;роцесс 17074 ожидает ShareLock в транзакция 28042018; заблокировано процессом 17227.

&#1055;роцесс 17227 ожидает AccessExclusiveLock в отношение 617158278 базы данных 617157902; заблокировано процессом 17074.

HINT:  &#1057;мотрите подробности о запросе в журнале сервера.

CONTEXT:  SQL-&#1082;оманда: "create table partitions.trends_2011_08_3

    (

     constraint partition_2011_08_3 check (

       clock >= '1313352000' and

       clock <= '1313956800'

     )

    )

    inherits (trends)"

PL/pgSQL function "partition_every_week" line 43 at команда EXECUTE

SQL-команда: "SELECT  partition_every_week ( $1 , 'partitions.',  $2 )"

PL/pgSQL function "trig_part_week" line 2 at PERFORM

 [insert into trends (itemid,clock,num,value_min,value_avg,value_max) values (26465,1313744400,98,0.000000,0.000000,0.000000),(32428,1313744400,99,0.000000,0.000000,0.000000);

]

 26735:20110819:140001.301 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ОШИБКА:  обнаружена взаимная блокировка (deadlock)

DETAIL:  Процесс 16651 ожидает ShareLock в транзакция 28042018; заблокировано процессом 17227.

Процесс 17227 ожидает AccessExclusiveLock в отношение 617158278 базы данных 617157902; заблокировано процессом 16651.

HINT:  Смотрите подробности о запросе в журнале сервера.

CONTEXT:  SQL-команда: "create table partitions.trends_2011_08_3

    (

     constraint partition_2011_08_3 check (

       clock >= '1313352000' and

       clock <= '1313956800'

     )

    )

    inherits (trends)"

PL/pgSQL function "partition_every_week" line 43 at команда EXECUTE

SQL-команда: "SELECT  partition_every_week ( $1 , 'partitions.',  $2 )"

PL/pgSQL function "trig_part_week" line 2 at PERFORM

 [insert into trends (itemid,clock,num,value_min,value_avg,value_max) values (30342,1313744400,109,0.000000,74.431873,236.625000);

]

**novoselov.ai** · 29-08-2011, 19:04

Originally posted by sire

Это наверно всем известная ошибка zabbix, не связанная напрямую с партиционированием БД. На всякий случай напишу здесь. Ошибка возникает при создании новых партиций. Помогает перезапуск сервера zabbix.

Да, тоже самое - только сервер не перезапускался. И вроде всё работает. Сегодня понаблюдаю.

Ad Widget

Партиционирование в PostgreSQL

Партиционирование в PostgreSQL

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment