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

Thu Sep 8 08:45:31 PDT 2005

We use mysql MySQL 4.1.10a on a Xeon 3GHz w/ 2GB memory running RedHat
Enterprise 4.

On this server (doing nothing else), one request like the one shown take
about .2 sec to be executed. Our horde_datatree table contains more han
10000 entries, and the horde_datatree_attribute also more than 10000
entries.

Luc.
-- 
Luc Germain, analyste
Support technique
Service des technologies de l'information
Université de Sherbrooke, Sherbrooke (Québec) Canada  J1K 2R1
tél: 819-821-8000 poste 2866    fax: 819-821-8045
émail: Luc.Germain at USherbrooke.ca

Selon Robert Atkinson <phreaki at gmail.com>:

> 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
>>
> --
> 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
>
>