[rt-users] Slow queries building list of privileged users (Postgres)

Fri Nov 20 11:14:52 EST 2009

On Fri, Nov 20, 2009 at 08:02:46AM -0600, Kenneth Marshall wrote:
> On Fri, Nov 20, 2009 at 11:41:50AM +0000, Dominic Hargreaves wrote:
> > I'm migrating from an RT 2 install to an RT 3.8 install with around
> > 170 privileged users (and around 90,000 total users).
> > 
> > I've done some initial testing with RT 3.8.6 and have observed
> > that building the list of privileged users (in the "create new
> > ticket" (ticket owner), "display ticket" (reminder owner) and
> > "ticket search" (ticket owner) pages) takes around 20-30 seconds to run.
> > 
> > It performs two queries which are logged by my slow query logger:

[snip details]

> > Both systems are Debian lenny, RT 3.8.6, Postgres 8.3.
> > 
> > If anyone has any other advice about running RT 3.8 on postgres
> > I'd be interested too - I've added a couple of extra indexes:
> > 
> > CREATE INDEX Groups3 ON Groups (LOWER(Domain), LOWER(Type));
> > CREATE INDEX users5 ON users (LOWER(emailaddress));
> > 
> > which are missing from the default installation which have
> > cut down some other common slow queries (the former
> > is already in an RT ticket:
> > 
> > http://rt3.fsck.com/Ticket/Display.html?id=13056
> > 
> > )

> First, do you have $UseSQLForACLChecks set? I know that that
> is a new option and there may still be performance tuning that
> needs to be done to have it work well.

No, it's not set. That in itself we'd certainly like to use, but it
introduced yet another unacceptable slowdown - something to analyse
separately, probably.

> We run RT 3.8.5 on a
> PostgreSQL 8.4.1 database with 25K users and about 400 privileged
> users and we do not see a performance problem. Would you mind
> posting your postgres.conf changes from the default values as
> well as the indexes you have defined for the tables involved.

I've attached our postgresql.conf.

The indexes we have defined are the standard ones from the 3.8.6
schemas, plus one of the two I already posted:

CREATE INDEX Groups3 ON Groups (LOWER(Domain), LOWER(Type));

I've just noticed that this one wasn't created on the particular test
instance I'm talking about, but the query in question doesn't use
emailaddress, so that's probably not relevant:

CREATE INDEX users5 ON users (LOWER(emailaddress));

For completeness, the indexes defined on the relevant tables are:

users:
    "users_pkey" PRIMARY KEY, btree (id)
    "users1" UNIQUE, btree (name)
    "users3" btree (id, emailaddress)
    "users4" btree (emailaddress)

acl:
    "acl_pkey" PRIMARY KEY, btree (id)
    "acl1" btree (rightname, objecttype, objectid, principaltype, principalid)

principals:
    "principals_pkey" PRIMARY KEY, btree (id)
    "principals2" btree (objectid)

cachedgroupmembers:
    "cachedgroupmembers_pkey" PRIMARY KEY, btree (id)
    "cachedgroupmembers2" btree (memberid)
    "cachedgroupmembers3" btree (groupid)
    "disgroumem" btree (groupid, memberid, disabled)

groups:
    "groups_pkey" PRIMARY KEY, btree (id)
    "groups1" UNIQUE, btree (domain, instance, type, id, name)
    "groups2" btree (type, instance, domain)
    "groups3" btree (lower(domain::text), lower(type::text))

> Also, what is your statistics target for your tables?

default_statistics_target = 10

and no per-table changes. I'm not familiar with tuning this; would
you suggest a different value?

Thanks,
Dominic.

-- 
Dominic Hargreaves, Systems Development and Support Team
Computing Services, University of Oxford
-------------- next part --------------
hba_file = '/etc/postgresql-instances/rt/pg_hba.conf'
ident_file = '/etc/postgresql-instances/rt/pg_ident.conf'
listen_addresses = [snip]
port = 5432
max_connections = 100
superuser_reserved_connections = 3
unix_socket_directory = '/var/run/postgresql-instances/rt'
unix_socket_group = ''
unix_socket_permissions = 0777
authentication_timeout = 1min
tcp_keepalives_idle = 0
tcp_keepalives_interval = 0
tcp_keepalives_count = 0
ssl = on
password_encryption = on
db_user_namespace = off
shared_buffers = 1GB
temp_buffers = 32MB
max_prepared_transactions = 5
work_mem = 32MB
maintenance_work_mem = 256MB
max_stack_depth = 2MB
max_fsm_pages = 500000
max_fsm_relations = 1000
max_files_per_process = 1000
vacuum_cost_delay = 0
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0
fsync = on
synchronous_commit = on
wal_sync_method = fdatasync
full_page_writes = on
wal_buffers = 64kB
wal_writer_delay = 200ms
commit_delay = 0
commit_siblings = 5
checkpoint_segments = 8
checkpoint_timeout = 5min
checkpoint_completion_target = 0.5
checkpoint_warning = 30s
archive_mode = off
enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on
enable_sort = on
enable_tidscan = on
seq_page_cost = 1.0
random_page_cost = 4.0
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
effective_cache_size = 128MB
geqo = on
geqo_threshold = 12
geqo_effort = 5
geqo_pool_size = 0
geqo_generations = 0
geqo_selection_bias = 2.0
default_statistics_target = 10
constraint_exclusion = off
from_collapse_limit = 8
join_collapse_limit = 8
log_destination = 'stderr'
logging_collector = off
client_min_messages = notice
log_min_messages = notice
log_error_verbosity = default
log_min_error_statement = error
log_min_duration_statement = -1
silent_mode = off
debug_print_parse = off
debug_print_rewritten = off
debug_print_plan = off
debug_pretty_print = on
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = off
log_hostname = off
log_line_prefix = '%c %l %d %u %r %v %x '
log_lock_waits = on
log_statement = 'ddl'
log_temp_files = 20480
log_timezone = UTC
track_activities = on
track_counts = on
update_process_title = off
log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
log_statement_stats = off
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 200000000
autovacuum_vacuum_cost_delay = 20
autovacuum_vacuum_cost_limit = -1
search_path = '"$user",public'
default_tablespace = ''
temp_tablespaces = ''
check_function_bodies = on
default_transaction_isolation = 'read committed'
default_transaction_read_only = off
session_replication_role = 'origin'
statement_timeout = 0
vacuum_freeze_min_age = 100000000
xmlbinary = 'base64'
xmloption = 'content'
datestyle = 'iso, dmy'
timezone = 'Europe/London'
timezone_abbreviations = 'Default'
extra_float_digits = 0
lc_messages = 'en_GB.UTF-8'
lc_monetary = 'en_GB.UTF-8'
lc_numeric = 'en_GB.UTF-8'
lc_time = 'en_GB.UTF-8'
default_text_search_config = 'pg_catalog.english'
explain_pretty_print = on
dynamic_library_path = '$libdir'
local_preload_libraries = ''
deadlock_timeout = 2s
max_locks_per_transaction = 64
add_missing_from = off
array_nulls = on
backslash_quote = safe_encoding
default_with_oids = off
escape_string_warning = on
regex_flavor = advanced
sql_inheritance = on
standard_conforming_strings = on
synchronize_seqscans = on
transform_null_equals = off
custom_variable_classes = ''
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: Digital signature
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20091120/25919e31/attachment.sig>