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

[Robert Atkinson]

More exactly what versions of each.. mysql and os

I personally use mysql 4+ and freebsd 4.X/5.X of which both versions of bsd 
don't perform nearly as well that others see with linux.



On 9/8/05, Robert Atkinson <phreaki at gmail.com> wrote:
> 
> 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
> > 
> 
>