Suggested test points for a performance tool? - Mailing list pgsql-performance

Hello everyone,

If one would create a postgres performance tool, what would one like to measure aside from the obvious things common to
alldatabases (query times, locks etc)? 

I'm in the slightly difficult situation of being asked to port "Jet Profiler for MySQL" (http://www.jetprofiler.com)
foruse with postgres (a "Jet Profiler for PostgreSQL" if you will). The author of the original product built several
veryheavily used systems on top of MySQL before writing this tool, so he knew what he wanted to look at. 

Personally I'm also much more comfortable with MySQL than Postgres, having almost worked exclusively with the former
andonly recently started working with Postgres. Is anyone interested in helping out with some suggestions on where to
startlooking? 

Christoffer
AEGIK / www.aegik.se


P.S. Here's a copy-paste from the [MySQL] feature blurb on the Jet Profiler site, most - but not all - are not specific
toMySQL. 

• Top Queries - See which queries are being run the most on your server.
• Top Users - See which users are using your server the most.
• Top Tables - See which database tables are opened the most.
• Top States - See which states your database is most busy doing, such as creating temp tables.
• Top IPs - See which client IPs are using your server the most.
• Replication Profiling - You can measure how much capacity you have left on the replication SQL thread on slaves. If
youare using MyISAM a lot, a lock analysis will help discover any locks associated with replication. 
• Master and Slave statistics - See how many threads are working on your masters and slaves. Find I/O or SQL
bottlenecks.
• MyISAM Lock Analysis - You can view which queries cause the most amount of MyISAM locking. This can be used to
minimizereplication lag and lock contention on busy tables. 
• Query Ratings - You can get your queries rated and see which queries are most likely to cause load due to missing
indices,big tables and more. 
• Query Visualization - The query execution plan can be visualized using EXPLAIN. A diagram shows the table lookups
involved,the rating and join size. 
• Slow Queries - See the slowest queries per time interval.
• Zoomable GUI - You can easily zoom in on spikes in your load and see the corresponding queries for that time
interval.
• General Server Metrics - Such as threads connected, network I/O, command statistics, handler statistics and more. 50+
metricsare recorded from the server. 
• Save / Load Support - Save profiling data for later use, compare week to week or normal load vs high load situations.
• Low Overhead - Running the tool against your database typically costs around 1%. Recording granularity customizable.
• Supports all MySQL Versions - Works on 3.x (!), 4.0, 4.1, 5.0, 5.1 and 6.0, Enterprise and Community editions.
• Works on Windows, Mac and Linux
• No Server Changes
• Simple Setup
• Free / Professional Version - The free version doesn't cost anything and isn't time limited. Upgrade to the
professionalversion to get all features. 
• Multi-language support - available in English, German and Swedish