[Tickets #4079] NEW: horde_prefs table access very slow under high usage

[bugs@bugs.horde.org]

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

Ticket URL: http://bugs.horde.org/ticket/?id=4079
-----------------------------------------------------------------------
 Ticket             | 4079
 Created By         | j.benoit at free.fr
 Summary            | horde_prefs table access very slow under high usage
 Queue              | Horde Base
 Version            | 3.1.1
 State              | Unconfirmed
 Priority           | 3. High
 Type               | Bug
 Owners             | 
+New Attachment     | horde_prefs.sql.patch
-----------------------------------------------------------------------


j.benoit at free.fr (2006-06-26 03:32) wrote:


With a few thousand user and a few hundreds simultaneous access to horde,
the application becomes too slow.

I tracked down the problem to too many sequential lookup in the table
horde_prefs.
At peak hours, the same type of SQL request start to pile up in the database
:

  SELECT pref_scope, pref_name, pref_value FROM horde_prefs WHERE pref_uid =
'xxxx'  AND (pref_scope = 'horde' OR pref_scope = 'horde') ORDER BY
pref_scope

In our database, this table containe over 62000 entries.

In pg_stat_user_tables (a system view providing detailed statistics of
database usage in PostgreSQL), we can see a high rate of sequential lookups
:

 relid | schemaname |          relname          | seq_scan | seq_tup_read |
idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del 
 16416 | public     | horde_prefs               |   128826 |   8010852425 | 
        |               |        47 |      3512 | 0

Other thing to notice is there is no index used.


The SQL creation script confirms that the table horde_prefs is not using any
index.


Creating two indexes solved the problem.
(see attached patch)

Environment : 
OS  : FreeBSD 6.0-Release-p6
Horde : 3.1.1
DB : postgresql 8.1.4