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

[Kenneth Marshall]

On Fri, Nov 20, 2009 at 04:14:52PM +0000, Dominic Hargreaves wrote:
> 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


Hi Dominic,

Here are the indexes that we have that differ from your
setup:

"users1" UNIQUE, btree (lower(name::text))

instead of:

"groups3" btree (lower(domain::text), lower(type::text))

we have:

"groups2" btree (lower(type::text), lower(domain::text), instance)

You also should definitely raise the statistics target to
at least 100, which is the new default in 8.4. We also have
the random_page_cost set to 2.0 since we are mainly memory
resident. I know that the index order needs to match the
query to be used, so maybe these index changes would help.

Regards,
Ken


Content-Description: Config file for problematic RT instance database server
> 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 = ''




> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com