GitPlanet

Cheap and reliable Node.js hosting starts at $3/month, and $1/month static HTML hosting

Created with love in Canada, visit hostnodejs.com today

Feel like to post an Ad? Learn Details
All Projects → jfcoz → Postgresqltuner

jfcoz / Postgresqltuner

Licence: gpl-3.0
Simple script to analyse your PostgreSQL database configuration, and give tuning advice

Programming Languages

perl
6916 projects
Dockerfile
14818 projects

Labels

dockerpostgresqlsshpostgrespostgresql-databasetuningmem-buffer

Projects that are alternatives of or similar to Postgresqltuner

Guardian auth
The Guardian Authentication Implementation Using Ecto/Postgresql Elixir Phoenix [ User Authentication ]
Stars: ✭ 15 (-99.32%)
Mutual labels:  postgresql, postgres, postgresql-database
Embedded Database Spring Test
A library for creating isolated embedded databases for Spring-powered integration tests.
Stars: ✭ 196 (-91.15%)
Mutual labels:  postgresql, postgres, postgresql-database
Niklick
Rails Versioned API solution template for hipsters! (Ruby, Ruby on Rails, REST API, GraphQL, Docker, RSpec, Devise, Postgress DB)
Stars: ✭ 39 (-98.24%)
Mutual labels:  postgresql, postgres, postgresql-database
Zapatos
Zero-abstraction Postgres for TypeScript: a non-ORM database library
Stars: ✭ 448 (-79.77%)
Mutual labels:  postgresql, postgres, postgresql-database
Fullstack Apollo Express Postgresql Boilerplate
💥 A sophisticated GraphQL with Apollo, Express and PostgreSQL boilerplate project.
Stars: ✭ 1,079 (-51.26%)
Mutual labels:  postgresql, postgres, postgresql-database
Wal G
Archival and Restoration for Postgres
Stars: ✭ 1,974 (-10.84%)
Mutual labels:  postgresql, postgres
Laravel Scout Postgres
PostgreSQL Full Text Search Engine for Laravel Scout
Stars: ✭ 140 (-93.68%)
Mutual labels:  postgresql, postgres
Go Cache
This project encapsulates multiple db servers, redis、ledis、memcache、file、memory、nosql、postgresql
Stars: ✭ 143 (-93.54%)
Mutual labels:  postgresql, postgres
Postgres Meta
A RESTful API for managing your Postgres. Fetch tables, add roles, and run queries
Stars: ✭ 146 (-93.41%)
Mutual labels:  postgresql, postgres
Postgres Operator
Production PostgreSQL for Kubernetes, from high availability Postgres clusters to full-scale database-as-a-service.
Stars: ✭ 2,166 (-2.17%)
Mutual labels:  postgresql, postgres
Pg stat monitor
PostgreSQL Statistics Collector
Stars: ✭ 145 (-93.45%)
Mutual labels:  postgresql, postgres
Elephant Shed
PostgreSQL Management Appliance
Stars: ✭ 146 (-93.41%)
Mutual labels:  postgresql, postgres
Ozo
OZO is a C++17 Boost.Asio based header-only library for asyncronous communication with PostgreSQL DBMS.
Stars: ✭ 138 (-93.77%)
Mutual labels:  postgresql, postgres
Masquerade
A Postgres Proxy to Mask Data in Realtime
Stars: ✭ 134 (-93.95%)
Mutual labels:  postgresql, postgres
Serverless Pg
A package for managing PostgreSQL connections at SERVERLESS scale
Stars: ✭ 142 (-93.59%)
Mutual labels:  postgresql, postgres
Postgres Operator
Postgres operator creates and manages PostgreSQL clusters running in Kubernetes
Stars: ✭ 2,194 (-0.9%)
Mutual labels:  postgresql, postgres
Sqlcell
SQLCell is a magic function for the Jupyter Notebook that executes raw, parallel, parameterized SQL queries with the ability to accept Python values as parameters and assign output data to Python variables while concurrently running Python code. And *much* more.
Stars: ✭ 145 (-93.45%)
Mutual labels:  postgresql, postgres
Sqorn
A Javascript library for building SQL queries
Stars: ✭ 1,871 (-15.49%)
Mutual labels:  postgresql, postgres
Psql2csv
Run a query in psql and output the result as CSV.
Stars: ✭ 153 (-93.09%)
Mutual labels:  postgresql, postgres
Doctrine Postgis
Spatial and Geographic Data with PostGIS and Doctrine.
Stars: ✭ 161 (-92.73%)
Mutual labels:  postgresql, postgres
View All Similar Projects ➔

postgresqltuner.pl

Build Status Donate

postgresqltuner.pl analyses your PostgreSQL instance and produces a report, giving hints. It is inspired by mysqltuner.pl

Demo

Here is a sample output:

postgresqltuner.pl version 1.0.1
Checking if OS commands are available on /var/run/postgresql...
[OK]      I can run OS commands
Connecting to /var/run/postgresql:5432 database template1 with user postgres...
[OK]      The user acount used for reporting has superuser rights on this PostgreSQL instance
=====  OS information  =====
[INFO]    OS: linux Version: 4.9.0 Arch: x86_64-linux-gnu-thread-multi
[INFO]    OS total memory: 94.36 GB
[OK]      vm.overcommit_memory is good: no memory overcommitment
[INFO]    Running (probably) directly on a physical machine
[INFO]    Currently used I/O scheduler(s): mq-deadline
=====  General instance informations  =====
-----  Version  -----
[OK]      You are using the latest major version (11.5 (Debian 11.5-1+deb10u1)) of PostreSQL
-----  Uptime  -----
[INFO]    Service uptime:  01h 41m 13s
[WARN]    Uptime less than 1 day.  My report may be inaccurate
-----  Databases  -----
[INFO]    Database count (except templates): 3
[INFO]    Database list (except templates): postgres wikistats adrenalib
-----  Extensions  -----
[INFO]    Number of activated extensions: 1
[INFO]    Activated extensions: plpgsql
[WARN]    Extension pg_stat_statements is disabled in database template1
-----  Users  -----
[OK]      No user account will expire in less than 7 days
[OK]      No user with password=username
[OK]      Password encryption enabled
-----  Connection information  -----
[INFO]    max_connections: 20
[INFO]    Current used connections: 8 (40.00%)
[INFO]    2 connections are reserved for super user (10.00%)
[INFO]    Average connection age:  01h 08m 18s
-----  Memory usage  -----
[INFO]    Configured work_mem: 128.00 MB
[INFO]    Using an average ratio of work_mem buffers by connection of 150% (use --wmp to change it)
[INFO]    Total work_mem (per connection): 192.00 MB
[INFO]    shared_buffers: 40.00 GB
[INFO]    Track activity reserved size: 0.00 B
[INFO]    maintenance_work_mem=2.00 GB
[INFO]    Max memory usage:
		  shared_buffers (40.00 GB)
		+ max_connections * work_mem * average_work_mem_buffers_per_connection (20 * 128.00 MB * 150 / 100 = 3.75 GB)
		+ autovacuum_max_workers * maintenance_work_mem (2 * 2.00 GB = 4.00 GB)
		+ track activity size (0.00 B)
		= 47.75 GB
[INFO]    effective_cache_size: 85.00 GB
[INFO]    Cumulated size of all databases: 2.17 TB
[INFO]    PostgreSQL maximum amount of memory used: 50.60% of system RAM
[WARN]    PostgreSQL will not use more than 60% of the amount of RAM.  On a dedicated host you may increase PostgreSQL shared_buffers, as it may improve performances.
[INFO]    max memory+effective_cache_size (less shared_buffers) is 98.29% of the amount of RAM
[WARN]    The sum of max_memory and effective_cache_size is too high, the planner may create bad plans because the system buffercache will probably be smaller than expected, especially if the machine is NOT dedicated to PostgreSQL
-----  Huge Pages  -----
[OK]      huge_pages enabled in PostgreSQL
[INFO]    Hugepagesize is 2048 kB
[INFO]    HugePages_Total 21000 pages
[INFO]    HugePages_Free 18004 pages
[INFO]    Suggested number of Huge Pages: 21001 (Consumption peak: 43009080 / Huge Page size: 2048)
-----  Logs  -----
[OK]      log_hostname is off: no reverse DNS lookup latency
[WARN]    Log of long queries deactivated.  It will be more difficult to optimize query performance
[OK]      log_statement=none
-----  Two-phase commit  -----
[OK]      Currently no two-phase commit transactions
-----  Autovacuum  -----
[OK]      autovacuum is activated.
[INFO]    autovacuum_max_workers: 2
-----  Checkpoint  -----
[OK]      checkpoint_completion_target(0.9) OK
-----  Disk access  -----
[BAD]     fsync is off.  You may lose data after a crash, DANGER!
[OK]      synchronize_seqscans is on
-----  WAL  -----
-----  Planner  -----
[OK]      I/O cost settings are set at their default values
[BAD]     Some plan features are disabled: enable_partitionwise_aggregate,enable_partitionwise_join
=====  Database information for database template1  =====
-----  Database size  -----
[INFO]    Database template1 total size: 8.02 MB
[INFO]    Database template1 indexes size: 4.91 MB (61.21%)
[INFO]    Database template1 indexes size: 3.11 MB (38.79%)
-----  Tablespace location  -----
[OK]      No tablespace in PGDATA
-----  Shared buffer hit rate  -----
[INFO]    shared_buffer_heap_hit_rate: 99.98%
[INFO]    shared_buffer_toast_hit_rate: 97.31%
[INFO]    shared_buffer_tidx_hit_rate: 98.97%
[INFO]    shared_buffer_idx_hit_rate: 99.95%
[OK]      This is very good (if this PostgreSQL instance was recently used as it usually is, and was not stopped since)
-----  Indexes  -----
[OK]      No invalid index
[OK]      No unused indexes
-----  Procedures  -----
[OK]      No procedures with default costs

=====  Configuration advice  =====
-----  checkpoint  -----
[URGENT] set fsync to on!
-----  extension  -----
[LOW] Enable pg_stat_statements in database template1 to collect statistics on all queries (not only those longer than log_min_duration_statement)
-----  hugepages  -----
[LOW] Change Huge Pages size from 2MB to 1GB
[MEDIUM] set vm.nr_hugepages=21001 in /etc/sysctl.conf and run sysctl -p to reload it.  This will allocate Huge Pages (it may require a system reboot).

To use it

Install it

It needs Perl with various modules, mainly DBD::Pg

  • On Debian or a derivative:
apt install libdbd-pg-perl libdbi-perl perl-modules
  • On Fedora or a derivative:
dnf install perl-DBD-Pg perl-DBI perl-Term-ANSIColor perl-Memoize
  • On Arch or a derivative:
pacman -S perl-dbi perl-dbd-pg
  • On MacOS with Homebrew:
brew install perl
cpan DBD-pg

Download the script. Invoke one of:

wget -O postgresqltuner.pl postgresqltuner.pl
wget -O postgresqltuner.pl https://postgresqltuner.pl
curl -Lo postgresqltuner.pl postgresqltuner.pl
curl -Lo postgresqltuner.pl https://postgresqltuner.pl

Set permissions:

chmod +x postgresqltuner.pl

Then invoke on the command line, as the "postgres" user, either:

  • By connecting to the PostgreSQL server via TCP:
postgresqltuner.pl --host=dbhost --database=testdb --user=username --password=qwerty
  • ... or via an Unix socket:
postgres$ postgresqltuner.pl --host=/var/run/postgresql  # PostgreSQL socket directory

If available, postgresqltuner.pl will use standard PostgreSQL variables like PGHOST, PGPORT, PGDATABASE, PGUSERNAME, and password from the ~/.pgpass file.

From package

  • On Fedora:
dnf install postgresqltuner
  • On RHEL/CentOS it is available in EPEL.

With Docker

Invoke on the command-line either:

  • via the plain network:
docker run -it --rm jfcoz/postgresqltuner --host=dbhost --user=username --password=pass --database=testdb
  • ... or via ssh:
docker run -it --rm -v $HOME/.ssh:/root/.ssh jfcoz/postgresqltuner --host=dbhost --user=username --password=pass --database=testdb
  • ... or via a docker link:
docker run -it --rm --link your-postgresql-container:dbhost jfcoz/postgresqltuner --host=dbhost --user=username --password=pass --database=testdb

SSH

When using postgresqltuner.pl to inspect a remote PostgreSQL instance, it will use ssh to collect OS informations. You should configure ssh to connect to the remote host with private key authentication.

You can provide adequate options to ssh:

  • ... as commend-line options:
--sshopt=Port=2200 --sshopt=IdentityFile=...
  • or in the configuration file "~/.ssh/config":
Host my-database-host
	IdentityFile=...
	Port=2200

PostgreSQL passwords

For better security use a ~/.pgpass file containing passwords, so no password will be saved in your shell history nor visible in a process complete name. .pgpass documentation

host:port:database:username:password

Options

  • Average number of work_mem buffer per connection:

A complex query can use many work_mem buffers. You can configure the average number of work_mem buffers per connection (in percent):

--wmp 300

The default in 150%

  • SSD storage:

If the PostgreSQL instance runs in an hypervisor or with SSD storage, I cannot detect it accurately.

--ssd

Allow to specify that storage is on SSD. Especially useful if PostgreSQL runs in a VM using an underlying (on the physical machine) SSD.

  • No color

The report will not be colorized. Useful to save it in a file by using shell redirection.

--nocolor
  • Skip ssh

When running against an RDS, it is not possible to have a ssh connection to the instance. The option skip-ssh option skips the ssh connection and related checks. However, without ssh the script won't be able to search for total_memory. In this case it is possible to manually inform it by using the memory parameter. This way we can still have the memory ratio warnings. The memory value should be in bytes.

--skip-ssh --memory=8219082752

Special FreeBSD settings

FreeBSD has support for virtual memory over-commit, using vm.overcommit configuration setting.

This setting is configured via /etc/sysctl.conf.

Change 'vm.overcommit: 0 ' to 'vm.overcommit: 1'.

Also, install freecolor.

Note that the project description data, including the texts, logos, images, and/or trademarks, for each open source project belongs to its rightful owner. If you wish to add or remove any projects, please contact us at [email protected].