Skip to main content

Postgres Query Stats using pg_stat_statements

Just wanted to share how to configure Postgres to capture query statistics using pg_stat_statements, including how often queries are run, how long they take to execute, along with a whole host of other useful information.

First, you need to edit the Postgres configuration, adding pg_stat_statements to preshared libraries

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

After editing postgresql.conf, restart postgres. On Ubuntu

sudo /etc/init.d/postgresql stop
sudo /etc/init.d/postgresql start

After restarting Postgres, you need to enabled the pg_stat_statements extension. You can enable it per database, or for all databases on the same Postgres server by installing on the postgres database.

CREATE EXTENSION pg_stat_statements;
Once pg_stat_statements is added to the preshared libraries and the extension is enabled, you can query pg_stat_statements to get useful information about running queries.

SELECT
  query,
  sum(calls) AS calls,
  sum(total_time) AS total_time,
  sum(rows) as rows,
  sum(shared_blks_hit) AS shared_blks_hit,
  sum(shared_blks_read) AS shared_blks_read
FROM pg_stat_statements
GROUP BY 1
ORDER BY 3 desc

 Here's a sample of the data available



By default, Postgres will track approximately 5K distinct queries. Check the pg_stat_statements documentation for tuning these settings.

You can reset these settings via the pg_stat_statements_reset function
select pg_stat_statements_reset();
pg_stat_statments is a handy tool to quickly determine the most expensive queries, which can then the analyzed for potential optimizations. I highly recommend Tatiyants Postgres Query Plan Visualization.

pg_stat_statements is fairly low overhead, and should be safe to use in production. But your milage may vary.

Comments

Popular posts from this blog

Generating Java Mixed Mode Flame Graphs

Overview I've seen Brendan Gregg's talk on generating mixed-mode flame graphs and I wanted to reproduce those flamegraphs for myself. Setting up the tools is a little bit of work, so I wanted to capture those steps. Check out the Java in Flames post on the Netflix blog for more information.

I've created github repo (github.com/jerometerry/perf)  that contains the scripts used to get this going, including a Vagrantfile, and JMeter Test Plan.

Here's a flame graph I generated while applying load (via JMeter) to the basic arithmetic Tomcat sample application. All the green stacks are Java code, red stacks are kernel code, and yellow stacks are C++ code. The big green pile on the right is all the Tomcat Java code that's being run.


Tools Here's the technologies I used (I'm writing this on a Mac).
VirtualBox 5.1.12Vagrant 1.9.1bento/ubuntu-16.04 (kernel 4.4.0-38)Tomcat 7.0.68JMeter 3.1OpenJDK 8 1.8.111linux-tools-4.4.0-38linux-tools-commonBrendan Gregg's Fla…

Basic Web Performance Testing With JMeter and Gatling

Introduction In this post I'll give a quick way to get some basic web performance metrics using both JMeter and Gatling.

JMeter is a well known, open source, Java based tool for performance testing. It has a lot of features, and can be a little confusing at first. Scripts (aka Test Plans), are XML documents, edited using the JMeter GUI.  There are lots of options, supports a wide variety of protocols, and produces some OK looking graphs and reports.

Gatling is a lesser known tool, but I really like it. It's a Scala based tool, with scripts written in a nice DSL. While the scripts require some basic Scala, they are fairly easy to understand and modify. The output is a nice looking, interactive, HTML page.
Metrics Below are the basic metrics gathered by both JMeter and Gatling. If you are just starting performance testing, these might be a good starting point.

Response Time – Difference between time when request was sent and time when response has been fully received

Latency –…

Multi Threaded NUnit Tests

Recently I needed to reproduce an Entity Framework deadlock issue. The test needed to run in NUnit, and involved firing off two separate threads. The trouble is that in NUnit, exceptions in threads terminate the parent thread without failing the test.

For example, here's a test that starts two threads: the first thread simply logs to the console, while the other thread turfs an exception. What I expected was that this test should fail. However, the test actually passes.

readonly ThreadStart[] delegates = { () => { Console.WriteLine("Nothing to see here"); }, () => { throw new InvalidOperationException("Blow up"); } }; [Test] public void SimpleMultiThreading() { var threads = delegates.Select(d => new Thread(d)).ToList(); foreach (var t in threads) { t.Start(); } foreach (var t in threads) { t.Join(); } }
Peter Provost posted an article that describes how to make this test fail. It works…