Re: Are bitmap index scans slow to start?

On Tue, Feb 26, 2013 at 4:33 PM, Carlo Stonebanks <
stonec(dot)register(at)sympatico(dot)ca> wrote:

> <<Is each of these write operations just covering a single row? Does this
> description apply to just one of the many (how many?) databases, so that
> there are really 14*N concurrent sessions?****
>
> >>** **
>
> ** **
>
> All writes are single row. All DB’s have exactly the same structure, only
> the content is different. Currently the server is hosting five active DB’s
> – although there 14 DB’s actually on the host, the balance are backups and
> or testing environments.
>

I had thought you were saying that any one ETL procedure into one database
used 14 concurrent threads. But really, each ETL procedure is
single-threaded, and there can be up to 5 (or theoretically up to 14) of
them running at a time into different databases?

> When a feed comes in, it can be anything from dozens to millions of rows,
> and may take minutes or days to run. I had asked that PG bouncer be
> installed in front of the host to act as a traffic cop. Try as I may to
> convince the engineering team that fewer sessions running faster is
> optimal, they say that the 14 concurrent sessions is based on real-world
> experience of what imports the fastest.
>

pgbouncer is more for making connections line up single-file when the line
is moving at a very fast clip, say 0.01 second per turn. If I were trying
to make tasks that can each last for hours or days line up and take turns,
I don't think pgbouncer would be the way to go.

> ****
>
> ** **
>
> << You really need to know whether those reads and writes are
> concentrated in a small region (relative to the amount of your RAM), or
> widely scattered. If you are reading and writing intensively (which you do
> seem to be doing) but only within a compact region, then it should not
> drive other data out of the cache. But, since you do seem to have IO
> problems from cache misses, and you do have a high level of activity, the
> easy conclusion is that you have too little RAM to hold the working size of
> your data.
> >>****
>
> ** **
>
> It won’t be a problem of physical RAM, I believe there is at least 32GB of
> RAM. What constitutes “a compact region”?
>

If you have 14 actively going on simultaneously, I'd say a compact region
would then be about 512 MB.
(32GB/ 14 / margin of safety of 4). Again, assuming that that is the
problem.

> The ETL process takes the feed and distributes it to 85 core tables. I
> have been through many PG configuration cycles with the generous help of
> people in this forum. I think the big problem when getting help has been
> this issue of those offering assistance understanding that the whopping
> majority of the time, the system is performing single row reads and writes.
> The assumption tends to be that the end point of an ETL should just be a
> series of COPY statements, and it should all happen very quickly in classic
> SQL bulk queries.****
>

That is often a reasonable assumption, as ETL does end with L :)

Is the original query you posted part of the transform process, rather than
being the production query you run after the ETL is over?

If so, maybe you need a EL(S)TL process, were you first load the data to
staging table in bulk, and then transform it in bulk rather than one row at
a time.

Cheers,

Jeff