jueves, diciembre 14, 2006

Optimizando Bases de Datos PostgreSQL

Neste meu primeiro artigo gostaria de descrever alguns ajustes finos do PostgreSQL que ajudam a melhorar a performance geral do banco de dados. Estas configurações são válidas para a versão 8.1. Caso você possua uma versão anterior, recomendo fortemente a atualização para a 8.1, pois esta última é muito mais rápida que as anteriores, mesmo utilizando as configurações padrão.

Infelizmente, a maior parte da degradação de performance de um banco de dados está na estrutura e/ou nos comandos SELECT mal elaborados. Nete artigo, será feita uma abordagem única e específica nos ajustes de configuração do SGBD, fazendo-o usufruir do máximo dos recursos de hardware onde está instalado o serviço do PostgreSQL.

É importante salientar também que não existe fórmula mágica para as configurações, sendo que as opções de um servidor pode não ser a melhor opção para outro.

Para começar, localize o arquivo chamado postgresql.conf. Este arquivo encontra-se no diretório de dados do cluster (ou agrupamento de bancos de dados) o qual você está inicializando. Em instalações normais, você pode encontrá-lo em:

- Microsoft Windows:
- Pasta "Arquivos de Configuração" no Menu Iniciar/Programas
ou
- C:Arquivos de ProgramasPostgreSQL8.1data

- Linux (Red Hat e Fedore)
/var/lib/pgsql/data

Após encontrá-lo, certifique-se de criar uma cópia de backup do arquivo antes de alterá-lo, pois erros na configuração podem fazer com que o PostgreSQL não seja inicializado.

Feito o backup, abra o arquivo em modo de edição. Se estiver no Linux, certifique-se de estar logado com o usuário root ou postgres.

Os parâmetros devem ser preenchidos seguindo o padrão nome_do_parametro = valor. Lembrando que para valores do tipo data e texto deve-se envolver o valor com aspas simples, e para os valores numéricos não inserir separador de milhar. Não esqueça de remover o caracter cerquilha "#" do início das opções que deseja ativar. Todas as alterações serão efetivadas após a reinicialização do serviço PostgreSQL.

shared_buffers

Define o espaço de memória alocado para o PostgreSQL armazenar as consultas SQL antes de devolvê-las ao buffer do sistema operacional. Esta opção pode solicitar que parâmetros do Kernel sejam modificados para liberar mais memória compartilhada do sistema operacional, pois esta passa a ser utilizada também pelo Postgre, em maior quantidade. O valor desta configuração está expresso em blocos de 8 Kbytes (128 representa 1.024 Kbytes ou 1 Mb).

Uma boa pedida é utilizar valores de 8% a 12% do total de RAM do servidor para esta configuração. Caso, após mudar o valor deste parâmetro, o PostgreSQL não inicializar o cluster em questão, altere o sistema operacional para liberar mais memória compartilhada. Consulte o manual ou equivalentes do seu sistema operacional para obter instruções de como aumentar a memória compartilhada (Shared Memory) disponível para os programas.

Exemplo:
shared_buffers = 2048 # Seta a memória compartilhada para 16 Mbytes

work_mem

Configura o espaço reservado de memória para operações de ordem e manipulação/consulta de índices. Este parâmetro configura o tamanho em KBytes utilizado no servidor para cada conexão efetivada ao SGBD, portanto esteja ciente que o espaço total da RAM utilizado (valor da opção multiplicado pelo número de conexões simultâneas) não deve ultrapassar 20% do total disponível (valor aproximado).

Exemplo:
work_mem = 2048 # Configura 2 Mbytes de RAM do servidor para operações de ORDER BY, CREATE INDEX e JOIN disponíveis para cada conexão ao banco.

maintenance_work_mem

Expressa em KBytes o valor de memória reservado para operações de manutenção (como VACUUM e COPY). Se o seu processo de VACUUM está muito custoso, tente aumentar o valor deste parâmetro.

Nota: O total de memória configurada neste parâmetro é utilizado somente durante as operações de manutenção do banco de dados, sendo liberada durante o seu uso normal.

Exemplo:
maintenance_work_mem = 16384 # 16 Mbytes reservados para operações de manutenção.

max_fsm_pages

Em bancos de dados grandes, é ideal que a cada execução do VACUUM mais páginas "sujas" sejam removidas do banco de dados do que a quantidade padrão, principalmente por questões de espaço em disco, e claro, performance. Porém, a configuração padrão traz apenas 20000 páginas. Em bancos transacionais, com no mínimo 10 usuários, o número mensal de páginas sujas pode exceder este valor.

Para realizar uma limpeza maior, aumente o valor desta configuração. Nota: incrementando o valor deste parâmetro pode resultar em aumento do tempo para execução do VACUUM, e cada página ocupa em média 6 bytes de RAM constantemente. O comando VACUUM pode ser comparado ao comando PACK do DBASE (DBFs), porém possui mais funcionalidades.

Exemplo:
max_fsm_pages = 120000 # Realiza a procura por até 120.000 páginas sujas na limpeza pelo VACUUM utilizando cerca de 71 Kb de RAM para isto.

wal_buffers

Número de buffers utilizados pelo WAL (Write Ahead Log). O WAL garante que os registros sejam gravados em LOG para possível recuperação antes de fechar uma transação. Porém, para bancos transacionais com muitas operações de escrita, o valor padrão pode diminuir a performance. Entretanto, é importante ressalvar que aumentar muito o valor deste parâmetro pode resultar em perda de dados durante uma possível queda de energia, pois os dados mantém-se . Cada unidade representa 8 Kbytes de uso na RAM. Valores ideais estão entre 32 e 64. Se o disco conjunto do servidor (HW & SW) são quase infalíveis, tente usar 128 ou 256.

Exemplo:
wal_buffers = 64 # Seta para 512 Kbytes a memória destinada ao buffer de escrita no WAL.

effective_cache_size

Esta configuração dita o quanto de memória RAM será utilizada para cache efetivo (ou cache de dados) do banco de dados. Na prática, é esta a configuração que dá mais fôlego ao SGBD, evitando a constante leitura das tabelas e índices ,dos arquivos do disco rígido.

Como exemplo, se uma tabela do banco de dados possui 20 Mbytes, e o tamanho para esta configuração limita-se aos quase 8 Mbytes padrão, o otimizador de querys irá carregar a tabela por etapas, em partes, até que ela toda seja vasculhada em busca dos registros. Esta opção impacta diretamente aumentando a performance do banco de dados, principalmente quando há concorrência, pois diminui consideravelmente as operações de I/O de disco.

Em consultas pela internet, encontrei várias referências para utilizar no máximo 25% da RAM total. Porém, se o servidor for dedicado, ou dispôr de uma grande quantidade de memória (512 Mbytes ou mais), recomendo o uso de 50% da RAM para esta configuração. Cada unidade corresponde a 8 Kbytes de RAM.

Exemplo:
effective_cache_size = 32768 # Seta o cache de dados do PostgreSQL para 256 Mbytes de RAM.

random_page_cost

Define o custo (em tempo) para a seleção do plano de acesso aos dados do banco. Se você possui discos rígidos velozes (SCSI, por exemplo), tente utilizar valores como 1 ou 2 para esta configuração. Para os demais casos, limite-se a 3 ou 4. Isto impacta no plano estabelecido pelo otimizador interno, que pode realizar um Index_Scan ou Table_Scan, etc, conforme aquilo que ele determinar ser mais otimizado.

Exemplo:
random_page_cost = 2 # Diminui o tempo para seleção aleatória de páginas do otimizador de consultas.

Notas gerais

Caso os valores inseridos possuam algum erro, é possível que o PostgreSQL não consiga inicializar o agrupamento de banco de dados no qual o arquivo postgresql.conf foi modificado. Se isto ocorrer, retorne o backup do mesmo e certifique-se de que as modificações estão corretamente setadas.

É possível obter ganhos de performance significativos com a correta seleção dos valores para as configurações acima. Tente várias combinações dependendo da capacidade e do uso do servidor (hardware) onde está instalado o PostgreSQL 8.1.

Dúvidas e/ou críticas sobre este artigo podem ser encaminhadas ao meu e-mail.

No próximo artigo, apresentarei mais dicas para a otimização de projetos de bancos de dados em PostgreSQL.
Fuente: http://www.imasters.com.br/ -- --
http://www.powerpostgresql.com/PerfList

Cuidado con el SQL Injection

Un reciente post de Joel explicaba como hacer SQL Injection. Voy a extender aquí un poco la información.
Imaginemos que somos muy malos programadores y tenemos el siguiente código:

String query=”select user, pass from user where user=’”+user+”‘ “;

Si introducimos como usuario pepito, tenemos que se ejecuta:

select user, pass from user where user=’pepito’

¡Fenomenal! …¿Fenomenal? y si en vez de usar pepito pongo ‘;drop table xxxx; — , ¿Qué pasa?

select user, pass from user where user=”; drop table xxxx; — ‘

¡Fenomenal! Nos acabamos de cepillar la tabla xxxx;

Vale, entonces es cuando pensamos, pues filtramos todos las comillas simples. Y entonces es cuando viene nuestro mejor cliente, el señor O’Brian y decide que si nuestro sistema no acepta su nombre quizás es que no merecemos que se gaste dinero en nosotros. Así que bueno, aceptamos ‘, pero sustituimos ‘ por ” en todos los literales.

Encones tenemos algo así:

insert into xxxx values … O”Brian ….

Y en BD queda como O’Brian.

Joder. ¡Somos buenísimos! Hemos arreglado el problema del SQL Injection con sustituir ‘ por ” doble… ¿o no?

Imaginamos que una vez tenemos un usuario en nuestra BD, luego utilizamos dicho dato para hacer otras queries a su vez (Ej.:”Dame todas las compras del usuario X”). Como solo securizamos las queries que tenían entrada de usuario, resulta que seguimos teniendo la siguiente query:

query=”select * from compras where usuario=’”+usuario+”‘”;

¡Y ya la tenemos montada otra vez!

Resulta que como protegemos la query de entrada pero, una vez almacenado el registro, guardamos el texto sin proteger, tenemos otra vez el mismo problema.

Conclusión 1 : Protege TODAS LAS QUERIES, no solo las que crees que son de entrada.

¿Filtramos solo las comillas simples?

Bueno, quizás tengas algo así:

String id= xxxxx

query=”select * from tabla where id=” +id;

Siendo el id numérico en la BD. Pues ya la tenemos montada, no nos hacen falta comillas para nada.

Podemos dar a id el valor de id, o algo peor id; drop table xxxx; o quitamos el resto del filtrado con — o /** **/ (Comentario en SQL) ,etc.

Otra: en mysql por ejemplo 9e3 es 9000, cuidado con pensar que limitando el tamaño del parámetro podemos conseguir algo…

Conclusión 2: Son vulnerables TODOS los campos, repasa todos y cada uno de los campos

Otro error común es pensar, bueno, nuestro modelo de datos es complejo, nadie va a adivinar el nombre de las tablas y de los campos…¡MAL! Pueden sacarlos si los errores de BD se sacan directamente al usuario. Ahh vale, tu siempre rediriges a una página de error, estas a salvo…¡EEEERRROOOOOR! Se pueden sacar por inferencia. ¿Cómo? Con sentencias condicionales:

select case when condicion then ‘menor’ else ‘mayor’ end;

Es decir, devolvemos un texto o otro dependiendo de la condición.

Así la condición puede ser, dame el primer bit de cierto campo. Y el then/else un elemento de diferenciación. Donde la “diferenciación” pueden ser varias cosas:

  • Esperas de tiempo diferente
  • Variar la respuesta
  • Devolver error en uno y en otro no (Ejemplo: el campo a devolver es un numérico y en uno devolvemos 3 y en otro ‘caca’ o más fácil: “select case when condición then 37 else 37/0″)

Además pueden atacar a las tablas de metadatos donde se almacenan las descripciones de las tablas, etc.

Por tanto…

Conclusión 3: Nuestro sistema no es invulnerable porque “ocultemos” los nombres de tablas y campos.

Hay unos cuantos caracteres más que filtrar. SQL es un lenguaje y por tanto hay muchas formas de hacer las cosas, así que si usas Java lo mejor es utilizar Prepared Statements.

Un presentación sobre el tema aquí.

UPDATE: Rubén me envía este enlace. Guía para desarrollo Web seguro. Imprescindible.


Fuente: http://centuryminds.wordpress.com/2006/11/10/cuidado-con-el-sql-injection/