Sugiro que você leia a transcrição do relatório de Vladimir Sitnikov do início de 2016 “PostgreSQL e JDBC estão espremendo todo o suco”
Boa tarde Meu nome é Vladimir Sitnikov. Trabalho na NetCracker há 10 anos. E gosto principalmente de produtividade. Tudo relacionado a Java, tudo relacionado a SQL é o que eu adoro.
E hoje vou falar sobre o que encontramos na empresa quando começamos a usar o PostgreSQL como servidor de banco de dados. E trabalhamos principalmente com Java. Mas o que vou contar hoje não é apenas sobre Java. Como a prática tem demonstrado, isto também ocorre em outras línguas.
Nós falaremos:
- sobre amostragem de dados.
- Sobre salvar dados.
- E também sobre desempenho.
- E sobre os ancinhos subaquáticos que estão enterrados lá.
Vamos começar com uma pergunta simples. Selecionamos uma linha da tabela com base na chave primária.
O banco de dados está localizado no mesmo host. E toda essa agricultura leva 20 milissegundos.
Esses 20 milissegundos são muito. Se você tiver 100 dessas solicitações, gastará um tempo por segundo percorrendo essas solicitações, ou seja, estamos perdendo tempo.
Não gostamos de fazer isso e olha o que a base nos oferece para isso. O banco de dados nos oferece duas opções para executar consultas.
A primeira opção é uma solicitação simples. O que há de bom nisso? O fato de pegarmos e enviarmos, e nada mais.
O banco de dados também possui uma consulta avançada, que é mais complicada, porém mais funcional. Você pode enviar separadamente uma solicitação para análise, execução, vinculação de variável, etc.
Consulta superestendida é algo que não abordaremos no relatório atual. Talvez queiramos algo da base de dados e haja uma lista de desejos que foi formada de alguma forma, ou seja, é isso que queremos, mas é impossível agora e no próximo ano. Então acabamos de gravar e vamos sair por aí agitando as pessoas principais.
E o que podemos fazer é uma consulta simples e uma consulta estendida.
O que há de especial em cada abordagem?
Uma consulta simples é boa para execução única. Uma vez feito e esquecido. E o problema é que ele não suporta o formato de dados binários, ou seja, não é adequado para alguns sistemas de alto desempenho.
Consulta estendida – permite economizar tempo na análise. Foi isso que fizemos e começamos a usar. Isso realmente nos ajudou. Não há apenas economia na análise. Há economia na transferência de dados. A transferência de dados em formato binário é muito mais eficiente.
Vamos passar à prática. Esta é a aparência de um aplicativo típico. Pode ser Java, etc.
Criamos declaração. Executou o comando. Criado perto. Onde está o erro aqui? Qual é o problema? Sem problemas. Isto é o que diz em todos os livros. É assim que deveria ser escrito. Se você deseja desempenho máximo, escreva assim.
Mas a prática tem mostrado que isso não funciona. Por que? Porque temos um método "fechar". E quando fazemos isso, do ponto de vista do banco de dados, acontece que é como um fumante trabalhando com um banco de dados. Dissemos "PARSE EXECUTE DEALLOCATE".
Por que toda essa criação e descarga extra de declarações? Ninguém precisa deles. Mas o que geralmente acontece em PreparedStatements é que quando os fechamos, eles fecham tudo no banco de dados. Não é isso que queremos.
Queremos, como pessoas saudáveis, trabalhar com a base. Pegamos e preparamos nossa declaração uma vez e depois a executamos várias vezes. Na verdade, muitas vezes - isto ocorre uma vez em toda a vida dos aplicativos - eles foram analisados. E usamos o mesmo ID de instrução em RESTs diferentes. Este é o nosso objetivo.
Como podemos conseguir isso?
É muito simples - não há necessidade de encerrar declarações. Escrevemos assim: “preparar” “executar”.
Se lançarmos algo assim, fica claro que algo irá transbordar em algum lugar. Se não estiver claro, você pode experimentar. Vamos escrever um benchmark que use esse método simples. Crie uma declaração. Nós o iniciamos em alguma versão do driver e descobrimos que ele trava rapidamente com a perda de toda a memória que possuía.
É claro que tais erros são facilmente corrigidos. Não vou falar sobre eles. Mas direi que a nova versão funciona muito mais rápido. O método é estúpido, mas ainda assim.
Como funcionar corretamente? O que precisamos fazer para isso?
Na realidade, os aplicativos sempre fecham as declarações. Em todos os livros dizem para fechar, senão a memória vazará.
E o PostgreSQL não sabe como armazenar consultas em cache. É necessário que cada sessão crie esse cache para si.
E também não queremos perder tempo analisando.
E como sempre temos duas opções.
A primeira opção é pegarmos e dizermos que vamos embrulhar tudo no PgSQL. Há um cache lá. Ele armazena tudo em cache. Vai ficar ótimo. Nós vimos isso. Temos 100500 solicitações. Não funciona. Não concordamos em transformar solicitações em procedimentos manualmente. Não não.
Temos uma segunda opção - pegue e corte você mesmo. Abrimos as fontes e começamos a cortar. Nós vimos e vimos. Acontece que não é tão difícil de fazer.
Isso apareceu em agosto de 2015. Agora existe uma versão mais moderna. E está tudo ótimo. Funciona tão bem que não alteramos nada no aplicativo. E até paramos de pensar na direção do PgSQL, ou seja, isso foi o suficiente para reduzirmos todos os custos indiretos a quase zero.
Conseqüentemente, as instruções preparadas pelo servidor são ativadas na 5ª execução para evitar o desperdício de memória no banco de dados em cada solicitação única.
Você pode perguntar – onde estão os números? O que você está recebendo? E aqui não vou dar números, porque cada solicitação tem o seu.
Nossas consultas foram tais que gastamos cerca de 20 milissegundos na análise de consultas OLTP. Houve 0,5 milissegundos para execução e 20 milissegundos para análise. Solicitação – 10 KiB de texto, 170 linhas de plano. Esta é uma solicitação OLTP. Solicita 1, 5, 10 linhas, às vezes mais.
Mas não queríamos desperdiçar 20 milissegundos. Reduzimos para 0. Está tudo ótimo.
O que você pode tirar daqui? Se você possui Java, pegue a versão moderna do driver e alegre-se.
Se você fala um idioma diferente, pense: talvez você precise disso também? Porque do ponto de vista da linguagem final, por exemplo, se PL 8 ou você tem LibPQ, então não é óbvio para você que você está gastando tempo não na execução, mas na análise, e vale a pena conferir. Como? Tudo é grátis.
Só que existem erros e algumas peculiaridades. E falaremos sobre eles agora. A maior parte será sobre arqueologia industrial, sobre o que descobrimos, o que encontramos.
Se a solicitação for gerada dinamicamente. Acontece. Alguém cola as strings, resultando em uma consulta SQL.
Por que ele é ruim? É ruim porque cada vez acabamos com uma string diferente.
E o hashCode dessa string diferente precisa ser lido novamente. Esta é realmente uma tarefa da CPU - encontrar um texto de solicitação longo, mesmo em um hash existente, não é tão fácil. Portanto, a conclusão é simples – não gere solicitações. Armazene-os em uma variável. E alegre-se.
Próximo problema. Os tipos de dados são importantes. Existem ORMs que dizem que não importa que tipo de NULL exista, que haja algum tipo. Se for Int, então dizemos setInt. E se for NULL, então será sempre VARCHAR. E que diferença faz no final o que NULL existe? O próprio banco de dados entenderá tudo. E esta imagem não funciona.
Na prática, o banco de dados não se importa nem um pouco. Se você disse na primeira vez que isso é um número e na segunda vez que é um VARCHAR, será impossível reutilizar instruções preparadas pelo servidor. E neste caso, temos que recriar a nossa declaração.
Se você estiver executando a mesma consulta, certifique-se de que os tipos de dados na sua coluna não estejam confusos. Você precisa tomar cuidado com NULL. Este é um erro comum que tivemos depois que começamos a usar PreparedStatements
Ok, ligado. Talvez eles tenham levado o motorista. E a produtividade caiu. As coisas ficaram ruins.
Como isso acontece? isso é um erro ou uma característica? Infelizmente, não foi possível entender se isso é um bug ou um recurso. Mas existe um cenário muito simples para reproduzir este problema. Ela nos emboscou de forma completamente inesperada. E consiste em amostrar literalmente de uma tabela. É claro que tivemos mais pedidos desse tipo. Via de regra, eles incluíam duas ou três tabelas, mas existe esse cenário de reprodução. Pegue qualquer versão do seu banco de dados e reproduza-a.
A questão é que temos duas colunas, cada uma delas indexada. Existem um milhão de linhas em uma coluna NULL. E a segunda coluna contém apenas 20 linhas. Quando executamos sem variáveis vinculadas, tudo funciona bem.
Se começarmos a executar com variáveis vinculadas, ou seja, executaremos o comando "?" ou “$ 1” para o nosso pedido, o que acabamos recebendo?
A primeira execução é a esperada. O segundo é um pouco mais rápido. Algo foi armazenado em cache. Terceiro, quarto, quinto. Então bang - e algo assim. E o pior é que isso acontece na sexta execução. Quem diria que era necessário fazer exatamente seis execuções para entender qual era o verdadeiro plano de execução?
Quem é o culpado? O que aconteceu? O banco de dados contém otimização. E parece estar otimizado para o caso genérico. E, portanto, a partir de algum momento ela muda para um plano genérico, que, infelizmente, pode acabar sendo diferente. Pode acabar sendo o mesmo ou pode ser diferente. E existe algum tipo de valor limite que leva a esse comportamento.
O que você pode fazer sobre isso? Aqui, é claro, é mais difícil presumir qualquer coisa. Existe uma solução simples que usamos. Isso é +0, OFFSET 0. Certamente você conhece essas soluções. Apenas pegamos e adicionamos “+0” à solicitação e está tudo bem. Eu vou te mostrar mais tarde.
E há outra opção - observe os planos com mais atenção. O desenvolvedor não deve apenas escrever uma solicitação, mas também dizer “explicar análise” 6 vezes. Se for 5, não funcionará.
E há uma terceira opção - escrever uma carta para os hackers do pgsql. Escrevi, no entanto, ainda não está claro se isso é um bug ou um recurso.
Enquanto pensamos se isso é um bug ou um recurso, vamos consertar. Vamos pegar nossa solicitação e adicionar "+0". Tudo está bem. Dois símbolos e você nem precisa pensar como é ou o que é. Muito simples. Simplesmente proibimos o banco de dados de usar um índice nesta coluna. Não temos índice na coluna “+0” e pronto, o banco de dados não utiliza índice, está tudo bem.
Esta é a regra de 6, explique. Agora, nas versões atuais, você precisa fazer isso 6 vezes se tiver variáveis vinculadas. Se você não possui variáveis vinculadas, é isso que fazemos. E no final é precisamente este pedido que falha. Não é uma coisa complicada.
Ao que parece, quanto é possível? Um bug aqui, um bug ali. Na verdade, o bug está em toda parte.
Vamos olhar mais de perto. Por exemplo, temos dois esquemas. Esquema A com tabela S e diagrama B com tabela S. Consulta – selecione dados de uma tabela. O que teremos neste caso? Teremos um erro. Teremos todos os itens acima. A regra é: um bug está em toda parte, teremos todas as opções acima.
Agora a pergunta é: “Por quê?” Parece que existe documentação de que, se tivermos um esquema, existe uma variável "search_path" que nos diz onde procurar a tabela. Parece que existe uma variável.
Qual é o problema? O problema é que as instruções preparadas pelo servidor não suspeitam que search_path possa ser alterado por alguém. Este valor permanece, por assim dizer, constante para o banco de dados. E algumas partes podem não adquirir novos significados.
Claro, isso depende da versão que você está testando. Depende da seriedade com que suas tabelas diferem. E a versão 9.1 simplesmente executará as consultas antigas. Novas versões podem detectar o bug e informar que você tem um bug.
Como tratar isso? Existe uma receita simples - não faça isso. Não há necessidade de alterar search_path enquanto o aplicativo está em execução. Se você mudar, é melhor criar uma nova conexão.
Você pode discutir, ou seja, abrir, discutir, adicionar. Talvez possamos convencer os desenvolvedores de banco de dados de que quando alguém altera um valor, o banco de dados deveria avisar o cliente sobre isso: “Olha, seu valor foi atualizado aqui. Talvez você precise redefinir as declarações e recriá-las?” Agora o banco de dados se comporta secretamente e não informa de forma alguma que as instruções foram alteradas em algum lugar interno.
E vou enfatizar novamente que isso não é típico de Java. Veremos a mesma coisa em PL/pgSQL um a um. Mas será reproduzido lá.
Vamos tentar mais alguma seleção de dados. Nós escolhemos e escolhemos. Temos uma tabela com um milhão de linhas. Cada linha equivale a um kilobyte. Aproximadamente um gigabyte de dados. E temos uma memória de trabalho na máquina Java de 128 megabytes.
Nós, conforme recomendado em todos os livros, usamos processamento de fluxo. Ou seja, abrimos o resultSet e lemos os dados daí aos poucos. será que vai dar certo? Cairá da memória? Você vai ler um pouco? Vamos confiar no banco de dados, vamos confiar no Postgres. Nós não acreditamos nisso. Vamos cair OutOFMemory? Quem experimentou OutOfMemory? Quem conseguiu consertar depois disso? Alguém conseguiu consertar.
Se você tem um milhão de linhas, não pode simplesmente escolher. OFFSET/LIMITE é necessário. Quem é a favor desta opção? E quem é a favor de brincar com o autoCommit?
Aqui, como sempre, a opção mais inesperada acaba sendo a correta. E se você desligar o autoCommit repentinamente, isso ajudará. Por que é que? A ciência não sabe disso.
Mas, por padrão, todos os clientes que se conectam a um banco de dados Postgres buscam todos os dados. PgJDBC não é exceção nesse aspecto; ele seleciona todas as linhas.
Há uma variação no tema FetchSize, ou seja, você pode dizer no nível de uma declaração separada que aqui, selecione os dados por 10, 50. Mas isso não funciona até que você desative o autoCommit. AutoCommit desativado - ele começa a funcionar.
Mas analisar o código e definir setFetchSize em todos os lugares é inconveniente. Portanto, fizemos uma configuração que dirá o valor padrão para toda a conexão.
Isso é o que dissemos. O parâmetro foi configurado. E o que conseguimos? Se selecionarmos pequenas quantidades, se, por exemplo, selecionarmos 10 linhas de cada vez, teremos custos indiretos muito grandes. Portanto, esse valor deve ser definido em cerca de cem.
O ideal, claro, você ainda precisa aprender como limitá-lo em bytes, mas a receita é esta: defina defaultRowFetchSize para mais de cem e seja feliz.
Vamos passar para a inserção de dados. A inserção é mais fácil, existem diferentes opções. Por exemplo, INSERIR, VALORES. Esta é uma boa opção. Você pode dizer “INSERT SELECT”. Na prática é a mesma coisa. Não há diferença no desempenho.
Os livros dizem que você precisa executar uma instrução Batch, os livros dizem que você pode executar comandos mais complexos com vários parênteses. E o Postgres tem um recurso maravilhoso - você pode fazer COPY, ou seja, fazer isso mais rápido.
Se você medir, poderá fazer novamente algumas descobertas interessantes. Como queremos que isso funcione? Não queremos analisar e executar comandos desnecessários.
Na prática, o TCP não nos permite fazer isso. Se o cliente estiver ocupado enviando uma solicitação, o banco de dados não lerá as solicitações na tentativa de nos enviar respostas. O resultado final é que o cliente espera que o banco de dados leia a solicitação e o banco de dados espera que o cliente leia a resposta.
E, portanto, o cliente é forçado a enviar periodicamente um pacote de sincronização. Interações extras de rede, perda extra de tempo.
E quanto mais os adicionamos, pior fica. O driver é bastante pessimista e os adiciona com bastante frequência, cerca de uma vez a cada 200 linhas, dependendo do tamanho das linhas, etc.
Acontece que você corrige apenas uma linha e tudo vai acelerar 10 vezes. Acontece. Por que? Como sempre, uma constante como essa já foi usada em algum lugar. E o valor “128” significava não usar lote.
É bom que isso não tenha sido incluído na versão oficial. Descoberto antes do lançamento começar. Todos os significados que dou são baseados em versões modernas.
Vamos experimentar. Medimos InsertBatch de forma simples. Medimos InsertBatch várias vezes, ou seja, a mesma coisa, mas existem muitos valores. Movimento complicado. Nem todos podem fazer isso, mas é um movimento tão simples, muito mais fácil do que COPIAR.
Você pode fazer COPIAR.
E você pode fazer isso em estruturas. Declare o tipo padrão do usuário, passe o array e INSERT diretamente para a tabela.
Se você abrir o link: pgjdbc/ubenchmsrk/InsertBatch.java, esse código estará no GitHub. Você pode ver especificamente quais solicitações são geradas lá. Não importa.
Nós lançamos. E a primeira coisa que percebemos foi que não usar lote é simplesmente impossível. Todas as opções de lote são zero, ou seja, o tempo de execução é praticamente zero comparado a uma execução única.
Inserimos dados. É uma mesa muito simples. Três colunas. E o que vemos aqui? Vemos que todas essas três opções são aproximadamente comparáveis. E COPY é, claro, melhor.
É quando inserimos peças. Quando dissemos que um valor VALORES, dois valores VALORES, três valores VALORES, ou indicamos 10 deles separados por vírgula. Isso é apenas horizontal agora. 1, 2, 4, 128. Percebe-se que o Batch Insert, desenhado em azul, o faz se sentir muito melhor. Ou seja, quando você insere um de cada vez ou mesmo quando insere quatro de cada vez, fica duas vezes melhor, simplesmente porque enfiamos um pouco mais em VALORES. Menos operações EXECUTE.
Usar COPY em pequenos volumes é extremamente pouco promissor. Eu nem desenhei nos dois primeiros. Eles vão para o céu, ou seja, esses números verdes para CÓPIA.
COPY deve ser usado quando você tiver pelo menos cem linhas de dados. A sobrecarga de abertura desta conexão é grande. E, para ser sincero, não fui nessa direção. Otimizei o Lote, mas não o COPY.
O que faremos a seguir? Nós tentamos. Entendemos que precisamos usar estruturas ou um banho inteligente que combine vários significados.
O que você deve tirar do relatório de hoje?
- PreparedStatement é tudo para nós. Isso dá muito para a produtividade. Produz um grande fracasso na pomada.
- E você precisa fazer EXPLAIN ANALYZE 6 vezes.
- E precisamos diluir OFFSET 0 e truques como +0 para corrigir a porcentagem restante de nossas consultas problemáticas.
Fonte: habr.com