orokusaki's questions

Tabela A

- auto-incrementing integer primary key
- 3 dates
- 17 booleans
- 7 varchars (less than 40 bytes in these combined varchars in 99% of rows)
- 5 foreign keys

Tabela B

- auto-incrementing integer primary key
- 1 date
- 2 boolean fields
- 3 varchars (1 unique UUID string in each row, other 2 varchars empty 99% of the time)
- 3 foreign keys

1. A Tabela A tem 25 milhões de linhas - SELECT COUNT(*)leva aproximadamente 70 segundos
2. A Tabela B tem 20 milhões de linhas - SELECT COUNT(*)leva aproximadamente 6-7 segundos

Eu só incluí a Tabela B para ilustrar minha suposição, que é que o 900% mais tempo que leva para contar a Tabela A, apesar de ter apenas 25% mais linhas do que a Tabela B, é que a Tabela A tem 33 colunas, em comparação com apenas 10 colunas na Tabela B. Percebo que 33 colunas não é uma tabela "grande", mas pode ser maior do que precisamos, pois usamos apenas algumas dessas colunas em muitas operações.

Não estamos enfrentando nenhum problema sério, além de contar demorar muito para ser útil dentro de uma solicitação da web (por exemplo, para paginação em uma exibição de lista de administração do Django).

No entanto, estamos prestes a aumentar o tamanho da Tabela A, de 25 milhões, em cerca de 1 a 2 milhões de linhas por mês, devido a um novo recurso, que aumenta nossos temores sobre o desempenho em geral.

Estamos considerando as seguintes opções:

Dividir Tabela A

Colocaríamos a maioria das colunas da Tabela A em uma nova tabela filha (Tabela A Meta) com uma relação de 1 para 1 com a Tabela A. Apenas 4 a 6 colunas mais usadas permaneceriam na Tabela A e, presumivelmente, isso ajudaria faça consultas de leitura e contagem mais rápidas às custas de precisar de uma consulta separada (ou JOIN) para obter os detalhes adicionais de uma determinada linha, bem como uma pequena quantidade de sobrecarga de gravação adicional.

Espere por problemas e particione

A outra opção é ignorar o fato de que SELECT COUNT(*)é basicamente inutilizável, já que outras coisas estão funcionando corretamente (consultas por meio de campos indexados, etc.), e continuar usando a Tabela A mesmo após 50, 75 milhões de linhas etc., eventualmente particionando em algum momento no futuro.

Sei que essas opções não são mutuamente exclusivas, mas gostaria de obter algum feedback geral sobre essas opções e se você se incomodaria em fazer a primeira etc.

Execução

Aqui está o plano de execução para o mais COUNTmencionado:

db=# EXPLAIN ANALYZE SELECT COUNT(*) FROM "message";
                                                            QUERY PLAN                                                             
-----------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1236008.12..1236008.14 rows=1 width=8) (actual time=79357.273..79357.293 rows=1 loops=1)
   ->  Seq Scan on message  (cost=0.00..1173727.10 rows=24912410 width=0) (actual time=1.460..65375.321 rows=24926666 loops=1)
 Planning time: 0.543 ms
 Execution time: 79357.350 ms
(4 rows)

Um padrão comum que observei ao longo dos anos é que as pessoas tendem a continuar aumentando a memória de trabalho à medida que o tamanho dos dados aumenta, de modo que consultas mais complexas ainda podem aproveitar ao máximo a velocidade da memória, mas para algumas tarefas extremas (por exemplo, relatórios).

Pode ser vantajoso aumentar esse número drasticamente apenas para algumas consultas atípicas que raramente são executadas. Por exemplo, eu poderia querer manter work_mempor exemplo. 8 MB para operações normais, mas depois execute uma consulta específica com um cliente/usuário específico com algo como 250 MB de memória de trabalho para que eu possa fazer coisas drásticas inteiramente na memória durante uma única sessão, rapidamente.

Isso é possível? Estou completamente fora da base para pensar nisso como uma solução?

Eu tenho uma animaltabela com um name varchar(255), e adicionei linhas com os seguintes valores:

Piranha
__Starts With 2
Rhino
Starts With 1
0_Zebra
_Starts With 1
Antelope
_Starts With 1

Quando executo esta consulta:

zoology=# SELECT name FROM animal ORDER BY name;
      name       
-----------------
0_Zebra
Antelope
Piranha
Rhino
_Starts With 1
_Starts With 1
Starts With 1
__Starts With 2
(8 rows)

Observe como as linhas são classificadas em uma ordem que implica que a entrelinha _é usada para colocar as _Starts With 1linhas antes da Startslinha, mas o __no __Starts With 2parece ignorar esse fato, como se o 2no final fosse mais importante do que os dois primeiros caracteres.

Por que é isso?

Se eu classificar com Python, o resultado é:

In  [2]: for animal in sorted(animals):
   ....:     print animal
   ....:     
0_Zebra
Antelope
Piranha
Rhino
Starts With 1
_Starts With 1
_Starts With 1
__Starts With 2

Além disso, a ordem do Python sugere que os sublinhados vêm depois das letras, o que indica que a classificação do Postgres das duas primeiras _Startslinhas antes da Startslinha está incorreta.

Nota: estou usando o Postgres 9.1.15

Aqui estão minhas tentativas de encontrar o agrupamento:

zoology=# select datname, datcollate from pg_database;
  datname  | datcollate  
-----------+-------------
 template0 | en_US.UTF-8
 postgres  | en_US.UTF-8
 template1 | en_US.UTF-8
 zoology   | en_US.UTF-8
(4 rows)

E:

zoology=# select table_schema, 
    table_name, 
    column_name,
    collation_name
from information_schema.columns
where collation_name is not null
order by table_schema,
    table_name,
    ordinal_position;
 table_schema | table_name | column_name | collation_name 
--------------+------------+-------------+----------------
(0 rows)

Para começar, não tenho nenhum tipo de failover automatizado instalado.

Depois de dois cenários, não tenho certeza do estado do banco de dados e de quaisquer ações necessárias, se houver:

1. Meu servidor master desaparece da face do planeta e eu touch my_trigger_file, convertendo meu standby em um servidor master.
2. Meu servidor mestre trava e reinicia no modo de recuperação

Minha suposição é que o servidor em questão em cada um desses cenários estará primeiro no modo de recuperação, terminará a recuperação e finalmente estará pronto como um servidor mestre.

No cenário 2, minha suposição significa que as coisas voltaram ao normal, enquanto no cenário 1, minha suposição significa que o modo de espera não faz ideia de que já foi um modo de espera e agora é simplesmente um mestre.

Essas suposições estão corretas ou há outras ações que precisam ocorrer em um ou em ambos os cenários após o término da recuperação (além de criar um novo escravo no caso do cenário 1)?

Além disso, existe uma diferença técnica notável entre o estado de um servidor escravo depois de se tornar um mestre e um servidor mestre em recuperação após uma falha ou, no que diz respeito ao Postgres, ambos são essencialmente servidores mestres que acabaram de se recuperar de algo ?

Estou usando o Postgres 9.2 com replicação de streaming assíncrona.

Estou lendo http://www.postgresql.org/docs/9.2/static/pgarchivecleanup.html e para mim parece que removerá todo e qualquer segmento WAL do meu servidor escravo que não seja necessário para executar a recuperação após o servidor slave travando (então basicamente tudo, exceto o último 1 ou alguns segmentos, dependendo de quão atrás meu slave está rodando). Eu gostaria de ser capaz de executar uma recuperação pontual de emergência no caso infeliz de corrupção de dados ou exclusão acidental, se possível. Para fazer isso, fico com as seguintes opções:

1. Mantendo todos os segmentos do WAL desde que o último backup base foi criado (no meu caso, este é aquele a partir do qual o servidor escravo foi criado)
2. Fazendo backups básicos o tempo todo ou fazendo algo com o PGBarman, etc.?
3. Estou entendendo algo completamente errado?

Se a opção 1 for usada, presumo que o comando de limpeza do arquivo pg seja inútil para mim. Se a opção 2 for usada, é verdade que devo usar o comando de limpeza como pretendido (excluir todos os segmentos WAL extras imediatamente após serem confirmados)? Há algo mais que estou perdendo (consulte a opção 3)?

Nota: estou usando o Postgres 9.2.4

Estou usando o Django e de vez em quando recebo este erro:

IntegrityError: valor de chave duplicado viola restrição exclusiva "myapp_mymodel_pkey"
DETALHE: A chave (id)=(1) já existe.

Meu banco de dados Postgres de fato tem um objeto myapp_mymodel com a chave primária de 1.

Por que o Postgres tentaria usar essa chave primária novamente? Ou é mais provável que meu aplicativo (ou ORM do Django) esteja causando isso?

Este problema ocorreu mais 3 vezes seguidas agora. O que descobri é que, quando ocorre , acontece uma ou mais vezes seguidas para uma determinada tabela, e não novamente. Parece acontecer para todas as mesas antes de parar completamente por dias, acontecendo por pelo menos um minuto ou mais por mesa quando ocorre, e acontecendo apenas intermitentemente (nem todas as mesas imediatamente).

O fato de esse erro ser tão intermitente (aconteceu apenas 3 ou mais vezes em 2 semanas - nenhuma outra carga no banco de dados, apenas eu testando meu aplicativo) é o que me deixa tão cauteloso com um problema de baixo nível.

O pg_xlogdiretório do meu cluster mestre tem esta aparência:

-rw------- 1 postgres postgres  16M Jun 21 21:42 000000010000000000000001
-rw------- 1 postgres postgres  16M Jun 21 22:42 000000010000000000000002
-rw------- 1 postgres postgres  16M Jun 21 23:42 000000010000000000000003
-rw------- 1 postgres postgres  16M Jun 22 00:42 000000010000000000000004
-rw------- 1 postgres postgres  16M Jun 22 01:42 000000010000000000000005
-rw------- 1 postgres postgres  16M Jun 22 01:49 000000010000000000000006
-rw------- 1 postgres postgres  16M Jun 22 01:55 000000010000000000000007
-rw------- 1 postgres postgres  16M Jun 22 02:05 000000010000000000000008
-rw------- 1 postgres postgres  16M Jun 22 02:30 000000010000000000000009
-rw------- 1 postgres postgres  16M Jun 22 02:50 00000001000000000000000A

O pg_xlog/archive_statusdiretório se parece com isso:

-rw------- 1 postgres postgres 0 Jun 22 01:49 000000010000000000000006.done
-rw------- 1 postgres postgres 0 Jun 22 01:55 000000010000000000000007.done
-rw------- 1 postgres postgres 0 Jun 22 02:05 000000010000000000000008.done
-rw------- 1 postgres postgres 0 Jun 22 02:30 000000010000000000000009.done

Quando ativei o arquivamento pela primeira vez, 006era o segmento atual, mas por que não eram 001- 005arquivados mesmo assim? A última vez que configurei o arquivamento em um cluster existente (que foi ontem - tenho experimentado), todos os segmentos de log foram arquivados quando ativei o arquivamento, apesar do pg_xlogdiretório conter mais de 50 segmentos de log anteriores.

Configurações relevantes:

wal_level = hot_standby
archive_mode = on
wal_keep_segments = 2048  # I know, right :)
archive_timeout = 3600
archive_command = 'rsync -aq --delay-updates --ignore-existing %p postgres@db-slave:/mnt/pgsql/'

Observação: alterei todas essas configurações (incluindo was_level, que era minimalanterior) quando ativei o arquivamento anteriormente. Será que meu nível de wal não era suficiente para justificar o arquivamento ou porque há uma incompatibilidade entre o nível anterior e o nível atual?

Estou perguntando isso porque o Postgres foi interrompido ontem e temo que possa haver dados parciais/corrompidos em um dos meus segmentos de log arquivados. Eu gostaria de simplesmente deletar os logs do meu servidor escravo e apenas fazer o Postgres começar de novo desde o início (existem apenas cerca de 90 segmentos, então não vai demorar muito), mas não tenho certeza do que fazer, para obter Postgres para fazer isso.

Configurei um banco de dados Postgres em um servidor com 2 núcleos de CPU, 4 GB de RAM e um volume SSD (além do RAID 10, é claro). Este é o nosso (mais ou menos) servidor de banco de dados de produção "carnudo" (posso facilmente adicionar mais RAM, etc. quando chegar a hora).

Agora é hora de configurar um servidor Postgres de backup (não usado para leituras ou qualquer outra coisa, apenas estritamente uma replicação/backup). Não quero desembolsar mais US$ 150/mês para a mesma configuração exata do meu servidor de backup e sei que muitos dos recursos do servidor usados ​​para um banco de dados de produção têm a ver com consultas complicadas, verificações sequenciais etc. ., então pensei que poderia me safar com um servidor de 512 MB com cerca de 1/6 do desempenho de E/S e 1 núcleo de CPU.

Esse pensamento está correto ou um servidor Postgres de replicação/backup normalmente precisa quase das mesmas especificações de um servidor Postgres de produção?

Observação: estou usando o Postgres 9.2.4 com replicação de streaming (não síncrona) em um servidor Ubuntu 12.04.2.

1. Instalei apt-geto Postgres 9.2.4 (usando PPA).
2. Eu costumava pg_dropcluster --stop 9.2 mainremover o cluster de banco de dados padrão, porque pretendia criar um novo cluster em um volume SSD separado (um volume de armazenamento em bloco Rackspace) que acabou de ser montado.
3. Eu criei esse novo cluster cujos dados residem no meu volume SSD e o iniciei ( psmostra todos os processos normais do Postgres em execução para esse novo cluster e consigo abrir um shell do Postgres e executar o SQL - ou seja, está funcionando bem). Agora, quando eu corro pg_lsclusters, não há nenhum listado.

O maincluster listou muito bem antes de excluí-lo. Por que meu novo cluster não aparece, mesmo quando está em execução? Eu mesmo reiniciei o servidor (apenas no caso).

Desejo configurar um banco de dados de backup no servidor físico que fica logo abaixo do meu servidor de banco de dados físico principal. Estou usando o Postgres 9.2 e quero usar a replicação de streaming síncrona (para atomicidade), mas desconheço completamente como coisas como A) a configuração inicial da tabela é transferida do servidor 1 para o servidor 2 (por exemplo, eu corro syncdbdo meu aplicativo Django, que cria uma série de tabelas no servidor 1) e B) como as alterações contínuas do esquema são transportadas do servidor 1 para o servidor 2 (por exemplo, eu executo uma migração do Django South, que envia ALTER TABLEconsultas, além de adicionar/ remover índices, etc.). Essas coisas são tratadas de forma transparente com a replicação de streaming ou há algo que devo fazer para afetar essa alteração nos dois servidores?

O estado dos documentos de consumo de recursos do Postgres , em shared_buffers:

...é improvável que uma alocação de mais de 40% de RAM para shared_buffers funcione melhor do que uma quantidade menor.

Por que é isso? Sempre me disseram que quanto mais RAM um servidor tiver, melhor será o desempenho do Postgres. Não é shared_buffersa configuração de memória por excelência para Postgres? Se estou alocando apenas 3 GB de 12 GB (o ponto de partida recomendado de 25%) em meu servidor para shared_buffers, onde posso esperar que o Postgres aproveite pelo menos 6 GB a mais?