Re: postgres sometimes returns no data - Mailing list pgsql-general
| From | Venkata Balaji N |
|---|---|
| Subject | Re: postgres sometimes returns no data |
| Date | |
| Msg-id | CAEyp7J8t18sQKkF6_NQqNzahHbvncmhaz2YsbgqQEeW6Yvn1mw@mail.gmail.com Whole thread Raw |
| In response to | postgres sometimes returns no data (db042190 <stanteitelbaum@roadrunner.com>) |
| Responses |
Re: postgres sometimes returns no data
(db042190 <stanteitelbaum@roadrunner.com>)
Re: postgres sometimes returns no data (John R Pierce <pierce@hogranch.com>) |
| List | pgsql-general |
On Fri, Nov 13, 2015 at 6:49 AM, db042190 <stanteitelbaum@roadrunner.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
pgsql-general by date: