PostgreSQL 9.0 High perfomance

I've just finished G.Smith's "PostgreSQL 9.0 High performance". It's just an excellent book for any DBA. It provides detail information on PostgreSQL-related tuning (from server hardware and OS settings to query tuning and DBMS monitoring). We are going to upgrade our DBMS server next year, so I'll have an opportunity to apply this recommendations in practice. The only disadvantage is that the book is Linux-oriented, and we have FreeBSD DBMS server. However, basic settings for our OS are also covered in this book. Now I wonder: should I use ZFS for PostgreSQL on FreeBSD? I think at least I should make some benchmarks...

Social network

When book is over and there is a mess in my head, movie helps... I've just watched "Social Network". At least it was interesting. The ethernal opposition between men who just do something and believe in this and men who just want to earn some money and clear off.

Paradise Lost

I've just finished reading Milton's "Paradise Lost". Mixed impression... I tried to start reading this book three times before. The book is sometimes quite boring, but it has very intersting details. Disliking the Bible and religion in general, I can appreciate Milton's interpretation of banishment from paradise. And also, author draws Lucifer image not without sympathy. A figure defying omnipotent God is worthy of respect...

Just remember never use ctrl+Ins in server console

I'm quite disturbed. I had found file to delete, had copied its name, entered "rm" and pressed ctrl+ins... What effectively pasted in the contents of one script. So I recieved "rm #!/bin/sh". I'm lucky, it wasn't "rm /bin/sh"... I hate several copy-paste buffers - one general buffer in KDE and separate buffer in Konsole.... Uuuuuu..........

One more awful day

Just one more day when everything goes wrong, you are so exhausted that can't even read, you eager to sleep, but you can't and so on, and so on. And there's no one to blame for this except yourself.
The only wish is to drink a glass of vodka and to go sleeping...
Sounds like a shit, feels much worse...

Vim-compatible web-based text editor

It is not a joke. Google created vim-compatible Web-based text editor :) I don't feel myself such a geek. It is the best editor in command line, but why should anyone use it in GUI environment, especially in user-oriented OS?

Is Russian Culture department a band of thieves?

I do like our government!!! Fuck them and their laws (c, Prodigy). The Russian Culture Department recently created new perfect idea: let's take money from computers and ebooks sellers (1 % from sales). Quite ellegant decision for feeding an army of bureaucrats when Russian economy is on the knees...

This is why Russian science will die or something about philanthropy

I finish reading a DBMS-related course in South Federal University. I've read 34 hours (once per week since September to December) lectures and received about 100$ for this work. Our universities are the most philanthropic organizations in the world!!!
I'm not complaining, I have other sources of income, but it clearly shows that I'm not motivated to read lectures :) For example, to prepare for one lecture I need about 6 hours + 2 hours of lecture - so we have 8 hours per week or about 128 hours overall. It's about half of full working month, so I supposed to work for 200$ per month... Cool! I was always shocked with Russian higher education system. It's main miracle is that it still exists.
And we always hear different shit from our beloved leaders about High Technologies and Science. It's quite funny.

XID wraparround and postgresql single user mode

I've just found on pgsql-general mailing list interesting detail. This case supports the idea that manuals should be read from cover to cover. Of course, I've never bothered with this :)
The case was the following: DBMS refused to start with message:

2010-12-01 08:36:42 EST LOG:  database system was shut down at
2010-11-29 17:38:17 EST
2010-12-01 08:36:42 EST FATAL:  invalid cache id: 19
2010-12-01 08:36:43 EST FATAL:  the database system is starting up
2010-12-01 08:36:43 EST LOG:  startup process (PID 640) exited with exit
code 1
2010-12-01 08:36:43 EST LOG:  aborting startup due to startup process
failure

The previous messages in the log were

2010-11-29 12:39:17 EST ERROR:  database is not accepting commands to
avoid wraparound data loss in database "fps_data"
2010-11-29 12:39:17 EST HINT:  Stop the postmaster and use a standalone
backend to vacuum that database.

I'm glad, that it was not mine DBMS. As we see from log, postgresql shutted down itself to avoid XID wrapparound. It is covered in detail in the manual. The main idea is that every row in table is marked with a XID of last transaction, which modified it. So, when row is not modified for long, vacuum (or autovacuum) will increase its XID, and it will not wrap (to compare XIDs PostgreSQL using modulo-2^31 arithmetic, so that "for every normal XID, there are two billion XIDs that are "older" and two billion that are "newer"; another way to say it is that the normal XID space is circular with no endpoint" (c) PostgreSQL 9.0 Documentation).
In this situation, server had seen that we were close to maximum 2 billion difference between current XID and oldest one in DB and shutted down. So, now we have to run postgresql in single user mode and do vacuum on affected database. Something like that:

# su - pgsql -c '/usr/local/bin/postgres --single -D /pgdata  dbname'
PostgreSQL stand-alone backend 9.0.0
backend> vacuum;
backend> ^D
# /usr/local/etc/rc.d/postgresql start

DBI-Link - why is it so buggy?

I've spent several hours trying to make dbi-link work - I had errors related to table names and errors related to dbi_link.shadow_trigger_func(). However, I couldn't just get up - I was asked to suggest a sollution for MySQL <=> PostgreSQL replication and this module could help me to do the job...
So, I've found the following post , which helped me a lot, so I modified dbi_link.shadow_trigger_func to look the following way:

 CREATE OR REPLACE FUNCTION dbi_link.shadow_trigger_func()  
 RETURNS TRIGGER  
 LANGUAGE plperlu  
 AS $$  
 require 5.8.3;  
 ######################################################  
 #                          #  
 # Immediately reject anything that is not an INSERT. #  
 #                          #  
 ######################################################  
 if ($_TD->{event} ne 'INSERT') {  
   return "SKIP";  
 }  
 spi_exec_query('SELECT dbi_link.dbi_link_init()');  
 my $data_source_id = shift;  
 die "In shadow_trigger_function, data_source_id must be an integer"  
   unless ($data_source_id =~ /^\d+$/);  
 my $query = "SELECT dbi_link.cache_connection( $data_source_id )";  
 warn "In shadow_trigger_function, calling\n  $query" if $_SHARED{debug};  
 warn "In shadow_trigger_function, the trigger payload is\n". Dump(\$_TD) if $_SHARED{debug};  
 my $rv = spi_exec_query($query);  
 my $remote_schema = $_SHARED{get_connection_info}->({  
   data_source_id => $data_source_id  
 })->{remote_schema};  
 my $table = $_TD->{relname};  
 warn "Raw table name is $table" if $_SHARED{debug};  
 warn "In trigger on $table, action is $_TD->{new}{iud_action}" if $_SHARED{debug};  
 $table =~ s{  
   \A         # Beginning of string.  
   (.*)        # Actual table name.  
   _shadow       # Strip off shadow.  
   \z         # End of string.  
 }  
 {$1}sx;  
 $table = $remote_schema . "." . $table if defined $remote_schema;  
 warn "Cooked table name is $table" if $_SHARED{debug};  
 my $iud = {  
   I => \&do_insert,  
   U => \&do_update,  
   D => \&do_delete,  
 };  
 if ($iud->{ $_TD->{new}{iud_action} }) {  
   $iud->{ $_TD->{new}{iud_action} }->({  
     payload => $_TD->{new},  
     tbl => $table,  
     source_id => $data_source_id  
   });  
 }  
 else {  
   die "Trigger event was $_TD->{new}{iud_action}<, but should have been one of I, U or D!"  
 }  
 return 'SKIP';  
 sub do_insert {  
   my ($params) = @_;  
   die "In do_insert, must pass a payload!"  
     unless (defined $params->{payload});  
   die "In do_insert, payload must be a hash reference!"  
     unless (ref $params->{payload} eq 'HASH');  
   my (@keys, @values);  
   foreach my $key (sort keys %{ $params->{payload} } ) {  
     next unless $key =~ /^.?new_(.*)/;  
     my $real_key = $1;  
     push @keys, $real_key;  
     push @values, $_SHARED{dbh}{ $params->{source_id} }->quote(  
       $params->{payload}{$key}  
     );  
   }  
   my $sql = <<SQL;  
 INSERT INTO $params->{tbl} (  
   @{[  
     join(  
       ",\n  ",  
       @keys  
     )  
   ]}  
 )  
 VALUES (  
   @{[  
     join(  
       ",\n  ",  
       @values  
     )  
   ]}  
 )  
 SQL  
   warn "SQL is\n$sql" if $_SHARED{debug};  
   $_SHARED{dbh}{ $params->{source_id} }->do($sql);  
 }  
 sub do_update {  
   my ($params) = @_;  
   die "In do_update, must pass a payload!"  
     unless (defined $params->{payload});  
   die "In do_update, payload must be a hash reference!"  
     unless (ref $params->{payload} eq 'HASH');  
   my $sql = <<SQL;  
 UPDATE $params->{tbl}  
 SET  
   @{[ make_pairs({  
     payload => $params->{payload},  
     which => 'new',  
     joiner => ",\n  ",  
      transform_null => 'false',  
      source_id => "$params->{source_id}"  
   }) ]}  
 WHERE  
   @{[ make_pairs({  
     payload => $params->{payload},  
     which => 'old',  
     joiner => "\nAND ",  
     transform_null => 'true',  
      source_id => "$params->{source_id}"  
   }) ]}  
 SQL  
   warn "SQL is\n$sql" if $_SHARED{debug};  
   $_SHARED{dbh}{ $params->{source_id} }->do($sql);  
 }  
 sub do_delete {  
   my ($params) = @_;  
   die "In do_delete, must pass a payload!"  
     unless (defined $params->{payload});  
   die "In do_delete, payload must be a hash reference!"  
     unless (ref $params->{payload} eq 'HASH');  
   my $sql = <<SQL;  
 DELETE FROM $params->{tbl}  
 WHERE  
   @{[ make_pairs({  
     payload => $params->{payload},  
     which => 'old',  
     joiner => "\nAND ",  
     transform_null => 'true',  
      source_id => "$params->{source_id}"  
   }) ]}  
 SQL  
   warn "SQL is\n$sql" if $_SHARED{debug};  
   $_SHARED{dbh}{ $params->{source_id} }->do($sql);  
 }  
 sub make_pairs {  
   my ($params) = @_;  
   die "In make_pairs, must pass a payload!"  
     unless (defined $params->{payload});  
   die "In make_pairs, payload must be a hash reference!"  
     unless (ref $params->{payload} eq 'HASH');  
   warn "In make_pairs, parameters are:\n". Dump($params) if $_SHARED{debug};  
   my @pairs;  
   foreach my $key (  
     keys %{ $params->{payload} }  
   ) {  
     next unless $key =~ m/^(.?)$params->{which}_(.*)/;  
     my $left = "$1$2";       
     warn "In make_pairs, raw key is $key, cooked key is $left" if $_SHARED{debug};           
      warn "In make_pairs, pairs are @pairs" if $_SHARED{debug};            
      $bl=!defined $params->{payload}{$key};  
      $bl1=defined $params->{transform_null};  
    if (  
       defined $params->{transform_null} && # In a WHERE clause,  
       !defined $params->{payload}{$key}   # turn undef into IS NULL  
     ) {         
        push (@pairs, "$left");  
     }  
     else {         
        warn "o, fuck";    
        warn "params->source_id is $params->{source_id}";          
       push @pairs, "$left = " . $_SHARED{dbh}{ $params->{source_id} }->quote(  
         $params->{payload}{$key}  
       );  
     }  
   }  
   my $ret =  
     join (  
       $params->{joiner},  
       @pairs,  
     );  
   warn "In make_pairs, the pairs are:\n". Dump(\@pairs) if $_SHARED{debug};  
   return $ret;  
 }  
 $$;  

And this worked! Now I just can create after each row triggers on local PostgreSQL table and promote data to MySQL. I like DBI-Link, however, if I were to use it in production I would examine its code attentively...

10 rules for choosing scalable DBMS suitable for your OLTP application

I've just read "Ten Rules for Scalable Performance in Simple Operation Datastores" by Michael Stonebraker and Rick Cattell. We've just discussed the scalability question at work. So, I've found this article quite in time.
The authors state 10 rules for scalable systems performing simple operations over data (read or write a few items).
In short, the rules are:

1. Only shared-nothing system can scale well (in case of adequate table partitioning and replicating read-mostly data). Shared-disk systems suffer from synchronization overheads.
   
2. High level languages can be fast. Compare, e.g. slow relational DBMS and fast navigational - e.g. network or hierarchical - who did win the most of the market share? At first SQL DBMS were considered extremely slow, but just for now optimizer is much smarter then most of the programmers. Also, API matters. However, stored procedures are necessary to achieve adequate performance (eliminating communication and query compilation overhead).
   
3. Your application (or DBMS) should leverage huge memory sizes (64 GB per 16 hosts - a terabyte of main memory is not a dream). However, existing DBMS have a lot of overhead in query processing - locking, logging, maintaining buffer pool, multi-thread locking (latching) overhead).
   
4. HA and automatic recovery is necessary for scalable application. HA includes possibility of automatic data repartitioning, normal operations during DoS, hardware failures, lost network connectivity, application errors, DBMS errors and so on. Sounds like a dream and not fully achivable. But at least we should avoid outages for the most situations and part of recovery actions should be done automatically (e.g. data repartitioning after node failure).
   
5. On-line everything. All operations should be on-line: adding nodes, performing updates, changing data schema and so on.
   
6. Avoid multi-node operations. You should select partitioning key properly to avoid multi-node operations and synchronizations. Such operations just can't be fast.
   
7. Don't build ACID yourself. If you need it (and in most cases you do), use available solutions (DBMSs). CAP theorem is not always a principal thing - ACID is more precious then impossibility of cluster partitioning. Modern LAN technologies allow you build redundant network, and loose of WAN-connected office is not a great problem - using WAN-connected nodes in your cluster transparently is madness because of network delays. If connection breaks, replication may be restarted later.
   
8. The system must not have a lot of knobs. A system should be easy to administer and be able to do auto tuning. I wish my DBMS were so clever :)
   
9. Node performance should be considered. 20 monster nodes is much better then 1000 half-broken outdated junk. And supporting them is usually much cheaper.
   
10. OpenSource makes you free. Without comments. I'd like to eliminate our outdated Oracle installation. We can't update it because it's too expensive, and can't rewrite our system from scratches....
    

I can't help but agreeing with them...

DLink DIR-615

I've just spent about an hour on DLink DIR-615. This piece of ... hardware just didn't want to establish PPTP connection with my provider (BeeLine). Luckily after about two hours of tambourine dance, I'm on-line... Firmware update from 3.02RU to 3.03RU solved my problem. And I can't find how to enable telnet access on this device. It's awful - I have to configure router using my browser...
At least, this device has a very pleasant price - about $50. This is its best feature...

ZFS snapshots or something new on something old

Everyone knows that snapshots are fascinating feature of ZFS. Automatic snapshots in OpenSolaris used to save my mental health. After migration to FreeBSD I thought "It would be easy. There are a lot of decisions for managing ZFS snapshots in FreeBSD". In fact, theare are too many decisions for managing ZFS snapshots in FreeBSD. It's hard to choose one decision. I've looked for example, on sysutils/zfsnap, sysutils/zfs-snapshot-mgmt... But my needs are quite different:

• I'd like the decision for making automatic snapshots to be simple and effective as an axe.
• I'd like to understand how every line of these scripts (for creating and deleting snapshots) work to feel myself comfortable.
• I'm going to use snapshots on my home desktop, and I'd only like to have quite recent copy of my data to recover files after making some silly mistakes.

At last, I decided to create my own solution which was supposed to work as rc script (I boot my computer once or twice per day, and it would be good to have startup snapshot of the system).
The text of script is provided below.

#!/bin/sh

. /etc/rc.subr

name="zfs_snapshot"

start_cmd="create_snapshots"
stop_cmd="drop_snapshots"
zfs_pool=${zfs_snapshot_pool:-"zpool"}
keep_days=${zfs_snapshot_keep_days:-30}

create_snapshots()
{
  echo "Creating zfs snapshots..."

  if [ -z "${zfs_snapshot_filesystems}" ]; then
     snapshot_filesystems=`zfs list -H -o name -t filesystem`;
  else 
     snapshot_filesystems=${zfs_snapshot_filesystems};
  fi

  
 
  DATE=`env LC_ALL=C date -j "+%Y_%m_%d-%H:%M"`
  for fs in $snapshot_filesystems ; do 
   snap="${fs}@AUTO_${DATE}"
   logger -t $name "Creating snapshot $snap"
   zfs snapshot $snap
  done
}

drop_snapshots()
{
  echo "Destroying old automatic zfs snapshots"
 
  current_date=`date "+%s"`
  if [ -z "${zfs_snapshot_filesystems}" ]; then
     snapshot_filesystems=`zfs list -H -o name -t filesystem`;
  else
     snapshot_filesystems=${zfs_snapshot_filesystems};
  fi
 
  for fs in $snapshot_filesystems ; do
     snapshots=`zfs list -H -t snapshot -o name -r $fs |grep "^${fs}@AUTO_"`;
     for snap in $snapshots; do 
       creation_local=`zfs list -H -t snapshot -o creation $snap`
       creation_real=`date -j -f "%a %b %d %k:%M %Y" "${creation_local}" "+%s"`
       
       eval creation_minus=$(( ( ${current_date} - ${creation_real})/3600/24 ))
       if [ $creation_minus -gt $keep_days ]; then
         logger -t $name "Destroying snapshot $snap"
         zfs destroy $snap
       fi
     done
  done

}

load_rc_config $name
run_rc_command $*

There are three parameters for my script - zfs_snapshot_pool, zfs_snapshot_filesystems and zfs_snapshot_keep_days. First or second parameter may be used to specify pool (and all its FS for creating snapshots) or explicitly selecting necessary filesystems. Parameter fs_snapshot_keep_days determines when snapshot is considered outdated.
This simple script is supposed to make my life more comfortable...

Is our favourite government fucken crazy?

It's not a dream of some moron, it's real bill. Hail to Mr. Mikhalkov and Mr. Putin! It seems some ancient morons created quite idiotic law...
1) We have to pay 1 % from the cost of CD/DVD disks, Hard drives and other "data stores" (материальные носители) for some organization which distribute this money among authors.
2) This organization is not even formed.
3) And what if I use my flash stick to backup my home directory or to carry my own presentation or even to setup Ubuntu or FreeBSD on it?
5) And if I've paid this taxes in the store, could I freely distribute copies of some good film (not Mikhalkov's one, but something really interesting)? It seems not. Some contradiction...

If I buy a new flash stick, it will be more expensive by 10-30 roubles (about 30 cents - $1). Not much. But if we summ all these money, we have a good amount to steal.

Hail to our government! The next thing we need is a tax on the amount of trees in the garden. And these taxes we will spread between.... let's suppose... agronomists (they created seeds for you... You might have bought these seeds but who cares).

Chromium was added to FreeBSD ports tree

Chromium was added to FreeBSD ports tree. I'm going to try it... Currently I use Konqueror, but it is not always perfect. And Firefox wants too much memory, works quite slow and (the most annoying for me) doesn't look good with KDE :)

Objects have failed

Interesting view on object-oriented programming languages. Full text is here. I was always wondering how could someone do such a ugly language as C++ from such a nice language as C. The author goes further and says that "programmer in Fortran can write a Fortran program in any language", or something like that. He describes different problems of modern object-oriented languages and he seems to be right. I doubt that FOL-based or LISP-like languages will be successful as general-purpose languages in this decade... However, it would be interesting to see their further develoment.

Unix as literature

I've just got an excellent link from my workmate Oleg: The Elements Of Style: UNIX As Literature( the same text in Russian, original is better). It's rather interesting: a man with humanitarian education finds Unix more simple and effective then Windows NT (the article dates to 1999). I think I should mention this article to our students. They complain of Unix-like systems being too uncomfortable for them :)

KDE 4.5.1 - Konqueror is a bit better

I've just updated my FreeBSD desktop. I haven't noticed big changes in KDE 4.5.1 yet, however, some small updates really matter. Now we have useful adblock-like feature in konqueror - I can block any frame/image I like and use some public adblock lists. However, konqueror can't block Flash objects. I'd like it to block them too...
And as always, Google Buzz doesn't work with konqueror. It's not suitable.
I'd like to try rekonq 0.6, but there is only 0.5 version in ports tree... WebKit-based browser it seems should be better in JavaScript processing.

One interesting post about ORM

I've just found one interesting post about ORM - you may be interested in it. This post is rather old, but it is quite adequate even now. The link is here . Russian translation may be found at citforum.

OpenIndiana oi_147

Today we finally got the first release of OpenIndiana - new Illumos distribution, comparable to OpenSolaris binary distribution. I've just set it up and looked throw. Some interesting new things after my migration to FreeBSD (at times of OpenSolaris dev build 134) - interesting thing in zfs:
zfs diff:

#  zfs diff -F  rpool/ROOT/openindiana@install rpool/ROOT/openindiana
M       /       /
M       /       /var/pkg
M       /       /var/cache/cups
M       /       /var/cache/cups/rss
M       /       /var/adm
M       /       /dev
M       /       /etc
M       /       /var/cache/gdm
M       /       /var/svc/log
M       /       /etc/gconf/gconf.xml.defaults
M       /       /var/tmp
M       /       /etc/rc2.d

It's quite convinient, however it would be really cool to make zfs diff on particular files :) Other things include DTrace udp and tcp probes and quite sensible improvements in NWAM. At least, it is usable now...

Why do I dislike ORM?

"Any problem in computer science can be solved with another level of indirection"... Sometimes we receive too much indirection levels.
Let's try. First time I use psql:

> \d employees 
                                  Table "public.employees"
  Column  |          Type          |                       Modifiers                        
----------+------------------------+--------------------------------------------------------
 id       | integer                | not null default nextval('employees_id_seq'::regclass)
 name     | character varying(255) | 
 surname  | character varying(255) | 
 dept     | character(3)           | 
 phone    | character varying(15)  | 
 hiredate | date                   | 
 job      | character varying(255) | 
 salary   | numeric(9,0)           | 
Indexes:
    "employees_pkey" PRIMARY KEY, btree (id)

Start with query:

UPDATE employees set salary=salary*(1+2/100) WHERE salary<=5000

Will it work? Of course, no. The reason is that we have implicit conversion to integer.
Let's try once more:

UPDATE employees set salary=salary*(1.0+2.0/100) WHERE salary<=5000

Now it worked. Ok.
Let's try to do something similar in Hibernate:

        Query q;
        int updated;       
        q=em.createQuery("update Employees set salary=salary+:par*salary");
        q.setParameter("par", new Float(1.1));
        updated=q.executeUpdate();

And...

INFO: could not bind value '1.1' to parameter: 1; java.lang.Float cannot be cast to java.lang.Integer
Exception in thread "main" java.lang.ClassCastException: java.lang.Float cannot be cast to java.lang.Integer
        at org.hibernate.type.IntegerType.set(IntegerType.java:41)
        at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:136)
        at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:116)
        at org.hibernate.param.NamedParameterSpecification.bind(NamedParameterSpecification.java:38)
        at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:67)
        at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396)
        at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
        at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141)
        at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
        at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:49)
        at testhybernate.Main.updateData(Main.java:57)
        at testhybernate.Main.main(Main.java:33)

Ok. Let's try select * from employees; update it and then select * again;

...
em=emf.createEntityManager();
showData(em);
updateData(em);
showData(em);
...
protected static void showData(EntityManager em) {
        Iterator   it;
        Query q;
        List  result;
        em.flush();
        q=em.createQuery("from Employees");
        result=q.getResultList();
        it=result.iterator();
        while(it.hasNext()){
            Employees emp;
            emp=(Employees)it.next();
            System.out.println(emp);
        }        
    }
    private static void updateData(EntityManager em) {
        Query q;
        int updated;       
        q=em.createQuery("update Employees set salary=salary+1.1*salary");
        updated=q.executeUpdate();
        System.out.println("updated "+updated+ " entities");       
    }

And results are:

3 Steven King DIR 1986-01-01 Director 659
5 John Doe BAS 1985-07-10 Engineer 659
1 Elizabeth Bates SAL 2000-12-05 Manager 659
4 John Doe MNT 1989-06-01 Cleaner 659
6 Ken Green BAS 1994-07-02 Programmer 659
7 Sergey Spiridonov MNT 2002-07-02 System Administrator 659
updated 6 entities
3 Steven King DIR 1986-01-01 Director 659
5 John Doe BAS 1985-07-10 Engineer 659
1 Elizabeth Bates SAL 2000-12-05 Manager 659
4 John Doe MNT 1989-06-01 Cleaner 659
6 Ken Green BAS 1994-07-02 Programmer 659
7 Sergey Spiridonov MNT 2002-07-02 System Administrator 659

Oops... Select didn't notice update... O, shit!!!
I had some more experiments: we had forgotten that criteriaQueries exists (they are not implemented in Hibernate JPA, may be they are included in some GlassFish jar, but I'm tired).

It's really easier to create DAO with JDBC. And embedded SQL in C is much easier to understand and debug then all this ORM things... I like to work with database. I understand how it works.
Don't do my life easier. I can create effective SQL myself! I spend more time learning different crutches!!!

DTrace: it's really cool to have it working in FreeBSD

I've just managed to enable DTrace in FreeBSD and was playing a bit with it.
I've added necessary for i386 options to my kernel config:

options KDTRACE_HOOKS
options DDB_CTF

then run:

make kernel WITH_CTF=1 KERNCONF=MyKern

and received nothing good. The only working thing was dtrace -l. Everything other complained on uid_t in psinfo.d.

ctfdump showed a lot of stuff in kernel, but there was no uid_t. So, after adding the following line to my kernel config:

makeoptions DEBUG=-g

I got working dtrace!

So, now I can launch something like this...

# dtrace -qn 'syscall::open:entry { printf ("%d %s %s\n",pid,execname,copyinstr(arg0)) ; }'
1297 sh /lib/libncurses.so.8
1297 sh /lib/libc.so.7
1297 id /etc/libmap.conf
1297 id /var/run/ld-elf.so.hints
1297 id /usr/lib/libbsm.so.3
1297 id /lib/libc.so.7
1296 make /usr/ports/Mk/bsd.licenses.mk
1296 make /usr/ports/Mk/bsd.sites.mk
1298 sh /etc/libmap.conf
1298 sh /var/run/ld-elf.so.hints
1298 sh /lib/libedit.so.7
1298 sh /lib/libncurses.so.8
1298 sh /lib/libc.so.7
1298 sysctl /etc/libmap.conf
1298 sysctl /var/run/ld-elf.so.hints
1298 sysctl /lib/libc.so.7
1296 make .depend
1299 sh /etc/libmap.conf
1299 sh /var/run/ld-elf.so.hints
1299 sh /lib/libedit.so.7
1299 sh /lib/libncurses.so.8
1299 sh /lib/libc.so.7
1299 rm /etc/libmap.conf
1299 rm /var/run/ld-elf.so.hints
1299 rm /lib/libc.so.7
1299 rm .
1299 rm /usr/ports/textproc/libxml2/work
1299 rm libxml2-2.7.7

Sequels and their perception

I've recently read new Verbitsky's novel "The chronicles of Zarechensk" (Хроники Зареченска). The novel was rather good, but I was disappointed that author had decided to make sequel. And this sequel is hinted in first part: some new plot lines are created and "to be continued" is written in the end with bold font.
So, I tried to remember different interesting (or even outstanding) stories and their continuations. Let's take, for example, Lukianenko's "Night Watch": great book, interesting plot, and this books looks finished. But there are sequels "Day Watch", "Twilight Watch" and others. Some of them are very good too. I like several extracts of "Day Watch" very much. But the novelty is already lost, other book are not so thrilling. Let's take Perumov's "Godsdoom". It was exciting. This book gave background description of the whole universe. And later he spoiled impression describing this universe in all details (I think in about ten books :)). In this sense, the perfect example is "The Lord of the Rings". It's really cool that author had written "Silmarillion" for himself and didn't publish it. So, the whole book has fascinating background. It is filled with songs, legends, smell of the former epochs. A lot of facts and plot lines are just mentioned and not over-emphasized. But this is a complete universe. It is described in several books and it is whole. On the other hand we have bright "Dune". It is sometimes called the best sci-fi book of 20 century. And it deserves this title. Rich fantasy of Frank Herbert created extensive world with a few traits. Mentats, Bene gesserit witches, powerful clans fighting for Spice... And most of this became quite boring, I was fed up with this when I finished his sixth or seventh book.

SQL is Turing complete

I've found one interesting thing: with recursive CTE and windowing functions SQL is a Turing complete language. The proof maybe found here.

Installation on multipath device in Fedora... Is it a joke?

I've just read that in Fedora 14 installation on multipath device will be supported. I installed SXCE b97 on zpool containing multipath device two years ago. And after this piece of news we hear that Linux is superior OS.... For desktop it is quite good, but after this news I can't imagine something like Fedora on my server. I'm interested is it a case for every Linux distribution. Does CentOS or Debian support installation on multipath device?
I'm rather shocked with this information. Solaris is dying but two years ago it was much better then today's Linux. I hope, Illumos Project will give us a reborn server OS.

FreeBSD and wine... Progress or regression?

I've just got almost working wine on FreeBSD. To achieve this I've installed wine-1.1.30-1 from FreeBSD 8.0 package collection. Before this I unsuccessfully tried to make wine 1.2.1 work. It was impossible - it seems, later wine versions have some problems with DRI interoperability. So, after having enabled DRI on running (almost?) all DirectX games in wine on 8.1, you just get the following error:
> wine fallout2.exe
X Error of failed request: BadMatch (invalid parameter attributes)
Major opcode of failed request: 1 (X_CreateWindow)
Serial number of failed request: 397
Current serial number in output stream: 399
...
However, wine 1.1.30 works with enabled DRI. It seems, that all wine versions after 1.1.43 are broken.

RIP, Solaris...

What are you doing when you want to promote some product? You say to you friends, colleagues and acquaintances: use it, it's great! And they understand, that it maybe not great, but at least useful. And they use it. Sun understood it. And we had OpenSolaris. I worked as Sun Campus Ambassador and promoted it. And (Open)Solaris was a great OS: it had ZFS, DTrace, Zones, Projects and quite understandable RBAC system. And it was cool to use this OS. I wanted to find and report some bugs to make it better. I wished to port some soft to make Solaris pleasant OS. And what do we have now? Solaris 11 Express and end of OpenSolaris.
Blame on Oracle. They are going to spoil everything they touch. I didn't believe that they were able to shoot themselves in the leg. But they are talented enough to do it. With OpenSolaris Sun Solaris began new life. Without such high priority project it is doomed. Other OS are coming. Of course, one of them will be Linux (it's not ideal, but free and have a lot of features that Solaris will never have: support for almost all hardware you can imagine and all soft you may ever need), I hope one of them will be FreeBSD (at least we'll have ZFS and DTrace here, jails are good replacement for zones, especially after completion of new jail resources control project), Windows Server will be alive and prosperous. But AIX, HP/UX and Solaris will be dead. Because they were already dead. One of this great systems was near its resurrection. But its owner decided to bury it alive.
It's not good news, but expected one. Oracle was quite stupid to make other irrelevant steps. So, I think the next time I'll see Solaris 10 will be in museum. It will be among OS/2, Amiga OS and SCO Unix. Without new blood every project is doomed. OpenSolaris gave this giant new blood. But this is just a part of history now. Blame on Oracle.

Why I'm going to read Oracle course in university... on PostgreSQL

I used to like Oracle. They gave us one of the best DBMS. They made a lot of work on promoting Linux. They used a lot of open source software in their products and promoted Java...
But their greed is unnatural. It is more than Microsoft's greed.
1) Their DBMS costs too much. When our university tried to prolong their support, they said taht we had to pay taxes... because we hadn't done this earlier.
2) They don't allow to use their products in education for free. Even MS Academy program is free for our University.
3) They have done everything to kill Sun OpenSolaris. Damn them!
4) They prohibited to use Sun Solaris for free.
5) And now they are suing Google for using Java technologies in Android.
IMHO, this company is crazy and ill on immediate profit. It doesn't matter to them that their steps only makes potential customers nervous. What is next? Will they sue Apache or SpringSource for developing Java-related products or FreeBSD for using ZFS?
No, thanks. I will not promote their products at our campus.
FreeBSD and PostgreSQL is a real base for DBMS server, not Solaris and one overpriced DBMS...

ethernal war: snd_hda and headphones

Today I won in one minor battle between snd_hda and headphone output. I couldn't force my headphones work without some magic. The first thing I found is that headphone nid should be on the same association with speaker. In my verbose dmesg I had:

hdac0: Processing audio FG cad=2 nid=1...
hdac0: GPIO: 0xc0000003 NumGPIO=3 NumGPO=0 NumGPI=0 GPIWake=1 GPIUnsol=1
hdac0: nid 10 0x0221421f as 1 seq 15 Headphones Jack jack 1 loc 2 color Green misc 2
hdac0: Patching pin config nid=10 0x0221421f -> 0x0221431f
hdac0: nid 11 0x01a19023 as 2 seq 3 Mic Jack jack 1 loc 1 color Pink misc 0
hdac0: nid 12 0x01813221 as 2 seq 1 Line-in Jack jack 1 loc 1 color Blue misc 2
hdac0: nid 13 0x01114210 as 1 seq 0 Speaker Jack jack 1 loc 1 color Green misc 2
hdac0: nid 14 0x40f0f0f0 as 15 seq 0 Other None jack 0 loc 0 color Other misc 0
hdac0: nid 15 0x40f0f0f1 as 15 seq 1 Other None jack 0 loc 0 color Other misc 0
hdac0: nid 16 0x40f0f0f2 as 15 seq 2 Other None jack 0 loc 0 color Other misc 0
hdac0: nid 17 0x40f0f0f3 as 15 seq 3 Other None jack 0 loc 0 color Other misc 0
hdac0: nid 18 0x503301f0 as 15 seq 0 CD None jack 3 loc 16 color Unknown misc 1
hdac0: nid 33 0x01442170 as 7 seq 0 SPDIF-out Jack jack 4 loc 1 color Grey misc 1
hdac0: nid 34 0x81c42090 as 9 seq 0 SPDIF-in Fixed jack 4 loc 1 color Grey misc 0

The only advice I found was to put headphones nid (in my case, 10 nid) on the same association with speaker (nid 13). To do this you should add the following line to /boot/loader.conf:
int.hdac.0.cad2.nid10.config="as=1 seq=15"

After doing that I understood, that my configuration already was right in this sense. This hint didn't change anything. After reading manual for snd_hda and consulting with Oleg (http://www.oleg-sharoyko.net) I set misc for nid10 to 3 (zero bit says that jack detection is not implemented in hardware). Now I have working headphones and can listen to music without doing harm to my neighbors...
So, this time the force is in another magic line in loader.conf:
hint.hdac.0.cad2.nid10.config="misc=3"

The battle is won. But the war continues... I'd like to have separate mixer for headphones. When I make it functional, I'll write...

Participating in AboutBSD agregator

I'm going to participate in AboutBSD blogs agregator. I work with FreeBSD since 2005, and sometimes I'd like to share some general thoughts, impressions or may be practical advices concerning this OS...

Long live, Illumos...

It sounds great - a developers' community inspired by Garrett D'Amore is going to create "Fully Open" ON... When they have open codebase, it seems there will be a few parties glad to create there own binary distro based on this code. It's really necessary project, but I doubt - isn't it too late? This project would give a breathe of fresh air to OpenSolaris 3-5 years ago. But just for now they have a lot of work to do - replace and test internalization bits in libc, replace a lot of binary drivers and some parts of cryptographic system. Even if they are supported by several companies (including Nexenta), it will take significant time. I wish good luck to this project. I will closely look at it. But I have some doubts in its future...

Gnome OS sounds frightening

The first my though about Gnome OS (Looking at Gnome as operating system, http://www.omgubuntu.co.uk/2010/08/proposal-to-make-gnome-fully-fledged-os.html) was doubt about what does it mean. When people see Linux as "implementation detail", does it mean that Gnome would be more or less Linux-oriented? I fear that this simply means further ignoring other OS. For example, you have to apply about 800 patches to build Gnome on Solaris (and there it is the main desktop environment). Unpatched Gnome can't be build on FreeBSD. Further Linux orientation doesn't bring anything good for other OS. We already have many Linux crutches like policykit (why is it necessary, for example, on Solaris, where we have good RBAC) and hal (the only thing this monster created by Linux community doesn't do is cooking coffee for you) and I don't like this trend...
Let's remember, Linux is common Unix-like environment, but not the single one...

Illumos Project

It seems, that community OpenSolaris distro is coming... There is no much clarity yet. All details will be available in 2 days: http://www.illumos.org/projects/site/wiki/Announcement . It may be good news...
However, my former OpenSolaris desktop nowadays submits BSD stats (www.bsdstats.org) :)
As always, there are own advantages and disadvantages in FreeBSD, but at least I'm confident in its future.
About advantages: a great heap of FOSS (e.g, now I can read djvu, have working fbreader and even watch youtube in konqueror). About disadvantages: wine malfunctions, system doesn't want to boot from second drive in ZFS mirror with first drive detached (It boots with healthy pool or with second drive detached. OpenSolaris-b134 didn't want to boot with any drive detached). And I haven't tried anything Oracle-related yet...

FreeBSD 8.1

Now I'm downloading FreeBSD 8.1 DVD. Release was announced today. New features includes
1) addition of zfsloader
2) zpool support updated to version 14 (passthrough-x aclinherit maybe very useful on terminal server)
3) support for new SPARC and Power PC chips (does someone really use FreeBSD on SPARC? it looks strange even to meet SPARC nowadays :()
4) contributed software updates...
Link to news: http://www.freebsd.org/releases/8.1R/announce.html
Among these features I like new zpool version and presence of zfsloader...

It's interesting, will FreeBSD/8.1-i386 be quite stable with ZFS mirror pool, 2 GB of RAM and KDE? Let's test it...

FreeBSD monitoring tool

I like to have a single monitoring tool for all my needs: to monitor I/O, interface bandwidth, virtual memory usage, ip statistics and so on.
Fortunately, there is such tool in FreeBSD: systat:
# systat
/0 /1 /2 /3 /4 /5 /6 /7 /8 /9 /10
Load Average ||

/0% /10 /20 /30 /40 /50 /60 /70 /80 /90 /100
root idle XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
root idle XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
root idle XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
root idle XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
alp trackerd XXXXXXXXX
alp firefox-bi X

Now, as in vi, we could use command line to choose necessary statisctics: e.g. we can type ":io"
and then ":numbers" to look at I/O rates:
/0 /1 /2 /3 /4 /5 /6 /7 /8 /9 /10
Load Average ||

/0% /10 /20 /30 /40 /50 /60 /70 /80 /90 /100
cpu user|
nice|
system|X
interrupt|
idle|XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

ad4 ad6 ad12
KB/t tps MB/s KB/t tps MB/s KB/t tps MB/s
0,00 0 0,00 0,00 0 0,00 0,00 0 0,00
0,00 0 0,00 0,00 0 0,00 0,00 0 0,00


ar0 md0 cd0
KB/t tps MB/s KB/t tps MB/s KB/t tps MB/s
61,46 457 27,45 0,00 0 0,00 0,00 0 0,00
61,05 452 26,97 0,00 0 0,00 0,00 0 0,00

Or, we just can watch interface bandwidth with ":ifstat":
/0 /1 /2 /3 /4 /5 /6 /7 /8 /9 /10
Load Average |

Interface Traffic Peak Total
lo0 in 0,039 KB/s 0,039 KB/s 1,777 MB
out 0,039 KB/s 0,039 KB/s 1,777 MB

em0 in 0,403 KB/s 0,403 KB/s 26,535 MB
out 0,000 KB/s 0,000 KB/s 4,549 MB

I really like this tool!

Good news

I've just read that ZFS v15 was commited to FreeBSD-HEAD with 2 months before MFC to 8-STABLE (http://ivoras.sharanet.org/blog/tree/2010-07-13.all-welcome-zfs-v15-in-freebsd.html). It's really good news: we'll have faster, more stable ZFS version and it will support user quotas (lack of quotas was one of the things that prevented us from deploying it on our file server)... Great thanks to Martin for his work!

It's not enough - another piece of good news pc-sysinstall now supports packages. What does it mean? As I understand, this means one more step in side of easy binary package management in FreeBSD (http://www.freebsd.org/cgi/query-pr.cgi?pr=148584).

And last one. I've just read that NASA is going to use PostgreSQL to store data for quazy realtime monitoring system of Space Station (http://www.spinics.net/lists/pgsql/msg112022.html). I'm happy for PostgreSQL.

Updating ZFS-based FreeBSD installation to 8.1-RC2

Updating ZFS-based FreeBSD installation to 8.1-RC2 was rather easy, but time consuming: it took more then 2 hours.
1) freebsd-update couldn't fetch updates from first try. freebsd-update is ugly thing. Comparing checksums file-by-file, sorting lists of files on disk and other "lovely" habits... Source update would be faster.
2) this good tool erased my zfs booter, had to boot from 8.0 dvd and recover it.
3) However, i386 FreeBSD 8.0 runs on ZFS quite stable (768 Mb of memory, no any tuning - 160 Mb arc, kmem_size_max - 330 Mb, kmem_size - 256 Mb (defaults))

Moving data to FreeBSD

Now I decided to move from OpenSolaris to FreeBSD.
Just for now I emulate this procedure in VirtualBox, later I'll do it on real hardware...
1) Installed OpenSolaris 09.06 and updated it to b134, updated zfs pool to version 19, added mirror disk to pool. So, I have configuration identical to my workstation.
2) Splitted pool (used zpool split for this task), destroyed second pool (which appeared as result of split) and filled first sectors of disk with zeroes (dd if=/dev/zero of=/dev/rdsk/) to erase all signs of zfs pool.
3) Booted from FreeBSD dvd (i386) and made everything similar to http://wiki.freebsd.org/RootOnZFS/ZFSBootPartition. Some caveats: I didn't manage to get zpool.cache from first time, had to import/export pool to get it. At the end of the procedure I got zfs pool consisting from /dev/ad1s1.
4) After some time I managed to boot from my pool (made several errors during install, e.g. forgot about zpool.cache and accidentally wrote wrong rc.conf).
5) Booted in OpenSolaris and imported my pool under alternative /fbsd root. Create on the FreeBSD zfs pool filesystem /old and zfs send all data from OpenSolaris /export fs there.
6) Rebooted. Couldn't boot FreeBSD. So I booted from FreeBSD DVD, imported ZFS pool and moved /boot/zfs/zpool.cache to zpool's /boot/zfs/zpool.cache
7) Now I have running ZFS FreeBSD installation
TODO:
a) add second mirror drive (old OpenSolaris drive) to new ZFS pool and put boot code and loader there.
b) tune i386 FreeBSD for ZFS usage...

After completing these tasks I'm going to make this procedure on physical system... (Certainly, after creating backup...)

Leaving sinking ship

We could be very optimistic, but bad things really happen. So, after waiting for half a year after Sun end, I decided to migrate my home workstation from OpenSolaris to some other solid OS.
The choice is not evident. Solaris is good in several things:
- It supports commercial soft. And I need Adobe Flash, Oracle DBMS, recent Java version.
- It supports zones. Experiments with OS is very easy. Being a system administrator, I appreciate this.
- It has ZFS. Yes, I like using ZFS mirror on my desktop PC.
- It has DTrace and truss, which help me to answer questions "What does my OS do?".

I see several alternatives: Solaris 10, FreeBSD 8.1 and Debian. I use Ubuntu on my notebook and it proved to be quite unstable.
At first glance, best choice is Solaris 10 - you shouldn't change your habits a lot. But it's a trap. I don't believe Oracle any more after 2010.H1 has come to end :)
Solaris 10 also has quite old software and awful IIIMD, and package management from 90's.... So, it's not my way. I wouldn't consider using it even for server (because it is illegal now).

Debian is good choice. It's stable. Oracle and Flash work there. And JDK has native support. But migration would look like:
1) split ZFS mirror
2) install Debian on one part of mirror
3) move data there using zfs-fuse
4) create several md-devices from other part of mirror
5) move data there
6) add first disk to md-device
7) update fstab
8) update mbr...
9) forget about snapshots, dtrace and easy ways to monitor disk pool.

So, I'd like to try hard way - FreeBSD/x86.
- Using x86 system will allow me to use wine without tambourines.
- Adobe Flash will work by means of nspluginwrapper.
- I'll migrate my data by splitting ZFS pool, creating new one with lower Zpool version from half of pool (e.g. 14 version), importing data there by means of zfs send/receive and mounting it in FreeBSD LiveCD.
- OpenJDK in FreeBSD is quite fresh, I can migrate all my projects to it (already successfully tried).
- I really like this system and have a lot of experience in it, so let's try this scenario,
- One big question is Oracle. I know that Oracle XE works perfectly in FreeBSD. Trying to install Oracle 10g - is a challenging exercise :)

Just for now I'm going to emulate all this actions in VirtualBox. After finishing this work, I'm going to share my experience.