Wednesday, June 29

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!

    Ultra 20
    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)
  • Tuesday, June 28

    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

    Monday, June 27

    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
    /

    Sunday, June 26

    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.

    Friday, June 24

    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!

    Thursday, June 23

    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.

    Wednesday, June 22

    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
    /


    Tuesday, June 21

    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
    /

    Monday, June 20

    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'
    /

    Saturday, June 18

    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.

    Wednesday, June 15

    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.

    Tuesday, June 14

    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.