Re: postgres sometimes returns no data

On Fri, Nov 13, 2015 at 6:49 AM, db042190 <stanteitelbaum(at)roadrunner(dot)com>
wrote:

> Hi. We have a postgres 9.1 query in a pentaho job (table input component
> contains the query and is followed by various switches) that runs nightly.
> More and more frequently (about half the time now), the query returns no
> data (or appears to return no data, see last paragraph) when there is
> clearly data to be extracted. And pentaho continues as if nothing is
> wrong.
> Whenever I simply rerun the component manually, the query returns data and
> I
> am able to complete the job.
>
> I'm looking at what I think is a postgres log around one of the times this
> last happened. And trying to correlate it to the time I see in the pentaho
> job's log when this component starts up.
>
> Around that time I just see a bunch of "checkpoints are occurring too
> frequently...". About a minute later and coincidentally around the time
> the
> pentaho job ends, I see "unexpected eof...could not receive data..target
> machine actively refused it.". The reason this last item interests me is
> that this component is the last postgres item to run in the job. And the
> next/last component wouldn't run long when there is an empty file created
> by
> the problematic component. The file is empty whenever this behavior
> occurs.
>

When checkpoints are occurring too frequently, then, it is important that
you tune the checkpoint parameters to ensure optimal checkpoint behavior.
Do you see any IO spike on the postgres server ?

As the above message says "could not receive data" ...

I suspect, the query is returning the rows at the postgres end without any
issues. Did you execute the query on the Postgres database and see if that
is returning rows ? if yes, then it is not a query related issue, it has
something to do with the performance. Do you notice any other activities on
the database when this query is running which are possibly generating High
CPU / High IO which can block or abort the application connections ?

I'm torn not knowing if this is a postgres issue or pentaho issue. The fact
> that the pentaho log doesn't show the switches as having "finished
> processing" makes me think either pentaho wouldnt execute those switches
> when there is no data or pentaho is the problem. The fact that its
> happening more often makes me think the db is somehow involved in the
> problem.
>
> I did a reindex and vacuum recently hoping it would alleviate the problem.
> Not so. Any advice would be appreciated.
>

The first thing to do is to tune the checkpoint parameters.

Regards,
Venkata B N

Fujitsu Australia