Hi all Every now and again, i get ask to run this or that query for some people one some wikimedia project, to make a TODO list, or gather some statistics. Usually, the queries are easy, and it's a matter of a couple of minutes to run them. Bit it's tricky for people who need such a query run to find someone to do it. So, I propose we run a "Query Service" - there's a quick writeup on <http://wiki.ts.wikimedia.org/view/Query_service>, basically the idea is to have a project for this in JIRA, and a couple of users handling tickets people file there. Simple enough. So, have a look, and if you would like to help out and run a query every now and then, get yourself added to the query-service group. Not sure how much we should advertise this right now - if no one knows about it, it's pointless, but if we get too many requests and too little help, it's not going to work. Perhaps it'll work this way: only advertise it if you are willing to handle requests :) -- Daniel _______________________________________________ Toolserver-l mailing list Toolserver-l(a)lists.wikimedia.org http://lists.wikimedia.org/mailman/listinfo/toolserver-l

How about a web-based form that can do SELECTs? Sean

Sean Whitton wrote: NO! Either it would only allow very simple queries, and would thus be pointless. Or it would frequently bog down the server, because people run insane queries. Also, some legit queries may take so long that the http request would time out. And, last but not least: people filing such requests often don't know (enough) SQL. You need some human judgment to see which requests to actually turn into queries, and how, and how often. -- Daniel _______________________________________________ Toolserver-l mailing list Toolserver-l(a)lists.wikimedia.org http://lists.wikimedia.org/mailman/listinfo/toolserver-l

Why don't we all just watchlist [[m:Requests for queries]]? We can't do them automatically, but we can review them for sanity and push them through phpMyAdmin.

What if mysql.connect_timeout would be set to a low value, say 30 seconds? People could use it for moderately simple queries, and there are a lot of useful queries that can be executed that time, or for preparation of more complex queries (for example, prepare on simple a query for en).

Date: Thu, 25 Oct 2007 20:32:22 -0400> From: Simetrical+wikilist(a)gmail.com&gt; To: toolserver-l(a)lists.wikimedia.org&gt; Subject: Re: [Toolserver-l] Database Query Service> > On 10/25/07, Merlijn van Deen &lt;valhallasw(a)arctus.nl&gt; wrote:> > Killing the connection does not stop the query.> > Killing the query when it hits 30 seconds could be easily arranged.> That would not, however, stop people from running one complicated> query in five hundred parts all at once, for instance, which would> kill the server regardless.> > _______________________________________________> Toolserver-l mailing list> Toolserver-l(a)lists.wikimedia.org&gt; http://lists.wikimedia.org/mailman/listinfo/toolserver-l

Just a point about implementation. As was already written, it's impossible to allow users to execute their own selects, but we need to implement new queries by their requests quick, without writing tonns of same code each time. What we can do: create some kind of repository which can be modified by any of those who have accounts on Toolserver. It would contain list of queries with placeholders ("?") for parameters. For example, query_id | query_text 1 SELECT user_id FROM user_ids WHERE user_name=? 2 SELECT page_namespace FROM page WHERE page_title=? ORDER BY page_namespace (this list may be written into mysql table). User can inflict query by accessing some script common for all queries, which would find query by it's number (query_id) and print HTML form on GET (with textfields instead of placeholders) or results on POST. All toolserver programmers should be able add/modify/delete any query in another form and receive query_id to be sent to users. All modifications must be written to special log and it should be possible to revert'em, of course. Single login with other ts sites (like MediaWiki) for this script would be appreciated. -- Ed _______________________________________________ Toolserver-l mailing list Toolserver-l(a)lists.wikimedia.org http://lists.wikimedia.org/mailman/listinfo/toolserver-l

2 SELECT page_namespace FROM page WHERE page_title=? ORDER BY page_namespace (this list may be written into mysql table). User can inflict query by accessing some script common for all queries, which would find query by it's number (query_id) and print HTML form on GET (with textfields instead of placeholders) or results on POST.

Can we limit the amount of CPU/processing a query could take? If not, that would be a very useful MySQL function. We could then run those queries but expect them to take a lot longer.

Or what if we only made the tool available only to those who know what they're doing? This way, we'll know that said people can go easy on the servers and if they screw up, they lose access. On 10/26/07, Edward Chernenko &lt;edwardspec(a)gmail.com&gt; wrote: _______________________________________________ Toolserver-l mailing list Toolserver-l(a)lists.wikimedia.org http://lists.wikimedia.org/mailman/listinfo/toolserver-l

So let's see... my idea is a duplicate of the toolserver, and we want a public query service, but it would prove too tempting for people to use it for a denial of service attack, not to mention the fact that it would bog down the server. Does this *have* to be on the toolserver, or can we take the databases and put it on its own server dedicated for querying? Maybe we could have a sexy *multi-server* layout featuring a distributed computing scheme.

I'm not talking about a dedicated database server, but a dedicated *query* server where the only thing done on that server is handling queries, allowing maximum processor capacity to be dedicated to it. On 10/26/07, Bryan Tong Minh &lt;bryan.tongminh(a)gmail.com&gt; wrote: _______________________________________________ Toolserver-l mailing list Toolserver-l(a)lists.wikimedia.org http://lists.wikimedia.org/mailman/listinfo/toolserver-l

On 10/26/07, James Hare &lt;messedrocker(a)gmail.com&gt; wrote: and Of course you can. Then those dedicated servers get DoS'd, deliberately or otherwise, and you're back where you started. _______________________________________________ Toolserver-l mailing list Toolserver-l(a)lists.wikimedia.org http://lists.wikimedia.org/mailman/listinfo/toolserver-l

Or we just keep it as it is and have people just post their queries to JIRA. I think we are trying to solve a non-existent problem.