pyLabs!

Cheetah textmate bundle

2011-03-05T15:58:00.001-08:00

Stack 2.0 - Mongodb, node.js, redis, ios

2011-03-04T11:25:00.001-08:00

Node.js seems to be the viable backend for scalable chat and game services

Mongodb seems to be the best tool for storing information on any backend game or mobile apps

When you compare this to what was available in 2005 , postgres/mysql and twisted/ejabberd - this is definitely a quantum improvement and 10gen definitely has made Mongodb the defacto backend replacing cassandra and many other backends

I wonder where redis fits into this ecosystem, i might presume it would be good as a caching option?

Jinja2 is good for templating, usage of jinja2 is enhanced by snippets in textmate

iOS is the winner among the smart phone oses

Apache mod_fcgid is the way to go for webpy

2010-03-19T21:41:00.000-07:00

1. automatically spawns more processes based on traffic - lighttpd automatically the spawns the max number of processes even when no traffic - which is not useful if your max-procs is 100

2. usually need to set PYTHONPATH or append to sys.path inside code.py manually

import os,sys

sys.path.append( os.path.dirname(os.path.abspath(__file__)))

3. This is how you hook app.run for webpy

if __name__ == '__main__':

if apache:

web.wsgi.runwsgi = lambda func, addr=None: web.wsgi.runfcgi(func, addr)

app.run(session_mw)

else:app.run(session_mw)

The link to the related Webpy cookbook is here

Python Accessing Caller **locals() from callee method using sys._getframe

2009-02-25T14:31:00.000-08:00

This is my first attempt at memcaching html page using cheetah

since cheetah render needs locals() i use getCallerInfo() to get the locals() and send to memcached

let me know if it is possible to better do this

notice getCallerInfo

utils.py

@log_time_func

def renderpage(key, htmlfile, deleteafter=3600):

from globaldb import mc

try:page = mc.get(key)

except:

page=None

clogger.info('except error mc.get '+ key)

if not page:

clogger.info(key+ ' rendering cheetah page')

terms = getCallerInfo(1)

#print terms

page = str(web.render(htmlfile, asTemplate=True, terms=terms))

try:mc.set(key, page, deleteafter)

except:

clogger.info('except error mc.set '+ key)

return page

@log_time_func

@memcachethis

def mcrenderpage(key, htmlfile, deleteafter=3600):

terms = getCallerInfo(2)

#print terms

return str(web.render(htmlfile, asTemplate=True, terms=terms))

def getCallerInfo(decorators=0):

'''returns locals of caller using frame.optional pass number of decorators\nFrom Dig deep into python internals http://www.devx.com/opensource/Article/31593/1954'''

f = sys._getframe(2+decorators)

args = inspect.getargvalues(f)

return args[3]

Usage

key=facebookstuff.APP_NAME+'newstart'+str(uid)

return utils.renderpage(key, 'pick.html')

Facebook sytle Notifications in couchdb

2009-01-10T15:01:00.000-08:00

Goal: To prepare a facebook style notification using couchdb
schema
class Notification(Document):
unread = BooleanField(default=True)
activity = TextField()
appname = TextField()
image = TextField()
type = TextField(default='notification')
fromuser = TextField()
touser = TextField()
link = TextField()
message = TextField()
created = DateTimeField(default=datetime.datetime.now())
time =TimeField(default=datetime.datetime.now())
date = DateField(default=datetime.date.today()

def savenotifications(fromuser, tousers, link, message, activity, image, appname, *args, **kwargs):
for touser in tousers:
if fromuser == touser:continue
notidoc = Notification( fromuser = fromuser,touser=touser, link=link, message=message, activity=activity, image = image, appname = appname)
notidoc.store(fbcouchdb)
for touser in tousers:
r=fbcouchdb.view('_view/pop/unread',key=str(touser), count=1)
for i in r:i

Bold: I even try to prepare the views when some one gets a new notifications by precalling the view unread ahead of time when a new notification for that user is inserted inorder to cause forced view regeneration

def getnotifications(self, all=False):
try:
from couchmodel import fbcouchdb
if not all:
return [ (x.id, x.value) for x in fbcouchdb.view('_view/pop/unre
ad',key=str(self.uid), count=5) ]
else:
return [ (x.id, x.value) for x in fbcouchdb.view('_view/pop/noti
fications',key=str(self.uid)) ]
except:return[]

Problem:but whenever i do getnotification it is really slow.. And the page doesnt open forever. Anyways to make couchdb usable with this db? the situation is that notifications are constantly inserted into the db all the time as things happen to the user in real time. is this not a good use case for couchdb

Using couchdb to build a EMAIL messaging solution with threaded inline replies

2009-01-07T00:23:00.001-08:00

Couchdb based Messaging system
The goal is to build an anonymous inbox with inline threaded replies and count of number of items in the inbox and sent. It works well, the only part that is hard is that the views are pretty slow the first time they are constructed

Schema

class Anonmessage(Document):
 subject = TextField()
 read = BooleanField()
 fromread = BooleanField()
 toread = BooleanField()
 msg = TextField()
 background = TextField()
 type = TextField()
 reply = TextField()
 fromuid = LongField()
 replycount = IntegerField()
 touid = LongField()
 created = DateTimeField(default=datetime.datetime.now())
 time =TimeField(default=datetime.datetime.now())
 date = DateField(default=datetime.date.today())

Permanent view for inbox:
function(d){ if(d.type != 'reply'){ emit(d.touid, d); if(d.touid != d.fromuid){ if (d.replycount>0) emit(d.fromuid, d); } } }

Count of number of messages in USER inbox
map:function(d){
if(d.type != 'reply'){
emit(d.touid, 1);
if(d.touid != d.fromuid){
if (d.replycount>0)
emit(d.fromuid, 1);
}
}
}

reduce:function(keys, values) { return sum(values)}

Count of Sent items
map:function(doc) { emit(doc.fromuid, 1) }
reduce:function(keys, values) { return sum(values)}

View to get replies
map:function(d) { if (d.type=="reply" ) emit(d.reply,d); }

Usage from python

inbox_messages = [ (x.id, x.value) for x in anondb.view('_view/truthbox/inbox',key=z.uid) ]

Ten Reasons why Couchdb is better than Mysql

2009-01-03T12:45:00.000-08:00

No schema - schema less db, which means you can develop at the speed of your thought, you dont need to do a db update everytime you add a column
everything is over http, simple http, get post, put, delete requests which means works with varnish/squid out of the box (i prefer varnish because it is clean and simple)
Attachments - you can store file attachments for eg., your greeting card to grandma can carry images, music, flash
Map Reduce - no more sql queries, use amazingly scalable map-reduce based views. Views once saved are lighting fast
Sexy Futon javascript interface, comes with a cool js interface for displaying and editing data
javascript server using mozilla spidermonkey to construct views - means no need php in flash
zero config replication - work from home with no internet
python couchdb library
bulk updates, deletes - you can store 100000 docs in one post request
Each couchdb document is just a simple JSON compatible doc, no cruft just simple
(Bonus) Uses Erlang, which means it is scalable for multicore multiprocessor machines
(Extra Bonus) Low memory requirement Takes 150MB compared to 8Gig taken by Mysql for a similar db setup
similar to zodb, but much more cleaner and intuitive
Extremely friendly community and developers - Damien, Jchris, paul davis, noah slater, chris anderson, Jan

Python Api for last.fm

2008-10-10T15:05:00.000-07:00

Pylast Python Api for last.fm

The best torrent search engine

2008-10-02T14:08:00.000-07:00

I accidentally discovered this new torrent search engine... It is very clean, googley and really fast.. When I say blazingly fast... Incredibly high speeds... .02 seconds

Do give it a spin

Recommendations : Highly Recommended

python datetime guide

2007-09-16T11:53:00.001-07:00

3. Dates and Times
Introduction

#-----------------------------
#introduction
# There are three common ways of manipulating dates in Python
# mxDateTime - a popular third-party module (not discussed here)
# time - a fairly low-level standard library module
# datetime - a new library module for Python 2.3 and used for most of these samples
# (I will use full names to show which module they are in, but you can also use
# from datetime import datetime, timedelta and so on for convenience)

import time
import datetime

print "Today is day", time.localtime()[7], "of the current year"
# Today is day 218 of the current year

today = datetime.date.today()
print "Today is day", today.timetuple()[7], "of ", today.year
# Today is day 218 of 2003

print "Today is day", today.strftime("%j"), "of the current year"
# Today is day 218 of the current year

Finding Today's Date

#-----------------------------
# Finding todays date

today = datetime.date.today()
print "The date is", today
#=> The date is 2003-08-06

# the function strftime() (string-format time) produces nice formatting
# All codes are detailed at http://www.python.org/doc/current/lib/module-time.html
print t.strftime("four-digit year: %Y, two-digit year: %y, month: %m, day: %d")
#=> four-digit year: 2003, two-digit year: 03, month: 08, day: 06

Converting DMYHMS to Epoch Seconds

#-----------------------------
# Converting DMYHMS to Epoch Seconds
# To work with Epoch Seconds, you need to use the time module

# For the local timezone
t = datetime.datetime.now()
print "Epoch Seconds:", time.mktime(t.timetuple())
#=> Epoch Seconds: 1060199000.0

# For UTC
t = datetime.datetime.utcnow()
print "Epoch Seconds:", time.mktime(t.timetuple())
#=> Epoch Seconds: 1060195503.0

Converting Epoch Seconds to DMYHMS

#-----------------------------
# Converting Epoch Seconds to DMYHMS

now = datetime.datetime.fromtimestamp(EpochSeconds)
#or use datetime.datetime.utcfromtimestamp()
print now
#=> datetime.datetime(2003, 8, 6, 20, 43, 20)
print now.ctime()
#=> Wed Aug 6 20:43:20 2003

# or with the time module
oldtimetuple = time.localtime(EpochSeconds)
# oldtimetuple contains (year, month, day, hour, minute, second, weekday, yearday, daylightSavingAdjustment)
print oldtimetuple
#=> (2003, 8, 6, 20, 43, 20, 2, 218, 1)

Adding to or Subtracting from a Date

#-----------------------------
# Adding to or Subtracting from a Date
# Use the rather nice datetime.timedelta objects

now = datetime.date(2003, 8, 6)
difference1 = datetime.timedelta(days=1)
difference2 = datetime.timedelta(weeks=-2)

print "One day in the future is:", now + difference1
#=> One day in the future is: 2003-08-07

print "Two weeks in the past is:", now + difference2
#=> Two weeks in the past is: 2003-07-23

print datetime.date(2003, 8, 6) - datetime.date(2000, 8, 6)
#=> 1095 days, 0:00:00

#-----------------------------
birthtime = datetime.datetime(1973, 01, 18, 3, 45, 50) # 1973-01-18 03:45:50

interval = datetime.timedelta(seconds=5, minutes=17, hours=2, days=55)
then = birthtime + interval

print "Then is", then.ctime()
#=> Then is Wed Mar 14 06:02:55 1973

print "Then is", then.strftime("%A %B %d %I:%M:%S %p %Y")
#=> Then is Wednesday March 14 06:02:55 AM 1973

#-----------------------------
when = datetime.datetime(1973, 1, 18) + datetime.timedelta(days=55)
print "Nat was 55 days old on:", when.strftime("%m/%d/%Y").lstrip("0")
#=> Nat was 55 days old on: 3/14/1973

Difference of Two Dates

#-----------------------------
# Dates produce timedeltas when subtracted.

diff = date2 - date1
diff = datetime.date(year1, month1, day1) - datetime.date(year2, month2, day2)
#-----------------------------

bree = datetime.datetime(1981, 6, 16, 4, 35, 25)
nat = datetime.datetime(1973, 1, 18, 3, 45, 50)

difference = bree - nat
print "There were", difference, "minutes between Nat and Bree"
#=> There were 3071 days, 0:49:35 between Nat and Bree

weeks, days = divmod(difference.days, 7)

minutes, seconds = divmod(difference.seconds, 60)
hours, minutes = divmod(minutes, 60)

print "%d weeks, %d days, %d:%d:%d" % (weeks, days, hours, minutes, seconds)
#=> 438 weeks, 5 days, 0:49:35

#-----------------------------
print "There were", difference.days, "days between Bree and Nat."
#=> There were 3071 days between bree and nat

Day in a Week/Month/Year or Week Number

#-----------------------------
# Day in a Week/Month/Year or Week Number

when = datetime.date(1981, 6, 16)

print "16/6/1981 was:"
print when.strftime("Day %w of the week (a %A). Day %d of the month (%B).")
print when.strftime("Day %j of the year (%Y), in week %W of the year.")

#=> 16/6/1981 was:
#=> Day 2 of the week (a Tuesday). Day 16 of the month (June).
#=> Day 167 of the year (1981), in week 24 of the year.

Parsing Dates and Times from Strings

#-----------------------------
# Parsing Dates and Times from Strings

time.strptime("Tue Jun 16 20:18:03 1981")
# (1981, 6, 16, 20, 18, 3, 1, 167, -1)

time.strptime("16/6/1981", "%d/%m/%Y")
# (1981, 6, 16, 0, 0, 0, 1, 167, -1)
# strptime() can use any of the formatting codes from time.strftime()

# The easiest way to convert this to a datetime seems to be;
now = datetime.datetime(*time.strptime("16/6/1981", "%d/%m/%Y")[0:5])
# the '*' operator unpacks the tuple, producing the argument list.

Printing a Date

#-----------------------------
# Printing a Date
# Use datetime.strftime() - see helpfiles in distro or at python.org

print datetime.datetime.now().strftime("The date is %A (%a) %d/%m/%Y")
#=> The date is Friday (Fri) 08/08/2003

High-Resolution Timers

#-----------------------------
# High Resolution Timers

t1 = time.clock()
# Do Stuff Here
t2 = time.clock()
print t2 - t1

# 2.27236813618
# Accuracy will depend on platform and OS,
# but time.clock() uses the most accurate timer it can

time.clock(); time.clock()
# 174485.51365466841
# 174485.55702610247

#-----------------------------
# Also useful;
import timeit
code = '[x for x in range(10) if x % 2 == 0]'
eval(code)
# [0, 2, 4, 6, 8]

t = timeit.Timer(code)
print "10,000 repeats of that code takes:", t.timeit(10000), "seconds"
print "1,000,000 repeats of that code takes:", t.timeit(), "seconds"

# 10,000 repeats of that code takes: 0.128238644856 seconds
# 1,000,000 repeats of that code takes: 12.5396490336 seconds

#-----------------------------
import timeit
code = 'import random; l = random.sample(xrange(10000000), 1000); l.sort()'
t = timeit.Timer(code)

print "Create a list of a thousand random numbers. Sort the list. Repeated a thousand times."
print "Average Time:", t.timeit(1000) / 1000
# Time taken: 5.24391507859

Short Sleeps

#-----------------------------
# Short Sleeps

seconds = 3.1
time.sleep(seconds)
print "boo"

Program: hopdelta

#-----------------------------
# Program HopDelta
# Save a raw email to disk and run "python hopdelta.py FILE"
# and it will process the headers and show the time taken
# for each server hop (nb: if server times are wrong, negative dates
# might appear in the output).

import datetime, email, email.Utils
import os, sys, time

def extract_date(hop):
# According to RFC822, the date will be prefixed with
# a semi-colon, and is the last part of a received
# header.
date_string = hop[hop.find(';')+2:]
date_string = date_string.strip()
time_tuple = email.Utils.parsedate(date_string)

# convert time_tuple to datetime
EpochSeconds = time.mktime(time_tuple)
dt = datetime.datetime.fromtimestamp(EpochSeconds)
return dt

def process(filename):
# Main email file processing
# read the headers and process them
f = file(filename, 'rb')
msg = email.message_from_file(f)

hops = msg.get_all('received')

# in reverse order, get the server(s) and date/time involved
hops.reverse()
results = []
for hop in hops:
hop = hop.lower()

if hop.startswith('by'): # 'Received: by' line
sender = "start"
receiver = hop[3:hop.find(' ',3)]
date = extract_date(hop)

else: # 'Received: from' line
sender = hop[5:hop.find(' ',5)]
by = hop.find('by ')+3
receiver = hop[by:hop.find(' ', by)]
date = extract_date(hop)

results.append((sender, receiver, date))
output(results)

def output(results):
print "Sender, Recipient, Time, Delta"
print
previous_dt = delta = 0
for (sender, receiver, date) in results:
if previous_dt:
delta = date - previous_dt

print "%s, %s, %s, %s" % (sender,
receiver,
date.strftime("%Y/%d/%m %H:%M:%S"),
delta)
print
previous_dt = date

def main():
# Perform some basic argument checking
if len(sys.argv) != 2:
print "Usage: mailhop.py FILENAME"

else:
filename = sys.argv[1]
if os.path.isfile(filename):
process(filename)
else:
print filename, "doesn't seem to be a valid file."

if __name__ == '__main__':
main()

svn deps

2007-07-15T21:38:00.000-07:00

Cheetah
Psycopg2
PIL
mx egenix
sudo python setup.py build --skip install
aspell.so

How to update to Fedora 7

2007-07-01T12:03:00.000-07:00

This is how I updated my FC6 to F7:

wget ftp://download.fedora.redhat.com/pub/fedora/linux/releases/7/Fedora/i386/os/Fedora/fedora-release-7-3.noarch.rpm wget ftp://download.fedora.redhat.com/pub/fedora/linux/releases/7/Fedora/i386/os/Fedora/fedora-release-notes-7.0.0-1.noarch.rpm rpm -U fedora-release-7-3.noarch.rpm fedora-release-notes-7.0.0-1.noarch.rpm yum update

In theory that’s the only thing you need to do, in practice it’s not that easy. I had some dependency issues with pidgin and liferea so I just removed them:

rpm -e pidgin liferea

Then yum complained about libexif being “not signed”. I tried to edit /etc/yum.conf and setting gpgcheck=0, that didn’t work, yum was still checking it. So I had to manually set gpgcheck=0 in all the repos on /etc/yum.repos.d/. Then it worked.

That’s it!

Adding a fast random row to a table in postgresql

2007-06-27T18:37:00.000-07:00

ALTER TABLE posts ADD myrand DOUBLE PRECISION;UPDATE posts SET myrand = RANDOM(); CREATE INDEX myrand_posts ON posts(myrand,id); ANALYZE VERBOSE posts;
ALTER TABLE posts ALTER myrand
SET DEFAULT RANDOM();
ALTER TABLE posts ALTER myrand
SET NOT NULL;
--SELECT * FROM posts WHERE myrand >= (SELECT RANDOM() OFFSET 0) ORDER BY myrand ASC LIMIT 1;

Boolean column in a table in postgres

2007-06-26T11:45:00.000-07:00

CREATE INDEX polls_active_idx ON polls(done) WHERE done='t';

how to get auto align css centering ala mybloglog and facebook

2007-06-18T18:11:00.000-07:00

body { width:99%;max-width:1024px; margin:auto;text-align:center; }
#wrap { margin: auto; width:85%; text-align:left; }
#main { float:left; width:74%; margin-top:1%; }
#sidebar { float:right;max-width: 25%;width:25%;min-width: 5%; padding:3px; }
#footer { background:#cc9; clear:both; }

tech tools

2007-05-10T19:32:00.001-07:00

Python

postgres

Amazon S3

webpy.org

Beautiful soup

lighttpd or nginx

vim and emacs

Determining who's blocking who in Postgres

2007-04-09T22:51:00.000-07:00

Determining who's blocking who in Postgres
If you have databases like I do with lots of concurrent queries, you can sometime run into situations where you issue a query and it just hangs there blocked. Or, more likely somebody or something issues a query and then comes calling when it doesn't seem to be doing anything.

Of course, you have the handy pg_stat_activity and pg_locks views at your disposal, but when it comes to determining exactly which queries are blocking which others and on what table, querying those alone is a tedious way to get the answer. What you really need is a query that sums it all up, in one neat and tidy bundle. Well, my friends here is such a query:

SELECT
bl.procpid as blocked_pid,
bl.usename as user,
bl.current_query as blocked_query,
bl.query_start,
relname as blocked_on ,
lq.procpid as blocking_pid,
lq.usename as user,
lq.current_query as blocking_query,
lq.query_start,
pgl2.mode as lock_type
FROM pg_stat_activity bl, pg_locks pgl1,
pg_stat_activity lq, pg_locks pgl2, pg_class
WHERE bl.procpid = pgl1.pid
AND not pgl1.granted
AND pg_class.oid = pgl1.relation
AND pgl2.relation = pgl1.relation
AND pgl2.granted
AND lq.procpid = pgl2.pid;

In extended mode (\x) psql returns something along these lines for this query:

blocked_pid | 21418
user | sueuser
blocked_query | insert values ('foo', 'bar', 'baz')
into extremely_large_table;
query_start | 2007-02-13 15:14:06.77606-08
blocked_on | extremely_large_table
blocking_pid | 21417
user | joeuser
blocking_query | delete from extremely_large_table;
query_start | 2007-02-13 14:45:34.637675-08
lock_type | AccessExclusiveLock

python spell checker

2007-04-09T22:50:00.001-07:00

How to Write a Spelling Corrector
In the past week, two friends (Dean and Bill) independently told me they were amazed at how Google does spelling correction so well and quickly. Type in a search like [speling] and Google comes back in 0.1 seconds or so with Did you mean: spelling. What surprised me is that I thought Dean and Bill, being highly accomplished engineers and mathematicians, would have good intuitions about statistical language processing problems such as spelling correction. But they didn't, and come to think of it, there's no reason they should. I figured they and many others could benefit from an explanation, and so on the plane back from my trip I wrote a toy spelling corrector, which I now share.

Let's get right to it. I figured that in less than a plane flight, and in less than a page of code, I could write a spelling corrector that achieves 80 or 90% accuracy at a rate of at least 10 words per second. And in fact, here, in 20 lines of Python 2.5 code, is the complete spelling corrector:

import re, string, collections

def words(text): return re.findall('[a-z]+', text.lower())

def train(features):
model = collections.defaultdict(lambda: 1)
for f in features:
model[f] += 1
return model

NWORDS = train(words(file('Documents/holmes.txt').read()))

def edits1(word):
n = len(word)
return set([word[0:i]+word[i+1:] for i in range(n)] + ## deletion
[word[0:i]+word[i+1]+word[i]+word[i+2:] for i in range(n-1)] + ## transposition
[word[0:i]+c+word[i+1:] for i in range(n) for c in string.lowercase] + ## alteration
[word[0:i]+c+word[i:] for i in range(n+1) for c in string.lowercase]) ## insertion

def known_edits2(word):
return set(e2 for e1 in edits1(word) for e2 in edits1(e1) if e2 in NWORDS)

def known(words): return set(w for w in words if w in NWORDS)

def correct(word):
return max(known([word]) or known(edits1(word)) or known_edits2(word) or [word],
key=lambda w: NWORDS[w])

This defines the function correct, which takes a word as input and returns a likely correction of that word. For example:

>>> correct('speling')
'spelling'
>>> correct('korrecter')
'corrector'

mencoder convert to flash

2007-03-20T19:04:00.001-07:00

mencoder pasta_maken_311205.avi -o video.flv -of lavf -ovc lavc -oac lavc -lavcopts vcodec=flv:vbitrate=500:autoaspect:mbd=2:mv0:trell:v4mv:cbp:last_pred=3:predia=2:dia=2:precmp=2:cmp=2:subcmp=2:preme=2:turbo:acodec=mp3:abitrate=56 -vf scale=320:240 -srate 22050 -af lavcresample=22050

thread control clean

2007-03-19T16:49:00.000-07:00

An example of an idiom for controling threads

Doug Fort
http://www.dougfort.net
"""

import threading

class TestThread(threading.Thread):
"""
A sample thread class
"""

def __init__(self):
"""
Constructor, setting initial variables
"""
self._stopevent = threading.Event()
self._sleepperiod = 1.0

threading.Thread.__init__(self, name="TestThread")

def run(self):
"""
overload of threading.thread.run()
main control loop
"""
print "%s starts" % (self.getName(),)

count = 0
while not self._stopevent.isSet():
count += 1
print "loop %d" % (count,)
self._stopevent.wait(self._sleepperiod)

print "%s ends" % (self.getName(),)

def join(self,timeout=None):
"""
Stop the thread
"""
self._stopevent.set()
threading.Thread.join(self, timeout)

if __name__ == "__main__":
testthread = TestThread()
testthread.start()

import time
time.sleep(10.0)

testthread.join()

OpenPoker how it works

2007-03-10T10:57:00.000-08:00

Writing low-pain massively scalable multiplayer servers

Another oldie. First penned for DevMaster over a year ago. Original article comes with a heated forum discussion!

I have since exited the poker business and removed related articles and links from this site. The Erlang source code is still available, though. I’ll dissect it in detail in a series of articles over the next few weeks.

Let me know if you have any trouble viewing this article. Use resizing page controls above to expand the page if you need to.

Introduction

This article describes an alternative approach to building massively scalable online multiplayer systems using my OpenPoker project as an example. OpenPoker is a massively multiplayer poker server with fault-tolerance, load balancing and unlimited scalability built-in. The source code to OpenPoker is available from my site under the GPL and comes in under 10,000 lines of code of which about 1/3 are dedicated to testing.

I prototyped extensively before coming up with the final version of OpenPoker and tried Delphi, Python, C#, C/C++ and Scheme. I also wrote a full-blown poker engine in Common Lisp. While I did spend over 9 months on research and prototyping, the final rewrite only took about 6 weeks of coding. I attribute most of the time and cost savings to choosing Erlang as my platform.

By comparison, it took a team of 4-5 people about 9 months to build the old OpenPoker. The original team also built a Windows poker client but even if I cut development time in half to account for this 1.5 month, it is far from 18 months that I will end up with. In today's world of bloated game development budgets such savings are nothing to sneeze at!

What is Erlang

I suggest you browse through the Erlang FAQ before continuing but I'll give you a quick summary here…

Erlang is a functional, dynamically typed language with built-in support for concurrency. It was specifically designed by Ericsson for telecommunications applications such as controlling a switch or converting protocols, and thus is particularly suitable for building distributed, soft real-time concurrent systems.

Applications written in Erlang are often composed of hundreds or thousands of lightweight processes communicating via message passing. Context switching between Erlang processes is typically one or two orders of magnitude cheaper than switching between threads in a C program.

It's easy to write distributed applications in Erlang because its distribution mechanisms are transparent: programs need not be aware that they are distributed.

The Erlang runtime environment is a virtual machine (VM), much like the Java virtual machine. This means that code compiled on one architecture runs anywhere. The runtime system also allows code in a running system to be updated without interrupting the program and the byte code can be compiled to native code when you need that extra boost.

Please head to the Erlang site and check out the excellent resources in the Getting started, Documentation and Examples sections.

Why Erlang

The concurrency model built into Erlang makes it particularly suitable for writing online multiplayer servers.

A massively scalable multiplayer backend in Erlang is built as a "cluster" with different "nodes" dedicated to different tasks. An Erlang node is an instance of the Erlang VM and you can run multiple Erlang nodes/VMs on your desktop, laptop or server. One node per CPU is recommended.

Erlang nodes track all other nodes connected to them. All you need to do to add a new node to the cluster is point it to an existing node. As soon as the two nodes establish contact all other nodes in the cluster become aware of the new node.

Erlang processes send messages to other processes using a process id which encodes information about the node where the process is running. Processes need not be aware of where other processes are located to communicate with them. A bunch of Erlang nodes linked together can be viewed as a grid or supercomputing facility.

Players, NPCs and other entities in massively multiplayer games are best modelled as concurrently running processes but concurrency is notoriously hard to work with. Erlang makes concurrency easy.

Erlang's bit syntax∞ makes it trivial to work with binary data and bests the structure packing/unpacking facilities of Perl and Python. This makes Erlang particularly suitable for handling binary network protocols.

The OpenPoker architecture

Everything in OpenPoker is a process. Players, bots, games, pots, etc. are all processes. For every poker client connected to OpenPoker there's a player "proxy" handling network messages. Depending on whether the player is logged in, some messages are ignored while others are passed to the process handling card game logic.

The card game process is an uber-state machine composed of state machine modules for every stage of the game. This lets me treat card game logic as a Lego constructor and add new card games by putting together the state machine building blocks. Take a look at the start function in cardgame.erl if you want to learn more about my approach.

The card game state machine lets different messages through depending on the game stage. It also uses a separate game process to handle the machinery common to all games such as keeping track of players, pots, limits and so on. When simulating 27,000 poker games on my laptop I found that I had about 136,000 players and close to 800,000 processes in total.

That said, I would like to focus on how Erlang makes it simple to implement scalability, fault tolerance and load balancing using OpenPoker as an example. My approach is not particular to poker or card games. The same approach can be used to quickly put together massively scalable multiplayer backends, do it cheaply and with a minimum amount of pain.

Scalability

I implement scalability and load-balancing by means of a multi-tier architecture. The first tier is represented by gateway nodes. Game server nodes form tier two and Mnesia "master" nodes can be thought of as the third tier.

Mnesia is the Erlang real-time distributed database. The Mnesia FAQ has a good explanation but Mnesia is basically a fast, replicating, in-memory database. There are no objects in Erlang but Mnesia can be thought of as object-oriented as it can store any Erlang data.

There are two types of Mnesia nodes: those that write to disk and those that do not. Regardless of this, all Mnesia nodes keep their data in memory. Mnesia master nodes in OpenPoker are nodes that write to disk. Gateways and game servers pick up their database from Mnesia masters upon startup and are memory-only nodes.

There's a handy set of command-line arguments that you can give to the Erlang VM and interpreter when starting up to tell Mnesia where the master database is located. After the new local Mnesia node establishes contact with the master Mnesia node, the new node becomes part of the master node’s cluster.

Assuming that the master nodes are located on hosts apple and orange, adding a new gateway, game server, etc. node to your OpenPoker cluster is as simple as

erl -mnesia extra_db_nodes \['db@apple','db@orange'\] -s mnesia start

where

-s mnesia start

is equivalent to starting Mnesia from the erlang shell like this

erl -mnesia extra_db_nodes \['db@apple','db@orange'\]
Erlang (BEAM) emulator version 5.4.8 [source] [hipe] [threads:0]

Eshell V5.4.8  (abort with ^G)
1> mnesia:start().
ok

OpenPoker keeps configuration information in Mnesia tables and this information is automatically downloaded by new nodes as soon as Mnesia starts. Zero configuration required!

Fault tolerance

OpenPoker lets me grow as high as I want by adding cheap Linux boxes to my server farm. Put together a couple of racks of 1U servers and you can easily handle 500,000 or even 1,000,000 players online. This would work just as well for a MMORPG as for poker.

I can dedicate some boxes to run gateway nodes and some to be database masters that write database transactions to disk. I can dedicate the rest of my boxes to run my game servers. I can limit game servers to accept a maximum of, say, 5000 simultaneous players so that no more than 5000 players are affected when my game server box crashes.

It's important to note that no information is lost when a game server crashes since all the Mnesia database transactions are replicated in real-time to all other nodes running Mnesia, game server nodes included.

In case of errors some assistance from the game client is required for the player to smoothly reconnect to the OpenPoker cluster. As soon as the poker client notices a network error it should connect to the gateway, receive a new game server address in a hand-off packet and reconnect to the new game server. What happens then is a little tricky as different types of reconnect scenarios need to be handled.

OpenPoker will handle the following reconnect scenarios:

The game server crashed
The client crashed or timed out due to a network error
The player is online on a different connection
The player is online on a different connection and is in a game

The most common scenario will probably be a poker client that disconnected due to a network error. A less likely but still possible scenario is a client reconnecting from one computer while already playing at another.

Each OpenPoker game buffers packets sent to players and every reconnecting poker client will first receive all the game packets since the game started before starting to receiving packets as usual. OpenPoker uses TCP connections so I don't need to worry about packet ordering – packets will simply arrive in proper order.

Every poker client connection is represented by two OpenPoker processes: the socket process and the actual player process. A visitor process with restricted functionality is used until the player logs in. Visitors cannot join games, for example. The socket process will be dead after a poker client disconnects while the player process will still be alive.

A player process can notice a dead socket when attempting to forward a game packet and should put itself into auto-play mode or fold the hand. The login code will check for the combination of a dead socket and live player process when reconnecting. The code to determine the condition looks like this:

login({atomic, [Player]}, [_Nick, Pass|_] = Args)
 when is_record(Player, player) ->
   Player1 = Player#player {
       socket = fix_pid(Player#player.socket),
       pid = fix_pid(Player#player.pid)
          },
   Condition = check_player(Player1, [Pass],
                [
                 fun is_account_disabled/2,
                 fun is_bad_password/2,
                 fun is_player_busy/2,
                 fun is_player_online/2,
                 fun is_client_down/2,
                 fun is_offline/2
                ]),
   ...

whereas the conditions themselves will be determined by the following code:

is_player_busy(Player, _) ->
   {Online, _} = is_player_online(Player, []),
   Playing = Player#player.game /= none,
   {Online and Playing, player_busy}.

is_player_online(Player, _) ->
   SocketAlive = Player#player.socket /= none,
   PlayerAlive = Player#player.pid /= none,
   {SocketAlive and PlayerAlive, player_online}.

is_client_down(Player, _) ->
   SocketDown = Player#player.socket == none,
   PlayerAlive = Player#player.pid /= none,
   {SocketDown and PlayerAlive, client_down}.

is_offline(Player, _) ->
   SocketDown = Player#player.socket == none,
   PlayerDown = Player#player.pid == none,
   {SocketDown and PlayerDown, player_offline}.

Notice that the first thing the login function does is to fix up dead process ids. This makes processing simple down the road and is accomplished with the following bits of code:

fix_pid(Pid)
 when is_pid(Pid) ->
   case util:is_process_alive(Pid) of
   true ->
       Pid;
   _ ->
       none
   end;

fix_pid(Pid) ->
   Pid.

and

-module(util).

-export([isprocessalive/1]).


isprocessalive(Pid)
 when is_pid(Pid) ->
   rpc:call(node(Pid), erlang, isprocessalive, [Pid]).

A process id in Erlang includes the id of the node where the process is running. is_pid(Pid) tells me if its argument is a process id (pid) but cannot tell me if the process is alive or dead. Erlang’s built-in erlang:is_process_alive(Pid) tells me whether a local process (running on the same node) is dead or alive. There's no variant of is_process_alive for checking remote nodes.

Fortunately, I can use the Erlang rpc facility together with node(pid) to call is_process_alive() on the remote node. In fact, this will work just as well on the local node so the code above functions as a universal distributed process checker.

All that is left to do is to act on the various login conditions. In the simplest case where the player is offline I start a player process, connect the player to the socket and update the player record.

login(Player, player_offline, [Nick, _, Socket]) ->
   {ok, Pid} = player:start(Nick),
   OID = gen_server:call(Pid, 'ID'),
   gen_server:cast(Pid, {'SOCKET', Socket}),
   Player1 = Player#player {
       oid = OID,
       pid = Pid,
       socket = Socket
          },
   {Player1, {ok, Pid}}.

Should the player login information not match I can return an error and increase the number of bad login attempts. If this number exceeds a predefined maximum I disable the account like this:

login(Player, bad_password, _) ->
   N = Player#player.login_errors + 1,
   {atomic, MaxLoginErrors} =
   db:get(clusterconfig, 0, maxlogin_errors),
   if
   N > MaxLoginErrors ->
       Player1 = Player#player {
           disabled = true
              },
       {Player1, {error, ?ERRACCOUNTDISABLED}};
   true ->
       Player1 = Player#player {
           login_errors = N
              },
       {Player1, {error, ?ERRBADLOGIN}}
   end;

login(Player, account_disabled, _) ->
   {Player, {error, ?ERRACCOUNTDISABLED}};

Logging out the player involves finding the player process id using their Object ID (which is just a number), stopping the player process and updating the player record in the database. This is accomplished by the following bit of code:

logout(OID) ->
   case db:find(player, OID) of
   {atomic, [Player]} ->
       player:stop(Player#player.pid),
       {atomic, ok} = db:set(player, OID,
                 [{pid, none},
                  {socket, none}]);
   _ ->
       oops
   end.

With logout out of the way I can address the various reconnect conditions. If the player is online but idle, i.e. hanging out in the lobby or watching a game (drinking a Bud? Wazzup!) and is reconnecting from a different computer, I can just log them out and log them back in as if they were offline:

login(Player, player_online, Args) ->
   logout(Player#player.oid),
   login(Player, player_offline, Args);

If the player was idle when their poker client disconnected then all I need to do is replace the socket process id in the player record and tell the player process about the new socket.

login(Player, clientdown, [, _, Socket]) ->
   gen_server:cast(Player#player.pid, {'SOCKET', Socket}),
   Player1 = Player#player {
       socket = Socket
          },
   {Player1, {ok, Player#player.pid}};

If the player was in a game then we run the code above and tell the game to resend the event history.

login(Player, player_busy, Args) ->
   Temp = login(Player, client_down, Args),
   cardgame:cast(Player#player.game,
         {'RESEND UPDATES', Player#player.pid}),
   Temp;

Overall, a combination of a real-time replicating database, a poker client that knows to reconnect to a different game server and some crafty login code allows me to provide a high degree of fault tolerance transparently to the player.

Load balancing

I can build my OpenPoker cluster from as many game server nodes as I want . I might want to allocate, say, 5000 players per game server and spread the load among the active game servers in my cluster. I can add new game servers at any time and they will automatically make themselves available to accept new players.

Gateway nodes spread the player load among the active game servers in the OpenPoker cluster. The job of a gateway node is to pick a random game server, ask it for the number of players connected and its address, host and port number where the game server is running. As soon as the gateway finds a game server where the number of players connected is less than the preset maximum it will return the address of that game server to the connected poker client and close the connection.

There's absolutely no load on gateway nodes and connections to them are extremely short-lived. You can have any cheap box acting as your gateway node.

Nodes should generally come at least in pairs so that if one node fails another one can take over. You would need a mechanism like Round-robin DNS to employ more than a single gateway node.

How do gateways learn about game servers?

OpenPoker uses the Erlang Distributed Named Process Groups facility to group game servers. The group is globally visible on all nodes, this happens automatically. New game servers join the game server group and when a game server node goes down it's automatically deleted.

This is what the code to find a game server with a maximum capacity of MaxPlayers looks like:

find_server(MaxPlayers) ->
   case pg2:getclosestpid(?GAME_SERVERS) of
   Pid when is_pid(Pid) ->
       {Time, {Host, Port}} = timer:tc(gen_server, call, [Pid, 'WHERE']),
       Count = gen_server:call(Pid, 'USER COUNT'),
       if
       Count <>
           io:format("~s:~w: ~w players~n", [Host, Port, Count]),
           {Host, Port};
       true ->
           io:format("~s:~w is full...~n", [Host, Port]),
           find_server(MaxPlayers)
       end;
   Any ->
       Any
   end.

pg2:get_closest_pid() returns a random game server process id since a gateway node is not expected to run any game servers. If a process id of the game server is returned I ask the game server for its address (host and port) as well the number of players connected. So long as the number of players connected is less than the maximum I return the game server address to the caller, otherwise I keep looking.

Multiple-outlet powerstrip middleware

OpenPoker is open source software and I have been pitching it to various poker vendors recently. All the vendors have the same problem with scalability and fault tolerance, even after several years of development. Some have recently finished major rewrites of their server software while others are just embarking on this journey. All of the vendors are heavily invested in their Java infrastructure and, understandably, do not want to switch to Erlang.

Still, it sounds to me like there is a need to be filled. The more I think about it the more it looks like Erlang can still be used to provide a cost-efficient solution while keeping things simple and straightforward. I see this solution as a multiple-outlet electrical power strip, just like the one you are probably using right now.

You write your game server as a simple socket-based server that uses a database backend. In fact, more likely than not this is how your game server is written now. Your game server is the standard electrical plug and multiple instances of your game server are plugged into my power outlets while players flow in through the other end.

You supply the game servers and I provide you with scalability, load balancing, and fault tolerance. I keep players connected to the power strip and monitor your game servers, restarting them as needed. I switch your players to another game server when one goes down and you can plug in as many game servers as you like into my power outlets.

The power strip middleware is a black box sitting between your players and your servers and likely won't even require any changes to your code. You will get all the benefits of a highly scalable, load-balanced, fault-tolerant solution while keeping your investment and modifying little of your existing infrastructure.

You can write this middleware in Erlang today, run it on a Linux box with a kernel specially tuned for a high number of TCP connections and put this box in a demilitarized zone while keeping your game servers behind a firewall. Even if you don't, I suggest that you take a close look at Erlang today and think about using it to simplify your massively multiplayer server architectures. And I will be here to help!

10 useful bash utils

2007-03-04T08:59:00.001-08:00

Bash Cures Cancer
Learn the UNIX/Linux command line
Home Search Man Pages RSS Feed Email:

Title: 10 Linux commands you've never used
Published: 02-19-2007

It takes years maybe decades to master the commands available to you at the Linux shell prompt. Here are 10 that you will have never heard of or used. They are in no particular order. My favorite is mkfifo.

pgrep, instead of:

# ps -ef | egrep '^root ' | awk '{print $2}'
1
2
3
4
5
20
21
38
39
...

You can do this:

# pgrep -u root
1
2
3
4
5
20
21
38
39
...

pstree, list the processes in a tree format. This can be VERY useful when working with WebSphere or other heavy duty applications.

# pstree
init-+-acpid
   |-atd
   |-crond
   |-cups-config-dae
   |-cupsd
   |-dbus-daemon-1
   |-dhclient
   |-events/0-+-aio/0
   |          |-kacpid
   |          |-kauditd
   |          |-kblockd/0
   |          |-khelper
   |          |-kmirrord
   |          `-2*[pdflush]
   |-gpm
   |-hald
   |-khubd
   |-2*[kjournald]
   |-klogd
   |-kseriod
   |-ksoftirqd/0
   |-kswapd0
   |-login---bash
   |-5*[mingetty]
   |-portmap
   |-rpc.idmapd
   |-rpc.statd
   |-2*[sendmail]
   |-smartd
   |-sshd---sshd---bash---pstree
   |-syslogd
   |-udevd
   |-vsftpd
   |-xfs
   `-xinetd

bc is an arbitrary precision calculator language. Which is great. I found it useful in that it can perform square root operations in shell scripts. expr does not support square roots.

# ./sqrt
Usage: sqrt number
# ./sqrt 64
8
# ./sqrt 132112
363
# ./sqrt 1321121321
36347

Here is the script:

# cat sqrt
#!/bin/bash
if [ $# -ne 1 ]
then
      echo 'Usage: sqrt number'
      exit 1
else
      echo -e "sqrt($1)\nquit\n" | bc -q -i
fi

split, have a large file that you need to split into smaller chucks? A mysqldump maybe? split is your command. Below I split a 250MB file into 2 megabyte chunks all starting with the prefix LF_.

# ls -lh largefile
-rw-r--r--  1 root root 251M Feb 19 10:27 largefile
# split -b 2m largefile LF_
# ls -lh LF_* | head -n 5
-rw-r--r--  1 root root 2.0M Feb 19 10:29 LF_aa
-rw-r--r--  1 root root 2.0M Feb 19 10:29 LF_ab
-rw-r--r--  1 root root 2.0M Feb 19 10:29 LF_ac
-rw-r--r--  1 root root 2.0M Feb 19 10:29 LF_ad
-rw-r--r--  1 root root 2.0M Feb 19 10:29 LF_ae
# ls -lh LF_* | wc -l
126

nl numbers lines. I had a script doing this for me for years until I found out about nl.

# head wireless.h
/*
* This file define a set of standard wireless extensions
*
* Version :    20      17.2.06
*
* Authors :    Jean Tourrilhes - HPL
* Copyright (c) 1997-2006 Jean Tourrilhes, All Rights Reserved.
*/

#ifndef _LINUX_WIRELESS_H
# nl wireless.h | head
   1  /*
   2   * This file define a set of standard wireless extensions
   3   *
   4   * Version :    20      17.2.06
   5   *
   6   * Authors :    Jean Tourrilhes - HPL
   7   * Copyright (c) 1997-2006 Jean Tourrilhes, All Rights Reserved.
   8   */

   9  #ifndef _LINUX_WIRELESS_H

mkfifo is the coolest one. Sure you know how to create a pipeline piping the output of grep to less or maybe even perl. But do you know how to make two commands communicate through a named pipe?

First let me create the pipe and start writing to it:
pipe" title="Making the pipe and writing to it" class="screenshot" src="http://bashcurescancer.com/media/10-linux-commands-youve-never-used/mkfifo-write-to-pipe.png" height="357" width="640">
Then read from it:

ldd, want to know which Linux thread library java is linked to?

# ldd /usr/java/jre1.5.0_11/bin/java
      libpthread.so.0 => /lib/tls/libpthread.so.0 (0x00bd4000)
      libdl.so.2 => /lib/libdl.so.2 (0x00b87000)
      libc.so.6 => /lib/tls/libc.so.6 (0x00a5a000)
      /lib/ld-linux.so.2 (0x00a3c000)

col, want to save man pages as plain text?
```
# PAGER=cat
# man less | col -b > less.txt
```

xmlwf, need to know if a XML document is well formed? (A configuration file maybe..)

# curl -s 'http://bashcurescancer.com' > bcc.html
# xmlwf bcc.html
# perl -i -pe 's@
@
@g' bcc.html
# xmlwf bcc.html
bcc.html:104:2: mismatched tag

lsof lists open files. You can do all kinds of cool things with this. Like find which ports are open:

# lsof | grep TCP
portmap    2587   rpc    4u     IPv4       5544                 TCP *:sunrpc (LISTEN)
rpc.statd  2606  root    6u     IPv4       5585                 TCP *:668 (LISTEN)
sshd       2788  root    3u     IPv6       5991                 TCP *:ssh (LISTEN)
sendmail   2843  root    4u     IPv4       6160                 TCP badhd:smtp (LISTEN)
vsftpd     9337  root    3u     IPv4      34949                 TCP *:ftp (LISTEN)
cupsd     16459  root    0u     IPv4      41061                 TCP badhd:ipp (LISTEN)
sshd      16892  root    3u     IPv6      61003                 TCP badhd.mshome.net:ssh->kontiki.mshome.net:4661 (ESTABLISHED)

Note: OpenBSD 101 pointed out that "lsof -i TCP" a better way to obtain this same information. Thanks!

Or find the number of open files a user has. Very important for running big applications like Oracle, DB2, or WebSphere:

# lsof | grep ' root ' | awk '{print $NF}' | sort | uniq | wc -l
179

Note: an anonymous commenter pointed out that you can replace sort | uniq with "sort -u". This is true, I forgot about the -u flag. Thanks!

SQL queries from javascript

2007-03-01T17:25:00.001-08:00

http://trimpath.com/project/wiki/TrimQuery
that is really cool..
if this is combined with berkeley db.. it ll be awesome

How to do form.fill

2007-02-26T19:14:00.000-08:00

suppose you have a form like:

form = web.form.Form(
web.form.Textbox('name', description='Name'),
web.form.Textbox('subject', description='Subject'),
web.form.Textarea('body', description='Message'),
)

You can pass a dictionary mapping values to form objects like:

form.fill(
{
'subject':'This is my subject!',
'body':t'This is the body!',
}
)

and these values will fill your textboxes.

X-Sendfile for large file transfers

2007-02-20T01:50:00.001-08:00

I read Lighttpd’s weblog (Lighty’s Life) regularly and I remember Jan talking about X-Sendfile. I thought it was interesting, but never really thought about using it…. Until today!

Basically, if you have a Ruby on Rails (or other environment) page that transfers a really massive file to the client, you should use X-Sendfile.

Here is what you’ll need:

*Lighttpd Configuration*

To the FastCGI section of your lighty config, along with host, port, etc., add “allow-x-send-file” => “enable”


fastcgi.server = ( ".fcgi" =>
 (  "server_1"  => ( "host" => "10.0.1.1", "port" => 8000, "allow-x-send-file" => "enable" )
                        )

Find shortest distance path postgis postgres linedata

2007-02-19T19:47:00.000-08:00

You could consider using pgRouting (http://www.postlbs.org/) .

This extension to Postgres/PostGIS needs some topology and topology support for PostGIS is still in its infancy and barely documented… L

Now to implement ‘topology’ the *easy* way… ;-)

Use OpenJump! (http://openjump.org)

There’s a tool there called ‘Planar Graph’…

You can use it to get every line segment documented (start and end nodes for each line).

Add a ‘length’ field to your dataset.

OpenJump also has a tool to calculate areas and lengths… ;-)

Save your dataset from OpenJump into PostGIS.

The ‘length’ field acts as an initial cost for turning….

Then use the shortest_path() function from pgRouting and you’re on your way!

Also heard from the developers of pgRouting that support for turn restrictions is somewhere in the near future… ;-)

(OJ Peopleà I’m posting this to the list as I think it’s useful ;-) )

HTH,

Pedro Doria Meunier

Making all columns in a table lower case

2007-02-18T19:21:00.000-08:00

psql -c "\d tablename" > 1.txt
get the table structure
cat 1.txt | awk '{print $1}' > 2.txt
get the 1st word (column name)
for x in `cat 2.txt`; do
echo "alter table tablename rename \"$x\" to $x;" >> 3.txt
done
build a file of sql commands to run
then, paste these into psql

import zcta zip data to postgres postgis

2007-02-18T09:59:00.000-08:00

Often you will receive data in a non-spatial form such as comma delimited data with latitude and longitude fields. To take full advantage of PostGIS spatial abilities, you will want to create geometry fields in your new table and update that field using the longitude latitude fields you have available.

General Note: All the command statements that follow should be run from the PgAdminIII Tools - Query Tool or any other PostGreSQL Administrative tool you have available. If you are a command line freak - you can use the psql command line tool packaged with PostGreSQL.

Getting the data

For this exercise, we will use US zip code tabulation areas instead of just Boston data. The techniques here will apply to any data you get actually.

First step is to download the data from US Census. http://www.census.gov/geo/www/gazetteer/places2k.html

Importing the Data into PostGreSQL

PostGreSQL comes with a COPY function that allows you to import data from a delimited text file. Since the ZCTAs data is provided in fixed-width format, we can't import it easily without first converting it to a delimited such as the default tab-delimited format that COPY works with. Similarly for data in other formats such as DBF, you'll either want to convert it to delimited using tools such as excel, use a third party tool that will import from one format to another, or one of my favorite tools Microsoft Access that allows you to link any tables or do a straight import and export to any ODBC compliant database such as PostGreSQL.

Create the table to import to

First you will need to create the table in Postgres. You want to make sure the order of the fields is in the same order as the data you are importing.


CREATE TABLE zctas
(
 state char(2),
 zcta char(5),
 junk varchar(100),
 population_tot int8,
 housing_tot int8,
 water_area_meter float8,
 land_area_meter float8,
 water_area_mile float8,
 land_area_mile float8,
 latitude float8,
 longitude float8
)
WITHOUT OIDS;

Convert from Fixed-width to Tab-Delimited

For this part of the exercise, I'm going to use Microsoft Excel because it has a nice wizard for dealing with fixed-width and a lot of windows users have it already. If you open the zcta file in Excel, it should launch the Text Import Wizard. MS Access has a similarly nice wizard and can deal with files larger than excels 65000 some odd limitation. Note there are trillions of ways to do this step so I'm not going to bother going over the other ways. For non-MS Office users other office suites such as Open-Office probably have similar functionality.

Open the file in Excel.
Import Text Wizard should launch automatically and have Fixed-Width as an option
Look at the ZCTA table layout spec http://www.census.gov/geo/www/gazetteer/places2k.html#zcta and set your breakouts the same as specified. For the above I broke out the Name field further into first 5 for zcta and the rest for a junk field.
Next File->Save As ->Text (Tab delimited)(*.txt) -give it name of zcta5.tab
Copy the file to somewhere on your PostGreSQL server.

The COPY command

Now copy the data into the table using the COPY command. Note the Copy command works using the PostGreSQL service so the file location must be specified relative to the Server.


    COPY zctas FROM 'C:/Downloads/GISData/zcta5.tab';

Creating and Populating the Geometry Field

Create the Geometry Field

To create the Geometry field, use the AddGeometryColumn opengis function. This will add a geometry field to the specified table as well as adding a record to the geometry_columns meta table and creating useful constraints on the new field. A summary of the function can be found here http://postgis.refractions.net/docs/ch06.html#id2526109.

SELECT AddGeometryColumn( 'public', 'zctas', 'thepoint_lonlat', 4269, 'POINT', 2 );

The above code will create a geometry column named thepoint_longlat in the table zctas that validates to make sure the inputs are 2-dimensional points in SRID 4269 (NAD83 longlat).

Populate the Geometry Field using the Longitude and Latitude fields


UPDATE zctas
       SET thepoint_lonlat = PointFromText('POINT(' || longitude || ' ' || latitude || ')',4269)

The above code will generate a Text representation of a point and convert this representation to a PostGis geometry object of spatial reference SRID 4269.

There are a couple of things I would like to point out that may not be apparently clear to people not familiar with PostGreSQL or PostGis

|| is a string concatenator. It is actually the ANSI-standard way of concatenating strings together. In MySQL you would do this using the CONCAT function and in Microsoft SQL Server you would use +. Oracle also uses ||. So what the inner part of the code would do is to generate something that looks like POINT(-97.014256 38.959448).
You can't just put any arbitrary SRID in there and expect the system to magically transform to that. The SRID you specify has to be the reference system that your text representation is in.

Transforming to Another spatial reference system

The above is great if you want your geometry in longlat spatial reference system. In many cases, longlat is not terribly useful. For example if you want to do distance queries with your data, you don't want your distance returned back in longlat. You want it in a metric that you normally measure things in.

In the code below, we will create a new geometry field that holds points in the WGS 84 North Meter reference system and then updates that field accordingly.


SELECT AddGeometryColumn( 'public', 'zctas', 'thepoint_meter', 32661, 'POINT', 2 );

UPDATE zctas
SET thepoint_meter = transform(PointFromText('POINT(' || longitude || ' ' || latitude || ')',4269),32661) ;

Index your spatial fields

One of the number one reasons for poor query performance is lack of attention to indexes. Putting in an index can make as much as a 100 fold difference in query speed depending on how many records you have in the table. For large updates and imports, you should put your indexes in after the load, because while indexes help query speed, updates against indexed fields can be very slow because they need to create index records for the updated/inserted data. In the below, we will be putting in GIST indexes against our spatial fields.


CREATE INDEX idx_zctas_thepoint_lonlat ON zctas
 USING GIST (thepoint_lonlat);

CREATE INDEX idx_zctas_thepoint_meter ON zctas
 USING GIST (thepoint_meter);

ALTER TABLE zctas ALTER COLUMN thepoint_meter SET NOT NULL;
CLUSTER idx_zctas_thepoint_meter ON zctas;

VACUUM ANALYZE zctas;

In the above after we create the indexes, we put in a constraint to not allow nulls in the thepoint_meter field. The not null constraint is required for clustering since as of now, clustering is not allowed on gist indexes that have null values. Next we cluster on this index. Clustering basically physically reorders the table in the order of the index. In general spatial queries are much slower than attribute based queries, so if you do a fair amount of spatial queries, you get a huge gain.

In the above we vacuum analyze the table to insure that index statistics are updated for our table.

urlparse(url, scheme='', allow_fragments=1)

2007-02-16T12:21:00.000-08:00

this is what we should use urlparse(url, scheme='', allow_fragments=1)
Parse a URL into 6 components:
:///;?#
Return a 6-tuple: (scheme, netloc, path, params, query, fragment).
Note that we don't break the components up in smaller bits
(e.g. netloc is a single string) and we don't expand % escapes.

pgsql users, and schemas

2007-02-14T21:09:00.000-08:00

so have you discovered pgsql users, and schemas yet
psql -U postgres
psql> create user newuser password 'newuser';
psql> create schema newuser authorization newuser;
psql> \q
# psql -U newuser
psql> create table foo();
psql> \d
the table is then owned by newuser user, in its own schema (like a namespace). this is somewhat how oracle user/schema owned tables are done.
this lets you have one database and then have many users within that database, each in their own schema.
this is same effect for having a different database for each project i guess.

Adding a new id column Primary key for an existing table

2007-02-14T11:29:00.000-08:00

10k rows. varchar types, no indexes. takes a while it seems.
ok j00 ready?
this is what i did:
alter table localeze_amacai_business add id integer;
create sequence localeze_amacai_business_id_seq;
create or replace function assign_localize_pk ()
returns integer as $_$
DECLARE
_id integer;
_count integer;
_row record;
BEGIN

for _row in select * from localeze_amacai_business LOOP
select into _id nextval('localeze_amacai_business_id_seq');
update localeze_amacai_business
set id = _id
where "PERSISTENTRECORDID" = _row."PERSISTENTRECORDID";
END LOOP;
return _count;
END;
$_$ language plpgsql;
select assign_localize_pk();
drop function assign_localize_pk();
alter table localeze_amacai_business alter id set not null;
alter table localeze_amacai_business add constraint localeze_amacai_business_pk primary key (id);

but how 2 set that sequence to this new table
11:18:30 am
Travis
so now it was built using the sequence
select last_value from localeze_amacai_business_id_seq
coup-# ;
last_value
------------
9258
oh, thats e-z
just:
alter table localeze_amacai_business alter id set default nextval('localeze_amacai_business_id_seq');
so now new inserts will invoke the sequence and you dont have to specify the id value

or you could always do
select into _id nextval('localeze_amacai_business_id_seq');
insert into. ... (id, ...) values (_id, ..)

table
i do not understand
i do not want to specify id values
11:20:33 am
Travis
i mod the existing table , added that "id" column to it
and it defaults to the sequence now
so how do you relate to the table if you dont care what its id is?

autoincrement sequence id

2007-02-14T11:04:00.000-08:00

postgresql now has the insert into .. working
so create table2 table with all the columns that table1 (original has)
hm, actually select into needs the table to not exist
i guess it is possible in postgresql too, wher you can have the original table, and a new table with the id pk column and then make a plpgsql function that
for _row in select * from table LOOP
select into _id nextval('a_sequence');
insert into new_table(id, ....) values (_id, .....);
LOOP

strip_phone_number

2007-02-14T11:02:00.000-08:00

create or replace function strip_phone_number(
_in varchar
) returns varchar as $_$
DECLARE
_len integer;
_i integer;
_chr varchar;
_test varchar;
_result varchar;
BEGIN

select into _len length(_in);

_i := 0;
select into _result '';
while _i <= _len LOOP
_i := _i + 1;
select into _chr substring(_in from _i for 1);
select into _test substring(_chr from '[0-9]$');
--_test := _chr;
if (_test is not null) then
select into _result _result || _test;
end if;
raise debug '%:%:%:%', _i, _chr, _test, _result;
END LOOP;

return _result;
END;

$_$ language plpgsql;

(u will need to fix the tabs thing in msn pastes)
select strip_phone_number('(123) 456-8909 x 1234');
strip_phone_number
--------------------
12345689091234
(1 row)
so that dumps the "not digit" characters from a string in pl/pgsql
but what good does that do?
dont you need the ui to undo that?

Import TIGER database to PostGIS

2007-02-11T11:03:00.000-08:00

wget http://www.gdal.org/dl/fwtools/FWTools-linux-1.2.0.tar.bz2
tar xjf FWTools-linux-1.2.0.tar.bz2
cd FWTools-1.2.0/
./install.sh
export LD_LIBRARY_PATH=/www/ask/work/ogr/FWTools-1.2.0/lib
export GDAL_DATA=/www/ask/work/ogr/FWTools-1.2.0/share
./bin/ogr2ogr -update -append -f "PostGreSQL" PG:"host=localhost user=postgres dbname=mydbname password=password" /www/ask/work/tiger/TGR06001.RT1 layer CompleteChain -nln masuf -a_srs "EPSG:4269"

Installing postgis

2007-02-10T20:28:00.000-08:00

yum install proj proj-devel

#get postgres source
cd contrib
svn co http://svn.refractions.net/postgis/trunk postgis
cd postgis
./autogen.sh
./configure --with-pgsql=/usr/local/pgsql/bin/pg_config
make && sudo make install
/usr/local/pgsql/bin/createlang plpgsql coupon
/usr/local/pgsql/bin/createlang plpgsql coupon -U postgres
/usr/local/pgsql/bin/psql -d coupon -f lwpostgis.sql -U postgres
/usr/local/pgsql/bin/psql -d coupon -f spatial_ref_sys.sql -U postgres

Vi Commands

2007-02-10T01:51:00.000-08:00

Why multithreaded design was avoided

2007-02-10T01:43:00.000-08:00

Multithreaded environments can be a headache. Experienced programmers know that and try to avoid threads, while on the other hand inexperienced programmers find them quite attractive and usually make applications a mess. It all boils down to synchronization. Synchronization of threads can be very hard to get right and is wet ground for a great number bugs to grow. Add to that, that race conditions and thread-related bugs can be extremely hard to hunt down, since the condiitons to reproduce them may be unknown. The efficiency of threads is also a concern. The scripting engine for a game must be fast. The game world contains many actors that need to be updated at least every frame. You don’t want a scheduler to take up half of your CPU trying to decide which - of many, many actors - to run next. Also, if you have to spawn and delete bullet actors in the game (coming from a fast machine gun), you should start looking for thread pools and other techniques since spawning each bullet thread can take too long.

To sum it up: below is the list of reasons that multithreaded environments where overlooked by game developers :

* Scheduling overhead
* Memory cost per thread
* Inefficient thread creation
* Synchronization problems
* More bug prune
* Difficult to debug

The main feature that makes Stackless Tasklets

2007-02-10T01:19:00.000-08:00

The main feature that makes Stackless so attractive for use as a scripting language is the support for tasklets. Tasklets make it possible to create “micro-threads”, allowing the programmer to switch among several execution threads that only exist in the python environment and have no dependencies on the underlying OS threads. Some would call these threads”green-threads”. These threads has very small footprint on memory and CPU. You can actually create hundreds of threads with almost no overhead. Every tasklet has only a few bytes of memory overhead. And the scheduling of threads takes O(1) time with a simple Round-Robin scheduling algorithm. If we where talking about native threads we would have almost 1MB of memory per thread and high cost scheduling to do things we don’t need. To all that add that the engine would behave very differently on different operating systems. Even on different versions of the same operating system.

Coroutines vs generators

2007-02-10T00:44:00.000-08:00

Coroutines have a completely separate stack which is saved when they yield,
so you have a load of nested function calls and yield from deep in the
middle of them.

Generators save only a single stack frame, so all yields must come directly
from the generator, not from functions which it calls.

You can use generators to get a similar effect to coroutines by nesting
generators and propogating the yields back up the chain, but this has to be
done explicitly at every level.

--
Duncan Booth duncan@rcp.co.uk

Posting Flash Videos with FFmpeg and FlowPlayer

2007-02-09T17:14:00.000-08:00

Posting Flash Videos with FFmpeg and FlowPlayer

Anna showing on FlowPlayer Last night I have posted my very first flash video on the web — and it was Anna sitting there watching, her own video for 2 minutes (which probably would only interest the parents and grand-parents). Anna’s video aside, I was also having fun figuring out getting that video online.

There are many ways putting videos online. You can either:

1. Upload your AVI/QuickTime/WMV files onto a folder somewhere inside your hosting account.
2. Use a third party video hosting service like Google Video or YouTube.

Personally I don’t like (2). You need to upload your videos to that 3rd party, and you have little control over how the final outcome will be encoded (bit rate, frame rate, quality, etc). Moreover, there are terms and conditions that you need to read through, let along agreeing to. At the end, who owns the rights to uploaded video?

Being a control freak (well, only over the systems that I need to manage), I have always preferred option (1) by hosting video files inside my own accounts, which has some crazy amount of space and data transfer anyway. Except you don’t get that nice Flash applet which you can embed into your own pages, so visitors can and watch the video without leaving the page. They don’t need to worry about saving onto the desktop, which media player to use, whether codec has been installed, etc. They Just WorksTM — perfect for the grand-parents :)

With a bit of time wasted on research and mocking around, it turns out that you can easily achieve the effect of embedded flash video, and yet host the video files on your own server. And there’s zero penny you need to spend — all can be done via these open source software, FFmpeg and FlowPlayer.
The Basis

Here’s a summary of what needs to be done.

1. Convert the video file into a suitable format for Flash players.
2. Upload the converted file onto hosted account.
3. Upload the Flash player if hasn’t been done.
4. Paste HTML code snippet into the web page.

Flash players can only play video files encoded into the FLV (Flash Video) format, which is also the format used by Google Video and YouTube. To do so the open source way is use the universal encoder, FFmpeg.
FFmpeg

Installing FFmpeg is trivial — at least on my Gentoo boxes :) Make sure appropriate USE flags are used during emerge. For example I have:

USE="aac amr encode ogg vorbis x264 xvid zlib" emerge ffmpeg

Other Linux distribution? Not using Linux? Err. Good luck.

To convert a movie using FFmpeg, do the following:

$ ffmpeg -i movie.avi movie.flv

It will then convert the AVI file into FLV Flash Video. FFmpeg can also handle many different container types, for example QuickTime, WMV1 (not WMV3 at the moment), MPEG4, etc, so just throw the video at it and see whether it handles it.

There are many command line options that you can use to alter the encoding behaviour. For example if I wish to rescale the movie to 320×240, with 15 frame/sec, at video at 250kbps and audio down-sampling to 22,050Hz at 48kbps, I just tell FFmpeg to do it on the command line:

$ ffmpeg -i movie.avi -s 320x240 -r 15 -b 250 -ar 22050 -ab 48 movie.flv

There are many more options so do check out their manual if you are interested.

There is another thing that we need to do — create a JPEG thumbnail for previewing. This will be displayed in the otherwise empty canvas of the flash player, before [Play] is pressed. For convenience sake, we’ll take the very first frame of the video.

$ ffmpeg -i movie.avi -f mjpeg -t 0.001 movie.jpg

FLVTool2

FLVTool2 is needed to calculate and update meta data in the FLV file. Well, you don’t really need it as you can already play the FLV file spill out from FFmpeg, but because of the missing info, Flash player cannot show the buffering status and current playing position, etc.

I was hesitated to install FLVTool2 because (1) it depends on Ruby which I need to emerge (2) it does not have an ebuild for it. But anyway, having it running is still trivial.

1. Make sure you already have Ruby installed.
2. Download the latest FLVTool2
3. Unpack the tarball, change into its directory, and run ruby setup.rb all as root.

Now just run

$ flvtool2 -U movie.flv

Well, installation is actually optional. You can pretty much run FLVTool2 from inside its unpacked directory, for example.

$ RUBYLIB=lib ruby bin/flvtool2 -U /movie.flv

Your FLV is ready to go! Upload both FLV and generated JPEG thumbnail onto your web hosting account. Make sure they are in the same folder.
FlowPlayer

FlowPlayer is an open source Flash video player that is light-weight (at around 22kb), and pretty easy to configure. Download the latest version from SourceForge.

Unpack the ZIP will give you the player file FlowPlayer.swf. Upload it somewhere on your website.

Now you need to cut and paste this HTML code snippet onto the web page you wish to show the video:

[your site] is the URL to where you keep the FlowPlayer.swf. [base URL] is the directory where you keep the FLV and JPEG files. For example, the final URL to FLV file will be [base URL]/movie.flv.

Paste that onto your website, or into your blog post, and check whether it works!

Please check FlowPlayer documentation on the options going to flashvars.
Conclusion

In fact those steps can be easily automated with a bit of scripting. I shall be posting more movies on Anna’s website.

If your hosting companies are not very generous quota (i.e. small timers who can’t really oversell), or if you think your video will get digged and slashdotted and become overnight hit, then maybe having Google Video or YouTube to host for you is a wiser idea, just in case a huge hosting bill landing on your credit card statement.

Otherwise, you might choose to host those videos on your own account, and regain a bit of control.

scaling rails

2007-02-05T12:58:00.000-08:00

I've said it before, but it bears repeating: There's nothing interesting about how Ruby on Rails scales. We've gone the easy route and merely followed what makes Yahoo!, LiveJournal, and other high-profile LAMP stacks scale high and mighty.

Take state out of the application servers and push it to database/memcached/shared network drive (that's the whole Shared Nothing thang). Use load balancers between your tiers, so you have load balancers -> web servers -> load balancers -> app servers -> load balancers -> database/memcached/shared network drive servers. (Past the entry point, load balancers can just be software, like haproxy).

In a setup like that, you can add almost any number of web and app servers without changing a thing.

Scaling the database is the "hard part", but still a solved problem. Once you get beyond what can be easily managed by a descent master-slave setup (and that'll probably take millions and millions of pageviews per day), you start doing partitioning.

Users 1-100K on cluster A, 100K-200K on cluster B, and so on. But again, this is nothing new. LiveJournal scales like that. I hear eBay too. And probably everyone else that has to deal with huge numbers.

So the scaling part is solved. What's left is judging whether the economics of it are sensible to you. And that's really a performance issue, not a scalability one.

If your app server costs $500 per month (like our dual xeons does) and can drive 30 requests/second on Rails and 60 requests/second on Java/PHP/.NET/whatever (these are totally arbitrary numbers pulled out of my...), then you're faced with the cost of $500 for 2.6 million requests/day on the Rails setup and $250 for the same on the other one.

Now. How much is productivity worth to you? Let's just take a $60K/year programmer. That's $5K/month. If you need to handle 5 million requests/day, your programmer needs to be 10% more productive on Rails to make it even. If he's 15% more productive, you're up $250. And this is not even considering the joy and happiness programmers derive from working with more productive tools (nor that people have claimed to be many times more productive).

Of course, the silly math above hinges on the assumption that the whatever stack is twice as fast as Rails. That’s a very big if. And totally dependent on the application, the people, and so on. Some have found Rails to be as fast or faster than comparable “best-of-breed J2EE stacks” — see http://weblog.rubyonrails.com/archives/2005/04/04/justingehtland-is-back-with-numbers-to-back-it-up/

The point is that the cost per request is plummeting, but the cost of programming is not. Thus, we have to find ways to trade efficiency in the runtime for efficiency in the “thought time” in order to make the development of applications cheaper. I believed we’ve long since entered an age where simplicity of development and maintenance is where the real value lies.
David Heinemeier Hansson
Tuesday, July 12, 2005

PSQL

2007-02-05T00:43:00.000-08:00

psql is so good
hey
in psql
there is a \h
which gives you sql query help,
so \h select
tehn as you are typing things out, hitting tab gives you options for what to use next sometimes
try typing
alter table [tab]
then it displays list of tables it can see.
then there is \? which lists other meta commands
like \dt shows tables
\l lists databases \
\dn lists schemas
\du lists users
well, for your setup mostly you have 1 users, one schema, and couple databases
when you are connected to one database in psql, \c newdbname
chages databases
after a few days you get used to the auto-complete features, and the \h things to help you, it feels like a gui sort of, but much much faster than pgadmin
though, i got into postgresql after being forced to work with oracle (yuk)
the only oracle gui at the time was toad, which is by Que$t $oftware.
and we never could afford to buy it
so we learned the oracle meta database,
which is oddly enough, tables and a database, to describe the database
postgresql has this too
the pg_catalog, where there are what looks like tables, to describe our user databases, schemas, tables, and our database objects.
and the \d commands in psql sort of are short cuts for this
but you can also do select .. from pg_* tables, and that gives us information on table features, and the columns, which is what pgadmin is doing behind the scenes for us to display their things all nicely formatted.
mysql, on the otherhand, does not have a meta database, but then what do you expect from junk :)
in version 3.3 the "show databases" command actually invoked a system command to "ls" (list files) in the mysql directory, since mysql used to (still does?) create databases as directories on the file system.
this has the horrible side effect of making table names Case Sensitive, which violates the SQL standard (lol, and mysql claimes to be sql compliant, but cant even get case insensitive table names :!)
we discovered that one time the hard way by migrating a mysql on windows app to mysql on unix, and of course developers will make code in different spots like
select * from MyTable
select * from mytable
select * from MYTABLE
etc

python handy debug error tip

2007-02-04T16:55:00.000-08:00

Here's a handy way to make debugging your web.py scripts a little
easier. Just add this to your main script (before you do web.run()):

def error():
 if web.webapi.ctx.ip == '': web.debugerror()
 else: origerror()
origerror = web.webapi.internalerror
web.webapi.internalerror = error

Add your IP address where it says. This will show detailed debug
output if an exception occurs, but only when the request is from your
IP address. Anyone else will get the usual "internal server error"
message. This is a convenient way to make your web.py app securely
debuggable without having to manually switch back and forth between
debug/deploy modes every time you want to make a change.

Cheetah base templates

2007-02-04T14:46:00.000-08:00

You can define a base class:

class base:
   def __init__(self):
       web._compiletemplate('default.html', base='base')

Use it:

class page1(base):
   def GET(self):
       web.render('page1.html')


Your templates:

#DEFAULT.HTML
< html >

< body >
#block content
CONTENT
#end block
< / body >
< / html >

#PAGE1.HTML
#extends base

#def content
PAGE 1

You're on the 1st page.
#end def

#PAGE2.HTML
#extends base

#def content
PAGE 2

You're on the 2nd page.
#end def

Ssh keys

2007-02-03T22:37:00.000-08:00

you need to edit /etc/ssh/sshd_config and disable password auth

but before that, you need to make sure you have the ssh keys set up, and the authorized_keys entry, and the directory permissions.

i usually just
ssh-keygen -t dsa -b 1024
that creates
$HOME/.ssh/id_dsa
$HOME/.ssh/id_dsa.pub
then cat $HOME/.ssh/id_dsa.pub >> $HOME/.ssh/authorized_keys
then chmod -R 700 $HOME/.ssh
then edit /etc/ssh/sshd_config to set password auth = no,
or something.

but the quickest defencse is to edit the sshd startup script and add
-p 1234
or some other not commonly thought of port

so then it can work as it is, but you just
ssh -p 1234 yourbox.
when ssh keys are working properly, you should be able to ssh without entering a password.

so your system would have the private, public keys, and the remote web server only needs to have the entry of the id_dsa.pub appended to authorized_keys

Do not forget to check for NULL [NULL + int in postgres results in NULL]

2007-02-03T00:26:00.000-08:00

-- Function: update_total_votes_pictures()

-- DROP FUNCTION update_total_votes_pictures();

CREATE OR REPLACE FUNCTION update_total_votes_pictures()
RETURNS int4 AS
$BODY$ -- returns the number of pixpair entries that were created
DECLARE
_count integer;
all RECORD;
_total_a integer;
_total_b integer;
_total integer;
BEGIN

_count := 0;

FOR all in
select id from pictures
LOOP
select into _total_a sum(pic1_votes) from pixpair where pic1_id = all.id;
if ( _total_a is NULL ) then
_total_a := 0;
end if;
select into _total_b sum(pic2_votes) from pixpair where pic2_id = all.id;
if ( _total_b is NULL ) then
_total_b := 0;
end if;
_total := _total_a + _total_b;
update pictures set total_votes = _total where id = all.id;

_count := _count + 1;
END LOOP;

return _count;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION update_total_votes_pictures() OWNER TO postgres;

Moving a live database to a different directory

2007-01-30T19:51:00.000-08:00

i want to move the running postgresql to a different directory any idea on how 2 do it
as it is running outta space
only 200mb left in 5gig
and in a day that ll be filled

well,
a database can be created to use a tablespace
where the tablespace can be another mounted volume

you want to make sure the volume is never not mounted , such as have it mounted in /etc/fstab

well, lets say you go by a nice new 80gb drive
and format it with ext3 or what ever you like to do
and hook it up in /etc/fstab so that it is /u01 or some path you like a lot
and then in postgresql
create tablespace something path '/u01/myfolder/mytablespace'
then you have to do something to alter database set tablespace , so that it uses that
hmm, iv never actually migrated an existing database to a differetn tablespace,
but i think it must be possible to move things from one tablespace to another one, so that it will physically sit on the new drive for us.
http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html
SET TABLESPACE
This form changes the table's tablespace to the specified tablespace and moves the data file(s) associated with the table to the new tablespace. Indexes on the table, if any, are not moved; but they can be moved separately with additional SET TABLESPACE commands. See also CREATE TABLESPACE.

Application Design for PostgreSQL Performance

2007-01-30T12:48:00.001-08:00

Query Writing Rules

For all database management systems (DBMSes), "round-trip" time is significant. This is the amount of time which it takes a query to get through the the language parser, the driver, across the network interface, the database parser, the planner, the executor, the parser again, back across the network interface, through the driver data handler, and to the client application. DBMSes vary in the amount of time and CPU they take to process this cycle, and for a variety of reasons PostgreSQL is a the high end of time and system resources per round-trip.

Further, PostgreSQL has significant per-transaction overhead, including log output and visibility rules which need to be set with each transaction. While you may think that you are not using transactions for singleton read-only SELECT statement, in fact every single statement in PostgreSQL is in a transaction. In the absence of an explicit transaction, the statement itself is an implicit transaction.

Offsetting this, PostgreSQL is only barely second to Oracle in processing large complex queries, and has the capability to handle complex multi-statement transactions with overlapping concurrency conflicts with ease. We also support cursors, both scrollable and non-scrollable.

Tip 1: Never use many small selects when one big query could go the job.

It's common in MySQL applications to handle joins in the application code; that is, by querying the ID from the parent record and then looping through the child records with that ID manually. This can result in running hundreds or thousands of queries per user interface screen. Each of these queries carres 2-6 milleseconds of round-trip time, which doesn't seem significant until you add it up for 1000 queries, at which point you're losing 3-5 seconds to round trip time. Comparatively, retrieving all of those records in a single query only takes a few hundred milleseconds, a time savings of 80%.

Tip 2: Group many small UPDATES, INSERTS or DELETEs into large statements, or failing that, large transactions.

First, the lack of subselects in early versions of MySQL has caused application developers to design their data modification statements (DML) in much the same way as joins-in-middleware. This is also a bad approach for PostgreSQL. Instead, you want to take advantage of subselects and joins in your UPDATE, INSERT and DELETE statements to try to modify batches in a single statement. This reduces round-trip time and transaction overhead.

In some cases, however, there is no single query which can write all the rows you want and you have to use a bunch of serial statements. In this case, you want to make sure to wrap your series DML statements in an explicit transaction (e.g. BEGIN; UPDATE; UPDATE; UPDATE; COMMIT;). This reduces transaction overhead and can cut execution time by as much as 50%.

Tip 3: Consider bulk loading instead of serial INSERTS

PostgreSQL provides a bulk loading mechanism called COPY, which takes tab-delimited or CSV input from a file or pipe. Where COPY can be used instead of hundreds or thousands of INSERTS, it can cut execution time by up to 75%.

Create a dictionary with these variables such that the keys are the variable names and the corresponding values are the variable values

2007-01-30T12:23:00.000-08:00

> i have few variables and i want to create a dictionary with these
> variables
> such that the keys are the variable names and the corresponding values
> are the variable values.how do i do this easily?
> for ex:
> var1='mark'
> var2=['1','2','3']
> my_dict = create_my_dictionary(var1, var2)
>
> and my_dict is {'var1':'mark', 'var2':['1','2','3']}
>
> is there a function for create_my_dictionary?

var1='mark'
var2=['1','2','3']
my_dict = dict(var1=var1, var2=var2)

In addition, if you are inside a function, and these are the only
variables, using locals() may be useful too:

def f():
a = 1
b = 2
c = locals()
print c
d = 3

prints {'a': 1, 'b': 2}

Tools used by Youtube

2007-01-30T02:45:00.000-08:00

http://www.erg.abdn.ac.uk/users/gorry/course/inet-pages/arp.html
http://isc.org/sw/bind/
http://www.postfix.org/
http://www.gnu.org/software/cfengine/
http://www.google.com/search?lr=&ie=UTF-8&oe=UTF-8&q=AWK%2C
http://www.linuxvirtualserver.org/
http://www.google.com/search?lr=&ie=UTF-8&oe=UTF-8&q=LVM%2C
System Architect

We're looking for a superstar operations-minded person to work on a myriad of vital infrastructure-related projects and handle daily site issues. The range of projects is truly huge and ranges from enhancing site monitoring to helping design and implement our ever-growing site infrastructure. If LVM, LVS, AWK, SVN, and ARP are more than TLAs for you, contact us!
Required Skills and Experience
At least 3 years experience with all of the following: HTML/DHTML, Javascript, Ajax, CSS, Python,
* Experience in managing and scaling a large set of systems.
* Working knowledge of: Linux, TCP/IP networking, security, mail, file systems.
* Scripting (bash, Perl, Python, etc.).
* BS in Computer Science or equivalent experience.
* Versatility. Must be able to pick up new skills / projects quickly.

Preferred Experience

* RAID
* Load balancing (hardware and/or software)
* Postfix
* BIND
* cfengine
* Apache, lighttpd
* Site monitoring tools, such as Nagios

To apply, please email a cover letter and resume (plain text, HTML, or PDF) to jobs@youtube.com. The subject line MUST include: "Job: System Architect".

Purely as a bonus, please send us the decoded version of this text: ORUGKIDBNZZXOZLSEBUXGIBUGI

HOWTO install tsearch2 for mediawiki on postgres (wikipgedia)

2007-01-27T21:19:00.000-08:00

I just got wikipgedia 0.0.4 installed and working, and it is running
sweeter than a horses arse at the Ascot races. tsearch2 gave me some
troubles, but they now seem to be resolved. For those of you new to
postgres or tsearch, I'll show you how I did it.

This HOWTO assumes the following:

You are running Debian unstable and you have the Postgres 8.1 client,
server, and contrib packages installed and running.

Create your database "wikidb" with owner "wikiadmin" and the schema
"mediawiki".

$ su - postgres -c "psql template1"
template1=# CREATE USER wikiadmin WITH PASSWORD "somepassword";
template1=# CREATE DATABASE wikidb WITH OWNER wikiadmin;
template1=# \c wikidb
wikidb=# CREATE SCHEMA mediawiki;
wikidb=# \i /usr/share/postgresql/8.1/contrib/tsearch2.sql

The \c command in psql connects you to the wikidb database.

The \i command in psql "includes" the named file, executing all the SQL
commands in the file as if you had typed them in.

Notice we didn't give the "wikiadmin" account superuser powers inside
postgres. For security reasons, I don't recommend it. So you need to
install tsearch2 into the wikidb database as the user "postgres", the
default superuser account. The commands above accomplish that for you.

When I did this I noticed various errors that worried me. Everything
seems to work, but I'd prefer a version of tsearch2 that didn't spit out
those errors during install. They seem harmless, so I will reproduce
them here, in case any googler shares my anxiety:

### TSORT2 INSTALL ERROR MESSAGES ###
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:13:
NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "pg_ts_dict_pkey" for table "pg_ts_dict"
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:145:
NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "pg_ts_parser_pkey" for table "pg_ts_parser"
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:244:
NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "pg_ts_cfg_pkey" for table "pg_ts_cfg"
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:251:
NOTICE: CREATE TABLE / PRIMARY KEY will create
implicit index "pg_ts_cfgmap_pkey" for table "pg_ts_cfgmap"
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:337:
NOTICE: type "tsvector" is not yet defined
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:342:
NOTICE: argument type tsvector is only a shell
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:396:
NOTICE: type "tsquery" is not yet defined
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:401:
NOTICE: argument type tsquery is only a shell
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:543:
NOTICE: type "gtsvector" is not yet defined
psql:/usr/share/postgresql/8.1/contrib/tsearch2.sql:548:
NOTICE: argument type gtsvector is only a shell
### END OF ERROR MESSAGES ###

Once tsearch2 was installed, I went through the steps to getting
wikipgedia set up through the web browser. It seemed to work. The main
page popped up. I clicked the Edit link. Clicked the Save button.

Horror!

### EDIT ERROR MESSAGES ###
Warning: pg_query(): Query failed: ERROR: column "si_title" of relation
"searchindex" does not exist in
/my/path/to/html/wiki/includes/DatabasePostgreSQL.php on line 98
A database error has occurred Query: INSERT INTO searchindex
(si_page,si_title,si_text) VALUES ( 1, to_tsvector('main
page'),to_tsvector(' wiki software successfully installed please see
documentation on customizing the interface and the user user''s guide
for usage and configuration help test test test second test; see her
knickers in a knot sh bm bm bm one more time for the howto ')) Function:
SearchTsearch2:update Error: 1 ERROR: column "si_title" of relation
"searchindex" does not exist

Backtrace:

* GlobalFunctions.php line 500 calls wfbacktrace()
* DatabasePostgreSQL.php line 573 calls wfdebugdiebacktrace()
* Database.php line 383 calls databasepostgresql::reportqueryerror()
* SearchTsearch2.php line 116 calls databasepostgresql::query()
* SearchUpdate.php line 103 calls searchtsearch2::update()
* index.php line 270 calls searchupdate::doupdate()
### END OF EDIT ERROR MESSAGES ###

Finally, a tsearch2 webpage mentioned something about granting search
privileges to some of the tsearch2 tables. So I did this, first
assuming the powers of the postgres account:

$ su - postgres -c "psql wikidb"
wikidb=# GRANT SELECT ON pg_ts_dict to wikiadmin;
wikidb=# GRANT SELECT ON pg_ts_parser to wikiadmin;
wikidb=# GRANT SELECT ON pg_ts_cfg to wikiadmin;
wikidb=# GRANT SELECT ON pg_ts_cfgmap to wikiadmin;

After doing this, wikipgedia worked like a champ. Edit was fast and
snappy and gave no errors.

Kudos to the wikipgedia team. If only every software package was so
easy to install. A pity I am no longer able to package it up and
include it in Debian.

urlquote post data

2007-01-27T20:05:00.000-08:00

never urlquote a string when passing it by POST method. does not work.

Invoking psql -c from bash for command to run in postgres

2007-01-27T13:32:00.000-08:00

made some pretty neat bash shell scripts that interact with postgresql database just from using psql

#!/bin/sh

#variable for common things postgresql needs
PSQL="/usr/bin/psql -q -t -h 192.168.1.4 -U thein -c ";

function log_mail() {
ip="${1}";
get_country "${ip}";
timestamp="`date +"%Y-%m-%d %H:%M:%S"`";
result="`${PSQL} "set search_path=net; select ip_block_log_save('${timestamp}', '${ip}', '${country}', false);"`";
}
so this is a crusty old example, but i am doing a psql call to invoke ip_block_log_save() stored procedure, from inside a bash shell
there are other goodies there too
but the general idea is it is part of my incoming SMTP email server, and it compares the from address to a blacklist in a postgresql database.
psql -q -t -h thehost -U theuser -c "select command here";
that type of command line parameters supress the formatting of psql output;
so it is a low level basic way to have shell scripts to work with databases, by using the psql command with -c "command to run" option

Using memcached as a session store

2007-01-27T02:31:00.001-08:00

I don't know about Flup. Remember that you have limitations on the
amount of data that you can put into a cookie. Furthermore, you have
to be careful because the user can hack his own cookies. Hence, if
you're going to put anything more than a session ID, you should
encrypt and sign the cookie. If you have lots of session data, you
should instead put your session data on a separate server that all the
Web servers can access. memcached is often used for this task. - Shannon Behrens

lighttpd fastcgi quirk

2007-01-26T03:08:00.001-08:00

Make sure mod_fastcgi is in your modules list, somewhere after mod_rewrite and mod_access, but not after mod_accesslog. You’ll probably want mod_alias as well, for serving admin media.

nginx file locations

2007-01-25T19:54:00.000-08:00

Configuration summary
+ threads are not used
+ using system PCRE library
+ OpenSSL library is not used
+ md5 library is not used
+ sha1 library is not used
+ using system zlib library

nginx path prefix: "/usr/local/nginx"
nginx binary file: "/usr/local/nginx/sbin/nginx"
nginx configuration file: "/usr/local/nginx/conf/nginx.conf"
nginx pid file: "/usr/local/nginx/logs/nginx.pid"
nginx error log file: "/usr/local/nginx/logs/error.log"
nginx http access log file: "/usr/local/nginx/logs/access.log"
nginx http client request body temporary files: "/usr/local/nginx/client_body_temp"
nginx http proxy temporary files: "/usr/local/nginx/proxy_temp"
nginx http fastcgi temporary files: "/usr/local/nginx/fastcgi_temp"

building flex, corelib with ant, json example

2007-01-22T21:57:00.000-08:00

get flex binaries and unzip it to a directory: flex
install jdk 1.5 and set
export JAVA_HOME=/usr/java/jdk1.5.0_10
export PATH=$JAVA_HOME/bin:$PATH

go to directory flex and build samples :
samples/build-samples.sh

it takes a while to build.
now get as3corelib from google code svn
svn co http://as3corelib.googlecode.com/svn/trunk/ as3corelib

need ant to build this
sudo yum install ant

edit as3corelib/build/build.properties and set right directories:
flex2sdk.bin.dir = /www/ask/work/flex/bin
flex2sdk.lib.dir = /www/ask/work/flex/frameworks/libs
flex2sdk.locale.dir = /www/ask/work/flex/frameworks/locale/{locale}

in directory as3corelib
ant -f build/build.xml lib

cp as3codelib/bin/corelib.swc flex/frameworks/libs/.

now compile JSONExample.mxml
flex/bin/mxmlc JSONExample.mxml

VMWARE in linux 2.6.19-1.2895 Fedora Core 6

2007-01-22T00:07:00.000-08:00

had compile errors for vmware compilation with kernel 2.6.19

make[1]: Entering directory `/usr/src/kernels/linux-2.6.19'
CC [M] /tmp/vmware-config0/vmnet-only/driver.o
CC [M] /tmp/vmware-config0/vmnet-only/hub.o
CC [M] /tmp/vmware-config0/vmnet-only/userif.o
/tmp/vmware-config0/vmnet-only/userif.c: In function `VNetCopyDatagramToUser':
/tmp/vmware-config0/vmnet-only/userif.c:629: error: `CHECKSUM_HW' undeclared (first use in this function)
/tmp/vmware-config0/vmnet-only/userif.c:629: error: (Each undeclared identifieris reported only once
/tmp/vmware-config0/vmnet-only/userif.c:629: error: for each function it appears in.)
make[2]: *** [/tmp/vmware-config0/vmnet-only/userif.o] Error 1
make[1]: *** [_module_/tmp/vmware-config0/vmnet-only] Error 2
make[1]: Leaving directory `/usr/src/kernels/linux-2.6.19'
make: *** [vmnet.ko] Error 2
make: Leaving directory `/tmp/vmware-config0/vmnet-only'
Unable to build the vmnet module.

so after finding some hints ( http://www.vmware.com/community/thread.jspa?messageID=512232)

I dit the foloing !! use on own RISK I dont now if this change is OK!! but it works

cd /usr/lib/vmware/modules/source/
cp vmnet.tar vmnet.tar_orig
tar xf vmnet.tar
vi vmnet-only/bridge.c (comment out First line and add second line)
----------------------------
/*if (skb->ip_summed == CHECKSUM_HW) { */
if (skb->ip_summed == CHECKSUM_COMPLETE) {
---------------------------
vi vmnet-only/userif.c
---------------------------
/* skb->ip_summed == CHECKSUM_HW && */ /* Without checksum */
skb->ip_summed == CHECKSUM_PARTIAL &&
---------------------------

tar cf vmnet.tar vmnet-only

I also did this for an other error touch /usr/src/kernels/linux-2.6.19/include/linux/config.h [dont do this do the ln instead ]

sudo ln -s autoconf.h config.h
in
/usr/src/kernels/2.6.19-1.2895.fc6-i686/include/linux
Afther this everything else went fine

fix rpm problems in fedora

2007-01-21T15:31:00.000-08:00

rm -f /var/lib/rpm/__db*
#db_verify /var/lib/rpm/Packages
#rpm --rebuilddb

Python Sets

2007-01-21T01:16:00.001-08:00

http://www.linuxforums.org/programming/introduction_to_python__part_1.html
Sets

I think I don't really have to explain what a set is, as everyone should know them from mathematics. It's simply a pile of elements that do not have ordering and do not contain duplicates.

A set has to be initialized with the elements of a list. Since you already know what a list is, we do this in one step. Just like with dictionaries, print can handle a set as it is.
Once we have a set, I show he first useful feature of sets: testing whether an element is in the set.

inventory_carpenter=set(['helmet', 'gloves', 'hammer'])
print inventory_carpenter # outputs set(['helmet', 'hammer', 'gloves'])

print 'gloves' in inventory_carpenter # outputs 'True'

Since sets are interesting only if we have more that one of them, let's introduce another one! Once we have that, we can immediately see what are the elements that both sets contain (intersection).

inventory_highscaler=set(['helmet', 'rope', 'harness', 'carabiner'])

print inventory_carpenter & inventory_highscaler # outputs 'set(['helmet'])'

Similarly, we can have the union of sets ( using | ), difference ( using - ), or symmetric difference (using ^).
For sets, you don't really need anything else, as you can do every meaningful operation using the ones above. For example, to add a new element, you can use union.

inventory_carpenter = inventory_carpenter | set(['nails'])

plpgsql function to move from usercomments to comments

2007-01-20T12:40:00.000-08:00

-- Function: usercomments_to_comments()

-- DROP FUNCTION usercomments_to_comments();

CREATE OR REPLACE FUNCTION usercomments_to_comments()
RETURNS int4 AS
$BODY$
declare
c usercomments%rowtype;
_count integer;
begin
_count := 0;
for c in
select * from usercomments
loop
insert into comments (comment, postid, created, ip, email, user_id, username, type_id) values (c.comment, c.user_id, c.created, c.ip, c.friend_email,c.friend_id,c.friend_name,2);
_count := _count + 1;
end loop;

return _count;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION usercomments_to_comments() OWNER TO postgres;

sql threaded comment view result set

2007-01-19T11:16:00.000-08:00

Re: Can SQL return a threaded-comment-view result set?

From: mvppetlab ( at ) yahoo ( dot ) com (Chris)

To: "pgsql-general ( at ) postgresql ( dot ) org ( dot ) pgsql-novice"@postgresql.org

Subject: Re: Can SQL return a threaded-comment-view result set?

Date: 9 Oct 2003 18:46:20 -0700

Hello all,

I've been meaning to get back to you all but I just haven't had time.
Okay, I've got a little bit of time now so here goes....

I received many useful answers from many of you including Tom Lane,
Joe Conway, and Josh Berkus. Max Nachlinger in particular on October
5th (which was my birthday) sent me a large amount of threaded
discussion forum code of his own. (Nice birthday present, Max. Thank
you.) I will be investigating his solution when I have more time since
his is almost certainly more efficient than my own.

My own solution is a 20-line PL/pgSQL function I put together after
reading the 7.3 docs at postgresql.org. It requires no modifications
to my original example table definitions other than that I decided to
use a 0 value instead of a NULL value for the in_reply_to column when
a message isn't a reply, because that way my plpgsql function doesn't
have to treat NULL as a special case.

In particular, my solution doesn't require a message to keep pointers
to its children. If a message is a reply, it simply points to its
parent's id via in_reply_to. You can add as many messages as you want
with just single simple INSERT statements; you don't have to do any
tree-refactoring or updating to the parent. The downside is that while
insert speed couldn't be any better and inserting couldn't be any
easier, building the threaded view seems rather algorithmically
inefficient, and in almost all applications optimising for obtaining
the threaded view rather than insert speed is more important. One
probably couldn't base even a moderate-load application on this
solution, but if one wanted to anyways I suppose an in-memory tree
representation could be maintained which allows new messages to be
linked into the in-memory tree efficiently as they're inserted into
the database, and then whenever the application is shutdown and
reloaded it could rebuild that in-memory representation on startup. Or
something. And until you run out of memory.... (Also, simply caching
the results of queries could be effective if you have many identical
queries producing identical results [which my application does] so
this solution might not work too bad for me.)

For the sake of googlers and like novices reading this, I've adapted
my PL/pgSQL function so that it works with the original example I
posted. (My real code uses more fields, different types, and has some
other subtle differences because there's more than one type of table
to consider and there are foreign key constraints.) After loading the
below code, evaluating

select * from threadview(0, 0);

builds a table like the one I wanted in my original posting.

          ---Chris

=================================================
-- This code originally due to Chris Barry,
http://www.goodfig.org/feedback
-- It's hereby placed in the public domain. These public domain
licenses
-- usually have some sort of warning about no guarantee of fitness for
a particular
-- purpose, etc. Well, the below code is DEFINITELY not fit for any
purpose! So,
-- use it at your own peril. Caveat emptor.

-- drop database discussion;
create database discussion;
\c discussion

--  The path to plpgsql.so may need to be edited for your system.
create function plpgsql_call_handler()
        returns opaque as '/usr/local/pgsql/lib/plpgsql.so' language
'c';

create language 'plpgsql' handler plpgsql_call_handler
                           lancompiler 'PL/pgSQL';

create table messages (
      message_id integer,
      in_reply_to integer,
      created date,
      author varchar(20),
      title varchar(30),
      message varchar(256),
      primary key (message_id)
);

-- A threadrow is the same thing as a row from the messages table
-- except a nesting integer has been added so the client knows how
-- much to indent the thread message. I'm not sure if there's a
-- syntax that makes it unnecessary to duplicate the redundant
-- information from the messages table (e.g inheritance).
create type threadrow as (
      message_id integer,
      in_reply_to integer,
      created date,
      author varchar(20),
      title varchar(30),
      message varchar(256),
      nesting integer
);


create or replace function threadview(int, int) returns setof
threadrow as '
declare
 p alias for $1; -- p is the parent
 i alias for $2; -- i is the indentation (nesting)
 c threadrow%rowtype;
 c2 threadrow%rowtype;
begin
 for c in select *, 0 as nesting from messages
                 where in_reply_to = p
                        order by created asc
 loop
        c.nesting = i;
  return next c;
  for c2 in select * from threadview(c.message_id, i+1) loop
   return next c2;
  end loop;
 end loop;
 return;
end;
' language 'plpgsql';


-- Load the table with some example data:

insert into messages values
(1,    0, '2003-09-01', 'John', 'Favorite DB?',
           'What is your favorite database?');
insert into messages values
(2,    0, '2003-09-02', 'Mike', 'New DB2 benchmarks',
           'I just posted some new DB2 benchmarks.');
insert into messages values
(3,    1, '2003-09-03', 'Mike', 'Re: Favorite DB?',
       'I\'d say DB2.');
insert into messages values
(4,    1, '2003-09-05', 'Dave', 'Re: Favorite DB?',
           'I\'m an Oracle man myself.');
insert into messages values
(5,    3, '2003-09-07', 'John', 'Re: Favorite DB?',
       'DB2? I thought you liked free databases?');

svn revert adds before commiting

2007-01-18T18:06:00.001-08:00

svn revert --recursive static/*
will revert all ur svn add

(extract('epoch' from now()) - extract('epoch' from created)) < 14400

2007-01-18T15:38:00.000-08:00

have a column named created tat says when the row was inserted
i want to choose say the columns inserted in the last 4 hrs
10 hrs or 1 day
i cudnt find out how to do it
select * from polls where created .. then waht
are the random database queries too slow
http://www.teenwag.com/poll?n=253
Travis
select * from polls.... where... and extract('epoch' from created) < 14400
wat is epoch
7:04:02 pm
Travis
where the epoch field is the time in seconds, since 1970
and 1440 is how many seconds in 4 hours
actually
7:04:23 pm

is there a field in minutes or hours
7:04:39 pm
Travis
that needs to be and (now() - extract('epoch' from created)) < 14400
there is a field to get the day, hour, minute,
year, month, but those are absolute (==)
if you want something that is 4 hours ago or newer, then you need to do
interval arithmetic yourself by taking the current time
crap that still aitn it
and (extract('epoch' from now()) - extract('epoch' from created)) < 14400
so this takes the absolute time, in seconds now and subtracts the
absolute time from when it was created
which if smaller than 60*60*4, is under 4 hours
7:07:41 pm

wow
kewl
7:08:22 pm
Travis
there is the age(timestamp) function but that displays pretty printing
formattng
not very usefl for a query
http://developer.postgresql.org/pgdocs/postgres/functions-datetime.html

From Hot Concept to Hot Site in Eight Days

2007-01-17T03:30:00.000-08:00

From Hot Concept to Hot Site in Eight Days

By James Hong

AmIHotOrNot.com evolved from an idea into one of the biggest sites on the Web in less than two months. Such rapid growth meant the site had to scale quickly, especially in the first eight days.

It all started the evening of October 3, 2000, when I was sitting in my living room sharing a few beers with my roommate, Jim Young, and my brother, Tony. Jim had just mentioned that he thought a girl we had met at a party was a perfect "10," when the idea suddenly came to me: "Wouldn't it be funny to have a Web site where you could rate random pictures of people from 1 to 10?"

For lack of anything better to do, we kept talking about it. We built the site in our heads, arguing over what kind of functionality the site would have, designing the user interface, and deciding on the details. After three hours, we had a whiteboard with a Web-site layout drawn on it and a burning desire to build the site.

We also had the time. I was unemployed and working on an online resource for Simple Object Access Protocol (SOAP) developers, called XMethods.net, with my brother. Jim was an electrical engineering grad student at the University of California at Berkeley. Because the concept was so simple, the site took only a couple of days to build.

Keep It Simple

After hours of arguing over the Web-site design, the whiteboard had only three pages on it: one page where people voted on the appearance of others; another where people submitted their own pictures; and a final page where people viewed their own rating. We thought that simple was better.

The simplicity of the site's user interface contributed to its addictive nature. Ultimately, we built the site using Apache, PHP, and MySQL.

Initially, we decided to create the site with only three CGI scripts. While that would have been easier, we eventually decided to make the site more scalable in case we wanted to make changes or additions. Instead, we used a state-machine architecture.

Every page view on our site can be thought of as a certain state. For instance, when a user is looking at a picture, he or she is in the "Vote" state. After a user votes, our machine's initial step is to process the "Exit" tasks of the Vote state, such as tallying the vote in the database.

Based on switching variables, the machine then determines what the next state should be. In the simplest case, the next state should be a return to the Vote state.

The state machine's final task is to perform the "Enter" tasks of the next state. This can include selecting a suitable photo to show the user. The Enter task also renders the HTML for the user, which, in this case, would be a page with another photo on which to vote (see Figure 1).

Using this structure for such a simple site might seem like overkill, but it has definitely paid off. Changing the site is extremely easy due to the fact that all of the various tasks are centralized, including the routing between states.

Another advantage of using the state-machine architecture is that it forced us to create distinct interfaces between states. By load balancing across a farm of identical servers, each running an instance of the state machine, the architecture made it easy to scale the site by just adding more Linux machines.

The Deluge

Not long after building our site, we noticed that starting at around 10 a.m. Pacific Standard Time, our machines' performance began degrading to the point at which our servers were being shut down. Upon further inspection, we noticed that the rate at which SYN connections were being made was overwhelming our servers. When one computer wants to initiate a connection with another, it does so by sending a SYN packet that basically says, "Hello, I want to talk to you, can you hear me?" Many Denial of Service (DoS) attacks involve flooding a Web site with SYN packets, so we immediately thought we were under a DoS attack.

Of course, this problem disappeared as soon as we solved the real problem—our system didn't have enough capacity. It turns out that we weren't under attack, but rather the demand for our Web site was more than our system could handle.

Reaching a position in which we might add machines was in and of itself a problem. When we started the site and were immediately flooded with hits, we considered obtaining some colocation space and setting things up ourselves. Then we realized a few things:

We didn't have the money to buy servers, firewalls, and load balancers.
Even if we had the money, it would take a long time to get them.
We didn't have the experience to set these up and maintain them.
We didn't have the resources to handle this side of things.
Hosting Web servers wasn't our core competency.

At that point, we had never heard of managed hosting. We learned about it when searching online for potential Web-hosting services. With managed hosting, customers lease machines that are already racked, instead of renting space in a data center. The managed host guarantees the uptime, handles the server maintenance and monitoring, and sells bandwidth based on actual usage instead of pipe width. Even more importantly, the host has extra machines on hand and can add servers at a moment's notice.

This option let us lease our machines without having to arrange for bank financing (no bank would have lent us money, anyway). With managed hosting, we could outsource our entire network operations department. Thus, this decision was a no-brainer.

We chose to use Rackspace Managed Hosting because it was top ranked by a couple of informational Web sites we consulted. This ended up being a great choice. That first week, I called Rackspace nearly every night around 3 a.m. to request another server. Each time, the new machine would be up and running by the time I awoke the next morning. By the end of the week, we had gone from one Web server to seven.

Database Overload

Once we had all the machines we needed to handle the massive amount of HTTP requests we were receiving, the database started bottlenecking. Our system architecture consisted of seven Web servers running Linux, and a Sun E220 that stored our database. One thing we learned through testing was that the open-source tools performed significantly better on a single-processor 700-MHz Pentium III machine running Linux than they did on a quad-processor Sun machine. MySQL is probably optimized for Linux because the open-source community develops it.

We found a way to help our database keep up with the traffic for our particular application. First, nearly every query made is a SELECT call. Second, there's no reason why all votes must be counted in real time. Given these circumstances, we decided to replicate the active portion of the database on each Web server so that SELECT calls could be made locally. We then started caching votes on each machine, and configured the master database (now a Linux box) to poll each server periodically to collect votes and maintain replication integrity. This method shifts much of the database load to the individual servers, significantly reducing the load on our primary database machine. If the primary database ever becomes overloaded, we can simply add two more server machines and another layer of caching, as illustrated in Figure 2.

Economics 101

Hosting our users' pictures—something we had originally done on our own—was another scaling issue we faced. This was such a big issue—due to the costs involved—that we almost decided to shut the site down. On its second night of operation, shortly after a Salon.com article about our site was published, we were forced to take the site down at 10 p.m.

We had already been operating under an incredible load for the two hours following 8 p.m., when the article went up. I estimate that we served more than 3GB worth of pictures in those two hours, and the number was accelerating. Because we weren't generating any revenue, it was clear that the economics of this plan just didn't scale. Not only did serving pictures incur bandwidth charges, but it also bottlenecked our CPUs.

After stressing out for a couple more hours, I remembered that Yahoo Geocities gives its users FTP access, meaning that we could quickly upload the pictures to a Geocities account. As soon as I realized that we didn't have to host photos ourselves, I called Jim. As an interim measure, we sent new users to Geocities to set up their own accounts and we let them submit the URLs for their pictures, instead of the pictures themselves.

As Jim began working on the solution, it occurred to me that some companies might actually want to host peoples' photos and pay us a bounty for sending them users. By directing our users to these companies, we turned one of our major costs into a revenue stream.

Scaling the Human Element

We had another problem with some users submitting pornography and other inappropriate photos. Initially, we decided to solve this by adding a link under each photo that said, "Click here if this picture is inappropriate." If a photo received enough clicks, based on a formula we had derived, the picture was removed.

This worked pretty well, but not well enough. I sent the chairman of a large advertising network a link to our site with a note proclaiming that: "The odds of getting an inappropriate picture are extremely low." Ten minutes later, I received his reply: "Unfortunately, the first picture I saw was that of a topless woman."

He informed us that if we wanted companies to advertise on our site, we'd have to filter each picture as it came in. Jim built an interface for us to do so. However, we soon realized that we couldn't spend all day screening pictures. The system's human component wasn't scalable. That's when we arrived at the moderator idea.

We decided to build a system in which moderators could vote on whether to approve or reject a picture before it passed on to the main site. If a picture got enough votes either way, it was approved or rejected. By making the decision collective, no single moderator could approve or reject a picture independently.

To help detect any rogue moderators, the system tracks each moderator's accuracy. A vote is counted as wrong when the moderator's vote goes against the final outcome of the picture. For instance, if one person votes to approve, but all others vote to reject, the one person is wrong. Moderators whose accuracy ratings drop below our threshold are kicked out.

We decided to take the moderator system one step further by adding security levels. The higher a moderator's security level, the more his or her votes counted. We also gave higher-ranking moderators special privileges, like an expert mode in which they could judge pictures much faster. We gave the highest ranking moderators the ability to reject or accept moderator applications, and the ability to kick out rogue moderators. Today, these top-level moderators essentially run the moderator section of the site and decide on the specific guidelines for what makes a photo inappropriate. More than 1000 moderators are currently active, and they form our strongest community.

A Full Night's Sleep

I got about 15 hours of sleep over AmIHotorNot.com's first eight days—the time during which we addressed most of our scalability issues. Eight days after launching, we broke the one million page view barrier, reaching more than 1.8 million page views that day. By the end of November, we made NetNielsen's list of the top 25 advertising domains.

The site now runs smoothly, and has handled as many as 14.8 million page views in a single day without even yawning. Looking back, I think that week of scaling easily wins distinction as the most stressful, most exhausting, most rewarding week I've ever had in my life. In this trial by fire, we certainly learned an incredible amount about building and scaling a Web application.

James is a cofounder of Eight Days, which runs the amihotornot.com Web site. [Editor's Note: Since publication the URL has been changed to www.hotornot.com.] He has a Bachelors Degree in electrical engineering and computer science, and an MBA, both from U.C. Berkeley.

Inside MySpace.com

2007-01-16T23:26:00.000-08:00

Inside MySpace.com
January 16, 2007

By David F. Carr

Booming traffic demands put a constant stress on the social network's computing infrastructure. Yet, MySpace developers have repeatedly redesigned the Web site software, database and storage systems in an attempt to keep pace with exploding growth - the site now handles almost 40 billion page views a month. Most corporate Web sites will never have to bear more than a small fraction of the traffic MySpace handles, but anyone seeking to reach the mass market online can learn from its experience.

Story Guide:

A Member Rants: "Fix the God Damn Inbox!"

The Journey Begins

Membership Milestones:

500,000 Users: A Simple Architecture Stumbles

1 Million Users:Vertical Partitioning Solves Scalability Woes

3 Million Users: Scale-Out Wins Over Scale-Up

9 Million Users: Site Migrates to ASP.NET, Adds Virtual Storage

26 Million Users: MySpace Embraces 64-Bit Technology

What's Behind Those "Unexpected Error" Screens?

Also in This Feature:

The Company's Top Players and Alumni

Technologies To Handle Mushrooming Demand

Web Design Experts Grade MySpace

User Customization: Too Much of a Good Thing?

Reader Question: Is MySpace the future of corporate communications? Write to: baseline@ziffdavis.com

Next page: A Member Rants: "Fix the God Damn Inbox!"

A Member Rants: "Fix the God Damn Inbox!"

On his MySpace profile page, Drew, a 17-year-old from Dallas, is bare-chested, in a photo that looks like he might have taken it of himself, with the camera held at arm's length. His "friends list" is weighted toward pretty girls and fast cars, and you can read that he runs on the school track team, plays guitar and drives a blue Ford Mustang.

But when he turns up in the forum where users vent their frustrations, he's annoyed. "FIX THE GOD DAMN INBOX!" he writes, "shouting" in all caps. Drew is upset because the private messaging system for MySpace members will let him send notes and see new ones coming in, but when he tries to open a message, the Web site displays what he calls "the typical sorry ... blah blah blah [error] message."

For MySpace, the good news is that Drew cares so much about access to this online meeting place, as do the owners of 140 million other MySpace accounts. That's what has made MySpace one of the world's most trafficked Web sites.

In November, MySpace, for the first time, surpassed even Yahoo in the number of Web pages visited by U.S. Internet users, according to comScore Media Metrix, which recorded 38.7 billion page views for MySpace as opposed to 38.05 billion for Yahoo.

The bad news is that MySpace reached this point so fast, just three years after its official launch in November 2003, that it has been forced to address problems of extreme scalability that only a few other organizations have had to tackle.

The result has been periodic overloads on MySpace's Web servers and database, with MySpace users frequently seeing a Web page headlined "Unexpected Error" and other pages that apologize for various functions of the Web site being offline for maintenance. And that's why Drew and other MySpace members who can't send or view messages, update their profiles or perform other routine tasks pepper MySpace forums with complaints.

These days, MySpace seems to be perpetually overloaded, according to Shawn White, director of outside operations for the Keynote Systems performance monitoring service. "It's not uncommon, on any particular day, to see 20% errors logging into the MySpace site, and we've seen it as high as 30% or even 40% from some locations," he says. "Compare that to what you would expect from Yahoo or Salesforce.com, or other sites that are used for commercial purposes, and it would be unacceptable." On an average day, he sees something more like a 1% error rate from other major Web sites.

In addition, MySpace suffered a 12-hour outage, starting the night of July 24, 2006, during which the only live Web page was an apology about problems at the main data center in Los Angeles, accompanied by a Flash-based Pac-Man game for users to play while they waited for service to be restored. (Interestingly, during the outage, traffic to the MySpace Web site went up, not down, says Bill Tancer, general manager of research for Web site tracking service Hitwise: "That's a measure of how addicted people are—that all these people were banging on the domain, trying to get in.")

Jakob Nielsen, the former Sun Microsystems engineer who has become famous for his Web site critiques as a principal of the Nielsen Norman Group consultancy, says it's clear that MySpace wasn't created with the kind of systematic approach to computer engineering that went into Yahoo, eBay or Google. Like many other observers, he believes MySpace was surprised by its own growth. "I don't think that they have to reinvent all of computer science to do what they're doing, but it is a large-scale computer science problem," he says.

MySpace developers have repeatedly redesigned the Web site's software, database and storage systems to try to keep pace with exploding growth, but the job is never done. "It's kind of like painting the Golden Gate Bridge, where every time you finish, it's time to start over again," says Jim Benedetto, MySpace's vice president of technology.

So, why study MySpace's technology? Because it has, in fact, overcome multiple systems scalability challenges just to get to this point.

Benedetto says there were many lessons his team had to learn, and is still learning, the hard way. Improvements they are currently working on include a more flexible data caching system and a geographically distributed architecture that will protect against the kind of outage MySpace experienced in July.

Most corporate Web sites will never have to bear more than a small fraction of the traffic MySpace handles, but anyone seeking to reach the mass market online can learn from its example.

Next page: The Journey Begins

The Journey Begins

MySpace may be struggling with scalability issues today, but its leaders started out with a keen appreciation for the importance of Web site performance.

The Web site was launched a little more than three years ago by an Internet marketing company called Intermix Media (also known, in an earlier incarnation, as eUniverse), which ran an assortment of e-mail marketing and Web businesses. MySpace founders Chris DeWolfe and Tom Anderson had previously founded an e-mail marketing company called ResponseBase that they sold to Intermix in 2002. The ResponseBase team received $2 million plus a profit-sharing deal, according to a Web site operated by former Intermix CEO Brad Greenspan. (Intermix was an aggressive Internet marketer—maybe too aggressive. In 2005, then New York Attorney General Eliot Spitzer—now the state's governor—won a $7.9 million settlement in a lawsuit charging Intermix with using adware. The company admitted no wrongdoing.)

In 2003, Congress passed the CAN-SPAM Act to control the use of unsolicited e-mail marketing. Intermix's leaders, including DeWolfe and Anderson, saw that the new laws would make the e-mail marketing business more difficult and "were looking to get into a new line of business," says Duc Chau, a software developer who was hired by Intermix to rewrite the firm's e-mail marketing software.

At the time, Anderson and DeWolfe were also members of Friendster, an earlier entrant in the category MySpace now dominates, and they decided to create their own social networking site. Their version omitted many of the restrictions Friendster placed on how users could express themselves, and they also put a bigger emphasis on music and allowing bands to promote themselves online. Chau developed the initial version of the MySpace Web site in Perl, running on the Apache Web server, with a MySQL database back end. That didn't make it past the test phase, however, because other Intermix developers had more experience with ColdFusion, the Web application environment originally developed by Allaire and now owned by Adobe. So, the production Web site went live on ColdFusion, running on Windows, and Microsoft SQL Server as the database.

Chau left the company about then, leaving further Web development to others, including Aber Whitcomb, an Intermix technologist who is now MySpace's chief technology officer, and Benedetto, who joined about a month after MySpace went live.

MySpace was launched in 2003, just as Friendster started having trouble keeping pace with its own runaway growth. In a recent interview with Fortune magazine, Friendster president Kent Lindstrom admitted his service stumbled at just the wrong time, taking 20 to 30 seconds to deliver a page when MySpace was doing it in 2 or 3 seconds.

As a result, Friendster users began to defect to MySpace, which they saw as more dependable.

Today, MySpace is the clear "social networking" king. Social networking refers to Web sites organized to help users stay connected with each other and meet new people, either through introductions or searches based on common interests or school affiliations. Other prominent sites in this category include Facebook, which originally targeted university students; and LinkedIn, a professional networking site, as well as Friendster. MySpace prefers to call itself a "next generation portal," emphasizing a breadth of content that includes music, comedy and videos. It operates like a virtual nightclub, with a juice bar for under-age visitors off to the side, a meat-market dating scene front and center, and marketers in search of the youth sector increasingly crashing the party.

Users register by providing basic information about themselves, typically including age and hometown, their sexual preference and their marital status. Some of these options are disabled for minors, although MySpace continues to struggle with a reputation as a stomping ground for sexual predators.

MySpace profile pages offer many avenues for self-expression, ranging from the text in the About Me section of the page to the song choices loaded into the MySpace music player, video choices, and the ranking assigned to favorite friends. MySpace also gained fame for allowing users a great deal of freedom to customize their pages with Cascading Style Sheets (CSS), a Web standard formatting language that makes it possible to change the fonts, colors and background images associated with any element of the page. The results can be hideous—pages so wild and discolored that they are impossible to read or navigate—or they can be stunning, sometimes employing professionally designed templates (see "Too Much of a Good Thing?" p. 48).

The "network effect," in which the mass of users inviting other users to join MySpace led to exponential growth, began about eight months after the launch "and never really stopped," Chau says.

News Corp., the media empire that includes the Fox television networks and 20th Century Fox movie studio, saw this rapid growth as a way to multiply its share of the audience of Internet users, and bought MySpace in 2005 for $580 million. Now, News Corp. chairman Rupert Murdoch apparently thinks MySpace should be valued like a major Web portal, recently telling a group of investors he could get $6 billion—more than 10 times the price he paid in 2005—if he turned around and sold it today. That's a bold claim, considering the Web site's total revenue was an estimated $200 million in the fiscal year ended June 2006. News Corp. says it expects Fox Interactive as a whole to have revenue of $500 million in 2007, with about $400 million coming from MySpace.

But MySpace continues to grow. In December, it had 140 million member accounts, compared with 40 million in November 2005. Granted, that doesn't quite equate to the number of individual users, since one person can have multiple accounts, and a profile can also represent a band, a fictional character like Borat, or a brand icon like the Burger King.

Still, MySpace has tens of millions of people posting messages and comments or tweaking their profiles on a regular basis—some of them visiting repeatedly throughout the day. That makes the technical requirements for supporting MySpace much different than, say, for a news Web site, where most content is created by a relatively small team of editors and passively consumed by Web site visitors. In that case, the content management database can be optimized for read-only requests, since additions and updates to the database content are relatively rare. A news site might allow reader comments, but on MySpace user-contributed content is the primary content. As a result, it has a higher percentage of database interactions that are recording or updating information rather than just retrieving it.

Every profile page view on MySpace has to be created dynamically—that is, stitched together from database lookups. In fact, because each profile page includes links to those of the user's friends, the Web site software has to pull together information from multiple tables in multiple databases on multiple servers. The database workload can be mitigated somewhat by caching data in memory, but this scheme has to account for constant changes to the underlying data.

The Web site architecture went through five major revisions—each coming after MySpace had reached certain user account milestones—and dozens of smaller tweaks, Benedetto says. "We didn't just come up with it; we redesigned, and redesigned, and redesigned until we got where we are today," he points out.

Although MySpace declined formal interview requests, Benedetto answered Baseline's questions during an appearance in November at the SQL Server Connections conference in Las Vegas. Some of the technical information in this story also came from a similar "mega-sites" presentation that Benedetto and his boss, chief technology officer Whitcomb, gave at Microsoft's MIX Web developer conference in March.

As they tell it, many of the big Web architecture changes at MySpace occurred in 2004 and early 2005, as the number of member accounts skyrocketed into the hundreds of thousands and then millions.

At each milestone, the Web site would exceed the maximum capacity of some component of the underlying system, often at the database or storage level. Then, features would break, and users would scream. Each time, the technology team would have to revise its strategy for supporting the Web site's workload.

And although the systems architecture has been relatively stable since the Web site crossed the 7 million account mark in early 2005, MySpace continues to knock up against limits such as the number of simultaneous connections supported by SQL Server, Benedetto says: "We've maxed out pretty much everything."

Next page: First Milestone: 500,000 Accounts

First Milestone: 500,000 Accounts

MySpace started small, with two Web servers talking to a single database server. Originally, they were 2-processor Dell servers loaded with 4 gigabytes of memory, according to Benedetto.

Web sites are better off with such a simple architecture—if they can get away with it, Benedetto says. "If you can do this, I highly recommend it because it's very, very non-complex," he says. "It works great for small to medium-size Web sites."

The single database meant that everything was in one place, and the dual Web servers shared the workload of responding to user requests. But like several subsequent revisions to MySpace's underlying systems, that three-server arrangement eventually buckled under the weight of new users. For a while, MySpace absorbed user growth by throwing hardware at the problem—simply buying more Web servers to handle the expanding volume of user requests.

But at 500,000 accounts, which MySpace reached in early 2004, the workload became too much for a single database.

Adding databases isn't as simple as adding Web servers. When a single Web site is supported by multiple databases, its designers must decide how to subdivide the database workload while maintaining the same consistency as if all the data were stored in one place.

In the second-generation architecture, MySpace ran on three SQL Server databases—one designated as the master copy to which all new data would be posted and then replicated to the other two, which would concentrate on retrieving data to be displayed on blog and profile pages. This also worked well—for a while—with the addition of more database servers and bigger hard disks to keep up with the continued growth in member accounts and the volume of data being posted.

Next page: Second Milestone: 1-2 Million Accounts

Second Milestone: 1-2 Million Accounts

As MySpace registration passed 1 million accounts and was closing in on 2 million, the service began knocking up against the input/output (I/O) capacity of the database servers—the speed at which they were capable of reading and writing data. This was still just a few months into the life of the service, in mid-2004. As MySpace user postings backed up, like a thousand groupies trying to squeeze into a nightclub with room for only a few hundred, the Web site began suffering from "major inconsistencies," Benedetto says, meaning that parts of the Web site were forever slightly out of date.

"A comment that someone had posted wouldn't show up for 5 minutes, so users were always complaining that the site was broken," he adds.

The next database architecture was built around the concept of vertical partitioning, with separate databases for parts of the Web site that served different functions such as the log-in screen, user profiles and blogs. Again, the Web site's scalability problems seemed to have been solved—for a while.

The vertical partitioning scheme helped divide up the workload for database reads and writes alike, and when users demanded a new feature, MySpace would put a new database online to support it. At 2 million accounts, MySpace also switched from using storage devices directly attached to its database servers to a storage area network (SAN), in which a pool of disk storage devices are tied together by a high-speed, specialized network, and the databases connect to the SAN. The change to a SAN boosted performance, uptime and reliability, Benedetto says.

Next page: Third Milestone: 3 Million Accounts

Third Milestone: 3 Million Accounts

As the Web site's growth continued, hitting 3 million registered users, the vertical partitioning solution couldn't last. Even though the individual applications on sub-sections of the Web site were for the most part independent, there was also information they all had to share. In this architecture, every database had to have its own copy of the users table—the electronic roster of authorized MySpace users. That meant when a new user registered, a record for that account had to be created on nine different database servers. Occasionally, one of those transactions would fail, perhaps because one particular database server was momentarily unavailable, leaving the user with a partially created account where everything but, for example, the blog feature would work for that person.

And there was another problem. Eventually, individual applications like blogs on sub-sections of the Web site would grow too large for a single database server.

By mid-2004, MySpace had arrived at the point where it had to make what Web developers call the "scale up" versus "scale out" decision—whether to scale up to bigger, more powerful and more expensive servers, or spread out the database workload across lots of relatively cheap servers. In general, large Web sites tend to adopt a scale-out approach that allows them to keep adding capacity by adding more servers.

But a successful scale-out architecture requires solving complicated distributed computing problems, and large Web site operators such as Google, Yahoo and Amazon.com have had to invent a lot of their own technology to make it work. For example, Google created its own distributed file system to handle distributed storage of the data it gathers and analyzes to index the Web.

In addition, a scale-out strategy would require an extensive rewrite of the Web site software to make programs designed to run on a single server run across many—which, if it failed, could easily cost the developers their jobs, Benedetto says.

So, MySpace gave serious consideration to a scale-up strategy, spending a month and a half studying the option of upgrading to 32-processor servers that would be able to manage much larger databases, according to Benedetto. "At the time, this looked like it could be the panacea for all our problems," he says, wiping away scalability issues for what appeared then to be the long term. Best of all, it would require little or no change to the Web site software.

Unfortunately, that high-end server hardware was just too expensive—many times the cost of buying the same processor power and memory spread across multiple servers, Benedetto says. Besides, the Web site's architects foresaw that even a super-sized database could ultimately be overloaded, he says: "In other words, if growth continued, we were going to have to scale out anyway."

So, MySpace moved to a distributed computing architecture in which many physically separate computer servers were made to function like one logical computer. At the database level, this meant reversing the decision to segment the Web site into multiple applications supported by separate databases, and instead treat the whole Web site as one application. Now there would only be one user table in that database schema because the data to support blogs, profiles and other core features would be stored together.

Now that all the core data was logically organized into one database, MySpace had to find another way to divide up the workload, which was still too much to be managed by a single database server running on commodity hardware. This time, instead of creating separate databases for Web site functions or applications, MySpace began splitting its user base into chunks of 1 million accounts and putting all the data keyed to those accounts in a separate instance of SQL Server. Today, MySpace actually runs two copies of SQL Server on each server computer, for a total of 2 million accounts per machine, but Benedetto notes that doing so leaves him the option of cutting the workload in half at any time with minimal disruption to the Web site architecture.

There is still a single database that contains the user name and password credentials for all users. As members log in, the Web site directs them to the database server containing the rest of the data for their account. But even though it must support a massive user table, the load on the log-in database is more manageable because it is dedicated to that function alone.

Next page: Fourth Milestone: 9 Million–17 Million Accounts

Fourth Milestone: 9 Million–17 Million Accounts

When MySpace reached 9 million accounts, in early 2005, it began deploying new Web software written in Microsoft's C# programming language and running under ASP.NET. C# is the latest in a long line of derivatives of the C programming language, including C++ and Java, and was created to dovetail with the Microsoft .NET Framework, Microsoft's model architecture for software components and distributed computing. ASP.NET, which evolved from the earlier Active Server Pages technology for Web site scripting, is Microsoft's current Web site programming environment.

Almost immediately, MySpace saw that the ASP.NET programs ran much more efficiently, consuming a smaller share of the processor power on each server to perform the same tasks as a comparable ColdFusion program. According to CTO Whitcomb, 150 servers running the new code were able to do the same work that had previously required 246. Benedetto says another reason for the performance improvement may have been that in the process of changing software platforms and rewriting code in a new language, Web site programmers reexamined every function for ways it could be streamlined.

Eventually, MySpace began a wholesale migration to ASP.NET. The remaining ColdFusion code was adapted to run on ASP.NET rather than on a Cold-Fusion server, using BlueDragon.NET, a product from New Atlanta Communications of Alpharetta, Ga., that automatically recompiles ColdFusion code for the Microsoft environment.

When MySpace hit 10 million accounts, it began to see storage bottlenecks again. Implementing a SAN had solved some early performance problems, but now the Web site's demands were starting to periodically overwhelm the SAN's I/O capacity—the speed with which it could read and write data to and from disk storage.

Part of the problem was that the 1 million-accounts-per-database division of labor only smoothed out the workload when it was spread relatively evenly across all the databases on all the servers. That was usually the case, but not always. For example, the seventh 1 million-account database MySpace brought online wound up being filled in just seven days, largely because of the efforts of one Florida band that was particularly aggressive in urging fans to sign up.

Whenever a particular database was hit with a disproportionate load, for whatever reason, the cluster of disk storage devices in the SAN dedicated to that database would be overloaded. "We would have disks that could handle significantly more I/O, only they were attached to the wrong database," Benedetto says.

At first, MySpace addressed this issue by continually redistributing data across the SAN to reduce these imbalances, but it was a manual process "that became a full-time job for about two people," Benedetto says.

The longer-term solution was to move to a virtualized storage architecture where the entire SAN is treated as one big pool of storage capacity, without requiring that specific disks be dedicated to serving specific applications. MySpace now standardized on equipment from a relatively new SAN vendor, 3PARdata of Fremont, Calif., that offered a different approach to SAN architecture.

In a 3PAR system, storage can still be logically partitioned into volumes of a given capacity, but rather than being assigned to a specific disk or disk cluster, volumes can be spread or "striped" across thousands of disks. This makes it possible to spread out the workload of reading and writing data more evenly. So, when a database needs to write a chunk of data, it will be recorded to whichever disks are available to do the work at that moment rather than being locked to a disk array that might be overloaded. And since multiple copies are recorded to different disks, data can also be retrieved without overloading any one component of the SAN.

To further lighten the burden on its storage systems when it reached 17 million accounts, in the spring of 2005 MySpace added a caching tier—a layer of servers placed between the Web servers and the database servers whose sole job was to capture copies of frequently accessed data objects in memory and serve them to the Web application without the need for a database lookup. In other words, instead of querying the database 100 times when displaying a particular profile page to 100 Web site visitors, the site could query the database once and fulfill each subsequent request for that page from the cached data. Whenever a page changes, the cached data is erased from memory and a new database lookup must be performed—but until then, the database is spared that work, and the Web site performs better.

The cache is also a better place to store transitory data that doesn't need to be recorded in a database, such as temporary files created to track a particular user's session on the Web site—a lesson that Benedetto admits he had to learn the hard way. "I'm a database and storage guy, so my answer tended to be, let's put everything in the database," he says, but putting inappropriate items such as session tracking data in the database only bogged down the Web site.

The addition of the cache servers is "something we should have done from the beginning, but we were growing too fast and didn't have time to sit down and do it," Benedetto adds.

Fifth Milestone: 26 Million Accounts

In mid-2005, when the service reached 26 million accounts, MySpace switched to SQL Server 2005 while the new edition of Microsoft's database software was still in beta testing. Why the hurry? The main reason was this was the first release of SQL Server to fully exploit the newer 64-bit processors, which among other things significantly expand the amount of memory that can be accessed at one time. "It wasn't the features, although the features are great," Benedetto says. "It was that we were so bottlenecked by memory."

More memory translates into faster performance and higher capacity, which MySpace sorely needed. But as long as it was running a 32-bit version of SQL Server, each server could only take advantage of about 4 gigabytes of memory at a time. In the plumbing of a computer system, the difference between 64 bits and 32 bits is like widening the diameter of the pipe that allows information to flow in and out of memory. The effect is an exponential increase in memory access. With the upgrade to SQL Server 2005 and the 64-bit version of Windows Server 2003, MySpace could exploit 32 gigabytes of memory per server, and in 2006 it doubled its standard configuration to 64 gigabytes.

Next page: Unexpected Errors

Unexpected Errors

If it were not for this series of upgrades and changes to systems architecture, the MySpace Web site wouldn't function at all. But what about the times when it still hiccups? What's behind those "Unexpected Error" screens that are the source of so many user complaints?

One problem is that MySpace is pushing Microsoft's Web technologies into territory that only Microsoft itself has begun to explore, Benedetto says. As of November, MySpace was exceeding the number of simultaneous connections supported by SQL Server, causing the software to crash. The specific circumstances that trigger one of these crashes occur only about once every three days, but it's still frequent enough to be annoying, according to Benedetto. And anytime a database craps out, that's bad news if the data for the page you're trying to view is stored there. "Anytime that happens, and uncached data is unavailable through SQL Server, you'll see one of those unexpected errors," he explains.

Last summer, MySpace's Windows 2003 servers shut down unexpectedly on multiple occasions. The culprit turned out to be a built-in feature of the operating system designed to prevent distributed denial of service attacks—a hacker tactic in which a Web site is subjected to so many connection requests from so many client computers that it crashes. MySpace is subject to those attacks just like many other top Web sites, but it defends against them at the network level rather than relying on this feature of Windows—which in this case was being triggered by hordes of legitimate connections from MySpace users.

"We were scratching our heads for about a month trying to figure out why our Windows 2003 servers kept shutting themselves off," Benedetto says. Finally, with help from Microsoft, his team figured out how to tell the server to "ignore distributed denial of service; this is friendly fire."

And then there was that Sunday night last July when a power outage in Los Angeles, where MySpace is headquartered, knocked the entire service offline for about 12 hours. The outage stood out partly because most other large Web sites use geographically distributed data centers to protect themselves against localized service disruptions. In fact, MySpace had two other data centers in operation at the time of this incident, but the Web servers housed there were still dependent on the SAN infrastructure in Los Angeles. Without that, they couldn't serve up anything more than a plea for patience.

According to Benedetto, the main data center was designed to guarantee reliable service through connections to two different power grids, backed up by battery power and a generator with a 30-day supply of fuel. But in this case, both power grids failed, and in the process of switching to backup power, operators blew the main power circuit.

MySpace is now working to replicate the SAN to two other backup sites by mid-2007. That will also help divvy up the Web site's workload, because in the normal course of business, each SAN location will be able to support one-third of the storage needs. But in an emergency, any one of the three sites would be able to sustain the Web site independently, Benedetto says.

While MySpace still battles scalability problems, many users give it enough credit for what it does right that they are willing to forgive the occasional error page.

"As a developer, I hate bugs, so sure it's irritating," says Dan Tanner, a 31-year-old software developer from Round Rock, Texas, who has used MySpace to reconnect with high school and college friends. "The thing is, it provides so much of a benefit to people that the errors and glitches we find are forgivable." If the site is down or malfunctioning one day, he simply comes back the next and picks up where he left off, Tanner says.

That attitude is why most of the user forum responses to Drew's rant were telling him to calm down and that the problem would probably fix itself if he waited a few minutes. Not to be appeased, Drew wrote, "ive already emailed myspace twice, and its BS cause an hour ago it was working, now its not ... its complete BS." To which another user replied, "and it's free."

Benedetto candidly admits that 100% reliability is not necessarily his top priority. "That's one of the benefits of not being a bank, of being a free service," he says.

In other words, on MySpace the occasional glitch might mean the Web site loses track of someone's latest profile update, but it doesn't mean the site has lost track of that person's money. "That's one of the keys to the Web site's performance, knowing that we can accept some loss of data," Benedetto says. So, MySpace has configured SQL Server to extend the time between the "checkpoints" operations it uses to permanently record updates to disk storage—even at the risk of losing anywhere between 2 minutes and 2 hours of data—because this tweak makes the database run faster.

Similarly, Benedetto's developers still often go through the whole process of idea, coding, testing and deployment in a matter of hours, he says. That raises the risk of introducing software bugs, but it allows them to introduce new features quickly. And because it's virtually impossible to do realistic load testing on this scale, the testing that they do perform is typically targeted at a subset of live users on the Web site who become unwitting guinea pigs for a new feature or tweak to the software, he explains.

"We made a lot of mistakes," Benedetto says. "But in the end, I think we ended up doing more right than we did wrong."

Next page: MySpace Base Case

MySpace Base Case
Headquarters: Fox Interactive Media (parent company), 407 N. Maple Drive, Beverly Hills, CA 90210
Phone: (310) 969-7200
Business: MySpace is a "next generation portal" built around a social networking Web site that allows members to meet, and stay connected with, other members, as well as their favorite bands and celebrities.
Chief Technology Officer: Aber Whitcomb
Financials in 2006: Estimated revenue of $200 million.

BASELINE GOALS:

Double MySpace.com advertising rates, which in 2006 were typically a little more than 10 cents per 1,000 impressions.

Generate revenue of at least $400 million from MySpace—out of $500 million expected from News Corp.'s Fox Interactive Media unit—in this fiscal year.

Secure revenue of $900 million over the next three years from a search advertising deal with Google.

User Customization: Too Much of a Good Thing?
January 16, 2007

By David F. Carr

One of the features members love about MySpace is that it gives people who open up an account a great deal of freedom to customize their pages with Cascading Style Sheets (CSS), a Web format that allows users to change the fonts, colors and background images associated with any element of the page.

That feature was really "kind of a mistake," says Duc Chau, one of the social networking site's original developers. In other words, he neglected to write a routine that would strip Web coding tags from user postings—a standard feature on most Web sites that allow user contributions.

The Web site's managers belatedly debated whether to continue allowing users to post code "because it was making the page load slow, making some pages look ugly, and exposing security holes," recalls Jason Feffer, former MySpace vice president of operations. "Ultimately we said, users come first, and this is what they want. We decided to allow the users to do what they wanted to do, and we would deal with the headaches."

In addition to CSS, JavaScript, a type of programming code that runs in the user's browser, was originally allowed. But MySpace eventually decided to filter it out because it was exploited to hack the accounts of members who visited a particular profile page. MySpace, however, still experiences periodic security problems, such as the infected QuickTime video that turned up in December, automatically replicating itself from profile page to profile page. QuickTime's creator, Apple Computer, responded with a software patch for MySpace to distribute. Similar problems have cropped up in the past with other Web software, such as the Flash viewer.

Planner: Calculating the Costs of a Web Site Makeover
January 7, 2007

PROJECT OVERVIEW

At your consumer products company, "Web 2.0" is officially in danger of becoming this year's "thinking outside of the box": buzzword fodder that's big on elocution, but short on execution.

That's why this six-month project to enliven and build out your consumer Web site will place so much emphasis on defining exactly what your company wants to accomplish with "Web 2.0"—a catchall that means different things to different businesses. For your company, it will mean adding more dimension to a flat consumer Web site and, most important, developing more direct connections to a customer base that gets harder to reach each day.

Part of your motivation here will be pure survival. Old-world media and marketing approaches are increasingly less effective in the face of rapidly fragmenting customer niches. But far from a business problem, those niches represent "an opportunity for customer engagement," says Patricia Seybold, founder and CEO of the Patricia Seybold Group and author of Outside Innovation: How Your Customers Will Co-Design Your Company's Future. "Instead of combating fragmentation, companies should be leveraging online tools and online communities to leverage customer fragmentation and to address more customers' needs, not fewer."

Delivering that online promise will mean actively and constructively involving your customers in your business processes through those online tools—blogs, surveys, contests, forums, ratings, and other one-to-one communication exchanges that will do more than just create a multilayered online presence. They'll also use a data analytics backbone to channel that customer interaction into tangible feedback on everything from smarter product development to more effective advertising to "viral" (buzzwords never die) word-of-mouth sales.

Web Design Experts Grade MySpace
January 15, 2007

By David F. Carr

MySpace.com's continued growth flies in the face of much of what Web experts have told us for years about how to succeed on the Internet. It's buggy, often responding to basic user requests with the dreaded "Unexpected Error" screen, and stocked with thousands of pages that violate all sorts of conventional Web design standards with their wild colors and confusing background images. And yet, it succeeds anyway.

Why?

"The hurdle is a little bit lower for something like this specifically because it's not a mission-critical site," says Jakob Nielsen, the famed Web usability expert and principal of the Nielsen Norman Group, which has its headquarters in Fremont, Calif. "If someone were trying to launch an eBay competitor and it had problems like this, it would never get off the ground." For that reason, he finds it difficult to judge MySpace by the same standards as more utilitarian Web sites, such as a shopping site where usability flaws might lead to abandoned shopping carts.

On most Web sites designed to sell or inform, the rampant self-expression Nielsen sees on MySpace would be a fatal flaw. "Usually, people don't go to a Web site to see how to express yourself," he says. "But people do go to MySpace to see how you express yourself, to see what bands you like, all that kind of stuff."

The reliability of the service also winds up being judged by different standards, according to Nielsen. If a Web user finds an e-commerce site is down, switching to a competitor's Web site is an easy decision. "But in this case, because your friends are here, you're more likely to want to come back to this site rather than go to another site," Nielsen says. "Most other Web sites could not afford that."

From a different angle, Newsvine CEO Mike Davidson says one of the things MySpace has done a great job of is allowing millions of members to sort themselves into smaller communities of a more manageable size, based on school and interest group associations. Davidson has studied MySpace to glean ideas for social networking features he is adding to his own Web site for news junkies. As a Web developer and former manager of media product development for the ESPN.com sports news Web site, he admires the way MySpace has built a loyal community of members.

"One of the things MySpace has been really great about is turning crap into treasure," Davidson says. "You look at these profile pages, and most of the comments are stuff like, 'Love your hair,' so to an outsider, it's kind of stupid. But to that person, that's their life." The "treasure" MySpace extracts from this experience is the billions of page views recorded as users click from profile to profile, socializing and gossiping online.

On the other hand, parts of the MySpace Web application are so inefficient, requiring multiple clicks and page views to perform simple tasks, that a good redesign would probably eliminate two-thirds of those page views, Davidson says. Even if that hurt MySpace's bragging rights as one of the most visited Web sites, it would ultimately lead to more satisfied users and improve ad rates by making each page view count for more, he argues.

"In a lot of ways, he's very right," says Jason Feffer, a former MySpace vice president of operations. While denying that the Web site was intentionally designed to inflate the number of page views, he says it's true that MySpace winds up with such a high inventory of page views that there is never enough advertising to sell against it. "On the other hand, when you look at the result, it's hard to argue that what we did with the interface and navigation was bad," he maintains. "And why change it, when you have success?"

Feffer, who is currently working on his own startup of an undisclosed nature called SodaHead.com, says one of the biggest reasons MySpace succeeded was that its users were always willing to cut it some slack.

"Creating a culture where users are sympathetic is very important," Feffer says. Especially in the beginning, many users thought the Web site was "something Tom was running out of his garage," he says, referring to MySpace president and co-founder Tom Anderson, who is the public face of the service by virtue of being the first online "friend" who welcomes every new MySpace user.

That startup aura made users more tolerant of occasional bugs and outages, according to Feffer. "They would think that it was cool that during an outage, you're putting up Pac-Man for me to play with," he says. "If you're pretending to be Yahoo or Google, you're not going to get much sympathy."

MySpace is starting to be held to a higher standard, however, since being purchased by News Corp. in 2005, and the reaction was different following a 12-hour outage this past summer, Feffer says: "I don't think anyone believed it was Tom's little garage project anymore."

MySpace Insiders
January 16, 2007

By David F. Carr

Rupert Murdoch
Chairman, News Corp.
As the creator of a media empire that includes 20th Century Fox, the Fox television stations, the New York Post and many other news, broadcast and music properties, Murdoch championed the purchase of MySpace.com as a way of significantly expanding Fox Interactive Media's presence on the Web.

Chris Dewolfe
CEO, MySpace
DeWolfe, who is also a co-founder of MySpace.com, led its creation while employed by Intermix Media and continues to manage it today as a unit of News Corp.'s Fox Interactive Media. Previously, he was CEO of the e-mail marketing firm ResponseBase, which Intermix bought in 2002.

Tom Anderson
President, MySpace
A co-founder of MySpace, Anderson is best known as "Tom," the first person who appears on the "friends list" of new MySpace.com members and who acts as the public face of the Web site's support organization. He and DeWolfe met at Xdrive, the Web file storage company where both worked prior to starting ResponseBase.

Aber Whitcomb
Chief Technology Officer, MySpace
Whitcomb is a co-founder of MySpace, where he is responsible for engineering and technical operations. He speaks frequently on the issues of large-scale computing, networking and storage.

Jim Benedetto
Vice President of Technology, MySpace
Benedetto joined MySpace about a month after it launched, in late 2003. On his own MySpace profile page, he describes himself as a 27-year-old 2001 graduate of the University of Southern California whose trip to Australia last year included diving in a shark tank. Just out of school in 2001, he joined Quack.com, a voice portal startup that was acquired by America Online. Today, Benedetto says he is "working triple overtime to take MySpace international."

Jason Feffer
Former vice president of operations, MySpace
Starting with MySpace's launch in late 2003, Feffer was responsible for MySpace's advertising and support operations. He also worked with DoubleClick, the Web site advertising vendor, to ensure that its software met MySpace's scalability requirements and visitor targeting goals. Since leaving MySpace last summer, he has been working on a startup called SodaHead.com, which promises to offer a new twist on social networking when it launches later this year.

Duc Chau
Founder and CEO, Flukiest
Chau, as an employee of Intermix, led the creation of a pilot version of the MySpace Web site, which employed Perl and a MySQL database, but left Intermix shortly after the production Web site went live. He went on to work for StrongMail, a vendor of e-mail management appliances. Chau now runs Flukiest, a social networking and file-sharing Web site that is also selling its software for use within other Web sites.

MySpace Tech Roster
January 16, 2007

By David F. Carr

MySpace has managed to scale its Web site infrastructure to meet booming demand by using a mix of time-proven and leading-edge information technologies.

APPLICATION	PRODUCT	SUPPLIER
Web application technology	Microsoft Internet Information Services, .NET Framework	Microsoft
Server operating system	Windows 2003	Microsoft
Programming language and environment	Applications written in C# for ASP.NET	Microsoft
Programming language and environment	Site originally launched on Adobe's ColdFusion; remaining ColdFusion code runs under New Atlanta's BlueDragon.NET product.	Adobe, New Atlanta
Database	SQL Server 2005	Microsoft
Storage area network	3PAR Utility Storage	3PARdata
Internet application acceleration	NetScaler	Citrix Systems
Server hardware	Standardized on HP 585 (see below)	Hewlett-Packard
Ad server software	DART Enterprise	DoubleClick
Search and keyword advertising	Google search	Google

Standard database server configuration consists of Hewlett-Packard HP 585 servers with 4 AMD Opteron dual-core, 64-bit processors with 64 gigabytes of memory (recently upgraded from 32). The operating system is Windows 2003, Service Pack 1; the database software is Microsoft SQL Server 2005, Service Pack 1. There's a 10-gigabit-per-second Ethernet network card, plus two host bus adapters for storage area network communications. The infrastructure for the core user profiles application includes 65 of these database servers with a total capacity of more than 2 terabytes of memory, 520 processors and 130 gigabytes of network throughput. Source: MySpace.com user conference presentations

select * from pg_stat_activity order by backend_start;

2007-01-16T13:28:00.000-08:00

select backend_start, client_addr from pg_stat_activity order by backend_start;

select * from pg_stat_activity order by backend_start;

Debugging postgresql functions

2007-01-16T01:33:00.001-08:00

in psql, i do the
set client_min_messages TO debug;

and then in the function you can do
raise debug 'foo message: %', _a value;
where % are replaced by the _a_value, variable, etc?

the raise debug is only shown if client_min_messages is debug
so you can leave them in there after, and turn on debug mode later
then
when you create
the function
to test i sometimes do
begin transaction;
select function();
and then when it fails, or after i poke at results,
rollback;
well, handy for some kinds of testing

Raw postgresql function

2007-01-16T01:32:00.001-08:00

i would start off with
create function do_vore (
pixpair_id integer,
picture_id integer
) returns integer as $$
DECLARE
_id integer;
BEGIN
_id := 0;

-- insert into pixpair_ips
-- increment count in pixpair
-- update picture set totals there too
return _id;
END;
$$ language plpgsql;?1

Reboot server detecting open connections

2007-01-16T01:20:00.000-08:00

#!/bin/bash
cd /home/mark/work/pop
svn up
echo 'updated to latest code. trying restart'

#find number of active connections that are established
number_of_conn=`netstat -apn 2>&1 | grep :80 | grep -i established | wc -l`
#echo $number_of_conn

trial=0
while [ $number_of_conn -ne 0 ]
do
trial=$((trial +1 ))
echo "$number_of_conn connections: trying again"
number_of_conn=`netstat -apn 2>&1 | grep :80 | grep -i established | wc -l`
done

/home/mark/bin/startstop
echo 'there were 0 connections. restarted'
echo "number of trials: $trial"

Remove dup pixpair

2007-01-16T00:55:00.001-08:00

remove_dup_pixpair
select pic_id, sum(pic_votes) as votes from

(
select pic1_id as pic_id,
pic1_votes as pic_votes
from
pixpair p1

union

select pic2_id as pic_id,
pic2_votes as pic_votes
from
pixpair p2

) a
group by pic_id
12:45:29 am

sum of all pictures.total_votes should equal pixpair.total_votes
12:45:33 am
Travis
and then, i sum this.
select sum(votes) from
(

select pic_id, sum(pic_votes) as votes from

(
select pic1_id as pic_id,
pic1_votes as pic_votes
from
pixpair p1

union

select pic2_id as pic_id,
pic2_votes as pic_votes
from
pixpair p2

) a
group by pic_id

)b
sum
-------
11622
and that gives a differnt number still.

Transactions and deletes in pgsql

2007-01-15T23:37:00.000-08:00

begin transaction;
delete from pixpair_ips where pixpair_id in (
select id from pixpair where pic1_id = 518 or pic2_id=518);
delete from pixpair where pic1_id = 518 or pic2_id=518;
DELETE 41
select update_pixpair(518);
update_pixpair
----------------
43
commit transaction;
COMMIT

Password securing postgres

2007-01-15T23:33:00.000-08:00

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0 password
host all all all password
#all and 0.0.0.0 mean the same - 0.0.0.0 is an ip address meaning any
# IPv6 local connections:
host all all ::1/128 password

Duplicates in pictures table

2007-01-15T23:31:00.000-08:00

select
p1.id as pix1_id,
p2.id as pix2_id
from
pixpair p1,
pixpair p2
where
p1.pic1_id = p2.pic2_id and p1.pic2_id = p2.pic1_id;
should show dupliacates..

select * from pixpair
where (pic1_id = _new_pic_id and pic2_id = allpics.id)
or (pic2_id = _new_pic_id and pic1.id = allpics.id) ) );

Dont forget to check if variable is valid before slicing object

2007-01-14T03:14:00.001-08:00

#if $cutie.comment
$cutie.comment[0:100]
#end if

Dont forget to put if exists
else you will get unscriptable object error

Static variables in python

2007-01-09T16:56:00.000-08:00

ints are not modifiable, so += actually rebinds the variable.  Try:

def_f():
   global global_var_c_hits
   global_var_c_hits +=1

Cron jobs need quiet operation

2007-01-08T18:42:00.001-08:00

Cron jobs need quiet operation; if a command generates output, you’ll get an email from cron with the command output. So if you want to fetch a file silently with wget or curl, use a command like this:

curl –silent –output output_filename http://go.com/urltofetch.html

wget –quiet –output-document output_filename http://google.com/urltofetch.html

There are shorter versions of these options, but using the verbose options will make code or cron jobs easier to understand if you come back to them. Be aware that urls with “&” in them can confuse wget at least, so depending on your shell (bash, csh, tcsh), you may need to put single or double quotes around the url.

Threaded data collection with Python

2007-01-06T11:52:00.000-08:00

Threaded data collection with Python, including examples

Posted in General

October 19 2006

On today's Internet 2.0 there are all sorts of data feeds available for consumption. From APIs to RSS feeds, it seems like nearly every site has a machine-readable output. There are many reasons why you'd want to collect this information, which I won't go in to, so in this post I'm going to walk you through an application which consumes RSS feeds. I'll be using the Python scripting language, and I'll show you an evolution of the ways to go about the task:

Application introduction

Our application is going to work like this:

A database contains the list of RSS feeds. This is long - 1000+ records
Our application reads this list of feeds and processes them
The items from the feeds are stored in the database

Database manipulation and RSS feed parsing are outside the scope of this tutorial, so we'll start off by defining some empty functions that handle all this:

PLAIN TEXT

PYTHON:

def get_feed_list():
""" Returns a list of tuples: (id, feed_url) """
pass
def get_feed_contents(feed_url):
""" Gets feed over HTTP, returns RSS XML """
pass
def parse_feed(feed_rss):
""" Parses the feed and returns a list of items """
pass
def store_feed_items(id, items):
""" Takes a feed_id and a list of items and stored them in the DB """
pass

We're going to have all these in a module called "functions", which can just be a file called functions.py in the same directory ( < python3.0)

Implementation 1: Single-threaded

This is the way most people would do it at first. So simple, I'll just post the sample code:

PLAIN TEXT

PYTHON:

import functions
for id, feed_url in get_feed_list():
rss = functions.get_feed_contents(feed_url)
items = functions.parse_feed(rss)
functions.store_feed_items(id, items)

Pretty simple huh? But there are fundamental problems. Feeds are usually slow, meaning that your program will spend a lot of time waiting for feeds to come in before you can parse them. You program will also be spending time parsing feeds when it could be getting them from the internet as well. Consequently this program will be as slow as molasses. It's like eating a bowl of peas one at a time - you'd rather just shovel them in wouldn't you? Enter: threading.

Implementation 2

So we reckon: "If we use threads, this will make things faster?" Answer: Yes. However, there are quite a few ways of doing this. We'll start off with this:

PLAIN TEXT

PYTHON:

import threading, functions, time
def thread(id, feed_url):
rss = functions.get_feed_contents(feed_url)
items = functions.parse_feed(rss)
functions.store_feed_items(id, items)
for id, feed_url in get_feed_list():
t = threading.Thread(target=thread, kwargs={"id":id, "feed_url":feed_url})
t.start()
while threading.activeCount()> 1: time.sleep(1)

Problem: This is just going to create as many threads as there are feed items immediately and then wait for them to finish. This has the following issues:

If you try to request 1000+ pages at a time, many of them will time out. If a percentage of these are on the same server, you'll DoS it.
With 1000 threads your app will likely either run out of memory or get so bogged down in context switching it'll take forever
Try this on any consumer-grade router and it'll probably crash

So what do we do? Well, let's set a limit on the number of concurrent threads:

PLAIN TEXT

PYTHON:

import threading, functions, time
THREAD_LIMIT = 20
def thread(id, feed_url):
rss = functions.get_feed_contents(feed_url)
items = functions.parse_feed(rss)
functions.store_feed_items(id, items)
for id, feed_url in get_feed_list():
while threading.activeCount()> THREAD_LIMIT:
time.sleep(1)
t = threading.Thread(target=thread, kwargs={"id":id, "feed_url":feed_url})
t.start()
while threading.activeCount()> 1: time.sleep(1)

Spot the difference? We have another while loop right in the for loop. This is going to make our main thread code hang there while there are other threads still running.

There's another problem though, and that's with the model. In this mode, we're continually creating new threads that live for a short time, then exit. This isn't efficient. It would be much better to create a pool of threads which we can then re-use. Let's kick this up a notch.

Implementaton 3: Thread pool, and a Queue

So in this version we're going to do a few new things:

Use a Queue object and populate it with the list of urls.
Spawns a number of threads that will read items off this Queue.
The threads will process the data and store it.

I'll start off with the sample code then walk you through it:

PLAIN TEXT

PYTHON:

import threading, functions, time, Queue
THREAD_LIMIT = 50
jobs = Queue.Queue(0) # arg1 means "No item limit"
def thread():
while True: # forever
try:
id, feed_url = jobs.get(False) # arg1 means "Don't wait for items to appear"
except Queue.Empty:
# Nothing left to do, time to die
return
rss = functions.get_feed_contents(feed_url)
items = functions.parse_feed(rss)
functions.store_feed_items(id, items)
for info in get_feed_list():
Queue.put(info)
for n in xrange(THREAD_LIMIT):
t = threading.Thread(target=thread)
t.start()
while threading.activeCount()> 1: time.sleep(1) # Wait to finish

Lines to note:

1: We create the job list. We use a Queue class from the standard library, as it's threadsafe. In fact, this is exactly what it was designed for.
7-11: Read from the queue. The False parameter means that once the queue is empty, we're not interested any more. This raises the Queue.Empty exception, and we terminate the thread.
16: Put lots of info into the queue

Our threads run in a loop, performing work in lines 12-14, until there is no more work, then they exit. This model will work just fine for the majority of people, however, there are (still) problems. They are:

Opening 50 threads that write to a database will usually mean 50 database connections, or lots of locking. Either way, this is bad. Your data-getting threads don't want to be sat around waiting on a DB lock when they could be getting more information. Worse, you don't want to exceed your connection limit to the databse.
In this model, you have one master thread doing nothing and 50 threads doing I/O and work. This isn't a great idea in this application. Ideally you should only use threading (in Python) when you need to either:
1. Wait for I/O
2. Truly perform more than one concurrent task

Problem #2 isn't so serious, but it would be better to have more control over the heavy lifting. However problem #1 definitely needs addressing. The solution is to shift all processing inline to the master thread, which takes care of all the processing.

Implementation 4: 1 worker, many runners

Here's the code:

PLAIN TEXT

PYTHON:

import threading, functions, time, Queue
THREAD_LIMIT = 50
jobs = Queue.Queue(0) # arg1 means "No item limit"
rss_to_process = Queue.Queue(THREAD_LIMIT) # We set a limit on this, I'll
# explain later
def thread():
while True: # forever
try:
id, feed_url = jobs.get(False) # arg1 means "Don't wait for items
# to appear"
except Queue.Empty:
# Nothing left to do, time to die
return
rss = functions.get_feed_contents(feed_url)
rss_to_process.put((id, rss), True) # This will block if our processing
# queue is too large
for info in get_feed_list(): # Load them up
jobs.put(info)
for n in xrange(THREAD_LIMIT): # Unleash the hounds
t = threading.Thread(target=thread)
t.start()
while threading.activeCount()> 1 or not rss_to_process.empty():
# That condition means we want to do this loop if there are threads
# running OR there's stuff to process
try:
id, rss = rss_to_process.get(False, 1) # Wait for up to a second for a
# result
except Queue.Empty:
continue
items = functions.parse_feed(rss)
functions.store_feed_items(id, items)

Notes:

4: Here we have a new Queue to return the RSS XML to the master thread. We set a limit on the size of the queue in case the threads return data faster than we can process it, meaning the queue will fill up with XML, burning memory. This sets a rudimentary rate limit on the threads, as they will block when the queue is full.
15: This is the line in the thread that blocks when the processing queue is full.
25-34: This is where all the heavy lifting takes place, all inline as well.

...and there you have it, a fully fledged multithreaded data collector. Not bad for a few hours' work. It's not finished though, as there are plenty of things you'd want to add to it. For example:

More exception handling, especially KeyboardInterrupt and other signals. I would advise importing the signal module, then wrapping our final loop in a big try/except block, catching KeyboardInterrupt. This would need to empty the job and processing queues, which will cause your threads to exit gracefully, and then your program will exit too.
Better support for multi-core machines. The Python global interpreter lock prevents more than one thread from running at a time, however you can still spread your threads over multiple cores (I believe). What you might want to consider is throwing a few os.fork() calls in, forcing your program to multiprocess, which could then take full advantage of multiple cores. You can do this by either dividing up your work queue at the start, or by moving the heavy lifting out of the main thread and into seperate processes. Your main thread could then communicate with these "worker" processes via shared memory or sockets (my preference) then pass back the results. Make sure your functions.parse_feed can produce picklable objects.
You could use GUI programming or ncurses to provide a progress bar, by inspecting the value of jobs.qsize()

How to ensure_ascii=False when using JSON dumps()

2007-01-05T12:41:00.000-08:00

simplejson is very well written, but there's a catch. If you're doing UTF-8, make sure you ensure_ascii=False whenever calling dumps(). I don't know what it is with it that it will give you funky \uSOMETHING sequences for every non-ascii byte, and although that may display
right in HTML/JS, you should want to keep raw UTF-8 for better interoperability (specially with other JSON parsers).

Fix autonumbering sequences when backing a single table in postgres

2007-01-04T19:03:00.000-08:00

yea, the sequences are never really captured on a database dump of just one table, need to do an entire backup.
in general, when i want to move a 1 table, i use the \d table_name;
then i write / copy the structure,
then create it with another psql to other db.

in general, when i want to move a 1 table, i use the \d table_name;
then i write / copy the structure,
then create it with another psql to other db.

So to fix id problems when backing a table :
select max (id) from table;

alter sequence my_sequence restart maxid+1;

the thing about a table is in the general can have (constraints, such as primary keys, foreign keys, that reference other tables), indexes(for finding data faster), triggers (calls a stored procedure), and rules (that rewrite operations based on data, like triggers)
but most people just have tables, maybe with a primary key default nextval('a_sequnce'), so it is a lot simpler
so in psql
\d tablename
dumps the structure of the table to the text console
---------
well like here
\d task
Table "task.task"
Column | Type | Modifiers
----------------+-------------------+---------------------------------------------------
id | bigint | not null default nextval('task_id_seq'::regclass)
entry_date | date |
completed_date | date |
project_id | bigint |
name | character varying |
description | text |
parent_id | bigint |
Indexes:
"task_pk" PRIMARY KEY, btree (id)
Foreign-key constraints:
"_parent_id" FOREIGN KEY (parent_id) REFERENCES task(id)
"_project_id" FOREIGN KEY (project_id) REFERENCES project(id)
this shows me the table "task" contains id, entry_date, completed_date,,...
and their types, an then i see the constriants, like what foreign key it references, so i know that this table depends on another table.
i then have a couple minutes in a text editor and make this into
CREATE TABLE TASK (ID BIGINT NOT NULL DEFAULT NEXTVAL('task_id_seq'),
entry_date date,
etc.
then use that to create teh table in new db.
i guess it is hte manual old-school way to d it.
then when the new table exists,
(in both new, and old databases,)
i can type
psql -h host1 -U user1 -c "copy task to stdout csv" | psql -h host2 -U user2 -c "copy task from stdin csv"
or i guess you can do a copy out to a file first, and then copy into the second db by cat the file.
like how you would install that country data, i emailed to you.
for more info on the copy command, "\h copy" from the psql prompt
lol, see, thats why i never use a "GUI", becuse the command line is always better than any GUI.
so then after i get the table created, i do the create sequence task_id_seq start nnnnn;
actually, if you ran the sql propertly, it doesnt let you create the table without having the sequence ther..
so i would then have the sequence manually created, and to start at next higher value.

DISTINT 300ms grouby 1300ms for 10 entries

2006-12-30T23:30:00.001-08:00

recentlycommentedposts = web.query('SELECT DISTINCT ON(comments.postid) comments.postid, comments.comment, posts.title, posts.id FROM comments JOIN posts on posts.id=comments.postid order by comments.postid DESC limit 10')
recentlycommentedposts = web.query('''select postid, max(created) as created
from comments
group by postid
order by created desc
limit 10;''')

select p.id, p.title, p.created as post_date, c.comment, a.created as comment_date
from posts p,
comments c,
(select postid, max(created) as created
from comments
group by postid
order by created desc
limit 10) a
where
p.id = a.postid
and c.postid = a.postid
and c.created = a.created
order by post_date desc;

"htpasswd" and /etc/httpd/conf/httpd.conf

2006-12-30T17:40:00.001-08:00

there is probably a
so anywhere after athat
after that change, we need to make the /www/svn_new/main/auth.conf file
the "htpasswd" commmand does this
from shell,
cd /www/svn_new/main/
htpasswd -c auth.conf travis
(-c creates if not exists)
then to add future users, for example,
htpasswd auth.conf travis
5:27:38 pm
mark
htpasswd -c /etc/httpd/repo_passwd user

Postgresql VIEWs will save your day

2006-12-30T14:14:00.001-08:00

oh yea, thats right mysql sucks ass for the complex queries, like even
select * from users where company_id in (select id from company where company_name='foo') ;
is that a join
Travis :
yea, it could be done with a join too
the above was running the output of one select into the input of a second.
:
i m doing a double join
i think it is super slow
2:04:33 pm
Travis
a join would be
select user.*
from users u, company c
where u.company_id = c.id
and c.company_name='foo'
i found with postgresql, if i was always running this sort of query
then i create a view
===================================
create or replace view v_company_users as
select user.* , c.company_name
from users u, company c
where u.company_id = c.id;
2:05:34 pm
mark
wat os a voew
2:05:40 pm
Travis
then my app does
select * from v_company_uses where company='foo'
so, instead of doing a join query and specifying parameters, create a view, and the view barfs out the values and you query the where on the view.

Rebuilding large GIN indexes

2006-12-30T05:44:00.000-08:00

Rebuilding large GIN indexes

takes time. A lot of time. With rebuilding, I mean when you for example have to UPDATE all your tsvector fields due to a change in tsearch2 configuration. (Yes, I had to do that because I had a slightly incorrect tsearch2 configuration for the archives search database). So don't do it. Instead use the fact that PostgreSQL has nice transactional DDL and do something like this:



BEGIN TRANSACTION;

CREATE TABLE messages_new AS SELECT id,txt,to_tsvector(txt) AS fti

 FROM messages;

CREATE INDEX messages_new_fti ON messages_new USING gin(fti);

ANALYZE messags_new;

ALTER TABLE messages RENAME TO messages_old;

ALTER TABLE messages_new RENAME TO messages;

COMMIT;

DROP TABLE messages_old;

ALTER INDEX messages_new_fti RENAME TO messages_fti;

(apologies for any typos, I didn't bother to actually type these commands into the database again, and I lost my cut-and-paste of what I ran)

This way, the messages table can still serve up searches without any disruption to the searches at all. And creating the new index is a lot faster than updating the existing one if you have to touch all rows.

Getting random rows from a database table

2006-12-30T05:00:00.001-08:00

Getting random rows from a database table

Selecting random rows from a table in your database is generally useful for two things: grabbing one or more rows to display and/or use somehow, and for selecting a random subset of your rows and performing some sort of statistical analysis on the data. While the standard way of using ORDER BY RANDOM() is occassionally useful, it is very slow, it is non-repeatable, and it does not scale well. I'll demonstrate some better methods to get random rows.

For this article, I'll be using a table named mydata which contains ten million rows of data, and a primary key named id, which is of type bigint.

First, it's important to distinguish between random and unordered. If you simply pull rows from your table without an ORDER BY clause, they may appear random, but they are not: they are simply in an undefined order. In PostgreSQL, they will be roughly in an order related to the last time they were updated or inserted. However, by "random" we really mean that any row in the table has as much chance as appearing as another row within our SELECT statement. We'll need some way to accomplish this is SQL.

ORDER BY RANDOM()

How do we get something "random" into our query? Every modern computer has some way of generating a random number, and PostgreSQL has a way as well: the built in RANDOM() function, which generates a double precision number from from 0.0 to 1.0:

SELECT RANDOM() FROM generate_series(1,5);

Running the above yields something like this:

        random
-------------------
739388620946556
706028273329139
150622034911066
396196706686169
412912936415523

(Note the use of the nifty new generate_series() function to repeat a SQL command a certain number of times).

PostgreSQL also allows you to use RANDOM() in the ORDER BY clause, which is one way to get a random row from the database. Let's pull out three random values from our test table:

SELECT id FROM mydata ORDER BY RANDOM() LIMIT 3;

    id
---------
 3635389
 9417084
 8385175

This appears to work just fine, but it has a major drawback - it does not scale, and gets extremely slow as the table size increases. This is a consequence of how ORDER BY RANDOM() works - it basically assigns a random number to every row in the database, then orders the entire table by the random numbers, and then returns the rows you want. For small tables, this is not much of a problem, but this is a terrible solution as the tables grow in size. Here's a breakdown on speeds on my system for grabbing a single row by using the query SELECT id FROM mydata ORDER BY RANDOM() LIMIT 1:

Number of rows	Time to run
One thousand (1000)	3 milliseconds
Ten thousand (10,000)	40 milliseconds
One hundred thousand (100,000)	Half a second
One million (1,000,000)	7 seconds
Ten million (10,000,000)	149 seconds

Fortunately, there are much better ways to obtain random rows. There are two basic approaches to take - we can pick randomly from a range of values, or we can store a random number inside the table itself.

Range of Values

Let's keep using our mydata table, which has a primary key of id. If we know enough information about a column in the database, we can use that to get random rows by picking random values of that column. In this example, all we need to know is the minimum and maximum value of the id column, and we can have an external program generate a random number between the minimum and the maximum and put it into a query:

SELECT * FROM mydata WHERE id = '4012341';

We can also have the database help us choose the number, if we know there are a maximum of 10 million ids:

SELECT * FROM mydata
WHERE id =
  (SELECT (RANDOM() * 10000000)::int OFFSET 0)
LIMIT 1;

Both run in under a second, as the primary key column id is indexed. (The use of OFFSET 0 is needed in the second query to force the planner to evaluate RANDOM() only one time).

There are a few problems with this approach, however. One obvious one is that the query above may fail if there are any "holes" in the range of numbers from min to max. Storing information about where the holes are is probably impractical, but we can get around it by finding the value that is closest to the random number we picked, like this:

SELECT * FROM mydata
WHERE id >= '4012341'
LIMIT 1;

While that query addresses the problem of holes, it has two additional problems: it does not guarantee that the same row is returned each time, and it sometimes runs very, very slow. Running an EXPLAIN plan shows us why the speed difference:

         QUERY PLAN
----------------------------------------
Limit  (cost=0.00..0.02 rows=1)
  ->  Seq Scan on mydata 
   (cost=0.00..223040.00 rows=9996117)
        Filter: (id >= 4012341::bigint)

The index is not being used. As a good rule of thumb, never use a LIMIT without an ORDER BY clause. Let's add one in, which will solve both of our problems. The index will be used, and the results will be predictable:

SELECT * FROM mydata
WHERE id >= '4012341'
ORDER BY id LIMIT 1;

This strategy of using ">= (value) ORDER BY (column) LIMIT 1" is one which we will us a lot from this point forward.

Another problem is that we are not guaranteed to get the number of rows that we want. For example:

SELECT * FROM mydata
WHERE id >= '9999999'
ORDER BY id LIMIT 100;

This will only return 2 rows since our sample data has a maximum id of ten million. There are two ways around this problem: you can re-run the query with a new random number until you get the number of random rows you need, or you can adjust your random number (or your table) to make sure that you always have at least that many. For example, if your data has 100 rows and you want to pull 10 of them at random, then make sure you never ask for an id of more than 90. Alternatively, you could "pad" your table with 10 extra rows, and then safely use the numbers 1-100.

There is one final problem: picking our own random values from range will not produce truly random rows unless the data is perfectly uniformly distributed. Consider a table with two rows and values of 1 and 10. Our strategy above would cause the 10 value to appear more often than the 1 value, which is not the randomness we are looking for. In addition to the holes, if the values are not unique, then the distribution may not be uniform, and we once again lack true randomness. We need a way to combine the true randomness of ORDER BY RANDOM() with the speed of a Range of Values.

Random Column

The final and best solution is to create a new column in your database that stores random values. The table can then be sorted by this column, and get back random rows in a fast, repeatable, and truly random way. What we are basically doing is emulating the effect of ORDER BY RANDOM(), which as you recall creates a random value for each row in the database. Let's apply it to our test table.

First, we create a new column to hold the random values. Since RANDOM() returns the type "double precision", we create a new column of that type. We'll name it myrand:

ALTER TABLE mydata ADD myrand DOUBLE PRECISION;

Now we can populate that row with a random number from 0.0 to 1.0:

BEGIN;
UPDATE mydata SET myrand = RANDOM();
COMMIT;

This does take a non-trivial amount of time to run (372 seconds to populate all ten million rows), but it is a one-time cost. Since we'll be hitting this column to generate our random rows, we should put an index on it as well. But before we do that, we have to also ensure that our results are reproducible. In other words, the same query should return the same exact rows. Something like this is not guaranteed to get the same 10 rows each time it is run:

SELECT * FROM mydata
ORDER BY myrand LIMIT 10;

Why? Because there is no unique constraint on the myrand column, and it is possible (especially with our 10 million row example table) that two myrand columns contain the same value. As another rule of thumb, always make sure your ORDER BY clause specifies a unique set of rows. Our primary key, "id", is unique, so that makes a good backup for when our myrand column happens to have the same value. Our new query becomes:

SELECT * FROM mydata
ORDER BY myrand, id LIMIT 10;

Now we can create the index, on both of the columns in that ORDER BY. For good measure, we'll analyze the table as well:

CREATE INDEX myrand_randomhelp ON mydata(myrand,id);

ANALYZE VERBOSE mydata;

Before the index was in place, the query to grab a random row took over 180 seconds. Now that it is in place, the query runs in less than 1 second (126 milliseconds).

So that's our basic "Random Column" strategy: assign each row a random number, make sure it is linked to another unique column, and make an index across both of them. This allows us to get fast, repeatable, and truly random rows. You can also ensure that new rows get a new random value automatically added to them by doing this:

ALTER TABLE mydata ALTER myrand
SET NOT NULL DEFAULT RANDOM();

If you don't care about repeatability, and simply want to grab a random row, you can do this:

SELECT * FROM mydata
WHERE myrand >= (SELECT RANDOM() OFFSET 0)
ORDER BY myrand ASC LIMIT 1;

The ORDER BY clause is needed to ensure that our index is used. Note that Postgres has no problem using our previous index we created on both columns, because we put the myrand column first inside of that index. The above query is basically what Wikipedia uses when you click on the "Random Page" link.

Another advantage to using a Random Column is that not are the results reproducible, they are resettable. Let's say that you are using this method to pull 100 random rows at time out of a table with 1000 rows for statistical analysis. You also want to make sure that you never use the same row more than once, so you use an OFFSET:

SELECT * FROM mydata ORDER BY myrand, id
  LIMIT 100 OFFSET 1;
SELECT * FROM mydata ORDER BY myrand, id
  LIMIT 100 OFFSET 100;
SELECT * FROM mydata ORDER BY myrand, id
  LIMIT 100 OFFSET 200;
etc... 

(Note: although offset starts at 0, we ignore the first column as OFFSET 100 is easier to read then OFFSET 99). At some point, you want to run some more tests, but you don't want the same grouping as before. In other words, you want to reshuffle the deck of cards. Simple enough, just assign new values to the 'myrand' column:

UPDATE mydata SET myrand = RANDOM();

The only drawback to the whole Random Column strategy is the time and effort it takes to set it up, and the additional disk space needed to handle the extra column. Because of the extra column, INSERTS and UPDATES may run slightly slower.

Here's a summary of the three strategies to grab some random rows from a table:

Technique	Pros	Cons	When to use
ORDER BY RANDOM()	Completely random No table changes needed Easy to append to existing queries	Very slow: does not scale Non-repeatable result sets	Quick ad-hoc queries and very small tables that will not grow large
Range of Values	Very fast Uses existing columns	Not truly random Must track minimum and maximum Hard to get desired number of rows Inserts can affect results	When data is very well-defined and stable (even then, be cautious)
Random Column	Very fast Truly random Reproducible Automatically maintained Resettable Column data type matches random() for easy use	Takes up disk space Initial setup cost Inserts may be slowed by the extra column and the default value	Whenever possible

index of max value in a list PYTHON

2006-12-27T15:39:00.000-08:00

the max function, it returns the maximum value in the
list rather than the index associated with that value.

How do I return the index?


l.index(max(l)

l.index(max(l)) will give you the index of the first occurrance of the
maximum.

m = max(l)
[ i for i,v in enumerate(l) if v==m ]

will give you a list of all indices where the max occurs. (Putting the
'max(l)' outside the list comprehension prevents it from being evaluated
for each loop element.)

autoVACUUM in postgres

2006-12-20T16:02:00.000-08:00

stats_start_collector = on
stats_row_level = on
autovacuum = on
autovacuum_naptime = 120

in postgresql.conf and
kill -1 the pid for postgres
using cat postmaster.pid

How to reload postgres config files without restarting db on the LIVE

2006-12-20T15:57:00.000-08:00

you can change it on the live service and kill -1 to make it take effect
we should not kill postgres right
yea, if you edit postmaster.conf and change max_connections=512 then it should not require stopping and starting .
cat postmaster.pid
database]$ cat postmaster.pid
32623
/work/database
database]$ cat postmaster.pid
32623
so pid 32623 is your postmaster pid f
so if you kill -1 32623
after you have done the changes to postgresql.conf file
that will make postmaster re-read the config file without shutting it down
i.e. existing connections dont get dropped.
ur sure it wont kill the db like last time
and how to check if it has read the new postgresql.conf
i guess if you tail -f the log file.
the active postgresql log file (in pg_log folder) will say "reloading.."

received SIGHUP, reloading configuration files
if you had a really bad typo , then the database would either ignore the changes, or safely shutitself down.

Fixing dbus problems with gaim

2006-12-19T20:55:00.000-08:00

rpm -ql dbus | grep lib
/lib/dbus-1.0
/lib/libdbus-1.so.3
/lib/libdbus-1.so.3.2.0
/var/lib/dbus
cd /lib/
sudo ln -s libdbus-1.so.3.2.0 libdbus-1.so.2
sudo ldconfig
gaim

vi tab retab --- space to tab -- tab to space

2006-12-17T20:56:00.001-08:00

:set tabstop=4 " Force tabs to be displayed/expanded to 4 spaces (instead of default 8).
:set softtabstop=4 " Make Vim treat key as 4 spaces, but respect hard Tabs.
: " I don't think this one will do what you want.
:set expandtab " Turn Tab keypresses into spaces. Sounds like this is happening to you.
" You can still insert real Tabs as [Ctrl]-V [Tab].
:set noexpandtab " Leave Tab keys as real tabs (ASCII 9 character).
:1,$retab! " Convert all tabs to space or ASCII-9 (per "expandtab"),
" on lines 1_to_end-of-file.
:set shiftwidth=4 " When auto-indenting, indent by this much.
" (Use spaces/tabs per "expandtab".)
:help tabstop " Find out more about this stuff.
:help vimrc " Find out more about .vimrc/_vimrc :-)

Make python use UTF8 instead of ascii

2006-12-14T13:04:00.000-08:00

cd /usr/lib/python2.3/site-packages/
vi sitecustomize.py

(add the following lines to the file, save it)
import sys, codecs
sys.setdefaultencoding('utf-8')

from
http://plone.org/products/cmfcontentpanels/issues/1

OSCON 2005 PostgreSQL Presentations

2006-12-11T00:42:00.001-08:00

The OSCON 2005 PostgreSQL Presentations are up, but a bunch of them are in OpenOffice format. I went through the pain of installing OpenOffice to convert them to PDF. For completeness, I have also included the presentations that were already available as PDF.

Chris Browne:

Joe Conway:

Terrabytes of Business Intelligence

Lance Obermeyer:

Running PostgreSQL On Windows

Bruce Momjian:

Aaron Thul:

PostgreSQL Built Your Car

Robert Treat:

Explaining Explain

How I replace out divs in inserts to keep the layout

2006-12-10T22:12:00.000-08:00

i.Description =re.sub(r'<.div[^>]*?>', '', i.Description)#replaces div id tags

When div when span

2006-12-05T22:04:00.001-08:00

DIV is an arbitrary *block* element. It can contain other block
elements (including other DIVs). P is block element for
paragraphs. P cannot contain other block elements; it cannot
contain other Ps; it cannot contain DIVs. DIV does not create a
new P. But DIV will close any P that is open.
[color=blue]
> I tend to use SPAN because it does not generate any line break.[/color]

SPAN is an *inline* element. It cannot contain a block element.
SPAN is closed whenever the block containing it is closed.

Faster Inserts with PostgreSQL

2006-12-03T20:33:00.000-08:00

Faster Inserts with PostgreSQL

As I mentioned yesterday, I'm working on optimizing lots of inserts into a database, and I need solutions for different DB servers. Today I have been working with PostgreSQL. Although the PostgreSQL COPY command is recommended, I can't seam to get it to work with ColdFusion, and cfquery. I've tried lots of different ways of doing it.

Fast Insert Solution on PostgreSQL:

So far the fastest solution I have come up with is using PREPARE to create a temporary prepared statement (yes, I'm aware of cfqueryparam, and this method inserts with the highest speed). So here's how you do it:

PREPARE preparedInsert (int, varchar) AS
 INSERT INTO tableName (intColumn, charColumn)
 VALUES ($1, $2);
EXECUTE preparedInsert (1,'a');
EXECUTE preparedInsert (2,'b');
EXECUTE preparedInsert (3,'c');
DEALLOCATE preparedInsert;

Your basically creating a function that allows you to pass variables to your insert statement. Inside the first set of parenthesis you list the types of your variables, then variables are referred to as $1, $3, etc. inside the statement.

Next you can EXECUTE the statement as many times as you need to (this can all be done inside one SQL statement, inside one cfquery tag).

Finally when I'm done, I DEALLOCATE the function, otherwise if you try to PREPARE a statement named preparedInsert again during the same connection session you will get an error.

Experimenting with functional python

2006-11-20T03:53:00.001-08:00

http://www.bluebits.gr/weblog/programming/experimenting-with-functional-python-2006-07-26-20-11.html

Building Web Pages with Python

2006-11-20T03:53:00.000-08:00

http://adminspotting.net/building-web-pages-with-python/

Default python encoding to unicode

2006-10-10T20:49:00.000-07:00

In file
vim /usr/lib/python2.4/site.py
Comment out line 352 and add 353

352 #encoding = "ascii" # Default value set by _PyUnicode_Init()
353 encoding = "utf8"

to check

ipython
import sys
sys.getdefaultencoding()
Out[2]: 'utf8'

cgi programming with python

2006-10-09T02:44:00.000-07:00

Take a look at Guido's examples here...

http://www.python.org/doc/essays/ppt/sd99east/sld057.htm

Best wishes,

How to disable system beep in windows

2006-09-29T22:28:00.000-07:00