PGCON 2007 Bombou !!!!!

É isso ai minha gente no ultimo sábado rolou o 1o PGCON aqui no Brasil.

Contamos com cases super interessantes como o do METRO de São Paulo e da FAB. Colaboradores do mundo livre também estavam la, como Telles, Diogo, Leo, Isis, David Fetter, Fike, Euler, Dutra e muito mais …

O evento bombou mesmo tinha muita gente !!!! as palestras foram muito bem recebidas e o auditório ficou cheio até a ultima (que foi de tunning do Fike).

No final rolou a chopada fornecida pela Lev Chopp e todos os organizadores terminaram a noite jantando no Rocks.

Se o primeiro foi surpreendente imagina só os próximos como serão!!!!!

Alguem já brincou de onde esta o Wally ????? Vamos brincar de onde esta  a Kenia?????. Acredite se quiser .. mas eu estou ai no meio !!!!!!

_c081110.jpg

Kenia Milene

Projeto de Migração do PostgreSQL 8.1 pra 8.2.5 – FASE 3 : Os Testes do Desenvolvimento

Bem …. já logo de cara sabíamos que enfrentaríamos um problema.

A maioria dos desenvolvedores aqui não indicam o schema dos objetos em suas rotinas, o schema era definido através do search_path de cada usuário (ALTER ROLE usuario SET search_path=schema).

Porém a versão 8.2.5 não reconhece o código se não indicar <schema.objeto>. Até existe um parâmetro de compatibilidade no postgres.conf da nova versão (add_missing_from = off), ou poderíamos manter o search_path do usuário, mas isso faz com que o banco faça mais uma verificação, fazendo o mesmo trabalhar mais um pouquinho !!!!

Pois bem … notificamos o desenvolvedor sobre essa mudança, e o mesmo alterou uma rotina da aplicação para teste. Resultado????

O desenvolvedor achou que ficou mais rápido e concordou em modificar as outras rotinas e as futuras já vir com essa alteração.

Estamos no aguardo do desenvolvedor terminar seus testes e nos dar o OK para o dia D

É isso ai ….
Contagem regressiva para o Dia D !!!

Kenia Milene

Projeto de Migração do PostgreSQL 8.1 pra 8.2.5 – FASE 2 : Preparando o Ambiente PostgreSQL

Permissões

Bem como dito la no começo o ponto de montagem para o banco é o /postgres, esse ponto deve ter o dono e o grupo postgres para que tudo funcione perfeitamente, sendo assim, antes de tudo crie o usuário postgres !!!!
Assim que o server foi instalado fizemos as alterações acima.

A Compilação

A versão para que vamos migrar é a 8.2.5, baixamos a versão, descompactamos e compilamos no diretório /home/postgres/postgresql-8.2.5 como mostrado a seguir:

$ tar -xvzf postgresql-8.2.5.tar.gz
$ cd postgresql-8.2.5
$ cd src/include
$ vi pg_config_manual.h

#define BLCKSZ 8192

Nesse parâmetro definimos o tamanho de cada bloco, que nesse caso será compilado com blocos de 8KB.

Porque a compilação no home do postgres?????

A compilação no home do postgres nos da a possibilidade de ter mais de uma versão do postgreSQL na mesma máquina, ou seja, posso ter no servidor de produção por exemplo a versão 8.1 (Operacional no momento) e a versão 8.2.5. Com isso posso caso alguma catástrofe aconteça (Esperamos que não!!!) , é possível voltar tudo para a versão anterior. Veja a seguir os passos para a compilação:

$ cd ../../
$ ./configure –prefix=/home/postgres/postgresql-8.2.5 –with-python –with-perl
$ make
$ make install

Path do usuário

É importante alterar o path do usuário postgres, pois como compilamos no home, precisamos indicar onde buscar os binários.

$ vi /home/postgres/.bashrc
PG=$HOME/postgresql-8.2.5/bin

PATH=$PATH:/$PG

PAGER=/usr/bin/less

export $PATH $PG $PAGER

LESS=”-S-N”

$ vi /home/postgres/.bash_profile
PATH=$PATH:$HOME/postgresql-8.2.5/bin:$HOME/bin
PAGER=less

export $PATH $LESS

unset USERNAME

Criando o Cluster

Para isso criamos a seguinte estrutura de arquivos: /postgres/pg825/dados
onde criaremos o cluster através do comando initdb e com o parâmetro latin1 para indicar o idioma a ser usado:

$ cd /home/postgres/postgresql-8.2.5/bin/
$ initdb -D /postgres/pg825/dados/ –encoding=latin1

Ajustando Parâmetros do PostgreSQL

Para configurar os parâmetros antes de subir o banco vamos alterar o arquivo postgresql.conf que esta no /postgres/pg825/dados/ :

$ cd /postgres/pg825/dados/
$ vi postgresql.conf

Parâmetro que define quais endereços clientes poderão fazer conexão no banco. Nesse caso todo e qualquer host é permitido, essa restrição faremos em outro arquivo.

listen_addresses = ‘*’

Parâmetro que define qual porta o serviço irá responder

port = 5432

Número máximo de conexões simultâneas permitidas. Essa é uma informação importante, que deve ser levada em consideração.

max_connections = 25

Memória compartilhada, é indicado no caso de servidores dedicado que seja disponibilizado para o postgresql 1/3 da memória disponível. Como temos 1GB de memória vamos então colocar 300MB.
shared_buffers = 300MB

Parâmetro que define a área de ordenação, área essa usada pelo ORDER BY por exemplo. É a área da memoria disponível no disco usada para ordenação das transações.
É importante saber que se essa área não for o suficiente, vai pra disco fazendo que com tudo fique mais lento. A área de ordenação é definida por usuário, e a quantidade total de conexões permitidas * a área de ordenação não pode ser maior do que a memória disponível ( No nosso caso os 600MB restantes). Lembrando que a ordenação não usa a memória compartilhada e sim a memória disponível.
Nesse caso o valor esta maior do que o normal, pois 25 conexões * 100MB cada uma seria equivalente a 2,5GB (Contamos com que os 25 usuários não façam ordenações simultâneas). Como é apenas ambiente de testes para as aplicações o número de conexões é bem reduzido, logo, não teremos problemas nesse ambiente quanto a área de ordenação.

work_mem = 100MB

Parâmetro que define a ordem da procura no schemas quando um objeto é referenciado apenas pelo nome, sem o schema.
Com esse parametro setado sem a variável $user, todo e qualquer objeto, seja ele tabela, view … etc , que não for apontado o schema será retornado um erro de objeto inexistente.

search_path = ‘public’

Alterando Parâmetros do Kernel

Os parâmetros variam de acordo com o Hardware. O ideal é que a memória compartilhada (Shared_buffers) do servidor seja equivalente a 1/3 da memória total e o shmmax seja equivalente a shared+S.O, ou seja, o parâmetro SHMMAX deve ser o valor da shared_buffers + uma folga para o Sistema Operacional. No nosso caso:

$cat /proc/sys/kernel/shmmax
841572800

Levantando o banco

Depois dos parâmetros configurados vamos levantar o banco:

$ /home/postgres/postgresql-8.2.5/bin/pg_ctl -D . start

Para sabermos se tudo correu bem primeiro vamos ver se o serviço esta de pé:

$ ps -aux | grep postgres
postgres 14666 0.3 0.2 29524 2364 pts/0 S 16:29 0:00 /home/postgres/postgresql-8.2.5/bin/postgres -D .

E então fazer uma conexão no banco

$ psql
Welcome to psql 8.2.5, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

postgres=#

Importação do Banco de Produção

Bom .. agora só falta os dados … para isso vamos fazer um dumpall da produção e redirecionar para nosso servidor de testes.
Usamos dumpall porque dessa forma ele leva tudo mesmo … inclusive usuários e grupos.
No servidor de produção:

$ pg_dumpall -p 5432 -i | psql -h host -p 5432 -d banco

Ambiente Disponível e Era isso !!!
Até a fase 3

Kenia Milene

Projeto de Migração do PostgreSQL 8.1 pra 8.2.5 – FASE 1 : O HARDWARE

Pois é minha gente la vou eu para mais uma aventura no mundo dos dados…
O desafio dessa vez é migrar a versão de vários bancos postgreSQL, inclusive os bancos de BI. olha só que aventura …
Bom, vou postando aqui as fases desse projeto, e a primeira fase é preparação de um ambiente de testes e homologação, para isso contamos com um servidor de testes dedicado ao projeto:

A migração

Na verdade essa migração esta sendo feita porque estamos com uma versão muito antiga, o que pode causar sérios problemas com possiveis bugs e assim podemos usufruir das melhorias.

Mas a nossa maior preocupação é: As aplicações vão funcionar?????? A principio sim, mas isso vamos ver ao longo dos testes.

Hardware

Bom .. a primeira luta é para ter um servidor de testes dedicado. Conseguimos!!!!
Não é uma super máquina mas para os testes com o desenvolvimento vai suprir as necessidades.
Infelizmente o projeto não contempla upgrade de hardware, ficamos com os servers antigos.

model name : Intel(R) Pentium(R) 4 CPU 1.80GHz
cpu MHz : 1800.354

cache size : 512 KB
D
isk1 : 10 GB
D
isk2 : 40 GB

Particionamento

O disco menor deixamos para o Sistema Operacional e o segundo disco para o banco, assim evitamos concorrência na gravação.

/dev/hda2 /
/dev/hda1 /boot
/dev/hdb1 /postgres

Sistema Operacional

Mesmo Sistema Operacional e kernel do servidor de teste será o mesmo que esta em produção.

S.O : Fedora Core 4
Kernel : 2.6.11-1

É isso ai ….
Até a fase 2 !!!!
Kenia Milene

Volumetria de Banco PostgreSQL

Tudo bem … já que fiz volumetria pro Oracle … segue a volumetria pro PostgreSQL !!!! (só pra não ficar com ciúmes).

O script do schema deu uma emperrada mas o Fabio Telles deu uma sugestão e a coisa saiu. Valeu Fabio !!!!

— Tamanho por tabela

SELECT n.nspname AS schemaname, c.relname AS tablename, C.reltuples::int AS Registros, pg_size_pretty(pg_relation_size(n.nspname ||’.’||c.relname)) as Tamanho
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = ‘r’::”char”
–AND n.nspname = ‘acc’
AND nspname NOT IN
(‘dbateste’,’information_schema’,’pg_catalog’,’pg_temp_1′,’pg_toast’,’postgres’,’publico’,’public’)
ORDER BY n.nspname

— Tamanho por schema

SELECT n.nspname, sum(c.reltuples)::int as Registros,
pg_size_pretty(pg_relation_size(n.nspname ||’.’||c.relname)) as Tamanho
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ‘r’::”char”
AND nspname NOT IN
(‘dbateste’,’information_schema’,’pg_catalog’,’pg_temp_1′,
‘pg_toast’,’xmg’,’postgres’,’publico’,’public’)
GROUP BY n.nspname, c.relnamespace, c.relname

— Tamanho por TableSpace

SELECT spcname, pg_size_pretty(pg_tablespace_size (spcname))
FROM pg_tableSpace;

— Tamanho por Banco

SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database;

É Isso Ai !!!
Kenia Milene

Contar Registros em Banco PostgreSQL

La vamos nós novamente …
H
oje me fizeram a seguinte pergunta.. :

QUANTOS REGISTROS NÓS TEMOS EM TODO O BANCO DE DADOS?”

Perguntinha difícil essa não???? pois bem, consegui depois de muito queimar a “muffa” fazer um script pra isso, e pra ajudar acabei criando mais outros com informações mais específicas:

Saber quantidade de registros no banco inteiro:
SELECT sum(C.reltuples)::int
FROM pg_class C
WHERE c.relkind = ‘r’::”char”

Saber quantidade de registros por tabela:
SELECT n.nspname AS schemaname, c.relname AS tablename, C.reltuples::int AS Registros
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = ‘r’::”char”
AND nspname NOT IN
(‘dbateste’,’information_schema’,’pg_catalog’,’pg_temp_1′,’pg_toast’,’postgres’,’publico’,’public’)
ORDER BY n.nspname

Saber quantidade de registros por schema:
S
ELECT n.nspname, sum(c.reltuples)::int
F
ROM pg_class c
L
EFT JOIN pg_namespace n ON n.oid = c.relnamespace
W
HERE c.relkind = ‘r’::”char”
A
ND nspname NOT IN
(‘dbateste’,’information_schema’,’pg_catalog’,’pg_temp_1′,’pg_toast’,’postgres’,’publico’,’public’)
G
ROUP BY n.nspname

Saber quantidade de registros de um determinado schema
SELECT n.nspname, sum(c.reltuples)::int
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ‘r’::”char”
AND nspname = ‘aaa’
GROUP BY n.nspname

Até Mais
Kenia Milene

Gerenciamento de Memória em Bancos PostgreSQL

Um dos pontos mais importante a ser analisado quando administramos um banco de dados é a MEMÓRIA COMPARTILHADA.
Saber, ao montar um servidor o quanto deixar de memória para o sistema operacional e o quanto alocar para o banco.
Será que a memória compartilhada é o suficiente para a quantidade de usuários (conexões) que tenho ???? e se meu banco tiver uma quantidade muito grande de transições ??? e se for um DW onde a maior fatia são das consultas???
Pois bem, esses eram alguns fantasmas que rondava a minha singela vida de DBA. Mas eu aprendi e espero que possa ajudar a quem estiver com essas mesmas dúvidas.

Primeiramente qual o papel da memória compartilhada?

Memória compartilhada é o valor disponibilizado no buffer para as transações do banco. Esse valor é variável, em bancos Postgres é indicado que este valor deve estar entre 1000 (8MB) a 500000 (400MB), e manipular esses valores dependendo do número de conexões e consultas (complexas ou simples).
Alguns bancos indicam ainda que o valor a ser destinado ao banco não pode ultrapassar de 1/3 (33%)da RAM disponível.

Como ela é calculada?

Um usuário consome em média 14,2 KB por conexão, com essa informação calculamos através da seguinte fórmula:((bloco do S.O X pag. a ser utilizada)+(14,2 KB * conexões))+250 KB= memória compartilhada

EX: ((4096 X 500000)+(14,2 *50))+250 KB= 2048000960 (2.048GB)

Esse valor deve ser setado nos parâmetros do kernel da seguinte forma:

echo “kernel.shmmax=2048000960 ” >> /etc/sysctl.conf ; sysctl -p

Tamanho dos Blocos

Esse parâmetro é usado para configurar o tamanho do bloco, o tamanho é definido tendo em vista as transições no banco, pois em uma transição pequena é melhor a busca em blocos pequenos e no caso de grandes buscas como em um DW por exemplo, é melhor um bloco maior.
Para grandes buscas como um DW, use 8192, porém esse valor só será considerável em servidores que suporte esse tipo de blocagem, e não se esqueça que será necessário diminuir a shared buffer, caso contrario, use a blocagem 4096.
Cada usuário utiliza em media 14,2 KB por conexão. Quanto mais conexões existirem no banco, menor deve ser a quantidade de páginas disponíveis. Tudo esta relacionado ao tipo e tamanho das transações e a área de ordenação.

Área de Ordenação

A área de ordenação (work_mem), é uma quantidade de memória física para realização de consultas por usuários. No caso de um DW é interessante ter essa área grande, pois, quanto maior a quantidade de memória destinada melhor será o desempenho da consulta principalmente se a mesma for complexa. Se a quantidade de memória estipulado nesse parâmetro foi ultrapassado o banco será obrigado a fazer SWAP causando lentidão nas consultas (e muito estress recheadas de ligações de usuários histéricos), resultando na concorrência entre o banco de dados e o sistema operacional.
No caso de banco com muitas consultas como um DW é indicado um servidor com uma quantidade considerável de memória e colocar o sistema operacional e o banco em discos separados para manter um melhor desempenho.

Então como diminuir a quantidade de páginas?

Bem, a paginação deve ser diminuída quando o número de conexões aumentar, isso tem que acontecer para que posteriormente não seja necessário mexer na memória compartilhada. Quando necessário, a quantidade de páginas a ser diminuída seguindo a seguinte fórmula:
(usuário acrescidos * 14,2KB) / blocos

Ex: 20 usuários acrescidos com a blocagem de 4 KB

(20 * 14,2) / 4
284 /4
71 KB(arredondando 100KB)

Sendo assim é necessário diminuir 100KB do shared buffer

Consultando as informações para os cálculos e alterando os arquivos de configuração

banco1=#
SELECT name, setting
FROM pg_settings
WHERE name IN(‘shared_buffers’,’max_connections’,’block_size’,’work_mem’);

name | setting

——————+———
block_size | 4096
max_connections | 50
shared_buffers | 500000
work_mem | 500000

com exceção do block_size que é definido na compilação os outros parâmetros podem ser alterados no postgresql.conf de cada banco.

Kenia Milene