Skip to content

Instantly share code, notes, and snippets.

@laurio

laurio/psa-test-0_Full_report.md

Created August 17, 2020 09:19
Show Gist options
  • Save laurio/1c53e55fbbce82db02ecd73e77b58e19 to your computer and use it in GitHub Desktop.
Save laurio/1c53e55fbbce82db02ecd73e77b58e19 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
psa-test-0_Full_report.md

PostgreSQL Checkup. Project: 'psatest1'. Database: 'programs'

Epoch number: '1'

NOTICE: while most reports describe the “current database”, some of them may contain cluster-wide information describing all databases in the cluster.

Last modified at: 2020-08-17 12:12:14 +0300

 

Table of contents

A002 Version Information
A003 Postgres Settings
A004 Cluster Information
A007 Altered Settings
D004 pg_stat_statements and pg_stat_kcache Settings
F001 Autovacuum: Current Settings
F002 Autovacuum: Transaction ID Wraparound Check
F003 Autovacuum: Dead Tuples
F004 Autovacuum: Heap Bloat (Estimated)
F005 Autovacuum: Btree Index Bloat (Estimated)
F008 Autovacuum: Resource Usage
G001 Memory-related Settings
G002 Connections and Current Activity
G003 Timeouts, Locks, Deadlocks
H001 Invalid Indexes
H002 Unused Indexes
H003 Non-indexed Foreign Keys
H004 Redundant Indexes
L001 Table Sizes
L003 Integer (int2, int4) Out-of-range Risks in PKs

Issues found

The empty lines represent reports for which Conclusions and Recommendations are not yet implemented.

Report P1 P2 P3
A002 Version Information 0 1 1
A003 Postgres Settings
A004 Cluster Information
A007 Altered Settings
D004 pg_stat_statements and pg_stat_kcache Settings
F001 Autovacuum: Current Settings 0 0 0
F002 Autovacuum: Transaction ID Wraparound Check 0 0 0
F003 Autovacuum: Dead Tuples
F004 Autovacuum: Heap Bloat (Estimated) 1 0 0
F005 Autovacuum: Btree Index Bloat (Estimated) 1 0 0
F008 Autovacuum: Resource Usage
G001 Memory-related Settings
G002 Connections and Current Activity 0 0 0
G003 Timeouts, Locks, Deadlocks
H001 Invalid Indexes
H002 Unused Indexes 0 0 0
H003 Non-indexed Foreign Keys
H004 Redundant Indexes 0 1 0
L001 Table Sizes
L003 Integer (int2, int4) Out-of-range Risks in PKs 0 0 0

  Table of contents

A002 Version Information

Observations

Data collected: 2020-08-17 12:09:07 +0300 EEST

Master (programs-search-api-rds-test.c2t.yle.fi)

PostgreSQL 11.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit

Conclusions

  • All nodes have the same Postgres version (11.6).

  • Postgres major version being used is 11 and it is currently supported by PostgreSQL Global Development Group. End of life is scheduled 2023-11-09. It means that in case of bugs and security issues, updates (new minor versions) with fixes will be released and available for use. Read more: Versioning Policy.

  • [P2] The minor version being used (11.6) is not up-to-date (the newest version: 11.9). See the full list of changes between 11.6 and 11.9.

Recommendations

  Table of contents

A003 Postgres Settings

Observations

Data collected: 2020-08-17 12:09:26 +0300 EEST

Master (programs-search-api-rds-test.c2t.yle.fi)

▼ Category Setting Value Unit Pretty value
Autovacuum autovacuum on
Autovacuum autovacuum_analyze_scale_factor 0.05
Autovacuum autovacuum_analyze_threshold 50
Autovacuum autovacuum_freeze_max_age 200000000
Autovacuum autovacuum_max_workers 3
Autovacuum autovacuum_multixact_freeze_max_age 400000000
Autovacuum autovacuum_naptime 15 s
Autovacuum autovacuum_vacuum_cost_delay 20 ms
Autovacuum autovacuum_vacuum_cost_limit 1200
Autovacuum autovacuum_vacuum_scale_factor 0.1
Autovacuum autovacuum_vacuum_threshold 50
Client Connection Defaults / Locale and Formatting client_encoding UTF8
Client Connection Defaults / Locale and Formatting DateStyle ISO, MDY
Client Connection Defaults / Locale and Formatting default_text_search_config pg_catalog.simple
Client Connection Defaults / Locale and Formatting extra_float_digits 0
Client Connection Defaults / Locale and Formatting IntervalStyle postgres
Client Connection Defaults / Locale and Formatting lc_collate en_US.UTF-8
Client Connection Defaults / Locale and Formatting lc_ctype en_US.UTF-8
Client Connection Defaults / Locale and Formatting lc_messages
Client Connection Defaults / Locale and Formatting lc_monetary C
Client Connection Defaults / Locale and Formatting lc_numeric C
Client Connection Defaults / Locale and Formatting lc_time C
Client Connection Defaults / Locale and Formatting server_encoding UTF8
Client Connection Defaults / Locale and Formatting TimeZone UTC
Client Connection Defaults / Locale and Formatting timezone_abbreviations Default
Client Connection Defaults / Other Defaults dynamic_library_path $libdir
Client Connection Defaults / Other Defaults gin_fuzzy_search_limit 0
Client Connection Defaults / Other Defaults tcp_keepalives_count 2
Client Connection Defaults / Other Defaults tcp_keepalives_idle 300 s
Client Connection Defaults / Other Defaults tcp_keepalives_interval 30 s
Client Connection Defaults / Shared Library Preloading jit_provider llvmjit
Client Connection Defaults / Shared Library Preloading local_preload_libraries
Client Connection Defaults / Shared Library Preloading session_preload_libraries
Client Connection Defaults / Shared Library Preloading shared_preload_libraries rdsutils, pg_stat_statements
Client Connection Defaults / Statement Behavior bytea_output hex
Client Connection Defaults / Statement Behavior check_function_bodies on
Client Connection Defaults / Statement Behavior client_min_messages notice
Client Connection Defaults / Statement Behavior default_tablespace
Client Connection Defaults / Statement Behavior default_transaction_deferrable off
Client Connection Defaults / Statement Behavior default_transaction_isolation read committed
Client Connection Defaults / Statement Behavior default_transaction_read_only off
Client Connection Defaults / Statement Behavior gin_pending_list_limit 4096 kB 4.00 MiB
Client Connection Defaults / Statement Behavior idle_in_transaction_session_timeout 86400000 ms
Client Connection Defaults / Statement Behavior lock_timeout 0 ms
Client Connection Defaults / Statement Behavior row_security on
Client Connection Defaults / Statement Behavior search_path "$user", public
Client Connection Defaults / Statement Behavior session_replication_role origin
Client Connection Defaults / Statement Behavior statement_timeout 0 ms
Client Connection Defaults / Statement Behavior temp_tablespaces
Client Connection Defaults / Statement Behavior transaction_deferrable off
Client Connection Defaults / Statement Behavior transaction_isolation read committed
Client Connection Defaults / Statement Behavior transaction_read_only off
Client Connection Defaults / Statement Behavior vacuum_cleanup_index_scale_factor 0.1
Client Connection Defaults / Statement Behavior vacuum_freeze_min_age 50000000
Client Connection Defaults / Statement Behavior vacuum_freeze_table_age 150000000
Client Connection Defaults / Statement Behavior vacuum_multixact_freeze_min_age 5000000
Client Connection Defaults / Statement Behavior vacuum_multixact_freeze_table_age 150000000
Client Connection Defaults / Statement Behavior xmlbinary base64
Client Connection Defaults / Statement Behavior xmloption content
Connections and Authentication / Authentication authentication_timeout 60 s
Connections and Authentication / Authentication db_user_namespace off
Connections and Authentication / Authentication krb_caseins_users off
Connections and Authentication / Authentication krb_server_keyfile /rdsdbdata/config/keytab
Connections and Authentication / Authentication password_encryption md5
Connections and Authentication / Connection Settings bonjour off
Connections and Authentication / Connection Settings bonjour_name
Connections and Authentication / Connection Settings listen_addresses *
Connections and Authentication / Connection Settings max_connections 5000
Connections and Authentication / Connection Settings port 5432
Connections and Authentication / Connection Settings rds.rds_superuser_reserved_connections 2
Connections and Authentication / Connection Settings superuser_reserved_connections 3
Connections and Authentication / Connection Settings unix_socket_directories /tmp
Connections and Authentication / Connection Settings unix_socket_group rdsdb
Connections and Authentication / Connection Settings unix_socket_permissions 0700
Connections and Authentication / SSL ssl on
Connections and Authentication / SSL ssl_ca_file /rdsdbdata/rds-metadata/ca-cert.pem
Connections and Authentication / SSL ssl_cert_file /rdsdbdata/rds-metadata/server-cert.pem
Connections and Authentication / SSL ssl_ciphers HIGH:MEDIUM:+3DES:!aNULL:!RC4
Connections and Authentication / SSL ssl_crl_file
Connections and Authentication / SSL ssl_dh_params_file
Connections and Authentication / SSL ssl_ecdh_curve prime256v1
Connections and Authentication / SSL ssl_key_file /rdsdbdata/rds-metadata/server-key.pem
Connections and Authentication / SSL ssl_passphrase_command
Connections and Authentication / SSL ssl_passphrase_command_supports_reload off
Connections and Authentication / SSL ssl_prefer_server_ciphers on
Customized Options pg_stat_statements.max 5000
Customized Options pg_stat_statements.save on
Customized Options pg_stat_statements.track top
Customized Options pg_stat_statements.track_utility on
Customized Options rds.extensions address_standardizer, address_standardizer_data_us, amcheck, aws_commons, aws_s3, bloom, btree_gin, btree_gist, citext, cube, dblink, dict_int, dict_xsyn, earthdistance, fuzzystrmatch, hll, hstore, hstore_plperl, intagg, intarray, ip4r, isn, jsonb_plperl, log_fdw, ltree, orafce, pageinspect, pgaudit, pgcrypto, pglogical, pgrouting, pgrowlocks, pgstattuple, pgtap, pg_buffercache, pg_freespacemap, pg_hint_plan, pg_prewarm, pg_repack, pg_similarity, pg_stat_statements, pg_transport, pg_trgm, pg_visibility, plcoffee, plls, plperl, plpgsql, plprofiler, pltcl, plv8, postgis, postgis_tiger_geocoder, postgis_topology, postgres_fdw, prefix, sslinfo, tablefunc, test_parser, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp
Customized Options rds.force_admin_logging_level disabled
Customized Options rds.force_autovacuum_logging_level info
Customized Options rds.internal_databases rdsadmin, template0
Customized Options rds.logical_replication off
Customized Options rds.restrict_password_commands off
Customized Options rds.superuser_variables session_replication_role
Developer Options allow_system_table_mods off
Developer Options ignore_checksum_failure off
Developer Options ignore_system_indexes off
Developer Options jit_debugging_support off
Developer Options jit_dump_bitcode off
Developer Options jit_expressions on
Developer Options jit_profiling_support off
Developer Options jit_tuple_deforming on
Developer Options post_auth_delay 0 s
Developer Options pre_auth_delay 0 s
Developer Options trace_notify off
Developer Options trace_recovery_messages log
Developer Options trace_sort off
Developer Options wal_consistency_checking
Developer Options zero_damaged_pages off
Error Handling data_sync_retry off
Error Handling exit_on_error off
Error Handling restart_after_crash on
File Locations config_file /rdsdbdata/config/postgresql.conf
File Locations data_directory /rdsdbdata/db
File Locations external_pid_file
File Locations hba_file /rdsdbdata/config/pg_hba.conf
File Locations ident_file /rdsdbdata/config/pg_ident.conf
Lock Management deadlock_timeout 1000 ms
Lock Management max_locks_per_transaction 64
Lock Management max_pred_locks_per_page 2
Lock Management max_pred_locks_per_relation -2
Lock Management max_pred_locks_per_transaction 64
Preset Options block_size 8192
Preset Options data_checksums on
Preset Options data_directory_mode 0700
Preset Options debug_assertions off
Preset Options integer_datetimes on
Preset Options max_function_args 100
Preset Options max_identifier_length 63
Preset Options max_index_keys 32
Preset Options segment_size 131072 8kB 1.00 GiB
Preset Options server_version 11.6
Preset Options server_version_num 110006
Preset Options wal_block_size 8192
Preset Options wal_segment_size 67108864 B
Process Title cluster_name
Process Title update_process_title on
Query Tuning / Genetic Query Optimizer geqo on
Query Tuning / Genetic Query Optimizer geqo_effort 5
Query Tuning / Genetic Query Optimizer geqo_generations 0
Query Tuning / Genetic Query Optimizer geqo_pool_size 0
Query Tuning / Genetic Query Optimizer geqo_seed 0
Query Tuning / Genetic Query Optimizer geqo_selection_bias 2
Query Tuning / Genetic Query Optimizer geqo_threshold 12
Query Tuning / Other Planner Options constraint_exclusion partition
Query Tuning / Other Planner Options cursor_tuple_fraction 0.1
Query Tuning / Other Planner Options default_statistics_target 100
Query Tuning / Other Planner Options force_parallel_mode off
Query Tuning / Other Planner Options from_collapse_limit 8
Query Tuning / Other Planner Options jit off
Query Tuning / Other Planner Options join_collapse_limit 8
Query Tuning / Planner Cost Constants cpu_index_tuple_cost 0.005
Query Tuning / Planner Cost Constants cpu_operator_cost 0.0025
Query Tuning / Planner Cost Constants cpu_tuple_cost 0.01
Query Tuning / Planner Cost Constants effective_cache_size 4059178 8kB 30.97 GiB
Query Tuning / Planner Cost Constants jit_above_cost 100000
Query Tuning / Planner Cost Constants jit_inline_above_cost 500000
Query Tuning / Planner Cost Constants jit_optimize_above_cost 500000
Query Tuning / Planner Cost Constants min_parallel_index_scan_size 64 8kB 512.00 KiB
Query Tuning / Planner Cost Constants min_parallel_table_scan_size 1024 8kB 8.00 MiB
Query Tuning / Planner Cost Constants parallel_setup_cost 1000
Query Tuning / Planner Cost Constants parallel_tuple_cost 0.1
Query Tuning / Planner Cost Constants random_page_cost 4
Query Tuning / Planner Cost Constants seq_page_cost 1
Query Tuning / Planner Method Configuration enable_bitmapscan on
Query Tuning / Planner Method Configuration enable_gathermerge on
Query Tuning / Planner Method Configuration enable_hashagg on
Query Tuning / Planner Method Configuration enable_hashjoin on
Query Tuning / Planner Method Configuration enable_indexonlyscan on
Query Tuning / Planner Method Configuration enable_indexscan on
Query Tuning / Planner Method Configuration enable_material on
Query Tuning / Planner Method Configuration enable_mergejoin on
Query Tuning / Planner Method Configuration enable_nestloop on
Query Tuning / Planner Method Configuration enable_parallel_append on
Query Tuning / Planner Method Configuration enable_parallel_hash on
Query Tuning / Planner Method Configuration enable_partition_pruning on
Query Tuning / Planner Method Configuration enable_partitionwise_aggregate off
Query Tuning / Planner Method Configuration enable_partitionwise_join off
Query Tuning / Planner Method Configuration enable_seqscan on
Query Tuning / Planner Method Configuration enable_sort on
Query Tuning / Planner Method Configuration enable_tidscan on
Replication track_commit_timestamp off
Replication / Master Server synchronous_standby_names
Replication / Master Server vacuum_defer_cleanup_age 0
Replication / Sending Servers max_replication_slots 10
Replication / Sending Servers max_wal_senders 10
Replication / Sending Servers rds.restrict_logical_slot_creation off
Replication / Sending Servers wal_keep_segments 32
Replication / Sending Servers wal_sender_timeout 30000 ms
Replication / Standby Servers hot_standby off
Replication / Standby Servers hot_standby_feedback off
Replication / Standby Servers max_standby_archive_delay 30000 ms
Replication / Standby Servers max_standby_streaming_delay 30000 ms
Replication / Standby Servers wal_receiver_status_interval 10 s
Replication / Standby Servers wal_receiver_timeout 30000 ms
Replication / Standby Servers wal_retrieve_retry_interval 5000 ms
Replication / Subscribers max_logical_replication_workers 4
Replication / Subscribers max_sync_workers_per_subscription 2
Reporting and Logging / What to Log application_name checkup
Reporting and Logging / What to Log debug_pretty_print on
Reporting and Logging / What to Log debug_print_parse off
Reporting and Logging / What to Log debug_print_plan off
Reporting and Logging / What to Log debug_print_rewritten off
Reporting and Logging / What to Log log_autovacuum_min_duration 10000 ms
Reporting and Logging / What to Log log_checkpoints on
Reporting and Logging / What to Log log_connections off
Reporting and Logging / What to Log log_disconnections off
Reporting and Logging / What to Log log_duration off
Reporting and Logging / What to Log log_error_verbosity default
Reporting and Logging / What to Log log_hostname on
Reporting and Logging / What to Log log_line_prefix %t:%r:%u@%d:[%p]:
Reporting and Logging / What to Log log_lock_waits off
Reporting and Logging / What to Log log_replication_commands off
Reporting and Logging / What to Log log_statement none
Reporting and Logging / What to Log log_temp_files -1 kB
Reporting and Logging / What to Log log_timezone UTC
Reporting and Logging / When to Log log_min_duration_statement -1 ms
Reporting and Logging / When to Log log_min_error_statement error
Reporting and Logging / When to Log log_min_messages warning
Reporting and Logging / Where to Log event_source PostgreSQL
Reporting and Logging / Where to Log log_destination stderr
Reporting and Logging / Where to Log log_directory /rdsdbdata/log/error
Reporting and Logging / Where to Log log_file_mode 0644
Reporting and Logging / Where to Log log_filename postgresql.log.%Y-%m-%d-%H
Reporting and Logging / Where to Log logging_collector on
Reporting and Logging / Where to Log log_rotation_age 60 min
Reporting and Logging / Where to Log log_rotation_size 10240 kB 10.00 MiB
Reporting and Logging / Where to Log log_truncate_on_rotation off
Reporting and Logging / Where to Log syslog_facility local0
Reporting and Logging / Where to Log syslog_ident postgres
Reporting and Logging / Where to Log syslog_sequence_numbers on
Reporting and Logging / Where to Log syslog_split_messages on
Resource Usage / Asynchronous Behavior backend_flush_after 0 8kB 0.00 bytes
Resource Usage / Asynchronous Behavior effective_io_concurrency 1
Resource Usage / Asynchronous Behavior max_parallel_maintenance_workers 2
Resource Usage / Asynchronous Behavior max_parallel_workers 8
Resource Usage / Asynchronous Behavior max_parallel_workers_per_gather 2
Resource Usage / Asynchronous Behavior max_worker_processes 8
Resource Usage / Asynchronous Behavior old_snapshot_threshold -1 min
Resource Usage / Asynchronous Behavior parallel_leader_participation on
Resource Usage / Background Writer bgwriter_delay 200 ms
Resource Usage / Background Writer bgwriter_flush_after 64 8kB 512.00 KiB
Resource Usage / Background Writer bgwriter_lru_maxpages 100
Resource Usage / Background Writer bgwriter_lru_multiplier 2
Resource Usage / Cost-Based Vacuum Delay vacuum_cost_delay 0 ms
Resource Usage / Cost-Based Vacuum Delay vacuum_cost_limit 200
Resource Usage / Cost-Based Vacuum Delay vacuum_cost_page_dirty 20
Resource Usage / Cost-Based Vacuum Delay vacuum_cost_page_hit 1
Resource Usage / Cost-Based Vacuum Delay vacuum_cost_page_miss 5
Resource Usage / Disk temp_file_limit -1 kB
Resource Usage / Kernel Resources max_files_per_process 1000
Resource Usage / Memory autovacuum_work_mem -1 kB
Resource Usage / Memory dynamic_shared_memory_type posix
Resource Usage / Memory huge_pages on
Resource Usage / Memory maintenance_work_mem 1064702 kB 1.02 GiB
Resource Usage / Memory max_prepared_transactions 0
Resource Usage / Memory max_stack_depth 6144 kB 6.00 MiB
Resource Usage / Memory shared_buffers 2029589 8kB 15.49 GiB
Resource Usage / Memory temp_buffers 1024 8kB 8.00 MiB
Resource Usage / Memory track_activity_query_size 4096 B
Resource Usage / Memory work_mem 4096 kB 4.00 MiB
Statistics / Monitoring log_executor_stats off
Statistics / Monitoring log_parser_stats off
Statistics / Monitoring log_planner_stats off
Statistics / Monitoring log_statement_stats off
Statistics / Query and Index Statistics Collector stats_temp_directory /rdsdbdata/db/pg_stat_tmp
Statistics / Query and Index Statistics Collector track_activities on
Statistics / Query and Index Statistics Collector track_counts on
Statistics / Query and Index Statistics Collector track_functions pl
Statistics / Query and Index Statistics Collector track_io_timing on
Version and Platform Compatibility / Other Platforms and Clients transform_null_equals off
Version and Platform Compatibility / Previous PostgreSQL Versions array_nulls on
Version and Platform Compatibility / Previous PostgreSQL Versions backslash_quote safe_encoding
Version and Platform Compatibility / Previous PostgreSQL Versions default_with_oids off
Version and Platform Compatibility / Previous PostgreSQL Versions escape_string_warning on
Version and Platform Compatibility / Previous PostgreSQL Versions lo_compat_privileges off
Version and Platform Compatibility / Previous PostgreSQL Versions operator_precedence_warning off
Version and Platform Compatibility / Previous PostgreSQL Versions quote_all_identifiers off
Version and Platform Compatibility / Previous PostgreSQL Versions standard_conforming_strings on
Version and Platform Compatibility / Previous PostgreSQL Versions synchronize_seqscans on
Write-Ahead Log / Archiving archive_command /etc/rds/dbbin/pgscripts/rds_wal_archive %p
Write-Ahead Log / Archiving archive_mode on
Write-Ahead Log / Archiving archive_timeout 300 s
Write-Ahead Log / Checkpoints checkpoint_completion_target 0.9
Write-Ahead Log / Checkpoints checkpoint_flush_after 32 8kB 256.00 KiB
Write-Ahead Log / Checkpoints checkpoint_timeout 300 s
Write-Ahead Log / Checkpoints checkpoint_warning 30 s
Write-Ahead Log / Checkpoints max_wal_size 2048 MB 2.00 GiB
Write-Ahead Log / Checkpoints min_wal_size 192 MB 192.00 MiB
Write-Ahead Log / Settings commit_delay 0
Write-Ahead Log / Settings commit_siblings 5
Write-Ahead Log / Settings fsync on
Write-Ahead Log / Settings full_page_writes on
Write-Ahead Log / Settings synchronous_commit on
Write-Ahead Log / Settings wal_buffers 8192 8kB 64.00 MiB
Write-Ahead Log / Settings wal_compression off
Write-Ahead Log / Settings wal_level replica
Write-Ahead Log / Settings wal_log_hints off
Write-Ahead Log / Settings wal_sync_method fdatasync
Write-Ahead Log / Settings wal_writer_delay 200 ms
Write-Ahead Log / Settings wal_writer_flush_after 128 8kB 1.00 MiB

  Table of contents

A004 Cluster Information

Observations

Data collected: 2020-08-17 12:09:35 +0300 EEST

▼ Indicator programs-search-api-rds-test.c2t.yle.fi
Config file /rdsdbdata/config/postgresql.conf
Role Master
Replicas
Started At 2020-03-18 05:42:03+00
Uptime 152 days 03:27:51
Checkpoints 147730
Forced Checkpoints 1.3%
Checkpoint MB/sec 0.186835
Database Name programs
Database Size 29 GB
Stats Since 2019-03-29 09:30:57+00
Stats Age 506 days 23:38:57
Cache Effectiveness 99.18%
Successful Commits 99.92%
Conflicts 0
Temp Files: total size 384 TB
Temp Files: total number of files 206435449
Temp Files: total number of files per day 407170
Temp Files: avg file size 1998 kB
Deadlocks 128
Deadlocks per day 0

Databases sizes

Database ▼ Size
programs 29.34 GiB
rdsadmin 7.90 MiB
template1 7.86 MiB
postgres 7.86 MiB
template0 7.69 MiB

Conclusions

Recommendations

  Table of contents

A007 Altered Settings

Observations

Data collected: 2020-08-17 12:10:21 +0300 EEST

Master (programs-search-api-rds-test.c2t.yle.fi)

Source Settings count Changed settings
/rdsdbdata/config/postgresql.conf 69 archive_command archive_mode archive_timeout autovacuum_analyze_scale_factor autovacuum_max_workers autovacuum_naptime autovacuum_vacuum_cost_limit autovacuum_vacuum_scale_factor checkpoint_completion_target client_encoding effective_cache_size fsync full_page_writes hot_standby huge_pages idle_in_transaction_session_timeout krb_server_keyfile lo_compat_privileges log_autovacuum_min_duration log_checkpoints log_destination log_directory log_file_mode log_filename logging_collector log_hostname log_line_prefix log_rotation_age log_timezone log_truncate_on_rotation maintenance_work_mem max_connections max_locks_per_transaction max_prepared_transactions max_replication_slots max_stack_depth max_wal_senders max_wal_size max_worker_processes min_wal_size password_encryption port rds.extensions rds.force_autovacuum_logging_level rds.internal_databases rds.logical_replication rds.rds_superuser_reserved_connections rds.superuser_variables shared_buffers shared_preload_libraries ssl ssl_ca_file ssl_cert_file ssl_key_file stats_temp_directory superuser_reserved_connections synchronous_commit TimeZone track_activity_query_size track_functions track_io_timing unix_socket_directories unix_socket_group unix_socket_permissions vacuum_cost_page_miss wal_keep_segments wal_level wal_receiver_timeout wal_sender_timeout
default 234

Conclusions

Recommendations

  Table of contents

D004 pg_stat_statements and pg_stat_kcache Settings

Observations

Data collected: 2020-08-17 12:10:27 +0300 EEST

Master (programs-search-api-rds-test.c2t.yle.fi)

pg_stat_statements extension settings

Setting Value Unit Type Min value Max value
pg_stat_statements.max 5000 integer 100 2147483647
pg_stat_statements.save on bool
pg_stat_statements.track top enum
pg_stat_statements.track_utility on bool

Conclusions

Recommendations

  Table of contents

F001 Autovacuum: Current Settings

Observations

Data collected: 2020-08-17 12:10:30 +0300 EEST

Master (programs-search-api-rds-test.c2t.yle.fi)

▼ Setting name Value Unit Pretty value
autovacuum on
autovacuum_analyze_scale_factor 0.05
autovacuum_analyze_threshold 50
autovacuum_freeze_max_age 200000000
autovacuum_max_workers 3
autovacuum_multixact_freeze_max_age 400000000
autovacuum_naptime 15 s
autovacuum_vacuum_cost_delay 20 ms
autovacuum_vacuum_cost_limit 1200
autovacuum_vacuum_scale_factor 0.1
autovacuum_vacuum_threshold 50
autovacuum_work_mem -1 kB
maintenance_work_mem 1064702 kB 1.02 GiB
vacuum_cleanup_index_scale_factor 0.1
vacuum_cost_delay 0 ms
vacuum_cost_limit 200
vacuum_cost_page_dirty 20
vacuum_cost_page_hit 1
vacuum_cost_page_miss 5
vacuum_defer_cleanup_age 0
vacuum_freeze_min_age 50000000
vacuum_freeze_table_age 150000000
vacuum_multixact_freeze_min_age 5000000
vacuum_multixact_freeze_table_age 150000000

Tuned tables

No tuned tables are found

Conclusions

Recommendations

All good, no recommendations here.

  Table of contents

F002 Autovacuum: Transaction ID Wraparound Check

Observations

  • Data collected: 2020-08-17 12:10:34 +0300 EEST
  • Current database: programs

Master (programs-search-api-rds-test.c2t.yle.fi)

Databases

# Database ▼ Age Capacity used, % Warning datfrozenxid
1 template0 81600272 4.18 562
2 rdsadmin 81600272 4.18 562
3 template1 81600272 4.18 562
4 postgres 81600272 4.18 562
5 programs 81600272 4.18 562

Tables in the observed database

The list is limited to 10 items. Total: 16.

# Relation Age ▼ Capacity used, % Warning rel_relfrozenxid toast_relfrozenxid
1 v1.series 81599303 4.18 15013626 1531
2 v1.publication 81599303 4.18 1531 1531
3 v1.season 81599303 4.18 26996232 1531
4 v1.flyway_schema_history 81599305 4.18 1529 1529
5 schema_config 71985472 3.69 28523092 9615362
6 v1.program 71112783 3.65 11156906 10488051
7 v1.playcount 71230577 3.65 31600711 10370257
8 pg_catalog.pg_class 50000003 2.56 31600831 0
9 v1.item_concepts 22613322 1.16 58987512 58987512
10 v1.publication_latest_mv 21698173 1.11 59902661 59902661

Conclusions

Recommendations

All good, no recommendations here.

  Table of contents

F003 Autovacuum: Dead Tuples

Observations

Data collected: 2020-08-17 12:10:38 +0300 EEST
Current database: programs
Stats reset: 1 year 4 mons 18 days 23:40:00 ago (2019-03-29 09:30:57 +0000 +0000)

Master (programs-search-api-rds-test.c2t.yle.fi)

# Relation reltype Since last autovacuum Since last vacuum Autovacuum Count Vacuum Count n_tup_ins n_tup_upd n_tup_del pg_class.reltuples n_live_tup n_dead_tup ▼Dead Tuples Ratio, %
1 v1.publication_latest_mv m 6 days 14:02:31.818434 29 0 819006 0 612982 205911 206024 18904 8.4
2 v1.publication r 17 days 18:04:07.658271 164 0 72849705 413899 64708054 8134449 8137290 637855 7.27
3 v1.season_ondemands_mv m 53 days 01:14:26.141756 4 0 199214 0 63747 132931 135467 3260 2.35
4 v1.series r 02:55:08.353592 299 0 26079 3518754 0 26079 26079 538 2.02
5 v1.item_concepts r 8 days 02:16:08.062848 8 0 390710 724812 5 383436 385852 5426 1.39
6 v1.publication_future_mv m 03:10:10.747279 74 0 585595 0 527002 58972 58593 697 1.18
7 v1.program r 00:35:44.896307 137 days 22:05:08.808028 2254 1 3473760 1574006072 23762 3424452 3424458 176 0.01

Conclusions

Recommendations

  Table of contents

F004 Autovacuum: Heap Bloat (Estimated)

⚠️ This report is based on estimations. The errors in bloat estimates may be significant (in some cases, up to 15% and even more). Use it only as an indicator of potential issues.

Observations

Data collected: 2020-08-17 12:10:42 +0300 EEST
Current database: programs

Master (programs-search-api-rds-test.c2t.yle.fi)

The list is limited to 50 items. Total: 51.

# Table Real Size ▼ Estimated bloat Est. bloat, bytes Est. bloat factor Est. bloat level, % Live Data Size Last vacuum Fillfactor
  ===== TOTAL ===== 4.24 GiB 1.67 GiB 1,790,599,168 1.65 39.40 ~2.57 GiB
1 v1.program 2.70 GiB 1.44 GiB 1,536,237,568 2.13 53.08 ~1.27 GiB 2020-08-17 08:34:58 (auto) 100
2 v1.publication 1.45 GiB 213.75 MiB 224,124,928 1.17 14.41 ~1.25 GiB 2020-07-30 15:06:35 (auto) 100
3 pg_catalog.pg_statistic 12.22 MiB 12.00 MiB 12,582,912 55.86 98.21 ~224.00 KiB 100
4 v1.item_concepts 53.93 MiB 5.71 MiB 5,980,160 1.12 10.58 ~48.23 MiB 2020-08-09 06:54:34 (auto) 100
5 v1.series 12.55 MiB 5.66 MiB 5,931,008 1.82 45.08 ~6.90 MiB 2020-08-17 06:15:34 (auto) 100
6 v1.season 7.23 MiB 4.11 MiB 4,308,992 2.32 56.86 ~3.12 MiB 2020-08-14 08:02:03 (auto) 100
7 v1.playcount 608.00 KiB 528.00 KiB 540,672 7.60 86.84 ~80.00 KiB 2020-08-17 09:08:18 (auto) 100
8 schema_config 400.00 KiB 384.00 KiB 393,216 25.00 96.00 ~16.00 KiB 2020-07-01 07:57:32 (auto) 100
9 pg_catalog.pg_attribute 584.00 KiB 136.00 KiB 139,264 1.30 23.29 ~448.00 KiB 100
10 pg_catalog.pg_rewrite 704.00 KiB 120.00 KiB 122,880 1.21 17.05 ~584.00 KiB 100
11 pg_catalog.pg_class 184.00 KiB 96.00 KiB 98,304 2.09 52.17 ~88.00 KiB 100
12 pg_catalog.pg_depend 512.00 KiB 48.00 KiB 49,152 1.10 9.38 ~464.00 KiB 100
13 information_schema.sql_features 56.00 KiB 32.00 KiB 32,768 2.33 57.14 ~24.00 KiB 100
14 pg_catalog.pg_type 104.00 KiB 24.00 KiB 24,576 1.30 23.08 ~80.00 KiB 100
15 pg_catalog.pg_proc 608.00 KiB 16.00 KiB 16,384 1.03 2.63 ~592.00 KiB 100
16 pg_catalog.pg_index 56.00 KiB 8.00 KiB 8,192 1.17 14.29 ~48.00 KiB 100
17 pg_catalog.pg_description 328.00 KiB 8.00 KiB 8,192 1.02 2.44 ~320.00 KiB 100
18 pg_catalog.pg_extension 8.00 KiB 1.00 0.00 ~8.00 KiB 100
19 pg_catalog.pg_init_privs 16.00 KiB 1.00 0.00 ~16.00 KiB 100
20 pg_catalog.pg_language 8.00 KiB 1.00 0.00 ~8.00 KiB 100
21 pg_catalog.pg_namespace 56.00 KiB 1.00 0.00 ~56.00 KiB 100
22 pg_catalog.pg_opclass 24.00 KiB 1.00 0.00 ~24.00 KiB 100
23 pg_catalog.pg_operator 120.00 KiB 1.00 0.00 ~120.00 KiB 100
24 pg_catalog.pg_opfamily 16.00 KiB 1.00 0.00 ~16.00 KiB 100
25 pg_catalog.pg_pltemplate 8.00 KiB 1.00 0.00 ~8.00 KiB 100
26 pg_catalog.pg_range 8.00 KiB 1.00 0.00 ~8.00 KiB 100
27 pg_catalog.pg_shdepend 8.00 KiB 1.00 0.00 ~8.00 KiB 100
28 pg_catalog.pg_shdescription 8.00 KiB 1.00 0.00 ~8.00 KiB 100
29 pg_catalog.pg_tablespace 8.00 KiB 1.00 0.00 ~8.00 KiB 100
30 pg_catalog.pg_ts_config 8.00 KiB 1.00 0.00 ~8.00 KiB 100
31 pg_catalog.pg_ts_config_map 16.00 KiB 1.00 0.00 ~16.00 KiB 100
32 pg_catalog.pg_ts_dict 8.00 KiB 1.00 0.00 ~8.00 KiB 100
33 pg_catalog.pg_ts_parser 8.00 KiB 1.00 0.00 ~8.00 KiB 100
34 pg_catalog.pg_ts_template 8.00 KiB 1.00 0.00 ~8.00 KiB 100
35 views_control 8.00 KiB 1.00 0.00 ~8.00 KiB 2020-08-17 09:10:33 (auto) 100
36 information_schema.sql_implementation_info 8.00 KiB 1.00 0.00 ~8.00 KiB 100
37 information_schema.sql_languages 8.00 KiB 1.00 0.00 ~8.00 KiB 100
38 information_schema.sql_packages 8.00 KiB 1.00 0.00 ~8.00 KiB 100
39 information_schema.sql_parts 8.00 KiB 1.00 0.00 ~8.00 KiB 100
40 information_schema.sql_sizing 8.00 KiB 1.00 0.00 ~8.00 KiB 100
41 pg_catalog.pg_aggregate 16.00 KiB 1.00 0.00 ~16.00 KiB 100
42 pg_catalog.pg_am 8.00 KiB 1.00 0.00 ~8.00 KiB 100
43 pg_catalog.pg_amop 56.00 KiB 1.00 0.00 ~56.00 KiB 100
44 pg_catalog.pg_amproc 32.00 KiB 1.00 0.00 ~32.00 KiB 100
45 pg_catalog.pg_auth_members 8.00 KiB 1.00 0.00 ~8.00 KiB 100
46 pg_catalog.pg_authid 8.00 KiB 1.00 0.00 ~8.00 KiB 100
47 pg_catalog.pg_cast 16.00 KiB 1.00 0.00 ~16.00 KiB 100
48 pg_catalog.pg_collation 408.00 KiB 1.00 0.00 ~408.00 KiB 100
49 pg_catalog.pg_constraint 8.00 KiB 1.00 0.00 ~8.00 KiB 100
50 pg_catalog.pg_conversion 24.00 KiB 1.00 0.00 ~24.00 KiB 100

Conclusions

  • [P1] Total table (heap) bloat estimation is ~1.67 GiB, it is 39.40% of the overall size of all tables and 5.68% of the DB size. Removing the table bloat will reduce the total DB size down to ~27.67 GiB. Free disk space will be increased by ~1.67 GiB. Total size of tables is 1.65 times bigger than it could be. Notice that this is only an estimation, sometimes it may be significantly off.

  • [P1] The following 1 table has significant size (>1 MiB) and bloat estimate > 90.00%:

    • pg_catalog.pg_statistic: size 12.22 MiB, can be reduced 55.86 times, by ~12.00 MiB (~98.21%)

  • [P2] There are 3 tables with size > 1 MiB and table bloat estimate >= 40.00% and < 90.00%:

    • v1.program: size 2.70 GiB, can be reduced 2.13 times, by ~1.44 GiB (~53.08%)
    • v1.series: size 12.55 MiB, can be reduced 1.82 times, by ~5.66 MiB (~45.08%)
    • v1.season: size 7.23 MiB, can be reduced 2.32 times, by ~4.11 MiB (~56.86%)

Recommendations

  • [P1] Reduce and prevent the high level of table bloat:

    • to prevent a high level of bloat in the future, tune autovacuum: consider more aggressive autovacuum settings (see F001);
    • eliminate or reduce the current table bloat using one of the approaches listed below.

  • The following tables have size > 1 MiB and table bloat estimate > 40.00%. Use this list to reduce the bloat applying one of the approaches described below. Here are these tables: v1.program, pg_catalog.pg_statistic, v1.series, v1.season.

  • If you want to get exact bloat numbers, clone the database, get table sizes, then apply database-wide VACUUM FULL (it eliminate all the bloat), and get new table sizes. Then compare old and new numbers.

  • To reduce the table bloat, consider one of the following approaches:

  • Read more on this topic:

  Table of contents

F005 Autovacuum: Btree Index Bloat (Estimated)

⚠️ This report is based on estimations. The errors in bloat estimates may be significant (in some cases, up to 15% and even more). Use it only as an indicator of potential issues.

Observations

Data collected: 2020-08-17 12:10:46 +0300 EEST
Current database: programs
ℹ️ This report considers only btree indexes.

Master (programs-search-api-rds-test.c2t.yle.fi)

The list is limited to 50 items. Total: 74.

# Index (Table) Index Size Table Size ▼ Estimated bloat Est. bloat, bytes Est. bloat factor Est. bloat level, % Live Data Size Fillfactor
  ===== TOTAL ===== 18.09 GiB 4.23 GiB 5.60 GiB 6,011,084,800 1.45 30.95 12.49 GiB
1 program_season_id_idx (v1.program) 781.11 MiB 2.70 GiB 736.19 MiB 771,948,544 17.39 94.25 ~44.93 MiB 90
2 program_series_id_idx (v1.program) 679.89 MiB 2.70 GiB 604.82 MiB 634,191,872 9.06 88.96 ~75.08 MiB 90
3 program_series_id_season_id_visible_idx (v1.program) 673.40 MiB 2.70 GiB 569.99 MiB 597,671,936 6.51 84.64 ~103.42 MiB 90
4 program_series_id_visible (v1.program) 583.11 MiB 2.70 GiB 508.50 MiB 533,200,896 7.82 87.20 ~74.61 MiB 90
5 program_series_id_visible_idx (v1.program) 581.49 MiB 2.70 GiB 506.88 MiB 531,496,960 7.79 87.17 ~74.61 MiB 90
6 publication_type_media_times_idx (v1.publication) 1.48 GiB 1.45 GiB 497.65 MiB 521,814,016 1.49 33.04 ~0.99 GiB 90
7 publication_type_media_available_end_time_start_time_idx (v1.publication) 1.42 GiB 1.45 GiB 442.98 MiB 464,494,592 1.44 30.51 ~0.99 GiB 90
8 publication_program_id_idx (v1.publication) 626.43 MiB 1.45 GiB 383.86 MiB 402,505,728 2.58 61.28 ~242.58 MiB 90
9 publication_pkey (v1.publication) 610.22 MiB 1.45 GiB 367.65 MiB 385,507,328 2.52 60.25 ~242.58 MiB 90
10 publication_program_id_type_times_idx (v1.publication) 1.68 GiB 1.45 GiB 361.18 MiB 378,716,160 1.27 21.08 ~1.33 GiB 90
11 program_series_id_visible_not_expired_idx (v1.program) 251.38 MiB 2.70 GiB 246.58 MiB 258,547,712 52.32 98.09 ~4.81 MiB 90
12 program_pkey (v1.program) 277.45 MiB 2.70 GiB 174.59 MiB 183,066,624 2.70 62.93 ~102.86 MiB 90
13 program_id_series_id_idx (v1.program) 318.40 MiB 2.70 GiB 157.04 MiB 164,659,200 1.97 49.32 ~161.37 MiB 90
14 program_id_not_hidden_idx (v1.program) 225.65 MiB 2.70 GiB 123.43 MiB 129,417,216 2.21 54.70 ~102.23 MiB 90
15 program_episode_number_series_id_idx (v1.program) 19.22 MiB 2.70 GiB 15.23 MiB 15,966,208 4.82 79.26 ~3.99 MiB 90
16 publication_catalog_tag_program_id_idx (v1.publication) 17.27 MiB 1.45 GiB 14.90 MiB 15,622,144 7.29 86.29 ~2.37 MiB 90
17 playcount_pkey (v1.playcount) 8.86 MiB 640.00 KiB 8.82 MiB 9,240,576 189.00 99.47 ~48.00 KiB 90
18 item_concepts_pkey (v1.item_concepts) 18.54 MiB 53.97 MiB 6.95 MiB 7,282,688 1.60 37.46 ~11.60 MiB 90
19 series_pkey (v1.series) 1.75 MiB 12.58 MiB 0.97 MiB 1,007,616 2.22 54.91 ~808.00 KiB 90
20 pg_attribute_relid_attnum_index (pg_catalog.pg_attribute) 944.00 KiB 616.00 KiB 840.00 KiB 860,160 9.08 88.98 ~104.00 KiB 90
21 pg_depend_depender_index (pg_catalog.pg_depend) 1.21 MiB 544.00 KiB 776.00 KiB 794,624 2.70 62.99 ~456.00 KiB 90
22 pg_attribute_relid_attnam_index (pg_catalog.pg_attribute) 1.26 MiB 616.00 KiB 768.00 KiB 786,432 2.48 59.63 ~520.00 KiB 90
23 pg_depend_reference_index (pg_catalog.pg_depend) 1.16 MiB 544.00 KiB 728.00 KiB 745,472 2.60 61.49 ~456.00 KiB 90
24 pg_class_relname_nsp_index (pg_catalog.pg_class) 472.00 KiB 216.00 KiB 384.00 KiB 393,216 5.36 81.36 ~88.00 KiB 90
25 season_pkey (v1.season) 600.00 KiB 7.26 MiB 336.00 KiB 344,064 2.27 56.00 ~264.00 KiB 90
26 pg_class_tblspc_relfilenode_index (pg_catalog.pg_class) 288.00 KiB 216.00 KiB 264.00 KiB 270,336 12.00 91.67 ~24.00 KiB 90
27 pg_type_typname_nsp_index (pg_catalog.pg_type) 336.00 KiB 136.00 KiB 256.00 KiB 262,144 4.20 76.19 ~80.00 KiB 90
28 pg_shdepend_depender_index (pg_catalog.pg_shdepend) 88.00 KiB 40.00 KiB 72.00 KiB 73,728 5.50 81.82 ~16.00 KiB 90
29 pg_index_indrelid_index (pg_catalog.pg_index) 80.00 KiB 88.00 KiB 64.00 KiB 65,536 5.00 80.00 ~16.00 KiB 90
30 pg_index_indexrelid_index (pg_catalog.pg_index) 80.00 KiB 88.00 KiB 64.00 KiB 65,536 5.00 80.00 ~16.00 KiB 90
31 pg_shdepend_reference_index (pg_catalog.pg_shdepend) 80.00 KiB 40.00 KiB 64.00 KiB 65,536 5.00 80.00 ~16.00 KiB 90
32 pg_ts_parser_prsname_index (pg_catalog.pg_ts_parser) 16.00 KiB 40.00 KiB 1.00 0.00 ~16.00 KiB 90
33 pg_ts_config_cfgname_index (pg_catalog.pg_ts_config) 16.00 KiB 40.00 KiB 1.00 0.00 ~16.00 KiB 90
34 pg_ts_config_map_index (pg_catalog.pg_ts_config_map) 32.00 KiB 48.00 KiB 1.00 0.00 ~32.00 KiB 90
35 pg_ts_template_tmplname_index (pg_catalog.pg_ts_template) 16.00 KiB 40.00 KiB 1.00 0.00 ~16.00 KiB 90
36 publication_available_idx (v1.publication) 1.65 GiB 1.45 GiB 1.00 0.00 ~1.65 GiB 90
37 publication_end_time_program_id_with_fields_idx (v1.publication) 1.83 GiB 1.45 GiB 1.00 0.00 ~1.83 GiB 90
38 publication_expiring_idx (v1.publication) 175.60 MiB 1.45 GiB 1.00 0.00 ~175.60 MiB 90
39 publication_future_idx (v1.publication) 1.54 GiB 1.45 GiB 1.00 0.00 ~1.54 GiB 90
40 publication_program_id_type_publisher_times_idx (v1.publication) 1.49 GiB 1.45 GiB 1.00 0.00 ~1.49 GiB 90
41 publication_start_time_type_idx (v1.publication) 1.31 GiB 1.45 GiB 1.00 0.00 ~1.31 GiB 90
42 schema_config_pkey (schema_config) 16.00 KiB 392.00 KiB 1.00 0.00 ~16.00 KiB 90
43 views_control_pkey (views_control) 16.00 KiB 40.00 KiB 1.00 0.00 ~16.00 KiB 90
44 pg_extension_name_index (pg_catalog.pg_extension) 16.00 KiB 40.00 KiB 1.00 0.00 ~16.00 KiB 90
45 pg_tablespace_spcname_index (pg_catalog.pg_tablespace) 16.00 KiB 40.00 KiB 1.00 0.00 ~16.00 KiB 90
46 pg_proc_proname_args_nsp_index (pg_catalog.pg_proc) 264.00 KiB 640.00 KiB 1.00 0.00 ~264.00 KiB 90
47 pg_auth_members_member_role_index (pg_catalog.pg_auth_members) 16.00 KiB 40.00 KiB 1.00 0.00 ~16.00 KiB 90
48 pg_auth_members_role_member_index (pg_catalog.pg_auth_members) 16.00 KiB 40.00 KiB 1.00 0.00 ~16.00 KiB 90
49 pg_database_datname_index (pg_catalog.pg_database) 16.00 KiB 40.00 KiB 1.00 0.00 ~16.00 KiB 90
50 pg_shdescription_o_c_index (pg_catalog.pg_shdescription) 16.00 KiB 40.00 KiB 1.00 0.00 ~16.00 KiB 90

Conclusions

  • [P1] Total index (btree only) bloat estimation is ~5.60 GiB, it is 30.95% of the overall size of all indexes and 19.08% of the DB size. Removing the index bloat will reduce the total DB size down to ~23.74 GiB. Free disk space will be increased by ~5.60 GiB. Total size of indexes is 1.45 times bigger than it could be. Notice that this is only an estimation, sometimes it may be significantly off.

  • [P1] The following 3 indexes have significant size (>1 MiB) and bloat estimate > 90.00%:

    • v1.program_season_id_idx: size 781.11 MiB, can be reduced 17.39 times, by ~740.70 MiB (~94.25%)
    • v1.program_series_id_visible_not_expired_idx: size 251.38 MiB, can be reduced 52.32 times, by ~247.06 MiB (~98.09%)
    • v1.playcount_pkey: size 8.86 MiB, can be reduced 189.00 times, by ~8.83 MiB (~99.47%)

  • [P2] There are 15 indexes with size > 1 MiB and index bloat estimate >= 40.00% and < 90.00%:

    • v1.program_series_id_idx: size 679.89 MiB, can be reduced 9.06 times, by ~612.34 MiB (~88.96%)
    • v1.program_series_id_season_id_visible_idx: size 673.40 MiB, can be reduced 6.51 times, by ~580.40 MiB (~84.64%)
    • v1.program_series_id_visible: size 583.11 MiB, can be reduced 7.82 times, by ~515.98 MiB (~87.20%)
    • v1.program_series_id_visible_idx: size 581.49 MiB, can be reduced 7.79 times, by ~514.36 MiB (~87.17%)
    • v1.publication_program_id_idx: size 626.43 MiB, can be reduced 2.58 times, by ~408.04 MiB (~61.28%)
    • etc.

Recommendations

  • [P1] Reduce and prevent a high level of index bloat:

    • to prevent a high level of bloat in the future, tune autovacuum: consider more aggressive autovacuum settings (see F001);
    • eliminate or reduce the current index bloat using one of the approaches listed below.

  • The following indexes have size > 1 MiB and index bloat estimate > 40.00%. Use this list to reduce the bloat applying one of the approaches described below. Here are these indexes: v1.publication_program_id_idx, v1.publication_pkey, v1.publication_catalog_tag_program_id_idx, v1.playcount_pkey, pg_catalog.pg_depend_depender_index, pg_catalog.pg_attribute_relid_attnam_index, pg_catalog.pg_depend_reference_index.

  • And the following indexes also have size > 1 MiB and index bloat estimate > 40.00%. However, they belong to the highly bloated tables (see F004), so if you plan to process those tables you may not need to use this additional list. Here are these indexes: v1.program_season_id_idx, v1.program_series_id_idx, v1.program_series_id_season_id_visible_idx, v1.program_series_id_visible, v1.program_series_id_visible_idx, v1.program_series_id_visible_not_expired_idx, v1.program_pkey, v1.program_id_series_id_idx, v1.program_id_not_hidden_idx, v1.program_episode_number_series_id_idx, v1.series_pkey.

  • If you want to get exact bloat numbers, clone the database, get index sizes, then apply database-wide VACUUM FULL (it eliminates all the bloat), and get new table sizes. Then compare old and new numbers.

  • To reduce the index bloat, consider one of the following approaches:

    • VACUUM FULL (:warning: requires downtime / maintenance window),
    • REINDEX (REINDEX INDEX, REINDEX TABLE; ⚠️ requires downtime / maintenance window),
    • recreating indexes online using CREATE INDEX CONCURRENTLY, DROP INDEX CONCURRENTLY and renaming (not trivial for indexes supporting PK, FK) // REINDEX CONCURRENTLY is available in Postgres 12+,
    • one of the tools reducing the bloat online, without interrupting the operations:

  • Read more on this topic:

  Table of contents

F008 Autovacuum: Resource Usage

Observations

Data collected: 2020-08-17 12:10:50 +0300 EEST

Settings

Setting name Value Unit Pretty value
autovacuum_max_workers 3
autovacuum_vacuum_cost_delay 20 ms
autovacuum_vacuum_cost_limit 1200
autovacuum_work_mem -1 kB
log_autovacuum_min_duration 10000 ms
maintenance_work_mem 1064702 kB 1.02 GiB
max_connections 5000
shared_buffers 2029589 8kB 15.49 GiB
vacuum_cost_limit 200
work_mem 4096 kB 4.00 MiB

CPU

Cpu count you can see in report A001

RAM

Ram amount you can see in report A001

Max workers memory: 4 GiB

DISK

⚠️ Warning: collection of current impact on disks is not yet implemented. Please refer to Postgres logs and see current read and write IO bandwidth caused by autovacuum.

Conclusions

Recommendations

  Table of contents

G001 Memory-related Settings

Observations

Data collected: 2020-08-17 12:11:33 +0300 EEST

Master (programs-search-api-rds-test.c2t.yle.fi)

Setting name Value Unit Pretty value
autovacuum_max_workers 3
autovacuum_work_mem -1 kB
effective_cache_size 4059178 8kB 30.97 GiB
maintenance_work_mem 1064702 kB 1.02 GiB
max_connections 5000
shared_buffers 2029589 8kB 15.49 GiB
temp_buffers 1024 8kB 8.00 MiB
work_mem 4096 kB 4.00 MiB

Conclusions

Recommendations

  Table of contents

G002 Connections and Current Activity

Observations

Data collected: 2020-08-17 12:11:37 +0300 EEST

Master (programs-search-api-rds-test.c2t.yle.fi)

# User DB Current state Count State changed >1m ago State changed >1h ago Tx age >1m Tx age >1h
1 ALL users ALL databases ALL states 47 33 0 0 0
2 ALL users ALL databases ALL states 4 0 0 0 0
3 ALL users ALL databases ALL states 4 0 0 0 0
4 programs ALL databases active 1 0 0 0 0
5 rdsadmin ALL databases ALL states 1 0 0 0 0
6 rdsadmin ALL databases ALL states 1 0 0 0 0
7 programs ALL databases idle 40 33 0 0 0
8 rdsadmin ALL databases idle 1 0 0 0 0
9 programs programs active 1 0 0 0 0
10 programs programs idle 40 33 0 0 0
11 rdsadmin rdsadmin idle 1 0 0 0 0

Conclusions

Recommendations

All good, no recommendations here.

  Table of contents

G003 Timeouts, Locks, Deadlocks

Observations

Data collected: 2020-08-17 12:11:40 +0300 EEST

Master (programs-search-api-rds-test.c2t.yle.fi)

Timeouts

Setting name Value Unit Pretty value
authentication_timeout 60 s
idle_in_transaction_session_timeout 86400000 ms
statement_timeout 0 ms

Locks

Setting name Value Unit Pretty value
deadlock_timeout 1000 ms
lock_timeout 0 ms
max_locks_per_transaction 64
max_pred_locks_per_page 2
max_pred_locks_per_relation -2
max_pred_locks_per_transaction 64

Databases data

# Database Conflicts ▼ Deadlocks Stats reset at Stat reset
1 programs 0 128 2019-03-29T09:30:56.990078+00:00 506 days 23:40:47
2 rdsadmin 0 0 2019-01-23T19:10:11.708222+00:00 571 days 14:01:32
3 postgres 0 0 2019-01-23T19:10:10.497522+00:00 571 days 14:01:33

Conclusions

Recommendations

  Table of contents

H001 Invalid Indexes

Observations

Data collected: 2020-08-17 12:11:44 +0300 EEST
Current database: programs

Master (programs-search-api-rds-test.c2t.yle.fi)

Invalid indexes not found

Conclusions

Recommendations

  Table of contents

H002 Unused Indexes

Observations

Data collected: 2020-08-17 12:11:47 +0300 EEST
Current database: programs

Stats reset: 1 year 4 mons 18 days 23:41:00 ago (2019-03-29 09:30:57 +0000 +0000)

Never Used Indexes

Nothing found.

Rarely Used Indexes

# Table Index programs-search-api-rds-test.c2t.yle.fi usage ▼ Index size Table size Comment Supports FK
  =====TOTAL===== 22.14 GiB 37.50 GiB
1 v1.publication v1.publication_end_time_program_id_with_fields_idx scans: 13,012,023/hour, writes: 137,971,788/hour 1.84 GiB 1.45 GiB Low Scans, High Writes
2 v1.publication v1.publication_available_idx scans: 123/hour, writes: 137,971,788/hour 1.66 GiB 1.45 GiB Low Scans, High Writes
3 v1.publication v1.publication_future_idx scans: 3,457,356/hour, writes: 137,971,788/hour 1.55 GiB 1.45 GiB Low Scans, High Writes
4 v1.publication v1.publication_type_media_times_idx scans: 2,492/hour, writes: 137,971,788/hour 1.48 GiB 1.45 GiB Low Scans, High Writes
5 v1.publication v1.publication_type_media_available_end_time_start_time_idx scans: 1,666/hour, writes: 137,971,788/hour 1.43 GiB 1.45 GiB Low Scans, High Writes
6 v1.publication v1.publication_start_time_type_idx scans: 9,906,426/hour, writes: 137,971,788/hour 1.32 GiB 1.45 GiB Low Scans, High Writes
7 v1.program v1.program_season_id_idx scans: 2,956,459/hour, writes: 1,577,503,599/hour 781.11 MiB 2.70 GiB Low Scans, High Writes
8 v1.program v1.program_series_id_idx scans: 1,300,001/hour, writes: 1,577,503,599/hour 679.89 MiB 2.70 GiB Low Scans, High Writes
9 v1.program v1.program_series_id_season_id_visible_idx scans: 212,851/hour, writes: 1,577,503,599/hour 673.40 MiB 2.70 GiB Low Scans, High Writes
10 v1.publication v1.publication_program_id_idx scans: 26,238,393/hour, writes: 137,971,788/hour 627.48 MiB 1.45 GiB Low Scans, High Writes
11 v1.program v1.program_series_id_visible scans: 47,722/hour, writes: 1,577,503,599/hour 583.11 MiB 2.70 GiB Low Scans, High Writes
12 v1.program v1.program_series_id_visible_idx scans: 1,482/hour, writes: 1,577,503,599/hour 581.49 MiB 2.70 GiB Low Scans, High Writes
13 v1.program v1.program_id_series_id_idx scans: 1,036,649,999/hour, writes: 1,577,503,599/hour 318.40 MiB 2.70 GiB Low Scans, High Writes
14 v1.program v1.program_series_id_visible_not_expired_idx scans: 9,868,000/hour, writes: 1,577,503,599/hour 251.38 MiB 2.70 GiB Low Scans, High Writes
15 v1.publication v1.publication_expiring_idx scans: 2,167,523/hour, writes: 137,971,788/hour 175.98 MiB 1.45 GiB Low Scans, High Writes
16 v1.program v1.program_episode_number_series_id_idx scans: 9,915/hour, writes: 1,577,503,599/hour 19.22 MiB 2.70 GiB Low Scans, High Writes
17 v1.publication v1.publication_catalog_tag_program_id_idx scans: 168,045/hour, writes: 137,971,788/hour 17.27 MiB 1.45 GiB Low Scans, High Writes
18 v1.publication v1.publication_program_id_type_publisher_times_idx scans: 1,926,078,578/hour, writes: 137,971,788/hour 1.50 GiB 1.45 GiB Seldom Used Large Indexes
19 v1.publication v1.epg_schedule_idx scans: 7,988,486/hour, writes: 137,971,788/hour 6.81 GiB 1.45 GiB High-Write Large Non-Btree

Conclusions

Recommendations

All good, no recommendations here.

  Table of contents

H003 Non-indexed Foreign Keys

Observations

Data collected: 2020-08-17 12:11:51 +0300 EEST
Current database: programs

Master (programs-search-api-rds-test.c2t.yle.fi)

Nothing found

Conclusions

Recommendations

  Table of contents

H004 Redundant Indexes

Observations

Data collected: 2020-08-17 12:11:56 +0300 EEST
Current database: programs

Stats reset: 1 year 4 mons 18 days 23:42:00 ago (2019-03-29 09:30:57 +0000 +0000)

# Table Index Redundant to programs-search-api-rds-test.c2t.yle.fi usage ▼ Index size Table size Supports FK
  =====TOTAL===== 3.18 GiB 8.29 GiB
1 v1.publication v1.publication_type_media_available_end_time_start_time_idx v1.publication_type_media_times_idx
 0 1.43 GiB 1.45 GiB
2 v1.publication v1.publication_program_id_idx v1.publication_program_id_type_publisher_times_idx
v1.publication_program_id_type_times_idx
 0 627.48 MiB 1.45 GiB
3 v1.program v1.program_series_id_visible v1.program_series_id_season_id_visible_idx
 0 583.11 MiB 2.70 GiB
4 v1.program v1.program_series_id_visible_idx v1.program_series_id_season_id_visible_idx
v1.program_series_id_visible
 0 581.49 MiB 2.70 GiB

Conclusions

  • [P2] 4 redundant index(es) have been found and their total size exceeds 5.00% of the database size.

Recommendations

  • [P2] Use the database migration provided below to drop the redundant indexes. Keep in mind, that under load, it is recommended to use DROP INDEX CONCURRENTLY (and CREATE INDEX CONCURRENTLY if reverting is needed) to avoid blocking issues.
  • Be careful dropping the indexes. If you have multiple setups of your software, the analysis of just a single setup might be not enough. Some indexes might be used (and therefore, needed) only on a limited number of setups. Also, in some cases, developers prepare indexes for new features in advance – in such cases, dropping those indexes is not a good idea.
  • If there are some doubts, consider a more careful approach. Before actualdropping, indexes disable listed in this report. For this, use queries like `UPDATE pg_index SET indisvalid = false WHERE indexrelid::regclass = (select oid from pg_class where relname = 'u_users_email');. Indexes will continue to get updates. In case of some performance degradations, re-enable the corresponding indexes, setting indisvalid to true. If everything looks fine, after a significant period of observations, proceed with DROP INDEX CONCURRENTLY.
  • "DO" database migrations
DROP INDEX CONCURRENTLY v1.program_series_id_visible; -- 583 MB, v1.program_series_id_season_id_visible_idx, table v1.program  
DROP INDEX CONCURRENTLY v1.program_series_id_visible_idx; -- 581 MB, v1.program_series_id_season_id_visible_idx, v1.program_series_id_visible, table v1.program  
DROP INDEX CONCURRENTLY v1.publication_program_id_idx; -- 627 MB, v1.publication_program_id_type_publisher_times_idx, v1.publication_program_id_type_times_idx, table v1.publication  
DROP INDEX CONCURRENTLY v1.publication_type_media_available_end_time_start_time_idx; -- 1457 MB, v1.publication_type_media_times_idx, table v1.publication
  • "UNDO" database migrations
CREATE INDEX CONCURRENTLY program_series_id_visible ON v1.program USING btree (series_id) WHERE (hidden = false); -- table v1.program  
CREATE INDEX CONCURRENTLY program_series_id_visible_idx ON v1.program USING btree (series_id) WHERE (hidden = false); -- table v1.program  
CREATE INDEX CONCURRENTLY publication_program_id_idx ON v1.publication USING btree (program_id); -- table v1.publication  
CREATE INDEX CONCURRENTLY publication_type_media_available_end_time_start_time_idx ON v1.publication USING btree (type, media_available, end_time, start_time); -- table v1.publication

  Table of contents

L001 Table Sizes

Observations

Data collected: 2020-08-17 12:12:03 +0300 EEST
Current database: programs

Master (programs-search-api-rds-test.c2t.yle.fi)

The list is limited to 50 items. Total: 71.

# Table Rows ▼ Total size Table size Index(es) Size TOAST Size
  ===== TOTAL ===== ~13M 29.19 GiB 4.24 GiB 24.95 GiB 1.22 MiB
1 v1.publication ~9M 22.07 GiB (75.61%) 1.45 GiB (34.22%) 20.62 GiB (82.64%) 8.00 KiB (0.64%)
2 v1.program ~4M 6.99 GiB (23.93%) 2.70 GiB (63.67%) 4.29 GiB (17.19%) 8.00 KiB (0.64%)
3 v1.item_concepts ~384K 76.83 MiB (0.26%) 53.97 MiB (1.24%) 22.85 MiB (0.09%) 8.00 KiB (0.64%)
4 v1.series ~27K 14.34 MiB (0.05%) 12.58 MiB (0.29%) 1.75 MiB (0.01%) 8.00 KiB (0.64%)
5 pg_catalog.pg_statistic ~5K 12.96 MiB (0.04%) 12.06 MiB (0.28%) 560.00 KiB (0.00%) 360.00 KiB (28.85%)
6 v1.playcount ~2K 9.52 MiB (0.03%) 640.00 KiB (0.01%) 8.89 MiB (0.03%) 8.00 KiB (0.64%)
7 v1.season ~9K 7.86 MiB (0.03%) 7.26 MiB (0.17%) 600.00 KiB (0.00%) 8.00 KiB (0.64%)
8 pg_catalog.pg_depend ~8K 2.94 MiB (0.01%) 544.00 KiB (0.01%) 2.41 MiB (0.01%)
9 pg_catalog.pg_attribute ~4K 2.83 MiB (0.01%) 616.00 KiB (0.01%) 2.23 MiB (0.01%)
10 pg_catalog.pg_class ~443 1.19 MiB (0.00%) 216.00 KiB (0.00%) 0.98 MiB (0.00%)
11 pg_catalog.pg_proc ~3K 0.97 MiB (0.00%) 640.00 KiB (0.01%) 344.00 KiB (0.00%) 8.00 KiB (0.64%)
12 pg_catalog.pg_rewrite ~122 816.00 KiB (0.00%) 128.00 KiB (0.00%) 32.00 KiB (0.00%) 656.00 KiB (52.56%)
13 pg_catalog.pg_type ~425 688.00 KiB (0.00%) 136.00 KiB (0.00%) 552.00 KiB (0.00%)
14 pg_catalog.pg_collation ~2K 600.00 KiB (0.00%) 440.00 KiB (0.01%) 160.00 KiB (0.00%)
15 pg_catalog.pg_description ~5K 552.00 KiB (0.00%) 360.00 KiB (0.01%) 184.00 KiB (0.00%) 8.00 KiB (0.64%)
16 schema_config ~1 496.00 KiB (0.00%) 392.00 KiB (0.01%) 16.00 KiB (0.00%) 88.00 KiB (7.05%)
17 pg_catalog.pg_index ~206 296.00 KiB (0.00%) 88.00 KiB (0.00%) 208.00 KiB (0.00%)
18 pg_catalog.pg_shdepend [pg_global] ~34 256.00 KiB (0.00%) 40.00 KiB (0.00%) 216.00 KiB (0.00%)
19 pg_catalog.pg_operator ~788 240.00 KiB (0.00%) 152.00 KiB (0.00%) 88.00 KiB (0.00%)
20 pg_catalog.pg_amop ~722 200.00 KiB (0.00%) 88.00 KiB (0.00%) 112.00 KiB (0.00%)
21 pg_catalog.pg_namespace ~495 176.00 KiB (0.00%) 96.00 KiB (0.00%) 80.00 KiB (0.00%)
22 pg_catalog.pg_constraint ~2 128.00 KiB (0.00%) 40.00 KiB (0.00%) 80.00 KiB (0.00%) 8.00 KiB (0.64%)
23 pg_catalog.pg_amproc ~458 128.00 KiB (0.00%) 64.00 KiB (0.00%) 64.00 KiB (0.00%)
24 pg_catalog.pg_conversion ~132 104.00 KiB (0.00%) 56.00 KiB (0.00%) 48.00 KiB (0.00%)
25 pg_catalog.pg_opclass ~134 88.00 KiB (0.00%) 56.00 KiB (0.00%) 32.00 KiB (0.00%)
26 pg_catalog.pg_cast ~226 80.00 KiB (0.00%) 48.00 KiB (0.00%) 32.00 KiB (0.00%)
27 pg_catalog.pg_opfamily ~116 80.00 KiB (0.00%) 48.00 KiB (0.00%) 32.00 KiB (0.00%)
28 pg_catalog.pg_ts_config_map ~304 80.00 KiB (0.00%) 48.00 KiB (0.00%) 32.00 KiB (0.00%)
29 pg_catalog.pg_extension ~1 72.00 KiB (0.00%) 40.00 KiB (0.00%) 32.00 KiB (0.00%)
30 pg_catalog.pg_am ~6 72.00 KiB (0.00%) 40.00 KiB (0.00%) 32.00 KiB (0.00%)
31 pg_catalog.pg_language ~4 72.00 KiB (0.00%) 40.00 KiB (0.00%) 32.00 KiB (0.00%)
32 pg_catalog.pg_authid [pg_global] ~9 72.00 KiB (0.00%) 40.00 KiB (0.00%) 32.00 KiB (0.00%)
33 pg_catalog.pg_database [pg_global] ~2 72.00 KiB (0.00%) 40.00 KiB (0.00%) 32.00 KiB (0.00%)
34 pg_catalog.pg_tablespace [pg_global] ~2 72.00 KiB (0.00%) 40.00 KiB (0.00%) 32.00 KiB (0.00%)
35 pg_catalog.pg_auth_members [pg_global] ~3 72.00 KiB (0.00%) 40.00 KiB (0.00%) 32.00 KiB (0.00%)
36 pg_catalog.pg_ts_config ~16 72.00 KiB (0.00%) 40.00 KiB (0.00%) 32.00 KiB (0.00%)
37 pg_catalog.pg_ts_dict ~16 72.00 KiB (0.00%) 40.00 KiB (0.00%) 32.00 KiB (0.00%)
38 pg_catalog.pg_ts_parser ~1 72.00 KiB (0.00%) 40.00 KiB (0.00%) 32.00 KiB (0.00%)
39 pg_catalog.pg_ts_template ~5 72.00 KiB (0.00%) 40.00 KiB (0.00%) 32.00 KiB (0.00%)
40 pg_catalog.pg_shdescription [pg_global] ~1 64.00 KiB (0.00%) 40.00 KiB (0.00%) 16.00 KiB (0.00%) 8.00 KiB (0.64%)
41 views_control ~6 64.00 KiB (0.00%) 40.00 KiB (0.00%) 16.00 KiB (0.00%) 8.00 KiB (0.64%)
42 pg_catalog.pg_init_privs ~162 64.00 KiB (0.00%) 48.00 KiB (0.00%) 16.00 KiB (0.00%)
43 pg_catalog.pg_trigger ~0 64.00 KiB (0.00%) 8.00 KiB (0.00%) 48.00 KiB (0.00%) 8.00 KiB (0.64%)
44 pg_catalog.pg_aggregate ~138 64.00 KiB (0.00%) 48.00 KiB (0.00%) 16.00 KiB (0.00%)
45 pg_catalog.pg_pltemplate [pg_global] ~8 56.00 KiB (0.00%) 40.00 KiB (0.00%) 16.00 KiB (0.00%)
46 pg_catalog.pg_range ~6 56.00 KiB (0.00%) 40.00 KiB (0.00%) 16.00 KiB (0.00%)
47 pg_catalog.pg_attrdef ~0 48.00 KiB (0.00%) 8.00 KiB (0.00%) 32.00 KiB (0.00%) 8.00 KiB (0.64%)
48 v1.flyway_schema_history ~0 48.00 KiB (0.00%) 8.00 KiB (0.00%) 32.00 KiB (0.00%) 8.00 KiB (0.64%)
49 pg_catalog.pg_db_role_setting [pg_global] ~0 32.00 KiB (0.00%) 8.00 KiB (0.00%) 16.00 KiB (0.00%) 8.00 KiB (0.64%)
50 pg_catalog.pg_statistic_ext ~0 32.00 KiB (0.00%) 0.00 bytes (0.00%) 24.00 KiB (0.00%) 8.00 KiB (0.64%)

Conclusions

Recommendations

  Table of contents

L003 Integer (int2, int4) Out-of-range Risks in PKs

Observations

Data collected: 2020-08-17 12:12:07 +0300 EEST
Current database: programs

Nothing found

Conclusions

Recommendations

All good, no recommendations here.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment