Translate

Pesquisar este blog

domingo, 8 de novembro de 2015

Benchmark de Firebird vs PostgreSQL.

Firebird e PostgreSQL são dois dos mais robustos bancos de dados open source existentes. Pode ser difícil determinar qual será o melhor para determinado projeto.
Neste post, eu fiz um teste de velocidade entre os dois bancos, usando o JMeter  .

Carreguei um volume considerável de dados do OpenStreetMap para a massa de dados: mais de 75 milhões de pontos de latitude e longitude, e mais 6 milhões de vias/ruas da América do Sul.

Permita-me apresentar os oponentes:

PostgreSQL

O PostgreSQL possui o slogan "The most advanced Open Source database". É um banco de dados bastante completo e avançado. Sua instalação vem com o Pgadmin3 que é o cliente oficial para administração da base. 
O PostgreSQL possui funcionalidades, algumas bastante exóticas, como tais:
  • Alta disponibilidade e replicação.
  • Full Text Search (FTS) com indexação.
  • Backups incrementais contínuos, com possibilidade de restauração até uma linha de tempo anterior, se necessário.
  • Foreign Data Wrappers (FDW), para a conexão com meios de dados externos.
  • Criação de índices concorrentes, sem a necessidade de travar a tabela em produção (CREATE INDEX CONCURRENTLY).
  • Suporte a funções e tipos de dados de XML e JSON. Permite indexar partes desses arquivos também. 
  • Suporte a funções e tipos de dados trigonométricos.
  • Linguagens de programação no lado do servidor plugáveis.


Firebird

O slogan do Firebird é "The true open source database". (Esse slogan me parece uma zueira com o MySQL, que não é totalmente livre, hehehee).















As funcionalidades do Firebird denotam sua grande facilidade de uso e versatilidade:
  • Três modos de servidor (SuperServer indicado para servidores menores), (Classic para cargas maiores), Embedded e SuperClassic. O formato dos arquivos de banco são os mesmos.
  • Capacidade de rodar em modo embarcado, sem necessidade de um servidor. Isso é bastante usado em programas embarcados feitos em Delphi ou Lazarus.
  • Boa retro-compatibilidade dos servidores com versões anteriores de arquivos. Facilitando a migração de versão do servidor.
  • Backups incrementais, com o comando nbackup. (Mas eu acho menos eficientes que os do PostgreSQL).

O servidor

Eu utilizei um desktop com disco SATA e CPU AMD Athlon64, 4 cores e 4 GB de RAM. Linux Slackware64 14.1com kernel compilado.

O PostgreSQL versão 9.4.4, compilado com -march=native -mtune=native -fPIC.

O Firebird SuperServer versão 2.5.4 compilado com -march=native -mtune=native -fPIC.



Parâmetros

No postgresql.conf:
shared_buffers = 384MB
work_mem = 12MB
checkpoint_segments = 32

No firebird.conf
FileSystemCacheThreshold = 67108864
DefaultDbCachePages = 8192

(O Firebird possui uma documentação oficial muito fraca, foi difícil procurar na net como melhorar esses parâmetros).

A Carga de dados

Eu baixei o arquivo da América do Sul do site do OpenStreeMap. Após tentar entender o XML do arquivo, criei um programa para transformá-lo em CSV e ser carregado no PostgreSQL com o comando COPY.

Depois disso, criei os índices no PostgreSQL, e fiz o comando COPY novamente para gerar um arquivo TXT e carregar no Firebird usando uma tabela external file desse arquivo TXT. Criei os índices no Firebird.

Minha impressão foi de que o PostgreSQL carregou bem mais rápido, tanto o arquivo texto como criou os índices. Infelizmesnte eu não anotei os tempos de cada um mas o PostgreSQL foi mais rápido.


Gráficos

Rodei os seguintes comandos, para cada thread:
  1. SELECT pela chave primária.
  2. INSERT na tabela de log.
  3. SELECT pela chave primária.
  4. SELECT por índice de inteiro.


Tempo médio gasto, com 10, 50 e 100 usuários.





















Aqui vemos que o Firebird possui mais dificuldade de escalar à medida que o número de usuários aumenta.

sexta-feira, 19 de junho de 2015

Introdução à Linguagem PL/PGSQL do PostgreSQL - Parte 2.

Controle de fluxo.

Toda linguagem de programação têm controle de fluxo, que são
instruções IF, CASE, FOR, WHILE, FUNCTION, etc.


Declaração de variável local:
CREATE OR REPLACE FUNCTION myfunction(entrada integer)
  RETURNS integer AS
$BODY$
declare
    i int;
    v varchar(200);
    rec record;
begin
...




IF-THEN-ELSE:

IF <condição SQL> THEN
 <instrução>;
 <instrução>;
ELSIF  <condição SQL> THEN
  <instrução>;
  <instrução>;
ELSE
  <instrução>;
  <instrução>;
END IF;



LOOP-END LOOP:

LOOP
  <instrução>;
  <instrução>;
    EXIT WHEN <condição SQL>;
  <instrução>;
  <instrução>;
END LOOP;

* Observação: A instrução EXIT WHEN pode ser usada em qualquer lugar dentro do loop.



RECORD IN SELECT LOOP-END LOOP:

declare
   rec record;
begin
     FOR rec IN select id, nome from tabela order by nome
     LOOP
     <instrução>;
     <instrução>;
     END LOOP;
...


* Observação: É preciso criar uma variável do tipo record ou rowtype para usar nesse tipo de loop;



Arrays:
Arrays de qualquer tipo de dados podem ser declarados. Coloca-se o tipo de dados seguido de [].

declare
   arr varchar[];
   str varchar;
begin
   arr:=array(select nome from usuario limit 100); --Aqui definimos com um SELECT em uma tabela.
   arr:=ARRAY['Zé', 'Fulano', 'Ciclano'];                --Colchetes é diferente de parênteses.
   arr:=string_to_array('Zé;Fulano;Beltrano',  ';');   --É possível expandir varchar delimitado para array.
   str:=array_to_string(arr,  ';');                                --O oposto também é possível.

   arr:=arr||'Lóide';                                                    --Concatenando um array.

   IF array_length(arr, 1)>5 THEN                            --Retornando o tamanho de um array.
   ...
   END IF;

   FOR str IN SELECT unnest(arr)                         --Estamos fazendo um LOOP com o array.
   LOOP
   <instrução>;
   <instrução>;
   END LOOP;


end;



CASE (com variável):

CASE var
WHEN <valor1>,<valor2> THEN
  <instrução>;
  <instrução>;
WHEN <valor3>,<valor4> THEN
  <instrução>;
  <instrução>;
...
ELSE
   <instrução>;
   <instrução>;
END CASE;



CASE (sem variável):

CASE
   WHEN <condição1> THEN
  <instrução>;
  <instrução>;
WHEN <condição2> THEN
  <instrução>;
  <instrução>;
...
ELSE
   <instrução>;
   <instrução>;
END CASE;



FOR:

FOR i IN 1..10 --i vai de 1 a 10
LOOP
    <instrução>;
    <instrução>;
END LOOP;

FOR i IN REVERSE 10..1 --i vai de 10 a 1
LOOP
    <instrução>;
    <instrução>; 
END LOOP;

FOR i IN REVERSE 10..1 BY 2 --i vai de 10,8,6,4,2
LOOP
    <instrução>;
    <instrução>; 
END LOOP;

quinta-feira, 18 de junho de 2015

Introdução à Linguagem PL/PGSQL do PostgreSQL - Parte 1

Programar dentro do banco de dados é uma polêmica entre desenvolvedores, com certeza. Mas há vantagens em usar o banco de dados para desenvolver a lógica de negócio dos aplicativos.

Eu mesmo já desenvolvi usando o PL/PGSQL, e achei muito produtivo e performático.

Produtivo principalmente quando o sistema exige consultas SQL complexas, e você está usando uma linguagem de programação como Java.
Performático porque uma consulta pré-compilada no banco exige menos passos do banco para executar as instruções.

A principal desvantagem consiste em que não poderá portar para outros DBs esse código feito. Mas se você usa apenas um banco de dados isso não é problema.

Um exemplo de função:
CREATE OR REPLACE FUNCTION teste(entrada integer)
  RETURNS integer AS
$BODY$
begin
    return entrada*5;
end;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


A função teste apenas retorna o parâmetro entrada vezes 5. Para executá-lo com o parâmetro chame o seguinte comando:

Select * from teste(6)

Ela retornará 30 que é 5*6.




Veja outro exemplo. Suponha que você tenha uma tabela usuarios (id int primary key, nome varchar, descricao varchar):

CREATE OR REPLACE FUNCTION get_usuario(id_user int, out user_nome varchar, out user_descricao varchar)
  RETURNS record AS
$BODY$
begin


   select a.nome, a.descricao from usuarios as a
   where a.id= id_user
   into user_nome, user_descricao;

   if not found then
     raise exception '%', 'Usuário não encontrado';
   end if;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;



  • Veja que os parâmetros user_nome e user_descricao são parâmetros de saída(OUT). 
  • O tipo de retorno da função de torna record. Isso tem que ser porque temos mais de um valor de retorno.
  • Pegamos os dados que precisamos com a instrução SELECT e colocamos os valores nas variáveis usando a cláusula INTO.
  • Em seguida é usada a variável do PG found. Ela serve para testar se algum registro retornado com SELECT ou UPDATE foi encontrado. Se nenhum registro foi encontrado, found será igual a false. Se o registro não foi encontrado, disparamos exceção.

select * from get_usuario(7)
Isso retornará os dados do usuário 7, ou dispara exceção se ele não existe.
Observe que não colocamos os parâmetros de saída, eles é que retornarão da função.





Também é possível retornar várias linhas de uma vez só. Para isso o tipo de retorno deve ser precedido de SETOF.

CREATE OR REPLACE FUNCTION get_usuarios(out user_id int, out user_nome varchar, out user_descricao varchar)
  RETURNS setof record AS
$BODY$
begin

  return query select id, nome, descricao from usuarios
  order by nome;  


end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

  • Quando o retorno da função é declarado com SETOF, deve-se retornar os dados com a instrução RETURN QUERY ao invés de setar uma variável sozinha. 
  • É possível chamar RETURN QUERY mais de uma vez, porque ele NÃO vai retornar da função imediatamente.

Atualizando o PostgreSQL de versão

Ao contrário do Firebird, o PostgreSQL usa um formato de banco para cada versão.

Até mesmo trocar a mesma versão do PG em Windows e Linux, ou entre 32 ou 64 bits não vai funcionar. Por motivos óbvios, restaurar um Point-In-Time Recovery (PITR) também não vai funcionar.

Por isso o PostgreSQL você deve fazer um dump da database antiga com o  pg_dumpall. E depois de atualizar a database deve fazer um restore com psql.

$ pg_dumpall -h myhost -p porta > db.sql

Agora instale o novo PostgreSQL, depois faça a restauração da base com o psql.

$ cat db.sql | psql -h myhost -p porta -U usuario

Atualizando o Firebird de versão.

No Firebird, não é necessário fazer o dump de uma database, apenas instalar o novo banco, desinstalando o antigo. Os bancos de dados (Arquivos FDB) podem permanecer inalterados porque o novo banco sempre reconhece as versões antigas.

Mas se você está migrando para uma versão mais antiga isso pode ser diferente. É preciso ver o dialeto em que o banco está e ver se o antigo banco suporta.

segunda-feira, 30 de março de 2015

Escrevendo os dados no Firebird rapidamente sem Forced Writes.

O Forced Writes é um parâmetro da database que pode ser mudado em tempo de execução.

Não é recomendado, porque pode levar a database à corrupção de dados em caso de falha do sistema, se o seu hardware não garantir.

Mesmo assim você pode precisar carregar os dados, como de uma importação esporádica, então pode ser útil.

Fazendo backup full e incremental com o Firebird

Firebird é um banco de dados muito robusto, mas também simples e fácil de utilizar.

Ele suporta o "basico" do padrão ANSI, sem muito mais que isso, ao contrário do PostgreSQL.

Também o sistema de backup incremental do Firebird, é mais simples que o do PostgreSQL. No Firebird você só precisa chamar o comando nbackup que vem com o banco.

A parte importante deste comando é a opção  -B. Ela indica o nível de backup utilizado.

Mas o que são níveis de backup?

Um backup nível 0 significa um backup full. Todos os dados irão para o backup.
Um backup nível 1 significa que serão salvos os dados desde o último backup nível 0.
Um backup nível 2 significa que serão os dados desde o último backup nível 1.
...

E assim por diante.

O backup full (de toda a database).
$ nbackup -B 0 -U sysdba -P senha teste.fdb teste.fbk
 
Fazendo um backup nível 1 ("diário") 
$ nbackup -B 1 -U sysdba -P senha teste.fdb teste_30032015.fbk

Fazendo um backup nível 2 ("horário")
$ nbackup -B 2 -U sysdba -P senha teste.fdb teste_30032015_1300.fbk


domingo, 29 de março de 2015

Pegando a lista de tabelas em SQL com Firebird

 Para ver as tabelas no Firebird, use o seguinte SQL:

select rdb$relation_name
from rdb$relations
where rdb$view_blr is null
and (rdb$system_flag is null or rdb$system_flag = 0)
order by 1


Para ver a lista de campos de uma tabela, use o SQL:

select c.rdb$type_name, a.rdb$field_name, b.RDB$FIELD_SCALE
from rdb$relation_fields a, rdb$fields b, rdb$types c
where a.rdb$relation_name = 'MINHA_TABELA'
and a.RDB$FIELD_SOURCE = b.rdb$field_name
and b.rdb$field_type = c.rdb$type
and c.rdb$field_name = 'RDB$FIELD_TYPE'


Para ver os campos com chave primária:

select b.rdb$field_name
from RDB$relation_constraints a, RDB$INDEX_SEGMENTS b
where a.rdb$constraint_type = 'PRIMARY KEY'
and a.rdb$index_name = b.rdb$index_name
and a.rdb$relation_name = 'MINHA_TABELA'


sábado, 28 de março de 2015

Particionando tabelas no PostgreSQL usando herança

No PostgreSQL existe como particionar tabelas usando herança de classe.

Esse sistema funciona um pouco como o MySQL, que usa particionamento padrão. Mas também é mais complexo.

Primeiro criamos uma tabela-base contendo os campos. Ela é abstrata, ou seja: ela não terá registros, apenas suas descendentes:

create table all_logs (
id bigserial,
date timestamp default now(),
id_user int,
action varchar(20),

check(false) no inherit
);

A constraint check(false) no inherit só funcionará se o banco for versão 9.2 ou superior. Se ainda não suporta, omita.

Observe que esta tabela não possui chave primária nem chaves estrangeiras. Eles devem existir apenas nas tabelas-filhas desta.

Estas são as tabelas filhas, que armazenam os registros. Veja que elas "herdam"os campos da tabela all_logs como em orientação a objetos:

create table logs_2015_01 (

primary key(id),
check(date between '2015-01-01' and '2015-02-01')
) inherits (all_logs);


create index on logs_2015_01(date);

create table logs_2015_02 (

primary key(id),
check(date between '2015-02-01' and '2015-03-01')
) inherits (all_logs);


create index on logs_2015_02(date);


  1. A cláusula inherits faz ela herdar da tabela all_logs. Mas é preciso definir os campos chaves primária e estrangeiras, bem como os índices em cada tabela filha. Definir na tabela-base, as filhas não vão herdar essas constraints.
  2. A cláusula check é importante para performance: uma pesquisa pela tabela all_logs usando o campo date fará excluir, das tabelas-filhas, todos os meses que não correspondem às datas no check, sem ler seus dados.

Inserindo registros.
Para inserir na tabela all_logs, é necessário criar uma trigger que indique para qual tabela-filha redirecionar o registro.

CREATE OR REPLACE FUNCTION insert_logs()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.date between '2015-01-01' AND '2015-02-01' ) THEN
        INSERT INTO logs_2015_01 VALUES (NEW.*);
    ELSIF ( NEW.date between '2015-02-01' AND '2015-03-01' ) THEN
        INSERT INTO logs_2015_02 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date fora de alcance. Corrija a função insert_logs()';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;


CREATE TRIGGER insert_logs
    BEFORE INSERT ON all_logs
    FOR EACH ROW EXECUTE PROCEDURE insert_logs();


Então você pode inserir registros usando INSERT na tabela all_logs. A trigger redirecionará para a tabela filha adequada.

Fazendo pesquisas:
No exemplo nós definimos a  partição pelo campo date. uma instrução de SELECT por este campo fará o banco escolher as partições corretas por causa da cláusula check.

SELECT * from all_logs where date between '2015-01-05' and '2015-01-10'
Isto, por exemplo, fará com que o banco utilize apenas a tabela logs_2015_01, porque a 2015_02 usa um check(date between '2015-02-01' and '2015-03-01').

Excluindo partições:






Basta dropar a tabela que você deseja excluir.
drop table 2015_01


terça-feira, 24 de março de 2015

Criando uma tabela externa no Firebird, em arquivo texto fixo

Criar uma tabela externa no firebird requer que o parâmetro ExternalFileAccess esteja devidamente setado no arquivo firebird.conf,por exemplo:

ExternalFileAccess = Restrict E:\FB;E:\FB2

Para criar a tabela usa-se um comando como:

CREATE TABLE EXT4 EXTERNAL 'E:\FB\MYFILE3.TXT'
(
  ID char(13) character set none,
  NOME char(20)
character set none,
  break char(1) character set none 
);

Observe que para criar um arquivo texto o tipo de dados mais adequado é char para todos os campos, caso contrário, o banco usará definições binárias pouco fáceis de utilizar.

Observe também que se utilizar character set utf8 o Firebird vai usar campos 4 vezes maiores do que definido nas tabelas. Assim um char(20) ocupará 80 bytes!!

Fazendo operações de DDL mais rápidas no PostgreSQL

Operações como CREATE INDEX, ALTER TABLE, etc são conhecidas como operações de DDL.

Elas podem ser mais rápidas se combinados alguns fatores como:
-Setar o asynchronous_commit.
-Setar o maintenance_work_mem para um valor alto o suficiente.
-O valor de checkpoint_segments no arquivo postgresql.conf for alto o suficiente (é necessário reinciar o servidor).

O valor de checkpoint_segments por default é 3, muito baixo, se há escrita intensa no servidor é possível colocar em shared_buffers/16 Megabytes.


$ show maintenance_work_mem;

$ set local maintenance_work_mem to '1024MB';
$ set synchronous_commit to off;
$ create index concurrently on minhatabela using gin(campo_tsvector_que_demora_muito_por_exemplo);

Criando índices sem bloquear a tabela no PostgreSQL

Índices criados com a cláusula CONCURRENTLY não travam a tabela enquanto ele é criado. Isto é uma vantagem interessante do elefante.

psql -U usuario bancodedados
$ create index concurrently on minhatabela(meucampo);
$ \q


Usando asynchronous commit com PostgreSQL.

Asynchronous commit é uma opção para as transações rodarem mais rápido no PostgreSQL, a um custo de elas serem perdidas em caso de um crash repentino no banco.

Para ativar o  asynchronous commit apenas ponha o parâmetro syncrhonous_commit para off. Não é necessário parar o banco para isso.


psql -U usuario bancodedados
$ SET LOCAL synchronous_commit TO OFF;
$ create index concurrently on minhatabela(meucampo);
$ \q

quarta-feira, 7 de janeiro de 2015

Criando um compartilhamento Windows no Samba.

Neste post eu vou criar um compartilhamento Windows usando Samba, apenas configurando o arquivo smb.conf.

Nós vamos tratar as duas abordagens diferentes: de uma rede sem domínio e de um membro do AD.


  • Instale e ligue o servidor do Samba: /etc/init.d/samba (Esse comando muda conforme a distro).
  • Se você está em uma rede AD, modifique o arquivo /etc/samba/smb.conf, conforme abaixo. Se não houver algumas dessas linhas, acrescente-as:
workgroup = MYWORKGROUP
realm = my.domain
security = ads
server role = member server

  • Se você ao invés, estiver em uma rede em casa, sem domínio modifique ou crie estas linhas:
workgroup = WORKGROUP
security = user
server role = standalone server

  • Crie o compartilhamento no /etc/samba/smb.conf:
[meucompartilhamento]
path = /tmp
read list = fulano cicrano
write list = cicrano
public = no
 

  • Os usuários mencionados no compartilhamento devem existir no Unix, para isso deve-se usar o comando useradd:. ATENÇÂO: Se a máquina estiver no AD, os usuários fulano e cicrano devem existir no AD.
#useradd fulano
#useradd cicrano

  • Caso você esteja em uma rede do AD, deve ingressar a máquina no domínio com o comando net ads join: 
#net ads join -U usuarioderede -S meu_servidor_ad my.domain

  • Se ao invés você está em uma rede sem domínio, deve criar as senhas para os usuários acessarem via rede com o comando smbpasswd. (Sem configurar o PAM também, ele não vai fazer isso automaticamente).
#smbpasswd -a fulano
Enter new password:

#smbpasswd -a cicrano
Enter new password:


Depois é só acessar com o Windows.