How to check throughput (query per second) of a database?

Question

I have PostgreSQL dbms. I want to check live throughput of the DBMS.

I want to build a graph of throughput which will be updated every 10 seconds.

How do I do that? Any suggestion for MySQL will also be helpful.

Answer 1

The mysql command show status like 'Queries' gives the total number of queries. You can periodically run a script which grabs this value and calculates the query rate for a particular time interval using the difference.

ie If you run the script every minute and get the change in total queries for each run and divide by 60 you will have a rough estimate of the rate queries/second.

As an example this shell command will just give the value of the 'Queries' field. mysql -e "show status like 'Queries'\G" | sed 1,2d | cut -d: -f2

Answer 2

Take a look to postgres stat tables.

To get total number of commits, use query like SELECT SUM(xact_commit) FROM pg_stat_database - this query would return total number of successful commits on all databases since server start. Running this query every N minutes/seconds would allow you to draw nice graph (subtracting previous value from the current one would give you number of commits finished in N minutes).

I'd also suggest you to use some monitoring system for this, and it might be useful for other purposes. Personally, I'm using zabbix and maintain small collection of templates & custom scripts, which allows me so see something like this:

Answer 3

A monitoring application such a Munin or Nagios may suit your needs.

Munin uses RRDTool and can collect & graph stats from a variety of sources "out of the box", amongst which is MySQL (presumably Postgresql as well) stats such as throughput.

Nagios is a full blown network monitoring application, but can also collect performance data with some configuration.