Oracle Dispatcher startup after shutdown

There is an "alter system shutdown immediate 'D000'" command to shutdown dispatcher in Oracle. However, how will you launch disppatcher after shutdown? It's evident - with "alter system set dispatchers='...'" :), where necessary config may be get from v$parameter. It seems that Oracle developers hate their users...

PostgreSQL quals in rewritten query plans

I've just examined PostgreSQL query debug output from the next query :

SELECT * from low_paid_emps_r;

where low_paid_emps_r is defined as

# SELECT definition from pg_views  where viewname like '%emps%';
                           definition     
-----------------------------------------------------
SELECT employees.employee_id, employees.first_name, employees.last_name, employees.email, employees.phone_number, employees.hire_date, employees.job_id, employees.salary, employees.commission_pct, employees.manager_id, employees.department_id FROM employees WHERE (employees.salary < (5000)::numeric);

In particular, I wished to find where is my where expression. It looks like the following.

   :quals 
               {OPEXPR 
               :opno 1754 
               :opfuncid 0 
               :opresulttype 16 
               :opretset false 
               :opcollid 0 
               :inputcollid 0 
               :args (
                  {VAR 
                  :varno 3 
                  :varattno 8 
                  :vartype 1700 
                  :vartypmod 524294 
                  :varcollid 0 
                  :varlevelsup 0 
                  :varnoold 3 
                  :varoattno 8 
                  :location -1
                  }
                  {FUNCEXPR 
                  :funcid 1740 
                  :funcresulttype 1700 
                  :funcretset false 
                  :funcformat 2 
                  :funccollid 0 
                  :inputcollid 0 
                  :args (
                     {CONST 
                     :consttype 23 
                     :consttypmod -1 
                     :constcollid 0 
                     :constlen 4 
                     :constbyval true 
                     :constisnull false 
                     :location -1 
                     :constvalue 4 [ -120 19 0 0 ]
                     }
                  )
                  :location -1
                  }
               )
               :location -1
               }

So, we have here <:

# SELECT oprname from pg_operator where oid=1754;
 oprname 
---------
<
(1 row)

The first argument is 8th attribute of our relation (salary). The other piece is ::numeric conversion:

# select proname from pg_proc where oid=1740;
 proname 
---------
 numeric
(1 row)

Ok, we reconstructed salary < numeric (Const) part. Let's find our constant.

# select typname from pg_type where oid=23;
 typname 
---------
 int4
(1 row)

It's int4. And constvalue as we can see from source code is a Datum. It seems form postgres.h that int4 is int32 and represented as:

#define DatumGetInt32(X) ((int32) GET_4_BYTES(X))

Now we can reconstruct out integer from ":constvalue 4 [ -120 19 0 0 ]" as 19*256+(256-120)=5000... Not evident; I possibly couldn't do this analysis not knowing original statement...

We've made this!

Hurray! We've catched up with China! Finally we have our own Great Russian Firewall!!!

Temperature monitoring with OpenNMS and brocade FC switch

We are expanding! And we have to monitor temperature level in our new data center. So, we have:

1. existing OpenNMS monitoring system,
2. Brocade 300 SAN FC switch (or something very similar).

Our task is the same: to monitor temperature and send SMS when it's too high. Later part was already discussed.
The above mentioned FC switch has three temperature sensors, which data is available by SNMP (oids .1.3.6.1.4.1.1588.2.1.1.1.1.22.1.4.1 - .1.3.6.1.4.1.1588.2.1.1.1.1.22.1.3). The only problem is that OpenNMS doesn't know about them. Let's teach it!
At first, we have to modify datacollection-config.xml and add to "groups" section:

<group  name="brocade-temperature" ifType="ignore">
         <mibObj oid=".1.3.6.1.4.1.1588.2.1.1.1.1.22.1.4" instance="1" alias="temperature1" type="integer" />
         <mibObj oid=".1.3.6.1.4.1.1588.2.1.1.1.1.22.1.4" instance="2" alias="temperature2" type="integer" />
         <mibObj oid=".1.3.6.1.4.1.1588.2.1.1.1.1.22.1.4" instance="3" alias="temperature3" type="integer" />
      </group>

I know, "temperature[1-3]" names are ugly, but I didn't care at first and now when the whole thing is working I don't want to change anything :)
And modify "Brocade FC Switches" systemDef in systems section:

     <systemDef name="Brocade FC Switches">
        <sysoidMask>.1.3.6.1.4.1.1588.</sysoidMask>
        <collect>
          <includeGroup>brocade-temperature</includeGroup>
          <includeGroup>brocade-switch-fctable</includeGroup>
        </collect>
     </systemDef>

Now we are ready and can define thresholds. To be sure that requested parameters are gathered you can look at rrdRepository. Path to the repository is specfied in rrdRepository property of datacollection-config resource (look at top of datacollection-config.xml). If you have temperature[1-3].jrb files in ${rrdRepository}/${NodeID} and these files contains some data, everything is OK. NodeId is displayed in URL, when you look at node (/opennms/node.jsp?node=353) or you can query "node" table in OpenNMS database:

SELECT nodeid from node where nodelabel='your label';

To be sure that temperature[1-3].jrb contains correct data, you can dump it (your version of jrobin-*.jar file may be different):

# cd ${rrdRepository}/${NodeID}
# echo "dump temperature1.jrb" |  java -jar ${OPENNMS_HOME}/opennms/lib/jrobin-1.5.12.jar

However, OpenNMS still doesn't know how to display this data. Let's help it. To define new graph we will add the following parts to snmp-graph.properties. In the begining of file we have to add reports names to reports definition:

reports=mib2.HCbits, mib2.bits, mib2.percentdiscards, mib2.percenterrors, \
...
brocade.switch.temperature1, brocade.switch.temperature2, brocade.switch.temperature3, \
...

and define reports later (here only one report is shown, other two are essentially the same, just change [Tt]emperature1 to [Tt]emperature[23]):

report.brocade.switch.temperature1.name=Brocade switch temperature1
report.brocade.switch.temperature1.columns=temperature1
report.brocade.switch.temperature1.type=nodeSnmp
report.brocade.switch.temperature1.command=--title="Brocade switch temperature1" \
 --vertical-label="degrees celsius" \
 DEF:temperature1={rrd1}:temperature1:AVERAGE \
 AREA:temperature1#0000ff:"Temperature1" \
 GPRINT:temperature1:AVERAGE:" Avg \\: %8.2lf %s" \
 GPRINT:temperature1:MIN:"Min \\: %8.2lf %s" \
 GPRINT:temperature1:MAX:"Max \\: %8.2lf %s\\n" 
.. 

Now after "service opennms restart" we'll get pretty graphs if we build graph based on "Node-level Performance Data" resources for brocade node.

P.S. And lastly I just must give you a link to a good document concerning data collection configuration in OpenNMS and another one concerning SNMP configuration.

Amazing new world with pkgng

I really like it. It's a pure magic... It's pkgng. So, my task is to deploy about 20-30 servers (jails) for practical courses in PostgreSQL. So, every student will have its own unique DBMS to crack and crash. Two years ago I said students to do:

# cd /usr/ports/databases/postgresql84-server/ && make install

So, for half a lesson we monitored building of postgresql... Cool. Last year I've prebuilt necessary packages and put them in /var/packages/. It was faster, a student just had to do:

# cd /var/packages
# pkg_add postgresql90-server*tbz

or something similar. But later we had to update database in one of tasks. Dealing with packages (and scripts to copy them to every jail was not pleasant. I even started to think about OpenVZ, debian and apt... However, i dislike the way of treating postgresql in Debian. It is overcomplicated by pg_ctlcluster, separation of config files from $PGDATA... However, this time I looked at pkgng. And so far I happy. To create package server I just installed pkg from ports:

# cd /usr/ports/ports-mgmt/pkg && make install

Added WITH_PKGNG=yes to /etc/make.conf, run pkg2ng, created required packages by

cd /usr/ports/databases/postgresql91-server/ && make package-recursive

Now I could install nginx, copy /usr/ports/packages/* to /data, run

# pkg repo /data/

share /data with nginx with the following location in server section:

location / {
            root   /data;
            autoindex on;
        }

Repository server is ready. On client I downloaded pkg-1.0.r6.tbz from freebsd package server (old one, with pkg_install packages), installed it and could use repository:

# pkg_add pkg-1.0.r6.tbz

Converted packages db to new format with pkg2ng, changed PACKAGESITE in pkg.conf to my server:

# cp /usr/local/etc/pkg.conf.sample /usr/local/etc/pkg.conf
# vi /usr/local/etc/pkg.conf

Run:

 
# pkg update
# pkg rquery -e '%n ~ pos*' '%n-%v'
postgresql-client-9.1.5
postgresql-contrib-9.1.5
postgresql-plperl-9.1.5
postgresql-server-9.1.5

But when I tried to install postgresql-server, I was asked to update pkg:

# pkg install postgresql-server-9.1.5
Updating repository catalogue
Repository catalogue is up-to-date, no need to fetch fresh copy
New version of pkg detected; it needs to be installed first.
After this upgrade it is recommendedthat you do a full upgrade using: 'pkg upgrade'

The following packages will be installed:

 Upgrading pkg: 1.0.r6 -> 1.0

The installation will free 12 MB

1 MB to be downloaded

Proceed with installing packages [y/N]: n

# pkg install pkg
Updating repository catalogue
Repository catalogue is up-to-date, no need to fetch fresh copy
New version of pkg detected; it needs to be installed first.
After this upgrade it is recommendedthat you do a full upgrade using: 'pkg upgrade'

The following packages will be installed:

 Upgrading pkg: 1.0.r6 -> 1.0

The installation will free 12 MB

1 MB to be downloaded

Proceed with installing packages [y/N]: y
pkg-1.0.txz                                                                                                                                                                                      100% 1686KB   1.7MB/s   1.7MB/s   00:00    
Checking integrity... done
Upgrading pkg from 1.0.r6 to 1.0... done

After this I could quickly install postgresql:

# pkg install postgresql-server

The only inconvenience is a bootstrap problem. The bootstrap tool is necessary requirement to make work with pkg easier...

Notes on configuring two-nodes proxmox cluster with drbd-backed storage

We had a task to deploy two new visualization servers with possibility of live migration and high availability data. The second means that in case of physical server failure you don't want faulted VMs to be powered up automagically on another node, but just that you can do it by hand in five minutes.

We decided to use proxmox VE 2, because it's free, we have experience of maintaining proxmox 1.9 systems and because it supports live migration without shared storage.

So, we configured two nodes with 4 additional LVM volume groups each: for VZ data for each node (n1vz with one lvm mounted on first node on /var/lib/vz and n2vz with one volume mounted on /var/lib/vz/ on second, n1kvm and n2kvm as VM disk storage on each node, n1kvm is used by VMs running normally on first node, n2kvm - by VMs running on second node). 4 DRBD volumes with primary-primary configuration was created for each of 4 volume groups. Using separate pair of drbd devices for VM's disks makes split brain recovery easier, as explained here. And note, we can't use drbd-mirrored (quazy-shared) disk for VZ storage, because final step of VZ migration includes "rm -rf" after rsyncing container private area.

In such configuration we can do live migration of KVM VMs and VZ. Also we have copy of each VM and VZ for emergencies (falling of one node).

Some difficulties we met were related to LVM and DRBD startup ordering. First one was the following: LVM locked drbd backing storage and drbd couldn't use them. It was solved with correct filter in lvm.conf. The other one was more difficult. Physical volumes n1vz and n2vz available over DRBD couldn't be mounted normally - they should be mounted after initial system startup. Usually firstly starts lvm (and init script makes vgchange -ay, activating volume groups), then drbd, and now we have additional VG, but they are not active.

To solve this problem we are supposed to use hearthbeat. But I am too lazy to study it. So I adopted things more familiar to me - automounter (autofs) to mount /var/lib/vz and udev to make volume groups available on drbd* device appearance. I've added "/- /etc/auto.direct" line to /etc/auto.master and created /etc/auto.direct file, containing:

/var/lib/vz              -fstype=ext4            :/dev/mapper/n1vz-data

Configuration of udev consisted from creation of /etc/udev/rules.d/80-drbd-lvm.rules file, containing:

ACTION=="add|change", SUBSYSTEM=="block",KERNEL=="drbd*", RUN+="/bin/sh -c /sbin/lvm vgscan; /sbin/lvm vgchange -a y'"

I consider this more elegant then just including "vgchange -a y && mount ..." in rc.local.

php-fpm troubles

Life is boring without troubles. We were almost ready to push new servers in production, however, php-fpm started falling randomly with the following message:

Jun  1 04:14:36 srv2 kernel: [566115.463835] php5-fpm[29696]: segfault at 0 ip 00007f3b191e5558 sp 00007fff70f193c8 error 4 in libc-2.15.so[7f3b190b3000+1b3000]
Jun  1 04:14:36 srv2 kernel: [566115.463847] php5-fpm/29696: potentially unexpected fatal signal 11.
Jun  1 04:14:36 srv2 kernel: [566115.463850] 
Jun  1 04:14:36 srv2 kernel: [566115.463851] CPU 5 
Jun  1 04:14:36 srv2 kernel: [566115.463853] Modules linked in: vesafb psmouse i7core_edac edac_core ioatdma dca serio_raw joydev mac_hid lp parport usbhid hid e1000e megaraid_sas
Jun  1 04:14:36 srv2 kernel: [566115.463868] 
Jun  1 04:14:36 srv2 kernel: [566115.463871] Pid: 29696, comm: php5-fpm Not tainted 3.2.0-24-generic #39-Ubuntu Supermicro X8DTT-H/X8DTT-H
Jun  1 04:14:36 srv2 kernel: [566115.463876] RIP: 0033:[<00007f3b191e5558>]  [<00007f3b191e5558>] 0x7f3b191e5557
Jun  1 04:14:36 srv2 kernel: [566115.463882] RSP: 002b:00007fff70f193c8  EFLAGS: 00010206
Jun  1 04:14:36 srv2 kernel: [566115.463885] RAX: 0000000000000000 RBX: 00007f3b1b53f000 RCX: 0000000000000011
Jun  1 04:14:36 srv2 kernel: [566115.463888] RDX: 0000000000000066 RSI: 0000000000af8b15 RDI: 0000000000000000
Jun  1 04:14:36 srv2 kernel: [566115.463890] RBP: 0000000002705b08 R08: 0000000000000011 R09: 0000000000000000
Jun  1 04:14:36 srv2 kernel: [566115.463893] R10: eea633fc2a689ca0 R11: 00007f3b192344d0 R12: 0000000000000001
Jun  1 04:14:36 srv2 kernel: [566115.463896] R13: 0000000000000000 R14: 0000000000000000 R15: 0000000002705bd0
Jun  1 04:14:36 srv2 kernel: [566115.463899] FS:  00007f3b1b531700(0000) GS:ffff8803332a0000(0000) knlGS:0000000000000000
Jun  1 04:14:36 srv2 kernel: [566115.463902] CS:  0010 DS: 0000 ES: 0000 CR0: 0000000080050033
Jun  1 04:14:36 srv2 kernel: [566115.463904] CR2: 0000000000000000 CR3: 0000000329cb2000 CR4: 00000000000006e0
Jun  1 04:14:36 srv2 kernel: [566115.463907] DR0: 0000000000000000 DR1: 0000000000000000 DR2: 0000000000000000
Jun  1 04:14:36 srv2 kernel: [566115.463910] DR3: 0000000000000000 DR6: 00000000ffff0ff0 DR7: 0000000000000400
Jun  1 04:14:36 srv2 kernel: [566115.463913] Process php5-fpm (pid: 29696, threadinfo ffff88025be20000, task ffff880326120000)
Jun  1 04:14:36 srv2 kernel: [566115.463916] 
Jun  1 04:14:36 srv2 kernel: [566115.463917] Call Trace:

After examining core dump I got the following backtrace:

(gdb) bt
#0  __strstr_sse42 (s1=0x0, s2=) at ../sysdeps/x86_64/multiarch/strstr.c:175
#1  0x0000000000736d13 in fpm_status_handle_request () at /home/alp/build/php5-5.3.10/sapi/fpm/fpm/fpm_status.c:128
#2  0x000000000042b4ab in main (argc=11237155, argv=0x0) at /home/alp/build/php5-5.3.10/sapi/fpm/fpm/fpm_main.c:1809

Firstly I started to blame libc. However, nothing else crashed. I rebuilt php from sources. The result was the same. So after getting magic kick from my chief I sighed deeply and looked at php5-5.3.10/sapi/fpm/fpm/fpm_status.c:

               /* full status ? */
                full = SG(request_info).request_uri && strstr(SG(request_info).query_string, "full");
                short_syntax = short_post = NULL;
                full_separator = full_pre = full_syntax = full_post = NULL;
                encode = 0;

It seems to be just a copy-paste or something like that. It was a piece of code from path processing php-fpm status. Firstly, I've disabled php-fpm monitoring and didn't have new segfaults since then. So, I patched the file with the following:

 
--- php5-5.3.10/sapi/fpm/fpm/fpm_status.c       2012-06-01 04:00:43.492744472 -0400
+++ php5-5.3.10/sapi/fpm/fpm/fpm_status.c       2012-06-01 04:03:59.233040497 -0400
@@ -125,7 +125,7 @@
                }
 
                /* full status ? */
-               full = SG(request_info).request_uri && strstr(SG(request_info).query_string, "full");
+               full = SG(request_info).query_string && strstr(SG(request_info).query_string, "full");
                short_syntax = short_post = NULL;
                full_separator = full_pre = full_syntax = full_post = NULL;
                encode = 0;

and recompiled php... It seems I caught one more bug. Interesting thing to note is that no one else hited it. I've just reported bug 62205 and PHP team reacted quickly.

P.S. The same error appeared in two more places in fpm_status.c. The above mentioned bug report is resolved now, fix was committed to php head.