In a previous tip, we learned that YSQL configuration parameters play an important role in optimizing and enhancing database performance.
And we saw how the YSQL SHOW command allows inspection of the current value of any parameter.
Another option to view run-time parameter settings is by querying the pg_settings system table.
Example:
yugabyte=# SELECT name, setting, short_desc FROM pg_settings WHERE name = 'max_connections';
name | setting | short_desc
-----------------+---------+----------------------------------------------------
max_connections | 300 | Sets the maximum number of concurrent connections.
(1 row)
We can also do wildcard searches! Say I want to see all of the YugabyteDB specific settings. They are prefixed with “yb_”.
yugabyte=# SELECT name, setting FROM pg_settings WHERE name ILIKE 'yb%' ORDER BY name;
name | setting
----------------------------------------------+-------------------------------------------
yb_binary_restore | off
yb_bnl_batch_size | 1
yb_bnl_enable_hashing | on
yb_bypass_cond_recheck | on
yb_debug_log_catcache_events | off
yb_debug_log_docdb_requests | off
yb_debug_log_internal_restarts | off
yb_debug_report_error_stacktrace | off
yb_default_copy_from_rows_per_transaction | 20000
yb_disable_transactional_writes | off
yb_disable_wait_for_backends_catalog_version | off
yb_effective_transaction_isolation_level | read committed
yb_enable_add_column_missing_default | on
yb_enable_base_scans_cost_model | off
yb_enable_create_with_table_oid | off
yb_enable_distinct_pushdown | on
yb_enable_docdb_tracing | off
yb_enable_expression_pushdown | on
yb_enable_geolocation_costing | on
yb_enable_hash_batch_in | on
yb_enable_index_aggregate_pushdown | on
yb_enable_memory_tracking | on
yb_enable_optimizer_statistics | off
yb_enable_pg_locks | on
yb_enable_sequence_pushdown | on
yb_enable_upsert_mode | off
yb_fetch_row_limit | 1024
yb_fetch_size_limit | 0
yb_follower_read_staleness_ms | 30000
yb_format_funcs_include_yb_metadata | off
yb_index_state_flags_update_delay | 0
yb_is_client_ysqlconnmgr | off
yb_lock_pk_single_rpc | off
yb_locks_max_transactions | 16
yb_locks_min_txn_age | 1000
yb_make_next_ddl_statement_nonbreaking | off
yb_network_fetch_cost | 4
yb_non_ddl_txn_for_sys_tables_allowed | off
yb_pg_batch_detection_mechanism | detect_by_peeking
yb_planner_custom_plan_for_partition_pruning | on
yb_plpgsql_disable_prefetch_in_for_query | off
yb_prefer_bnl | off
yb_pushdown_is_not_null | on
yb_pushdown_strict_inequality | on
yb_read_from_followers | off
yb_test_block_index_phase |
yb_test_fail_index_state_change |
yb_test_fail_next_ddl | off
yb_test_planner_custom_plan_threshold | 5
yb_test_system_catalogs_creation | off
yb_transaction_priority | 0.000000000 (Normal priority transaction)
yb_transaction_priority_lower_bound | 0
yb_transaction_priority_upper_bound | 1
yb_wait_for_backends_catalog_version_timeout | 300000
yb_xcluster_consistency_level | database
(55 rows)
Have Fun!
