Analyzing slow queries on Postgres can be done by enabling slow queries, but it is even more fun with the builtin extension: pg_stat_statements
1. Enable tracking by changing parameters group values (if you did not create a custom parameter group follow these instrcutions and don't forget to reboot the instance to take effect):
pg_stat_statements.track ALL
2. Connect to Postgres
psql -U ADMIN_USER -h RDS_PATH.rds.amazonaws.com -d postgres
3. Create the extension
CREATE EXTENSION pg_stat_statements;
4. Verify the extension was created:
SELECT *
FROM pg_available_extensions
WHERE
name = 'pg_stat_statements' and
installed_version is not null;
5. Analyze the queries usage:
SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20;
Keep Performing,
Moshe Kaplan
News, Personal view and perspective of the software performance field, cloud computing and industry based on my experience
Showing posts with label PostgreSQL. Show all posts
Showing posts with label PostgreSQL. Show all posts
Jun 7, 2020
Apr 10, 2014
Looking for PostgreSQL Performance Issues
As traffic goes up, even your PostgreSQL may become a bottleneck.
In this cases it is important to analyze the traffic and understand the usage pattern. The way you will be able to tune the system to meet the challenge.
Understand Usage Pattern at Peak Time
Use PostreSQL top project to get key usage patterns in real time:
To get a broad insight of PostgreSQL behavior use pgFouine. This tool analyzes the PostgreSQL logs and provides detailed usage patterns reports such as leading queries, duration, queries by type and queries patterns.
You can get some of these metrics by querying the pg_catalog schema (such as pg_stat_user_tables and pg_stat_user_indexes), and use log_statement to analyze all queries.
Enable Slow Queries
SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
Explain the Execution Plans
Use the Explain statement to analyze slow queries execution paths and eliminate them:
Bottom Line
Using these great tools, you can boost your PostgreSQL and meet the business challenges
Keep Performing,
Moshe Kaplan
In this cases it is important to analyze the traffic and understand the usage pattern. The way you will be able to tune the system to meet the challenge.
Understand Usage Pattern at Peak Time
Use PostreSQL top project to get key usage patterns in real time:
- Current active SQL statements running
- Query plans
- Locks
- User tables and indexes statistics
Understand Overall Usage Pattern
To get a broad insight of PostgreSQL behavior use pgFouine. This tool analyzes the PostgreSQL logs and provides detailed usage patterns reports such as leading queries, duration, queries by type and queries patterns.
You can get some of these metrics by querying the pg_catalog schema (such as pg_stat_user_tables and pg_stat_user_indexes), and use log_statement to analyze all queries.
Enable Slow Queries
Probably #1 tool to eliminate performance issues:
- Add pg_stat_statements to shared_preload_libraries statement @ postresql.conf
- Restart PostgreSQL daemon.
- use the pg_stat_statements view to pinpoint the bottlenecks:
SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
Explain the Execution Plans
Use the Explain statement to analyze slow queries execution paths and eliminate them:
EXPLAIN (FORMAT JSON) SELECT * FROM table_name;
Bottom Line
Using these great tools, you can boost your PostgreSQL and meet the business challenges
Keep Performing,
Moshe Kaplan
Jan 13, 2014
Create Your Postgres Cluster in 6 Steps
Setting a Postgres cluster (or even a Master-Slave configuration) was not an easy task before version Postgres 9.0.
In the following lines you will find a short guideline that will help you create you Postgres HA solution:
1. Install postgres on both machines
sudo yum -y install postgresql-server
sudo service postgresql initdb
sudo service postgresql start
sudo sudo -u postgres psql
2. Stop the Postgres instance on both machines, delete the /var/lib/postgresql folder at the slave, and recover it by copy the files from the master.
3. Do initial configuration on both machines to enable logging:
sudo mkdir /var/log/postgresql/
sudo chown -R postgres:postgres /var/log/postgresql/
sudo vi /var/lib/pgsql9/data/postgresql.conf
> log_directory = '/var/log/postgresql'
4. Configure the Master and Slave to enable replication
sudo vi /var/lib/pgsql9/data/postgresql.conf @ Master (enable logging and listening)
> listen_addresses = '*'
> wal_level = hot_standby
> max_wal_senders = 3
sudo vi /var/lib/pgsql9/data/pg_hba.conf @ Master (enable access to the server, tune it, if your cluster is not in a secure environment):
> host replication all 192.168.0.2/32 trust
> host all all 0.0.0.0/0 trust
sudo vi /var/lib/pgsql9/data/postgresql.conf @ Slave (turn on standby)
> hot_standby = on
sudo vi /var/lib/pgsql9/data/recovery.conf @ Slave (turn on standby a new file)
> standby_mode = 'on'
> primary_conninfo = 'host=192.168.0.1'
5. Finally, start your slave, and then your master on both sides:
sudo service postgresql start
6. And check replication on by creating database at the Master, and check it at the slave
master> sudo sudo -u postgres psql
CREATE DATABASE a
\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
a | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
slave> sudo sudo -u postgres psql
\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
a | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
P.S If you are using a CentOS and yum (and not AWS), the following link will be useful for you.
Recovering from Cluster Crash (Thanks for Yariv Rachmani for contribution)
If you find the following errors in the your Postgres error:
> 2015-01-01 19:41:14.419 IST >LOG: started streaming WAL from primary at 0/86000000 on timeline 1
> 2015-01-01 19:41:14.420 IST >FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 000000010000000000000086 has already been removed
In this case the quickest procedure to recover the slave:
Postgres replication is not so complicated starting with Postgres 9.0. Now, it's your turn to keep your system uptime!
Keep Performing,
Moshe Kaplan
In the following lines you will find a short guideline that will help you create you Postgres HA solution:
1. Install postgres on both machines
sudo yum -y install postgresql-server
sudo service postgresql initdb
sudo service postgresql start
sudo sudo -u postgres psql
2. Stop the Postgres instance on both machines, delete the /var/lib/postgresql folder at the slave, and recover it by copy the files from the master.
3. Do initial configuration on both machines to enable logging:
sudo mkdir /var/log/postgresql/
sudo chown -R postgres:postgres /var/log/postgresql/
sudo vi /var/lib/pgsql9/data/postgresql.conf
> log_directory = '/var/log/postgresql'
4. Configure the Master and Slave to enable replication
sudo vi /var/lib/pgsql9/data/postgresql.conf @ Master (enable logging and listening)
> listen_addresses = '*'
> wal_level = hot_standby
> max_wal_senders = 3
sudo vi /var/lib/pgsql9/data/pg_hba.conf @ Master (enable access to the server, tune it, if your cluster is not in a secure environment):
> host replication all 192.168.0.2/32 trust
> host all all 0.0.0.0/0 trust
sudo vi /var/lib/pgsql9/data/postgresql.conf @ Slave (turn on standby)
> hot_standby = on
sudo vi /var/lib/pgsql9/data/recovery.conf @ Slave (turn on standby a new file)
> standby_mode = 'on'
> primary_conninfo = 'host=192.168.0.1'
5. Finally, start your slave, and then your master on both sides:
sudo service postgresql start
6. And check replication on by creating database at the Master, and check it at the slave
master> sudo sudo -u postgres psql
CREATE DATABASE a
\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
a | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
a | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
P.S If you are using a CentOS and yum (and not AWS), the following link will be useful for you.
Recovering from Cluster Crash (Thanks for Yariv Rachmani for contribution)
If you find the following errors in the your Postgres error:
> 2015-01-01 19:41:14.419 IST >LOG: started streaming WAL from primary at 0/86000000 on timeline 1
> 2015-01-01 19:41:14.420 IST >FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 000000010000000000000086 has already been removed
In this case the quickest procedure to recover the slave:
- Stop the service: > service postgresql-9.3 stop
- Backup the data folder: > cp -prf /var/lib/pgsql/9.3/data /var/lib/pgsql/9.3/data-org
- Remove the data folder: > rm -rf data
- Copy the data folder from master: > pg_basebackup -h
-D /var/lib/pgsql/9.3/data -U postgres -P -v -x - Change permissions: > chown -Rf postgres:postgres data
- Copy the configuration file from the backup:> cp -p data-org/*.conf data/
- Start again the slave: > service postgresql-9.3 start
- Verify the slave log file
Postgres replication is not so complicated starting with Postgres 9.0. Now, it's your turn to keep your system uptime!
Keep Performing,
Moshe Kaplan
Subscribe to:
Posts (Atom)