Issue #296: Slow Performance with a lot of Repos (conservancy/kallithea)

[Marcus Marcus]

New issue 296: Slow Performance with a lot of Repos
https://bitbucket.org/conservancy/kallithea/issues/296/slow-performance-with-a-lot-of-repos

Marcus Marcus:

Hi,

we have Version 0.3.3 (and 0.3.99) with LDAP Authentification and MySQL.

If you try to open the login-page it takes up to 8 Seconds.

You can reproduce it, if you create a lot of repos, run with only one (we have 20) kallithea process and wait for the login page.
Also try to clone a repo. You will see the delay.

We analyze the problem and see that Kallithea is always reading all the repos.
If the login-page is requested it makes a request for user "none" (or default) to the database. The result is (in our case), that the db gives back all the 4000 repos we have, because the default User has the permission "none" in all these repositories.

```
#!python


SELECT
         repo_to_perm.repo_to_perm_id AS repo_to_perm_repo_to_perm_id,
         repo_to_perm.user_id AS repo_to_perm_user_id,
         repo_to_perm.permission_id AS repo_to_perm_permission_id,
         repo_to_perm.repository_id AS repo_to_perm_repository_id,
         repositories.user_id AS repositories_user_id,
         repositories.statistics AS repositories_statistics,
         repositories.downloads AS repositories_downloads,
         repositories.landing_revision AS repositories_landing_revision,
         repositories.locked AS repositories_locked,
         repositories.changeset_cache AS repositories_changeset_cache,
         repositories.repo_id AS repositories_repo_id,
         repositories.repo_name AS repositories_repo_name,
         repositories.repo_state AS repositories_repo_state,
         repositories.clone_uri AS repositories_clone_uri,
         repositories.repo_type AS repositories_repo_type,
         repositories.private AS repositories_private,
         repositories.description AS repositories_description,
         repositories.created_on AS repositories_created_on,
         repositories.updated_on AS repositories_updated_on,
         repositories.enable_locking AS repositories_enable_locking,
         repositories.fork_id AS repositories_fork_id,
         repositories.group_id AS repositories_group_id,
         permissions.permission_id AS permissions_permission_id,
         permissions.permission_name AS permissions_permission_name
        FROM repo_to_perm
        INNER JOIN repositories ON repo_to_perm.repository_id = repositories.repo_id
        INNER JOIN permissions ON repo_to_perm.permission_id = permissions.permission_id
        WHERE repo_to_perm.user_id = 1;

```

This SQL-select will also called by every hg clone, Push, Pull etc, so every hg-Transaction will have a delay for answering the request.

To fix this we acitvated the Cache inside the auth.py file:


```
#!python

LazyProperty
    def permissions(self):
        return self.__get_perms(user=self, cache=False)
to
return self.__get_perms(user=self, cache=True)

```

Is there a reason why this cache was deactivated? (I know if you make changes to the permissions it takes the cache-refresh-time so the permissions takes effekt)
If you use this cache, the login page came back in 0.2 seconds.


We also try to fix the SQL-Statement to come with a faster result:

Change inside the kallithea/kallithea/model/db.py 

```
#!python


classmethod def get_default_perms(cls, default_user_id): q = Session().query(UserRepoToPerm, Repository, cls) \ .join((Repository, UserRepoToPerm.repository_id == Repository.repo_id)) \ .join((cls, UserRepoToPerm.permission_id == cls.permission_id)) \ .filter(UserRepoToPerm.user_id == default_user_id ```

to 

classmethod def get_default_perms(cls, default_user_id): q = Session().query(UserRepoToPerm, Repository, cls) \ .join((Repository, UserRepoToPerm.repository_id == Repository.repo_id)) \ .join((cls, UserRepoToPerm.permission_id == cls.permission_id)) \ .filter(UserRepoToPerm.user_id == default_user_id)\ .filter(cls.permission_id<>1)

```

The Webgui then works fine, but an hg clone refuses with an Permission denied.

We are thinking about an own cache-region only for the repositories and an flush Routine, if you update the Permissions in Kallithea. 
What do you think?

Regards
Marcus