SQL - small mistakes, big impact

the last couple of days I was fighting with a database which became slower and slower and slower and now needed close to 24h to execute a rather simple query...

After working for hours on different parameters and tuning the database, including setting up a spare server to see if a dump and restore fixes the issues I got the time down to 5 minutes.

Sounds good?

well not really since I needed an execution in the range of a couple of seconds. So I actually looked at the sql statement which caused issues and found a gigantic error in it...

the statement:


select * from BIN where bin_id not in ( SELECT bin_id from SPECTRA where sample_id = 424105 AND SPECTRA.bin_id = BIN.bin_id)


and the correct statement would be:


select * from BIN a where a.bin_id not in ( SELECT bin_id from SPECTRA where sample_id = 424105 and bin_id is not null) ORDER BY retention_index ASC


the problem?


SPECTRA.bin_id = BIN.bin_id


this little part is totally wrong in the whole statement and causes a seq scan over a table with 70 Million entries against a table with 6000 entries. It does nothing for the queries and I guess it was left over from a copy/paste accident 6 years ago when I wrote this script originally.

let's compare some numbers:

explain before:

Seq Scan on bin (cost=0.00..459513.42 rows=3044 width=1290)
SubPlan
Filter: (NOT (subplan))
-> Bitmap Heap Scan on spectra (cost=75.20..75.42 rows=2 width=8)
Recheck Cond: ((sample_id = 424105::double precision) AND (bin_id = ($0)::double precision))
-> BitmapAnd (cost=75.20..75.20 rows=2 width=0)
-> Bitmap Index Scan on spectra_sample_id (cost=0.00..18.99 rows=5696 width=0)
-> Bitmap Index Scan on spectra_binid (cost=0.00..56.18 rows=17658 width=0)
Index Cond: (sample_id = 424105::double precision)
Index Cond: (bin_id = ($0)::double precision)

explain after:

Sort (cost=1765.47..1766.23 rows=3044 width=1290)
Sort Key: retention_index
-> Seq Scan on bin a (cost=647.21..1747.86 rows=3044 width=1290)
Filter: (NOT (hashed subplan))
SubPlan
-> Index Scan using spectra_sample_id on spectra (cost=0.00..646.42 rows=3138 width=8)
Index Cond: (sample_id = 424105::double precision)
Filter: (bin_id IS NOT NULL)


in real world number this translates to 24 hours before statement compared to 4 seconds after statement...
...

maven2 - choose dependcies based on platform

today I run into an old friend. I was compiling my stuff happily under oxs and building eclipse plugins with maven2 and the psteclipse plugin, which I by now more or less rewrote and adapted to my project to perform as good as possible. Anyway, basically I encountered the issue that eclipse ships with different dependencies under osx/win32 and linux.

So promptly it fails to compile under linux...

solution for this problem?

maven2 profiles, your best friend for cross platform development. They actually allow you define dependencies based on the current platform.

Example for out case would be:


<profiles>
<profile>
<id>dev-windows</id>

<dependencies>
</dependencies>

<activation>
<activeByDefault>true</activeByDefault>
<os>
<family>windows</family>
</os>
</activation>
</profile>
<profile>
<id>dev-mac</id>
<dependencies>
<dependency>
<groupId>psteclipse</groupId>
<artifactId>org.eclipse.swt.cocoa.macosx</artifactId>
<version>3.5.1</version>
<scope>provided</scope>
</dependency>

</dependencies>

<activation>
<activeByDefault>false</activeByDefault>
<os>
<family>mac</family>
</os>
</activation>
</profile>
<profile>
<id>dev-linux</id>

<dependencies>
<dependency>
<groupId>psteclipse</groupId>
<artifactId>org.eclipse.swt.gtk.linux.x86</artifactId>
<version>3.5.1</version>
<scope>provided</scope>
</dependency>
</dependencies>

<activation>
<activeByDefault>false</activeByDefault>
<os>
<name>linux</name>
</os>
</activation>
</profile>
</profiles>


And sets a different library for osx and linux. Sweet!

hibernate - configure caching

since the hibernate tools for eclipse need to have the caching enabled, something I'm not the biggest fan off for several reason.

Well


<property name="hibernate.cache.provider_class">
org.hibernate.cache.EhCacheProvider
</property>

<property name="hibernate.cache.use_second_level_cache">true</property>
<property name="hibernate.cache.use_query_cache">true</property>


so this makes the binbase configuration run in the eclipse tools.

Log4J and eclipse

sometimes you want to use the log4j system instead of the internal eclipse provide logging framework.

The use of this is rather simple and straight forwarded. You only need to register an ILogListener at the platform object and implement it.

An example would be:


package edu.ucdavis.genomics.metabolomics.binbase.gui.swt.logging;

import org.apache.log4j.Logger;
import org.eclipse.core.runtime.ILogListener;
import org.eclipse.core.runtime.IStatus;

/**
* used to forward eclipse logging to log4j
*
* @author wohlgemuth
*
*/
public class Log4JListener implements ILogListener {

static Logger LOGGER = Logger.getLogger(Log4JListener.class);

public void logging(IStatus status, String plugin) {
if (status.getSeverity() == IStatus.WARNING) {
if (status.getException() == null) {
LOGGER.warn(status.getMessage() + "(" + status.getCode() + ")");
} else {
LOGGER.warn(status.getMessage() + "(" + status.getCode() + ")",
status.getException());
}
} else if (status.getSeverity() == IStatus.ERROR) {
if (status.getException() == null) {
LOGGER
.error(status.getMessage() + "(" + status.getCode()
+ ")");
} else {
LOGGER.error(
status.getMessage() + "(" + status.getCode() + ")",
status.getException());
}

} else if (status.getSeverity() == IStatus.INFO) {
if (status.getException() == null) {
LOGGER.info(status.getMessage() + "(" + status.getCode() + ")");
} else {
LOGGER.info(status.getMessage() + "(" + status.getCode() + ")",
status.getException());
}

}
}

}



and to register it


this.listener = new Log4JListener();
Platform.addLogListener(this.listener);



Eclipse SWT 3.5 and an annoying change...

after I moved a part of my software over to eclipse 3.5 to take advantage of bug fixes and new features I noticed that some of my dialogs don't work anymore.

For example the login dialog has 2 fiels. A username and a password. These fields are defiend like this:


password = new Text(content, SWT.BORDER | SWT.PASSWORD);
user = new Text(content, SWT.BORDER);

user.addKeyListener(this);
password.addKeyListener(this);


and in the key listener I validate the inputs. But with version 3.5 key listener won't work on password fields anymore and I didn't find any documentation about it. So instead you need to use a modify listener now


password.addModifyListener(new ModifyListener() {

public void modifyText(ModifyEvent e) {
}
});


duplicating a disk under OSX

since my laptop has no cd drive anymore I have no the need of cloning all my cd's with my mac pro.

How to do this?

1. Insert CD/DVD source
2. Fire up a Terminal, you can then determine the device that is you CD/DVD drive using the following command:

$ drutil status
 Vendor   Product           Rev
 MATSHITA DVD-R   UJ-835E   GAND

           Type: DVD-ROM              Name: /dev/disk1
      Cur Write:    8x DVD          Sessions: 1
      Max Write:    8x DVD            Tracks: 1
   Overwritable:   00:00:00         blocks:        0 /   0.00MB /   0.00MiB
     Space Free:   00:00:00         blocks:        0 /   0.00MB /   0.00MiB
     Space Used:  364:08:27         blocks:  1638627 /   3.36GB /   3.13GiB
    Writability:
      Book Type: DVD-ROM

3. Umount the disk with the following command:

$ diskutil unmountDisk /dev/disk1
Disk /dev/disk1 unmounted

4. Create the ISO file with the dd utility (may take some time):

$ dd if=/dev/disk1 of=file.iso bs=2048

5. Test the ISO image by mounting the new file (or open with Finder):

$ hdid file.iso

taken from here

OSX - upgraded my macbook pro to an SSD and the differences

After long considerations I updated my macbook pro to a SSD, from corsair. Since the price seemed to be not to outragios.

Now the tests are clear:

SSD

Drive Type CORSAIR CMFSSD-128GBG2D

Disk Test    173.68   
    Sequential    132.15   
        Uncached Write    143.67    88.21 MB/sec [4K blocks]
        Uncached Write    101.53    57.44 MB/sec [256K blocks]
        Uncached Read    94.19    27.56 MB/sec [4K blocks]
        Uncached Read    351.79    176.81 MB/sec [256K blocks]
    Random    253.29   
        Uncached Write    121.43    12.85 MB/sec [4K blocks]
        Uncached Write    196.26    62.83 MB/sec [256K blocks]
        Uncached Read    1503.47    10.65 MB/sec [4K blocks]
        Uncached Read    556.60    103.28 MB/sec [256K blocks]


Drive Type ST9500325AS

 Disk Test    43.26  
    Sequential    80.53  
        Uncached Write    78.89    48.44 MB/sec [4K blocks]
        Uncached Write    68.76    38.91 MB/sec [256K blocks]
        Uncached Read    77.68    22.73 MB/sec [4K blocks]
        Uncached Read    104.42    52.48 MB/sec [256K blocks]
    Random    29.57  
        Uncached Write    10.43    1.10 MB/sec [4K blocks]
        Uncached Write    73.40    23.50 MB/sec [256K blocks]
        Uncached Read    64.02    0.45 MB/sec [4K blocks]
        Uncached Read    98.74    18.32 MB/sec [256K blocks]

I think these result's speak for them self!