From: Carlo Stonebanks <stonec.register@sympatico.ca>
To: 'Jeff Janes' <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Are bitmap index scans slow to start?
Date: Tue, 26 Feb 2013 19:33:13 -0500
Message-ID: <01a601ce1482$07142db0$153c8910$@sympatico.ca> (raw)
In-Reply-To: <CAMkU=1wc1uMsx5viu6NHU=LFn3q_AjwUWE0y6vuCN=eYKZ+eAg@mail.gmail.com>
References: <005701ce0fb5$6f841f30$4e8c5d90$@sympatico.ca>
<001501ce1054$7b518380$71f48a80$@sympatico.ca>
<CAMkU=1yG884Nak4zPoNHwDGpC7w2nrKH6_xztPw4b7P9am0wdg@mail.gmail.com>
<B6F6FD62F2624C4C9916AC0175D56D8801B17F@jenmbs01.ad.intershop.net>
<006201ce1125$2c554150$84ffc3f0$@sympatico.ca>
<CAPmjWd1On_uAp6suqYoHuh3ksKD0xn6tOhOM_Z2vaweO-vexSA@mail.gmail.com>
<009001ce1137$f9c31bf0$ed4953d0$@sympatico.ca>
<CAMkU=1z-UOitt2mtnjn=XQ8kk2cL2nAnd2MfycLwju__qC49wQ@mail.gmail.com>
<010601ce137a$266b7f00$73427d00$@sympatico.ca>
<CAMkU=1wc1uMsx5viu6NHU=LFn3q_AjwUWE0y6vuCN=eYKZ+eAg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
<<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.
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.
<< 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"? 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stonec.register@sympatico.ca, jeff.janes@gmail.com
Subject: Re: Are bitmap index scans slow to start?
In-Reply-To: <01a601ce1482$07142db0$153c8910$@sympatico.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox