Have you read the part in the wiki about "SLOW_OK" [1]? [1] https://wiki.toolserver.org/view/Databases#Slow_queries_and_the_query_killer Best regards, Jan -----Ursprüngliche Nachricht----- Von: toolserver-l-bounces(a)lists.wikimedia.org [mailto:toolserver-l-bounces@lists.wikimedia.org] Im Auftrag von Lars Aronsson Gesendet: Dienstag, 29. November 2011 18:49 An: toolserver-l(a)lists.wikimedia.org Betreff: Re: [Toolserver-l] Tracking external link statistics over time On 11/25/2011 10:20 PM, Lars Aronsson wrote: substring_index(substring_index(el_to, '/', 3),'/',-1)) as site In the last few days, I've learned many things, such as 1) using the rrdb instead of userdb servers, 2) using SGE to submit jobs that require a particular database cluster, 3) getting a list of databases in a cluster, looping over them, 4) including the File, Author, Index, and Portal namespaces. This is going great. Looping over all of the 516 databases in the s3 cluster takes 17 minutes, or an average of 2 seconds per query. This is very reasonable. The 17 databases in s2 take 47 minutes or 151 seconds per query, which is okay. But enwiki is too large to execute this query within the allowed 30 minutes. Even a simple "select count(*) from externallinks;" timed out on me. For Wikimedia Commons, the query times out when I try to include external links found in the File (6) namespace, but if I only count the article (0) namespace (where there are very few external links), it produces a fine result. Does the toolserver give me any other options to get statistics on the enwiki database? I could load page.sql and externallinks.sql into my own MySQL instance, now that these dumps are produced on a monthly schedule. Is that my best option? Can I set up my own MySQL for this on the toolserver? Or should I move the project to my own server? So far, I output the result to text files. Later I might want to store it in a database, but this is no hurry. -- Lars Aronsson (lars(a)aronsson.se) Aronsson Datateknik - http://aronsson.se _______________________________________________ Toolserver-l mailing list (Toolserver-l(a)lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/toolserver-l Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette

On 29/11/11 18:49, Lars Aronsson wrote: If you were to store the data in the server, you would need a userdb server. (...) You could load it in ranges, and then merge all of the results. That's the wrong solution. It might be faster to process the sql file with string tools (unlikely, having to match pageids with namespaces) but using a user level mysqld for that is completely overkill.

TL;DR: Look for protocol relative links. That's interesting. There are such links in the dumps: gzip -d < /mnt/user-store/dumps/frwiki-group-writable/frwiki-20111123-externallinks.sql.gz | grep -oP ".{17}commons.wikimedia.org[^']*" | more etc. Although few of them seem to come from NS_MAIN: sql frwiki_p "select page_namespace from page where page_id IN (0$(gzip -d < frwiki-20111123-externallinks.sql.gz | grep --line-buffered -oP "([0-9]+)(?=,'http://commons.wikimedia.org[^']*)" | uniq | head -500 | sed s/^/,/g ))" The trick seems to be that most of them they will be present through //commons.wikimedia.org, not http://commons.wikimedia.org Talk pages will have more url-copying, while articles will use templates like {{Autres projets}} does show many NS_MAIN entries. I suspect you are only taking into accoutn http: and https: links with your perl script, while all of them with your sql query. 101439 + 399 = 101838 ~= 101619 which is your db result.