Uncategorized- gnegg

PostgreSQL vs. MySQL – a subjective view

Still quite enthusiastic about my success with PostgreSQL erlier today and after reading the first comment on that entry, I think, it’s time for a little list describing the highlights why I prefer PostgreSQL to mySQL and another one describing what mySQL does better:

PostgreSQL

psql, the command line tool for accessing the database is much better than the mySQL pendant. What many don’t seem to know is x. Try it and you will ask yourself, why mysql can’t do that. Also, I really like that a pager is invoked when dealing with large result sets. MySQL does not do that either
The license. While I certailny prefer any free software license to any proprietary one, I much prefer the more free BSD one. But I better leave the flam^Wphilosophying about this to others…
All those “professional” database-features like VIEWs, stored procedures (which can even be written in Perl or Python), triggers, rules, enforced referential integrity and all that stuff. I could never ever imagine going back to a database without VIEWs. Those things are so incredibly useful both for much friendlier interface to complex data and integrating different pieces of software.
The community around PostgreSQL is very strong. Reading the “general” and “developers” mailinglists is very interesting and many times provides a very good insight in database design

Back in 2002 where I was working on the new adsl.ch, I used VIEWs to merge satisfy the needs both PostNuke and phpBB2 had concerning their table containing the user accounts. With a view and a little bit cusomized scripting I was able to integrate both without the need for any patching around in either of them which makes applying security-updates so much easier. This is where I deceided that I will never use anything else but PostgreSQL for my database needs.

The mySQL list

mysqli is an object oriented interface for PHP scripts directly built into the language (and thus fast). Too bad it requires MySQL 4.1 as Gentoo does not have fitting ebuilds yet. And don’t get me wrong: Postgres’ interface is not bad either.
Seems easier to handle. Just install and run. ALTER TABLE is much more powerful than in PostgreSQL, so changing the structure after the fact is easy. Nothing must be configured to get quite the optimum performance
Clustering built into the core of the database, though it’s still a master-slave replication which provides fail-safety, but no (real) load balancing.

ALTER TABLE in PostgreSQL 8 is about as powerful as the one of MySQL, but PostgreSQL 8 suffers from the same problem as MySQL 4.1: No Gentoo ebuild. Here, on my iMac I’m already running the latest BETA of 8.0

The decision to go with PostgreSQL is an easy one: None of the advantages of MySQL are big enough to outweigh the missing features. Oh and if you ask for benchmarks and tell me that PostgreSQL is slower than MySQL, let me tell you this: While I doubt that this statement is still true (mySQL got slower due to the transaction support and PostgreSQL got much faster), I can say one thing for certain: PostgreSQL is fast enough for my needs. What is it worth giving up data integrity and writing lots of dirty code that should really be stored directly in the database just because of a percent more performance or so?

Another thing is how those systems perform under high load. While I certainly know that PostgreSQL handles it well and stays fast for many more concurrent connections, I always hear problems form people using mySQL: Corrupted tables (sometimes beyond repair), hanging connections,… Nothing I want to happen to me even if it would mean to live with one or two percent less performance under unrealistical-benchmarky load.

Oh and everything I told about performance is quite un-scientific. While I did some load-tests with Postgres, all my expirience with MySQL under same conditions comes from other people. I never tried it myself. Why should I? PostgreSQL is perfect.

PostgreSQL rocks!

I told so before, but I have to again: PostgreSQL is incredibly cool.

Today I had this job of importing around 11’000’000 datasets distributed to 15 tables. 10 millions of them went into one big table. And after importing the whole thing should still respond fast to queries involving JOINS with this large table.

What surprises me: After a bit tweaking of the settings (one of them would be moving the beast to a partition where there’s enough space on to store the indexes ;-) ), the queries I did on a much smaller amount of data before, remain as fast as ever. PostgreSQL really makes great use of its indexes

Granted: Importing all those datasets was somewhat slow (I could and can not use COPY because I’m just receiving differences), but tweaking around with the indexes helped a lot (tip: drop them while inserting)

While processing the import, Postgres still was as responsive as ever while working with other parts of the database.

I know that all this is nothing fancy – I mean: I expect nothing less from a good RDBMS, but still… it’s amazing how good and flawless this worked and how fast it is.

Maybe, I could get faster INSERT/UPDATE performance if I’d be using MySQL instead, but I absolutely want to use all those features a real database should have that MySQL lacks: Views, referential integrity, subselects (still using 4.0 until Gentoo releases a more current ebuild).

Yes. Postgres is just great.

Just for your interest:

pilif@fangorn /home % sudo du -chs pgdata
1.8G    pgdata
1.8G    total

And it’s still as fast as your common little webboard-application. I still cannot quite believe it.

AirPort basesation and external DHCP server

Recently, I bought an airport basestation.

I wanted to use it as a NAT router and a wireless access point. DNS and DHCP I wanted to do via a fully-fledged BIND/dhcpd combination running on my iMac.

DNS I need because I’m doing some work for the office from home. As much of it is web based, I need virtual hosts on my server and I certainly don’t want to go back to stone age and move around hosts files. DNS was invented for something, so please, let me use it.

DHCP I wanted because sometimes, I’m using applications on my notebook that require some ports forwarded to them (bittorrent for example). Forwarding ports without fixed IP-adresses can be difficult (especially if changing the forwarding address requires a restart of the router), so I wanted the possibility to give the MAC-adress of my notebooks NIC a fixed IP-address. This is not possible with airports built-in DHCP server (and I don’t blame them for this – it’s quite a special feature)

Now, imagine how disappointed I was seing, that this is not possible when using Apples configuration program:

They tie NAT and DHCP together: Either you turn off both NAT and DHCP, NAT only, or none of them. Turning off DHCP only is not possible.

Looking around on the web, I came across Jon Sevys Java Based Configurator again.

With this tool my configuration certainly is possible:

Configure your basestation using Apples utility. Tell it to enable NAT and distribute IP-Adresses
Update the configuration and exit Apples utility.
Run the Java Based configurator.
On the “DHCP Functions”-Tab, unckeck the Checkbox
On the “Bridging Functions”-Tab uncheck “Disable bridging between Ethernet and wireless Lan”
Save the configuration.
The last step is important if you want the Basestation to continue working as an usable wireless access point. I forgot to do this the first time I tried and did not get an IP-Adress and could not connect to the wired lan after setting one manually either. Logical, but disturbing if you think you got the solution but it still does not work as expected…

AC3-Divx on my PPC

As I’ve written in the review of my hx4700 PDA, the thing really shines when it comes to displaying XViD videos.

The single big problem about Betaplayer is that it lacks support for decoding AC3-streams. This is bad, as most of my movies have an AC3 audio stream (always looking for the optimum quality). So I was on the lookout for a solution.

I quickly found PockedDivxEncode which comes with nice presets for encoding videos for a PocketPC

The problem was that the current version always insists to recoding the video stream when converting the video file, thus reducing the overall quality (it’s no use compressing two times) and needing a long time to do its job (about 50% realtime or slower. Haven’t tried).

Then, on the download-page I found this not-so-visible link to the the current beta test version, which has – under “Advanced Settings” an option to leave the video stream alone and just work on the audio stream.

Using this configuration, recoding just the AC3 stream becomes possible. As it’s leaving the video alone, it’s reasonably fast too – about 4 times realtime on my thinkpad.

This is a usable solution until Betaplayer gets AC3-Support.

Pile of new hardware

Last wednesday, I finally did what I have been talking since the very beginning of this blog: I bought myself a Mac. In the end, what lead me to the decision was that I wanted my home server back. I had some requirements to the new hardware:

It must run quietly. I don’t have enough space to designate a dedicated server-room so the server – if constantly running, must be quiet. This is where my older solution failed.
It must run a UNIX derivate. Much of the work I do requires a UNIX server. Having such a beast at home can save me from going to the office here and then.

The iMac (17inch, 1.8Ghz) I finally bought fullfills those two requirements (it’s quite quiet as long as I’m not doing anything calcualation intensive – which I’m not – at least not when sleeping) and has the additional benefit of a cool UI frontend.

So in the end, this was a logical decision: Had I deceided to go with a quiet Linux box, the parts alone would have been more expensive – not to speak of the time required to assemble the beast.

Setting up the iMac was easy (as I’ve expected). First, I wanted to go with Gentoo for MacOS X, but this is extremely under construction, so I went with Fink for my UNIX-needs

Now I’m running a DNS, DHCP, PostgreSQL and Apache Server. All I need to do my work.

So. After my UNIXish needs where fulfilled, there came the Macish ones: I wanted to video-iChat with my girfriend. This has proven to be quite a hassle to set up:

We never managed to get a working connection. I got timeouts everytime I tried to connect. A bit debugging around on my ZyWall router quickly determined it as the culprit: Despite me having configured the iMac as default NAT-Server, the device did not forward any UDP packets to the host. No wonder this does not work.

So it was time for my now nearly 4 years old ZyWall to be replaced (lately it began crashing quite often anyways). As I did not want to take any further risks, I bought an Airport Basesation. Works nicely – also with my other gear (PocketPC and Thinkpad).

Furthermore, it became clear to me that I finally have a continously running server in my home, so I finally could (at least somewhat) justify buying myself a Squeezebox. This device arrived only today and while I knew how great the thing is, it suprised me even more now that I had a look at it: So many settings to tweak and so great quality of the hardware. Very good.

In the end, the last week (ending today) was quite hardware-intensive:

The iMac
Two iSights (one for me, another for my girfriend). Speaking of iSight. I’ve just noticed that the iMac has a magnet in the middle of the screen to be used with the iSight magnet holder to position it nicely in the middle of the screen. Very nice
An Airport Extreme Basestation. Not that I wanted one, but the investement into the iSights would have been in vain as it’s technically impossible to video-chat over the ZyWall
The squeezebox

All in all quite a lot of junk, but so much fun to play with ;-)

Two years of gnegg.ch

Two years ago, I started to use my spare gnegg.ch domain with this weblog. My first posting was quite the ordinary welcome-posting. Even back then, I promised to create a better layout for the site, which I finally did this february:

As I am not-so-good™ with layout, I kept the default one of Movable Type, my blogging-engine. Maybe Richard will help me here sometime in the future.

And Richard did a really good job with it. Thanks again.

Many times, gnegg was lingering around a bit, but I managed to put myself together all the time and in the last two years, there was at least one post every month. Since around january 2004, post much more often. Currently I’ve nearly 200 postings on the site, wich means that I wrote the same amount of postings in 6 months that took me a year and a half before: My 100th post was only this march.

With the increased amount of postings, I also got more visits: 2003 there where and average of 115 visits a day producing 184 pageviews. Now it’s more like 552 visits producing 12883 pageviews. Tendency: rising. Thank you, my fellow readers, for this.

With gnegg.ch becoming more known, also the problems grew: Currently, I’m filtering about 50 SPAM comments per day. A year ago it was at most one per month.

Posting here still is a lot of fun and I’m certainly going to continue writing here.

And in case your wonder, what “gnegg” actually means: It’s nothing. In 2001 I created that word quite by accident by typing around the keyboard to create some blind text and it liked it so much that I reserved the domain… What I liked about the name was that I was quite uncommon in the internet so far. Ok. There’s this, but whatever it is, it’s funny anyway…

My hx4700

Last thursday, I’ve written about my iPAQ hx4700 and I’ve promised a more thorough review. Well, here it is:

First of all, I can’t understand all the moaning about the device being so big out there. Granted, it’s larger than most PDA’s, but much less bulky than all it’s predecessors (the last one I used being the iPAQ 5550). Also, it’s acutally lighter than the previous model.

And then there’s this plastic cover, all are complaining about. I can’t confirm that either. You can flip it around without problems or fear of breaking it. So, I actually think, the cover is quite great as it does not thicken the device while still providing excellent protection for the display.

Speaking of displays: This is where I absolutelty concur with the other reviewers: It’s great. Extremely great. And while I understand Microsofts intentions when they created this special-vga-mode (essentially you have the same amount of real estate on the screen as with the 320×240 resolution. It just looks better and more detailed), I got used to the extremely small look of the screen with SE_VGA enabled which is how I have my device currently configured.

SE_VGA turns off this pixel-duplication and provides real VGA resolution. Everything get’s quite smallish, but you know: I’m the resolution guy…

Unfortunatly, none of the programs currently out there are prepared for this extended VGA mode. The glitches range from too much whitespace over cut icons to quite unusable screens (iPAQ wireless, the communications center of the device, being such an example). All in all, the high resolution outweights those glitches for me.

Turning this mode on and off requires a warm reboot, unfortuntately

The eye-candy software HP is providing with the device is quite useless: The screensaver does not make sense to me (I don’t have pictures I would place on it and the status information provided on the today screen is more useful anyways) and the today-applet is unconfigurable and not really featureful, too.

Spb Pocket Plus is much more useful for that matter.

While HP provides a copy of Pocket Informant on the ROM, the version is already outdated. Updating is possible, but is a tedious process if you want to profit from the 50% rebate.

Really useful is this bluteooth phone configurator. Getting a GPRS-connection to work has never been easier. The phone and even the mobile provider got recognized and automatically configured.

What I extremely dislike about the device is that it has only 64 MB of RAM. This lead me to install all the applications to the iPAQ File Store which is about 100 MB large. While this is a solution, it has two problems:

Flash ROM is slower than RAM. Starting Mobile Agent (a GPS software) took long already when installed in RAM. Now imagine starting it from the ROM. We’re talking minutes here!
The software installation is semi-automatic as I must change the folder on every installation.

As software can easily be reinstalled even on power loss (and thus empty RAM), I don’t see any advanteage of the overly big ROM at the cost of more useful RAM.

Battery live is average. One day worth of heavy usage, most of it connected via WLAN and some via Bluetooth and GPRS (have I told you, how great this bluetooth phone tool is? I guess I have, but saying it again is the least I can do to emphasize how great it really is ;-) ) about brings the battery down. This is neither more nor less than I’d had expected.

What I really looked forward to (besides better user experience while bathtub-surfing) was watching videos on the device. Lying in bed, ready to sleep and watching an episode of Stargate or so on the device which is turned off fast is much more comfortable than using the notebook or even the video beamer.

First, I was quite disappointed: Using Windows Media Encoder and the built-in WMP9 was quite laggish: Many framedrops, bad sound quality.

Where the device really began to shine was with XViD movies and Betaplayer: No framedrops, great picture quality and no encoding-time. Very nice.

Now, if AVI would be streamable and/or if the pocketpc would just be a little bit faster while transferring files, this would get really great.

As it is now, copying a movie to the device without plugging around SD-Cards is impossible. Neither ActiveSync (much faster over USB2) nor WLAN are fast enough for transferring a movie in a bearable time frame. I think this is either a problem in the OS or in the bus where CF and SD-Cards are connected to.

All in all, I’m very happy with the device. Its slick look, the metallic body and the more-than-sufficient performance make it a great update from my 5550. What I’d wish for on a future incarnation would be more RAM and a HID capable bluetooth driver so I could use my BT-keyboard with it to write my short stories in bed too (none posted here yet).

Oh and last but not least: Maybe you ask yourself about this touchpad of the hx4700. It’s no coincidence I forgot to write about it ’till just now: It’s unspectacular. On one side it somewhat kills gaming (which does not matter for me as I’m not using my PDA for gaming – I have my GPA SP for that), on the other hand it’s just there. Neither useful nor useless. Neither comfortable nor not. Neiter an innovation nor not. It’s just there.

I have not yet come across an occation where it really is useful, but having the cursor-mode on also is no disadvantage (besides the mouse pointer floating around), so it really doesn’t matter for me.

HP iPAQ hx4700

Finally.

Last July, Lukas and me ordered HPs iPAQ hx4700 and only just today it finally arrived. Nice thing. I’m still looking at everything, so I’m going to post a deeper review sometime in the future.

But now back to my new toy ;-)

Learning by example

After getting through with Head First Servlets & JSP, yesterday I bought Programming Jakarts Struts just outof pure interest. You never know when knowing those things may come in handy.

Currently I’m somewhere in chapter 3 and already know quite a lot of things about struts (that I really like the framework is one of them – I should really try to do something Servlet-ish in the future). Chapter 3, for those that don’t know the book, is an introduction to Struts by example of a very simple online banking application.

And this gets me to the point: I’m a very practical person and I despise of doing lots of theoretical stuff. Usually I come quite soon to a point where I lose my interest because the topic gets to theoretical.

This is why I learn best using examples.

When I have to learn some database structure, I usually don’t even try to learn from the documentation. I just look at how the database is built to learn how to use it. That way, I’m doing something practical while still learning how to do the right thing. Only whhen I’m not sure somewhere, I’m going to look at the documentation.

The same thing with meetings. As soon as it gets redundant, I almose immediately lose interest. My brain hungers for more, clear information. If there is some, it just sticks. I seldom take notes and I seldom forget important stuff – just as long as it’s non redundant and somewhat visual.

So, the chapter three of the Struts-book is the optimal way for me to learn something as it’s expaining things by dissecting a complete application. This way I always know the big picture and a practical goal (the application) which helps me greatly understand and memorize the details.

And all this is the reason I so much like doing what I do at our company. Our philosphy has always been to try something out, never to think of being unable to do something, every time saying yes to some request of a potentional customer.

That way, I can always be on the lookout for practical solutions. I can always learn by example (the project I’m currently working on). In the last five years it seldom happened that I had to do something I did before. It’s learning, trying, erring, trying again all the time.

And as this is how I work best, we never failed so far to actually deliver what we promised to. From my very first CGI-script (“CGI? Never did that… but it can’t be that difficult”) over streaming satellite TV over the internet to Linux powered barcode scanners: It always worked out. And it always will.

Fix for comment spam?

Yesterday, asterisk* talks about comment spam and an easy fix to do it.

Reading the article gives quite a good insight on how those spammers work: They don’t seem to really request the page of your entry, but they only submit hardcoded values in some database.

This gets this seemingly simple trick to work. Inststead of reading the weblog page and submitting the real form, spammers still submit the hardcoded value, missing the additional form-element.

Unfortunately, this problem is easy to fix for the spammer: Just update the database with the new information form the forms. And I promise you: As soon as this hack gets more known (which is bound to happen soon as it’s so simple to impelement), they will update their scripts.

The logical next consequence would be to change this additional tag more often, leading to the spammers updating the index more often.

The ultimate consequence would be a script generating some kind of random cookie which is different on every request. This in turn would lead the spammers to actually request the form before sumitting it.

I don’t think, I have to name the consequences of that: The spam will stay, but the bandwidth needed will increase greatly. Instead of just posting, the spammer will also request the whole page.

And the spammer will certainly do that on all weblogs. Regardless of whether they deploy this cookie or not.

So in the end, this “fix” just makes the whole thing worse for all us bloggers.

Sorry. No solution. Or ist it? Convince me otherwise!