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

[Luc Germain]

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