[horde] Database performance problem with Horde3/IMP4 with large user base

[Luc Germain]

Hi again,

Concerning the database performance problem reported last week for horde
v3 with a large user base, I did some simulations on our database, and
I can confirm that the requests that generate a heavy load on the
database server are those that evaluate permissions on the datatree
table, like this one:

SELECT c.datatree_id, c.datatree_name FROM horde_datatree c LEFT JOIN
horde_datatree_attributes a1 ON a1.datatree_id = c.datatree_id WHERE
c.group_uid = 'horde.shares.nag' AND ((a1.attribute_name = 'owner' AND
a1.attribute_value = 'myuserid') OR (a1.attribute_name = 'perm_users'
AND a1.attribute_key = 'myuserid' AND a1.attribute_value &
2) OR (a1.attribute_name = 'perm_creator' AND a1.attribute_value & 2) OR
(a1.attribute_name = 'perm_default' AND a1.attribute_value & 2))
GROUP BY c.datatree_id, c.datatree_name, c.datatree_order ORDER BY
c.datatree_order, c.datatree_name, c.datatree_id

The problem seems to come from the fact that the database engine must
evaluate a really great number of things like "a1.attribute_value & 2"
for each of these requests. As for tuning the server cache, there is
not much that can be done since the datatree table is modified quite
frequently and cached results are flushed each time the table is
written to.

So, at this point, I think there's nothing I can do to solve this
performance problem on the infrastructure side.

Apart from redesigning the database, the only way I see to support lots
of concurrent users would be to cache the results of these permissions
evaluation requests in the user session for some configurable time. Is
it a solution that would be acceptable to horde developers? Would it be
easy to implement?

By the way, are there installations of horde v3 out there supporting
many thousands users without problem?

Thanks!!

Luc.
--
Luc Germain
Université de Sherbrooke, Canada
email: Luc.Germain at USherbrooke.ca

Selon Luc Germain <Luc.Germain at USherbrooke.ca>:

> Hi,
>
> We have about 20000 users on our mail servers, and maybe 10000 that
use
> horde as their daily mail interface.
>
> We have been running horde v2 for a couple of years with good
> performance even under heavy usage. This week we tried to migrate to
> Horde 3. After about 500 users have logged in, the database server
load
> increased to about 15 and the response time became VERY slow.
>
> The mysql database is on a dedicated server with 1GB memory. To try to
> reduce the load on the db, we moved the session_handler table on
> another mysql server, but the load on the main horde db server didn't
> change, so the problem was not with the session handling. We then
tried
> to move the database on another server with 2GB memory, and tuned it
> with the "my-huge.cnf" configuration file. Load lowered to about 7,
but
> it was still too much slow. So we went back to v2 for now :(
>
> I looked at the requests that a typical v3 session sends to the
> database, and I spotted these requests that are sent each time an
> application wants to display data (it gets the permissions of the user
> on the shared objects):
>
> SELECT c.datatree_id, c.datatree_name FROM horde_datatree c LEFT JOIN
> horde_datatree_attributes a1 ON a1.datatree_id = c.datatree_id WHERE
> c.group_uid = 'horde.shares.nag' AND ((a1.attribute_name = 'owner' AND
> a1.attribute_value = 'myuserid') OR (a1.attribute_name = 'perm_users'
> AND a1.attribute_key = 'myuserid' AND a1.attribute_value &
> 2) OR (a1.attribute_name = 'perm_creator' AND a1.attribute_value & 2)
OR
> (a1.attribute_name = 'perm_default' AND a1.attribute_value & 2))
> GROUP BY c.datatree_id, c.datatree_name, c.datatree_order ORDER BY
> c.datatree_order, c.datatree_name, c.datatree_id
>
> The problem with these requests is that mysql needs to use a temporary
> table and scan a number of row proportionnal to the number of entries
> for this application (which is on the order of the number of users).
> Here is the Explain result for this request on our database:
>
> | select_type | table | type | rows | Extra
> | SIMPLE      | c     |  ref | 4537 | Using where; Using temporary;
>                                      Using filesort
> | SIMPLE      | a1    |  ref |    3 | Using where
>
> On a fully configured summary page (the default on our installation),
> this kind of request is done up to 4 times for each page refresh. Each
> request takes on the order of .2 sec the first time it is done by a
> user (this is on an idle mysql server). If there are enough users on
> line, the results won't fit in cache and the database server will
likely
> begin to choke on these requests.
>
> So, has anyone found a way to optimize the database so these requests
> are done more efficiently for many concurent users?
>
> If not, it might be a good idea to cache these results in the session
> for some configurable time (30min, 1hour?)? Would it be easy to
> implement?
>
> Are there other requests that could explain why the database server
> could not keep up?
>
> Thanks for any help! We would really like to update to horde v3 soon!
>
> Luc.
> --
> Luc Germain
> Université de Sherbrooke, Canada
> email: Luc.Germain at USherbrooke.ca
>
>
> --
> Horde mailing list - Join the hunt: http://horde.org/bounties/#horde
> Frequently Asked Questions: http://horde.org/faq/
> To unsubscribe, mail: horde-unsubscribe at lists.horde.org
>
>