[ckan-dev] Large and slow CKAN instance

[Alex (Maxious) Sadleir]

Also to look out for in 2.2.0 are some issues with large organisations
that are harder to track down with postgres slowlog because it's
thousands of unnecessary small queries in a row
https://github.com/ckan/ckan/pull/1978
https://github.com/ckan/ckan/pull/1208

These two PRs got backported to 2.2.1, right? I can see the tag on one
but not the other.

On Tue, Oct 28, 2014 at 4:25 AM, Ian Ward <ian at excess.org> wrote:
> On Mon, Oct 27, 2014 at 11:43 AM, Milica Knezevic
> <knezevic.milica at gmail.com> wrote:
>> I have a CKAN instance (2.2) with ~90,000 datasets. Most of these datasets
>> (~80,000) belong to one organization. With an increase of number of
>> datasets, everything becomes utterly slow. What happens is that postgres
>> becomes very busy for a long period of time. I set up db logging (queries
>> that takes more than 2 seconds to execute are logged) and it seems that this
>> query is executed each time I try to access, well, anything, even the About
>> page. It takes more than 7 seconds to execute and problematic are external
>> sorts and merge. I don't think I should increase Postgres work_mem that
>> much... Here's the logged query and the result of explain analyze. If anyone
>> has any suggestion it would be great.
>
> Hello Milica,
>
> Thank you for the investigation you've done so far. Please create a
> new issue on github for this slow query, and include the contents of
> this message.
>
> This looks like at least two real problems: an inefficient activities
> query that's being called even when not necessary. Tuning your
> postgres settings as most of us have done would probably just hide it.
>
> Thanks!
> Ian
> _______________________________________________
> ckan-dev mailing list
> ckan-dev at lists.okfn.org
> https://lists.okfn.org/mailman/listinfo/ckan-dev
> Unsubscribe: https://lists.okfn.org/mailman/options/ckan-dev