Finding PostgreSQL temporary_file problems with tail_n_mail
By Greg Sabino Mullane
November 10, 2011
Image by Flickr user dirkjanranzijn
PostgreSQL does as much work as it can in RAM, but sometimes it needs to (or thinks that it needs to) write things temporarily to disk. Typically, this happens on large or complex queries in which the required memory is greater than the work_mem setting.
This is usually an unwanted event: not only is going to disk much slower than keeping things in memory, but it can cause I/O contention. For very large, not-run-very-often queries, writing to disk can be warranted, but in most cases, you will want to adjust the work_mem setting. Keep in mind that this is very flexible setting, and can be adjusted globally (via the postgresql.conf file), per-user (via the ALTER USER command), and dynamically within a session (via the SET command). A good rule of thumb is to set it to something reasonable in your postgresql.conf (e.g. 8MB), and set it higher for specific users that are known to run complex queries. When you discover a particular query run by a normal user requires a lot of memory, adjust the work_mem for that particular query or set of queries.
How do you tell when you work_mem needs adjusting, or more to the point, when Postgres is writing files …
database monitoring performance postgres
Double habtm Relationship Between Models
By Steph Skardal
November 4, 2011
Oh, man! It’s been a month since my last blog article. End Pointers Brian Buchalter, Evan Tann, Phunk, and I have been working on a sizable Ruby on Rails project for a client. We’ve been excited to work with Rails 3.1 and work on a project that presents many unique and interesting web application challenges.
Today I wanted to write about the fairly simple task of defining two has and belongs to many (or many to many) associations between the same models, which is something I haven’t seen often in Rails applications.
Data Model
First, let’s look at the data model and discuss the business case for the data model. As shown above, the data model excerpt contains four tables. Users is the standard users table, which uses devise for user authentication. Groups are intended to be a group of users that will be allowed to do some combination of controller#action in our application. In our case, groups have many members (or users), but they also have many owners, who are allowed to manage the group. And obviously on the user side, users can exist as a member or an owner in many groups.
The Code
The groups_users relationship is a standard has and belongs to many relationship. The User class …
ruby rails
RPM building: Fedora’s _sharedstatedir
By Jon Jensen
October 25, 2011
When Red Hat Enterprise Linux does not offer packages that we need, EPEL (Extra Packages for Enterprise Linux) often has what we want, kept compatible with RHEL. When EPEL also doesn’t have a package, or we need a newer release than is offered, we rebuild packages from Fedora, which has consistently high-quality packages even in its “rawhide” development phase. We then distribute our packages in several compatibility-oriented Yum repositories at packages.endpointdev.com.
Of course some things in the latest Fedora are not compatible with RHEL. In rebuilding the logcheck package (needed as a dependency for another package), I found that Fedora RPM spec files have begun using the _sharedstatedir macro in /usr/lib/rpm/macros, which RHEL has never used before.
On RHEL that macro has been set to /usr/com, a strange nonexistent path that apparently came from the GNU autoconf tools but wasn’t used in RHEL. Now in Fedora the macro is set to /var/lib and is being used, as described in a Fedora wiki page on packaging.
The easiest and most compatible way to make the change without munging the system- or user-wide RPM macros is to add this definition to the top of the spec file where it’s …
hosting redhat sysadmin
Using the new version of imapfilter with mutt
By Greg Sabino Mullane
October 17, 2011
Image by Flickr user p886
My beloved mutt/imapfilter combo recently stopped working after an operating system switch. (tl;dr: that combo rocks; use ipairs instead of pairs) When my laptop wireless stopped working, and after spending some time fighting with it, I decided to simply install a new OS. As all of my important data is on a separate partition, this was not that big a deal. I ended up using Scientific Linux, as I’d heard good things about it, and it was one of the few distros that actually would install on my laptop (failures for one reason or another: Fedora, FreeBSD, Ubuntu, and OpenBSD). After the install, I simply copied my ~/.mutt directory and ~/.muttrc file into place, and similarly copied my ~/.imapfilter directory, which contained the all important config.lua file. The imapfilter program itself was not available via the normal yum repositories, so I simply grabbed the latest and greatest and did a manual install:
$ git clone https://github.com/lefcha/imapfilter.git
$ cd imapfilter
$ sudo yum install gcc lua-devel openssl-devel pcre-devel
$ make
$ sudo make install
I’ve used a lot of email clients over the years (and may have been using email longer than most …
email linux
Rails Controllers and Transactions
By Sonny Cook
October 11, 2011
Actions involving single objects in Rails are generally nicely and automatically handled. By handled, what I mean specifically, is that ActiveRecord will encapsulate saving and updating an object in a transaction and you can set up the various callbacks and validations to ensure that the object and its associations meet whatever requirements you have before you allow it to be committed to the database.
You are allowed some degree of latitude to say “Give it a shot, if it doesn’t work, then it’s no big deal.” One of the upsides is that you can throw whatever random nonsense the UI passes you right on through, and, presumably, the model correctness validation code will do double duty as input validation as well.
Which is nice, as far as it goes, but it tends to be localized. Which is to say, that your objects generally only care about validating themselves. Sometimes, it turns out to be necessary to update the state of multiple (possibly unrelated) objects simultaneously and additionally to ensure that if any part of any of these updates fail, you roll the whole thing back.
The application in mind is in a controller, where we are getting input from the browser and we have access to a …
ruby rails
PG West 2011 Re-cap
By David Christensen
October 7, 2011
I just recently got back from PG West 2011, and have had some time to ruminate on the experience (do elephants chew a cud?</note-to-self>). I definitely enjoyed San Jose as the location; it’s always neat to visit new places and to meet new people, and I have to say that San Jose’s weather was perfect for this time of year. I was also glad to be able to renew professional relationships and meet others in the PostgreSQL community.
Topic-wise, I noticed that quite a few talks had to do with replication and virtualization; this certainly seems to be a trend in the industry in general, and has definitely been a pet topic of mine for quite a while. It’s interesting to see the various problems that necessitate some form of replication, the tradeoffs/considerations for each specific problem, and wide variety of tools that are available in order to attack each of these problems (e.g. availability, read/write scaling, redundancy, etc).
A few high points from each of the days:
Tuesday
I had dinner with fellow PostgreSQL contributors; some I knew ahead of time, others I got to know. This was followed by additional socializing.
Wednesday
I attended a talk on PostgreSQL HA, which covered …
bucardo conference postgres
Viewing schema changes over time with check_postgres
By Greg Sabino Mullane
October 5, 2011
Image by Flickr user edenpictures
Version 2.18.0 of check_postgres, a monitoring tool for PostgreSQL, has just been released. This new version has quite a large number of changes: see the announcement for the full list. One of the major features is the overhaul of the same_schema action. This allows you to compare the structure of one database to another and get a report of all the differences check_postgres finds. Note that “schema” here means the database structure, not the object you get from a “CREATE SCHEMA” command. Further, remember the same_schema action does not compare the actual data, just its structure.
Unlike most check_postgres actions, which deal with the current state of a single database, same_schema can compare databases to each other, as well as audit things by finding changes over time. In addition to having the entire system overhauled, same_schema now allows comparing as many databases you want to each other. The arguments have been simplified, in that a comma-separated list is all that is needed for multiple entries. For example:
./check_postgres.pl --action=same_schema \
--dbname=prod,qa,dev --dbuser=alice,bob,charlie
The above …
audit database postgres
CRUD, RESTful, and JSON for Address Management in Interchange
By Steph Skardal
October 4, 2011
Recently, I worked on a large project for Paper Source that introduced the functionality to allow users to split orders into multiple addresses, which is especially valuable during the holiday season for gift purchase. Paper Source runs on Interchange, one of the ecommerce frameworks End Point is intimately familiar with.
This project requires CRUD functionality for address editing during the checkout process; users must be able to add, edit, list, and remove addresses to be assigned to the various items in the cart. Another challenge here is that both logged in and logged out users are required to have this functionality, where addresses modified by logged in users would persist between sessions, and addresses of logged out users are available during their session and destroyed after they end the session (or in Paper Source’s case, when they close the browser window).
With these requirements, I set off to develop an architecture that followed RESTful practices, described below:
Listing Addresses
A Perl module contains a method for listing user addresses, which is shown below in simplified form. The user_addresses scratch variable is created and contains an array of hashed …
ecommerce interchange json perl rest