[Tickets #5728] horde3_prefs table in IMP Database very poor performance

[bugs at bugs.horde.org]

DO NOT REPLY TO THIS MESSAGE. THIS EMAIL ADDRESS IS NOT MONITORED.

Ticket URL: http://bugs.horde.org/ticket/?id=5728
-----------------------------------------------------------------------
 Ticket             | 5728
 Created By         | lbellamy at deakin.edu.au
 Summary            | horde3_prefs table in IMP Database very poor performance
 Queue              | Horde Base
 Version            | 3.1.3
 Type               | Bug
 State              | Unconfirmed
 Priority           | 2. Medium
 Owners             | 
-----------------------------------------------------------------------


lbellamy at deakin.edu.au (2007-09-17 20:19) wrote:

*informational only; we have mitigated this bug from our side*

We currently have a horde 3.1.3 install running IMP as our university Web
Mail client. On the same host we run webmail from, we have the imp
database. We have a potential userbase of this application of 40,000
users.

Recently we noticed that we had rather high load on this host  (in excess
of 100....) in comparison to the 2 or 3 it normally sits on. 

Investigation lead us to notice that imp was generating some inefficient
queries to the imp database, and more specifically to the horde3_prefs
table. In this table, we have approx 350,000 rows, and the only entry into
this table was via a full table scan. During the times of high load, imp
was causing our oracle backend to do about 6.6 billion fetched records;
understandably causing high load.

We resolved this by placing indexing on the horde3_prefs table so full
table scans are not required every single query, which has significantly
reduced the load placed on the server by horde/imp/oracle.

This is just to let you know that it would appear once we hit a certain
user / preference threshold, performance of the application was
significantly impacted, to the point where it caused issues with the server
it was running on. 

If you need any more information, please dont hesitate to send me an
email.

Regards,

Luke