New Blogs #5

After having solved a performance issue (caused by NULL columns), it is time for the fifth New Blogs issue. There are quite a few new blogs online in Chemical blogspace:

• eCTD (entry in Cb)
• Closeted Chemistry (entry in Cb)
• C&EN @ ACS Weblog (entry in Cb)
• Protein Spotlight (entry in Cb)
• Noel O'Blog (entry in Cb)
• Paper Cuts (entry in Cb)
• one in ten thousand (entry in Cb)
• Social Detritus (entry in Cb)
• The Thesis Sprint (entry in Cb)
• Pharma's Cutting Edge (entry in Cb)
• Science, Theory, and Liberty (entry in Cb)
• Chemist In A Transition State (entry in Cb)
• Xcovery blog - kinase industry knowledge collected and sometimes analyzed (entry in Cb)
• TotallyMechanistic (entry in Cb)
• dreaming spirals (entry in Cb)
• Liquid Carbon (entry in Cb)
• neksa (entry in Cb)
• The Seven Stones (entry in Cb)
• On The Road (entry in Cb)

Suggestions are most welcome, and thanx to those who did in the last month.

Why Cb is slow: SQL query trouble

Yesterday and today I am back at the CUBIC to meet up with my former colleagues, which gives me the opportunity to proper debugging of the Cb server instabilities. Earlier I turned on this useful: MySQL option:

log_slow_queries = /var/log/mysql/mysql-slow.log

which pointed out a serious problem:

# Time: 070427  8:30:04
# User@Host: pg[pg] @ wiki.cubic.uni-koeln.de [134.95.151.115]
# Query_time: 426  Lock_time: 0  Rows_sent: 50  Rows_examined: 4096407
SELECT t1.tag AS tag, COUNT(DISTINCT posts.blog_id) AS count FROM tags AS
  t1, tags AS t2, posts WHERE t1.post_id = t2.post_id AND t1.tag != t2.tag
  AND t2.tag='Visualization' AND posts.post_id = t1.post_id GROUP BY t1.tag
  HAVING count >= 1 ORDER BY count DESC LIMIT 50;

The time consumed in this example, 426 seconds, is already stupidly long, but it can be even worse. Now the problem really seems to be in the number of rows examined which is slightly over 4 milion, while the tags table really only has about 35 thousand entries. The reason why it actually is slow, is that during this query it massively reads and write from the harddisk. That is, 20-30 MB a second for about the time it takes to do the query. It is obvious that that leads to server instabilities.

Next step is to understand what this query is supposed to do, and why the hell it is actually making so many entries. Euan, if you are tuned in, the blog_id and post_id columns only contains NULL, which might cause the row explosion?

Update: I tracked this query down to the functionality get_similar_tags and disabled that for now, until I get it fixed.

Broken Cb PipeLine...

Last week, someone reported me that his blog items were not showing up. He was right. There was an invalid XML file created at some point, which broke processing of the blog items using a Perl XML module. This is fixed now, and resulted in a big blog of new items today.

Database server instabilities...

The machine that is running the database behind the Chemical blogspace is having trouble keeping alive. This is happening since the (scheduled) power outage of this weekend. Now, since the CUBIC has shut down as organization, my postdoc contract has ended too. As a consequence, I will not have frequent access to the machine, and remote SSH access is having hickups too. So far, a (former) collegue (Miguel) is helping me out, by rebooting every now and then, but I will work on a more permanent solution.

The best solution would be to get some permanent hosting somewhere, but without a university position where the machine could run, that is not cheap. Not too expansive either, but for a hobby... In the short term, I am considering moving the database to frontend machine, which has somewhat higher load, but is more stable too.

To be continued...

The JACS TOC featuring your review?

Yes, that is now possible: your JACS paper review from your blog in the TOC (and most other journals):


Noel adapted a Greasemonkey script by Pedro that retrieves blog items that discuss the item from Chemical blogspace and inserts them in the TOC. If you hover over the Cb icon, the comments will pop up (Noel, the bubble is a bit to comic for my taste):


The details on this and other chemical user scripts can be found here. Oh, just to make sure for those who do not yet know how this works: the script modifies the TOC only locally. That is, only browsers that have the userscript installed will see the pimped TOC.

New Blogs #4

Already the fourth in the series (after #1, #2 and #3), and there are ten new blogs, summing up to 96 blogs now:

• Nascent (entry in Cb)
• ChemBlogs (entry in Cb)
• hdreioplus.de (entry in Cb)
• Lab Cat (entry in Cb)
• The Sceptical Chymist (entry in Cb)
• Chemistry Central (entry in Cb)
• Chemical Engineering World (entry in Cb)
• The Green Chemistry Technical Blog (entry in Cb)
• blog about science (entry in Cb)
• Chemistry 221 (entry in Cb)

Suggestions are most welcome, and thanx to those who did in the last month.

Chemical blogspace getting physical at the ACS?

Lamentations on Chemistry is proposing to meet at the next ACS meeting in Chicago. I think this is an excellent idea, and hope to meet the other bloggers in chemical blogspace in person. Mitch replied that a similar thing was done at the previous ACS meeting.

Interestingly, the program of the meeting contains talks on blogging too:

CHED 25: "Teaching organic chemistry with blogs and wikis" Jean-Claude Bradley
... The evolution in the use of blogs and wikis in the teaching of undergraduate organic chemistry classes will be described, both as convenient tools to deliver course material and as platforms for student assignments. ...

CHED 21: "Blogging the culture of chemistry" Michelle M. Francl
... The Culture of Chemistry blog explores the relationship between chemistry, chemists and everything else. The connections between science content blogs and science culture blogs will be explored using the Culture of Chemistry as a jumping off point. ...

What about Monday or Wednesday afternoon?