It's alive!

Finally managed to get the two new servers for www.faktiskt.se installed and up and running.
Quad CPU database server "Emma" and dual CPU web server "Lina".
A good friend was kind enough to donate a stack of 15krpm 18Gb drives to the project so there is now plenty of disk space as well.

Must say it feels much better to be on MySQL 4.1 instead of the ancient 3.23 version I had on the old RedHat 9 box (yes yes, beat me up, I should have upgraded that box years ago).

The couriers that shipped the servers to Sweden where even more evil than the ones I used in the UK. Luckly a friend helped me to hammer and solder the servers together before installing them. The power socket on one of the boxes looked nasty.
Surgery pics can be seen here.

And a huge thanks to exeo for allowing me to host the machiens at a very good price.

Xmas holiday!

Yeay! Two and a half week holiday, going back to Sweden to celebrate and play with my new Faktiskt.Se servers.



Mary christmas people! :-)

Follow the white rabbit

Ok, so all admins fear network problems. Routing problems and other annoyances, specially if they happened to occour when there is another problem on to solve. I’ve seen this a few times in past years, like the LAN-people are reconfiguring switches just as I reboot a Sun server from the LOM-interface and for some reason the server doesn’t come up cleanly and I can’t access that mgmt-segment where the LOM is since someone forgot my VLAN in the new switch config. Same goes if you are accessing a remote site and the WAN connection goes down or the Internet pipe drops in the middle of a NIC reconfiguration.
So what should people do about this, I’ve started using a concept I call "a rabbit" in most datacenters we have. Its can be a huge security hole if not done right. I want one machine in the network that can access all network segments, and here is the important bit; without passing any L3 switches, any routers and as few tagged VLANs as possible, simply to keep the network access as simple and straight forward as possible. This box should be your entry-point to these segments. The rabbit only works for a single location of course. WAN access without routers is most often a bit of a pain :-)

• Take a decent machine like a U5 with a QFE-card or P3 with a pack of old 100Meg NICS. Oh, and you probably need a serial port as well.
  
• Install a good secure OS on it, preferably OpenBSD. Install as little as possible. Remember this is a rabbit, not a "server".
  
• Configure pf to block all incoming traffic (on ALL interfaces) except SSH (you should probably move SSH to an obscure port like 54088 instead of the default 22).
  
• Connect a GRPS modem, can be an old Nokia phone or a proper modem like the Siemens MC35 I use and either configure PPPd dial-in to the server or simply place a simple getty on that tty. Allowing PPPd to the box is probably better since you can then forward SSH ports from all corners of the network, but in a way opens up a possible security hole (do people still use modems for hacking?).
  
• Connect ethernet cables to switches in the actual segments you want to access. Worst case, use tagged VLANS in the machine, but try to avoid it.

Having the GPRS modem hooked up gives you the possibility to access the box from anywhere in the world, great to be able to plumb NIC’s from a beach in Tahiti. :-)
One additional possibility would be to have a DSL connection to this box to better connection speed to the machine or even VPN, that’s to risky in my opinion. I probably forgot a few steps in but at least you get the picture what the rabbit is supposed to achieve.

Happy Easter.

PS. Creds to my fellow admin Wector for explaining this concept to me with his now well known matburk.

Software wants to be free (and hosted on a Sun cluster)

Sun made a quite interesting announcement earlier this week, this fall actually has been full of interesting announcements from Sun.
This time they announced that more or less the entire Sun software suite will be open sourced and released free to use. Right on! I'd say! :)

Linux made it in through the backdoor
Why do so many small/medium sized companies use Linux on more or less all their servers? I believe one strong factor is simply because that's what the sysadmins know. Young admins, say 22-27 years old, grew up using Linux. Linux was the cool thing and if they wanted to play with a "UNIX-like" (jeje) OS Linux was the obvious choice. So when they get their first positions in small/medium companies with good growth potential they go for what they know and the people they report to doesn't complain since they see how "cheap" (free) Linux is, no need to care about support contracts.

Why both customers and Sun will benefit
Now Sun wants all these admins to look again, to be able to download and test all the awesome features and scalability JES provides. Oracle did partly the same a few years ago when they released most Oracle products free to use for testing and development. My very unscientific guess is that they have sold thousands of licenses based on testing and development made by curious admins and programmers that would never even have thought of using Oracle if they had to fork up the cash without testing it first.
Lets hope Sun can see the same benefits and that people will realize the benefits of running Sun software on Sun servers.
Deploy your application on a pair of X4100 Opteron servers today, cluster them using Sun Cluster server for free. A year down the line your business goes through the roof and you need to expand, now move your application to the a rack full of 64-core Niagra servers and get the support contracts your customers require... all this without modifying one single line of code. Now we see the benefits for both you and for Sun (hint hint you narrow minded financial analysts out there). And of course, if you do need to change code, the compilersa and development environments are also, yes you guessed it, free.

Read Scott McNealys interview in BusinessWeek.

Now why are you still reading this? You should be over here downloading all the enterprise class software you can take.

PostgreSQL 8.1 on ZFS in Solaris

Benchmark time!

Got some free time with my laptop so I decided to do some basic (very unscientific) benchmarks with Postgres to compare Sun's new filesystem ZFS and with the older UFS filesystem.

Test system
Dell Inspiron 8600
Intel Centrino 1.4GHz/1Mb.
768Mb RAM
30Gb 4200RPM 2.5" HDD (Hehe, did you say bottleneck?)

Postgres version is 8.1 and I used more or less the default config. Increased shared_buffers and the fsm parameters, the fsync parameter was toggled on and off during the test as indicated below in the chart.

SQL schema

create table i1 (
        id      serial,
        test    char(50)
);

-- Insert 1 million rows with the letter x 50 times in each row.
insert into i1(test) select lpad('', 50,'x') from 
  generate_series(1,1000000);

I also did a test using the pgbench tool (20 simultaneous clients) that comes in the postgres contrib tree.

Here are the results

FS      FSYNC   Compre. Ins time. Pgbench TPS
------- ------- ------- --------- -----------
UFS     On      -       25718 ms  312
UFS     Off     -       26347 ms  380
ZFS     On      Off     98544 ms  52
ZFS     Off     Off     31850 ms  380
ZFS     On      On      69753 ms  62
ZFS     Off     On      12114 ms  375

Some results are quite intersting.
First, the automatic fsync in postgres kills zfs write performance.
Second, compression gives higher performance on my system.

Conclusion, this benchmark would have been more intersting if it had been done on a Sun Opteron server hooked up to a proper storage system. But the basic IDE drive benchmark may give some pointers. ZFS would probably have performed much better on a more suitable setup and probably proved faster than UFS due to its more direct approach to disk striping.

Portable Solaris

I installed Solaris build 27 on my Dell Inspiron 8600 laptop today. And, of course, it works like a charm. Solaris 10 and OpenSolaris has really come a long way on x86 (looking back at the 2.6 days).

root@halidell$ uname -a
SunOS halidell 5.11 snv_27 i86pc i386 i86pc

Had to install drivers for the onboard bc440 NIC and the ipw2100 WLAN card. Simple installs, pkgadd and followed the instructions. WIFI worked perfectly out of the packages, wificonfig found my access point(s) right away and it took less than 30 seconds to get everything working and browsing slashdot.
Second hurdle was to configure the 1680x1050 widescreen LCD display, seemless as well. Used the same xorg.conf file as I used for Fedora Linux.

Spend a few minutes playing with ZFS and it really rocks.
So much power and so easy to use. Plain and simple.
Veritas should be shaking their balls. Both of their cash-cows, Oracle and Sun both require less and less middleware to be powerfull.

Evil couriers

The damn couriers the delivered my second DL580 server managed to smash the power-cable sockets. Only one of the powersupplies powered up. Crap!
But I think I can fix it with a small hammer and some patiens. Works case I'll "hotwire" it straight through with a kettle lead.
But the four 700MHz/2Mb Xeon processors really kick ass. I installed a minimal centos install and it starts GNOME faster than my 3.0GHz desktop does. Not bad.

Intel Xeon 700MHz slot-2 CPU


Some good news
I have today and tomorrow of from work.Yeay.
When shopping today and picked up a nice blazer and some shoes!
Not even sure what my plan is for tomorrow.

Second DL580 server

And we have lift-off! :-)

At least quite soon.
Won another Compaq DL580 auction today, used the "buy-it-now" feature actually.
£205 for a Quad 700Mhz/2Mb CPU machine with 2Gb RAM. Unfortunatly no HDDs' in it.
Now I have both the DB back-end and the Apache front-end machine.
Need to find a few drives now, one more 36Gb drive and at least three 18Gb drives would be sweet.
I'll post some geek pr0n later :)

eBay snipers and Compaq servers

Ok, so I've this web site project going. A bit secret so far but I'm coding some things for a very cool site. And for this site I of course need a few servers to run it on. I'm not expecting insane amounts of hits bit still want to cater for quite some traffic. One dedicated DB server and one Apache server. I have high hopes :-)
After scanning eBay for a few weeks I decided that two good old Compaq DL580s was the way to go. Nice quad CPU machines with lots of redundancy and expandability. And best of all, the price on eBay is quite good. They usually go for about £250GBP in a decent spec. I would have preferred to get Sun or IBM machines but after doing the price/performance maths on refurbished machines HP/Compaq just made more sense. The alternatives where either IBM x350/6000r's or Sun E420r's I guess, perhaps if I can find a real bargin.

I actually managed to snag a DL580 for just £150GBP last week. Quite good spec, 2x700MHz Xeons, 4Gb RAM (yes four!), 3x36Gb SCSI drives and a nice SmartRaid 6400 controller. The drives alone usually run for £35 on eBay.
Haven't much time to play with it yet, re-configured the drives to a RAID5 array, flashed the BIOS to the latest version and installed CentOS on it, feels extremly fast and responsive for being a 4 year old 700MHz machine.
Also bought a few Intel gigabit server NICs at £14 a pop.

Then today I found another nice DL580, good price and all four CPUs installed. So I place my bid, someone bids over, ok I increase my bid by 30 pounds. Then 1 second before the auction ends some bastard snipes it right infront of my nose. DAMN!
eBay should really try to stop people from using these 3rd party softwares to fuck things over.
Looks like I have to get a machine from Germany now, some company there has like 30 DL580s in stock and seem to be quite alright to negotiate with. Hmm, perhaps I should get two more machine so I can get two max speced ones and also have a spair chassis in case things go face up.

PS. Anyone wanna trade 5x 36Gb FC-AL drives (and the bulky MetaStor array the come in) for HP/Compaq SCSI drives? :-)

PostgreSQL 8.1 released!

Yeay!

Finally ready for production, or at least intense testing.

Biggest news:

• Role-based access control, similar to Oracle
  
• Bitmap scans. Faster index usage, fewer tuples to check.
  
• min() and max() can now come in on an index
  
• Table partioning. Could be quite nice to have if you have huge tables. Think billions of rows.
  

See the full list here.

Go test check it out!
Download here.

Oh yes, PgAdmin 1.4 was released. Download that here.

Damn, I need more RAM for all my VMware instances.

Oracle XE 10g beta public release

Oracle is launching step 2 (InnoDB was 1) in messing with MySQL with the launch of Oracle XE.
XE is free to use and free to distribute. Perfect to bundle with small software packages or when developing a website that needs scalability. Oracle guarantees application compatibility with SE/EE versions of Oracle.

I also think that the release of XE will bring more light to the cute little tool HTMLDB, for those of you who are not familiar with HTMLDB it is more or less an application server with a built in IDE. You can create a small web based application in the matter of minutes. Perhaps a small reporting interface or a timesheet system. A good example of a larger HTMLDB application is Ask Tom. Tom Kyte is by the way the main man endorsing XE. He is happily explaining how much better Oracle XE is than MySQL in this nice podcast.

Installing Oracle XE is a piece of cake on most systems, Oracle even acknowledge that it will work on Debian (spoke to benr on IRC and he mention that there where a few issues installing on Gentoo). A quick rpm -i and small config script sorted everything out on my Fedora Core 4 VM. Just connect. Well almost, the installer still has a few tiny bugs but those will probably be sorted before final release.
One annoyance is that there is no oraenv script in a decent path. The default env script is located in /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh. Perhaps not the best choice if you ask me. The whole idea of installing under /usr/lib seems a bit silly.

Main limits

• Will only utilize 1 CPU
  
• Can only use 1 Gb RAM
  
• Maximum USER tablespace size 4Gb.
  
• All high availability and online juggling features are disabled
  
• Only available for Linux and Windows on i386 so far
  
• Only one instance per machine/container
  
• No official support available
  

Main features

• Free (doh!)
  
• Easy to install using RPM in Linux. No more OUI.
  
• Easy to use with HTMLDB, minimal settings to play with.
  
• Almost full Oracle SQL feature set available.
  
• Application compatibility with big brother guaranteed
  
• Support community will probably be great
  

Read the FAQ [pdf].
Download Oracle XE beta release here and don't forget to register and visit the XE forums.

Auto incremental primary keys in Oracle

Why doesn't Oracle support auto incrementing a number column?
The logical way to do this would be to allow a sequence to be tied to a column default value.

This "should" work.

create table itest (
        id number(9) default seq_itest_id.nextval primary key,
        name varchar2(10) not null,
        age number(2)
);

But it doesn't. The concept exist in this way in Postgres (using a sequence, it can even do all the association itself if you specify datatype 'serial'. MySQL has its' own quite odd way to this, simply just "auto increment" the field without any real flexibility or control... the typical MySQL approach ;)

I've been helping a friend port a MySQL schema/app to Oracle without needing to change the app code to much, so auto incremental id's was a must.

This is the way to do it, using a very simple trigger.

drop table itest;

create table itest (
        id number(9) primary key,
        name varchar2(10) not null,
        age number(2)
);

drop sequence seq_itest_id;

create sequence seq_itest_id;

drop trigger itest_id_trigger;

create trigger itest_id_trigger
  before insert on itest
  for each row
  begin
    select seq_itest_id.nextval into :new.id from dual;
  end;
/

insert into itest(name,age) values('Scott',25);
insert into itest(name,age) values('Sarah',24);
insert into itest(name,age) values('Billy',33);

-- Ok, lets see what we got in our table
select * from itest;

        ID NAME              AGE
---------- ---------- ----------
         1 Scott              25
         2 Sarah              24
         3 Billy              33

Not to difficult.

Cute HTML reports from psql

Most people are aware that it is possible to dump quite decent HTML reports from Oracle SQL*Plus, but not many people know that the same is also available from PostgreSQL psql tool.

I've written a few scripts and template for a small project and thought it could be fun to share. The framework simply looks in a directory for .sql files and execute them using psql -f FILE.sql and expects the script to spool a file with the same name but the prefix .data.html. It then concatinates the script with a header and footer HTML file to create a pretty report. This is without using any PHP/Perl or server side parsing. Perfect for things like static weekly reports.
Modify the script after your needs and slam it in to cron.

Show example.

Download the basic framework tarball. The docs aren't great tho :)
Included the sample data and query to generate the example.

Day off and DSL installed

Have had the day off! Yes!

Was busy with 'something' this morning but all afternoon I slacked around. Or not slacked around perhaps, went shopping and even found time to get a hair cut.
First time in almost two years I've had a day off.
I think I'm going to take new Tuesday off as well. Things are sort of slow in the office right now so it shouldn't be a problem.

Secondly
My ADSL modem started flashing green today instead of the annoyingly red.
The PPPoA line syncs in at around 5.2Mbit, can't say I'm overthrilled by the speed but it is definately good enough. Someone told me that the Speedtouch 330 USB-modem Bulldog offers for free suck donkey balls and that the speed is usually alot better when using a high-end router.
I'll see if I feel downloady enough to bother.

IRC topic of the day
Todays topic in #oracle on FreeNode has been PL/SQL. Seems like alot of people are migrating from MySQL systems and tend to over-use PL/SQL wherever they can, even where plain SQL is enough. Hmm.
I should get around to write that small PL/SQL tutorial I've been thinking about for a while. Just some basics to stop people from pasting SQL-queries straight in to PL/SQL and expecting the column aliases to be referable.

Note to self.
Fix special character bug in the phpNuke WAP interface.

Cool breeze of AMD

Question:
-What is the average temp of a desktop CPU these days?

The last time I built a PC for myself and actually cared a bit about the components and performance of the machine it was a AMD XP1800+. I remember spending something like £40 GBP on a better CPU fan and chassi fan to get the temp down and keep it reasonably quiet. However, I still had a CPU temp in the high 40's. Usually around 47C if I remember correct.

The new box I built the other day with a AMD64 3200+ proc using the retail boxed fan and a normal 12cm chassi fan has a CPU temp of 28C. Yes, that is correct. Both the BIOS and the awful "GigaByte EasyTune" reports the same temp. I was quite impressed tbh.

Coolness! :-)

CD recommendation:

The Cardignas: Super Extra Gravity
Ok, it doesn't top Long Gone Before Daylight but the new Cardigans CD is great. I'd say number two on their list.

apt-get install ubuntu

So I finaly got around to buying a new computer. Built a simple AMD64 box (Nope, did not go dual core) with parts bought from my Scandinavian friends over at Komplett.
With a new machine I of course needed an OS and since Ubuntu has gotten so much press lately I sort of felt it was my duty to test it.
Ok, I have to admit, I was quite hesitant of using a Debian distro after being forcefed RPM's for the last 5 years and Solaris 10 DVD's for the last year.

However, I was quickly proven wrong :)
Install was a breeze and the default system looked quite alright.
After first reboot X failed to start due to that the installer hadn't found my ATI x800 graphics card (PCI-e) but the driver was just an apt-get away. Downloaded and ran fglrxconfig to configure my settings (damn thing did not add the Zaxis-mapping to make the scroll-wheel work tho).
No more problems after that, I added a few more apt repos and installed some more basic software.
All in all good but I must say I felt a bit confused with the file structure of the PostgreSQL 8 install with files all over the place. I'm used to compiling it myself to a dedicated dir, most often /u01/postgres with the datadir under /u02/pgdata. No a big issue anyway.

If I feel motivated enough I will install Solaris 10 on the box as well. Need somewhere to run Oracle. Perhaps VMWare is the solution?

Oh, and I forgot to write that I bought a new monitor a month back, an Iiyama Prolite E481S (8ms response and 700:1 contrast). The picture quality is truly amazing. Almost comparable to a good CRT. It really came to good when hooked up to the new ATI card.

Le desktop de Ubuntu :-)


Ciao!

PS. Finally got my Bulldog DSL install date. Tuesday 18th this week. *Yeay*

London broadband suckyness

I got sucked in to a great broadband deal the other month.
8Mbit broadband and free telephone line for only £20 per month.
Great I thought.
Now six weeks later and not a beep from the ISP I am a bit puzzled if it was such a good deal after all.

By now I think about 90% of all brittons have figured out that I signed up to Bulldog Broadband a part of Cable & Wireless.
In short; they suck and I should have known better.

Bulldog fan sites
http://www.my-bulldog-hell.co.uk/
http://www.bulldog-hell.com/


I should just have waited for the Swedes :D
http://www.bethere.co.uk
24Mbit service in central London before xmas. Yes please!

Fun and games with foreign keys in Oracle

Thought I'd post two small scripts I wrote to take care of evil foreign keys when doing data restructuring.

First spool this script to a file to have the create statements of all FK's. The script does a self join to get the referenced column.

create_foreign_keys.sql

set lines 180
set pages 0
set heading off
set feedback off
select distinct
        'alter table ' ||c.table_name||
        ' add constraint '|| c.constraint_name ||
        ' foreign key ('||c.column_name||
        ') references ' || d.table_name ||
        '('|| d.column_name||');'
from
        dba_cons_columns c,
        dba_cons_columns d,
        dba_constraints a
where
        c.owner='SCHEMANAME'
        and c.constraint_name=a.constraint_name
        and a.constraint_type='R'
        and a.R_CONSTRAINT_NAME=d.CONSTRAINT_NAME
/

Then spool and execute this script to drop all constraints.

drop_foreign_keys.sql

set lines 180
set pages 0
set heading off
set feedback off
select distinct
        'alter table ' ||c.table_name||
        ' drop constraint '|| c.constraint_name ||';'
from
        dba_cons_columns c,
        dba_constraints a
where
        c.owner='SCHEMANAME'
        and c.constraint_name=a.constraint_name
        and a.constraint_type='R'
/

After you are done with your restructuring you just execute the first script to re-create all the foreign keys. Done!
Probably doesn't work on to advanced FK's but anyway. :)

phpNuke forums in your mobile

A few users over at Faktiskt.se got talking about how cool it would be to have the phpBB forum on their mobiles.
Well said and done, I sat down a wrote a small PHP script to do just that.
It probably doesn't comply with any WML standards but it works great on all phones we have tested it on (mostly semi-new Nokia and Sony/Ericssons (read XHTML)).

And lets say there is alot of room for improvements. Only functionality right now is basic browsing, I plan to add a search function and a text pre-processor (remove bbcode/large images etc) amongst other things when I find the time.
Posting will however probably not be implemented.

It shouldn't be to difficult to port it to a plain phpBB forum, just change on or two SQL queries.

Test drive (in Swedish) at http://www.faktiskt.se/wap/

Download the script from here.

Enterprise action

Lots of cool stuff happening in the IT world right now.
Summers over, get back to work!

Skype
Ebay buys Skype. I have my doubt that this was good for Skype but at least they made a shitload of cash ($2.6b + $1.5b to be specific). I myself is not to keep to use Skype with people on Ebay all the time. E-mail is good enough.
Reuters has got the scoop.

Siebel
I've been ranting about this one for a few years now :)
Oracle buys Siebel. Along with Peoplesoft Sibel got eaten by the beast.
Story at The Reg.

Sun
And I guess noone missed the NC05Q3 yesterday.
Some nice new Opteron boxes from Sun. Good boxes, but not amazing. Cool KVM remote access thingie, SAS 2.5" drives, low power consumption. I was hoping for an introduction of the new 32 thread SPARC procs (yes, after 3 years "suckyness" SPARCs are interesting again. Would make a perfect Java application server in my company. Wide load instead of the depth the Opterons give.
Anyway, the X-series is cool, good price. I'll try to get my hands on some :P

Cheap cables

I needed to order some Ethernet- and power-cables for our data centre and just did a quick Google for cables and found this company called Cable universe.
They seemed to have good prices, so I ordered a pack of power cables and Ethernet cables (violet cables for our MGMT/LOM segment *pretty*).
Cool, same day dispatch if I order before 3.15pm. Within 1 minute I had a "order is being processed" and after less than 4 minutes I received an e-mail saying that my order had been dispatched.

4 minutes for a complete order processing is quite impressive, I must say.

Highly recommended!

iPod nano

Apple does it again...

The new iPod nano must be the coolest gadget so far this year.
Sleak design as always. Looks amazing.
Priced quite good as well, 4Gb model is £179 GBP ($299 USD)

All together now:
-I want one!


Picture gallery

Dell PowerEdge SC1425 review

Since I never can find a review of servers before I buy them I thought it was my responsibility to do one myself.

Introduction
The SC1425 is thought to be a HPC-node or similar where more than one box is used. It gives good performance at a good price. But provides close to no redundancy, dual drives is about it. We will use our boxes as load balanced application servers where it doesn't really matter if one box fail as long as it is repaired in a reasonable time.
Starting price point in the UK is £339, the review spec. lists at £880.



Specs for my review box:

Dell PowerEdge SC1425

Singel Intel Xeon 3.2GHz

2Gb DDR2 ECC memory (4x512Mb modules)

2x 80Gb 7200rpm SATA drives

Dual port Intel Pro1000 Gigabit Ethernet

Intel SATA controller

Single PSU

1U rack-mount kit

No CD, no DVD, no floppy (Yes! the way I want it!)

See the Dell website for full specs.

Installation
Rack mounted in 4 minutes, nice snap-in rails.
I wrote a quick kickstart script for CentOS 4.
The PXE prompt appeared as it should I after entering my kickstart file and pressing enter twice it took about 30 minutes and voilaia, I had a clean login prompt and a pre-configured system. Quite lengthy install you might think but keep in mind that I did not have a hardware RAID controller in the box (£135 extra option) so I opted to build software-raid MD devices during the install so just syncing them took an extra 15 minutes.
Clean and simple, no hickups. All the hardware was supported, no problems with anything so far. [dmesg].
Logged in thru SSH and did a yum -y upgrade to get the latest kernel etc, nice 180Mb download. *sigh*
.

Management
During the startup I had seen a cool IPMI prompt flash by so I just had to reboot and check it out. The IPMI/BMC interface will give you some very basic remote managemepossibilitiestes outside the OS level. Like power cycle the machine or check sensor temperatures. Keep in mind you need a Dell patched ipmitool for use with Dell servers (bastards). Configured an IP for the IPMI card (which actually uses eth0 as it's mgmt interface) and set it to our mgmt vlan (yes, had to tag the port in the switch as well). Easy enough. All worked straight out. I could not find an option to switch on or off the gimp light.
The IPMI facility does not compare to the LOM cards in Sun boxes or the addon RAC cards from Dell, no remote console etc. But it does the trick to power cycle a frozen server. Totally acceptable for the price.


Performance
The SC1425 is very fast for the price, chipset is alright and I guess the only performance draw back is expected. The onboard SATA controller and software-raid.
CPU crunching and memory access is quite good.
During a the pgbench runs I got about 20% iowait which is far from good. But fixing this adds another £250 to the box price (PERC + SCSI drives).

PGBENCH results.

[root@ron1 pgbench]# ./pgbench -t 50 -c 50
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 50
number of transactions per client: 50
number of transactions actually processed: 2500/2500
tps = 149.314233 (including connections establishing)
tps = 150.790507 (excluding connections establishing)
[root@ron1 pgbench]# ./pgbench -t 50 -c 100
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 100
number of transactions per client: 50
number of transactions actually processed: 5000/5000
tps = 139.651475 (including connections establishing)
tps = 140.947096 (excluding connections establishing)

Compiling PostGRES 8.0.3

time make -j3 
real    1m58.274s
user    3m22.002s
sys     0m13.220s

Conclusion
My full impression of the Dell PowerEdge SC1425 is quite good. Price wise it is really good, performance is good and redundancy is as expected. I would never recommend someone to use this machine as a database server or office file server. It should purely to be used in farms of two or more boxes. Web servers, Citrix servers, application nodes or pure HPC.
They could have added a few more remote management features.

More pictures










* Note, all picture are taken with a Ericsson K750i mobile phone, quite good phone I must say. Thanks John.

PS
The SC1425 is known to work flawlessly under Solaris 10 so if I manage to get some time I will try to install it and check it out.

Ask slashdot about free RDBMS

Slashdot is running an "ask-slashdot" thread about comparing MySQL and PostgreSQL. Quite funny to see peoples misunderstandings of Postgres.

I just can't figure out why most people prefer MySQL over Postgres. Probably "user-friendliness", you can throw ugly SQL at it and it will still make a decent attempt to parse it and execute.
Secondly speed of course, however I find the speed point a bit hyped. When indexing Postgres properly with partial and functional indexes it is damn fast. And given that most of the MySQL speed optimizations are done with the sacrifice of transaction robustness and data concistantcy.
Now with bitmap scans in PG 8.1 it should fly.
On the other hand, all these things require a good DBA, not a "well I did rpm -i and it all is sooo fast" guy. :)

I'd say that people should read the slashdot debate with a pinch of salt, 90% of the people probably have no clue what they are talking about.

And don't forget that Mysql is now evil.

Read more over at Slashdot.Org

Postgres triggers

Postgres trigger
Database triggers are server side function that are executed on a row or statement when a DML command is received to the server. It's a good way to "fool" applications and users to believe things are done as they should or simply to cascade an command to more tables, i.e. logging.
Today (yes, on a damn Saturday) I was asked to try to create a scenario to stop our application from changing a value in the database so our client unit would repeat an operation for regression testing purpose (without modifying application code).
Simple, just add a trigger to change it back.

Here is a small trigger example for PG 8 (almost identical in Oracle except for some syntax changes):

-- Create sample table
CREATE TABLE dogs (
 DOG_ID  SERIAL PRIMARY KEY,
 NAME    VARCHAR(20),
 STATUS  VARCHAR(1)
);

-- Create function
CREATE FUNCTION dogstat() RETURNS trigger AS '
BEGIN
IF NEW.name=''FIDO'' THEN
  IF NEW.status=''S'' THEN
    UPDATE dogs SET STATUS=''A'' where name=''FIDO'';
  END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER dogtrigg AFTER UPDATE ON dogs
  FOR EACH ROW EXECUTE PROCEDURE dogstat();

-- Insert test
INSERT INTO dogs(name,status) VALUES('FIDO','A');
INSERT INTO dogs(name,status) VALUES('PLUTO','A');
-- Ok lets test
test1=> select * from dogs;
 dog_id | name  | status
--------+-------+--------
      1 | FIDO  | A
      2 | PLUTO | A
(2 rows)

test1=> update dogs set status='S' where name='FIDO';
UPDATE 1
-- Ok what happened?
test1=> select status from dogs where name='FIDO';
 status
--------
 A
(1 row)
-- Nothing really! Noone can (S)ell FIDO! :-)
-- What about PLUTO?
test1=> update dogs set status='S' where name='PLUTO';
UPDATE 1
test1=> select status from dogs where name='PLUTO';
 status
--------
 S
(1 row)
-- SOLD!

Ok, not to difficult.
However, I do not advice to use triggers all over the place in production databases. This sort of logic should be at the application level in my opinion, but in this case for a regression test scenario it works very well.

Want to read more?
Postgres 8.0 trigger docs

pi is NOT 3.14

According to Indiana, US state law the value of pi is 4, not 3.1415 :)

Check out more funny american laws here http://www.ahajokes.com/

Fake Ralphies

Just spotted a fake Ralph Lauren Classic polo shirt on a guy on the street. Funny thing is that the color combo doesn't even exist in the real collection. Fake fakes :-)
Bogus! Watch out.

The real thing:

Why Starbucks

-Why do I like Starbucks?

Good coffee! First and most importantly, the coffee is the best around when it comes to common chains.

Nice stores. The stores always look clean and fresh, not old and scruffy as many others. Often nice sofas soft chairs

Atmosphere. It's always a nice relaxing feeling to step in to a Starbucks store (well almost, rush hour can be mayhem (that's why I don't work regular hours)).

Good staff. Most of their staff is extremely friendly and very nice, they seem to hire people with a good attitude and at least some experience in the area. And without sounding to sexist, they do hire "more beautiful people". In my local Starbucks (Cowcross St, Farringdon) there is an extremely cute blonde with freckles working. Oh yes!)

Fun drinks. Ok, I'm not a huge fan of the exotic drinks, 99 times out of 100 I will order a Grante Late, however, it is nice to have the option to order a nice strawberry frappochino on a hot summer day.

Cool music. Perhaps a big part of the atmosphere. Starbucks always play smooth relaxing music. Jazz, blues, soft soul, folk etc.

Backup alternative? Caffee Nero

XFCE - And things just get faster!

Time to take back the desktop. I'm sick and tired of people complaining about GNOME, KDE, CDE and JDS all the time ranting about how slow it makes their systems.
Stop complaining and install XFCE!

XFCE provides a clean and fast desktop without clutter or overgrown widgets that you never use. On my Centrino laptop screenshot [1600x1200]) it starts in about 3 seconds.

Fresh screenshot of my desktop:


Official snaps:




Guys, we all like breasts yes?
Please donate to the Breast Cancer Research campaign over at http://www.breastcancercampaign.org/ to save as many breasts as possible!
You can pop in to any Ann-Summers and donate or use the web site. They give you a cool pink wristband if you do!

The x64 Factor

Sun is continuing to kick x64 ass. First the quite decent OEM v20z and v40z (from Sanmina) then the awesome Ultra 20 and now it's time to slay the x86 market with their own line of opteron servers (codename becky). Most likely three models 2 socket, 4 socket and the 8 socket monster.
Read the article on The Register

Click to sign up [sun.com] to the Network Computing 05Q3 Web event on the 12th of September.

And now a music recommendation.
New cool electronica album from Goldfrapp. Very modern and smooth with classic samplings with the SID feeling. Simply good. Go buy it (or take a peak listen here).

Goldfrapp - Supernatural

IRC is for you!

Time to get some more people to use IRC.

Firstly, I'm now a permanent "op" (moderator/operator) in the #oracle channel on FreeNode.

Secondly, there must be at least a few hundred channels that will catch your interest. More or less every single known community open-source project has got an IRC channel. You have #kde, #postgres, #mplayer. There are also channels for probably all programming and scripting languages such as #python, #perl, #java and #sql and of course #solaris and #opensolaris. Sun even keep a few real Sun support techies in the Solaris channels. Cool.

So just yum/pkg-get/apt-get an IRC client if you don't already have one (good changes are that you already have one).
I personally use IRSSI which is a shell based client that works great to run in a screen so I can attach/detach wherever I am.
Other GUI based options are XChat for any operating system (Linux, Solaris, AIX, Windows, *BSD etc) or the old classic mIRC client for Windows.

See you on IRC!

Database/schema duplication in Postgres

In Oracle it is extremely simply to replicate a schema to another. Using an export and doing the import using the fromuser/touser options it is done within minutes without hassle with permissions or other issues.
So how do I do that in Postgres?
Here is the method I currently use, way to many steps IMO.

postgres@server77[~]$ pg_dump -O database > database_dump.sql
postgres@server77[~]$ psql template1 postgres
Welcome to psql 8.0.3, the PostgreSQL interactive terminal. [...]
template1=> create user bob password 'bobby123';
CREATE USER
template1=> grant all on tablespace data1 to bob;
GRANT
template1=> alter user bob set default_tablespace to 'data1';
ALTER USER
template1=> alter user bob set search_path to schema1,
schema2, public;
NOTICE:  schema "schema1" does not exist
NOTICE:  schema "schema2" does not exist
ALTER USER
template1=> create database newdb owner=bob tablespace=data1
template1-> encoding='unicode' template=template1;
CREATE DATABASE
template1=> \connect newdb bob
You are now connected to database 'newdb' as user 'bob'.
newdb=> \i database_dump.sql
[ lots of output from data execution ]
newdb=> \d
[ list of relations, make sure all your 
objects are present ]
newdb=> ^D

I'm welcome to suggestions on how to do this easier and faster. Our developers love to have test database to break and play with.

Another thing is that I've seen some problems with schema entities from Java, even tho the user search_path is set correctly it's not possible to reference a table.
This is even more likely to happened when a schema has been renamed. Our Java guru Andres (hey dude!) have informed me about this a few times. I'll see if I can find something in PG bugzilla or I'll submit a new report to the development team.

Over and out.

Server shopping

So yesterday was Dell bashing day. Today is Dell praising day.
I need to get a few new server for an upcoming project with a quite strict budget. After shopping around for a few hours the options where quite clear, we only had one option. Dell.
The price for one PE2850 with two 3.2GHz procs, 2Gb RAM, 6x 36Gb 15krpm disks and two PESC1425 single 3.2GHz proc, 2Gb RAM, 2x 80Gb disks is just over £4000. I could hardly believe it, really good price. I hope the SC1425 budget boxes perform as well as their big brother the 1850 tho. The only real down buy is the lack of a RAC controller and that they are single PSU (not counting the fact I went for the SATA model instead of SCSI). We will load balance the pair anyway so redundancy for the machines themselves isn't that important.
There not v40z's but hey, good bang for buck ratio. :)

Now the question is regarding partitioning on the Postgres server.
6 drives with two viable options.

All six drives in a RAID5 volume.

Three RAID1 volumes.

In my past experience the best option for off-the-shelf RAID controllers with less than 10 drives is just to make one single RAID5 volume. Probably what I'll end up doing this time as well. IBM has got a quite nice RAID level called 5E, sort of like RAID5 but you loose a bit more space but gain a lot in write performance, to bad Dell/LSI can't adopt something similar.

Another question, is EM64T Linux mature enough for a production environment?
I doubt it, but will probably test it before we go live on 32-bits.

Ok, back to writing anaconda kickstart scripts for Centos.

Dell vs. Home built x86 PC

Ok, I've had this annoyance for a while now. My desktop at work is a Dell Dimension with a 3.0GHz proc and a SATA drive, but it still gets completely bogged down when I write a DVD disc, the same thing happens when someone copies like a large ISO-file from my PC over the LAN. Initially I thought it was just the way it was when writing DVD's or other heavy I/O.
However, when I tried burning a DVD on my friends home built 3.0GHz box with a similar SATA drive and spec., it was hardly noticeable. The only real difference in hardware is the he's got a better motherboard (Asus or Abit I think) and thus probably a better IDE chipset. My guess is that the cheap bastards at Dell chose the cheapest possible junk chips they could find and hence leaving more load on the CPU when I/O comes in to play, more DMA load or something.

Conclusion; -Dell suck when it comes to raw-power!
Sounds like I need that dual-core machine sort of ASAP. :D

Cool flash aninmation about switching to Linux (like RPM and shit):
http://www.nata2.info/humor/flash/switchlinux3.swf

Intel as the low cost budget alternative?

5 years ago when the AMD Athlon first came to the market it was the "bang for buck" leader on the market with a quite low price. With its fair share of problems, overheating, a bit unstable etc. Now when AMD have sorted out those problems and even got the technology advantage on the market it seems like Intel is trying to take that spot. Don't get me wrong, I've been a AMD users since the Athlon XP was released, except for the last year when I've only had my Pentium M laptop.
The "new" dual-core Pentium processor are cheap, fast, lots of cache. But hot and their power consumption is totally out the window, some sources say 70W at idle, just insane, the equivalent AMD64 idles at less than 15W.

Ok, but that aside. The pros of the d800 series chips are quite nice, you get DDR2, the i955 chipset and most i955 motherboards support 8Gb RAM instead of the usual 4Gb you get on normal AMD boards. The prices of RAM is starting to become quite acceptable, a 2Gb branded DDR2 kit (2x1Gb) is around £150GBP Inc. VAT here in the UK. Add a decent motherboard at say 120 and you get a good system upgrade for around £450 GBP. Of course this is not the best option for a gamer, but for me as a boring data crunching / data shuffeling guy it's damn good value for money. A perfect database test pc.
The equivalent AMD X2 system is probably another hundred or so.

SMP systems are still quite costly, I do miss the old classic concept of the Abit BP6 motherboard, capable of running dual Celerons without any adapters.
I do miss my old Bp6 machine (some of my old friends probably remember the blue sprayed mega-tower machine nick named "KES" (from StarTrek yes)) :).
The only good alternative these days is to try to pick up a SMP box on Ebay. It's easy to find a good spec. Sparc or PA-RISC box. But I do probably need to boot Windows once in a blue moon to test something (Ok, to play games).

One nosy squirrel

A friend and I had a close encounter with a nosy squirrel today. We sat on a bench in Hyde park eating our muffins and drinking our lates from Starbucks when this squirrel senses the smell and starts hassling us to get a bite. It even climbs up on the bench and snatches my paper bag. I managed to take a few snaps with my camera phone. I think it was Carrie who called them "rats with furry tails" but I must say that they are way smarter than rats (and cuter).




More snaps

Sysadmin day

Happy System Administrator Appreciation Day!
If your a sysadmin I hope your users appreciate (read buy you [beer|cake]) today, and if you are a "user" please buy your sysadmin [beer|cake].
I got our office a box of Krispy Kremes.
http://www.sysadminday.com

I've been doing some benchmarks on huge PostgreSQL tables in the past days. Loaded a table with 150 million rows and created 10 partial indexes of 15 million rows each. The response times are quite amazing, selects around 1ms and updates under 20ms.
I'll consider posting some more results if anyone is interested.

The London terror wave continues, today police raided a council estate a few miles from where I live. I thought that area was mainly inhabited by rasta faris but apparently some Muslims live there as well.
BBC News has the scope.

Another music recommendation:
Heard "Hurricane" on TV and had to dig up the old classic Bob Dylan - Desire album.

Gadget recession

- "I don't have a MP3 player, I don't have a Blackberry, I don't have a PDA, I don't have a smart-phone, I don't have a GPS navigator, I don't own one single piece of Bluetooth hardware, I don't even have a USB memory stick."

What is happening with the world? Most of my "geek friends" don't have loads of gadgets any more.
Are gadgets becoming fashion accessories? At least the iPod has, followed by the Blackberry (which isn't even black any more but comes in all sorts of cute colors.
On the streets of London I see people with gadgets all the time, but not the people you used to see with gadgets. Now we see cute girls with iPods and flashy mobile handsets, bankers with their Blackberrys and just normal people using IM on the move.
I even saw a nice looking girl the other day with a Zaurus Linux PDA. She probably didn't even now how hot it looked.

What is left of the "legacy gadget freaks"? Have the geeks "been there, done that" and realized that gadgets don't chance our lives that much or are they just rejecting gadgets because they are "socially acceptable".

Is blogging is the new gadget!? :)

Need to stock up on Gadgets?
Gadget Stuff
Dr. Gadget
Think Geek
mp3players.co.uk
Gadget Review

Function based indexes in Postgres

I actually found something that PostgreSQL does better than Oracle (or at least they
take a different approach that proves quite good in our use case).

Lets say w have a table with ticket numbers and most of the queries run against the table is to determine if a ticket is valid or not and the condition is that VALID='Y' and that REDEEM_DATE is not set. Simple, lets create an index for just those conditions. I'm not claiming this can't be done in Oracle, but to be honest, the approach in Postgres is so much simpler for such a basic scenario.

Example DDL

CREATE TABLE tickets (
 ID serial,
 TICKET_CODE char(10),
 ISSUE_DATE timestamp,
 VALID char(1),
 REDEEM_DATE timestamp,
 CAMPAIGN_ID int
);

[ insert data (90.000 rows in this case) ]

CREATE INDEX tickets_valid_idx ON tickets (redeem_date,valid) 
WHERE valid='Y' AND redeem_date is null;

ANALYZE TICKETS;

Ok, lets see the execution plans (hail GEQO).
Compare the cost figures for the first (heavily used) query with the negation of the second query (which is never run except in this test case).

testdb=> explain select count(*) from tickets where 
VALID='Y' and redeem_date is null;

                        QUERY PLAN
-------------------------------------------------------
 Aggregate  (cost=192.91..192.91 rows=1 width=0)
   ->  Index Scan using tickets_valid_idx on tickets  
       (cost=0.00..192.79 rows=48 width=0)
         Filter: (("valid" = 'Y'::bpchar) 
           AND (redeem_date IS NULL))
(3 rows)

testdb=> explain select count(*) from tickets where 
VALID='N' and redeem_date is null;

                        QUERY PLAN
-------------------------------------------------------
 Aggregate  (cost=2715.14..2715.14 rows=1 width=0)
   ->  Seq Scan on tickets  
       (cost=0.00..2714.12 rows=403 width=0)
         Filter: (("valid" = 'N'::bpchar) 
           AND (redeem_date IS NULL))
(3 rows)

The downside is of course as with all indexes that updates and insert to the table will be a bit slower. We do bulk inserts so that's not a huge problem. One concern tho is that when a ticket is used we set VALID to U and redeem_date to now() so that the row in question must be removed from the index, this could lead to quite fragmented indexes. Might be worth keeping an eye on it for the first few months.

Oracle data tranportation made easy

I got to spend some time with the Oracle Data Pump this morning. We where sent an expdp file for analysis, quite large and using something like 15 tablespaces and of course they had also renamed our main application schema. The dump was even made with 10gR2 and my target system was 10gR1.
So what, not a problem?! :-)
A few lines of impdp hacking and the exp loaded like a charm, to our default two tablespaces and to our default schema name.

Example (cut down version):

USERID="/ as sysdba"
LOGFILE=import.log
DIRECTORY=tempdir
DUMPFILE=dp050720.dmp
REMAP_SCHEMA=appschema1:app
REMAP_TABLESPACE=users:data,data1:data,data2:data,index1:indx

Read the Oracle Data Pump Overview docs.

And a quick CD recommendation:
We've all heard them before, just need to be reminded about their greatness once in a while. Their live performances are something extra, there is a reason they where the first band to play at LiveAid 20 years ago.

Dire Straits - Alchemy - Live

How UNIX are you?

Sometimes people are just wasting time when it comes to computers or rather the computers are wasting peoples time. That's why I like UNIX and applications like Oracle. If you know what you are doing, you waste very little time fucking around. Things are where they are supposed to be and they just work as intended (well, at least most of the time).

I wrote a small check list to see how UNIX-like you are :)

Do any of these statements match you?

Start X11 without a window manager to save resources (hey, xterm's go a long way).

Ever said "Damn, mutt is bloated!" to yourself

Installed an older version of a compiler because the new features look scary?

Added your CD collection to a heavily indexed Oracle database?

You try to solve performance issues by tuning instead of buying more hardware

Girls using SSH are hot!

Starbucks coffee is so cheap!

If more than three or four of these are true for you then I think you are more UNIX based than you think :-)
Congrats.

Oracle oratab location bug [update]

Update on my bug discovery.

Oracle of course knew about it already and had a closed bug report.
Strange that it hasn't been fixed.

In addition to editing dbhome Oracle suggested to edit the dbshut and dbstart scripts as well (I always write my own init.d scripts).

Bug ID 4108039

Case closed

Oracle oratab location bug

Ok, so I finally got around to confirm my suspicion of a tiny bug in Oracle on Linux.
Tested and confirmed on the default 10.1.0.3 installation and I'm pretty sure I've seen it on at least 9.2.0.4.

Problem description
If the directory /var/opt/oracle exists during the Oracle installation the dbhome script will have the parameter ORATAB set to "/var/opt/oracle/oratab" instead of "/etc/oratab". The normal DBCA process during a default installation will still add the instance entry to /etc/oratab.

This causes the oraenv script to fail finding the ORACLE_HOME and will prompt the user. oraenv calls dbhome to determine the ORACLE_HOME for the instance selected.

Workaround
Modify the dbhome script(s) in your "common" path (usually /usr/local/bin) and in $ORACLE_HOME/bin.

Change line 47 from
ORATAB=/var/opt/oracle/oratab
to
ORATAB=/etc/oratab

I'll submit the TAR to Oracle and see what they say about it. Tiny bug, I'm quite sure that not many people create /var/opt/oracle on Linux.

Extracting Oracle object DDL

It is often usefull to be able to get the DDL for existing schema objects, one easy method is to do an export of the schema in question and import it to an indexfile and the other method is to use the dbms_metadata package, the later method is probably faster and the preferred way.

Example how to get scott.emp DDL

set heading off
set pages 0
set long 1000
select 
  dbms_metadata.get_ddl('TABLE','EMP','SCOTT') 
from 
  dual
/

I wrote a small script to export a full schema, just connect as the schema owner and run the script. [ View ].

Read Using DBMS_METADATA documentation.

Summer time is maintenance time

Upgrades
People are on vacation and system usage is quite low, perfect for some upgrades!
I took the opportunity to upgrade our last 8i databases, we are officially (almost) 8i free. There is one old Sun box running 8.1.7 system in India that no one really cares about, it holds a quite big database of historical data and has perhaps 2 logins per month.
The lowest production release we have now is 9.2.0.4 which I plan to upgrade to 9.2.0.6 sometime in the coming weeks.
A few old RedHat 2.1/9 systems also got upgraded to RHEL4 or CentOS 4 (yes, sick and tired of paying RedHat the big dosh for terrible "support").
The older Solaris systems keeps ticking with old release that are still fully supported by Sun without any hassle or weird comments from the support, we should use Solaris more.

Oracle CPU licensing policies
I browsed through the Oracle price list and noticed that multi-core CPU's are charged higher than single core. My position is that one CPU is one socket and I think most companies agree with me, but not Oracle :(
fortunately for us single CPU systems with multiple cores are treated as 1 CPU when running SE/SE One, but on larger systems you have to multiply the number of cores with 0.75 to get the number of CPU licenses to purchase. Takes some of the glory from the upcoming v80z from Sun, a four CPU system with dual-cores will be charged as a six CPU box, meaning about $80k extra cost!
This is not cool, Oracle needs to re-think this!

10gR2 features part#1

Spent some time playing with the 10gR2 instance today, some things are quite nice. Sort operations are definitely faster, several of our reporting queries are 10-20% faster and the new functionally of rman is truly amazing, tape library support etc.

Example how to use restore points.
Ok, to be honest this could "almost" be done in 10gR1 as well, we did have flashbacks to SCN's and timestamps but this is easier to manage.
You must the database in a media recovery state (archivelog enabled.)

[ enable database flashbacks (if you haven't done so already) ]
SQL> SHUTDOWN TRANSACTIONAL;
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=720 scope=spfile;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;
[ create a database restore point ]
SQL> CREATE RESTORE POINT TEST_POINT1 GUARANTEE FLASHBACK DATABASE;
SQL> SELECT NAME, TIME FROM V$RESTORE_POINT;
[ do stuff ]
[ damn, broke things]
[ lets flashback the database to it's previous state ]
SQL> SHUTDOWN TRANSACTIONAL;
SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO RESTORE POINT TEST_POINT1;
SQL> ALTER DATABASE OPEN NORESETLOGS;
SQL> DROP RESTORE POINT TEST_POINT1;

Oracle documentation for restore points.

Oracle 10g Release 2

So we finally have the second release of Oracle 10G available for download from the Oracle website. It's actually been available since June 6th but I've been tied up in other things so I haven't had time to look at it until now. Anyway.

I installed it on one of our test boxes and the installation hit a few problems, first gcc/ld failed with a missing libstd++.so.5 error, had to install another compat library and the linking retry worked fine.
Secondly dbca failed with a boring "TNS: lost connection" error. Quick look in the log files showed that sqlplus died with a missing libaio error (yes, the pre-reqs should have checked for this and warned if the RPM was missing). After installing the RPM I ran dbca again and it finished without problems.

10gR2 pretty much feels like normal 10g on the surface, the cool stuff is deeper down. I'm going to try out some of the new features later today or tomorrow, and no, I'm not going to write any stored procedures in VB. ;-)
One thing I reflected over is that 10gR2 feels a bit "lighter" than 10g, it uses a tad more memory but feels faster on my quite slow 800MHz test box than 10g does.
View the 10g Release 2 documentation library.

Command to check for the two missing RPMs (required on my FC4 system):

rpm -q compat-libstdc++-33 libaio

Wasting CPU cycles

A friend asked me to have a look at one of his Linux servers that have had some performance problems in the last few weeks. The machine collects and do simple data processing, mostly in SQL but there are a few shell scripts launched from cron every few minutes. When looking at the ps output there where a lot of those shell scripts running and continuously forking processes over and over, which of course slowed down the machine. Looking at the shells scripts they where quite simple, open one file and output to another. All data processing was done with the classic shell utils (cut, sed, wc, tr etc.). What people seem to forget is that bash has a quite nice array of built-in features that require way less resources and doesn't fork any child processes. I simply changed perhaps 15-20 lines a few shell scripts and the load avg. went down quite a bit.

The main features that can save CPU cycles are for variable manipulation. Things like the classic cut -d, -f1 can be replaces by ${VARNAME//,*} and wc -c can be replaced with ${#VARNAME}. It is highly recommended to study the bash man-page if you frequently use shell-scripts on production systems.

I wrote a small script to demonstrate two different ways to get fields from a CSV value (view).
Here are the results for 100.000 processed values (2x50.000).

[hali@halidell rep]$ ./fields.sh
Running 50000 tests...
Method one took 170 seconds
Method two took 5 seconds
[hali@halidell rep]$

Busy busy

I've been quite busy the last few days, been trying to sort out a few things at our India site and then your head Java developer left here in the UK so it's been a couple of hectic days.
Then the terrible London bombing occurred yesterday. I was on a circle line train at Paddington when the blast at Edgeware road happened, we all got asked to leave the station in a rush. I wasn't aware of the magnitude of the incident until I got to work (walked) and read about it online.
The Indian news channel Aaj Tak found out that I had been near one of the explosions (we are in the same building as them in India) so they wanted to interview me on live television, only 34 million viewers(!!!).

Site recommendation
Cool article about Oracle time and date usage.
http://forums.dbasupport.com/oracle/ora9i/TimeZone.shtml

Music recommendation
Some soft blues from Taj Mahal
Especially the song "Ain't gwine to whistle dixi" is so soft and well performed, I've been listening to it in my Grado headphones all morning (the recording is very good as well).

Get the machine type in Linux

Found a new usefull command in Linux, similiar to the prtconf command in Solaris.
I've been looking for some way to find out the machine type in Linux and after much seeking I found dmidecode. It's not uncommon I get asked to "check out" a server in India or elsewhere in the world and it's always frustrating not to know what brand/model it is.

Example from a Dell PowerEdge 650 pizzabox.

[root@monitor root]# dmidecode |grep Prod
                Product Name: PowerEdge 650
                Product Name: 0W1373
[root@monitor root]#

Pink Floyd @ Live8

Sometimes it's good to live in central London, sometimes it suck.
Today, it's extremely good!
500 meters to Live8 and the Pink Floyd concert at midnight. Coolness!
To bad I didn't score any tickets (stupid contest!).

Oracle I/O tuning (or not to)

When an Oracle table is updated and new inserts happen it is possible for a row to end up on more than one block, this will cause more disk I/O than what is really necessary. It's not a huge problem in a smaller installation but may become an issue if you have very large tables and lots of fetches.
Here is a small script that a schema owner can use to find chained rows in his/hers tables.

column AVG format 99.99
select
  table_name,
  avg_row_len,
  num_rows,
  chain_cnt,
  chain_cnt/num_rows*100 AVG
from 
  user_tables
where
  chain_cnt >0
/

TABLE_NAME     AVG_ROW_LEN   NUM_ROWS  CHAIN_CNT    AVG
-------------- ----------- ---------- ---------- ------
INVENTORY               87       3066       1377    .45

Ok, so we got a table that has got some quite a high percentage of chained rows.
First thought in most DBAs head would be to resolve the "problem" as soon as possible, start rebuilding things. Why do DBAs always want to rebuild things? Indexes tables and god knows what. I'd say 75% or all object rebuild are unnecessary or even damaging (i.e. loosing index statistics).
But does it really matter, do we really fetch these rows? Lets check.

select 
  name,
  value
from 
  v$sysstat 
where 
  name like 'table fetch cont%'
/

NAME                                 VALUE
------------------------------- ----------
table fetch continued row             4000

So there seems to be some problems with chained rows. We now need to either rebuild the table or simply delete and re-insert the chained rows. The later solution is of course preferred but may not be possible due to constraints that cannot be dropped.
Oracle provides nice tools to find chained rows and re-insert them.
A quick guide on how to that is provided here: Eliminating Migrated or Chained Rows in a Table
To create the CHAINED_ROWS table that is mention in the above article issue this command at your SQL*Plus prompt.

@?/rdbms/admin/utlchain

There is also a quite nice thread about this over at Ask Tom

Update:
To see all you basic row fetch stats use this query.

select 
  name, 
  value 
from 
  v$sysstat 
where 
  name like 'table fetch%' 
  or name like 'table scan%gotten'
/

Time for a new workstation

I remember speaking to Sun HW rep. at a seminar this spring and telling him that Sun should stop f*cking around and give Dell a run for the money and release a kick ass midrange workstation. They guy didn't say much back but said they where considering expanding the Opteron line of workstation. My expectations where quite low since Sun already had two decent workstations in the Opteron line (the W1100z and W2110z). I was expecting some semi-decent box starting at the $995 mark.
Ok, now what we got is quite far from that. It's a great machine starting at the $895 mark (although you really need to opt for a better graphics card).

So it is a with a great welcome Sun introduced the Sun Ultra 20
It simply owns anything else in the segment.

Why?

Fast Opteron proc

Cheapest "real" workstation ever sold

True 64-bit

Solaris 10

Comes with a shit load of (previously) expensive software

Real UNIX-style Sun keyboard as default

It uses disk SPUDs just as its old SPARC siblings (u2/u30/u60 etc)

Doesn't require expensive ECC-memory (but can use it of course)

It just feels right!


Check out the webcast (Warning! RealMedia rubbish required).

On the other front,
I've been starting to investigate the use of undo tablespace in Oracle to create "diffs" in time between an existing table an a older version of that table. Will probably publish some examples later on.

Wanna learn Swedish? ;-)
mastering_swedish_-_lesson_3.mp3 (slayradio.org)

Slow slab day

Slow day today. Haven't done much so far except from extracting some web site statistics and trying to get a better understanding about file system cache vs. application cache in Linux. Especially when using non-caching/buffering raw devices. Been studying the kernel slab when using a raw vs. non-raw volume.
First impression is that file systems suck! :)

Favorite Linux command of the day: slabtop

Oracle 10gR2 highlights

The key for the upcoming Oracle 10g release 2 is totally "do more with less" or simply "Quality of service". Automation and simplification is been extended even more and the need for middleware and other 3rd party applications to support the database is simply not there any more (but sometimes it do help).
Simpler cluster management both with RAC and DataGuard. Easier tuning, optimization with improved functionality in ADDM, Segment advisor, Automatic backup (now with full tape support (no need for that $10k Legato license!)).

xquery
The new query language. Now you can not just store XML/XSLT files in your database you can even query XML data (and normal "flat" data as well for course) in the next xquery language.

Microsoft CLR (common language runtime)
Now honest I'm not sure if this is really a good thing, but anyway, you can now have stored procedures written in VB and C#. My worry is that this will open the doors to many junior programmers to destroy what I love about Oracle ("It just works!"). I'm sure that Oracle will attract a few new customers with this addition though.

Restore points
How many times hasn't a DBA been in the position to think "Ok, lets do a backup before we start this huge data manipulation operation"? Now you can do that even without messing backups, just create a database restore point and if you encounter problems you just "flashback" the database to your restore point.

Clusterware HA API
This I've been waiting for, now you can write your applications to natively support full cluster failover and recoveries. Restart and application at node failover or balance your application servers even before the Oracle client layer.

Transparent Data Encryption
Encrypt data on-the-fly, create a table with encrypted columns. Even the DBA can't access the data without the key used when inserting the data.

Really something for everyone, I can see that the improved CLR and PHP support will probably sell a few SE One licenses and the terrific cluster and manageability enhancements will give EE a boost as well.

OTN has the full story. I can recommend the podcast with Ken Jacobs.

SQL quickie:
Check if any (l)users have stored data objects in the wrong tablespace. I keep finding indexes in the data tablespaces all the time. May be worth to check every two months or so if you have optimized your storage and keep indexes different spindles.

select distinct 
  tablespace_name, 
  segment_type 
from 
  dba_segments
/

Midsummer celebration

Yesterday was the Swedish midsummer (summer solstice) celebration.
There was a very nice party in Hyde Park in London hosted by the Swedish Junior Chamber of Commerce. After a few drinks and some awful herring (tradition) in the park we moved on the Ruby Bar by Lecister square and finished the day with some hot dancing. Met some really nice new Swedish people as well, always good to exercise my Swedish. :)
Now I'm off for some Sunday slacking and brunching.

phpNuke FTP user uploads

Together with some friends I run a small website that uses the phpNuke CMS-system. Our uses have been asking for a way to upload image to a FTP-site to be able to publish articles with images on the web site. All user logins must be authenticated through an existing MySQL schema and since MySQL sucks and the version we use doesn't support views the FTPd must allow user defined queries for all operations. I've been thinking on a way to do this for quite some time and finally came up with this solution. I'm a ProFTPD monkey at heart but in this scenario I had to use PureFTPD due to some SQL-limitation in ProFTPD. This solution should work with a phpBB database as well and should be easily adaptable for other systems.

PureFTPD installation.

$ wget ftp://ftp.pureftpd.org/pub/pure-ftpd/releases/
pure-ftpd-1.0.20.tar.bz2
$ tar xjf pure-ftpd-1.0.20.tar.bz2
$ ./configure --prefix=/usr/local/pureftpd --with-mysql
$ make
$ make install
$ groupadd -g 800 nukeftp
$ useradd -u 800 -g nukeftp -d /u01/user_uploads nukeftp

Create a MySQL config file and save it as /etc/pureftpd-mysql.conf.
I created a new MySQL user and granted it select privs to the nuke database (although using the same user as the web site also works fine). I also choose to use one single UID/GID for all uploads, there is really no need for anything else since all users are chrooted to their home and since we don't have real home directories in the DB I just did a fake select from nothing (same as DUAL in Oracle) using the \L (login name) macro.
This is what I used.

Example:

MYSQLSocket     /var/lib/mysql/mysql.sock
MYSQLUser       username
MYSQLPassword   password
MYSQLDatabase   nukedb
MYSQLCrypt      md5
MYSQLGetPW      select user_password FROM nuke_users WHERE UserName='\L'
MYSQLDefaultUID 800
MYSQLDefaultGID 800
MYSQLGetDir     SELECT '/u01/user_uploads/\L'

Apache configuration
Recommended reading: Apache 2.0 docs

SetEnvIfNoCase Referer "^http://www.mysite/" local=1
SetEnvIfNoCase Referer "^-" local=1
SetEnvIfNoCase Referer "^$" local=1
[ Stop hot-linking to picture from outside your site, you may
want to add google/yahoo/msn etc. to this list to allow searches
to hit your image archive. ]

ReadmeName README.html
HeaderName HEADER.html
[ Allow users to have descriptions of their image archives. ]
Alias /uploads/ "/u01/user_uploads"
< Directory "/u01/user_uploads">
   AllowOverride AuthConfig Limit
   Options +Indexes
   Order allow,deny
   Allow from env=local
< /Directory>

Start PureFTPD
One really annoying thing with PureFTPD is that it doesn't have a propper configuration file, there is some lame perl wrapper that can parse a text file and provide the command line arguments. PureFTPD will also create any user home directories that does not exist. Specify the IP to listen on with the -S option.
I choose do it manually anyway (./pure-ftpd -h to see all options).
Start PureFTPD with this command (don't forget to put this in a init file somewhere):

$ /usr/local/pureftpd/sbin/pure-ftpd -B -fftp -H -A -lmysql:/etc/pureftpd-mysql.conf -m4 -s -S10.0.0.1,21 -j -p55000:60000 -I300 -d

Finished! Go test it out!

10G patching

Just finished patching one of our 10g development systems to the latest patchset from Oracle (10.1.0.4 Patchset 2). Smooth as always with 10g. catpatch.sql may take quite some time on slow systems, same with utlrlp.sql.

1. Download patchset 4163362 (and the README!) from Metalink and decompress it.
2. Stop the listener, cssd, em, all instances etc.
2b. If you are upgrading an important production box (do test on a staging server before going live!), do a backup. I'm to lazy to do this for a development box.
3. Start the runInstaller and click through all the defaults. (I always do this in a VNC session to the server so I can detach if I get bored or need to reboot my WS).
4. For each instance login (using sqlplus) as sysdba and run:

SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF
[ review patch.log for any errors, fix 
them and if needed run catpatch.sql again]
SQL> SHUTDOWN
SQL> STARTUP
SQL> SPOOL utlrlp.log
SQL> @?/rdbms/admin/utlrp.sql
SQL> SPOOL OFF
[ review utlrlp.log for errors, ignore recyclebin
errors, shouldn't be any other errors really (unless 
you i.e. disabled public execute access to utl_file as
Oracle so kindly recommends) ]

5. Start the listener, em etc.
Done!

Don't forget to upgrade RMAN catalogs and stuff like that if you use that and if you get problems with EM just reset the timezone as described in the Readme.

Bitmap index

Been playing with bitmap indexes in Oracle on my FC4 machine all morning, our application really gets a boost when using them. I'm just afraid that the scares updates will be suffering and to slow, need to benchmark update speed as well.
My select benchmarks (very unscientific) on a 3 million row table give some pointers on speed;

Simple where x=y query

Full table scan:  3.50 sec
B-tree index:     0.65 sec
Bitmap index:     0.02 sec

Query with list where x in (y,z). Now here it gets interesting

Full table scan:  3.50 sec
B-tree index:     3.05 sec
Bitmap index:     0.05 sec

The index doesn't do much at all with a btree index since the inlist iterator didn't come in as it did when using the bitmap index.
I've put the execution plans for the second test in a file if someone is interested (view)


SQL of the day (on a completely different matter):
This query will summarize all connected users, since many applications spawn several hundred DB connections from the same application the query will group by username, application and the machine they are connecting from and also show the total number of connected user (not that internal Oracle connections will not be listed).

column Username format a15
column Application format a30
column Machine format a25
break on report
compute sum of "No. Sessions" on report
select
 username "Username",
 program "Application",
 machine "Machine",
 count(*) "No. Sessions" 
from
 v$session
where
 username is not null
group by
 username,
 program,
 machine
order by
 username
/

Oracle 10g on Fedora Core 4

Quick update on Oracle compatibility.
I just installed 10g (10.1.0.3) on Fedora Core 4 without any issues, runs like a charm.
I did a normal server install and added the development/X11 RPM groups, then installed openmotif manually, did the sysctl jada jada and finally used my default 10g repsonseFile and it finished without any tackles.
Puschitz guide for FC3 should probably work if you need more directions.

Thank good for AC!

The summer is definatly here now, temperatures in London touching 34C some days now. Without AC in the office we would get boiled. Now I just need to get one at home as well.
Our India team is reporting +45C temps so perhaps I shouldn't complain. :-)

SQL of the day:
A simple query to give a quick overview of who is using database disk space and also in what tablespace.

column USED format 999,999.99
select 
  owner, 
  tablespace_name, 
  sum(bytes/1024/1024) USED 
from 
  dba_segments 
group by 
  owner, 
  tablespace_name
/

Back to work

"Travel suck!" -Hampus Linden, 2005

Had a quite boring flight back to London. Sat next to a "large women" so I couldn't really move an inch during the 2 hour flight (which was delayed for half an hour), then after landing all trains to London from the airport was canceled due to engineering work (don't we all know it) so I had to take the bus. Argh! Took another half an hour extra to get back home.

At least I've had some time writing on the Oracle sqlplus/rlwrap article, should be done by next week or so. Feels good to be back at work again, Resin fighting and Oracle auditing on the schedule today.

Today's SQL tidbit:
List all users that are allowed to connect to an instance and have open accounts.

select r.grantee from 
  dba_role_privs r, 
  dba_users u 
where 
  r.grantee=u.username 
  and u.account_status='OPEN' 
  and granted_role='CONNECT'
/

Out and about

So things are chaotic! :)
The bike race is just about finished and there are cyclists everywhere, camping, sleeping, eating, leaving their rubbish *Rant* *Rant*. Germans especially, nothing bad about Germans in general (yeah right) but they do leave their stuff everywhere and doesn't care about cleaning up after themselves.
Had a coffee with the old man in town and watched the cyclists as the finished the race, quite nice day and lots of cute girls around.

Went out to some clubs and bars yesterday and I must say the new smoking ban rules! Spent like 5 hours in a bar and didn't come home smelling like an ashtray. Yeay! :) Met some old mates as well, nice to catch up on things and hear about how everyone is doing.

Vacation slacking

Spent a good few hours cleaning and fixing my old bike, it's been in storage at my Moms place here in Sweden while I've been working in London. Took a quick ride around town when I was done trying it out, the gears still need some tweaking and some more oil. I think but I'll save that for the weekend *lazy*

My home town, Motala, is really coming to life for a few days now. There is a big (the biggest in northern Europe actually) bike race this weekend, 17k people or something racing (and just for the record, No, I'm not in the race).

Also dug up my old Sun Ultra 60 computer from storage, installed some RAM and a HDD I picked up on Ebay a few months back. Must say that the U60 is one of the last "true Sun boxes". It just feels right to work with, proper disk spuds, robust case etc.

The blog way

Ok so everything is done by blog these days. Guess it's time for me to take the step and start ranting about everyday nonsense (and hopefully some interesting things).
Being sort of a techie guy I'm probably a few years behind on "the blog scene".
Currently got huge shoe issues. More to come in that area ;-)

Next to ranting about annoying things and should-know-better PHB's I'll also try to post links to articles and papers I publish about various techie subjects in the Oracle/Unix/Linux/storage area.

I the meantime, check out the (unfortunately in Swedish) music and Hi-Fi web site http://www.faktiskt.se

Catch ya'll later.