# pg-tuna
[](https://github.com/delijati/pg-tuna/actions/workflows/ci.yml)
[](https://pypi.python.org/pypi/pg-tuna/)
```
PostgreSQL + <({(>(
```
pg-tuna is a cli program to generate optimal PostgreSQL and AWS PostgreSQL RDS
settings. It outputs for AWS RDS already the needed units conversion so the
settings can be easily applied.
It is based on excelent work of:
- https://github.com/le0pard/pgtune
- https://github.com/gregs1104/pgtune
This tool only supports Linux there is no option to choose any other platform
and why ;)
## Install && run
```
$ pip install pg-tuna
```
Run it like:
```
$ pg-tuna --db-type web --db-version 11 --memory 8 --cpu-num 8 --disk-type ssd
#-------------------------------------------------------------------------------------------------------------------------
# pg-tuna run on 2023-06-28
# Settings used: db_type = web | db_version = 11 | connections = None | total_memory = 8 | cpu_num = 8 | disk_type = ssd
# Based on 8 GB RAM, platform Linux, 200 clients and web workload
#---------------------------------------------------------- PG ----------------------------------------------------------
max_connections = 200
random_page_cost = 1.1
shared_buffers = 2048 MB
effective_cache_size = 6144 MB
work_mem = 2621 kB
maintenance_work_mem = 512 MB
min_wal_size = 1024 MB
max_wal_size = 4096 MB
checkpoint_completion_target = 0.9
wal_buffers = 16 MB
default_statistics_target = 100
max_parallel_workers_per_gather = 4.0
max_worker_processes = 8
max_parallel_workers = 8
max_parallel_maintenance_workers = 4.0
#---------------------------------------------------------- AWS ----------------------------------------------------------
max_connections = 200
random_page_cost = 1.1
shared_buffers = 262144 pages (8kB)
effective_cache_size = 786432 pages (8kB)
work_mem = 2621 kB
maintenance_work_mem = 524288 kB
min_wal_size = 1024 MB
max_wal_size = 4096 MB
checkpoint_completion_target = 0.9
wal_buffers = 2048 pages (8kB)
default_statistics_target = 100
max_parallel_workers_per_gather = 4.0
max_worker_processes = 8
max_parallel_workers = 8
max_parallel_maintenance_workers = 4.0
```
## Debugging performance
To debug performance issues we first need to indentify the slow queries. Then
we can start benchmarking them and apply changes to our code (adding indexes,
modify our ERM , or apply optimized settings to PostgreSQL)
To test queries PostgreSQL has a nice tool `pgbench`. If you like me can't ssh into
the PostgreSQL server and you don't like to install PostgreSQL to get `pgbench`
use the included `Dockerfile` (it will only create a 8MB image).
https://www.PostgreSQLql.org/docs/10/pgbench.html
### pgbench
```bash
$ docker build -t pg_tuna/pgbench .
```
Set settings in `env.list` to connect to your PostgreSQL instance
We use a query defined in `bench/select_count.sql` to run our performance
tests.
Run
```
$ docker run -it --env-file ./env.list -v `pwd`/bench:/var/bench pg_tuna/pgbench pgbench -c 10 -j 4 -t 100 -f /var/bench/select_count.sql
```
Run via local jumphost
```
$ docker run -it --network="host" --env-file ./env.list -v `pwd`/bench:/var/bench pg_tuna/pgbench pgbench -c 10 -j 4 -t 100 -f /var/bench/select_count.sql
```
## Deploy
```bash
$ pip install build twine
$ python -m build
$ twine upload -r pypi dist/*
```
Raw data
{
"_id": null,
"home_page": "https://github.com/delijati/py-tuna",
"name": "pg-tuna",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "",
"author": "Josip Delic",
"author_email": "",
"download_url": "https://files.pythonhosted.org/packages/c1/34/1018b648c5a72e83c1b39ebbab1471e7da461708fa1357d79806165aa925/pg_tuna-0.1.1.tar.gz",
"platform": null,
"description": "# pg-tuna\n\n[](https://github.com/delijati/pg-tuna/actions/workflows/ci.yml)\n[](https://pypi.python.org/pypi/pg-tuna/)\n\n```\nPostgreSQL + <({(>(\n```\n\npg-tuna is a cli program to generate optimal PostgreSQL and AWS PostgreSQL RDS\nsettings. It outputs for AWS RDS already the needed units conversion so the\nsettings can be easily applied.\n\nIt is based on excelent work of:\n\n- https://github.com/le0pard/pgtune\n- https://github.com/gregs1104/pgtune\n\nThis tool only supports Linux there is no option to choose any other platform\nand why ;)\n\n## Install && run\n\n```\n$ pip install pg-tuna\n```\n\nRun it like:\n\n```\n$ pg-tuna --db-type web --db-version 11 --memory 8 --cpu-num 8 --disk-type ssd\n\n#-------------------------------------------------------------------------------------------------------------------------\n# pg-tuna run on 2023-06-28\n# Settings used: db_type = web | db_version = 11 | connections = None | total_memory = 8 | cpu_num = 8 | disk_type = ssd \n# Based on 8 GB RAM, platform Linux, 200 clients and web workload\n#---------------------------------------------------------- PG ----------------------------------------------------------\n\n max_connections = 200\n random_page_cost = 1.1\n shared_buffers = 2048 MB\n effective_cache_size = 6144 MB\n work_mem = 2621 kB\n maintenance_work_mem = 512 MB\n min_wal_size = 1024 MB\n max_wal_size = 4096 MB\n checkpoint_completion_target = 0.9\n wal_buffers = 16 MB\n default_statistics_target = 100\n max_parallel_workers_per_gather = 4.0\n max_worker_processes = 8\n max_parallel_workers = 8\n max_parallel_maintenance_workers = 4.0\n\n#---------------------------------------------------------- AWS ----------------------------------------------------------\n\n max_connections = 200\n random_page_cost = 1.1\n shared_buffers = 262144 pages (8kB)\n effective_cache_size = 786432 pages (8kB)\n work_mem = 2621 kB\n maintenance_work_mem = 524288 kB\n min_wal_size = 1024 MB\n max_wal_size = 4096 MB\n checkpoint_completion_target = 0.9\n wal_buffers = 2048 pages (8kB)\n default_statistics_target = 100\n max_parallel_workers_per_gather = 4.0\n max_worker_processes = 8\n max_parallel_workers = 8\n max_parallel_maintenance_workers = 4.0\n\n```\n\n## Debugging performance\n\nTo debug performance issues we first need to indentify the slow queries. Then\nwe can start benchmarking them and apply changes to our code (adding indexes,\nmodify our ERM , or apply optimized settings to PostgreSQL)\n\nTo test queries PostgreSQL has a nice tool `pgbench`. If you like me can't ssh into\nthe PostgreSQL server and you don't like to install PostgreSQL to get `pgbench`\nuse the included `Dockerfile` (it will only create a 8MB image).\n\nhttps://www.PostgreSQLql.org/docs/10/pgbench.html\n\n### pgbench\n```bash\n$ docker build -t pg_tuna/pgbench .\n```\nSet settings in `env.list` to connect to your PostgreSQL instance\n\nWe use a query defined in `bench/select_count.sql` to run our performance\ntests.\n\nRun\n```\n$ docker run -it --env-file ./env.list -v `pwd`/bench:/var/bench pg_tuna/pgbench pgbench -c 10 -j 4 -t 100 -f /var/bench/select_count.sql\n```\n\nRun via local jumphost\n```\n$ docker run -it --network=\"host\" --env-file ./env.list -v `pwd`/bench:/var/bench pg_tuna/pgbench pgbench -c 10 -j 4 -t 100 -f /var/bench/select_count.sql\n```\n\n## Deploy\n```bash\n$ pip install build twine\n$ python -m build\n$ twine upload -r pypi dist/*\n```\n",
"bugtrack_url": null,
"license": "",
"summary": "pg_tuna",
"version": "0.1.1",
"project_urls": {
"Homepage": "https://github.com/delijati/py-tuna"
},
"split_keywords": [],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "6e40c523a827628769ac1a93091eb9d4940a1a1808778f038f7e63d67e52c453",
"md5": "37f4ca7b738e6cfbb4338a6e326c07d3",
"sha256": "cc15d8cb3852f457d3ca9d691fd0cf3cf4e511bdbda733783be25e7ee683b887"
},
"downloads": -1,
"filename": "pg_tuna-0.1.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "37f4ca7b738e6cfbb4338a6e326c07d3",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 7016,
"upload_time": "2023-06-29T10:59:37",
"upload_time_iso_8601": "2023-06-29T10:59:37.248366Z",
"url": "https://files.pythonhosted.org/packages/6e/40/c523a827628769ac1a93091eb9d4940a1a1808778f038f7e63d67e52c453/pg_tuna-0.1.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "c1341018b648c5a72e83c1b39ebbab1471e7da461708fa1357d79806165aa925",
"md5": "267bb18a5ce0e1ca998f15dbc1f944dc",
"sha256": "7c7ae8231022dbf8f4150528124927a9c9a0be991b18f04fdb00649aff0ccfa1"
},
"downloads": -1,
"filename": "pg_tuna-0.1.1.tar.gz",
"has_sig": false,
"md5_digest": "267bb18a5ce0e1ca998f15dbc1f944dc",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 7222,
"upload_time": "2023-06-29T10:59:38",
"upload_time_iso_8601": "2023-06-29T10:59:38.791349Z",
"url": "https://files.pythonhosted.org/packages/c1/34/1018b648c5a72e83c1b39ebbab1471e7da461708fa1357d79806165aa925/pg_tuna-0.1.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-06-29 10:59:38",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "delijati",
"github_project": "py-tuna",
"github_not_found": true,
"lcname": "pg-tuna"
}
JSON
