The love of Data, Database Engineering, Architecture, Entrepreneurship, and other assorted bits
03 March 2009
I moved the pgstat (previously named pgd) project to pgfoundry. Thank you to the folks over there approving the project. I added a column for ‘active’ processes from pg_stat_activity as well as some fixes requested by Devrim that I really should have had done from the start. Thanks for the contribution. Downloads can be found here. Oh, and I will remove the OSX file from the .tar file in the future.
13 February 2009
Since the economy is not exactly rocking these days, I suspect there are a lot of companies out there trying to save a buck or two on infrastructure. Databases are not exactly cheap, so anything that an engineer or DBA can do to save cycles is a win. So how do you stretch your existing hardware and make it perform more transactions for the same amount of cash?
12 December 2008
The debate about the optimum shared_buffers size in PostgreSQL is clearly far from over. However, I have not seen any results where the buffer cache and the FS cache were tuned in unison.
05 November 2008
If you’re using the <a href=http://www.kennygorman.com/wordpress/?p=260>pgd pgstat utility</a> I posted about previously, you can graph the output with very little effort using <a href=http://www.gnuplot.info/>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.
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=http://pgfoundry.org/projects/pgstat2/>download the utility</a> and give it a whirl.
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: