[ckan-dev] Large and slow CKAN instance

[Ian Ward]

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