Investigating Kallithea performance issues

[Jan Heylen]

On Wed, Mar 29, 2017 at 3:25 AM, Mads Kiilerich <mads at kiilerich.com> wrote:
> On 03/23/2017 06:26 AM, Jan Heylen wrote:
>>>
>>> What I do see as queries running long on the database are these, (But I
>>> haven't correlated these directly with the above peeks yet (but it could
>>> well be).)
>>> 47362  kallithea            kallithea        127.0.0.1    0.0  0.0
>>> 0.00B    0.00B  00:47.10  N    N  SELECT cache_invalidation.cache_id AS
>>> cache_invalidation_cache_id, cache_invalidation.cache_key AS
>>> cache_invalidation_cache_key, cache_invalidation.cache_args AS
>>> cache_invalidation_cache_args, cache_invalidation.cache_active AS
>>> cache_invalidation_cache_active FROM cache_invalidation WHERE
>>> cache_invalidation.cache_key = 'devws048-32241review/ms/sw-review'
>>>
>>> As you see, that query runs for 47 seconds at the moment I catch it.
>
>
>
> How fast is it when you rerun the query?
>
> I doubt any other queries or updates could slow it down ... unless
> cache_invalidation has a lot of entries.

And what fills it? As far as I understand more reviews? Or is it
(also) more comments? Or more changesets in the repos?
I'm asking to understand which table we would need to 'clean up'. If any.

>
> Perhaps try rerunning it regularly and see if the execution time varies?
>
>>> As far as I understand it now, these queries are the result of updated
>>> repositories from 'outside' kallithea and the accompanied repo-'cache
>>> invalidation' trigger.
>>> If this is really hitting us that would mean:
>>> * requests to kallithea webgui have to wait until these queries are over?
>>> * we need to find a way to have these repos updated without the need
>>> of the cache invalidation, so instead of doing an 'outside pull',
>>> would a 'push to kallithea' prevent the need of invalidating the
>>> cache?
>
>
> No, pushes are also invalidating the cache. The problem is that it should
> take that much time. But also: How do you invalidate cache after external
> pushes?

We're using the invalidate cache per repo api, so that should indeed
be the same.

Given that we cannot prevent it probably, we're also moving our repos
to a faster disk (ssd), and we'll take it from there probably.
For the rest of the investigation in the kallithea code, we will have
to move to the default or stable head first, we're way behind at this
moment :-s

>
> /Mads

best regards,

Jan