The love of Data, Database Engineering, Architecture, Entrepreneurship, and other assorted bits

Graphing pgstat output

05 November 2008

If you’re using the <a href=>pgd pgstat utility</a> I posted about previously, you can graph the output with very little effort using <a href=>gnuplot</a>. In my case I use pgd pgstat for capturing output for various PostgreSQL performance tests, and of course graphing that output is important.

Read More

pgstat: a database utility like iostat

21 October 2008

I needed a utility for capturing various data-points about a PostgreSQL database as I performed load tests. I copied a utility I have used previously on Oracle that worked quite well. The new utility is called pgd pgstat. This utility spits out various DB metrics on the command line similar to iostat. It’s good for quick diagnosis, performance testing, etc. The output is suitable for import/graphing in excel as well, just use space delimited format when importing the data. Here is a screenshot of how the output appears. You can <a href=>download the utility</a> and give it a whirl.

Read More

Unique identifier for a database without connecting to the database?

16 September 2008

In PostgreSQL, the working directory is a unique identifier for a database, and sometimes you want to use that working dir in your script(s). But what if you don’t want to actually connect and query the database? Is there a way to find out the unique identifier for the db? In a high transaction environment, making the archive process fast and efficient is very important, so keeping an eye on efficiency when coding up these little bits is paramount. The key lies in the /proc/ filesystem in Linux. Here is a little function that returns the unique identifier:

Read More

Open source databases; scaling the application stack.

30 July 2008

After reading Allan Packers excellent blog entry ‘are proprietary databases doomed’ , a couple of additional thoughts came to mind. He mentions that open source databases (OSDB’s) are becoming “good enough”, and explains about how most users don’t need or use the vast amount of the features available in the proprietary databases, and thus, OSDB’s are becoming more popular. Coming from a proprietary background myself, scaling Oracle at Paypal and then Ebay, I know that side of the business pretty well. In the last year, I have made the professional leap to OSDB’s in a big way at Hi5, where we do quite a bit of workload on an OSDB - PostgreSQL. So Allan’s points hit home for me.

Read More

Python script showing PostgreSQL objects in linux memory:

02 July 2008

I got some email and comments about the code I used for <a href=>my post</a>; Mapping Linux memory for PostgreSQL using fincore so I thought I would post the code I am using. Nothing too fancy here, it needs a config file and some more bits. I highly recommend someone do this in perl vs python and incorporate fincore as a module instead. I used <a href=>psycopg2</a>, other than that, all the other modules are stock with python <a href=>2.5.2</a>. If someone wants to show me how to query the data directory PostgreSQL is currently using and incorporate that into the script vs the static string: mydir = “/data/mydb/base”, that would be great. In order to use this script, you must change the variables for fincore, and mydir below.

Read More