On Wed, 27 Feb 2013, Ferran Jorba wrote:
> 1723 ddd localhost ddduab 0 Query UPDATE schTASK SET priority = NULL
> WHERE status IN ('WAITING', 'RUNNING', 'SLEEPING', 'ABOUT TO STOP',
> 'ABOUT TO S
OK, this query looks kind of meaningless. It could make
calculate_rows() work for nothing, as it were, especially if the above
query is too frequent. Do you have many incoming bibupload queries in
your queue?
Here is a patch to try that will reduce the number of these SQL queries:
| diff --git a/modules/bibsched/lib/bibsched.py
b/modules/bibsched/lib/bibsched.py
| index af2f10c..ddf5140 100644
| --- a/modules/bibsched/lib/bibsched.py
| +++ b/modules/bibsched/lib/bibsched.py
| @@ -1256,7 +1256,7 @@ class BibSched(object):
| 'SCHEDULED', 'CONTINUING')
| AND proc = 'bibupload'
| AND runtime <= NOW()""")
| - if max_bibupload_priority:
| + if max_bibupload_priority[0][0]:
| run_sql(
| """UPDATE schTASK SET priority = %s
| WHERE status IN ('WAITING', 'RUNNING', 'SLEEPING',
BTW, since you mentioned that your DB works a lot, you don't have to run
Python profiling in itself, you can concentrate on DB matters first.
E.g. you can enable SQL logging temporarily by doing:
$ sudo -u www-data /opt/invenio/bin/bibsched stop
$ sudo -u www-data sed -i 's/\#\#\# log_sql_q/log_sql_q/g' \
/opt/invenio/lib/python/invenio/dbquery.py
$ sudo -u www-data /opt/invenio/bin/bibsched start
and observe your SQL query log:
$ tail -f /opt/invenio/var/log/dbquery.log
This can show possibly unneeded SQL queries.
E.g. the first thing to do is to optimise your tables:
$ sudo -u www-data /opt/invenio/bin/bibsched stop
$ echo "OPTIMIZE TABLE schTASK" | /opt/invenio/bin/dbexec
$ sudo -u www-data /opt/invenio/bin/bibsched start
If you have not optimised your tables since a long time, this can give
really great speed-ups.
Otherwise what does `mysqltuner' says WRT your buffer sizes? See
<http://invenio-software.org/wiki/Tools/MySQL/Tuning> for some hints.
Best regards
--
Tibor Simko
