More PostgreSQL and SystemTap
By Josh Tolley
August 5, 2009
Recently I’ve been working on a database with many multi-column indexes, and I’ve wondered how often all the columns of the index were used. Many of the indexes in question are primary key indexes, and I need all the columns to guarantee uniqueness, but for non-unique indexes, it would make sense to remove as many indexes from the column as possible. Especially with PostgreSQL 8.3 or greater, where I can take advantage of heap-only tuples[1], leaving columns out of the index would be a big win. PostgreSQL’s statistics collector will already tell me how often an index is scanned. That shows up in pg_stat_all_indexes. But for a hypothetical index scanned 100 times, there’s no way to know how many of those 100 scans used all the columns of the index, or, for instance, just the first column.
First, an example. I’ll create a table with three integer columns, and fill it with random data:
5432 josh@josh# CREATE TABLE a (i INTEGER, j INTEGER, k INTEGER);
CREATE TABLE
5432 josh@josh*# INSERT INTO a SELECT i, j, k FROM (SELECT FLOOR(RANDOM() * 10) AS i, FLOOR(RANDOM() * 100) AS j, FLOOR(RANDOM() * 1000) AS k, GENERATE_SERIES(1, 1000)) f;
INSERT 0 1000
5432 josh@josh*# CREATE INDEX a_ix ON a …postgres
Look Ma, I made an iPhone Enterprise Application!
By David Christensen
August 3, 2009
One of our clients recently came to us for help with implementing an iPhone Enterprise application. This particular application involved deploying specific signed mail configurations for customers with iPhones. This was primarily a server-side application, although the front-end interface was created using the DashCode development tools from Apple. Although this was a web application, the DashCode integration enabled us to create the interface in a way that it appeared to be a native application on the iPhone. Client-side validation was performed in a way that for all intents and purposes appeared native.
The backend was a traditional CGI script which generated the .mobileconfig configuration payloads to easily integrate the customer’s mail server information into the iPhone’s Mail application. The backend was written to support any number of accounts deployed per customer, and the resulting payload was signed and verified by the customer’s PEM key.
We integrated openssl’s pkcs12 support to transparently sign the generated dynamic mobileconfig. This was keyed off of the client’s deployment key, so all of the generated keys were automatically indicated as trusted and registered by …
mobile
Slony, sl_status and diagnosing a particular type of lag
By Selena Deckelmann
July 31, 2009
During some routine checking on a slony cluster, Greg noticed something curious. Replication was still happening between the master and a couple slaves, but we were seeing our indicator for lag inside of slony increasing.
To check out the status of slony replication, you will typically take a look at the view ‘sl_status’:
mydatabase=# select * from sl_status;
st_origin | st_received | st_last_event | st_last_event_ts | st_last_received | st_last_received_ts | st_last_received_event_ts | st_lag_num_events | st
_lag_time
-----------+-------------+---------------+----------------------------+------------------+----------------------------+---------------------------+-------------------+---------
----------------
2 | 1 | 2697511 | 2008-04-30 02:40:06.034144 | 2565031 | 2008-04-14 15:31:32.897165 | 2008-04-14 16:24:08.81738 | 132480 | 15 days
10:16:03.060499
(1 row)
This view pulls data out of sl_event and sl_confirm, two tables that keep track of the forward progress of replication. Every time there is an event—SYNCs, DDL changes, slony administrative events—a row is added to sl_event. Slony is very chatty …
postgres
Comparing databases with check_postgres
By Greg Sabino Mullane
July 28, 2009
One of the more recent additions to check_postgres, the all-singing, all-dancing Postgres monitoring tool, is the “same_schema” action. This was necessitated by clients who wanted to make sure that their schemas were identical across different servers. The two use cases I’ve seen are servers that are being replicated by Bucardo or Slony, and servers that are doing horizontal sharding (e.g. same schema and database on different servers: which server you go to depends on (for example) your customer id). Oft times a new index fails to make it to one of the slaves, or some function is tweaked on one server by a developer, who then forgets to change it back or propagate it. This program allows a quick and automatable check for such problems.
The idea behind the same_schema check is simple: we walk the schema and check for any differences, then throw a warning if any are found. In this case, we’re using the term “schema” in the classic sense of a description of your database objects. Thus, one of the things we check is that all the schemas (in the classic RDBMS sense of a container of other database objects) are the same, when running the “same_schema” …
database open-source perl postgres
SDCH: Shared Dictionary Compression over HTTP
By Jon Jensen
July 27, 2009
Here’s something new in HTTP land to play with: Shared Dictionary Compression over HTTP (SDCH, apparently pronounced “sandwich”) is a new HTTP 1.1 extension announced by Wei-Hsin Lee of Google last September. Lee explains that with it “a user agent obtains a site-specific dictionary that then allows pages on the site that have many common elements to be transmitted much more quickly.” SDCH is applied before gzip or deflate compression, and Lee notes 40% better compression than gzip alone in their tests. Access to the dictionaries stored in the client is scoped by site and path just as cookies are.
The first client support was in the Google Toolbar for Internet Explorer, but it is now going to be much more widely used because it is supported in the Google Chrome browser for Windows. (It’s still not in the latest Chrome developer build for Linux, or at any rate not enabled by default if the code is there.)
Only Google’s web servers support it to date, as far as I know. Someone intended to start a mod_sdch project for Apache, but there’s no code at all yet and no activity since September 2008.
It is interesting to consider the challenge this will have on HTTP proxies that filter …
browsers hosting networking security compression
Fedora goes up to eleven
By Daniel Browning
July 23, 2009
I upgraded to Fedora Core 11 this week, up from 9. What really surprised me is how fast it is. I don’t recall ever having such a noticably faster and responsive desktop after a distro upgrade. Everything is more responsive and instantaneous. Even though FC9 didn’t seem particularly slow, I realize now that I had been spending time just waiting for the software to catch up. I don’t know where the credit is due. Could be anything from the kernel to XFCE to the apps. But I like it.
Normally, most SELinux issues occur when it is enabled. But after the upgrade I ran into one of the opposite variety: the gedit program threw an error on every file save. The 244605 and 477070 tickets might be the same issue I ran into. I worked around the problem by switching to ‘kate -u’.
I need at least four different text editors in order to enjoy using the computer. How could anyone stand the monotony of a single text editor all day? Right now I’m doing:
- gvim for detailed things
- vanilla vim for when gvim is too awesome.
- kate for vogon poetry
- jedit for when I get that certain IDE feeling (you know the one)
- emacs for inducing carpal tunnel syndrome
It’s also important to switch out text editors at …
redhat linux
OSCON so far! Filesystem information bonanza on Wednesday
By Selena Deckelmann
July 23, 2009
Wednesday was the first official day of OSCON, and I spent it elbow deep in filesystems. The morning was kicked off with Val Aurora delivering a great overview of Btrfs, a new fileystem currently in development. Some of the features include:
- Copy on write filesystem
- Cheap, easy filesystem snapshots
- Dynamically resizable partitions
- Indexed directory structure
- Very simple administration
Val demonstrated basic functionality, including creating snapshots and creating a Btrfs filesystem on top of an ext3 filesystem. Cool stuff! The filesystem is still under heavy development, but seems very promising.
Next I saw Theodore Ts’o, the primary developer behind ext4, talk about the future of filesystems and storage. He referenced a great paper that dives deep into the economics behind SSD (solid state drives) and platter hard drive manufacturing. One interesting calculation was that even if we could convert all the silicon fabs to manufacture flash, would only be able to covert about 12% of the world-wide capacity of hard drive production. Because of this, Theodore believes that it is going to be challenging for the cost of SSDs to drop to the point where it becomes cost competitive with …
conference postgres
Gmail Contacts Notes Converter
By Jon Jensen
July 21, 2009
As I mentioned previously, I recently got a Google Ion phone running Android. I recently began using it as my main mobile phone, and thus needed to finally migrate the contacts from my Nokia 6126 phone to Android.
This is apparently easy to do by first copying all the contacts from the Nokia 6126 internal memory to the SIM card, then moving the SIM card to the Ion and importing the contacts. But that only works if all your contacts fit on the SIM card. If not, they’re truncated, and you have to delete many contacts on the Nokia to fit more, which would be a nonreversable move.
Several posts describe ways to do the export and import, such as this one that didn’t really apply to my phone, and this one that involves VCF export & import which I didn’t see a way to do.
Ultimately I found an article that described Nokia’s PC Suite software that I’d never heard of before, which I downloaded on an old Windows machine and used to download the contacts from the phone via Bluetooth, then export to a CSV file and import into Gmail. So far, so good.
Except as this post and another post describe, then all the contact data showed up in a single Notes field, useless for dialing or emailing.
I …
mobile