I have yet to try running this for anything, but I do appreciate how it really sticks to the "do one thing well" ethos. Modern web servers can be extremely complicated with a lot of moving parts. This boils it down to just one thing and lets a person focus on the project instead of the infrastructure. Granted, it's very simplistic, but that's its strength.
That the sqlite website is able to run this way is more a testament to Linux's work on a lightweight/fast fork() than anything else. This would perform terribly on a more traditional Unix.