O que fazer quando há um aplicativo de código fechado que não acessa o banco de dados da maneira mais ideal? Como ajustar as consultas sem alterar o aplicativo e, possivelmente, o próprio banco de dados?
Se você não fez essas perguntas, é um DBA muito bem-sucedido e rigoroso.
Bem, se solicitado, deixe-me compartilhar o sofrimento e a experiência.
Precisa armazenar mais dados ou definir uma tarefa
Você pode rolar com segurança nesta seção se o histórico do problema não for interessante.
Inicialmente, tínhamos um sistema proprietário que analisava seus dados de um formato fechado no banco de dados PostgreSQL, de onde lemos, analisamos e processamos esses dados.
Além disso, as ferramentas desse sistema também usavam essa base para determinadas operações; portanto, abandoná-lo e criar uma cópia com sua estrutura parecia uma idéia fútil.
Por padrão, o sistema excluiu automaticamente os registros anteriores a uma semana, para que não houvesse problemas de desempenho no estande.
No entanto, precisamos armazenar dados por muito mais tempo, desde que haja espaço suficiente no disco do servidor. Bem, é muito aconselhável não perder o acesso a esses dados e ainda usar as ferramentas internas do sistema, mesmo para dados antigos.
Portanto, a decisão óbvia foi fazer particionamento e gatilhos nas operações INSERT. O foco é bastante simples e eficaz. Os dados são inseridos nas partições necessárias, a exclusão de registros antigos é desativada, tudo parece estar bem.
Até alguns anos se passaram e os dados não se acumularam bem.
Aqui, “de repente”, verificou-se que os pedidos feitos pelas ferramentas do sistema utilizado não limitam a seleção por data (ou melhor, limitam-no não ao campo em que a partição é feita). I.e. se estamos procurando algo - a pesquisa continua em todas as partições. As operações de atualização também começaram a desacelerar - em condições, havia apenas um ID-shnik usado.
Como resultado, a solicitação é executada por um longo tempo, reduz todas as outras solicitações, a carga está crescendo rapidamente.
Obviamente, a primeira coisa que vem à mente é entrar em contato com o desenvolvedor.
No entanto, na maioria dos casos, ele não está mais na zona de acesso ou solicitará o custo de outro sistema para conclusão em várias linhas.
Portanto, surgiu a idéia de que provavelmente já existe algum tipo de proxy que pode nos ajudar.
Precisamos de um proxy
A pesquisa rápida no Google não encontrou uma resposta clara para a pergunta de como reescrever uma consulta recebida ao lado do PostgreSQL ou de algum software de terceiros.
Portanto (bem, apenas por diversão, é claro), foi criado um software bastante simples que aceita conexões de clientes e as proxies no PostgreSQL. Ao mesmo tempo, as consultas SQL recebidas são lidas e, se necessário, são substituídas.
Compartilhando um link para o githubEmbora eu não tenha feito nenhum pacote binário, minhas mãos não chegaram. Mas a montagem é bem simples. Tudo está escrito em C ++ / Qt, porque Eu escrevo sobre isso há muito tempo ...
A configuração é bem simples:
Especifique qual interface e porta escutar:
listen_address=0.0.0.0 listen_port=5433
Nós forçamos o software negligente a se conectar ao endereço especificado em vez de se conectar diretamente ao servidor PostgreSQL.
Escrevemos para onde encaminhar as conexões (neste exemplo, o proxy está localizado na mesma máquina que o servidor PostgreSQL):
dst_address=127.0.0.1 dst_port=5432
Definimos uma expressão regular para capturar a solicitação desejada:
query = SELECT \* FROM tablename WHERE (.+)
Dizemos que precisamos reescrevê-lo:
action = rewrite
Dizemos como reescrever:
rewrite = SELECT * FROM tablename WHERE (col3 >= '$(now-1M)') AND $(1)
Neste exemplo, adicionamos um filtro às condições da consulta pela coluna com a data, indicando que estamos interessados apenas nos registros do último mês.
Alguém poderia escrever assim:
rewrite = SELECT * FROM tablename WHERE (col3 >= now() - interval '1 month') AND $(1)
Mas a solicitação não será ideal devido à presença da função now () - a pesquisa ainda será realizada em todas as partições. Para pesquisar apenas o necessário, você deve especificar um valor constante. Portanto, nosso proxy substitui o registro de data e hora com um turno de um mês em vez da construção $ (agora-1 milhão).
Resultado (do log):
ORIGINAL query: SELECT * FROM tablename WHERE id=1; MODIFIED query (rule 1): SELECT * FROM tablename WHERE (col3 >= '2018-11-12 11:25:23.0+00') AND id=1;
Assim, é possível substituir, em princípio, qualquer solicitação. As respostas do servidor não são alteradas e são transmitidas ao cliente como estão. Dessa maneira, o atraso da transmissão é minimizado. Além disso, o aplicativo normalmente espera por uma resposta de um determinado formato, portanto, é indesejável alterar o conjunto de colunas na solicitação e resposta.
Também é possível imprimir facilmente todas as solicitações de interesse no log:
query = .+ action = log
O repositório possui uma configuração com exemplos e uma descrição mais detalhada.
A propósito, é fácil determinar o quão bem o desenvolvedor escreve corretamente para trabalhar com o banco de dados. Por exemplo, se você vir uma solicitação executada com tanta frequência, é hora de alguém fumar manuais.
INSERT INTO tablename (col1, col2, col3) VALUES('value1', 1, '2018-12-31')
Deve ser assim:
INSERT INTO tablename (col1, col2, col3) VALUES($1::varchar, $2::integer, $3::date)
Infelizmente, até agora nosso proxy não é capaz de escrever desta maneira: / mas isso não é difícil de fazer. Talvez no futuro seja possível reescrever a primeira solicitação na segunda.
Sim, o ponto importante é que o SSL ainda não é suportado, portanto, todas as conexões dos clientes aos proxies ficarão sem criptografia.
Ficarei feliz em comentar e comentar.
Se houver um interesse ativo dos usuários, talvez eu desenvolva ainda mais o projeto.
Você pode adicionar trabalho com outros bancos de dados.