From: | Mark Steben <mark(dot)steben(at)drivedominion(dot)com> |
---|---|
To: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | longrunning query - could it be buffer - hit? |
Date: | 2019-06-29 21:03:38 |
Message-ID: | CADyzmyzcp1506zRcbM8ePcSyYvB-K30SMArXczSaLzTuH6u6sw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Good morning,
We run postgres 9.4. We have a query that is running for more than 6
hours. I have downloaded the query, the explain analyze with buffers, the
sizes of the principle tables (june29. tocommunity.op) the table
descriptions of the principle tables, the postgresql.conf file, and the
plpgsql function where the bottleneck is occurring (number_of_sends.plpgsql)
One observation I would like specific comments for: the buffers: shared hit
on the explain shows 532 million hit buffers - could that be a clue to the
6 hour query? Why so many? The largest table (queuenodes) is only
6,500,000 pages. the next largest - emailrcpts is only 3,700,000 pages If
this is a valid observation, is there a memory setting I could use to
alleviate that? Or am I interpreting wrong. Another insight: when I run
this query again, with cache benefit, the query runs in less than a minute
and the buffers:shared hit rises to only 232,000.
Any observation, and / or explanation of the 'buffers' parameter of the
explain would be appreciated.
--
*Mark Steben*
Database Administrator
@utoRevenue <http://www.autorevenue.com/> | Autobase
<http://www.autobase.net/>
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com <http://www.autorevenue.com/>
Attachment | Content-Type | Size |
---|---|---|
qaq.june28.2nd.query.expl.op | application/octet-stream | 13.0 KB |
tabledescriptions.op | application/octet-stream | 15.2 KB |
postgresql.conf.cpy | application/octet-stream | 23.9 KB |
number_of_sends.plpgsql | application/octet-stream | 815 bytes |
june29.tocommunity.op | application/octet-stream | 2.7 KB |
qaq.june28.2nd.query | application/octet-stream | 2.7 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Steben | 2019-07-01 11:43:35 | question on a symbol next to my post in pgsql_admin |
Previous Message | Massimo Penengo | 2019-06-29 08:06:08 | pgadmin 4 no backup/restore detail box |