Definition of "Lock wait ratio"

When "tuner-primer.sh" under MySQL is used, "Lock wait ratio" appears at the end of the report.
Here is the explanation of Lock wait ratio:
A metric that attempts to quantify the percentage of queries that are required to wait for object locks to be released so that the query can itself acquire a lock on the object.

Let Django work with view cache

1. Modify django/utils/decorators.py
   

   --- django/utils/decorators.py (revision 4490)
   

   +++ django/utils/decorators.py (working copy)
   

   @@ -30,4 +30,12 @@
   

   return result
   

   return response
   

   return _wrapped_view
   

   +
   

   + def _true_24_decorator(*args, **kwargs):
   

   + def _decorator(f):
   

   + return _decorator_from_middleware(f, *args, **kwargs)
   

   + return _decorator
   

   +
   

   + _decorator_from_middleware.decorator = _true_24_decorator
   

   +
   

   return _decorator_from_middleware
   

2. Usage:
   

   @cache_page.decorator(60)
   

   def my_cool_view(request):
   

   # cool processing
   

Install Mysql with non root account on linux system.

Download mysql server at http://dev.mysql.com/downloads/mysql/
Run it directly.

OneToOne relations in Elixir

Note that a OneToOne relationship cannot exist without a corresponding ManyToOne relationship in the other way. This is because the OneToOne relationship needs the foreign_key created by the ManyToOne relationship.
When an OneToOne relation is needed, you need to make ManyToOne relationship first, where in database foreign keys are stored.

Example:

class Movie(Entity):
using_options(tablename='Movie',autosetup=True)
title=Field(Unicode(30))
year=Field(Integer)
decription=Field(Unicode)
director=ManyToOne('Director')

class Director(Entity):
using_options(tablename='Director',autosetup=True)
name=Field(Unicode(60))
movie=OneToOne('Movie')

Release memory when using SqlAlchemy for a long time contextual session

First
session.flush()
then
session.close()

The close() method issues a clear(), and releases any transactional/connection resources. When connections are returned to the connection pool, whatever transactional state exists is rolled back.

When close() is called, the Session is in the same state as when it was first created, and is safe to be used again. close() is especially important when using a contextual session, which remains in memory after usage. By issuing close(), the session will be clean for the next request that makes use of it.

New Group Webpage is on line written by Django

http://biocroma.uzh.ch

It is a django site. Django is very good and fast developing.

Join example in SQLalchemy

We have 2 tables

CREATE TABLE `zincnew01`.`zincmol` ( `zincid` int(8) unsigned NOT NULL, `pacname` char(3) NOT NULL, `numatoms` tinyint(3) unsigned NOT NULL, `numc` tinyint(3) unsigned NOT NULL, `numn` tinyint(3) unsigned NOT NULL, `numo` tinyint(3) unsigned NOT NULL, `numhal` tinyint(3) unsigned NOT NULL, `nums` tinyint(3) unsigned NOT NULL, `nump` tinyint(3) unsigned NOT NULL, `numarombnd` tinyint(3) unsigned NOT NULL, `numdoubbnd` tinyint(3) unsigned NOT NULL, `numtribnd` tinyint(3) unsigned NOT NULL, `numamibnd` tinyint(3) unsigned NOT NULL, `numacc` tinyint(3) unsigned NOT NULL, `numdon` tinyint(3) unsigned NOT NULL, `numring` tinyint(3) unsigned NOT NULL, `totringsize` tinyint(3) unsigned NOT NULL, `longestchain` tinyint(3) unsigned NOT NULL, `wienerind4` float(16,14) NOT NULL, `numbnd` tinyint(3) unsigned NOT NULL, `numfrg` tinyint(3) unsigned NOT NULL, `numrotbnd` tinyint(3) unsigned NOT NULL, `mw` float(8,3) unsigned NOT NULL, `clogp` float(5,2) NOT NULL, `charge` int(2) NOT NULL, `mol2file` blob, `tag` char(20) default NULL, PRIMARY KEY (`zincid`), KEY `pacname` (`pacname`), KEY `tag` (`tag`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1

and

CREATE TABLE `zincnew01`.`Pose` ( `id` int(11) NOT NULL auto_increment, `pose` int(4) default NULL, `ele` float default NULL, `elee` float default NULL, `vdw` float default NULL, `vdwe` float default NULL, `pdbfile` blob, `mol_zincid` int(8) unsigned default NULL, `tag` char(20) default NULL, PRIMARY KEY (`id`), KEY `ix_Pose_mol_zincid` (`mol_zincid`) ) ENGINE=MyISAM AUTO_INCREMENT=39261434 DEFAULT CHARSET=latin1

I want to query lile select * from Pose,zincmol where zincid=mol_zincid and pacname="xaa" In SQLalchemy (elixir) I use Pose.query.filter(Pose.tag==None).join('mol').filter(Mol.pacname=='xaa') to get the same results.

Use regular expression in sqlalchmy with Mysql

writing...

Exec and eval in python

Example:
exec('a=1+3')
or
a=eval('1+3')
then a=4

Set environment variables in python

client.py

#!/usr/bin/env python

import os,datetime

starttimestr=datetime.datetime.now().strftime('%Y%m%d%M%S')

os.environ['starttime']=starttimestr

os.system('t1.sh')

t1.sh

#!/bin/bash

echo $starttime

runt1.py

#!/usr/bin/env python

import os,datetime

starttime=datetime.datetime.strptime(os.environ['starttime'],'%Y%m%d%M%S')

print starttime

environment variables can be transferred in this way. Such changes to the environment affect subprocesses started with os.system(), popen() or fork() and execv(). Availability: most flavors of Unix, Windows.

Use elixir to execute raw SQL command

metadata.bind.execute('select * from Pose ')

Change screen enviroment variables

When you reconnect screen session again, you find that the DISPLAY variable changed, that the X application cannot be executed easily.

In this case, the things you can do is

screen -X "setenv"

Then enter

DISPLAY

:0.0

OK!

When build a new screen using C-a C, the DISPLAY vars will be set to :0.0.

Deal with ERROR 126 (HY000): Incorrect key file for table in Mysql

1. copy your data files(*.MYD) to a safe place.
2. save your table making scripts.
3. drop the broken table.
4. make a brand new table with the same structure. mysql < [table making scripts]
5. copy your data files back.
6. myisamchk -r -q .

Semiempirical RM1 and PM6 seems better than AM1 and PM3

Recently it is reported that RM1 and PM6, which were developed by J. J. P. Stewart's group, perform better than classical AM1 and PM3, even comparable to B3LYP in their research on QSPR. They recalibrated 10 previously published models (for different properties and groups of congeneric compounds) employing PM6 and RM1 descriptors instead of B3LYP ones, and demonstrated that by applying RM1 and PM6 descriptors, we could obtain QSPR models with quality similar to that of models based on B3LYP descriptors. This level of accuracy was out of reach for the models employing AM1- and PM3-based descriptors.

The original paper is Calculation of Quantum-Mechanical Descriptors for QSPR at the DFT Level: Is It Necessary?

Update with join in Mysql

update ProbEnertag join ProbEner on ProbEner.id=ProbEnertag.probener_id set ProbEnertag.sign=3 where ProbEnertag.sign=0 and not (probe1<-2 and probe2<0.5>

This clause will set sign to 3, where ProbEner passes some filter and ProbEnertag.sign=0.

Use line lock of innodb in MySQL to improve the perfromace of the "tag table"

"Tag table" is used for labeling the status of the related molecules when docking. When there are plenty of computing clients request data from database, they should know which ones has already been booked or are being managed by other clients. So the "tag table" is designed for tracing the status of the data.

Innodb owns the character of "line lock", which can improve the performance of the "tag table", because "tag table" needed to be locked for updating frequently. If using MYISAM, which can only table-locked, then all the requests of updating will be blocked when one client trying to get and update the data of this table. The things, however, are different, when using innodb. Only the updating lines with its one former line and one following line will be locked.

The MySQL codes are like

set autocommit=0
select a_id,id from atag where sign=0 limit %d for update
/* update command let sign=1*/
commit
set autocommit=1

sign=0 means needing to manage. sign=1 means managing. When finishing, update sign=2.