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: sa...@bestpractical.com > > > Discover RT's hidden secrets with RT Essentials from O'Reilly Media. > Buy a copy at http://rtbook.bestpractical.com _______________________________________________ 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