Por que o tamanho de página padrão para bancos de dados é tão pequeno?

Estou executando o Linux (Fedora 34, 64 bits, dois núcleos, quatro CPUs, 32 GB de RAM - PostgreSQL 13.3).

Se eu executar stat -f some_random_fileda seguinte forma:

[pol@fedora inst]$ stat -f blah.txt
  File: "blah.txt"
    ID: f1b798b1610e7067 Namelen: 255     Type: ext2/ext3
Block size: 4096       Fundamental block size: 4096
Blocks: Total: 322411548  Free: 316122834  Available: 299727775
Inodes: Total: 81960960   Free: 81739842
[pol@fedora inst]$

Nota: Block size: 4096= 4096 bytes = 32768 bits.

Agora, mesmo se você tiver um arquivo com dois bytes de comprimento ( "Hi") - ele ainda ocupará 4096 bytes no disco - é basicamente a E/S mínima que pode ser executada pelo SO. O sistema operacional tira as coisas do disco como "pedaços" de 4K e os cospe de volta em pedaços de 4K - veja aqui para uma visão geral rápida. Você pode querer testar em seu próprio sistema.

O próprio disco tem sua própria unidade "atômica". Com HDDs, isso normalmente era de 512 bytes, mas veja o link acima - "e no nível de hardware as unidades antigas usavam setores de 512B, enquanto os novos dispositivos geralmente gravam dados em pedaços maiores (geralmente 4kB ou até 8kB)". Veja aqui para HDDs e aqui para SSDs. (Obrigado a @RonJohn por seu comentário).

Da mesma forma, o banco de dados lerá os dados em blocos (também chamados de páginas - a terminologia pode ser confusa) - se você alterar um único bit em um registro, o banco de dados ainda terá que ler a página inteira em que o registro está e gravar a página inteira de volta para o disco assim que a modificação for concluída.

No PostgreSQL, você tem o tamanho de bloco padrão de 8K.

test_1=# SELECT name, setting, short_desc, extra_desc FROM pg_settings WHERE name like '%block%' or short_desc LIKE '%block%';

      name      | setting |                  short_desc                  | extra_desc
----------------+---------+----------------------------------------------+------------
 block_size     | 8192    | Shows the size of a disk block.              |
 wal_block_size | 8192    | Shows the block size in the write ahead log. |
(2 rows)

test_1=#

É importante que não haja uma lacuna muito grande entre os tamanhos do HDD, do SO e da "unidade atômica" do RDBMS - caso contrário, você corre o risco de páginas rasgadas - no link:

Evitando páginas rasgadas

Para o layout do arquivo Postgres, o Postgres lê e grava dados no disco 8kb por vez. A maioria dos sistemas operacionais usa um tamanho de página menor, como 4kb. Se o Postgres estiver rodando em um desses sistemas operacionais, um caso extremo interessante pode ocorrer. Como o Postgres grava em disco em unidades de 8kb e o sistema operacional grava em disco em unidades de 4kb, se a energia acabou na hora certa, é possível que apenas 4kb de uma gravação de 8kb que o Postgres estava realizando tenham sido gravados no disco. Esse caso de borda às vezes é chamado de “páginas rasgadas”. O Postgres tem uma maneira de contornar páginas rasgadas, mas aumenta a quantidade de E/S que o Postgres precisa executar.

Além disso, veja aqui :

Gravações Parciais / Páginas Rasgadas

Então, sobre o que são escritos de página inteira? Como o comentário em postgresql.conf diz que é uma maneira de recuperar de escritas parciais de página – o PostgreSQL usa páginas de 8kB (por padrão), mas outras partes da pilha usam tamanhos de pedaços diferentes. Os sistemas de arquivos Linux normalmente usam páginas de 4kB (é possível usar páginas menores, mas 4kB é o máximo em x86), e no nível de hardware as unidades antigas usavam setores de 512B, enquanto os novos dispositivos geralmente gravam dados em pedaços maiores (geralmente 4kB ou até 8kB) .

Assim, quando o PostgreSQL escreve a página de 8kB, as outras camadas da pilha de armazenamento podem dividi-la em partes menores, gerenciadas separadamente. Isso apresenta um problema em relação à atomicidade da gravação. A página de 8kB do PostgreSQL pode ser dividida em duas páginas de sistema de arquivos de 4kB e, em seguida, em setores de 512B. Agora, e se o servidor travar (falha de energia, bug do kernel, …)?

Assim como muitas coisas relacionadas à ciência da computação, é uma questão de trocas e compromissos - aqui está um benchmark do PostgreSQL executado no mesmo sistema apenas alterando o tamanho do bloco - do post:

Samsung SSD 840, 500 GB                          TPS (txns/second)

blocksize=2k                                     147.9
blocksize=4k                                     141.7
blocksize=8k                                     133.9
blocksize=16k                                    127.2
blocksize=1MB                                     42.5

Então, você pode ver que uma abordagem ingênua de "tornar o tamanho do bloco db o maior possível" não funciona muito bem. Tudo o que direi sobre isso é que os benchmarks de banco de dados são um atoleiro total ... para alguns aplicativos 1 MB pode ser adequado - embora se extraviar além de 16 KB exigiria uma justificativa considerável. Os parâmetros padrão dos sistemas são apenas isso - padrões - escolhidos para serem razoavelmente bons sob a mais ampla gama de circunstâncias ...

Ré. a parte histórica da questão - sim, muito disso se relaciona com a história quando os discos vinham em setores de 512 bytes ... O desempenho do HDD caiu muito atrás do de CPUs e RAM... a capacidade aumentou, a velocidade nem tanto (veja aqui ) - daí o nascimento do movimento "NoSQL" (mas isso é outro dia de trabalho :-))!

Tem muita coisa acontecendo na área hoje em dia...

Se você estiver interessado - e tiver tempo - eu o li algumas vezes, mas está um pouco acima do meu salário... há um artigo aqui sobre Linux I/O e como ele está sendo revolucionado pelo io_uring (veja wiki - e links nele).

A Intel também está fornecendo um kit de ferramentas de código aberto, o SPDK (o Storage Performance Development Kit) que parece (pelo menos para meus olhos destreinados) ser uma forma de permitir que os processos do espaço do usuário acessem diretamente o hardware sem passar pelo kernel. Aqui está uma visão interessante de como isso pode ser aplicado a bancos de dados.

E, chegando também em cena, está o armazenamento endereçável de (8) bytes... por motivos mais conhecidos dos designers de hardware, os SSDs (pelo menos alguns deles) também possuem blocos e páginas... Eles não são uma panacéia (confira SSD TLC e velocidade de gravação de HDD normal - apenas um ganho de 30%).

No entanto, no horizonte (longe?) há Memória Persistente - do post:

Atomicidade de 8 bytes

A memória persistente (como a memória persistente Intel Optane DC) opera nativamente byte a byte em vez de em blocos de dados como o armazenamento convencional. Os dados são persistidos em blocos de 8 bytes no máximo na Memória Persistente (novamente, usando o comportamento padrão). Para aplicativos (como bancos de dados) baseados em uma construção BLOCK, a atomicidade de 8 bytes da memória persistente pode ser um problema. A gravação de 8.192 bytes de dados (um bloco de 8K) será persistida em 1.024 blocos de 8 bytes cada. Falha de energia ou outra condição anormal pode deixar blocos de dados "fraturados" ou "rasgados" em pedaços, com partes do bloco contendo dados antigos e outras partes com dados novos. Aplicativos (como bancos de dados) precisam ser alterados para tolerar esse tipo de fratura ou ruptura de blocos. Caso contrário, estes são blocos de dados efetivamente corrompidos.

Assim, podemos ver como problemas como páginas rasgadas ainda podem ocorrer com esses sistemas - mas eles oferecem a possibilidade - quando os programadores de banco de dados alcançam - de ter o tamanho do bloco = 8 bytes ( não 8 KB) - você deseja alterar um valor do BIGINT, tudo que você precisa fazer é ler 8 bytes e escrever 8 bytes...

Talvez se chegarmos a este nível, ou mesmo à especificidade por um único byte, toda a noção de páginas sairá pela janela para o disco, o sistema operacional e o RDBMS? Tenho certeza de que ainda haverá sistemas de arquivos - só não tenho certeza de como eles funcionarão.

Esta é uma área fascinante (+1 para a pergunta!), especialmente para geeks de banco de dados.

Vou responder pela minha experiência com o SQL Server, embora acredite que o motivo possa ser o mesmo para os outros RDBMS que você mencionou.

Se você verificar o documento do Guia de arquitetura de páginas e extensões , verá que:

As operações de E/S de disco são executadas no nível da página. Ou seja, o SQL Server lê ou grava páginas de dados inteiras.

Isso significa que quando você solicita dados, eles serão carregados na memória por página e não por linha. Com isso em mente, considere a imagem a seguir como uma representação de uma página:

Uma página pode conter espaço vazio e se o tamanho padrão fosse, digamos, 1 Gb para armazenar mais dados por página, uma nova página teria quase 1 Gb de espaço vazio e apenas alguns seriam necessários para alocar a memória do servidor rapidamente com espaço vazio espaço.

Outro ponto relacionado à memória é que desde que você consiga manter uma página na memória ( Page Life Expectancy (PLE) no SQL Server ) você não precisa perder tempo lendo-a do disco para a memória toda vez que os dados forem solicitados. Se a memória do servidor for consumida rapidamente com poucas páginas, cada página será removida da memória com mais frequência para alocar as recém-solicitadas para que o SQL Server possa trabalhar com elas.

O fundamento básico do Microsoft SQL Server para consultar dados é carregar as páginas de dados necessárias no buffer pool do disco e devolvê-las ao cliente. Se o buffer não tiver espaço suficiente para processá-lo, as páginas de dados mais antigas serão erradicadas para liberar espaço conforme necessário para novas páginas.

Essas são as razões básicas pelas quais as páginas são pequenas , como você diz.

Pequeno é um termo subjetivo neste contexto. Quanto maior for a configuração do tamanho da página em um banco de dados, mais dados serão armazenados em uma página e, portanto, mais dados que precisam ser carregados sempre que uma determinada página precisar ser carregada no disco. Você pode pensar no Pages como uma unidade de medida de como os dados são armazenados fisicamente no disco, e o disco geralmente é o componente de hardware mais lento para um servidor.

Por exemplo, se uma consulta que você executa precisar retornar apenas 4 KB de dados, mas o tamanho da página estiver definido como 1 GB, isso significa que você precisará esperar que um total de 1 GB de dados seja carregado no disco para servir apenas 4 KB de dados. Provavelmente, isso não será ótimo em termos de desempenho.

Além disso, isso é apenas sob a suposição de que seus 4 KB de dados são armazenados consecutivamente na mesma página, o que dependerá de seus dados e dos predicados de sua consulta. Se seus dados foram distribuídos em 4 páginas, por exemplo, agora 4 GB de dados precisam ser carregados do disco para servir apenas 4 KB de dados.

Para referência, 4 KB de dados são aproximadamente 1.000 linhas para um único inteiro ou coluna de data e hora. Portanto, mesmo que estejamos falando de um conjunto de dados com 10 colunas de largura com um tamanho médio de dados de um tipo de dados inteiro, ainda são 100 linhas de dados que 4 KB podem conter.

Portanto, o tamanho da página é escolhido para não ser muito grande, de modo que o desperdício de E/S seja gasto carregando mais dados do disco do que o necessário para atender às consultas, mas também não muito pequeno, caso contrário, você pode ter um gargalo de desempenho devido a um aumento no o número de operações necessárias para carregar muitas páginas para uma pequena quantidade de dados. 4 KB a 16 KB tem estado no reino do razoável entre os bancos de dados, e é por isso que é o padrão. Você sempre pode ajustá-lo se encontrar a carga de trabalho do banco de dados e os casos de uso oferecerem suporte para alterá-lo, mas geralmente não é necessário alterá-lo.