diff options
author | GitLab Bot <gitlab-bot@gitlab.com> | 2023-10-19 21:07:43 +0300 |
---|---|---|
committer | GitLab Bot <gitlab-bot@gitlab.com> | 2023-10-19 21:07:43 +0300 |
commit | 1e1012d3d28c426637eecc0909c415fe2c8b7c3a (patch) | |
tree | d230780752bb3b98cf4f3ede1c6f8e98c9a238c7 /doc/administration/postgresql | |
parent | 3c55affa6684311ca73bc4e3d3bfb17b7541f63b (diff) |
Add latest changes from gitlab-org/gitlab@master
Diffstat (limited to 'doc/administration/postgresql')
-rw-r--r-- | doc/administration/postgresql/external_metrics.md | 33 | ||||
-rw-r--r-- | doc/administration/postgresql/index.md | 4 |
2 files changed, 37 insertions, 0 deletions
diff --git a/doc/administration/postgresql/external_metrics.md b/doc/administration/postgresql/external_metrics.md new file mode 100644 index 00000000000..fc4c5652a18 --- /dev/null +++ b/doc/administration/postgresql/external_metrics.md @@ -0,0 +1,33 @@ +--- +stage: Data Stores +group: Database +info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/product/ux/technical-writing/#assignments +--- + +# Monitoring and logging setup for external databases + +External PostgreSQL database systems have different logging options for monitoring performance and troubleshooting, however they are not enabled by default. In this section we provide the recommendations for self-managed PostgreSQL, and recommendations for some major providers of PostgreSQL managed services. + +## Recommended PostgreSQL Logging settings + +You should enable the following logging settings: + +- `log_statement=ddl`: log changes of database model definition (DDL), such as `CREATE`, `ALTER` or `DROP` of objects. This helps track recent model changes that could be causing performance issues and identify security breaches and human errors. +- `log_lock_waits=on`: log of processes holding [locks](https://www.postgresql.org/docs/current/explicit-locking.html) for long periods, a common cause of poor query performance. +- `log_temp_files=0`: log usage of intense and unusual temporary files that can indicate poor query performance. +- `log_autovacuum_min_duration=0`: log all autovacuum executions. Autovacuum is a key component for overall PostgreSQL engine performance. Essential for troubleshooting and tuning if dead tuples are not being removed from tables. +- `log_min_duration_statement=1000`: log slow queries (slower than 1 second). + +The full description of the above parameter settings can be found in +[PostgreSQL error reporting and logging documentation](https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT). + +## Amazon RDS + +The Amazon Relational Database Service (RDS) provides a large number of [monitoring metrics](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Monitoring.html) and [logging interfaces](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Monitor_Logs_Events.html). Here are a few you should configure: + +- Change all above [recommended PostgreSQL Logging settings](#recommended-postgresql-logging-settings) through [RDS Parameter Groups](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithDBInstanceParamGroups.html). + - As the recommended logging parameters are [dynamic in RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.Parameters.html) you don't require a reboot after changing these settings. + - The PostgreSQL logs can be observed through the [RDS console](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/logs-events-streams-console.html). +- Enable [RDS performance insight](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.html) allows you to visualise your database load with many important performance metrics of a PostgreSQL database engine. +- Enable [RDS Enhanced Monitoring](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_Monitoring.OS.html) to monitor the operating system metrics. These metrics can indicate bottlenecks in your underlying hardware and OS that are impacting your database performance. + - In production environments set the monitoring interval to 10 seconds (or less) to capture micro bursts of resource usage that can be the cause of many performance issues. Set `Granularity=10` in the console or `monitoring-interval=10` in the CLI. diff --git a/doc/administration/postgresql/index.md b/doc/administration/postgresql/index.md index af0a86c3d72..483dd681b9e 100644 --- a/doc/administration/postgresql/index.md +++ b/doc/administration/postgresql/index.md @@ -30,6 +30,10 @@ your own external PostgreSQL server. Read how to [set up an external PostgreSQL instance](external.md). +When setting up an external database there are some metrics that are useful for monitoring and troubleshooting. +When setting up an external database there are monitoring and logging settings required for troubleshooting various database related issues. +Read more about [monitoring and logging setup for external Databases](external_metrics.md). + ### PostgreSQL replication and failover for Linux package installations **(PREMIUM SELF)** This setup is for when you have installed GitLab using the |