postgresql – Kyle Cordes

Data/API Servers/Services for Single Page Web Applications

A Client needs a Server, or at least a Service

Over the last few years the team at Oasis Digital has created various complex “single page” web applications, using AngularJS, KnockoutJS, and other tools. These applications’ assets and code are be statically hosted (cheaply on CDN if needed), but of course each application needs a backend data service (possibly comprised of smaller or even “micro” services internally) to provide data and carry the results of user operations to permanent storage.

Context and Background

Our work context is typically data- and rule-centric line-of-business applications, hosted in a company data center or on virtual/cloud or dedicated hardware, or occasionally a (more cloudy) PaaS like Heroku; so advice here is for that context. “Backend as a Service” solutions are appealing, but I don’t have any experience with those.

The systems we build most often store data in a traditional RDBMS like PostgreSQL or MS SQL Server, but data service needs are similar with a NoSQL or other non-RDBMS data store. (Among the many topics I should write about: making effective, justified use of polyglot persistence with CQRS and related ideas.)

We have also worked extensively with multi-tier desktop applications, which have essentially the same data service needs, albeit with different data serialization formats. As a result, we have worked on and thought about data services extensively.

Building a Data API Service

For convenient, rapid, efficient development of robust data/API services, your tool set should have as many as possible of the following:

A server-side programming language / platform, with its runtime environment, native or VM.
A way of routing requests (hopefully with a RESTful pattern matching approach).
Automatic unmarshaling of incoming data into data structures native to the programming language. If you find yourself writing code that takes apart JSON “by hand”, run away.
Similarly, automatic marshaling of ordinary data structures into JSON. If you see code which uses string concatenation to build JSON, it should either be to meet some specific needs for extra marshalling performance of a huge data structure, or shouldn’t be there at all.
Analogous support for other data formats. Successful systems live a long time. If you create one, someone will want to talk to it in a situation where JSON isn’t a good fit. A few years from now, you might think of JSON the way we think of XML now. Therefore, stay away from tools which are too deeply married to JSON or any other single data format.
If you’re using a relational database, your data server toolkit should make it quite easy to talk to that database. Depending on the complexity of the relationship between your data services and the underlying data store, either an object relational mapper (ORM) or perhaps a table/query mapper is suitable. If you find yourself working with the low-level database API to pluck fields out, you are looking at a long investment with little payoff.
Good support for a wide variety of database types (relational and otherwise). This reduces the risks from future database support requirements.
A reasonable error handling system. Things will go wrong. When they do, an appropriate response should flow back to the client code, while a fully detailed explanation should land in a log or somewhere else suitable – ideally without re-inventing this on each project or for every API entry point.
Depending on application needs, some way of maintaining a persistent connection (SSE, websocket, or fallback) to stream back changing information.
A declarative way to specify security roles needed for subsets of your API (RESTful or otherwise).
Monitoring / metrics.
Scalability.
Efficiency, so you are less likely to need to scale, and so that if you must scale, the cost isn’t awful.
Rapid development supported by good tooling. Edit-compile-run cycles of a few seconds.
A pony.

Is That All?

This is quite a checklist; but a toolset lacking these things means that a successful, growing project will probably need to reinvent many of them – shop carefully. In later posts, I’ll write more about particular technology stacks.

Mobile Workforce Management, a Five Year SaaS Mission Completed

Here is the story of a substantial chunk of my professional life over the last five years. I didn’t tell this story in real time (for various good reasons), though I have mentioned bits of it in various talks.

In 2004, I co-founded a vertical market Software as a Service firm, Mobile Workforce Management (MWM). MWM serves the underground utility locating industry with a Software-as-a-Service offering, TicketRx. Most people interact with this industry only with an occasional “call before you dig”, and think of it as just a phone number. However, there is a lot more to that industry than a phone number; there are numerous companies involved, each interacting with the others to complete the work. It is a vertical market niche with specific software needs, which our product met.

I personally wrote and administrated the first version of the TicketRx software and the first few servers, and my cofounder personally performed analysis, support, documentation, operations, and mountains of other work. We then incrementally hired a team to expand our capacity (and make ourselves replaceable), building an organization to serve its customers. Our software startup became an operating business with a life of its own.

Fast forward… five years of incremental and accelerating growth…

In 2009, the opportunity presented itself to sell MWM, and we did so. MWM is still there, operating fine without me. The press release about the sale is online and is also reproduced below. It is amusing to see how PR-speak invaded, labeling TicketRx as “custom” even though its whole essence was to not be custom, but rather off-the-shelf and highly configurable. Perhaps it is custom in the very broad sense of being industry-specific.

As is common in deals like this, the “terms of the transactions were not disclosed”, along with many other interesting bits. Still, I have a great number of lessons-learned to share in future posts and talks; and as of early 2010, there is extensive information about the product itself on the company’s web site, http://mwmsolutions.com/

Where does that leave me?

For some reason, the notion of having two companies then selling one, has been surprisingly hard to communicate. I still own Oasis Digital Solutions Inc., a consulting / custom software development firm, and work more intensely than ever with its customers and developers. Oasis Digital is growing up rapidly, with marketing efforts and ever-increasing process and organizational maturity.

Growing a product/SaaS business was a great experience, and one I hope to repeat. I’m actively on the lookout for another non-consulting software business to launch, when the time and opportunity are right.

St. Louis-based MWM sold to Consolidated Utility Services Inc.

Custom software product TicketRx, provides cost effective job tracking for utility locating company

Jan. 19, 2010: ST. LOUIS, Mo. – St. Louis-based Mobile Workforce Management has announced the successful sale of its company assets, including its commercial software as a service product, TicketRx, to Consolidated Utility Services Inc., an underground utilities locator company based in Omaha, Nebraska.

“With TicketRx, we created a customizable system to provide field service staff remote access and management tools for receiving, routing and tracking tickets and job assignments in real time,” said Kyle Cordes, a principal of Mobile Workforce Management (MWM) and owner of local consulting firm Oasis Digital. “We started TicketRx in 2004, and experienced great success with over 1,000 users and a growth rate of 25% per year.”

The sale of TicketRx to Consolidated will allow the company to integrate the system into their full spectrum of services that serve to protect utility companies’ underground infrastructure. In addition to ticket tracking, Consolidated offers clients systems for locating utilities, performing field audits and managing claims.

“Creating a comprehensive software solution such as TicketRx that fulfills a complex set of needs and watching it operate successfully is a very rewarding experience,” said Cordes. “I am confident the custom software solution we developed will make Consolidated’s business stronger.”

TicketRx processes one-call tickets from ‘call before you dig’ call centers or utility companies and then routes the work to the appropriate field worker. Technicians have immediate access to the information they need, which improves on-time performance. And managers have easy-to-use tools for scheduling, balancing work loads and providing emergency notifications. The system tracks all activity on the ticket, which can be used to create invoices and reports.

TicketRx is a Software as a Service (SaaS) model, a growing trend in which companies are adopting easy-to-use services that can be integrated efficiently, with minimal risk and at a cost advantage. With SaaS companies can have the service they need without the responsibility for their own internal servers, data centers or related IT staff, saving them time and money. According to industry analyst firm Gartner by 2010, 30 percent of all new software will be delivered as a service

Since the sale of MWM, Cordes will focus his energies on Oasis Digital. “The sale of MWM and TicketRx allows us to concentrate our efforts first on our consulting clients here in St. Louis and elsewhere, then later on our next SaaS opportunity,” Cordes said.

About Oasis Digital Solutions Inc.

St. Louis-based Oasis Digital develops custom software for workflow management, application integration, business process automation, and handheld devices for companies nationwide. Oasis Digital can produce a whole project or subsystem depending on the needs of the client, using a variety of computer languages and technologies. Fore more information, visit www.oasisdigital.com.

About TicketRx

TicketRx is a product of Mobile Workforce Management, LLC, and is a software-as-a-service program for the underground utility locating industry that can manage locating tickets from one-call centers or directly from the utility companies. TicketRx offers a unique combination of a broad feature set, fast setup and quick learning time. For more information, visit www.mwmsolutions.com/ticketrx.

Unrealistic Cost Expectations, and How to Fix Them

I suppose there have been hiring companies with wildly unrealistic cost expectations forever; the internet just makes it more visible. Take, for example, this job post for PostgreSQL expert, which I republish here for criticism and comment, anonymized:

We are looking for a postgre expert with indepth Oracle skill to help with the following project:

1) migrate all data and structure from Oracle 9i to PostgreSQL 8.3.
2) create a script to capture daily differentials on Oracle db and export the changes to PostgreSQL
3) create a script to automate the import the Oracle differential export into PostgreSQL on a daily basis
4) full documentation

Will provide both Oracle and Postgre dev box to work with, interested party please send email to (REDACTED)

Job budget between USD 300 to USD 400. However need this delivered within one week from job acceptance, or before Dec. 31, 2009, whichever come first.

To clarify for anyone reading this, this is not my job post. I am not looking for a PG expert. Do not contact me to apply for this work.

This fellow wants:

An experienced guru
In two quite complex technologies, one of which is a very expensive technology
To do a non-trivial project, and presumably, to be responsible for the results actually working
Who can do their project Right Now
Over the Christmas holiday, at least here in the US
For a $400

It seems to me that this person, in addition to creating some annoyance on the mailing list where they posted it, simply has wildly unrealistic expectations. As a result, they are likely to end up disappointed with any real person applying for their work. They will quite likely get multiple applicants, eager to attack the job for the budget shown; so I am not suggesting that noone will do it.

Instead, I estimate that most likely a week will come and go, $400 will come and go, and they will not have a working system. With some struggle and legwork on the hiring end, they may get the end result for a surprisingly small multiple of the proposed budget… but if they started with a more realistic amount in the first place, they’d likely get there faster and with less work on the hiring end.

A broader lesson, that I’ve learned through experience in the trenches, is that if you don’t have a good feel for the price range, start with no price range. Then talk with the first handful of applicants, listening carefully. With a couple of hours (for a simple request), you’ll probably have at least some realistic sense of the size of your project. With this knowledge, you can make more realistic and credible job posts, yielding more and better applicants.

Were you hoping for an approach to fix someone else’s unrealistic expectations? Sorry, I’ve not found a good way to do this. The best you can do is to find and fix your own.

Analyzing PostgreSQL logs with pgFouine (on Ubuntu 8.04)

pgFouine is a slick, useful, and free tool for analyzing PostgreSQL query workloads. It works without any impact on the running PostgreSQL: it analyzes the PG log output. The caveat is that it needs PG configured to write the right kind of log output.

Sadly, as of version 8.3 PG has a wrinkle in how it writes its logs: multi-line queries can get jumbled together in the stderr-based log, resulting in erroneous output from pgFouine. Hopefully a future PG will be able to write its logs without this issue, but in the meantime, the answer it to use syslog logging instead of native PG logging. This isn’t a bad idea anyway, since syslogd and friends are well proven.

On our project where this need arose, we use the Ubuntu Linux distribution, currently version 8.04. Ubuntu’s PG package sets up native stderr logging; here are the steps needed to change that to syslog logging instead. These steps are about the same for other distributions (or for manual compiles), but with different paths.

The setting shown here for log_min_duration_statement will log all queries that take more than 4 seconds to complete. Depending on your server, workload, and type of workload (OLTP vs. OLAP), this might be too high or too low.

Edit your postgresql.conf file:

vi /etc/postgresql/8.3/main/postgresql.conf

log_destination = 'syslog'
log_line_prefix = 'user=%u,db=%d '
log_min_duration_statement = 4000
silent_mode = on
logging_collector = off

With PostgreSQL 8.2, set redirect_stderr instead of logging_collector:

redirect_stderr = off

Next, setup where syslog will store the data, and add “local0,none” to the ;-separated list of what goes in to var/log/message. On my system it ended up looking like this, but of course it may vary depending on what else you’ve set up in syslog:

vi /etc/syslog.conf

# add this:

local0.*        -/var/log/postgresql/main.log

# edit this:

    *.=info;*.=notice;*.=warn;\
    auth,authpriv.none;\
    local0.none;\
    cron,daemon.none;\
    mail,news.none          -/var/log/messages

Restart syslogd to make the change take effect:

/etc/init.d/sysklogd restart

Then restart PG so it starts logging there:

/etc/init.d/postgresql-8.3 restart

Note that we are putting these new logs in the existing /var/log/postgresql directory which the Ubuntu PG package creates; if you install PG manually, create such a directory yourself, or set up syslog to write to the pg_log directory. The existing logs there will remain, holding only the messages from PG startup and shutdown (via /etc/init.d/postgresql). I find this unhelpful but harmless.

Log Rotation

By putting the files in this preexisting location, we take advantage of the log rotation already set up in /etc/logrotate.d/postgresql-common. On a busy server, you may want to adjust the rotation setting therein from weekly to daily, or add a line with “size 1000k” or so. Take a look at “man logrotate” to learn about many useful options, such as the ability to have these logs emailed to your DBA as they rotate.

pgFouine

Finally, you are ready to analyze logs. If you plan to analyze them on the same machine where you run your database (probably not a great idea), proceed (on Ubuntu) to get the PHP command line executable:

apt-get install php5-cli

Then download the pgFouine tarball, quietly curse the lack of an Ubuntu package, put it in your $PATH, and run it. Don’t be alarmed by its .php file extension; PHP is a usable (though not particularly charming) language for writing command line tools, as well as dynamic web pages.

cd /var/log/postgresql

pgfouine.php -file main.log  >somefile.html

View the HTML file in your web browser, and dig in to your worse queries. Good luck.

Multicast your DB backups with UDPCast

At work we have a set of database machines set up so that one is the primary machine, making backups once per day, and several other machines restore this backup once per day, for development, ad hoc reporting, and other secondary purposes. We started out with an obvious approach:

back up on server1, to a file on server1
SCP or rsync the file from server1 to server2
restore the DB on server2

… but over time as the data has grown the inefficiency of such an approach become equally obvious: the backup data goes back and forth across the network and to/from disk repeatedly. These steps only count the backup data, not the live storage in the DBMS:

On to the disk on server1 (putting additional load on the primary DB machine)
Off the disk on server1 (putting additional load on the primary DB machine)
On to the disk on server2
Off the disk on server2

This is also wasteful from a failure-recovery point of view, since the place we are least likely to need the backup is on the machine whose failure would lead us to need a backup.

Pipe it over the network instead

The project at hand uses PostgreSQL on Linux, so I’ll show example PG commands here. The principles apply equally well to other DBs and platforms of course, though some DBMSs or platforms might not offer backup and restore commands that stream data. (I’m looking at you, MS SQL Server!)

What we need is a pipe that goes over the network. One way to get such a pipe is with ssh (or rsh), something like so, run from server1:

pg_dump -Fc dbnameonserver1 | ssh server2 pg_restore -Fc -v -O -x -d dbnameonserver2

This variation will simultaneously store the backup in a file on server1:

pg_dump -Fc dbnameonserver1 | tee dbname.dump | ssh server2 pg_restore -Fc -v -O -x -d dbnameonserver2

This variation (or something close, I last run this several days ago) will store the backup in a file on server2 instead:

pg_dump -Fc dbnameonserver1 | ssh server2 "tee dbname.dump | pg_restore -Fc -v -O -x -d dbnameonserver2"

To reduce the CPU load from this, adjust SSH to use less CPU-intensive encryption, or avoid that entirely with rsh (but only if you have a trusted / local network).

Multicast / Broadcast it over the network instead

The above commands are good for point-to-point streaming backup / restore, but the scenario I have in mind has one primary machine and several (3, 4, or more) secondary machines. One answer is to run the above process repeatly, once for each secondary machine, but that sends the whole backup over the network N times. Inefficiency! (==Blashphemy?)

To avoid that, simply use UDPCast. It’s a trivial install on Debian / Ubuntu:

apt-get install udpcast

(Be warned though: there is at least one annoying bug in the old (2004) UDPCast offered off-the-shelf in Debian / Ubuntu as of 2008. You might need to the latest UDPCast source from its web site above.)

Run this on the server1:

pg_dump -Fc dbnameonserver1 | udp-sender --min-wait 5 --nokbd

Run this on the server2 .. serverN:

udp-receiver --nokbd | pg_restore -Fc -v -O -x -d dbnameonserverN

With this approach, the backup data will be multicast (or broadcast, if multicast does not work and if all the machines are on the same segment), only traversing the network once no matter how many receiving machines are set up. udp-receiver has a –pipe option, but I found that I occasionally get corruption with huge (50GB+) transfer, when using –file or –pipe. So I recommend this, to save a copy on the receiving end:

udp-receiver --nokbd | tee mydatabase.dump | pg_restore -Fc -v -O -x -d dbnameonserverN

Or perhaps you want to just receive and store the backup on a file server, with this:

udp-receiver --nokbd >mydatabase.dump

To make all this happen automatically, you’ll set the sender to start at the same time as the receivers in “cron” on the relevant machines. Use NTP to keep their clocks in sync, and adjust the udp-sender and udp-receiver options as needed to get the whole process to start smoothly in spite of minor timing variations (–min-wait t, –max-wait t).

As with the previous suggestion for rsh, the data will travel unencrypted over your network, so do this only if you trust your network (such as a LAN segment between your database servers).

Multicast / broadcast is very useful technology, and with UDPcast it is quite easy to use. UDPcast also implements a checksum/retransmit mechanism, it is not a “bare”, loss-prone UDP transmission.

Network / System Monitoring Smorgasbord

At one of my firms (a Software as a Service provider), we have a Zabbix installation in place to monitor our piles of mostly Linux servers. Recently we look a closer look at it and and found ample opportunities to monitor more aspects, of more machines and device, more thoroughly. The prospect of increased investment in monitoring led me to look around at the various tools available.

The striking thing about network monitoring tools is that there are so many from which to choose. Wikipedia offers a good list, and the comments on a Rich Lafferty blog post include a short introduction from several of the players. (Update – Jane Curry offers a long and detailed analysis of network / system monitoring and some of these tools (PDF).)

For OS level monitoring (CPU load, disk wait time, # of processes waiting for disk, etc.), Linux exposes extensive information with “top”, “vmstat”, “iostat”, etc. I was disappointed to not find any of these tools conveniently presenting / aggregating / graphing the data therein. From my short look, some of the tools offer small subsets of that data; for details, they offer the ability for me to go in and figure out myself what data I want in and how to get it. Thanks.

Network monitoring is a strange marketplace; many of the players have a very similar open source business model, something close to this:

core app is open source
low tier commercial offering with just a few closed source addons, and support
high tier commercial offering with more closed source addons, and more support

I wonder if any of them are making any money.

Some of these tools are agent-based, others are agent-less. I have not worked with network monitoring in enough depth to offer an informed opinion on which design is better; however, I have worked with network equipment enough to know that it’s silly not to leverage SNMP.
I spent yesterday looking around at some of the products on the Wikipedia list, in varying levels of depth. Here I offer first impressions and comments; please don’t expect this to be comprehensive, nor in any particular order.

Zabbix

Our old installation is Zabbix 1.4; I test-drove Zabbix 1.6 (advertised on the Zabbix site as “New look, New touch, New features”. The look seemed very similar to 1.4, but the new feature list is nice.

We most run Ubuntu 8.04, which offers a package for Zabbix 1.4. Happily, 8.04 packages for Zabbix 1.6 are available at http://oss.travelping.com/trac.

The Zabbix agent is delightfully small and lightweight, easily installing with a Ubuntu package. In its one configuration file, you can tell it how to retrieve additional kinds of data. It also offers a “sender”, a very small executable that transmits a piece of application-provided data to your Zabbix server.

I am reasonably happy with Zabbix’s capabilities, but I have the GUI design to be pretty weak, with lots of clicking to get through each bit of configuration. I built far better GUIs in the mid-90s with far inferior tools to what we have today. Don’t take this as an attack on Zabbix in particular though; I have the same complaint about most of the other tools here.

We run PostgreSQL; Zabbix doesn’t offer any PG monitoring in the box, but I was able to follow the tips at http://www.zabbix.com/wiki/doku.php?id=howto:postgresql and get it running. This monitoring described there is quite high-level and unimpressive, though.

Hyperic

I was favorably impressed by the Hyperic server installation, which got two very important things right:

It included its own PostgreSQL 8.2, in its own directory, which it used in a way that did not interfere with my existing PG on the machine.
It needed a setting changed (shmmax), which can only be adjusted by root. Most companies faced with this need would simply insist the installer run as root. Hyperic instead emitted a short script file to make the change, and asked me to run that script as root. This greatly increased my inclination to trust Hyperic.

Compared to Zabbix, the Hyperic agent is very large: a 50 MB tar file, which expands out to 100 MB and includes a JRE. Hyperic’s web site says “The agent’s implementation is designed to have a compact memory and CPU utilization footprint”, a description so silly that it undoes the trust built up above. It would be more honest and useful of them to describe their agent as very featureful and therefore relatively large, while providing some statistics to (hopefully) show that even its largish footprint is not significant on most modern servers.

Setting all that aside, I found Hyperic effective out-of-the-box, with useful auto-discovery of services (such as specific disk volumes and software packages) worth monitoring, it is far ahead of Zabbix in this regard.

For PostgreSQL, Hyperic shows limited data. It offers table and index level data for PG up through 8.3, though I was unable to get this to work, and had to rely on the documentation instead for evaluation. This is more impressive at first glance than what Zabbix offers, but is still nowhere near sufficiently good for a substantial production database system.

Ganglia

Unlike the other tools here, Ganglia comes from the world of high-performance cluster computing. It is nonetheless apparently quite suitable nowadays for typical pile of servers. Ganglia aims to efficiently gather extensive, high-rate data from many PCs, using efficient on-the-wire data representation (XDR) and networking (UDP, including multicast). While the other tools typically gather data at increments of once per minute, per 5 minutes, per 10 minutes, Ganglia is comfortable gathering many data points, for many servers, every second.

The Ganglia packages available in Ubuntu 8.04 are quite obsolete, but there are useful instructions here to help with a manual install.

Nagios

I used Nagios briefly a long time ago, but I wasn’t involved in the configuration. As I read about all these tools, I see many comments about the complexity of configuring Nagios, and I get the general impression that it is drifting in to history. However, I also get the impression that its community is vast, with Nagios-compatible data gathering tools for any imaginable purpose.

Others

How Many Monitoring Systems Does One Company Need?

It is tempting to use more than one monitoring system, to quickly get the logical union of their good features. I don’t recommend this, though; it takes a lot of work and discipline to set up and operate a monitoring system well, and dividing your energy across more than one system will likely lead to poor use of all of them.

On the contrary, there is enormous benefit to integrated, comprehensive monitoring, so much so that it makes sense to me to replace application-specific monitors with data feeds in to an integrated system. For example, in our project we might discard some code that populates RRD files with history information and published graphs, and instead feed this data in to a central monitoring system, using its off-the-shelf features for storage and graphing.

A flip side of the above is that as far as I can tell, none of these systems offers detailed DBA-grade database performance monitoring. For our PostgreSQL systems, something like pgFouine is worth a look.

Conclusion

I plan to keep looking and learning, especially about Zenoss and Ganglia. For the moment though, our existing Zabbix, upgraded to the current version, seems like a reasonable choice.

Comments are welcome, in particular from anyone who can offer comparative information based on substantial experience with more than one of these tools.