SQLite no Rails: como e por que buscar desempenho ideal

 (fractaledmind.github.io)
1 pontos por GN⁺ 2024-09-13 | 1 comentários | Compartilhar no WhatsApp
  • Embora o SQLite ainda não seja suficiente para produção no Rails usando apenas os padrões atuais, com alguns ajustes extras é possível criar aplicações de alto desempenho e resilientes
  • Sob carga de escrita concorrente, o lock global de escrita do SQLite causa SQLITE_BUSY, e o fluxo típico de transações de escrita do Rails não combina bem com o modo deferred padrão
  • Desde o sqlite3-ruby 1.6.9, é possível mudar o modo padrão de transação para IMMEDIATE no database.yml, fazendo com que falhas ao adquirir o lock de escrita aguardem e tentem novamente de forma mais segura
  • O busy_timeout mantém o GVL do Ruby durante a espera e prejudica o paralelismo do Puma; usar sleep no busy_handler para liberar o GVL e tentar novamente em intervalos de 1 ms reduz a latência de cauda longa
  • Modo WAL, transações IMMEDIATE, busy_handler liberando o GVL, as configurações padrão de SQLite no Rails 7.1 e a separação opcional entre pool de conexões de leitura/escrita são os pilares centrais para melhorar o desempenho de SQLite no Rails

Limitações expostas pela configuração padrão de Rails + SQLite

  • Para operar uma aplicação Rails com SQLite de forma performática e resiliente, as configurações padrão atuais ainda não bastam
  • O objetivo é fazer com que a experiência padrão com SQLite no Rails 8 esteja pronta para produção
  • A aplicação de demonstração é o Lorem News
    • Um clone básico no estilo Hacker News, com usuários, posts e comentários
    • O conteúdo é composto por Lorem Ipsum
  • Para teste de carga, são usados o CLI de load testing oha e as rotas de benchmark embutidas no app
  • Ao enviar requisições sequenciais por 5 segundos ao endpoint post#create, o RPS se mantém estável e todas as requisições têm sucesso
  • Ao enviar 4 requisições simultâneas por 5 segundos ao mesmo endpoint, algumas requisições retornam resposta 500

SQLITE_BUSY e transações IMMEDIATE

  • O primeiro problema visível nos logs é a exceção SQLITE_BUSY
  • O SQLite usa um lock de escrita no banco de dados para permitir apenas uma operação de escrita por vez
    • Apenas uma conexão pode manter o lock de escrita por vez
    • Se outra conexão já estiver com o lock e uma nova conexão tentar obtê-lo, ocorre SQLITE_BUSY
  • À medida que a aplicação Rails recebe maior carga concorrente, também aumenta a proporção de requisições que falham com SQLITE_BUSY
  • O modo de transação padrão do SQLite é deferred, e ele não adquire o lock até que uma escrita realmente aconteça
    • Isso é vantajoso em termos de desempenho em ambientes com uma única conexão ou muitas transações somente de leitura
    • Mas uma aplicação Rails em produção usa várias conexões em várias threads, e o Rails encapsula queries de escrita em transações, o que entra em conflito com esse padrão
  • Se a aquisição do lock de escrita falha no meio da transação, o SQLite não pode repetir essa query com segurança sem quebrar o isolamento serializável, então lança a exceção imediatamente
  • As transações IMMEDIATE tentam adquirir o lock de escrita já no início da transação
    • O SQLite pode colocar a query de escrita em fila e tentar adquirir o lock novamente mais tarde
    • Esse comportamento combina melhor com o padrão de transações de escrita do Rails do que o modo deferred
  • A gem sqlite3-ruby passou a suportar a configuração do modo padrão de transação a partir da 1.6.9
    • O Rails repassa as chaves de nível superior do database.yml para a inicialização do banco no sqlite3-ruby
    • Com isso, dá para configurar o database.yml para executar as transações SQLite do Rails em modo IMMEDIATE
  • Depois dessa mudança, em testes simples de carga, o sistema lida com concorrência quase sem erros 500, mas com 16 requisições simultâneas alguns erros voltam a aparecer

busy_timeout, GVL e busy_handler customizado

  • O próximo gargalo é o aumento abrupto da latência p99 quando o número de requisições simultâneas se aproxima ou ultrapassa a quantidade de workers do Puma
  • O tempo real de processamento das requisições continua estável mesmo com carga concorrente 3 vezes maior que o número de workers do Puma, mas quando começam a aparecer requisições levando cerca de 5 segundos, surgem também respostas 500 por SQLITE_BUSY
  • Esses 5 segundos correspondem à configuração de timeout do database.yml, mapeada para o busy_timeout do SQLite
  • O busy_timeout tenta readquirir o lock de escrita durante a quantidade de milissegundos definida, em vez de lançar imediatamente a exceção BUSY
    • O SQLite tenta readquirir o lock com uma espécie de backoff exponencial
    • Ele só lança a exceção BUSY se não conseguir o lock dentro do timeout
    • Assim, uma aplicação web pode abrir várias conexões sem precisar coordenar diretamente a ordem das escritas, deixando isso a cargo do SQLite
  • O gargalo surge porque o SQLite está embutido dentro do processo Ruby e o sqlite3-ruby não libera o GVL do Ruby ao chamar o código C do SQLite
    • Um worker do Puma continua segurando o GVL enquanto espera o retorno da query do banco
    • Isso dificulta até que outros workers do Puma enviem queries de escrita simultaneamente ao SQLite
    • A característica de escrita sequencial do SQLite acaba tornando também o processamento das requisições Rails mais linear, reduzindo bastante o throughput
  • O SQLite oferece o hook busy_handler, de nível mais baixo que o busy_timeout
    • O busy_timeout é uma implementação específica de busy_handler fornecida pelo próprio SQLite
    • O sqlite3-ruby fornece binding para a função C sqlite3_busy_handler, então é possível criar um callback Ruby chamado quando a query entra na fila
  • Ao usar Kernel.sleep nesse callback Ruby, é possível liberar o GVL enquanto a query espera para tentar novamente o lock de escrita
  • Essa abordagem melhora bastante a latência p99 sob carga concorrente, mas na latência p99.99 ainda permanece o problema de as requisições mais lentas ficarem ainda mais lentas conforme a concorrência cresce

A cauda longa do backoff exponencial e tentativas a cada 1 ms

  • A lógica do busy_timeout do SQLite, reimplementada em Ruby, cria uma estrutura em que queries que já esperaram mais tempo ficam em desvantagem
  • Nas tentativas iniciais, o tempo de espera é pequeno, mas ele cresce conforme o número de chamadas do callback aumenta
    • Na primeira espera, aguarda 1 ms
    • Na décima chamada, aguarda 50 ms
    • A partir da 12ª, aguarda 100 ms por vez
    • Quando o tempo acumulado de espera ultrapassa o timeout de 5000 ms, ocorre a exceção
  • Se novas queries de escrita continuam chegando, as queries novas conseguem tentar adquirir o lock com mais frequência graças aos tempos de espera menores
    • Enquanto uma query antiga, já esperando pela terceira vez, fica 10 ms parada, uma query nova pode passar por esperas de 1 ms, 2 ms e 5 ms e tentar três vezes nesse intervalo
    • Esse backoff crescente aumenta a chance de queries mais antigas não conseguirem o lock de escrita e expirarem por timeout
  • A solução é fazer com que todas as queries tentem novamente na mesma frequência, independentemente da idade da query
  • Essa mudança já foi incorporada na branch main do sqlite3-ruby
    • No momento em que o texto foi escrito, o recurso ainda não estava incluído em uma release tagged
    • O callback Ruby usa sleep e libera o GVL durante a espera
    • Ele sempre faz sleep de apenas 1 ms
  • Depois dessa alteração, os benchmarks mostram achatamento da curva de latência p99.99
    • Ainda há um salto quando a concorrência passa da metade do número de workers do Puma
    • Depois disso, a latência de cauda longa se estabiliza em cerca de 0,5 segundo

Modo WAL e separação entre pools de conexões de leitura/escrita

  • Para desempenho do SQLite no Rails, há quatro condições necessárias: transações IMMEDIATE, busy_handler liberando o GVL, configurações adequadas do SQLite e modo WAL
  • O write-ahead log permite que o SQLite trate várias leituras simultâneas
    • O modo rollback journal padrão permite apenas uma query por vez, seja de leitura ou escrita
    • O modo WAL permite vários readers ao mesmo tempo, mas ainda apenas um writer por vez
  • Desde o Rails 7.1, o Rails aplica melhores configurações padrão para bancos SQLite
    • Essas configurações são importantes para que o SQLite funcione bem no contexto de aplicações web
    • Os ajustes detalhados e seus motivos são tratados em um post separado no blog
  • Uma quinta alavanca opcional de desempenho é separar um pool somente leitura e um pool somente escrita
    • O modo WAL do SQLite suporta várias conexões de leitura e uma conexão de escrita
    • Se o pool de conexões do Active Record ficar saturado por conexões de escrita, operações de leitura podem ser bloqueadas sem necessidade
  • É possível usar o suporte a múltiplos bancos de dados do Rails para fazer as configurações de reader e writer apontarem para o mesmo banco SQLite
    • Na prática, não são bancos separados, mas o mesmo banco único
    • O resultado é a criação de pools de conexão separados e configurações de conexão distintas
  • O pool de conexões reader é composto por conexões somente leitura, enquanto o pool writer fica com apenas uma conexão
  • Os modelos do Active Record são configurados para se conectar ao pool apropriado conforme seu papel
  • Com o automatic role switching do Rails, a conexão padrão de todas as requisições web passa a ser o pool reader, e só muda para o pool writer quando é necessário gravar no banco
    • Como o banco é o mesmo, não é necessário delay para garantir read your own writes
    • O método transaction do adaptador ActiveRecord é patchado para garantir que a transação se conecte ao banco writer
  • Essa combinação de “deferred requests” com pools de conexão isolados mostra melhora de desempenho em RPS simples no teste do endpoint de criação de comentários

Melhorias empacotadas em uma gem

  • Não é preciso implementar todas essas melhorias manualmente em cada aplicação Rails
  • Ao instalar activerecord-enhancedsqlite3-adapter, é possível aplicar essas melhorias
  • O recurso de pools de conexão isolados é uma funcionalidade experimental mais recente, então é preciso ativá-la por configuração explícita
  • Essa abordagem reúne ferramentas, técnicas e padrões para usar SQLite de forma rápida e flexível em produção com Rails
  • O Rails é um framework de aplicações web muito adequado para trabalhar com SQLite, e o ecossistema de ferramentas e gems relacionadas está crescendo

Leituras relacionadas

1 comentários

 
GN⁺ 2024-09-13
Comentários do Hacker News

  • Se você pretende usar SQLite + Rails, vale muito a pena conhecer o projeto Litestack do Oldmoe (X/GitHub)
    Litestack é uma gem Ruby que oferece, em um único pacote para apps Ruby e Ruby on Rails, um banco de dados SQL, cache rápido, fila de tarefas, message broker, mecanismo de busca full-text e plataforma de métricas, aproveitando o caráter embutido do SQLite
    Estou usando no meu projeto atual e estou muito satisfeito; o link é https://github.com/oldmoe/litestack

  • Não consigo imaginar quantos dias deve ter levado para escrever um texto tão detalhado
    É um artigo útil para quem está pensando em escalar aplicações web com SQLite, indo além de Rails

  • Quem trabalha com SQLite deveria ler este artigo independentemente da linguagem ou framework usado
    Como alguém que precisou descobrir a maior parte disso sozinho há alguns anos, agradeço por terem organizado tudo

  • Estou construindo um sistema de análise FOSS e, como a instalação precisa ser simples, quero enviar os dados de eventos para um banco de dados SQLite separado dos dados principais da aplicação
    Mesmo um site com movimento moderado pode gerar mais de 1.000 eventos por segundo, então fico preocupado com a escalabilidade
    Gostaria de saber se uma abordagem razoável para contornar a limitação de muitas escritas no SQLite seria acumular eventos na memória do servidor e fazer uma gravação em lote uma vez por segundo, ou se existe uma ideia melhor

    • Parece uma ideia bem razoável e boa. Já implementei algo parecido em vários sistemas e, em geral, o processamento em lote reduz o overhead por item
      Isso pode ser demonstrado facilmente com benchmark, e você também pode colocar o lote inteiro em uma única transação
      Com processamento em lote, na prática você acaba tendo uma única thread que pega o lote e faz a escrita de fato, o que combina bem com a limitação de apenas 1 escrita concorrente do SQLite
      Só que isso aumenta um pouco a complexidade. É preciso decidir o que fazer se uma gravação em lote não terminar em 1 segundo, se a fila mantida em memória terá tamanho ilimitado, se for ilimitada como garantir que o servidor não morra por OOM sob sobrecarga, se houver limite se você aceitará perder itens, quais itens descartar, e qual deve ser o tamanho máximo da fila
      Essas perguntas inevitavelmente aparecem em quase todo sistema que precisa de fila, e pensar nelas ajuda não só agora, mas também em cenários futuros
    • Não é que o SQLite não consiga escrever. O ponto é que ele só suporta uma transação de escrita por vez
      Se for difícil confiar no desempenho de concorrência de transações do SQLite, basta serializar todas as escritas em uma thread ou processo específico
    • Funcionou bem aqui e mudou minha visão sobre o SQLite. O Hipp também trabalhou nisso, e o SQLite pode entregar um desempenho enorme: https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a...
    • Dados analíticos normalmente são orientados a escrita, então eu recomendaria ClickHouse
      Com o recurso async-insert[0] do ClickHouse, você não precisa se preocupar em fazer o agrupamento de eventos no lado da aplicação
      Se você estiver procurando uma solução embutida, pode usar o chDB, baseado em ClickHouse
      [0] https://clickhouse.com/blog/asynchronous-data-inserts-in-cli...
    • Gravação em lote provavelmente é uma boa ideia, mas a melhor forma de fazer esse tipo de coisa com SQLite é usar WAL e ter um único escritor designado
      Você pode ter quantos leitores quiser, e o escritor pode receber entrada de algo como uma fila
      Só isso muitas vezes já produz um desempenho realmente impressionante

  • Ainda não entendo bem essa tendência de querer usar SQLite como banco de dados backend de produção
    O SQLite é excelente como um banco de dados pequeno e embarcável para aplicações cliente, como a agenda de contatos de um celular, mas os próprios desenvolvedores têm resistido consistentemente a expandi-lo além desse escopo
    Por exemplo, não adicionam tipos nativos úteis como data/hora ou UUID. Isso aumentaria o tamanho do código e dos objetos embutidos e, no fim, deixa tudo preso ao estado de “tudo é string”
    A integridade referencial pode ser ativada, mas as opções de restrições também são bem limitadas
    Não entendo por que continuam tentando forçá-lo a cumprir um papel para o qual ele não se encaixa nem tem suporte adequado

    • Primeiro, não sei se Richard Hipp concordaria com essa visão sobre qual seria o papel “originalmente pretendido” do SQLite
      O motivo é simples. Em padrões de acesso voltados para leitura, o SQLite é absurdamente rápido, rápido o suficiente para simplificar o código de acesso ao banco de dados e, por exemplo, muitas vezes fazer com que consultas N+1 não sejam um problema real
      Além disso, o SQLite remove uma camada de uma arquitetura em N camadas e, com isso, também reduz um ponto adicional de falha. Se você já operou Postgres ou MySQL diretamente, sabe que muita coisa pode dar errado na prática
      Não é a escolha perfeita para todo aplicativo, nem mesmo para a maioria deles, mas a tendência atual está mais próxima de um reequilíbrio contra a ideia claramente errada de que o SQLite só serve para “pequenos bancos de dados embarcados em apps cliente”
    • No fim das contas, você percebe que o “servidor de API” na verdade já era um SGBD desde o começo
      Quando você olha por esse ângulo, fica até meio engraçada a arquitetura com outro SGBD ao lado, lidando com os mesmos dados
      Então a direção acaba se dividindo entre deixar o cliente se conectar direto ao Postgres ou remover o Postgres e focar mais no próprio SGBD
      Se você escolhe a segunda opção, o SQLite é um mecanismo conveniente para construir isso por cima. Não é perfeito, mas é a ferramenta que temos hoje
      Como essa percepção em escala só se espalhou relativamente há pouco tempo, há muitos experimentos em andamento para descobrir o que funciona e o que não funciona
      É um ciclo natural da computação, uma espécie de retorno do antigo como novidade
      Você pode ler Postgres como MySQL, MSSQL, Oracle ou outro SGBD também
    • UUID pode ser usado como string ou BLOB, e datas como string ou timestamp inteiro/de ponto flutuante, não?
      Esse tipo de simplificação beneficia de várias formas não só os desenvolvedores do SQLite e o hardware mais modesto, mas também os desenvolvedores de aplicações
      A documentação fica mais simples, a curva de aprendizado é menor, e tanto a superfície de bugs quanto o tamanho do binário diminuem
      O software atual tem uma tendência de adicionar inchaço e complexidade a tudo, então é muito bom ver alguns projetos como o SQLite resistindo a isso

  • Excelente artigo, e fiquei curioso se existe algo parecido para Django
    O ArchiveBox usa SQLite via Django, e já enfrentei com bastante frequência exatamente os mesmos problemas que o texto descreve no Rails
    Seria bom ter uma solução em camada SQLite que não exigisse serializar todas as escritas por outros caminhos da aplicação

  • A gem sqlite3-ruby, por design, não libera o GVL durante chamadas ao SQLite, e pelo comentário da issue vinculada https://github.com/sparklemotion/sqlite3-ruby/issues/287#iss... parece que havia a suspeita de que o custo de readquirir o lock fosse alto, mas sem validação
    Considerando todas essas soluções alternativas, isso parece um pouco suspeito
    Num ecossistema de extensões Python, imagino que seria projetado ao contrário, então fiquei curioso sobre como isso é tratado por lá na prática
    Além disso, na issue vinculada também há um comentário dizendo que “a gem extralite é um cliente SQLite alternativo que libera o GVL durante bloqueios, e a parte sobre concorrência está em https://github.com/digital-fabric/extralite?tab=readme-ov-fi.... Em geral ela é muito mais rápida do que esta gem e também não tem problemas de concorrência”

    • Uma discussão mais detalhada pode ser vista em https://github.com/sparklemotion/sqlite3-ruby/pull/528 e https://github.com/digital-fabric/extralite/pull/46
      Foi confirmado que simplesmente liberar o GVL a cada step da máquina virtual do SQLite prejudica bastante o desempenho em thread única
      Encontrar um meio-termo entre desempenho em thread única e em múltiplas threads é complicado
      No Rails, sabemos que há múltiplas threads por causa do pool de conexões, mas essa gem de baixo nível também é muito usada em outras bibliotecas e ferramentas rodando em ambientes single-thread

  • Alguns ajustes que mantenho no meu pequeno serviço web pessoal são os seguintes
    PRAGMA journal_mode = WAL;
    PRAGMA busy_timeout = 5000;
    PRAGMA synchronous = NORMAL;
    PRAGMA cache_size = 1000000000;
    PRAGMA foreign_keys = true;
    PRAGMA temp_store = memory;
    E uso transações BEGIN IMMEDIATE
    https://kerkour.com/sqlite-for-servers

    • Fiquei curioso sobre como vocês veem cache_size e mmap_size

  • Gosto tanto de SQLite quanto de Rails, mas isso parece parecido com usar MS Access em produção

    • Não é tão diferente assim, mas em cenários com foco em leitura o SQLite tem desempenho muito melhor que o Jet (MS Access)
      Além disso, os computadores e discos de hoje são muito mais rápidos do que eram décadas atrás, quando o uso de Access era mais comum
      Se for majoritariamente leitura, tanto o SQLite quanto até o Jet provavelmente conseguiriam chegar com certa facilidade a dezenas de milhares de requisições por segundo
      A maioria das aplicações não tem centenas de milhares de usuários simultâneos, então o SQLite pode se encaixar bem
      Outro ponto forte do SQLite é que ele tem clientes para praticamente todas as plataformas e linguagens
      Arquivamento, backup e portabilidade também são casos de uso muito adequados para o SQLite. Insisti bastante no uso de SQLite por caixa em um projeto com entrada de dados limitada a certos períodos, e ainda acho que isso teria sido melhor
      Em vez de criar esquemas complexos e recursos personalizados de exportação/arquivamento, daria para simplesmente copiar um arquivo e usá-lo como arquivo morto ou backup, sem precisar se preocupar tanto com mudanças de esquema ao longo do tempo
      Depende do caso, mas é uma solução bem razoável para muitos problemas. É parecido com o fato de que, na maioria das aplicações, PostgreSQL ou outro SGBD relacional muitas vezes é uma escolha melhor do que opções NoSQL mais escaláveis
      Havia uma tendência de projetar demais, e agora estamos chegando a um ponto em que o desempenho de computação e de I/O justifica cada vez menos esse esforço
    • Seria ótimo existir algo como MS Access para apps web. Já usei vários produtos de criação de sites, mas nada foi tão intuitivo quanto o Access
    • Fico curioso com isso. Especialmente para aplicações centradas em leitura, de quais limites de escalabilidade você está falando?
    • Para Pieter Levels, essa abordagem funciona. Claro que, se o número de usuários passar de um certo limite, vai haver problemas

  • Artigo muito útil e bem escrito
    Fico curioso para saber por que o método busy_timeout padrão tem backoff exponencial que penaliza consultas antigas
    Qual seria o motivo para isso ser uma escolha razoável como padrão?