Hacker News Reader

Comment by kingmanaz | Hacker News Reader

This is going to sound pathetic and off topic, but do you have pointers toward any good materials on database performance benchmarking? Your screenshots display exactly the kinds of presentation I am after, yet I've never managed to progress beyond windows performance monitor and a pile of unorganized spreadsheets. I keep getting more and more DBA work dumped on me and it seems like somewhere I've missed the principles of the art of DBA (if there are any). Other than some Celko and Date, everything I come in contact with has market-speak and corporate buzzwords written all over it.

View on HN

Greg Smith's PostgreSQL 9.0 High Performance [1] Cary Milsap's Optimizing Oracle Performance [2], and - a pale third, Davidson and Ford's Performance Tuning with SQL Server Dynamic Management Views [3]

The first 2 are bottom-up, structured approaches to benchmarking low-level system performance with an emphasis on *nix, and building up to database performance characterization and investigation. Despite their names, both have a lot of great general, non-product-specific use.

The third I include because it is the only MSSQL-specific book I have on the subject, and it sounds like you're in Windows Land. It has some real gems, but little coverage of the OS or methodology. I cannot over-emphasize how important that methodology is.

Make sure you learn how to use the Resource Monitor, and SQL Profiler.

If you want to chat about it more, I'm justinpitts at google's mail.

[1] http://www.amazon.com/PostgreSQL-High-Performance-Gregory-Sm... [2] http://www.amazon.com/Optimizing-Oracle-Performance-Cary-Mil... [3] http://www.amazon.com/Performance-Tuning-Server-Dynamic-Mana...

That graph looks like it was generated by rrdtool (http://oss.oetiker.ch/rrdtool/). If you are looking for monitoring of stats like system load, disk io, etc, check out cacti (http://www.cacti.net/). It has quite a few plugins to monitor other stuff as well. It also works for remote servers via snmp.

For motioning performance of Linux machines I recommend using munin. It has excellent PostgreSQL plugins, most of them written by a member of the PostgreSQL core team (Thanks Magnus!).

cacti is also supposed to be good but I have never used it myself.

The graphs are produced by a web frontend (I forget the name) that reads from Collectd data. Collectd runs on each box and collects performance data that can be graphed on a central box.

We used Munin previously, and I must admit that Collectd feels like a step backwards. Both are very primitive, so we might look for something better soon, perhaps Graphite.

I recommend migrating to Linux (or one of the BSDs) if you want to get serious about system administration. The wealth of mature tools is vastly superior.

The Postgres book by Greg Smith is indispensible if you want to tune a Postgres installation.

One thing to be careful of when producing benchmarks for databases is making sure you avoid artificial testing scenarios that don't match your actual workload.

If you don't already, understanding your concurrency and read/write patterns will go a long way to help you identify which benchmarks are meaningful for you.

Reply on news.ycombinator.com