PostgreSQL: Força dados na memória

O Postgres 9.4 finalmente adicionou uma extensão para pré-carregar dados de relações no SO ou cache de buffer do banco de dados (a sua escolha):

pg_prewarm

Isso permite atingir o desempenho operacional total mais rapidamente.

Execute uma vez em seu banco de dados (instruções detalhadas aqui ):

CREATE EXTENSION pg_prewarm;

Então é simples pré-carregar qualquer relação. Exemplo básico:

SELECT pg_prewarm('my_tbl');

Localiza a primeira tabela nomeada my_tblno caminho de pesquisa e a carrega no cache de buffer do Postgres.

Ou:

SELECT pg_prewarm('my_schema.my_tbl', 'prefetch');

prefetchemite solicitações de pré-busca assíncrona para o sistema operacional, se houver suporte, ou gera um erro caso contrário. read lê o intervalo de blocos solicitado; ao contrário prefetchde , isso é síncrono e suportado em todas as plataformas e compilações, mas pode ser mais lento. bufferlê o intervalo solicitado de blocos no cache de buffer do banco de dados.

O padrão é buffer, que tem o maior impacto (maior custo, melhor efeito).

Leia o manual para mais detalhes .
Depesz também blogou sobre isso.

Você pode estar interessado em um dos tópicos das listas de discussão , é respondido por Tom Lane (core dev):

[..] Mas minha opinião é que as pessoas que pensam que são mais inteligentes do que um algoritmo de cache LRU geralmente estão enganadas. Se a tabela for muito usada, ela permanecerá na memória muito bem. Se não for suficientemente usado para ficar na memória de acordo com um algoritmo LRU, talvez o espaço de memória realmente deva ser gasto em outra coisa. [..]

Você também pode estar interessado em uma pergunta SO: https://stackoverflow.com/questions/486154/postgresql-temporary-tables e talvez mais adequado https://stackoverflow.com/questions/407006/need-to-load-the -whole-postgresql-database-into-the-ram

No caso geral, se você tiver RAM suficiente, geralmente pode confiar no serviço de banco de dados para fazer um bom trabalho ao manter as coisas que você usa regularmente na RAM. Alguns sistemas permitem sugerir que a tabela deve sempre ser mantida na RAM (o que é útil para tabelas pequenas que não são usadas com frequência, mas quando são usadas é importante que respondam o mais rápido possível), mas se o pgsql tiver essas dicas de tabela você precisa ter muito cuidado ao usá-los, pois está reduzindo a quantidade de memória disponível para armazenar em cache qualquer outra coisa, de modo que pode diminuir a velocidade geral do seu aplicativo.

Se você deseja preparar o cache da página do banco de dados na inicialização (por exemplo, após uma reinicialização ou outra operação de manutenção que faz com que o banco de dados esqueça tudo o que está armazenado em cache), escreva um script que faça o seguinte:

SELECT * FROM <table>
SELECT <primary key fields> FROM <table> ORDER BY <primary key fields>
SELECT <indexed fields> FROM <table> ORDER BY <indexed fields>

(essa última etapa é repetida para cada índice, ou curso, e tome cuidado para que os campos da cláusula ORDER BY estejam na ordem correta)

Depois de executar o acima, todas as páginas de dados e índice devem ter sido lidas e, portanto, estarão no cache da página da RAM (pelo menos por enquanto). Temos scripts como este para nossos bancos de dados de aplicativos, que são executados após a reinicialização para que os primeiros usuários que fizerem login no sistema posteriormente não tenham uma resposta mais lenta. É melhor escrever à mão qualquer script desse tipo, em vez de varrer as tabelas de definição de banco de dados (como sys.objects/ sys.indexes/ sys.columnsno MSSQL), então você pode varrer seletivamente os índices que são mais comumente usados ​​em vez de varrer tudo o que levará mais tempo.

Eu tive um problema semelhante:
depois de reiniciar o serviço do servidor e todos os dados descontados caíram, muitas consultas chamadas pela primeira vez eram realmente muito lentas, por causa da complexidade específica das consultas, até que todos os índices e dados necessários fossem descontados. isso significa que, por exemplo, os usuários devem acessar uma vez a cada "item" (1-3 segundos de tempo de execução) e dados relacionados de 50 milhões de linhas, para que os usuários não sofram mais atrasos indesejados. Leva as primeiras 3 horas para os usuários experimentarem travamentos irritantes, até que os dados mais usados ​​sejam descontados e os programas estejam arruinando o alto nível com o desempenho da produção, e mesmo assim, 2 dias com alguns atrasos repentinos, ao atingir menos dados acessados ​​pela primeira vez ... , para dados estatísticos etc.

Para resolver isso, escrevi um pequeno script python que executa seleções nas tabelas usadas mais pesadas com índices grandes. Demorou 15 minutos para ser executado e sem atrasos no desempenho.

Hmmm, pode ser que o comando COPY ajude. Basta executar COPY para stdout e ler a partir dele. É possível fazer isso usando pg_dump:

pg_dump -U <user> -t <table> <database> > /dev/null

Outra maneira é encontrar todos os arquivos de tabela e executar cat <files> > /dev/null.

Aqui está o exemplo de como obter nomes de arquivos de tabela:

# SELECT oid, datname FROM pg_database ;
  oid  |  datname  
-------+-----------                                                                                                                                          
<...>
 16384 | test
-- out of database is 16384
# SELECT oid, relname FROM pg_class WHERE relname like 'fn%';
  oid  | relname 
-------+---------
 24576 | fn
(1 row)
-- oid of our table is 24576

então, o(s) arquivo(s) da tabela é /path/to/pgsql/data/base/16384/24576*

Você pode querer ler índices e tabelas de brinde também, obter seus oids da mesma maneira.

BTW, por que você precisa disso? Acredito que o postgresql e o OS são inteligentes o suficiente para armazenar em cache os dados mais quentes e manter bons. eficiência do cache.

Eu uso o RamDrive da QSoft, que foi avaliado como o ramdisk mais rápido para Windows. acabei de usar

initdb -D e:\data

onde e:\ é o lugar do RamDisk.