This is an automated email from the ASF dual-hosted git repository. rcordier pushed a commit to branch postgresql in repository https://gitbox.apache.org/repos/asf/james-project.git
commit b81b4a640e07976bd44942040bed52b6a765236e Author: Tung Tran <[email protected]> AuthorDate: Wed Jul 3 12:49:21 2024 +0700 [Antora] [PGSQL] Add Performance benchmarks section to postgresql doc --- .../james-imap-base-performance-postgres.png | Bin 0 -> 376870 bytes .../assets/images/postgres_pg_stat_statements.png | Bin 0 -> 208175 bytes docs/modules/servers/nav.adoc | 2 ++ .../postgres/benchmark/benchmark_prepare.adoc | 40 +++++++++++++++++++++ .../pages/postgres/benchmark/db-benchmark.adoc | 8 +++++ .../servers/pages/postgres/benchmark/index.adoc | 9 +++-- .../pages/postgres/benchmark/james-benchmark.adoc | 10 ++++++ 7 files changed, 67 insertions(+), 2 deletions(-) diff --git a/docs/modules/servers/assets/images/james-imap-base-performance-postgres.png b/docs/modules/servers/assets/images/james-imap-base-performance-postgres.png new file mode 100644 index 0000000000..47bb0eb2c9 Binary files /dev/null and b/docs/modules/servers/assets/images/james-imap-base-performance-postgres.png differ diff --git a/docs/modules/servers/assets/images/postgres_pg_stat_statements.png b/docs/modules/servers/assets/images/postgres_pg_stat_statements.png new file mode 100644 index 0000000000..4cc1e46989 Binary files /dev/null and b/docs/modules/servers/assets/images/postgres_pg_stat_statements.png differ diff --git a/docs/modules/servers/nav.adoc b/docs/modules/servers/nav.adoc index 2ea41be43f..03a4fd2900 100644 --- a/docs/modules/servers/nav.adoc +++ b/docs/modules/servers/nav.adoc @@ -120,4 +120,6 @@ *** xref:postgres/operate/index.adoc[] *** xref:postgres/extending/index.adoc[] *** xref:postgres/benchmark/index.adoc[] +**** xref:postgres/benchmark/db-benchmark.adoc[] +**** xref:postgres/benchmark/james-benchmark.adoc[] ** xref:test.adoc[] diff --git a/docs/modules/servers/pages/postgres/benchmark/benchmark_prepare.adoc b/docs/modules/servers/pages/postgres/benchmark/benchmark_prepare.adoc new file mode 100644 index 0000000000..5257ef4840 --- /dev/null +++ b/docs/modules/servers/pages/postgres/benchmark/benchmark_prepare.adoc @@ -0,0 +1,40 @@ +=== Postgresql prepare benchmark + +==== Install extension pg_stat_statements + +The `pg_stat_statements` extension provides a means for tracking execution statistics of all SQL statements executed by a server. +The extension is useful for identifying high-traffic queries and for monitoring the performance of the server. +For more information, see the [PostgreSQL documentation](https://www.postgresql.org/docs/current/pgstatstatements.html). + +To install the extension, connect to the database and run the following query: + +[source,sql] +---- +create extension if not exists pg_stat_statements; +alter system set shared_preload_libraries='pg_stat_statements'; + +-- restart postgres +-- optional +alter system set pg_stat_statements.max = 100000; +alter system set pg_stat_statements.track = 'all'; +---- + +To reset statistics, use: `select pg_stat_statements_reset()`; + +The response fields that we are interested in are: + +- `query`: Text of a representative statement + +- `calls`: Number of times the statement was executed + +- `total_exec_time`, `mean_exec_time`, `min_exec_time`, `max_exec_time` + +To view the statistics, run the following query: + +```sql +select query, mean_exec_time, total_exec_time, calls from pg_stat_statements order by total_exec_time desc; +``` + +The result sample: + +image::postgres_pg_stat_statements.png[Storage responsibilities for the {server-name}] \ No newline at end of file diff --git a/docs/modules/servers/pages/postgres/benchmark/db-benchmark.adoc b/docs/modules/servers/pages/postgres/benchmark/db-benchmark.adoc new file mode 100644 index 0000000000..5e9bf216f7 --- /dev/null +++ b/docs/modules/servers/pages/postgres/benchmark/db-benchmark.adoc @@ -0,0 +1,8 @@ += Postgresql James server -- Database benchmarks +:navtitle: Database benchmarks + +:backend-name: postgres +:server-name: Postgresql James server +:backend-database-extend-sample: PostgreSQL 16 as main database: 1 nodes (OVH instance, 2 CPU / 7 GB RAM, 160 GB SSD) + +include::partial$benchmark/db-benchmark.adoc[] \ No newline at end of file diff --git a/docs/modules/servers/pages/postgres/benchmark/index.adoc b/docs/modules/servers/pages/postgres/benchmark/index.adoc index 0b65da7671..0532346caa 100644 --- a/docs/modules/servers/pages/postgres/benchmark/index.adoc +++ b/docs/modules/servers/pages/postgres/benchmark/index.adoc @@ -1,2 +1,7 @@ -= Distributed James Postgres Server — Performance testing -:navtitle: Performance testing \ No newline at end of file += Postgresql James server — Performance testing +:navtitle: Performance testing the Postgresql James server + +:xref-base: postgres +:server-name: Postgresql James server + +include::partial$benchmark/index.adoc[] \ No newline at end of file diff --git a/docs/modules/servers/pages/postgres/benchmark/james-benchmark.adoc b/docs/modules/servers/pages/postgres/benchmark/james-benchmark.adoc new file mode 100644 index 0000000000..52bdec9769 --- /dev/null +++ b/docs/modules/servers/pages/postgres/benchmark/james-benchmark.adoc @@ -0,0 +1,10 @@ += Postgresql James server benchmark +:navtitle: James benchmarks + +:server-name: Postgresql James server +:backend-database-extend-sample: PostgreSQL 16 as main database: 1 nodes (OVH instance, 2 CPU / 7 GB RAM, 160 GB SSD) +:provision_file_url: https://github.com/apache/james-project/blob/d8225ed7c5ca8d79cde3b1c8755ee9ffcf462e29/server/apps/postgres-app/provision.sh +:benchmark_prepare_extend: servers:postgres/benchmark/benchmark_prepare.adoc +:james-imap-base-performance-picture: james-imap-base-performance-postgres.png + +include::partial$benchmark/james-benchmark.adoc[] \ No newline at end of file --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
