Как быстро загрузить HTTP-лог Kerio Control размером 6Гб в MariaDB с помощью Python?

В лог-файле HTTP шлюза Kerio Control больше 41 млн строк (формат Apache). Нужно получить возможность применить фильтр по IP, дате или URL. Также было бы здорово извлечь полный список доменов или IP, к которым пользователь обращался в рассматриваемый период времени. SQL удачно подходит для этой задачи.

Лог имеет следующий вид:

192.168.12.6 - - [21/Mar/2019:14:22:34 +0300] "GET http://example.com/media/sidebar.jpg HTTP/1.1" 200 31378 +2
192.168.12.6 - - [21/Mar/2019:14:22:34 +0300] "GET http://example.com/media/banner1.jpg HTTP/1.1" 200 28716 +3
192.168.12.6 - - [21/Mar/2019:14:22:34 +0300] "GET http://example.com/media/banner2.jpg HTTP/1.1" 200 14080 +4
192.168.12.6 - - [21/Mar/2019:14:22:35 +0300] "GET http://example.com/static/bundle.min.css HTTP/1.1" 200 227528 
...

И таких записей на 6 Гб :-)

План

  1. Огромный лог разделим на файлы-кусочки. По умолчанию — по 20000 строк на файл.

  2. Кусочки логов будут обрабатывать несколько процессов — задействуем многозадачность. Запустим по 2 процесса на каждый вычислитель.

  3. Функция-обработчик будет конвертировать записи лога в SQL-инструкции INSERT. Для ускорения процесса будут формироваться множественные вставки. Это уменьшит количество сетевых обращений. Один запрос по умолчанию будет вносить в базу данных 100 записей.

База данных

  1. Авторизуйтесь в MariaDB и создайте пользователя с полным набором привилегий для будущей БД log:

    $ mysql -u root -p
    > GRANT ALL PRIVILEGES ON `log`.* TO 'log'@'localhost' IDENTIFIED BY 'YOUR_PASS';
    
  2. Создайте таблицу для хранения информации из логов:

    > CREATE DATABASE log;
    > USE log;
    > CREATE TABLE log (
        id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 
        ip VARCHAR(15) NOT NULL, 
        dt DATETIME NOT NULL, 
        url VARCHAR(500), 
        CONSTRAINT pk_log PRIMARY KEY (id));
    > CREATE INDEX ip_idx ON log(ip);
    > CREATE INDEX dt_idx ON log(dt);
    > CREATE INDEX url_idx ON log(url);
    

Скрипт-парсер

Потребуются установленные в системе Python 3.7+ и pipenv.

  1. Склонируйте репозиторий проекта в удобное место:

    $ git clone https://github.com/ezyatev/kerio-log-loader.git
    
  2. Установите скрипт в виртуальное окружение:

    $ pipenv install
    
  3. Скопируйте .env-example, отредактируйте и сохраните под именем .env.

  4. Запустите импорт логов в БД:

    $ pipenv shell
    $ python loader.py -f http.log
    

    При желании можно управлять основными параметрами скрипта, такими как количество процессов, количество записей на одну INSERT-инструкцию и размер (в строках) каждого кусочка, на которые разбивается исходный лог-файл. Для получения справки наберите:

    $ python load.py -h
    
    usage: load.py [-h] --file FILE [--numprocs NUMPROCS] [--numrows NUMROWS]
                   [--chunksize CHUNKSIZE]
    
    optional arguments:
      -h, --help            show this help message and exit
      --file FILE, -f FILE  path to log file
      --numprocs NUMPROCS, -p NUMPROCS
                            number of processes
      --numrows NUMROWS, -r NUMROWS
                            number of rows for INSERT statement
      --chunksize CHUNKSIZE, -s CHUNKSIZE
                            number of lines for chunk file
    

Тестирование

MacBook Air 2017

  • Intel® Core™ i5-5350U CPU @ 1.80GHz, 2 ядра, 4 потока, 8 Гб ОЗУ, SSD.
  • MacOS 10.14.3, Python 3.7.2.
  • 8 процессов Kerio Log Loader.

~55 минут

VPS DigitalOcean

  • Intel® Xeon® CPU E5-2650 v4 @ 2.20GHz, 6 ядер, 16 Гб ОЗУ, SSD.
  • CentOS 7.6, Python 3.7.2
  • 12 процессов Kerio Log Loader

~33 минуты

MariaDB [log]> select count(id) from log;
+-----------+
| count(id) |
+-----------+
|  41172000 |
+-----------+

Примеры запросов

Какие HTTP-запросы 24 марта 2019 года отправляла машина 192.168.12.6?

SELECT 
  ip, 
  dt, 
  url 
FROM 
  log 
WHERE 
  dt BETWEEN '2019-03-24 0:00:00' 
  AND '2019-03-24 23:59:59';

К каким доменам обращался IP 192.168.12.6 начиная с 1 ноября 2018 года по настоящее время?

SELECT 
  DISTINCT LEFT(
    RIGHT(
      url, 
      length(url) - (
        position('//' IN url) + 1
      )
    ), 
    position(
      '/' IN RIGHT(
        url, 
        length(url) - (
          position('//' IN url) + 1
        )
      )
    ) -1
  ) AS domain 
FROM 
  log 
WHERE 
  ip = '192.168.12.6' 
  AND dt >= '2018-10-01 0:00:00' 
ORDER BY 
  dt ASC;

Советы по оптимизации запросов

Составляя запросы, важно помнить про ограничения, которые накладываются на индексы. Если использовать индексы правильно, результаты можно получить максимально быстро.

В примере с LEFT, приведенном выше, индекс не используется, запрос будет выполняться довольно долго, потому что LEFT — функция, и поэтому не может использовать индексы. SQL-сервер выполняет эту (и другие) функцию для каждой возвращаемой записи.

Если вы выбираете между LEFT и LIKE, то предпочтите LIKE, если это возможно, потому что LIKE не является функцией и, следовательно, может использовать любые индексы. Но и здесь есть ограничение. Индексы строятся в предположении, что слова/символы условия поиска идут слева направо. Использование символа подстановки в начале условия поиска (например, LIKE '%example.com') или в начале и в конце одновременно (LIKE '%example.com%') исключает для СУБД возможность использования поиска по B-дереву, в то время как LIKE 'http://example.com%' или LIKE 'http://%.com%' задействуют индекс.


Теперь легче получить ответы на интересующие вопросы.