Статистика розподілу доменів по AS, IP, NS, MX і іншим параметрам

Давним-давно перестав працювати 1stat.ru — відверто кажучи, для нас це була трагедія (зараз начебто він працює).

Робочий день починався з чашки кави і приємного перегляду значень приросту доменів. Звичайно ж, такі метрики не показують ні успішність компанії, ні її капіталізацію, ні тип клієнтів, які розміщуються у компанії. Але, незважаючи на це, побічно можна оцінити динаміку зростання або деградації компанії — якщо кількість доменів на NS сервери росте, а не падає — це добре. Як показала практика, це, так чи інакше, відображає стан справ на ринку.



Перша версія firststat.ru
Недовго думаючи, накидали скриптик з таблицею, де збирали значення NS серверів для домену і робили невелику статистику. Актуальність таблиці була не сама хороша, так як ми підсумовували всі домени на всіх серверах і просто ділили їх на кількість серверів, яке з часом у деяких провайдерів змінювалося. Але це не заважало нам влаштовувати корпоративи при пересуванні вгору за рейтингом =). У цієї реалізації не було нічого цікавого, вона була зроблена за один вечір і розповідати про неї лінь.

Друга версія firststat.ru
Приблизно рік тому pavelodintsov надіслав цікаву статистику щодо розподілу доменів з автономним системам.



А також посилання на код, який збирає дану статистику. Сам збір був оформлений як запуск різних сценаріїв bash, perl і sqlite. При цьому алгоритм був досить простий і цікавий, в принципі, від Павла, як від талановитого фахівця, нічого іншого і не варто було очікувати, правда працював він досить повільно і збирав дані тільки по AS.

  • запитувався у реєстратора список доменів в зонах ru, su і рф
  • по кожному з доменів запитувалася А запис
  • отримували BGP fullview і робили відповідність до мережі AS
  • на основі цих даних збирали статистику по розподілу доменів з автономним адресами


Алгоритм був поліпшений — повністю переписаний на Python, введені історичні таблиці, доданий збір і зберігання всіх записів з DNS. Також написали просту обгортку для відображення даних firststat.ru, яку при наявності часу і інтересу поза обов'язково будемо покращувати.



Збір статистики
Оригінальний проект: https://github.com/pavel-odintsov/ru_open_statistics
Мій форк, переписаний на Python: https://github.com/AlexeyManikin/domain_statistic

Для роботи необхідні модулі Python:
  • MySQLdb
  • dnspython
  • SubnetTree
  • psutil >= 2.2
Також необхідний сервер MySQL і вкрай бажано швидкий DNS резолвер, так як основна частина навантаження припадає на нього. Необхідно запросити всі записи (A, AAAA, SOA, TXT, MX, SRV, NS) для кожного з 6000000+ доменів. У перший час також збирав інформацію з аналогічних проектів для перевірки коректності результатів.

Структуру проекту описувати, думаю, не має сенсу, код досить простий для розуміння.
update_as_info.py — оновлює дані по AS
update_domain.py — оновлює дані по доменах
update_statistic.py — агрегує статистику в БД

Структура БД
Нижче наведу таблиці з полями, які можуть бути корисні при вибірці даних:

as_list: таблиця відповідності номери AS та її опису, країни
  • id — номер AS
  • description — опис
  • country — країна

domain: таблиця, в якій зберігається поточний стан домену, гадаю, більша частина полів зрозуміла з назви
  • domain_name — FQDN
  • registrant — через кого був зареєстрований домен
  • tld — зона для швидкого пошуку
  • register_date — дата реєстрації
  • register_date_end — дата закінчення реєстрації
  • free_date — дата звільнення домену
  • delegated — делегований домен чи ні
  • a1...a4 — A запису домену
  • ns1...ns4 — NS записи домену, саме ті, які віддає DNS
  • mx1...mx4 — MX записи домену
  • txt — TXT запис домену
  • asn1...asn4 — номер AS для відповідної А запису
  • aaaa1...aaaa4 — AAAA (ipv6) запису домену
  • cname — CNAME запису домену

domain_history: містить історію змін записів у таблиці domain, крім всіх полів, які є в domain, присутні ще поля:
  • domain_id — посилання на запис в domain
  • date_start — дата, з якої дана інформація була актуальною
  • date_end — дата, коли інформація перестала бути актульаной

Представлені нижче таблиці містять агрегируемую інформацію:
  • domain_count_statistic — кількість доменів в різних зонах по днях
  • as_count_statistic — кількість доменів на AS по днях
  • mx_count_statistic — кількість доменів, які використовують MX, по днях
  • ns_count_statistic — кількість доменів на NS серверах по днях
  • registrant_count_statistic — статистика по реєстраторам по днях
  • a_count_statistic — статистика по кількості доменів на одному IP по днях
  • cname_count_statistic — статистика за даними CNAME по днях
Всю статистику можна вибрати з таблиць domain і domain_history, але запити досить довгі. Вирішили для прискорення зробити окремі таблиці.

Навантаження на сервер
Як співзасновнику хостингу, після довгих умовлянь мені все-таки виділили сервер =) Відповідно, статистику на постійній основі почав збирати з січня 2016 року, щоправда іноді забував включати після перезавантаження.
Все це працює на сервері 2 x Intel® Xeon® CPU E5-2620 0 @ 2.00 GHz 32 Gb RAM.
Для збору даних та агрегування статистики по всіх доменів потрібно приблизно 11 годин. На поточний момент в таблиці domain_history міститься 41436250 записів, загальний розмір БД 23 гігабайти. Кількість потоків pdns і паралельних обробників вибиралося експериментальним шляхом зупинився на опрацювання в 150 потоків.

Графіки навантаження CPU, LA і споживання пам'яті наведено нижче:







Запуск скрипта збору статистики

Найпростіший варіант запуску через Docker. Встановлюємо Docker і git

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

викачуємо репозиторій

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

збираємо образи

cd domain_statistic/docker
docker-compose build

запускаємо контейнери

docker-compoces up -d


Доступ до бази
Якщо кому цікаво, створив копію бази, відкрив доступ до БД в режимі ReadOnly, можна зайти погратися (обмеження в 300 підключень). Якщо буде хабраэффект — буде тупити =).
Сервер: manikin.beget.ru
Порт: 3310
Користувач: amanikin_stat
Пароль: openstatistic
База: domain_statistic

Якщо хто не вміє користуватися консоллю, встановив PhpMyAdmin: pma.amanikin.ru

Цікаві SQL, які нам знадобилися:
# Кількість доменів, які прописали замість 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 domain FROM 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 = 'ua'

Або як змінювалися DNS для вашого домену habrahabr.ru

Результат вибірки
mysql> select * from domain_history where domain_id = '1717034' \G 
*************************** 1. row ***************************
id: 1717033
domain_id: 1717034
date_start: 2015-07-31
date_end: 2015-09-18
domain_name: habrahabr.ru
registrant: registrator-ua
tld: ru
register_date: 2006-04-18 00:00:00
register_date_end: 2016-04-18 00:00:00
free_date: 2016-05-19 00:00:00
delegated: Y
a1: 178.248.233.33
a2: NULL
a3: NULL
a4: NULL
ns1: ns1.habradns.net.
ns2: ns2.habradns.net.
ns3: ns3.habradns.net.
ns4: NULL
mx1: alt1.aspmx.l.google.com.
mx2: alt2.aspmx.l.google.com.
mx3: aspmx.l.google.com.
mx4: aspmx2.googlemail.com.
txt: mailru-verification: 78856df53544c6fd v=spf1 include:spf.habramail.net include:aspmx.googlemail.com ~all
asn1: 197068
asn2: NULL
asn3: NULL
asn4: NULL
aaaa1: NULL
aaaa2: NULL
aaaa3: NULL
aaaa4: NULL
cname: NULL
nserrors: 
*************************** 2. row ***************************
id: 15762868
domain_id: 1717034
date_start: 2015-09-18
date_end: 2016-04-12
domain_name: habrahabr.ru
registrant: registrator-ua
tld: ru
register_date: 2006-04-18 00:00:00
register_date_end: 2016-04-18 00:00:00
free_date: 2016-05-19 00:00:00
delegated: Y
a1: 178.248.233.33
a2: NULL
a3: NULL
a4: NULL
ns1: ns1.habradns.net.
ns2: ns2.habradns.net.
ns3: ns3.habradns.net.
ns4: NULL
mx1: alt1.aspmx.l.google.com.
mx2: alt2.aspmx.l.google.com.
mx3: aspmx.l.google.com.
mx4: aspmx2.googlemail.com.
txt: google-site-verification=kdqnhqj_7jlohipxgkwxizyedshff0askqtc2ovh1em mailru-verification: 78856df53544c6fd v=spf1 include:spf.habramail.net include:aspmx.googlemail.com ~all
asn1: 197068
asn2: NULL
asn3: NULL
asn4: NULL
aaaa1: NULL
aaaa2: NULL
aaaa3: NULL
aaaa4: NULL
cname: NULL
nserrors: 
*************************** 3. row ***************************
id: 35689334
domain_id: 1717034
date_start: 2016-04-12
date_end: 2099-01-01
domain_name: habrahabr.ru
registrant: registrator-ua
tld: ru
register_date: 2006-04-18 00:00:00
register_date_end: 2017-04-18 00:00:00
free_date: 2017-05-19 00:00:00
delegated: Y
a1: 178.248.233.33
a2: NULL
a3: NULL
a4: NULL
ns1: ns1.habradns.net.
ns2: ns2.habradns.net.
ns3: ns3.habradns.net.
ns4: NULL
mx1: alt1.aspmx.l.google.com.
mx2: alt2.aspmx.l.google.com.
mx3: aspmx.l.google.com.
mx4: aspmx2.googlemail.com.
txt: google-site-verification=kdqnhqj_7jlohipxgkwxizyedshff0askqtc2ovh1em mailru-verification: 78856df53544c6fd v=spf1 include:spf.habramail.net include:aspmx.googlemail.com ~all
asn1: 197068
asn2: NULL
asn3: NULL
asn4: NULL
aaaa1: NULL
aaaa2: NULL
aaaa3: NULL
aaaa4: NULL
cname: NULL
nserrors: 
3 rows in set (0.04 sec)


Повний SQL дамп у вигляді архіву можна сказати посилання (розмір 2.2 G)

Аналогічні проекти
http://1stat.ru/
http://statonline.ru/
http://stat.nic.ru/
https://myip.ms/view/best_hosting/RUS
та інші…

Хтось використовує статистику відвідувань, хтось незрозуміло як вважає, у кого-то купа реклами. У будь-якому випадку, исходников жодного сервісу я не знайшов.

Посилання
Сайт — http://firststat.ru
Репозиторій — https://github.com/AlexeyManikin/domain_statistic
Творці:
pavelodintsov — творець ідеї і прототипу

redfenix — реалізація на Python, збір і зберігання даних, прототип сайту

ilin — доопрацювання сайту

Будемо раді побажань щодо поліпшення. Якщо кому потрібен доступ до бази для ваших додатків — пишіть, відкриємо.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.