Hassan Baig's questions

Estou tendo problemas com falhas inesperadas no sistema provenientes de uma implantação do postgresql em um servidor de produção moderadamente ocupado (aplicativo Django 1.8 implantado no droplet DO com um gunicornservidor de aplicativos e nginxproxy reverso).

Sou um DBA acidental e preciso da opinião de um especialista para entender o que está acontecendo. Eu não tenho ideia agora; Vou fornecer vários dados de log abaixo:

Todo o aplicativo trava - a nova relíquia acaba mostrando o seguinte erro:

django.db.utils:OperationalError: ERROR: no more connections allowed (max_client_conn)

Rastreamento de pilha completo sendo:

File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gevent/baseserver.py", line 26, in _handle_and_close_when_done
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/ggevent.py", line 155, in handle
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/base_async.py", line 56, in handle
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/ggevent.py", line 160, in handle_request
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/base_async.py", line 114, in handle_request
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/api/web_transaction.py", line 704, in __iter__
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/api/web_transaction.py", line 1080, in __call__
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/handlers/wsgi.py", line 189, in __call__
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/handlers/base.py", line 108, in get_response
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/hooks/framework_django.py", line 228, in wrapper
File "/home/ubuntu/app/mproject/middleware/mycustommiddleware.py", line 6, in process_request
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/utils/functional.py", line 225, in inner
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/utils/functional.py", line 376, in _setup
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/contrib/auth/middleware.py", line 22, in <lambda>
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/contrib/auth/middleware.py", line 10, in get_user
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/contrib/auth/__init__.py", line 174, in get_user
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/contrib/auth/backends.py", line 93, in get_user
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/models/manager.py", line 127, in manager_method
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/models/query.py", line 328, in get
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/models/query.py", line 144, in __len__
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/models/query.py", line 965, in _fetch_all
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/models/query.py", line 238, in iterator
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 838, in execute_sql
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 164, in cursor
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 135, in _cursor
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 130, in ensure_connection
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/utils.py", line 98, in __exit__
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 130, in ensure_connection
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 119, in connect
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 176, in get_new_connection
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/hooks/database_dbapi2.py", line 102, in __call__
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/psycopg2/__init__.py", line 126, in connect
File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/psycogreen/gevent.py", line 32, in gevent_wait_callback

A nova relíquia também mostra um grande aumento em Postgres set:

O log lento do Postgresql mostra durações de COMMIT exorbitantes assim:

2020-05-15 01:22:47.474 UTC [10722] ubuntu@myapp LOG:  duration: 280668.902 ms  statement: COMMIT
2020-05-15 01:22:47.474 UTC [11536] ubuntu@myapp LOG:  duration: 49251.277 ms  statement: COMMIT
2020-05-15 01:22:47.474 UTC [10719] ubuntu@myapp LOG:  duration: 281609.394 ms  statement: COMMIT
2020-05-15 01:22:47.474 UTC [10719] ubuntu@myapp LOG:  could not send data to client: Broken pipe
2020-05-15 01:22:47.474 UTC [10719] ubuntu@myapp FATAL:  connection to client lost
2020-05-15 01:22:47.475 UTC [10672] ubuntu@myapp LOG:  duration: 285371.872 ms  statement: COMMIT

O painel do oceano digital mostra um pico em Load Averagee Disk IO(mas CPU, memory usagee disk usagepermanece baixo).

As estatísticas do instantâneo no momento desse Load Averagepico são:

syslogtem inúmeras instâncias do seguinte log de erros:

May 15 01:22:46 main-app gunicorn[10779]: error: [Errno 111] Connection refused
May 15 01:22:46 main-app gunicorn[10779]: [2020-05-15 01:22:46 +0000] [10874] [ERROR] Socket error processing request.
May 15 01:22:46 main-app gunicorn[10779]: Traceback (most recent call last):
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/base_async.py", line 66, in handle
May 15 01:22:46 main-app gunicorn[10779]:     six.reraise(*sys.exc_info())
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/base_async.py", line 56, in handle
May 15 01:22:46 main-app gunicorn[10779]:     self.handle_request(listener_name, req, client, addr)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/ggevent.py", line 160, in handle_request
May 15 01:22:46 main-app gunicorn[10779]:     addr)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/base_async.py", line 129, in handle_request
May 15 01:22:46 main-app gunicorn[10779]:     six.reraise(*sys.exc_info())
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gunicorn/workers/base_async.py", line 114, in handle_request
May 15 01:22:46 main-app gunicorn[10779]:     for item in respiter:
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/api/web_transaction.py", line 704, in __iter__
May 15 01:22:46 main-app gunicorn[10779]:     for item in self.generator:
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/api/web_transaction.py", line 1080, in __call__
May 15 01:22:46 main-app gunicorn[10779]:     self.start_response)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/handlers/wsgi.py", line 189, in __call__
May 15 01:22:46 main-app gunicorn[10779]:     response = self.get_response(request)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/handlers/base.py", line 218, in get_response
May 15 01:22:46 main-app gunicorn[10779]:     response = self.handle_uncaught_exception(request, resolver, sys.exc_info())
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/hooks/framework_django.py", line 448, in wrapper
May 15 01:22:46 main-app gunicorn[10779]:     return _wrapped(*args, **kwargs)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/hooks/framework_django.py", line 441, in _wrapped
May 15 01:22:46 main-app gunicorn[10779]:     return wrapped(request, resolver, exc_info)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/handlers/base.py", line 256, in handle_uncaught_exception
May 15 01:22:46 main-app gunicorn[10779]:     'request': request
May 15 01:22:46 main-app gunicorn[10779]:   File "/usr/lib/python2.7/logging/__init__.py", line 1193, in error
May 15 01:22:46 main-app gunicorn[10779]:     self._log(ERROR, msg, args, **kwargs)
May 15 01:22:46 main-app gunicorn[10779]:   File "/usr/lib/python2.7/logging/__init__.py", line 1286, in _log
May 15 01:22:46 main-app gunicorn[10779]:     self.handle(record)
May 15 01:22:46 main-app gunicorn[10779]:   File "/usr/lib/python2.7/logging/__init__.py", line 1296, in handle
May 15 01:22:46 main-app gunicorn[10779]:     self.callHandlers(record)
May 15 01:22:46 main-app gunicorn[10779]:   File "/usr/lib/python2.7/logging/__init__.py", line 1336, in callHandlers
May 15 01:22:46 main-app gunicorn[10779]:     hdlr.handle(record)
May 15 01:22:46 main-app gunicorn[10779]:   File "/usr/lib/python2.7/logging/__init__.py", line 759, in handle
May 15 01:22:46 main-app gunicorn[10779]:     self.emit(record)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/utils/log.py", line 129, in emit
May 15 01:22:46 main-app gunicorn[10779]:     self.send_mail(subject, message, fail_silently=True, html_message=html_message)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/utils/log.py", line 132, in send_mail
May 15 01:22:46 main-app gunicorn[10779]:     mail.mail_admins(subject, message, *args, connection=self.connection(), **kwargs)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/api/function_trace.py", line 110, in literal_wrapper
May 15 01:22:46 main-app gunicorn[10779]:     return wrapped(*args, **kwargs)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/mail/__init__.py", line 98, in mail_admins
May 15 01:22:46 main-app gunicorn[10779]:     mail.send(fail_silently=fail_silently)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/newrelic-2.56.0.42/newrelic/api/function_trace.py", line 110, in literal_wrapper
May 15 01:22:46 main-app gunicorn[10779]:     return wrapped(*args, **kwargs)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/mail/message.py", line 303, in send
May 15 01:22:46 main-app gunicorn[10779]:     return self.get_connection(fail_silently).send_messages([self])
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/mail/backends/smtp.py", line 100, in send_messages
May 15 01:22:46 main-app gunicorn[10779]:     new_conn_created = self.open()
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/django/core/mail/backends/smtp.py", line 58, in open
May 15 01:22:46 main-app gunicorn[10779]:     self.connection = connection_class(self.host, self.port, **connection_params)
May 15 01:22:46 main-app gunicorn[10779]:   File "/usr/lib/python2.7/smtplib.py", line 256, in __init__
May 15 01:22:46 main-app gunicorn[10779]:     (code, msg) = self.connect(host, port)
May 15 01:22:46 main-app gunicorn[10779]:   File "/usr/lib/python2.7/smtplib.py", line 316, in connect
May 15 01:22:46 main-app gunicorn[10779]:     self.sock = self._get_socket(host, port, self.timeout)
May 15 01:22:46 main-app gunicorn[10779]:   File "/usr/lib/python2.7/smtplib.py", line 291, in _get_socket
May 15 01:22:46 main-app gunicorn[10779]:     return socket.create_connection((host, port), timeout)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gevent/socket.py", line 96, in create_connection
May 15 01:22:46 main-app gunicorn[10779]:     sock.connect(sa)
May 15 01:22:46 main-app gunicorn[10779]:   File "/home/ubuntu/.virtualenvs/app/local/lib/python2.7/site-packages/gevent/_socket2.py", line 244, in connect
May 15 01:22:46 main-app gunicorn[10779]:     raise error(err, strerror(err))

Algumas linhas de vmtstat 1:

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0      0 4698028 280600 16692992    0    0     0    14    0    0 13  1 87  0  0
 3  0      0 4697204 280600 16693016    0    0     0    24 8294 8111 12  0 88  0  0
 3  0      0 4698148 280600 16693044    0    0     0   204 8533 9047 15  0 85  0  0
 4  0      0 4697628 280600 16693092    0    0     0    76 8695 8775 17  1 83  0  0
 3  0      0 4697288 280600 16693140    0    0     8    16 9578 9720 19  1 80  0  0
 3  0      0 4695768 280600 16693200    0    0     0   152 8594 8073 16  1 83  0  0
 6  0      0 4686044 280600 16693272    0    0     0    32 7998 7706 12  1 87  0  0
 5  0      0 4681876 280600 16693308    0    0     0   224 9130 8810 14  1 85  0  0

Isso é praticamente tudo o que tenho. Eu não posso juntar o que poderia estar acontecendo em tudo. Está acontecendo quase uma vez por dia agora. Algum especialista pode dar uma luz sobre isso? Desde já, obrigado!

Nota: em caso de garantia, SELECT version();produz:

PostgreSQL 9.6.16 on x86_64-pc-linux-gnu (Ubuntu 9.6.16-1.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit

Em um aplicativo chamado Links, os usuários publicam conteúdo interessante que descobriram recentemente e outros podem comentar essas postagens.

Há também uma funcionalidade adicional, que permite aos usuários deixar um comentário em um comentário.

Todos os comentários (e comentários sobre comentários) são salvos em uma tabela chamada links_publicreplyno meu banco de dados postgresql 9.6.5. Esta tabela contém uma chave estrangeira auto-referenciada para dar suporte ao comments on comments. É uma tabela grande, com quase 200 milhões de linhas.

Uma UPDATEconsulta na links_publicreplytabela está aparecendo consistentemente no slow_log. Está demorando mais de 5000ms e é ~100X mais lento do que estou experimentando na maioria das outras operações do postgresql.

Aqui está um exemplo do SQL correspondente do meu log lento:

UPDATE "links_publicreply"
SET "direct_reply_tgt_text_prefix"='',
    "direct_reply_tgt_text_postfix"=''
WHERE "links_publicreply"."direct_reply_id"=175054159;

Veja os resultados da análise explicativa: https://explain.depesz.com/s/G8wX

De acordo com isso, o Seq Scan acaba filtrando 47.535.365 linhas e é a fonte da lentidão.

O que faço para reduzir esse tempo de execução exorbitante?

Sendo um DBA acidental, não sou especialista no assunto. Minha intuição é que estou filtrando a chave estrangeira auto-referencial, então isso já deveria ser um índice (portanto otimizado para pesquisas)? Estou um pouco perplexo aqui.

Adição:

Aqui está toda a saída para \d links_publicreply:

            Column             |           Type           |                           Modifiers                            
-------------------------------+--------------------------+----------------------------------------------------------------
 id                            | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id               | integer                  | not null
 answer_to_id                  | integer                  | not null
 submitted_on                  | timestamp with time zone | not null
 description                   | text                     | not null
 category                      | character varying(20)    | not null
 seen                          | boolean                  | not null
 abuse                         | boolean                  | not null
 direct_reply_tgt_uname        | text                     | 
 direct_reply_tgt_text_prefix  | text                     | 
 direct_reply_tgt_text_postfix | text                     | 
 direct_reply_id               | integer                  | 
 level                         | integer                  | 
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "id_answer_to_id" btree (answer_to_id, id DESC)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_direct_reply_id_fkey" FOREIGN KEY (direct_reply_id) REFERENCES links_publicreply(id)
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_direct_reply_id_fkey" FOREIGN KEY (direct_reply_id) REFERENCES links_publicreply(id)
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

Em um aplicativo chamado 'Links', os usuários postam links e fotos de conteúdo interessante que descobriram recentemente (e outros comentam sobre as referidas postagens).

Esses comentários postados sob as fotos são salvos em uma links_photocommenttabela no meu banco de dados postgresql 9.6.5.

Uma SELECTconsulta na links_photocommenttabela está aparecendo consistentemente no slow_log. Está demorando mais de 500 ms e é ~ 10 vezes mais lento do que estou experimentando na maioria das outras operações do postgresql.

Aqui está um exemplo do SQL correspondente do meu log lento:

LOG: duração: 5071,112 ms declaração:

SELECT "links_photocomment"."abuse",
       "links_photocomment"."text",
       "links_photocomment"."id",
       "links_photocomment"."submitted_by_id",
       "links_photocomment"."submitted_on",
       "auth_user"."username",
       "links_userprofile"."score"
FROM   "links_photocomment"
       INNER JOIN "auth_user"
               ON ( "links_photocomment"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
                    ON ( "auth_user"."id" = "links_userprofile"."user_id" )
WHERE  "links_photocomment"."which_photo_id" = 3115087
ORDER  BY "links_photocomment"."id" DESC
LIMIT  25;

Veja os explain analyzeresultados: https://explain.depesz.com/s/UuCk

A consulta acaba filtrando 19.100.179 linhas de acordo com isso!

O que eu tentei:

Minha intuição é que o Postgres baseia esse plano de consulta em estatísticas enganosas. Assim, eu corri VACUUM ANALYZEna referida mesa. No entanto, isso não mudou nada.

Sendo uma espécie de DBA acidental, estou procurando uma orientação rápida de especialistas sobre o assunto. Desde já agradeço e peço desculpas pela pergunta noob (se for).

Termo aditivo:

Aqui está toda a saída para \d links_photocomment:

                                      Table "public.links_photocomment"
     Column      |           Type           |                            Modifiers                            
-----------------+--------------------------+-----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_photocomment_id_seq'::regclass)
 which_photo_id  | integer                  | not null
 text            | text                     | not null
 device          | character varying(10)    | not null
 submitted_by_id | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 image_comment   | character varying(100)   | not null
 has_image       | boolean                  | not null
 abuse           | boolean                  | default false
Indexes:
    "links_photocomment_pkey" PRIMARY KEY, btree (id)
    "links_photocomment_submitted_by_id" btree (submitted_by_id)
    "links_photocomment_which_photo_id" btree (which_photo_id)
Foreign-key constraints:
    "links_photocomment_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "links_photocomment_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_photo" CONSTRAINT "latest_comment_id_refs_id_f2566197" FOREIGN KEY (latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_photocomment_id_fkey" FOREIGN KEY (which_photocomment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photo" CONSTRAINT "second_latest_comment_id_refs_id_f2566197" FOREIGN KEY (second_latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED

Em um aplicativo chamado Links, os usuários postam conteúdo interessante em uma interface semelhante a um fórum, e outros postam respostas ou comentários sob esse conteúdo postado publicamente.

Essas respostas postadas são salvas em uma tabela postgresql chamada links_publicreply(postgresql 9.6.5 DB).

Uma consulta executada na tabela continua surgindo no slow_log(maior que 500ms). Isso está relacionado à exibição das 25 respostas públicas mais recentes acumuladas em um determinado conteúdo compartilhado.

Aqui está uma amostra do log lento:

LOG: duração: 1614,030 ms declaração:

 SELECT "links_publicreply"."id",
       "links_publicreply"."submitted_by_id",
       "links_publicreply"."answer_to_id",
       "links_publicreply"."submitted_on",
       "links_publicreply"."description",
       "links_publicreply"."abuse",
       "auth_user"."id",
       "auth_user"."username",
       "links_userprofile"."id",
       "links_userprofile"."user_id",
       "links_userprofile"."score",
       "links_userprofile"."avatar",
       "links_link"."id",
       "links_link"."description",
       "links_link"."submitter_id",
       "links_link"."submitted_on",
       "links_link"."reply_count",
       "links_link"."latest_reply_id"
FROM   "links_publicreply"
       INNER JOIN "links_link"
               ON ( "links_publicreply"."answer_to_id" = "links_link"."id" )
       INNER JOIN "auth_user"
               ON ( "links_publicreply"."submitted_by_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
                    ON ( "auth_user"."id" = "links_userprofile"."user_id" )
WHERE  "links_publicreply"."answer_to_id" = 8936203
ORDER  BY "links_publicreply"."id" DESC
LIMIT  25  

Aqui estão os explain analyzeresultados da referida consulta: https://explain.depesz.com/s/pVZ5

De acordo com isso, cerca de 70% do tempo de consulta parece ser ocupado pela verificação do índice. Mas para um DBA acidental como eu, não é imediatamente óbvio qual otimização posso fazer para tornar isso mais eficiente. Talvez um índice composto em links_publicreply.answer_to_id, links_publicreply.id?

Isso me ajudaria muito a saber se um especialista em domínio pode fornecer orientação + intuição para resolver esse tipo de problema.

P \d links_publicreplyé:

                                      Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers                            
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
 category        | character varying(20)    | not null
 seen            | boolean                  | not null
 abuse           | boolean                  | not null
 device          | character varying(10)    | default '1'::character varying
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

Em um aplicativo chamado Links , os usuários postam fotos de conteúdo interessante que descobriram recentemente (e outros podem votar ou comentar nelas).

Essas fotos postadas são salvas em uma links_phototabela no meu banco de dados postgresql 9.6.5.

O perfil de cada usuário mostra suas fotos postadas - paginadas por 10objetos cada - ordenadas por upload time.

Uma SELECTconsulta na links_phototabela está aparecendo consistentemente no slow_log. Está demorando mais de 500 ms e é ~ 10 vezes mais lento do que estou experimentando na maioria das outras operações do postgresql.

Aqui está um exemplo do SQL correspondente do meu log lento:

LOG: duração: 542,755 ms declaração:

SELECT "links_photo"."id",
       "links_photo"."owner_id",
       "links_photo"."image_file",
       "links_photo"."upload_time",
       "links_photo"."comment_count",
       "links_photo"."vote_score",
       "links_photo"."caption",
       "links_photo"."category",
       "auth_user"."id",
       "auth_user"."username",
       "auth_user"."date_joined",
       "links_userprofile"."id",
       "links_userprofile"."user_id",
       "links_userprofile"."score",
       "links_userprofile"."avatar"
FROM   "links_photo"
       INNER JOIN "auth_user"
               ON ( "links_photo"."owner_id" = "auth_user"."id" )
       LEFT OUTER JOIN "links_userprofile"
                    ON ( "auth_user"."id" = "links_userprofile"."user_id" )
WHERE  ( "links_photo"."owner_id" = 78689
         AND "links_photo"."category" = '1' )
ORDER  BY "links_photo"."upload_time" DESC
LIMIT  10 offset 10

Veja os explain analyzeresultados: https://explain.depesz.com/s/DPJo

De acordo com isso, o Index Scan Backward acaba filtrando 1.196.188 linhas e é a fonte da lentidão.

O que acho que devo tentar:

Sendo uma espécie de DBA acidental, estou procurando uma orientação rápida de especialistas sobre o assunto. Eu teria pensado que ter um índice upload_timeseria suficiente, mas não. Então, talvez um composto em (owner_id, upload_time DESC)?

Adição:

Aqui está toda a saída para \d links_photo:

                                           Table "public.links_photo"
          Column          |           Type           |                        Modifiers                         
--------------------------+--------------------------+----------------------------------------------------------
 id                       | integer                  | not null default nextval('links_photo_id_seq'::regclass)
 owner_id                 | integer                  | not null
 image_file               | character varying(100)   | not null
 upload_time              | timestamp with time zone | not null
 comment_count            | integer                  | not null
 is_public                | boolean                  | not null
 vote_score               | integer                  | not null
 visible_score            | integer                  | not null
 invisible_score          | double precision         | not null
 caption                  | character varying(100)   | 
 avg_hash                 | character varying(16)    | not null
 latest_comment_id        | integer                  | 
 second_latest_comment_id | integer                  | 
 category                 | character varying(11)    | not null
 device                   | character varying(10)    | not null
Indexes:
    "links_photo_pkey" PRIMARY KEY, btree (id)
    "links_photo_latest_comment_id" btree (latest_comment_id)
    "links_photo_owner_id" btree (owner_id)
    "links_photo_second_latest_comment_id" btree (second_latest_comment_id)
    "links_photo_upload_time" btree (upload_time)
Foreign-key constraints:
    "latest_comment_id_refs_id_f2566197" FOREIGN KEY (latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
    "links_photo_owner_id_fkey" FOREIGN KEY (owner_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "second_latest_comment_id_refs_id_f2566197" FOREIGN KEY (second_latest_comment_id) REFERENCES links_photocomment(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_photostream" CONSTRAINT "cover_id_refs_id_d62b783f" FOREIGN KEY (cover_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photocomment" CONSTRAINT "links_photocomment_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photoobjectsubscription" CONSTRAINT "links_photoobjectsubscription_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photovote" CONSTRAINT "links_photovote_photo_id_fkey" FOREIGN KEY (photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photo_which_stream" CONSTRAINT "photo_id_refs_id_916b4355" FOREIGN KEY (photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED

Em um aplicativo chamado 'Links', os usuários postam links de conteúdo interessante que descobriram recentemente (e outros votam nas referidas postagens).

Esses links postados são salvos em uma links_linktabela no meu banco de dados postgresql 9.6.5.

SELECTUma consulta de aparência inócua na links_linktabela está aparecendo consistentemente no slow_log. Está demorando mais de 500 ms e é ~ 10 vezes mais lento do que estou experimentando na maioria das outras operações do postgresql.

Aqui está um exemplo do SQL correspondente do meu log lento:

LOG: duração: 8648,676 ms declaração:

SELECT "links_link"."id",
       "links_link"."description",
       "links_link"."submitted_on",
       "links_link"."reply_count",
       "links_link"."net_votes"
FROM   "links_link"
WHERE  "links_link"."submitter_id" = 811645
ORDER  BY "links_link"."id" DESC
LIMIT  1  

Veja os explain analyzeresultados: https://explain.depesz.com/s/Xp5v

A consulta acaba filtrando 14.331.127 linhas de acordo com isso!

O que eu tentei:

Minha intuição é que o Postgres baseia esse plano de consulta em estatísticas enganosas. Assim, eu corri VACUUM ANALYZEna referida mesa. No entanto, isso não mudou nada.

Sendo uma espécie de DBA acidental, estou procurando uma orientação rápida de especialistas sobre o assunto. Desde já agradeço e peço desculpas pela pergunta noob (se for).

Editar:

Aqui está toda a saída para \d links_link:

                                         Table "public.links_link"
        Column        |           Type           |                        Modifiers                        
----------------------+--------------------------+---------------------------------------------------------
 id                   | integer                  | not null default nextval('links_link_id_seq'::regclass)
 description          | text                     | not null
 submitter_id         | integer                  | not null
 submitted_on         | timestamp with time zone | not null
 rank_score           | double precision         | not null
 url                  | character varying(250)   | not null
 cagtegory            | character varying(25)    | not null
 image_file           | character varying(100)   | 
 reply_count          | integer                  | default 0
 device               | character varying(10)    | default '1'::character varying
 latest_reply_id      | integer                  | 
 which_photostream_id | integer                  | 
 is_visible           | boolean                  | default true
 net_votes            | integer                  | default 0
Indexes:
    "links_link_pkey" PRIMARY KEY, btree (id)
    "links_link_latest_reply_id_idx" btree (latest_reply_id)
    "links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
    "link_whichphotostreamid_fkey" FOREIGN KEY (which_photostream_id) REFERENCES links_photostream(id) ON UPDATE CASCADE ON DELETE CASCADE
    "links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_link_id_fkey" FOREIGN KEY (which_link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_vote" CONSTRAINT "links_vote_link_id_fkey" FOREIGN KEY (link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photoobjectsubscription" CONSTRAINT "which_link_id_photoobjectsubscription" FOREIGN KEY (which_link_id) REFERENCES links_link(id) ON DELETE CASCADE

Ao configurar o postgreSQL com um servidor web (todos na mesma máquina), é fundamental ter 0.0.0.0:5432aberto ou é 127.0.0.1:5432suficiente? Por que ou por que não?

No geral, estou tentando entender o papel que as portas desempenham no encaminhamento de tráfego para o serviço pg.

O pano de fundo é que estou tentando configurar o pgbouncer (um pooler de conexões) para trabalhar com o postgresql, portanto, preciso garantir que as portas relevantes estejam abertas. No entanto, antes de fazer isso, preciso entender como a rede funciona nesse nível.

Eu tenho um aplicativo chamado 'Links' onde 1) os usuários se reúnem em grupos e adicionam outros, 2) publicam conteúdo uns para os outros nos referidos grupos. Os grupos são definidos por uma links_grouptabela no meu banco de dados postgresql 9.6.5, enquanto as respostas que eles postam neles são definidas por uma links_replytabela. No geral, o desempenho do DB é ótimo.

No entanto, uma SELECTconsulta na links_replytabela está aparecendo consistentemente em slow_log. Está demorando mais de 500 ms e é ~ 10 vezes mais lento do que estou experimentando na maioria das outras operações do postgresql.

Eu usei o Django ORM para gerar a consulta. Aqui está a chamada ORM: replies = Reply.objects.select_related('writer__userprofile').filter(which_group=group).order_by('-submitted_on')[:25]. Essencialmente, isso é selecionar as últimas 25 respostas para um determinado objeto de grupo. Também está selecionando associados usere userprofileobjetos.

Aqui está um exemplo do SQL correspondente do meu log lento: LOG: duration: 8476.309 ms statement:

SELECT

    "links_reply"."id",             "links_reply"."text", 
    "links_reply"."which_group_id", "links_reply"."writer_id",
    "links_reply"."submitted_on",   "links_reply"."image",
    "links_reply"."device",         "links_reply"."category", 

    "auth_user"."id",               "auth_user"."username", 

    "links_userprofile"."id",       "links_userprofile"."user_id",
    "links_userprofile"."score",    "links_userprofile"."avatar" 

FROM 

    "links_reply" 
    INNER JOIN "auth_user" 
        ON ("links_reply"."writer_id" = "auth_user"."id") 
    LEFT OUTER JOIN "links_userprofile" 
        ON ("auth_user"."id" = "links_userprofile"."user_id") 
WHERE "links_reply"."which_group_id" = 124479 
ORDER BY "links_reply"."submitted_on" DESC 
LIMIT 25

Veja os resultados da análise explicativa aqui: https://explain.depesz.com/s/G4X A varredura do índice (para trás) parece estar consumindo o tempo todo.

Aqui está a saída de \d links_reply:

Table "public.links_reply"
     Column     |           Type           |                        Modifiers                         
----------------+--------------------------+----------------------------------------------------------
 id             | integer                  | not null default nextval('links_reply_id_seq'::regclass)
 text           | text                     | not null
 which_group_id | integer                  | not null
 writer_id      | integer                  | not null
 submitted_on   | timestamp with time zone | not null
 image          | character varying(100)   | 
 category       | character varying(15)    | not null
 device         | character varying(10)    | default '1'::character varying
Indexes:
    "links_reply_pkey" PRIMARY KEY, btree (id)
    "category_index" btree (category)
    "links_reply_submitted_on" btree (submitted_on)
    "links_reply_which_group_id" btree (which_group_id)
    "links_reply_writer_id" btree (writer_id)
    "text_index" btree (text)
Foreign-key constraints:
    "links_reply_which_group_id_fkey" FOREIGN KEY (which_group_id) REFERENCES links_group(id) DEFERRABLE INITIALLY DEFERRED
    "links_reply_writer_id_fkey" FOREIGN KEY (writer_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_groupseen" CONSTRAINT "links_groupseen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_reply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_reply(id) DEFERRABLE INITIALLY DEFERRED

É uma tabela grande (~ 25 milhões de linhas). O hardware em que está operando tem 16 núcleos e 60 GB de memória. Ele compartilha esta máquina com um aplicativo python. Mas tenho monitorado o desempenho do servidor e não vejo gargalos ali.

Existe alguma maneira de melhorar o desempenho desta consulta? Por favor, informe sobre todas as opções (se houver) que tenho aqui.

Observe que essa consulta teve um desempenho excepcionalmente bom até a semana passada . O que mudou desde então? Realizei um pg_dumpe depois pg_restoredo DB (em uma VM separada), e atualizei do Postgresql 9.3.10 para 9.6.5. Também usei um pool de conexões chamado pgbouncerantes, que ainda não configurei na nova VM para a qual migrei. É isso.

Por fim, também notei (pela experiência do usuário) que para todos os objetos de grupo criados até a semana passada, a consulta ainda é executada rapidamente. Mas todos os novos objetos que estão sendo criados agora estão produzindo um log lento. Isso poderia ser algum tipo de problema de indexação, especificamente com o links_reply_submitted_oníndice?

Atualização: As otimizações prescritas realmente mudaram as coisas. Dar uma olhada:

Vou transferir um banco de dados postgresql 9.3.10 (~ 55 GB) da VM do Azure para um AWS EC2. Sendo um novato, esta questão é sobre como acertar o processo. Eu vi este post e preciso ir um pouco além disso:

1. O despejo do banco de dados de produção ativo em um servidor ocupado é perfeitamente seguro? Os documentos explicam como o pg_dump é non-blocking , então estou inclinado a pensar que é. Na sua experiência, pg_dumpsempre funciona sem problemas na produção, ou há exceções? Minha outra opção (desagradável) é desligar o servidor, criando uma janela de manutenção "difícil".

2. Os documentos mencionam o uso --formatpara controlar o formato de despejo. c(custom) parece promissor, mas achei a descrição vaga em termos de como usá-lo. Devo simplesmente tentar o pg_dumpcomando assim: pg_dump -U postgres -Fc db_name > db.dump? Quaisquer outros sinalizadores práticos também seriam de interesse.

3. Um guia simples que li recomenda agrupar toda a restauração em uma única transação: psql -1 restored_database < backup_file. Isso fornece um resultado binário falhando totalmente se houver erros de restauração. i) O que poderia causar erros de restauração? e ii) o comando de restauração que originalmente planejei usar é pg_restore --verbose --clean --no-acl --no-owner -U myuser -d mydb latest.dump. Isso parece certo?

Depois de receber algumas orientações perspicazes em um post anterior aqui, vou rodar VACUUM FULLem 4 tabelas PostgreSQL 9.3.10. Os tamanhos das mesas são:

1) links_publicreply: ~30 milhões de linhas, 9 colunas, 3 índices (tipos: int, timestamp, char, bool)

2) links_reply: ~25 milhões de linhas, 8 colunas, 6 índices (tipos: int, text, timestamp, char)

3) links_link: ~8 milhões de linhas, 14 colunas, 3 índices (tipos: int, text, dbl precision, timestamp, char bool)

4) links_user_sessions: ~2 milhões de linhas, 7 colunas, 4 índices (tipos: int, text, timestamp, inet)

Esta é minha primeira tentativa de recuperar espaço em disco. É um servidor ocupado de um site de rede social local. Nenhum tempo é realmente "tempo de inatividade". Mas o menos ocupado é ~4:00 AM, então a janela que vou usar.

Falando por experiência, vocês podem formar alguma opinião sobre quanto tempo VACUUM FULL levaria para as 4 mesas que indiquei? Eu gostaria de colocar uma mensagem "em manutenção até xx:xx:xx" no site enquanto isso está acontecendo. Eu sei que ninguém pode ter certeza, mas isso é determinista o suficiente para você formar uma opinião aproximada?

Em segundo lugar, apenas para estarmos na mesma página, os comandos que eu estaria executando no psql são simplesmente VACUUM (FULL, VERBOSE, ANALYZE) link_publicreply;(e assim por diante), corretos? Não queira estragar tudo.

Em um aplicativo semelhante ao reddit (chamado Links) eu mantenho, os usuários postam hiperlinks e outros podem responder publicamente nos referidos links. O primeiro é salvo em uma tabela postgresql 9.3.10 chamada links_link, o último em uma tabela chamada links_publicreply.

Minha tarefa é:

1. Exclua todos os linkobjetos criados em uma janela de 30 dias.

2. Exclua todos os publicreplyobjetos associados a objetos em (1) e criados em uma janela de 45 dias.

Aqui está como eu fiz isso para uma janela de tempo de 2016:

begin;
  delete from links_publicreply where submitted_on >= timestamp'2016-07-23 01:01:00' and submitted_on < timestamp'2016-09-07 01:00:00' and answer_to_id in (select id from links_link where submitted_on >=timestamp'2016-07-23 01:00:00' and submitted_on < timestamp'2016-08-23 01:00:00');
  delete from links_link where submitted_on >= timestamp'2016-07-23 01:00:00' and submitted_on < timestamp'2016-08-23 01:00:00';
commit;

Observe que estou consultando links_linkexatamente da mesma maneira duas vezes . Ou seja, uma vez cada em ambas as deletedeclarações. Agora, no Django ORM (do qual sou nativo), eu teria otimizado isso primeiro obtendo todos os links_linkids de objeto necessários separadamente e, em seguida, usando-os em todas as instruções de procedimento. Como faço para otimizar isso na psqllinha de comando?

O conselho mais pertinente que vi está nesta resposta SO , mas parece que a withcláusula deve estar dentro da instrução SQL em que está sendo chamada.

Em outras palavras, o seguinte não funcionaria?

BEGIN;
  DELETE FROM links_publicreply
    WHERE submitted_on >= timestamp '2016-07-23 01:01:00'
        AND submitted_on < timestamp '2016-09-07 01:00:00'
        AND answer_to_id in (
            SELECT id
            FROM links_link
            WHERE submitted_on >= timestamp '2016-07-23 01:00:00'
                AND submitted_on < timestamp '2016-08-23 01:00:00'
    );
    DELETE FROM links_link
    WHERE submitted_on >= timestamp'2016-07-23 01:00:00'
        AND submitted_on < timestamp'2016-08-23 01:00:00';
COMMIT;

Eu entendo que o espaço não é liberado abruptamente (a menos que um faça TRUNCATE). Mas este parece-me anormal.

Qual poderia ser o motivo de uma tabela postgresql (9.3.10) não liberar espaço após ter 70% de seus registros removidos?

Eu tenho uma tabela chamada user_sessions_session. Como o nome indica, ele armazena dados de sessões para cada usuário de um aplicativo da web.

Seu tamanho original era:

              Table               |  Size   | External Size 
----------------------------------+---------+---------------
 user_sessions_session            | 15 GB   | 13 GB

Depois disso, excluí todas as sessões de usuário com mais de 3 meses atrás. Essa foi a maioria das linhas na tabela. Isso foi há 3 dias. Acabei de verificar o tamanho da tabela novamente, eis o que vejo:

              Table               |  Size   | External Size 
----------------------------------+---------+---------------
 user_sessions_session            | 15 GB   | 13 GB

Além disso, select * from pg_stat_activity where query like 'autovacuum:%';revela que nenhuma aspiração está acontecendo neste momento.

Btw eu tive esse mesmo problema com esta mesma tabela de sessões antes - não é um caso isolado.

Caso seja importante, aqui está o SQL que usei para obter os tamanhos das tabelas (user_sessions_session apareceu como número 1 na lista):

SELECT                                                         
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

Eu tenho um aplicativo do tipo reddit chamado 'Links', onde 1) os usuários postam hiperlinks interessantes e 2) outros usuários respondem publicamente a essas postagens. O primeiro é definido como links_linktabela no meu banco de dados postgresql 9.3.10, o último é links_publicreply.

Percebi que a exclusão é bastante rápida na maioria das tabelas no banco de dados deste aplicativo da web.

No entanto, links_publicreplyé problemático. Por exemplo, acabei de excluir 238 linhas dessa tabela e esperei cerca de 20 minutos para concluir. Isso não é uma exceção; tem sido mais como a regra há mais de um ano.

Agora, confessamente, é uma tabela grande (~ 75 milhões de linhas). O hardware em que está operando tem 16 núcleos e 120 GB de memória. Tenho monitorado o desempenho do servidor - não há gargalo, longe disso.

Veja os resultados da análise explicativa aqui: https://explain.depesz.com/s/ATwE Parece que o tempo é consumido por uma varredura sequencial.

Além disso, aqui está a saída de \d links_publicreply:

                                      Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers                            
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
 category        | character varying(20)    | not null
 seen            | boolean                  | not null
 abuse           | boolean                  | not null
 device          | character varying(10)    | default '1'::character varying
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

Existe alguma maneira que eu poderia ter realizado uma consulta como esta de forma diferente? Preciso realizar manutenção nesta mesa - mas os tempos são exorbitantes. Por favor, informe sobre todas as opções (se houver) que tenho aqui.

Caso seja importante, adicione também a saída para \d links_linkaqui:

                                         Table "public.links_link"
        Column        |           Type           |                        Modifiers                        
----------------------+--------------------------+---------------------------------------------------------
 id                   | integer                  | not null default nextval('links_link_id_seq'::regclass)
 description          | text                     | not null
 submitter_id         | integer                  | not null
 submitted_on         | timestamp with time zone | not null
 rank_score           | double precision         | not null
 url                  | character varying(250)   | not null
 cagtegory            | character varying(25)    | not null
 image_file           | character varying(100)   | 
 reply_count          | integer                  | default 0
 device               | character varying(10)    | default '1'::character varying
 latest_reply_id      | integer                  | 
 which_photostream_id | integer                  | 
 is_visible           | boolean                  | default true
 net_votes            | integer                  | default 0
Indexes:
    "links_link_pkey" PRIMARY KEY, btree (id)
    "links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
    "link_whichphotostreamid_fkey" FOREIGN KEY (which_photostream_id) REFERENCES links_photostream(id) ON UPDATE CASCADE ON DELETE CASCADE
    "links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_link_id_fkey" FOREIGN KEY (which_link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_vote" CONSTRAINT "links_vote_link_id_fkey" FOREIGN KEY (link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photoobjectsubscription" CONSTRAINT "which_link_id_photoobjectsubscription" FOREIGN KEY (which_link_id) REFERENCES links_link(id) ON DELETE CASCADE

Muito recentemente, mudei o data_directoryposgresql (9.3.10) para apontar para um novo disco de dados que adicionei recentemente à minha VM (Ubuntu 14.04 OS). Todos os dados antigos também foram transferidos para este novo disco.

Antes dessa mudança, o data_directoryestava residindo no disco que também tinha o sistema operacional instalado. Este foi um arranjo digerível inicialmente, mas no final data_directoryquase chegou a preencher todo o espaço de 30 GB disponível. Assim fiz a mudança.

Minha pergunta é: posso excluir o conteúdo da var/lib/postgresql/9.x/main/pasta antiga agora que data_directoryestá apontando inteiramente para um novo local?

Os arquivos antigos ainda estão intactos, ocupando muito espaço, e a única razão pela qual não os excluí é porque outros elementos na configuração do postgresql (ou alguns scripts daemon) podem estar se referindo a essa estrutura de pastas de alguma forma. Não tenho certeza. Alguém com experiência talvez possa ajudar aqui.

É possível definir o local de criação do arquivo temporário para um backend Postgresql (versão 9.3.10)?

Aqui está o meu cenário: meu banco de dados Postgresql reside em uma VM dedicada com o sistema operacional Ubuntu 14.04. Minha VM vem com 200 GB de armazenamento SSD temporário de alto desempenho fornecido pelo meu provedor de infraestrutura, destinado a armazenamento de curto prazo para aplicativos e processos. Veja como ele se parece:

Filesystem      Size  Used Avail Use% Mounted on
/dev/sdb1       221G  9.9G  200G   5% /mnt

Estou com pouco espaço em disco e preciso executar algumas consultas analíticas que podem me levar ao disco cheio devido à criação de arquivos temporários.

Além de tomar medidas como excluir entradas de log para liberar mais espaço ou configurar temp_file_limitno postgresql conf, também estou interessado em saber se posso definir o local para arquivos temporários. Afinal, tenho 220 GB disponíveis para esse tipo de cenário, então quero usá-los bem (o espaço de troca também é definido aqui).

Um exemplo ilustrativo de como posso configurar isso seria muito útil, pois sou um DBA neófito.

As consultas SELECT (com JOINs de tabela) em um banco de dados afetam o espaço em disco enquanto estão em execução?

Background: Eu tenho um aplicativo Django com um backend Postgresql (9.3.10). Meu banco de dados reside em uma VM que estava com pouco espaço em disco (cerca de ~400 MB restantes).

Consultei algumas tabelas para avaliar quais dados devem ser preteridos para liberar espaço em disco (isso inclui junções entre tabelas). Essas consultas do tipo analítico foram agrupadas em um único URL e executadas em uníssono. Quando eu bati o URL, a VM contendo meu banco de dados ficou sem espaço após cerca de meio minuto.

Eu sou um DBA acidental e ainda estou aprendendo as cordas. Alguém pode explicar por que fiquei sem espaço neste cenário? Algum tipo de arquivo temporário é criado em tais operações? Compartilharei meus detalhes de configuração caso sejam necessários.

Eu tenho um aplicativo da web Django com backend postgresql 9.3.10 (sentado em um sistema operacional Linux). Eu encontrei um erro de disco cheio, de modo que, mesmo se eu tentar truncar uma tabela, recebo erros do tipo:

ERROR:  could not extend file "base/30137/33186048": No space left on device
HINT:  Check free disk space.

Não consigo adicionar facilmente mais espaço em disco ao servidor, nem posso excluir coisas nesta VM. No entanto, existem várias tabelas que são candidatas ao truncamento, mas parece que também não posso truncá-las agora.

Alguém pode me dar uma dica do que posso fazer aqui? Isso está afetando muito meu servidor de produção, e eu sou um DBA acidental aqui, totalmente perplexo.

Background: Um pouco de um DBA acidental aqui. Eu tenho um site semelhante ao reddit onde os usuários enviam links apontando para vários conteúdos da Internet e podem deixar comentários em cada postagem. Este aplicativo - vamos chamá-lo de Links - tem duas tabelas correspondentes para armazenar dados: linke publicreply(ou seja, comentários associados a cada link).

Problema: não consigo excluir registros (para manutenção) dessas duas tabelas devido a restrições FK interdependentes. Precisa de orientação para resolver a situação.

Detalhes: Cada Publicreplyobjeto armazena um FK para o Linkobjeto ao qual está associado. Além disso, cada Linkobjeto também salva uma referência à última resposta pública associada a ele. Isso cria uma situação em que todos os Publicreplyobjetos têm um LinkFK e vice-versa. Como em:

Table "public.links_link"
        Column        |           Type           |                        Modifiers                        
----------------------+--------------------------+---------------------------------------------------------
 id                   | integer                  | not null default nextval('links_link_id_seq'::regclass)
 description          | text                     | not null
 submitter_id         | integer                  | not null
 submitted_on         | timestamp with time zone | not null
 url                  | character varying(250)   | not null
 image_file           | character varying(100)   | 
 reply_count          | integer                  | default 0
 latest_reply_id      | integer                  | 
 is_visible           | boolean                  | default true
Indexes:
    "links_link_pkey" PRIMARY KEY, btree (id)
    "links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
    "links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED

 Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers                            
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

Como excluo registros da tabela Linknesse cenário? E de Publicreply? Estou usando o postgresql 9.3.10.

Mais de uma semana atrás, apaguei todas as linhas em uma tabela postgresql (não via truncate, em vez de delete from ...). Select count (*)revela que as linhas da tabela agora são 0.

Agora, quando executo consultas para consultar o espaço em disco, ainda vejo a tabela ocupando espaço. Especificamente, todos os índices ainda existem e estão ocupando espaço. Como faço para me livrar deles e liberar espaço em disco? Em segundo lugar, por que eles permanecem em primeiro lugar, quando me livrei de todas as linhas?

Aqui está a descrição detalhada da tabela em caso justificado:

                                      Table "public.links_photoobjectsubscription"
     Column     |           Type           |                                 Modifiers                                  
----------------+--------------------------+----------------------------------------------------------------------------
 id             | integer                  | not null default nextval('links_photoobjectsubscription_id_seq'::regclass)
 viewer_id      | integer                  | not null
 updated_at     | timestamp with time zone | not null
 seen           | boolean                  | not null
 type_of_object | character varying(15)    | not null
 which_photo_id | integer                  | 
 which_link_id  | integer                  | 
 which_group_id | integer                  | 
 which_salat_id | integer                  | 
Indexes:
    "links_photoobjectsubscription_pkey" PRIMARY KEY, btree (id)
    "links_photoobjectsubscription_seen" btree (seen)
    "links_photoobjectsubscription_updated_at" btree (updated_at)
    "links_photoobjectsubscription_viewer_id" btree (viewer_id)
    "links_photoobjectsubscription_which_photo_id" btree (which_photo_id)
Foreign-key constraints:
    "links_photoobjectsubscription_viewer_id_fkey" FOREIGN KEY (viewer_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "links_photoobjectsubscription_which_photo_id_fkey" FOREIGN KEY (which_photo_id) REFERENCES links_photo(id) DEFERRABLE INITIALLY DEFERRED
    "which_group_id_photoobjectsubscription" FOREIGN KEY (which_group_id) REFERENCES links_group(id) ON DELETE CASCADE
    "which_link_id_photoobjectsubscription" FOREIGN KEY (which_link_id) REFERENCES links_link(id) ON DELETE CASCADE
    "which_salat_id_photoobjectsubscription" FOREIGN KEY (which_salat_id) REFERENCES links_salatinvite(id) ON DELETE CASCADE

Preciso descobrir todos os usuários que se registraram no meu site com suporte Postgresql 9.3 na janela de 24 horas há 2 dias . Atualmente, estou fazendo isso executando as seguintes consultas e subtraindo manualmente a diferença:

select count (*) from auth_user where date_joined > now() - interval'24 hours';
select count (*) from auth_user where date_joined > now() - interval'48 hours';

Como faço tudo na mesma consulta SQL, inclusive a subtração? Desde já, obrigado!

Se eu fizer isso select count (*) from auth_user where date_joined > (now() - interval'48 hours') - (now() - interval'24 hours');, eu recebo:

Nenhum operador corresponde ao(s) nome(s) e tipo(s) de argumento fornecidos. Pode ser necessário adicionar conversões de tipo explícito.