Databases for simple web development

I have log been a fan of PostgreSQL over MySQL, believe that PostgreSQL is more feature complete and generally as fast or faster, with obvious caveats about being used appropriately, of course, and not to mention no real comparative testing. Every body gets to have an untested opinion, right?

I did end up doing some performance testing though. What I learned is that both are reasonable fast at simple queries. Great. However opening a new connection to MySQL is much faster than opening a new connection to PostgreSQL. Once the connection is open, but seem equally fast for very simple tests.

Why this matters though is that simple web development in many languages with the most common tools don’t do connection pooling. If you want to just whip up an example PHP program using mod_php, then every page load will result in a new connection. The same goes for mod_python or mod_wsgi (as well as frameworks sitting on top of those plugins). Using each of these common tools with PostgreSQL results in a slow web site. This was driven home when I upgraded from a single 550mhz UltraSPARC II to a dual 1.1Ghz UltraSPARC3 III, and still certain web apps I’ve been tinkering with writing using PostgreSQL for the database are slow.

Certainly there are ways around this. Using a database connection pooling tool for starters, would certainly cure the problem. Also, choosing something that keeps your script running (or a least your database connections open) would also help. Or even writing your application as stand alone program that keeps the database connections open and talks to the web server via JSON-RPC or XML-RPC. But, to quickly whip something out MySQL may be simpler.

Of course, for some applications Sqlite could be a contender. Certainly it is very fast, and very simple to use. For a scalable web site though, it is probably out of the question. There is a reason that Django defaults to using Sqlite first though. And there are also, those less traditional database servers like CouchDB or memcachedb which seem to generally have very fast connection times.

This is a bit disappointing though. AOLServer used to offer connection pooling built into the web server. Of course, I certainly don’t want to use TCL as my development language, but still that would be nice to have.

Meanwhile, can anyone suggest a good Solaris and PostgreSQL connection pooling library?

Making Miro work with USB sound devices on Ubuntu

On Ubuntu (and possibly other linux distributions) Miro refuses to work with a secondary sound card, it will only work with the primary one despite what the ALSA default is set to, unlike most programs which offer some way to override the default.

Potentially, the second sound card in question could be a PCI card or something else, but based on other people’s experience (like my own) it is usually a USB sound card that is causing trouble. See here (note, the suggested fix didn’t work for me, just like it didn’t the original poster there) and here (they mention fixing it in the trunk, but that doesn’t help me until a new release comes out).

Some people actively want both the onboard sound and the USB or PCI device working, but if you are willing to sacrifice on-board sound, I found a work around. In my case, the on-board sound is worthless. It has some terrible humming/buzzing in the background so I never ever want to use it again.

The solution is to find what the module is that supplies your on-board sound. In my case, the on-board sound is a VT8233, so when I looked at the output from lsmod, it was obvious that the module for this sound device was the snd_via82xx module.

Then, open the /etc/modprobe.d/blacklist file to edit it:
sudo pico /etc/modprobe.d/blacklist
and add the line:
blacklist snd_via82xx
Then reboot.

Now, the USB audio device will be the first audio device.

A few Solaris 10 notes

Actually, these are primarily Solaris 11 notes, but they will probably all apply to Solaris 10 when the next release comes out, which I understand to be scheduled for sometime later this month.

First, recently a lot of SCSI hard drives I’ve gotten have been a little mysterious about being used by the Solaris installer and have looked a little odd in format. It turns out that they’ve been EFI labeled drives. Since Solaris understands EFI labelling, it doesn’t just suggest you relabel the drive and be done with it. However, despite Solaris understanding EFI, it refuses to boot or install from EFI on SPARC hardware. The trick has been to get a prompt, then use “format -e”. Then when you choose the label command, it will ask you about a SMI or a EFI label. Choose the SMI option. If you are going to choose to do a ZFS root, then the partitioning doesn’t matter.

After fixing the disk, you are ready to install. The ZFS boot option is only offered on very new copies of Solaris (2008/05 maybe, Solaris Express build 98 or maybe slightly older definately). However, you only get the choice from the text installer. If you are installing over the serial console, then no problem, you get this by default. However, from a graphical console, you will need to use a boot parameter. Thus, you boot command will look something like this: “boot cdrom – text” or “boot net – text”. Using – nowin instead may be faster.

When you get to the ZFS option, just choose it and away you go. You can choose to name the pool something other than rpool, but there is no need to.

If you want a mirrored root, it is easy to add the second disk later. First, when you install to a ZFS root, it repartitions the root drive and uses a slice (parition) instead of the whole disk (even though the slice fills the entire disk). You will need to partition the second disk identically. Just look at the partition map if the first disk in format, then copy it over to the second disk. Then from a root prompt, type something like “zpool attach rpool c0t0d0s0 c0t1d0s0”, assuming that c0t0d0 and c0t1d0 are the two disks in question (which is a good guess on a lot of two disk Sun systems). The mirror is now made, but it may take awhile to sync up in the background, and the machine may run slowly until it is done. Check the progress with “zpool status”.

To be able to do a fallback boot to the second disk will require rebooting and going back out to the OpenBoot ok prompt. But before that, you will need to make the second disk bootable with this command: “installboot -F zfs /usr/platform/`uname -i`/lib/fs/zfs/bootblk /dev/rdsk/c0t1d0s0”
Finally, before you head to the OK prompt, you will want to find the openboot device paths for each disk. Do “ls -l /dev/dsk/c0t0d0s0 /dev/dsk/c0t1d0s0”. This will show you something like:

lrwxrwxrwx 1 root root 41 Oct 1 21:02 /dev/dsk/c0t0d0s0 -> ../../devices/pci@1f,4000/scsi@3/sd@0,0:a
lrwxrwxrwx 1 root root 41 Oct 1 22:57 /dev/dsk/c0t1d0s0 -> ../../devices/pci@1f,4000/scsi@3/sd@1,0:a

Write down the target of the symlinks, the part after the ../../devices, changing the sd’s to disk’s, and get rid of the :a’s.

Now reboot and Stop-A to an ok prompt. If your second disks isn’t where the second disk normally will be, you will need to create a devalias for it. Assuming that you used the c0t0d0 and c0t1d0, then you can just do this:
setenv boot-device disk disk2

If you need to change the disk and disk2 aliases (or want to create new names), use the nvalias command from the ok prompt. See the man page for more detailed operation though.

Flash on Ubuntu 8.04 AMD64

I run Ubuntu 8.04 AM64 on a laptop at work.  I’ve been doing this since Ubuntu 6.10.  This has not been a smooth ride. Ubuntu 6.10 i386 on my old laptop (I only “upgraded” because the old one was stolen from the plane on a busines trip) worked flawlessly for me.  Things have gotten a bit better as upgrades came out, but I still can’t use the wireless  (BCM43 device of some sort, no native driver, ndiswrapper won’t play nice), for instance.

My first and biggest tip is to stay away from 64bit linux on the desktop or laptop, unless you know why you need it.  That is very unlikely to be the case on laptops.

Moving on, for the longest time Flash would not work.  When I tried to configure the nswrapper system, it would start (sometimes) then crash the plugin.  Maybe I could view one flash website before needing to restart, maybe no flash web sites.  I finally got Flash worked out, and that is the main point of this post.

The trick to make flash work was to first install the 32bit version of FF3 from the Mozilla web site.  Put it in a new location (I went with /usr/local/firefox), and put that location in your path before /usr/bin.  For this to run, you will need ia32-libs installed.

Step 2 then is to go to the Adobe web and download the Flash 9 .tar.gz.  Don’t try to use the autodiscovery/autoinstall thing that Firefox will offer to do.  Extract the Flash 9 installer to a temporary directory, then copy the file libflashplayer.so from the temporary directory to the plugins directory (/usr/local/firefox/plugins for me since that is where extracted the 32 bit firefox from the mozilla web site).  Now, when you restart firefox, you will be using the 32bit only Flash with a 32bit version of firefox, and everything will work happily.

I think that in general, Linux doesn’t handle the 64bit transition as well as Solaris or Irix did.  As far as I can tell, Flash is 32 bit only on all platforms.  However on Solaris and Irix, 32bit versions of firefox or Mozilla are supplied, even though they are running on 64bit hardware.  Also, there seems to be a lot more of defaulting to 32bit unless specified otherwise, which is often reasonable.  And the culter seems to do a better job with supplying both 32bit and 64bit versions of libraries.

About ReferURL

ReferURL.net is a link shorting service I created. You paste in a long URL (say to an eBay auction or newpaper article) and it gives you a short URL to use (http://referurl.net/123). You also have the option of picking an alias for a referurl, something like http://referurl.net/r/xxx. Also, a common usage pattern is a bookmarklet that you drag from the page to the toolbar. Whenever you click on the bookmarklet, it runs a bit of javascript code that submits the page you are currently looking at to ReferURL.

URL shortening services are great for emailing URLs to friends. Recently they are even more important for posting URLs on twitter (with only 140 characters, every character saved counts).

A service that does similar things called TinyURL.com has been around for a long time. Personally, I do not like tinyurl.com. I think it is ugly. There is another reason I remember disliking them, but it is possible that I have two services confused, so I won’t mention it. They also didn’t offer aliases when I wrote ReferURL.

I used to use another service, but it broke repeatedly, then when it had several months of downtime I decided to write my own. That service also didn’t support aliases.

At this point when I look around the new services that are similar, I see three things that may be better than ReferURL.

  1. Some services are prettier (of course, extra graphics means slower load times).
  2. Some services put the new shorter url into your clipboard buffer so that you don’t have to copy it yourself. I would love to add this, but as far as I can tell it is implemented with Flash, which I don’t own.
  3. With twitter, every character counts. There are now some services with names much shorter than referurl.net. TinyURL.com is one character shorter. Bit.ly is six characters shorter. If any one has an good idea for a name that is shorter than referurl.net, I would love to steal it. In my own twitter usage, I haven’t had trouble with the length of ReferURL yet though.

Anyway, those are my comments on the creation of ReferURL.net. For the time being, I plan to keep looking for ways to improve it and will keep working on it.

Also, I will be releasing the code for people who want to run/write their own service in the future. I had previously released some code, but now that it is several months old, I took it down until I had time to clean the current code for re-release. If someone were to email me asking about that, it would probably get me to do it sooner.  It is a Python project build on mod_python and PostgreSQL.

SunPCI on Solaris 9

I just imported this page because I don’t want to forget the information. If you don’t know why you need to read this, then feel free to ignore it.

About

The SunPCI is a 400mhz AMD k62 card for running Windows in a PCI
Sun. Never versions of the card are the SunPCI-II and the SunPCI-III,
both of which use higher speed Celerons.

Software

The original SunPCI was dropped after version 1.3 of the SunPCI
software (both 1.3 and newer versions are obtainable from Sun’s web
site, registration required). Version 1.3 of the SunPCI software isn’t
supported on Solaris’ newer than 8. Thus this page on making it work on
Solaris 9.

I doubt that it is possible to use this card with Solaris 10.

Important Requirement (that wasn’t immediately obvious to me)

One requirement that the software has is that it can only display on
the Sun X server. This means that you need to either need a framebuffer
in the same machine as the SunPCI, or you need to be remotely displaying
the software on another Solaris machine running the Sun X server.
Thankfully, framebuffers for almost every machine are rather cheap.

Presumably if you still want remote display on a different X server
(say, Xsgi, which is what I want), you could use VNC or Remote Desktop
on the Windows system. You still would need a graphical console to
run the SunPCI, but this could be a case of attaching a monitor to a
Sun in the basement to start the software, then using RDesktop from
your office to operate the Windows session.

When I started trying to set this up, I had only headless Sun machines, and I thought I could run this on one of them.  Oops.

Procedure

Install the software as normal. It will fail trying to run the
postinstall script, complaining about “/etc/devlink.tab not updated
correctly”. The first thing to do is to:

  • cd /opt/SUNWspci/drivers/solaris
  • ln -s /opt/SUNWspci/drivers/solaris/sunpcidrv.280 sunpcidrv.290
  • ln -s /opt/SUNWspci/drivers/solaris/sunpcidrv.280.64 sunpcidrv.290.64

You will also need to make sure your OBP is new enough. I don’t know
what is required exactly. Perhaps 3.11. 3.9 seems to be rather common
and is too old. 3.27 seems to be the latest, and likely last version.
New versions can be downloaded from sunsolve.

Additional notes

Google Groups article with some additional notes that I didn’t require.

Also, from a german site, I got this:

d) If SunPCi still gives you trouble, edit:
~/pc/SunPC.ini
and add...
[Display]
OldText

I haven’t needed it either.

Mail Configuration with Postfix and Dovecot

(NOTE in 2011: This one post attracts enormous amounts of spam comments, so I’m closing comments on it, while still leaving comments on elsewhere.)

Notes on configuring a SMTP/IMAP server with TLS, SSL, and SMTP AUTH, the least pain possible.

The platform used was Solaris 10 with SunStudio 11. The notes should apply to any reasonably recent unix-like operating system, although Linux users in particular may find it easier to just install the setup from their prefered package management system. I will note any place that is likely to contain Solaris specific stuff.

The main pieces used are Postfix and Dovecot.

A quick note: TLS is basically SSL

About SASL

Postfix requires an external SASL library to use for SMTP AUTH. The normal choice is Cyrus SASL, which has been around for some time. Cyrus SASL is difficult to build on Solaris with SunStudio, and is generally a much hated library.

Starting with Dovecot 1.0, Dovecot now has it’s own SASL library. This is much easier to use. The only downside is that when doing Postfix to Postfix SMTP AUTH connections, the client copy of Postfix can’t use Dovecot SASL. If your installation of Postfix is always going to be a SMTP AUTH server and never a client, then please use Dovecot SASL.

Dovecot

Compile with:

./configure --prefix=/opt/dovecot --with-pgsql --with-gssapi \
          --with-ssl=openssl --with-gc

gssapi still doesn’t seem to work for me, and I’m not actually using pgsql, so I could have left it off as well.

 

At this point, all you have to do is edit the dovecot.conf file. I don’t have anything but plain authentication working correctly. I can get CRAM-MD5 working, but then plain won’t work, at least with mutt. I make up for this by requiring the non-localhost imap connections use SSL, and by later setting postfix to only allow SMTP AUTH over TLS. I accomplish the imap/SSL requirement with a firewall rule.

Find the commented protocols line and uncomment, set to “protocols = imap imaps”. This disables POP usage. Add pop and pops if you wish.

Set the ssl_cert_file and ssl_key_file lines in your dovecot.conf file. Search elsewhere for a guide on creating the key. I reused the same key that I created for apache/ssl usage.

Set the mail_location line to “mail_location = maildir:~/Maildir”.

Adjust the first_valid_uid line, if needed. I set mine to: “first_valid_uid = 100”.

Find the “auth default” section. Change the mechanisms line to: “mechanisms = plain login”. In the passwd pam { second, find the commented out args line, and change it to “args = /etc/pam.conf”.

Still in the auth default section, find the commented out “socket listen” section. Uncomment the first line to: “socket listen {“. Then scroll down to the client section, and uncomment the non-descriptive lines and change to:

    client {
      # The client socket is generally safe to export to everyone. Typical use
      # is to export it to your SMTP server so it can do SMTP AUTH lookups     
      # using it.                                                              
      path = /var/spool/postfix/private/auth
      user = postfix
      group = postfix
      mode = 0660
    }

 

Start up dovecot. Under Solaris 9 or linux, I added it to the rc.local file (on some systems I had to create a rc.local system to do so). On Solaris 10, find a manifest and method here and here.. To install these files, copy the manifest to /var/svc/manifest/site and the method to /lib/svc/method and install them into SMF with the command:

svccfg import /var/svc/manifest/site/imap-dovecot.xml

 

Procmail

Very simple. In the expanded source directory, “./configure && make && sudo make install”.

Postfix

To build:

make makefiles CCARGS='-DUSE_SASL_AUTH -DDEF_SERVER_SASL_TYPE=\"dovecot\" -DUSE_TLS \
   -I/usr/local/ssl/include' \
   AUXLIBS="-L/usr/local/ssl/lib -R/usr/local/ssl/lib -lssl -lcrypto" \
   CC=/opt/SUNWspro/bin/cc

Non-solaris users can omit the -R option, and the CC option.

 

The primary options that need set are to append to the end:

smtpd_sasl_type = dovecot
smtpd_sasl_path = private/auth
smtpd_sasl_auth_enable = yes
smtpd_tls_auth_only = yes
smtpd_tls_cert_file = /etc/ssl/your.crt
smtpd_tls_key_file = /etc/ssl/your.key
smtpd_tls_loglevel  = 1
smtpd_tls_received_header = yes
smtpd_tls_security_level = may

 

Additionally, find the mailbox_command section and set:

smtpd_sasl_type = dovecot
smtpd_sasl_path = private/auth
smtpd_sasl_auth_enable = yes
smtpd_tls_auth_only = yes
smtpd_tls_cert_file = /etc/ssl/your.crt
smtpd_tls_key_file = /etc/ssl/your.key
smtpd_tls_loglevel  = 1
smtpd_tls_received_header = yes
smtpd_tls_security_level = may

smtpd_recipient_restrictions =
   permit_sasl_authenticated,
   permit_mynetworks,
   reject_unauth_destination

 

Additionally, find the mailbox_command section, and set:

mailbox_command = /usr/bin/procmail -a "$EXTENSION" DEFAULT=$HOME/Maildir/ MAILDIR=$HOME/Maildir

 

Also set: myhostname, mydomain, mydestination.

Follow other guides for setting up things like spam checking, or mailman.

PostgreSQL Notes

I don’t use Postgres quite heavily enough to remeber how to do certain administrative tasks. Having gotten sick of flipping back and forth between various sections in the manual, I’m making a page of notes here.

Anything here is probably permenantly burned in the brains of heavier users, but perhaps this will be useful to other light users.

General

Postgres generally runs as a dedicated user, and that dedicated user probably has complete control over adding roles and dbs.

To use postgres as a specific user, “psql -U username” is easier than su’ing to that user (and the postgres user you want may not be a system user anyway.

Creating a DB

createdb -U postgresuser newdbname

Adding a User

Users are just roles that can login.

createuser -P newusername

The -P flag is to tell it to set a password. It is possible to have accounts without passwords.

To see the users, from inside psql, do \du.

Assigning the DB to a user

This is night the same as granting rights to users (see the GRANT command), although the DB owner does have full rights in the DB.

alter database dbname owner to username;