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
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 = ''
signature.asc
Description: Digital signature
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com