[Bioperl-l] bioperl-db issues

[Hilmar Lapp]

On 2/21/06, Chris Fields <cjfields at uiuc.edu> wrote:
> [...]
> I find it odd that it worked well back in December and doesn't work now.  I
> updated bioperl and bioperl-db from CVS since then, so have there been any
> changes that may have caused this?  I noticed a few changes here and there.

The changes were fixes to retrieve the rank on persistent annotation
objects (it was only stored before, but never retrieved). Neither the
SpeciesAdaptor nor any of the taxonomy queries was affected by this.

>
> Here's what I have tried thus far:
>
> 1) I reinstalled MySQL.  I thought it might be that I had my database on a
> partitioned drive, so I reinstalled on the main drive.
>
> 2) I rebuilt the database from scratch, loading taxonomy fresh, loaded the
> schema, and got the same error when loading (hanging on SpeciesAdaptor.
> Tried ANALYZE:
> ------------------------------------
> mysql> ANALYZE TABLE taxon;
> +----------------+---------+----------+----------+
> | Table          | Op      | Msg_type | Msg_text |
> +----------------+---------+----------+----------+
> | bioseqdb.taxon | analyze | status   | OK       |
> +----------------+---------+----------+----------+
> 1 row in set (0.42 sec)
>
> mysql> ANALYZE TABLE taxon_name;
> +---------------------+---------+----------+----------+
> | Table               | Op      | Msg_type | Msg_text |
> +---------------------+---------+----------+----------+
> | bioseqdb.taxon_name | analyze | status   | OK       |
> +---------------------+---------+----------+----------+
> 1 row in set (0.36 sec)

I'm not sure but you may have to analyze all tables.

>
> mysql>
> ------------------------------------
> so that's fine.
>
> 3) Using EXPLAIN table:
> ------------------------------------
> mysql> EXPLAIN taxon;

Note that you wouldn't use EXPLAIN on a table but on a query instead.
I.e., copy&paste the offending query into the mysql editor, prefix it
with EXPLAIN and then see what the results are. It should show whether
the indexes are being used properly.

Most likely it doesn't use one of the idnexes that it should be using
but does a full table scan instead. The explain plan should pinpoint
that.

BTW you can also use this to reconfirm the command line observation
about the query being slow - it should 'hang' in the mysql shell as
well. If it doesn't then there is something else going on. (if the
placeholders pose a problem replace them with the actual values as
given in the log)

> [..]
> SpeciesAdaptor: binding UK column 1 to "scientific name" (name_class)
> SpeciesAdaptor: binding UK column 2 to "208963" (ncbi_taxid)
> ------------------------------------
> Which is where it hangs, as before, usually about 2 minutes for each
> sequence.

Do you also see a SELECT CLASSIFICATION query succeeding the one above
(e.g., if you wait)? I'm asking because I'm surprised that that isn't
the one you're seeing as taking too long, because it has been reported
earlier to cause such problems with mysql. Alex Zelensky posted what
he found worked as a fix.

  -hilmar
--
----------------------------------------------------------
: Hilmar Lapp -:- San Diego, CA -:- hlapp at gmx dot net :
----------------------------------------------------------