On Thu, Feb 16, 2017 at 3:27 PM, David Hinkle wrote:
> I guess this doesn't work, latest test run crashed. It still uses the
> bad plan for the hostid column even after n_distinct is updated.
>
> cipafilter=# select attname, n_distinct from pg_stats where tablename
> cipafilter-# = 'log_raw' and
I guess this doesn't work, latest test run crashed. It still uses the
bad plan for the hostid column even after n_distinct is updated.
cipafilter=# select attname, n_distinct from pg_stats where tablename
cipafilter-# = 'log_raw' and (attname = 'urlid' or attname = 'titleid'
or attname =
cipafilt
Yep, 420ish million records out of 540 million records have a titleid
of 1. There are about 880,000 other unique values, but most of the
records are 1. Of course, n_distinct is only 292. I'm surprised
it's not eliminating the duplicates while it builds that hash table.
This is what I'm doing
David Hinkle writes:
> Tom, there are three columns in this table that exhibit the problem,
> here is the statistics data after an analyze, and the real data to
> compare it to.
> attname | n_distinct | most_common_freqs
> titleid |292 | {0.767167}
Ouch. That's saying there's some s
Maybe also useful to know:
cipafilter=# select reltuples from pg_class where relname = 'log_raw';
reltuples
-
5.40531e+08
(1 row)
On Wed, Feb 15, 2017 at 7:55 PM, David Hinkle wrote:
> Thanks for your help!
>
> Karsten: The system does fill up swap before it blows up. This
> part
Thanks for your help!
Karsten: The system does fill up swap before it blows up. This
particular model has 8G of ram and 4G of swap and runs kernel
4.4.0-53-generic #74~14.04.1-Ubuntu.
Tom, there are three columns in this table that exhibit the problem,
here is the statistics data after an analyz
On Wed, Feb 15, 2017 at 01:04:51PM +0100, Karsten Hilbert wrote:
> > Nope, that pops too. The query runs for a long time at a somewhat
> > normal rate of ram consumption, using ~1G of RSS then suddenly spikes
> > to about 6G, at which point the OOM killer pops it. Box has 8G of ram
> > and 4G of
On Mon, Feb 13, 2017 at 03:47:08PM -0600, David Hinkle wrote:
> Nope, that pops too. The query runs for a long time at a somewhat
> normal rate of ram consumption, using ~1G of RSS then suddenly spikes
> to about 6G, at which point the OOM killer pops it. Box has 8G of ram
> and 4G of swap.
By
I wrote:
> David Hinkle writes:
>> Thanks guys, here's the information you requested:
>> psql:postgres@cipafilter = show work_mem;
>> work_mem
>> ──
>> 10MB
>> (1 row)
> [ squint... ] It should absolutely not have tried to hash a 500M-row
> table if it thought work_mem was only 10MB. I
David Hinkle writes:
> Thanks guys, here's the information you requested:
> psql:postgres@cipafilter = show work_mem;
> work_mem
> ──
> 10MB
> (1 row)
[ squint... ] It should absolutely not have tried to hash a 500M-row
table if it thought work_mem was only 10MB. I wonder if there's a
I manually updated the pg_statistics data by literally set it to an
appropriate amount, and the planner picked a new plan and the new plan
worked. Any idea what I should do about this? Is manually updating
these values my best bet?
psql:daemon@cipafilter = update pg_statistic set stadistinct = 8
I managed to get this version to finish:
psql:postgres@cipafilter = explain (ANALYZE, BUFFERS) select count(*)
from (select titleid from log_raw group by titleid) as a;
QUERY PLAN
───
psql:postgres@cipafilter = EXPLAIN (ANALYZE, BUFFERS) select titleid
from titles WHERE NOT EXISTS ( SELECT 1 FROM log_raw WHERE
log_raw.titleid = titles.titleid );
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processi
On Mon, Feb 13, 2017 at 12:43 PM, David Hinkle
wrote:
> Thanks Jeff,
>
> No triggers or foreign key constrains:
>
> psql:postgres@cipafilter = \d+ titles
> Table "public.titles"
> Column │ Type│Modifiers
Thanks Jeff,
No triggers or foreign key constrains:
psql:postgres@cipafilter = \d+ titles
Table "public.titles"
Column │ Type│Modifiers
│ Storage │ Stats target │ Description
─┼─
On Mon, Feb 13, 2017 at 11:53 AM, David Hinkle
wrote:
> Thanks guys, here's the information you requested:
>
> psql:postgres@cipafilter = show work_mem;
> work_mem
> ──
> 10MB
> (1 row)
>
OK, new theory then. Do you have triggers on or foreign key constraints to
the table you are del
Thanks guys, here's the information you requested:
psql:postgres@cipafilter = show work_mem;
work_mem
──
10MB
(1 row)
psql:postgres@cipafilter = select version();
version
On Mon, Feb 13, 2017 at 9:40 AM, David Hinkle wrote:
> I'm having trouble with purges related to a large table. The delete
> query consumes ram until postgres crashes due to OOM. I have a very
> large table called log_raw. There are half a dozen related tables,
> such as 'urls' and 'titles'.
Hi
2017-02-13 18:40 GMT+01:00 David Hinkle :
> I'm having trouble with purges related to a large table. The delete
> query consumes ram until postgres crashes due to OOM. I have a very
> large table called log_raw. There are half a dozen related tables,
> such as 'urls' and 'titles'. log_r
I'm having trouble with purges related to a large table. The delete
query consumes ram until postgres crashes due to OOM. I have a very
large table called log_raw. There are half a dozen related tables,
such as 'urls' and 'titles'. log_raw.urlid = urls.urlid and urls
contains the text of the
20 matches
Mail list logo
