Domain distribution statistics by AS, IP, NS, MX and other parameters

Once upon a time 1stat.ru stopped working - frankly, for us it was a tragedy (now it seems to work somehow).



The working day usually began with a cup of coffee and a pleasant view of the values ​​of domain growth. Of course, such metrics do not show the success of the company, nor its capitalization, nor the type of customers that are placed with the company. But despite this, you can indirectly assess the dynamics of growth or degradation of the company - if the number of domains on NS servers grows, and does not fall, this is good. As practice has shown, it somehow reflects the state of affairs in the market.

The first version of firststat.ru

Without thinking twice, they threw a script with a table, where they collected the values ​​for the NS servers for the domain and made some statistics. The relevance of the table was not the best, since we summarized all the domains on all servers and simply divided them by the number of servers, which over time with some providers changed. But this did not prevent us from arranging corporate events when moving upward by rating =). There was nothing interesting in this realization, it was done in one evening and it was too lazy to talk about it.

')

The second version of firststat.ru

About a year ago, pavelodintsov sent interesting statistics on the distribution of domains across autonomous systems.







As well as a link to the code that collects these statistics. The collection itself was designed to run various bash, perl and sqlite scripts. At the same time, the algorithm was quite simple and interesting, in principle, from Pavel, as a talented specialist, nothing should have been expected, although he worked rather slowly and collected data only on AS.

• requested from the registrar a list of domains in the zones ru, su and rf
• for each of the domains requested A record
• Receive BGP fullview and do network conformity to AS
• Based on this data, statistics on the distribution of domains to offline addresses was collected.

The algorithm was improved - it was completely rewritten in Python, historical tables were introduced, collection and storage of all records from the DNS was added. We also wrote a simple wrapper for displaying data on firststat.ru , which, given the availability of time and interest from the outside, we will definitely improve.

Statistics collection

Original project: https://github.com/pavel-odintsov/ru_open_statistics

My fork rewritten in Python: https://github.com/AlexeyManikin/domain_statistic



To work, you need the Python modules:

• Mysqldb
• dnspython
• Subnettree
• psutil> = 2.2

A MySQL server is also needed and a fast DNS resolver is highly desirable, as the bulk of the load falls on it. You must request all records (A, AAAA, SOA, TXT, MX, SRV, NS) for each of the 6,000,000+ domains. At first, I also collected information from similar projects to check the correctness of the results.



The structure of the project to describe, I believe, does not make sense, the code is quite simple to understand.

update_as_info.py - updates data on AS

update_domain.py - updates data by domains

update_statistic.py - aggregates statistics in the database

DB structure

Below are tables with fields that can be useful when retrieving data:



as_list : the table of correspondence of the AS number and its description, the country

• id - AS number
• descriptions - description
• country - country

domain : the table that stores the current state of the domain, I think most of the fields are clear from the title

• domain_name - FQDN
• registrant - through whom the domain was registered
• tld - quick search zone
• register_date - registration date
• register_date_end - registration end date
• free_date - domain release date
• delegated - domain delegated or not
• a1 ... a4 - A domain record
• ns1 ... ns4 - NS domain records, exactly those that DNS gives out
• mx1 ... mx4 - MX domain records
• txt - TXT domain record
• asn1 ... asn4 is the AS number for the corresponding A record
• aaaa1 ... aaaa4 - AAAA (ipv6) domain records
• cname - CNAME domain record

domain_history : contains the history of changes in the records in the domain table, in addition to all the fields that exist in the domain, there are also other fields:

• domain_id - link to the domain entry
• date_start - the date since this information was current
• date_end - the date when the information has ceased to be an actulae

The following tables contain aggregated information:

• domain_count_statistic - the number of domains in different zones by day
• as_count_statistic - the number of domains on AS by days
• mx_count_statistic - the number of domains using MX, by day
• ns_count_statistic - the number of domains on NS servers by day
• registrant_count_statistic - daily registrar statistics
• a_count_statistic - statistics on the number of domains on one IP per day
• cname_count_statistic - statistics on CNAME data by day

All statistics can be selected from the domain and domain_history tables, but the queries are quite long. We decided to make separate tables for speeding up.

Server load

As a co-founder of hosting, after much persuasion, I was still assigned a server =) Accordingly, I started collecting statistics on an ongoing basis from January 2016, although I sometimes forgot to turn it on after a reboot.

The whole thing works on a 2 x Intel® Xeon® CPU E5-2620 0 @ 2.00GHz server with 32 Gb RAM.

It takes approximately 11 hours to collect data and aggregate statistics for all domains. Currently, the domain_history table contains 41436250 records, the total size of the database is 23 gigabytes. The number of threads pdns and parallel handlers was chosen experimentally, stopped at processing 150 threads.



CPU, LA, and memory usage graphs are shown below:

Run the statistics collection script

The easiest way to run through Docker . Install Docker and git

sudo apt-get update sudo apt-get install docker.io git 

download the repository

  cd /home git clone https://github.com/AlexeyManikin/domain_statistic.git 

collect images

  cd domain_statistic/docker docker-compose build 

we launch containers

  docker-compoces up -d 

Access to database

If anyone is interested, created a copy of the database, opened access to the database in ReadOnly mode, you can go play (limit 300 connections). If there is a habraeffekt - will be stupid =).

Server: manikin.beget.ru

Port: 3310

User: amanikin_stat

Password: openstatistic

Base: domain_statistic



If someone does not know how to use the console, install PhpMyAdmin : pma.amanikin.ru

Interesting SQL, which are useful to us:

 #  ,    MX NS- mysql> SELECT count(*) FROM domain WHERE mx1 LIKE 'ns1.%'; +----------+ | count(*) | +----------+ | 1064 | +----------+ 1 row in set (22.05 sec) #  mysql> SELECT count(*) FROM domain WHERE ns1 LIKE 'mx%'; +----------+ | count(*) | +----------+ | 436 | +----------+ 1 row in set (0.01 sec) #  ,   ,        mysql> SELECT count(*) FROM domain WHERE register_date_end <= '2016.05.27'; +----------+ | count(*) | +----------+ | 231235 | +----------+ 1 row in set (27.99 sec) #     ,  LIKE,  ,     FULLTEXT INDEX. #  ,    domain,   6 . ,     habrahabr   mysql> SELECT domain_name FROM domain WHERE MATCH (domain_name) AGAINST ('+habrahabr*' IN BOOLEAN MODE); +---------------+ | domain_name | +---------------+ | habrahabrs.ru | | habrahabr.ru | | habrahabra.ru | | habrahabr.su | | habrahabru.ru | +---------------+ 5 rows in set (2.18 sec) #  ,     LIKE   . #  - ,    ns    beget mysql> SELECT count(*) FROM domain USE INDEX(ns_all_ft) WHERE MATCH (ns1,ns2,ns3,ns4) AGAINST('+beget*' IN BOOLEAN MODE) +----------+ | count(*) | +----------+ | 224715 | +----------+ 1 row in set (2.18 sec) #     ru     tld = 'ru' 

Or how the DNS for the domain habrahabr.ru changed





The full SQL dump in the form of an archive can be seen from the link (size 2.2G)

Similar projects

http://1stat.ru/

http://statonline.ru/

http://stat.nic.ru/

https://myip.ms/view/best_hosting/RUS

other…



Someone uses the statistics of visits, someone does not understand how they think, someone has a lot of advertising. In any case, I did not find the source of any service.

Links

Site - http://firststat.ru

Repository - https://github.com/AlexeyManikin/domain_statistic

Creators:

pavelodintsov - creator of the idea and prototype

redfenix - implementation in Python, data collection and storage, site prototype

ilin - site refinement



We will be glad to suggestions for improvement. If anyone needs access to the database for your applications - write, open.