Re: Lots of stuck queries after upgrade to 9.4

Well, so far with commit_delay=0 no problems. I will report back of couse
if something happens, but I believe that the problem may indeed be
solved/masked with that setting.

Rough description of our setup, or how to reproduce:
* Timeseries data in table , say, "measurements", size: 3-4TB, about 1000
inserts/second
* table measurements also has a trigger on insert to also insert on
measurements_a (for daily export purposes)
Just the above would cause a stuck query after a few days.

Now for exporting we run the following CTE query (measurements_b is an
empty table, measurements_a has about 5GB)
* WITH d_rows AS (DELETE FROM measurement_events_a RETURNING * ) INSERT
INTO measurement_events_b SELECT * FROM d_rows;
The above caused the problem to appear every time, after a 10-20 minutes.

Regards,
-Spiros

*Spiros Ioannou IT Manager, inAccesswww.inaccess.com
<http://www.inaccess.com>M: +30 6973-903808T: +30 210-6802-358*

On 20 July 2015 at 17:02, Andres Freund <andres(at)anarazel(dot)de> wrote:

> On 2015-07-20 17:00:52 +0300, Spiros Ioannou wrote:
> > FYI we have an 9.3.5 with commit_delay = 4000 and commit_siblings = 5
> with
> > a 8TB dataset which seems fine. (Runs on different - faster hardware
> > though).
>
> 9.4 has a different xlog insertion algorithm (scaling much better), so
> that unfortunately doesn't say very much...
>