Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Has anyone usefull ideas, inspration or literature for me to design a good test bench for PostgreSQL. I need repeatable results, which are applyable in realworld and production environement (no Journaling off for example ;) ). I need the testbench to see effects on query and/or configuration tuning. Further it would be interessting to detect CPU, Memory or HD bottlenecks.

I know this question is rather general, but right now I try to understand the process of improving prameters and tuning queries through consistent and repeatable results. Down the road it is about tuning and optimizing a routing database for GIS applications.

Thanks for your input! Martin

share|improve this question
add comment

3 Answers

While I can't speak to measuring specific metrics like CPU, storage throughput, etc. in your environment, I have found HammerDB to be a good tool for running a good, standardized benchmark against your databases. This tool runs an unofficial TPC-C benchmark against your database. You can configure for concurrent user connections and workloads. Using this conjunction with other metric tracking will help you evaluate your system for bottlenecks in an application agnostic manner.

As a note, HammerDB supports the following platforms at this time:

  • Oracle
  • SQL Server
  • MySQL
  • PostgreSQL
  • Redis
share|improve this answer
add comment

I recommend pgFouine. It is a good way of analyzing your Postgres for any given snapshot. I've used it for a couple of projects with great success.

http://pgfouine.projects.pgfoundry.org/

Also, for general ideas on how to optimize Postgres. I subscribe to the Instagram blog and have personally gleamed a lot of inspiration from it. In particular to the pgFouine issue, they provide fabric scripts and anecdotes.

http://instagram-engineering.tumblr.com/#pgfouine

share|improve this answer
add comment

You can always use pgbench (http://www.postgresql.org/docs/devel/static/pgbench.html), which is the standard tool for benchmarking Postgres.

From the docs:

pgbench is a simple program for running benchmark tests on PostgreSQL. It runs the same sequence of SQL commands over and over, possibly in multiple concurrent database sessions, and then calculates the average transaction rate (transactions per second). By default, pgbench tests a scenario that is loosely based on TPC-B, involving five SELECT, UPDATE, and INSERT commands per transaction. However, it is easy to test other cases by writing your own transaction script files.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.