drupal.org

It is now several weeks after the upgrade of drupal.org to Drupal 6 and I've taken a look at google's crawling statistics for drupal.org.

This is the most interesting graph for me as infrastructure manasger, it shows the average time that googlebot needs to download a html page from drupal.org. We apparently had a bit of a rough ride in January, but recently this has smoothed out. About 600ms per page seems quite a good value to me.

On drupal.org we have much less spammers than other websites. One reason is the fact that we do not allow anonymous users to post anything and that every user needs a valid mail address in order to use his account.

This poses the question: Which email providers to our spammers use?

Luckily, this is rather easy to answer:

mysql> select substring_index(substring_index(init, '@', -1), '.', 1) as provider, count(substring_index(substring_index(init, '@', -1), '.', 1)) as count from users where status = 0 and login != 0 group by provider order by count;

The title roughly translates to "Oh, to once work with true professionals!"

This is something which I all too often have to think (and sometimes say aloud) when working on client projects. The nature of my work usually results in working for some kind of web agency which has secured a bigger Drupal project that they cannot handle alone. I then work with the agency's employees and other freelancers to complete whatever the project demands.

select count(login) from users where login > 0 and from_unixtime(login) > '2008-08-01 00:00:00' and status= 1;
+--------------+
| count(login) |
+--------------+
| 34626 |
+--------------+

That means that 10% of all drupal.org user accounts are actively used. Not that bad at all.

133257 is the result for the same query if you change the date to 1st of January. That is over50% of the accounts that exist on drupal.org and have been used at least once. Quite impressive.

mysql> select count(login) from users where login > 0 and from_unixtime(login) > '2008-08-01 00:00:00' and status = 0 ;
+--------------+
| count(login) |
+--------------+
| 450 |
+--------------+

The query gives us the number of users who logged in during August and are now blocked.