Início / dba / Perguntas / 9227
Accepted
Derek Downey
Asked: 2011-12-16 09:03:15 +0800 CST

Projeto de hierarquia de partição PostgreSQL para registro centralizado

  • 772

Estou pensando em configurar o log de todos os meus servidores em um banco de dados postgresql centralizado. Faz sentido poder descartar logs por data ou por host, então gostaria de configurá-lo com particionamento , mas uma partição de várias camadas:host-service inherits service, host-service-yyyymm inherits host-service.

Elaborei um esquema de exemplo para um serviço específico (erros de PHP neste caso) e estou procurando alguns especialistas em PostgreSQL para criticá-lo por gargalos óbvios de desempenho.

// SET UP MASTER PHP LOG TABLE //
CREATE TABLE php (
 log_id     int not null,
 host       char(5),    
 logdate     date not null,
 message        text
);

// SET UP HOST-SPECIFIC 'PARTITIONS' //
CREATE TABLE host1_php (
 CHECK ( host = 'host1' )
) INHERITS (php);

CREATE TABLE host2_php (
 CHECK ( host = 'host2' )
) INHERITS (php);

// SET UP HOST-SPECIFIC TIME 'PARTITIONS' //
CREATE TABLE host1_php_2011m12 (
 CHECK ( logdate >= DATE '2011-12-01' AND logdate < DATE '2012-01-01' )
) INHERITS (host1_php);

CREATE TABLE host1_php_2012m01 (
 CHECK ( logdate >= DATE '2012-01-01' AND logdate < DATE '2012-02-01' )
) INHERITS (host1_php);

CREATE TABLE host2_php_2011m12 (
 CHECK ( logdate >= DATE '2011-12-01' AND logdate < DATE '2012-01-01' )
) INHERITS (host2_php);

CREATE TABLE host2_php_2012m01 (
 CHECK ( logdate >= DATE '2012-01-01' AND logdate < DATE '2012-02-01' )
) INHERITS (host2_php);

CREATE INDEX host1_php_2011m12_logdate ON host1_php_2011m12 (logdate);
CREATE INDEX host1_php_2012m01_logdate ON host1_php_2012m01 (logdate);
CREATE INDEX host2_php_2011m12_logdate ON host2_php_2011m12 (logdate);
CREATE INDEX host2_php_2012m01_logdate ON host2_php_2012m01 (logdate);

Também adicionarei serviços como acesso/erros do apache.

Acho que tenho que acionar para inserir no host_service (para utilizar a restrição de verificação do host) e, em seguida, cada host_service deve acionar para inserir na tabela host_service_yyyymm.

O que posso esperar em termos de desempenho de tal esquema de acionamento/partição?

Algumas informações extras discutidas no chat:

  • PostgreSQL versão 9.1.2
  • As consultas de dados não serão muito frequentes e principalmente no mês atual de dados para vários hosts.
  • O PHP tem apenas cerca de 1 inserção por minuto, mas o apache total provavelmente ficará em torno de 300-500 por segundo em todos os hosts.
postgresql partitioning

1 respostas

  1. Best Answer

    O que posso esperar em termos de desempenho de tal esquema de acionamento/partição?

    As opções de contexto significam que o uso de gatilhos sempre usará muito mais CPU do que um simples arquivo insert. O script abaixo pode ser usado para quantificar quanto impacto isso terá - e também demonstra a criação automática de partições usando gatilhos e compara o desempenho de qualquer maneira.

    Observe que não incluí nenhuma indexação ou consideração de updatedeclarações.

    begin; 
set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;
--
--******** the above creates a nice clean schema as a test area
--
set client_min_messages to warning; --******** or you get a lot of "NOTICE:  merging column "xyz" with inherited definition" notices
--
create table phpheap(log_id serial not null, host text not null, logdate date not null, message text not null); --******** This table is used to compare 'insert' performance with that on the partitioned version
create table php(log_id serial not null, host text not null, logdate date not null, message text not null);
--
create function php_host_insert() returns trigger language plpgsql security definer as $$
begin 
  set search_path to 'stack';
  execute 'insert into php_'||new.host||'_'||to_char(new.logdate, 'YYYYmMM')||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null;
exception when undefined_table then
  execute 'create table php_'||new.host||'_'||to_char(new.logdate, 'YYYYmMM')||'(log_id int not null, host text not null check(host='''||new.host||'''), logdate date not null check(to_char(logdate, ''YYYYmMM'')='''||to_char(new.logdate, 'YYYYmMM')||'''), message text not null) inherits (php_'||new.host||')';
  execute 'insert into php_'||new.host||'_'||to_char(new.logdate, 'YYYYmMM')||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null;
end;$$;
--
create function php_insert() returns trigger language plpgsql security definer as $$
begin 
  set search_path to 'stack';
  execute 'insert into php_'||new.host||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null; 
exception when undefined_table then
  execute 'create table php_'||new.host||'(log_id int not null, host text not null check(host='''||new.host||'''), logdate date not null, message text not null) inherits(php)';
  execute 'create trigger trig_insert_php_'||new.host||' before insert on php_'||new.host||' for each row execute procedure php_host_insert()';
  execute 'insert into php_'||new.host||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null;
end;$$;
--
create trigger trig_insert_php before insert on php for each row execute procedure php_insert();
--
\timing on
insert into phpheap(host, logdate, message) select 'host1', current_date-(generate_series(-99999, 0, 1)/1000)::integer, repeat('hello',20);
--******** output
--INSERT 0 100000
--Time: 1102.140 ms
insert into php(host, logdate, message) select 'host1', current_date-(generate_series(-99999, 0, 1)/1000)::integer, repeat('hello',20);
--******** output
--INSERT 0 0
--Time: 35615.498 ms
insert into php(host, logdate, message) select 'host1', current_date-(generate_series(-99999, 0, 1)/1000)::integer, repeat('hello',20);
--******** output
--INSERT 0 0
--Time: 34074.579 ms
\timing off
--
--******** Now we replace the trigger functions with the 'normal' kind that don't auto-create partitions
--
create or replace function php_host_insert() returns trigger language plpgsql security definer as $$
begin 
  set search_path to 'stack';
  execute 'insert into php_'||new.host||'_'||to_char(new.logdate, 'YYYYmMM')||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null;
end;$$;
--
create or replace function php_insert() returns trigger language plpgsql security definer as $$
begin 
  set search_path to 'stack';
  execute 'insert into php_'||new.host||'(log_id, host, logdate, message) values($1, $2, $3, $4)' using new.log_id, new.host, new.logdate, new.message;
  return null; 
end;$$;
--
\timing on
insert into php(host, logdate, message) select 'host1', current_date-(generate_series(-99999, 0, 1)/1000)::integer, repeat('hello',20);
--******** output
--INSERT 0 0
--Time: 28457.146 ms
\timing off
--
rollback;
    • 6

relate perguntas