query – Made by Mikal

Estimating the progress of queries on MySQL

Post author:mikal
Post published:May 8, 2008
Post category:Mysql

I've been doing a lot of batch updates on one of my databases at home recently. show processlist says something like this: mysql> show processlist; +-------+------+---------------+--------------+---------+-------+----------+------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+------+---------------+--------------+---------+-------+----------+------------------------------------------| | 18354 | root | maui:37403 | smtp_servers | Query | 57234 | Updating | update ips_218 set reverse_lookup = null | | 22286 | root | maui:37348 | smtp_servers | Query | 38103 | Updating | update ips_80 set reverse_lookup = null, | | 22851 | root | maui:54982 | smtp_servers | Query | 34091 | Updating | update ips_19 set reverse_lookup = null, | | 23351 | root | molokai:58232 | smtp_servers | Sleep | 57 | | NULL | | 23496 | root | maui:40923 | smtp_servers | Query | 29973 | Updating | update ips_62 set reverse_lookup = null, | | 23906 | root | maui:38068 | smtp_servers | Query | 26794 | Updating | update ips_83 set reverse_lookup = null, | | 25675 | root | maui:56438 | smtp_servers | Query | 12505 | Updating | update ips_82 set reverse_lookup = null, | | 25846 | root | maui:41334 | smtp_servers…

Reducing the MySQL query lock timeout?

Post author:mikal
Post published:July 24, 2007
Post category:Mysql

Apart from sniping the queries, is there any other way to reduce the amount of time that a query will wait before giving up on getting access to a locked table?

MySQL scaling: query snipers

Post author:mikal
Post published:March 7, 2007
Post category:Mysql

I've been wanting a query sniper for a while for MySQL (some people seem to call them "query killers"). They're basically programs which inspect the MySQL "show processlist" command and decide to kill some processes if they violate specified rules. I have a prototype of one in python, and couldn't find any other implementations until tonight when I came across Querybane, which is what Wikipedia use to do exactly the same thing. Now, I need to think further about if querybane does what I want, especially given I can't find the source code. Perhaps it's hidden in the media wiki code somewhere? The only code for a project named "servmon" (it's parent project) that I can find is four years old and in PHP. (Oh, by the way, the Wikipedia MySQL docs are really interesting, for example this page has some nice practical advice about replication log corruption. I wish I had found this before now for two reasons: it's nice to see how other people do this stuff; and there is some stuff I can totally steal from here.) So, does anyone know where I can find recent querybane code? Alternatively, what do other people use as a query…

Alternate queries on results pages making it easier for future evilness?

Post author:mikal
Post published:August 24, 2005
Post category:Google

John Battelle implies that the new search result page being testing by Google could make evilness easier in the future. The premise is that offering alternate queries might be useful now, but it would make it easier to insert paid listings later. I'm confused though, and John doesn't have a comment link in his RSS output, so I'll post it here. Surely if Google wanted to embrace evil later, they could just direct their coders to make it happen? They already know what keywords you're searching for (i.e. Adwords), couldn't they just roll those into the page in a way which looks like search results? I see no real way this positions for future evil. I've been keeping an eye on Google news recently, and there seems to be a propensity towards paranoia in a lot of people (for instance the gmail displaying Adwords thing). If people are bothered, why don't they just use another search engine? You can't tell me that MSN and Yahoo! are any less evil...