solaris

I've been following reports of the 8.2.3 statistics bug for a few weeks now, mainly with intrest/concern as we run some 8.2.3 databases at [http://www.omniti.com/home OmniTI]. The reports all seemed reasonable (see emails [http://archives.postgresql.org/pgsql-hackers/2007-02/msg01751.php here], [http://archives.postgresql.org/pgsql-hackers/2007-02/msg01902.php here], and [http://archives.postgresql.org/pgsql-hackers/2007-03/msg00068.php here]), but it seemed odd because I haven't experienced such pains myself. I was kind of chalking it up to our 8.2.3 instances just not being heavily taxed (they can't all be TB databases [[image /xzilla/templates/default/img/emoticons/wink.png alt=";-)" style="display: inline; vertical-align: bottom;" class="emoticon" /]] ), but with some people [http://archives.postgresql.org/pgsql-hackers/2007-03/msg01529.php swearing off the 8.2 branch] until this gets fixed, I thought it might be worth a closer look to make sure of what was going on. I don't have answers per say, but I do have some information... I ran a few tests on our machines that run Solaris and PostgreSQL (compiled with Sun Studio, not GCC):





Of course, this is Solaris, so I poked [http://www.lethargy.org/~jesus/ Theo] for some dtrace-fu, and (in the end) got the following results:



The first couple times I did this with a lower number of calls (1000 range) the numbers didnt seem so bad, but the ratio looked sketchy so I kicked it up a notch to get a better reproduction and sure enough, they start to look at lot like those reports on -hackers. This makes more sense as the bug in question isn't something that should be able to be optimized away.



So what does this mean? I dunno... if you do a fair amount of testing to make sure your servers can handle the load you're going to be putting on them, I don't see much reason to avoid 8.2.3 (Again, our boxes are humming along fine). Even if you think this will cause you a performance issue, testing your schema and application against any of the 8.2.x branch is a good way to spend time while you wait for 8.2.4 (the fix is in CVS now, so we should see a released fix soon), and in many cases resolving those types of fixes are generally more time consuming anyway.

I've heard a lot of talk recently about Yahoo's new service "Yahoo Pipes". For those that haven't seen it, it basically allows you to mash-up rss feeds to create your own customized feed for reuse and display. It's not exactly revolutionary but it does lower the barrier to entry for a number of folks. In any case, last night, by way of log buffer and David Van Couvering, I came across Alex Iskold's interesting entry on the topic comparing the idea of Yahoo Pipes to that of Relational Databases. A good read for sure, but it struck me that maybe if you want to have "the web as database" maybe you should just put "the web in your database". Assuming your database gives you some type of remote data capabilities it shouldn't be that hard; if your database lets you do it in PHP it's downright "simple".



Yep, that pretty much does it. Now I can pass in my favorite feed and get back a standard sql result set.



What's better is I can make all of this transparent...



At this point we can look at all of our blogs just like any other type of data in our database, and we can do all kinds of (quasi) interesting stuff, like grab the list of recent posts



or maybe plot the relative activity of each of us on our blogs



So there you have it. Granted, this may not be the most practical application of this functionality, but being able to take any information on the internet and present as a standard object in your database is bound to come in handy at some point.

So last night I ended up having to restart my database. As it turns out, [http://people.planetpostgresql.org/xzilla/index.php?/archives/287-killing-invincible-processes-with-dbx.html killing those processes with dbx] cause each process to shut down uncleanly, leaving locks open and the database thinking that those transactions were still open. This was causing vacuums to be unable to reclaim any space, which was slowly causing performance degredation, and eventually would have caused things to go kablooy. What does that look like? Heres one view of it (and no, you never want to see something like this on your databases)



Yep, those process id's were the ones I killed from yesterday. So it looks like calling exit from dbx is not safe, and you really should not do it. I do think that using something similar might work if you can find the right call to make, I'll be doing some testing on that later, but for now remember, don't use dbx for killing processes!

So yesterday I was doing some maintainece on my database, [http://www.postgresql.org/docs/8.2/interactive/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE reviewing index usage] and adjusting [http://www.postgresql.org/docs/8.2/interactive/manage-ag-tablespaces.html tablespaces], when I ran across a table that could not be altered because of some long running transactions that were holding locks open. As it turned out, the transactions involved some database jobs that connect over to an Oracle database and pulling information back over to the PostgreSQL instance. Looking in pg_stat_activity I saw





Based on thier query times, I knew these jobs were bogus, because last week we had a server crash on the Oracle machine due to hardware failure. While it wasn't impossible that these jobs would continue to run even with an outage on the Oracle instance, they shouldn't take this long. First verifying that nothing was going on, I trussed the process, verified it was sleeping, and then went to cancel the query





From the above, you can see my "successful" attempt at canceling the query, followed by the query still running when looking in pg_stat_activity. More concerning is the truss output, which shows my sleeping process but also shows the kill attempt being caught and the process going back to sleep. Given the inability to cancel the query I also tried to kill the process from the command line with little sucess.





So there I was, qeuries that wouldn't die and me needing to make them stop without causing trouble. I thought about trying to play with different flags for kill, though obviously kill -9 was not an option. Unfortunatly I have seen this problem before which is caused by a bug in the OCI drivers, where you just can't kill the process, but generally when I have gotten into this state I wasable to kill the process from the Oracle side and continue on. In this case there was no Oracle side, and short of a shutdown I didn't seem to have many options. Luckily one option I had was running this scenario by Theo, who said to try attaching with dbx and see if I could stop it with an exit call. I was a little skeptical at the idea, but figured it was worth a shot. For those not in the know, [http://en.wikipedia.org/wiki/Dbx_debugger dbx] is a debugger available on Solaris, similar to gdb. In fact attaching to a process works much the same way





Ok, it would work the same if I wasn't running truss on the process [[image /xzilla/templates/default/img/emoticons/smile.png alt=":-)" style="display: inline; vertical-align: bottom;" class="emoticon" /]]. I killed truss and reattached.





After attaching I gave the exit call a swing and lo and behold it actually claimed that it worked. A quick check from the command line verified the success.





And a double check to make sure the server was alive.





Yep, all was well. So toss another option into my bag of tricks, crazy as it might seem. Next time you end up with a connection that wont die, you might try using dbx, the DataBase connection eXterminator!

Well, after [http://people.planetpostgresql.org/xzilla/index.php?/archives/91-Is-Sun-getting-ready-to-join-the-PostgreSQL-community.html some speculation], they have made it official. From the [http://news.com.com/Sun+backs+open-source+database+PostgreSQL/2100-1014_3-5958850.html article on CNet]:



"We're going to take (the database) and do tighter integration with Solaris and support it on a worldwide basis," Loiacono said. The company expects to offer database-related services by next month. "



Time to check off "Get the backing of a major hardware vendor" from the World Domination task list [[image /xzilla/templates/default/img/emoticons/smile.png alt=":-)" style="display: inline; vertical-align: bottom;" class="emoticon" /]]



[http://tb.news.com/tb.cgi/2100-1014_3-5958850 ]

I took [http://www.enterprisedb.com EnterpriseDB] for a spin this week, and was hoping to blog it, but time constraints have put that on the back burner for now. In the mean time, if you're looking for information on migrating from [http://www.oracle.com Oracle] to [http://www.postgresql.org PostgreSQL], there is a new article on [http://techdocs.postgresql.org techdocs] that describes installing PostgreSQL 8 on Solaris 10, with [http://jdbc.postgresql.org JDBC] and tips on things to be aware of if you are coming from Oracle. Thanks to Chris Drawater for this. ([http://techdocs.postgresql.org/techdocs/pg_solaris10_jdbc_v1.2.1.pdf Click here for the article, beware it is a pdf])