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

[Robert Atkinson]

You are using mysql? On what operating system?

Thanks

On 9/8/05, Luc Germain <Luc.Germain at usherbrooke.ca> wrote:
> 
> 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
> >
> >
> 
> 
> 
> --
> 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
>