Slides from PGCon 2010 Tutorial

2010-05-20T15:45:00.001-05:00

Just wanted everyone to know the slides were up...

Drupal 7 and PostgreSQL

2010-03-28T20:03:00.005-05:00

I am in the midst of preparing for my tutorial, Realistic Load Testing, at PGCon 2010. After talking with a number of folks at my favorite IRC channel, I decided to use the Drupal 7 Project as my test case.
For years, Drupal worked best with MySQL. Not that I harbor ill will against the database, it is just my experience with databases has led me to PostgreSQL as a superior engine. That said, MySQL works great for these types of applications. The types of applications we do at Digitec tend to need a much more robust solution.
With Drupal 7, there has been a more concerted effort in giving PostgreSQL a chance at being a valid alternative to MySQL. The code changes and the model the Drupal development team has made have helped.
My hope is that by using Drupal, I will not only give a good "real world" example application, but also give back to the Open Source community through the tests and tools that will be created for the tutorial. As Drupal 7 is still in beta development, I hope these tests can help the developers look into solutions for any problems that occur. I also hope that it will help in showing that PostgreSQL is a great fit for Drupal.

HOWTO setup pgBouncer on Debian Part 1

2010-03-26T08:27:00.005-05:00

When looking to provide database connections under a steady (or heavy) load, you will likely need to look into a database connection pooler. There are a number of good options for PostgreSQL such as pgBouncer, pgpool-II, SQL Relay, and a few others.
My personal favorite as of late is pgBouncer. Here's a few reasons why I like it:

a lightweight connection pooler--it is designed solely for pooling
low resource requirement
written with Python (a lot of our inernal apps use Python, so interoperability is great to have)
supports online restart/upgrade without dropping client connections

When setting up a pooler, you will want to have a separate server just for the pooler. Adding a pooler to the same server as the database only degrades the performance of PostgreSQL, so make sure you don't.

I use Debian as my choice of Linux distro. Unfortunately, there isn't an official package to install on Lenny. However, thanks to good ol' backports, we can find an up-to-date package. First things first, add backports to your /etc/apt/sources.list by adding the following source:
deb http://www.backports.org/debian lenny-backports main contrib non-free

Next, you will want to do aptitude update (or apt-get update)

You can learn more about how to use backports on their instructions page.

Finally, here's how to do an install of pgBouncer:
$ aptitude -t lenny_backports install pgbouncer

EXPLAIN ANALYZE is Your Friend

2010-03-06T22:50:00.003-06:00

EXPLAIN ANALYZE is a useful tool to help see what query plan the planner creates for any query. One tool that I have found useful for some time is depesz's online tool, http://explain.depesz.com/. Simply "paste your explain analyze plan, and see the output."

PGCon 2010 Schedule and Registration Now Open!

2010-03-06T22:37:00.004-06:00

According to Dan Langille's blog, the schedule and registration page is now up for PGCon 2010 held in Ottowa, Canada! I will be giving a tutorial on "Realistic Load Testing." I hope to see you there.

PGBouncer or PGPool II? That is the question!

2010-03-06T22:03:00.003-06:00

We have been trying out both PgBouncer and pgpool II for connection pooling in front of our Postgresql database servers. One of the issues we are trying to tackle is how to make PgBouncer HA (High Availability) and if it matters.
pgpool II already has the ability to be HA using pgpool-HA, but pgpool is also a more featureful application that does much more than simple connection pooling.
On the other hand, PgBouncer is a nice, lightweight connection pooler that we have found to fit the bill rather well. Our question is, if we have auto-failover set up in our private cloud for PgBouncer, is there a need to be HA? What would need to be done? If we forgo making the pooler HA, what risks do we pose?
All these and other questions are going through my head. Any suggestions?

PGBouncer and Database Updates

2010-03-04T23:25:00.004-06:00

I have been using pgBouncer for a while and have run into a need in our development server to drop and recreate a database. I have had issues in the past with trying to PAUSE, but that ends up stopping all connections, which isn't always a feasible option. I have found that simply commenting out the database in question in the pgbouncer.ini file and simply doing a RELOAD. This will also kill any clients currently connected to that database and allow you to drop/recreate. After making changes, simply uncomment and RELOAD again.

Realistic Load Testing

2010-02-26T09:41:00.004-06:00

I just got word that I will be speaking at PGCon 2010. PGCon is a PostgreSQL Conference for Users and Developers held May 20-21, 2010 at University of Ottawa, with two days of Tutorials on May 18-19, 2010.

I will be leading a tutorial on Realistic Load Testing:

Applications and databases need testing. But how can you get valid results for a fully integrated system Flight-Check test at realistic loads? This tutorial addresses the many challenges that arise in an application or database development to give confidence to you and your customers in presenting a production-ready product.

After running into many obstacles in proving a recent enterprise product launch could handle the expected loads of our customers, Digitec, Inc. invested time in writing realistic Flight-Check tests using PostgreSQL functions and Python. The results of these tests gave confidence to the engineers, developers, and our customers that the entire system would be able to perform as designed.

Topics covered during this tutorial include:

Shortfalls of FLOSS benchmark tests
Identifying the Project Test Components
Identifying Realistic Loads
Identifying Historical Data
Developing Tests and Procedures
PostgreSQL Functions for Tests
Python Scripts for Tests
Helpful Tools

PGCon is the place to meet, discuss, build relationships, learn valuable insights, and generally chat about the work you are doing with PostgreSQL. If you want to learn why so many people are moving to PostgreSQL, PGCon will be the place to find out why. Whether you are a casual user or you've been working with PostgreSQL for years, PGCon will have something for you.

Come join me and many others and maybe we can hang out. I look forward to seeing many of my IRC friends there!

View the PGCon Schedule and register today!

WaveRoute Enterprise unveiled at Sensors Expo. www.waveroute.com

2009-06-10T08:44:00.002-05:00

Machine to Machine (M2M) communications is increasingly important to the success and viability of businesses today. Our WaveRoute products allow for devices and sensors to be connected and available for data communications. WaveRoute Enterprise is a hosted, web-based M2M application for managing, monitoring, and controlling these devices and sensors remotely. Easily add devices and receive data in under a few minutes, create powerful rules and alerts, and visually know what is happening anytime, anywhere.

If you are looking for a solution to bridge the gap between your sensors, devices, and existing data to your end users, WaveRoute Enterprise can bring it all together.

Come Visit Us at Sensors Expo Booth 603! Speak one-on-one with Digitec engineers, grab some mints, and learn more about WaveRoute Enterprise first hand. You can also visit our website, http://www.waveroute.com , to request a live demonstration.

WaveRoute is a division of Digitec, Inc. The WaveRoute product line offers drop-in M2M network hotspots. WaveRoute Enterprise is a hosted, web-based M2M application for remote monitoring and control. Established in 1982, Digitec is an electronics research, development, and manufacturing company in Milford, Nebraska.

M2M Expo

2009-04-14T22:27:00.003-05:00

We are getting things ready for our booth at the M2M Expo. If you have done trade shows and have some tips, I would be thankful for the advice!

Update: We are no longer going to the M2M Expo in favor of going to Sensors Expo. Look for details soon!

Trying to Make EAV Work

2009-04-14T10:17:00.003-05:00

I heart PostgreSQL. I heart data. I heart challenges. So when my friend David Fetter challenged me recently on a db design that stored data using the EAV model, I felt a bit cornered and not sure how to tackle the downsides of EAV.

There seems to be a lot of interesting discussions out there for and against EAV. The project I am currently working on requires flexibility as we, the developers, don't know what we need to add to the system. We have a fairly rigid set of requirements about the data and have metadata tables to help in the process.

Our application will be responsible for making sure that the data is stored properly. Secondly, all data is stored as NUMERIC, not as VARCHAR/TEXT as what most EAV systems seem to use. Thirdly, we assign what kind of data is being stored and use a lookup table for finite textual data.

So, now the question is, are we setting ourselves up for disaster going with EAV? I hope not.

List with Timestamp as Bullet

2008-03-12T15:47:00.003-05:00

I was trying to come up with a way to create an Unordered List with a timestamp as the "bullet" and come up with the following:

CSS


ul.timestamp {
    margin: 0;
    padding: 0;
    list-style: none;
    }
ul.timestamp li span {
    float: left;
    display: block;
    width: 125px;
    margin: 0 8px 0 -133px;
    padding: 0;
    text-align: right;
    color: #444;
    font-weight: bold;
    text-indent: -133px;
    }
ul.timestamp li {
    padding-left: 133px;
    }

HTML


<ul class="timestamp">
    <li><span>3/9/2008 11:15:02 AM</span>This is a list item with a longer copy text so to check the wrapping of this and make sure it stays in line with the timestamp and the margins set up in the CSS.</li>
    <li><span>3/7/2008 11:15:02 AM</span>Bullet item #2</li>
    <li><span>3/7/2008 11:15:02 AM</span>Bullet item #3</li>
</ul>

EXAMPLE

3/9/2008 11:15:02 AMThis is a list item with a longer copy text so to check the wrapping of this and make sure it stays in line with the timestamp and the margins set up in the CSS.

3/7/2008 11:15:02 AMBullet item #2

3/7/2008 11:15:02 AMBullet item #3

This works pretty slick!