fsync is off and full_page_writes is off because the script works one time. We create the db, we load the data, then we dump the data and kill the db.
No need to handle servers crashed or anything like that.
0.1 vacuum_cleanup_index_scale_factor is the default value.
No need to handle servers crashed or anything like that.
0.1 vacuum_cleanup_index_scale_factor is the default value.
On Thu, Jul 8, 2021 at 4:06 PM Justin Pryzby <pryzby@xxxxxxxxxxxxx> wrote:
On Thu, Jul 08, 2021 at 03:49:12PM +0200, Allan Barrielle wrote:
> > Is it true that the SELECTs have no joins in them ?
>
> Yes there is a lot of LEFT JOIN.
>
> > It'd be very useful to get "explain analyze" for a working query and for a
> > stuck query. It sound like the stuck query never finishes, so maybe the second
> > part is impossible (?)
>
> We run an explain analysis and we see some very interesting stuff going on.
> It seems without explicitly adding a `ANALYZE`, the query has a cost of
> over billions, so the query is not stuck but took forever.
> When I run the same scripts with an ANALYZE right before running the query,
> the query is exec is 50secondes and the cost is normal
It sounds like sometimes autoanalyze processes important tables being queried,
but sometimes it doesn't.
Since there are JOINs involved, you should analyze the tables after populating
them and before querying them. The same as if it were a temp table, or
anything else.
> The configuration is tuned by aws aurora, [...]
> fsync,off
> full_page_writes,off
really?
> vacuum_cleanup_index_scale_factor,0.1
also interesting
