Перейти к публикации
iT4iT.CLUB
Kitsum

Учимся пересылать данные с MQTT брокера в базу данных MySQL

Рекомендованные сообщения

Доброе время суток.

Сегодня пойдет речь о том, как переправлять данные с MQTT брокера в базу данных MySQL. Транспортировать будем как сами адреса, так и значения всех топиков, на которые оформлена подписка. Данную задачу нельзя назвать распространенной, но все же, она имеет место быть и может пригодиться в том случае, если данные востребованы в системах не способных работать с MQTT протоколом самостоятельно или брокер находится в изолированной системе, а данные востребованы, например, в GUI за её приделами.

mqtt_to_mysql_by_it4it.club.png

Для осуществления задуманного нам потребуется самостоятельный процесс, который сыграет роль транспортного узла между MQTT брокером и базой данный MySQL. А значит, его придется где-то держать. В моем случае, это сервер под управлением операционной системой Linux Ubuntu 16.04.3 и дальнейшее описание будет под неё, но для других ОС действия аналогичные.

Сам демон будет написан на Python и для его работы нам потребуется:

  1. python3
  2. python-pip
  3. python-dev
  4. libmysqlclient-dev
  5. библиотека paho-mqtt для python https://pypi.python.org/pypi/paho-mqtt
  6. библиотека mysqlclient для python https://github.com/PyMySQL/mysqlclient-python

Но начнем мы, в первую очередь, с подготовки базы данных.

На плечи MySQL ляжет не только хранение, но и частичная обработка данных. Для этих целей нам потребуется отдельная база, хранимая процедура и функция с реализацией небольшой логики, и пользователь с ограниченными правами под чьим именем мы будем обращаться к ним. В конце поста, кроме самого демона, будет опубликован .sql файл, который достаточно просто импортировать, например с помощью стандартный средств базы данных

mysql -uroot -p

Вводим пароль администратора MySQL и импортируем наш .sql файл, но прежде, дочитайте статью до конца, возможно, Вы захотите внести свои изменения.

mysql> source /media/mqttMySqlClient.sql

После этого будет получен следующий результат:

  1. Создана база (схема) с именем mqtt
  2. Пользователь с именем mqtt-agent и паролем p@$$w0rd имеющий возможность подключаться с внешних адресов
  3. Пользователю будут назначены ограниченные права (только EXECUTE) в этой схеме
  4. Будет добавлена процедура update_topic, на которую ляжет задача добавления и обновления данных
  5. Будет добавлена функция get_topic для упрощения поиска данных

На тот случай, если Вы захотите внести изменения или создать все ручками, рассмотрим содержимое sql файла.

Если схема mqtt не существует, она будет создана

CREATE DATABASE IF NOT EXISTS `mqtt`;

Аналогичным образом будет создан пользователь mqtt-agent. Если необходимо конкретизировать, с какого адреса будет производиться подключение под этим пользователем, то замените % на доменное имя или ip адрес хоста. Если планируется использовать демона на том же сервере где установлен MySQL, замените % на localhost. Также разрешено не более 2 активных подключений, измените это значение на необходимое Вам.

CREATE USER IF NOT EXISTS 'mqtt-agent'@'%' IDENTIFIED BY 'p@$$w0rd' WITH MAX_USER_CONNECTIONS 2;

Пользователю будут выставлены ограниченные права. Ему будет разрешено пользоваться только хранимыми процедурами и функциями. Никакие самостоятельные запросы выполнять нельзя.

GRANT EXECUTE ON `mqtt`.* TO 'mqtt-agent'@'%';

Переходим к работе с самой схемой

USE `mqtt`;

Будет создана таблица topics со следующей структурой.

  • md5 - содержит уникальный одноименный хеш полученный из полного адреса топика. Именно по этому ключу и будет производиться поиск данных. Почему именно по нему, а не по самому имени? Дело в том, что md5 хеш имеет фиксированную, заранее известную, длину, что нельзя сказать о имени топика. Именно это ограничение не позволит сделать имя топика первичным ключом и явно идентифицировать данные в таблице.
  • time - содержит UNIX время добавления/обновления данных по конкретному топику (по умолчанию GMT+0)
  • topic - адрес топика. В контексте, упомянутого ранее, поля md5, не несет для нас никакой смысловой нагрузки.
  • value - данные опубликованные в топике.
DROP TABLE IF EXISTS `topics`;
CREATE TABLE `topics` (
  `md5` varchar(32) NOT NULL,
  `time` bigint(20) DEFAULT NULL,
  `topic` text,
  `value` text,
  PRIMARY KEY (`md5`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Теперь необходимо создать хранимые процедуры и функции, но сделать это будет невозможно из-за присутствие в их синтаксисе разделителя совпадающего с концом данных в sql запросе - ";" Чтобы избежать этот неловкий момент, изменяем разделить на произвольный.

DELIMITER $$

Создает процедуру update_topic. Она принимает в качестве входных параметров два значения, адрес топика и опубликованные данные. Оба параметра являются текстовыми. Процедура, вычисляет md5 хеш из адреса топика и уже по нему производит поиск записи в таблице. Если запись не будет найдена, она будет создана, в противном случае данные в поле value будут обновлены. Данная процедура должна ускорить работу демона и избавить его от задержек, которые были бы неминуемы при выполнении этих же запросов на стороне клиента.

DROP PROCEDURE IF EXISTS `update_topic`$$
CREATE DEFINER=CURRENT_USER() PROCEDURE `update_topic`(topic text, value text)
BEGIN
	declare nMD5 varchar(32) default md5(topic);
	declare NUM bit;
	declare uTime bigint(20) default UNIX_TIMESTAMP();

	SELECT COUNT(t.md5) INTO NUM FROM topics t WHERE t.md5 = nMD5;
	if NUM <> 1 then
		INSERT INTO topics VALUES(nMD5, uTime, topic, value);
	else
		UPDATE topics t SET t.time = uTime, t.value = value WHERE t.md5 = nMD5;
	end if;
END$$

Также будет добавлена функция get_topic. Она необходима для запроса данных от имени созданного ранее пользователя и ограничений, наложенных на него. Функция принимает адрес топика в текстовом виде, производит вычисление md5 хеша и основываясь на его совпадении с имеющимися записями выводит значение поля value искомого топика.

DROP FUNCTION IF EXISTS `get_topic`$$
CREATE DEFINER=CURRENT_USER() FUNCTION `get_topic` (topic text)
RETURNS text
BEGIN
	declare hMD5 varchar(32) default md5(topic);
	declare topicValue text;
	SELECT t.value INTO topicValue FROM topics t WHERE t.md5 = hMD5;
RETURN topicValue;
END$$

И в завершении всего, будет восстановлено стандартное значение разделителя.

DELIMITER ;

На этом разбор sql файла можно считать законченным. Он не содержит каких-либо сложным манипуляций и должен быть понятен. Все эти операции можно выполнить руками, но я совету воспользоваться импортом, как и было описано ранее.

Переходим к демону

В первую очередь устанавливаем необходимые пакеты.

sudo apt-get install python3 python-pip python-dev libmysqlclient-dev

Устанавливаем недостающие библиотеки для Python

pip install paho-mqtt mysqlclient

Добавим пользователя из-под которого будет запускаться демон

sudo useradd --shell /usr/sbin/nologin --system mqtt-agent

Выставляем все необходимые права (каталог /media как пример)

sudo chown mqtt-agent:mqtt-agent /media/mqttMySqlClient.py
sudo chmod 0700 /media/mqttMySqlClient.py

Добавляем демона в автозагрузку через планировщик задач и от имени созданного пользователя

sudo crontab -u mqtt-agent -e

Добавляем в конец следующую запись

@reboot /media/mqttMySqlClient.py start

Запускаем демона от имени все того же пользователя

sudo -u mqtt-agent /media/mqttMySqlClient.py start

Это основное, что требуется сделать на сервере для организации работы демона.

Переходим к разбору настроек программы

Т.к изначально за основу была взята концепция другого демона из ветки Zabbix, то конфигурация перекочевала оттуда и аналогично разбита на несколько секций.

""" Настройки MQTT """
mqtt_server = "mqtt.it4it.club"
mqtt_port = 1883
mqtt_login = ""
mqtt_password = ""
mqtt_client_id = "mqttMySqlClient"

Настройки подключения к брокеру. Все должно быть интуитивно понятным. Помните, что при совпадении идентификаторов клиентов, они начнут конкурировать за подключение и по очереди терять связь. Не допускайте их совпадений.

""" Список топиков для подписки """
subscribe = {
    '$SYS/#',
    '#',
}

Список топиков для подписки указывается через запятую и в кавычках.

""" Настройки MySQL """
mysql_host = "127.0.0.1"
mysql_port = 3306
mysql_user = "mqtt-agent"
mysql_passwd = "p@$$w0rd"
mysql_schema = "mqtt"
mysql_log_file = "/var/log/mqttMySqlClient.log"

Настройки подключения с MySQL серверу также не должны вызывать вопросов.

""" Настройки общие """
pid_file = "/tmp/mqttMySqlClient.pid"

Последний параметр указывает на размещение .pid файла демона.

Команды управления классические

  • start - запуск в режиме демона
  • stop - остановка в режиме демона
  • restart - перезапуск в режиме демона
  • window - запуск в оконном режиме, также позволяет запускать процесс в операционных системах Windows

После запуска, демон пытается установить связь с MQTT брокером и пока это не произойдет, связь с MySQL сервером устанавливаться не будет. Если во время работы, связь с брокером будет потеряна то в принудительном порядке, будет разорвано соединение с базой данных. Таким образом, по активным сессиям MySQL сервера можно судить о наличии связи у демона с брокером. Во время простоя, а в нашем случае, это отсутствие потока данных от брокера, для проверки связи с сервером базы данных будет использована процедура эмуляции ping для MySQL сервера. Она представляет из себя простую арифметическую задачу на сложение не приводящей к работе с данными в базе. Операция выполняется крайне быстро и её удачное выполнение сигнализирует клиенту о наличии связи с базой, а базе об активности клиента. В связи с этим, периодическая активность клиента при отсутствие данных от брокера, является показателем нормальной работы.

И на последок

Если вы хотите произвести выборку из таблицы под именем пользователя используемым по умолчанию и с его ограничениями. Необходимо воспользоваться функцией get_topic с указанием полного адреса, интересующего топика.

select mqtt.get_topic('$SYS/broker/version');

В ответ мы получим

+---------------------------------------+
| mqtt.get_topic('$SYS/broker/version') |
+---------------------------------------+
| mosquitto version 1.4.8               |
+---------------------------------------+
1 row in set (0,00 sec)

На этом пока все.

Файлы проекта: 

PS: Это тестовая версия демона и возможно она будет претерпевать некоторые изменения.

 

  • Like 1

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах

@Kitsum спасибо за полезную статью.

Сам как раз на роутере MQTT брокер локальный поднял, но данные на него в php утянуть нельзя. Поэтому неделю пытался на роутере же( там тоже линукс) поставить paho и таки победил(тот еще фокус c Entware, пока допер что у phyton старые сертификаты. поэтому он один зависимый модуль для установки paho скачать не мог) , но за отсутствием времени отложил разбирательства с phyton. Чтобы дампить данные от MQTT брокера в Mysql базу, а далее с помощью php сделать web интерфейс для данных MQTT брокера и для отправки сообщений через MQTT брокер исполнительным устройствам. А тут Ваша статья, весьма кстати оказалась. вы прям мысли читаете :D Хотя сейчас пока нет времени на это, но ближе к НГ, думаю займусь, и могут появиться вопросы, пожелания. замечания.

Пока исходники не изучал, по описанию возникает вопрос: данный демон и из базы данных выборку делает также в которую сам дампит?

Изменено пользователем Alex_DIY

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах

@Alex_DIY на данный момент - это тестовый вариант демона т.к это мой второй в жизни опыт написания программ на Python. Но я очень рад, что он может быть Вам полезен. Изначально логика работы подразумевает передачу данных только в одном направлении - от MQTT брокера в базу данный MySQL. Мы получаем полный дубликат данных всех топиков на которые подписаны. Я старался, чтобы данные передавались в реальном времени, но не тестировал демона при большой нагрузке, например, несколько сотен сообщений в секунду.

Если Вам понадобится обратная связь, то это вполне можно реализовать, но Вы должны понимать, что передача в обратную сторону будет с задержкой т.к нет явного события, происходящего в момент обновления данных. То есть, при обновлении данных на MQTT брокере, мы получим оповещение, но при обновлении данных в базе MySQL такого события не будет (его можно создать искусственно, но процесс будет сложен для обывателя, не безопасен и не верен с точки зрения задач, возложенных на базу данных), придется периодически опрашивать базу, выявлять изменения и обновлять данные на брокере.

PS: В любом случае, буду рад Вам помочь в изменениях программы, если они понадобятся. Но не представляю, как на маршрутизаторе будет работать MySQL. Я у себя дома завел маленький nettop на Intell Atom. Маленькое энергопотребление и небольшие ресурсы, но их более чем достаточно. Сам компьютер находится за приделами спальных комнат, а питание подается по Passive POE.

IMG_0585.JPGIMG_0584.JPG

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах
14 часа назад, Kitsum сказал:

Изначально логика работы подразумевает передачу данных только в одном направлении - от MQTT брокера в базу данный MySQL. Мы получаем полный дубликат данных всех топиков на которые подписаны.

Мне пока только это и нужно. В обратную сторону можно и извратиться через php  и mosquitto_pub утилиту. В обратную сторону разве что для bash скриптов, но мне это не надо. Да и это лишняя нагрузка на базу.

У меня роутер asus n56 с прошивкой от padavan + Entware подключил. До этого даже вэб сервер на предыдущем asus wl500gp поднимал с пакетом mysql и даже какой-то php форум ставил. Человек 5 держал без лагов :-).asus n56 повеселее в плане железа и прошивка хорошо оптимизирована, так что у меня на нем и vpn сервер крутится(для возможности безопасного серфинга с телефона в публичных wifi сетях).

image.png.8b874452b7b379cc3b58f2ed63d2057d.png Ресурсы есть, учитывая, что mqtt брокер будет обслуживать только меня и мои не очень активные устройства, проблем быть не должно. Неттоп конечно хорошо и упрощает работу, ведь там можно развернуть полноценный линукс, а не с ограничениями как на роутере, но пока роутер справляется с теми небольшими дополнениями, которые я на него навешиваю. Разве что приходится больше времени тратить на настройку и т.п., так как инструкции в сети по настройке и запуску пакетов для линуск не всегда прямо применимы в ограниченном линуксе роутера. Если перестанет хватать, то да, буду подыскивать другую платформу для своих целей.

Вчера загорелся всё-таки потестировать Ваш скрипт. Установил Mysql server, сервер вроде встал, но при создании базы данных вылетает с out of memory. Начал морщить лоб и припомнил. что раньше я не Mysql, а SQLite использовал. Посмотрел, вроде б phyton имеет модуль sqlite. Попробую Ваш скрипт на этой связке запустить. В принципе в репозитории , есть и PostgreSQL так что хоть что-то да должно заработать :-)

 

Изменено пользователем Alex_DIY

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах

@Kitsum сходу sqlite Ваш файл sql не переварил, ругаясь на неверный синтаксис. Начал править, куря мануал по sqlite, затем перечитал еще раз первый пост. Получается демон просто хранит в базе всего лишь последние значения каждого топика насколько я понял? То есть я не смогу из таблицы topics сделать выборку записей определенного топика за какой-то промежуток времени?

Почему выбран вариант подсчет md5 в виде процедуры базы данных, а не силами python? Тут вопрос корыстный, Sqlite такого не может(в плане процедур), поэтому буду этосилами python считать. Хотя, если ответ на вопросы в начале моего сообщения утвердительный, то мои таблицы будут выглядеть совсем иначе получается, и мне хэш имен топика ни к чему, так как я планирую логировать все значения определенных топиков во времени. Пока ясно лишь одно, что надо подумать над структурой своей БД.

Туплю. В базе мы должны иметь таблицу с последними полученными от брокера значениями. Всё верно, иначе мы их никак не получим от брокера, ибо он последние пришедшие не особо то и хранит. Хотя вроде бы показалось, что были опции в конфиге по времени хранения сообщений. но  все равно в базе надежнее. Просто мне нужно отдельную таблицу завести для логирования оперделенных топиков.

CREATE TABLE `topics` (
  `md5` varchar(32) NOT NULL,
  `time` bigint(20) DEFAULT NULL,
  `topic` text,
  `value` text,
  PRIMARY KEY (`md5`),
  UNIQUE KEY `md5_UNIQUE` (`md5`)
)

@Kitsum не понял что делает эта конструкция  UNIQUE KEY `md5_UNIQUE` (`md5`)?

UNIQUE KEY (`md5`) объявляло бы md5 уникальными ключами, но PRIMARY KEY как бы априори содержит уникальные ключи.

Но md5_UNIQUE меня вообще в ступор вводит.

Изменено пользователем Alex_DIY
  • Thanks 1

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах
10 часов назад, Alex_DIY сказал:

Получается демон просто хранит в базе всего лишь последние значения каждого топика насколько я понял? То есть я не смогу из таблицы topics сделать выборку записей определенного топика за какой-то промежуток времени?

Именно так. Чтобы реализовать выборку за определенный промежуток времени, необходимо переработать саму таблицу.

10 часов назад, Alex_DIY сказал:

Почему выбран вариант подсчет md5 в виде процедуры базы данных, а не силами python?

Это попытка переложить часть работы на базу, в нагрузку к реализации логики выбора типа запроса. В Вашем случае придется ввести иной ключ и подумать об оптимизации выборки.

11 час назад, Alex_DIY сказал:

не понял что делает эта конструкция  UNIQUE KEY `md5_UNIQUE` (`md5`)?

Создает уникальный ключ с именем md5_UNIQUE для поля md5. Как Вы и заметили, присутствие данной записи избыточно из-за наличия PRIMARY KEY для этого поля. Относится он к первому варианту таблицы которая была переработана, а признак уникального поля был оставлен по ошибке. Это будет исправлено.

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах
6 часов назад, Kitsum сказал:

признак уникального поля был оставлен по ошибке

т.е. в исправленном виде это будет выглядеть так?

CREATE TABLE `topics` (
  `md5` varchar(32) NOT NULL,
  `time` bigint(20) DEFAULT NULL,
  `topic` text,
  `value` text,
  PRIMARY KEY (`md5`))

Просто на картинке в первом посте столбца md5_UNIQUE не наблюдается.  Я так понимаю, что это md5 от md5? Нагружать за каждое сообщение от брокера повторным расчетом хэша мне кажется это избыточно. 

Я лет 15 назад в университете с базами данных Oracle баловался ( под Windows NT 4.0), но то было давно, да и технологии шагают семимильными шагами, поэтому могут вырываться глупые вопросы :-)

6 часов назад, Kitsum сказал:

Это попытка переложить часть работы на базу

Если и python и база на одном и том железе, то по идее какая разница какой процесс процессорное время скушает.

 

  • Like 1

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах

@Alex_DIY Я внес исправления в пост и SQL файл.

3 часа назад, Alex_DIY сказал:

Просто на картинке в первом посте столбца md5_UNIQUE не наблюдается

Так и должно быть т.к это имя индекса для выбранного поля. Вы можете дать ему другое имя или вообще не задавать его.

3 часа назад, Alex_DIY сказал:

Я так понимаю, что это md5 от md5?

Нет, это не так. Вас скорее всего запутало имя индекса, если бы оно выглядело иначе, например, "abc_UNIQUE", то все было бы яснее. В любом случае стоит пояснить, что происходит на самом деле. Нам необходимо производить быстрый поиск по таблице и сделать это без индекса невозможно. Самым логичным решением было бы использовать в качестве первичного ключа имя топика, но оно имеет тип text, а индексация по этому типу невозможна. Поле должно иметь тип с явно определенной длинной, но тогда мы рискуем обрезать имена топиков и потерять уникальность поля, что приведет к проблемам. Если я ошибаюсь, прошу меня поправить.

Для решения этой проблемы было введено поле с MD5 хешем вычисленным из имени топика. Теперь у нас есть уникальное поле с типом VARCHAR и фиксированной длинной в 32 байта. Оно позволяет четко идентифицировать запись и соответствует конкретному топику. Это поле можно использовать для индексации записей.

mysql> use `mqtt`
Database changed
mysql> EXPLAIN SELECT * FROM topics WHERE md5 = md5('$SYS/broker/version');
+----+-------------+--------+------------+-------+--------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys      | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+--------------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | topics | NULL       | const | PRIMARY,md5_UNIQUE | PRIMARY | 98      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+--------------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0,00 sec)

А вот так бы происходил поиск записи без использования индекса

mysql> EXPLAIN SELECT * FROM topics WHERE topic = '$SYS/broker/version';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | topics | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   48 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0,00 sec)

Никаких вычислений MD5 из MD5 не происходит.

3 часа назад, Alex_DIY сказал:

Нагружать за каждое сообщение от брокера повторным расчетом хэша мне кажется это избыточно

Я не буду утверждать, но мне кажется, что расчет MD5 хеша на уровне MySQL сервера будет выполнен с большей эффективностью чем на стороне Python. Особенно учитывая, что вычисленный хеш индексируется только один раз при добавлении записи. А значит можно провести простой тест.

mysql> select benchmark(1000000, md5('$SYS/broker/version'));
+------------------------------------------------+
| benchmark(1000000, md5('$SYS/broker/version')) |
+------------------------------------------------+
|                                              0 |
+------------------------------------------------+
1 row in set (0,65 sec)

Данные расчеты произведены на Foxconn NanoPC nT-i1250 c процессором Intel Dual Core Atom D2550 1.86GHz и памятью DDR3 объемом 2Gb. Расчеты для Python не производил т.к для этого необходимо написать программу, но если Вам будет интересно, то я сделаю это позже. Но думаю, что полученные результаты для MySQL должны быть вполне убедительны.

Также вынос MD5 расчета был обусловлен разделением обязанностей. Мне показалось более красивым и правильным передавать на MySQL сервер имя топика, а сервер уже самостоятельно принимает решение что и как с ним делать. Также это разделение мне нравится из-за возможности вносить изменения в работу программы не останавливая демона. Это также позволяет расширять функционал, например, добавить необходимое Вам логирование конкретных топиков при этом сам демон не увидит никакой разницы при работе с базой. Ну и до кучи, это логика INSERT/UPDATE без штрафа на передачу данных между демоном и базой, но это уже не имеет никакого отношения к заданному Вами вопросу.

3 часа назад, Alex_DIY сказал:

Если и python и база на одном и том железе, то по идее какая разница какой процесс процессорное время скушает.

Разница будет в реализации алгоритмов, и для одной и той же задачи процессору понадобится разное количество тактов на выполнение. Совсем глубоко я не копал, но это интересный вопрос и, как мне кажется, работа базы более оптимизирована. Также меня волновал момент с реализацией вызова функции on_message в библиотеки paho и я бы хотел перейти максимально быстро к разбору следующего в очереди сообщения. Также я прекрасно отдаю себе отчет, что мой код очень далек от идеала и я трачу много времени на лишние операции и имею большие паузы между ними и это довольно весомый фактор, по крайней мере для меня.

Я буду рад любым предложениям по оптимизации. И еще раз спасибо за интересные и правильные вопросы.

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах
23 часа назад, Kitsum сказал:

имя индекса

именно, про именованные индексы я забыл :( и принял его за столбец таблицы? Теперь ясно.

То , что поиск по индексам происходит  значительно быстрее, чем не по индексированным столбцам это общеизвестно, в приведенном подтверждении по команде EXPLAIN не понял,  разве , что отсутствует оптимизация, не стоит на этом терять время, поясняя мне просто примем за аксиому, да и я это не подвергаю сомнению.

Алгоритм подсчета md5, мне кажется один и стандартизован, поэтому где он реализован быстрее в phyton или Mysql ... думаю скорости сопоставимы, а вот то что  в базу передаются только топики, а то что она индексы сама себе организует, то да, решение более красивое, согласен.

23 часа назад, Kitsum сказал:

Разница будет в реализации алгоритмов, и для одной и той же задачи процессору понадобится разное количество тактов на выполнение.

Если алгоритм един, то одинаковое вроде бы. Если алгоритм разные, то да, будут отличия. Подсчет хэш суммы вряд ли имеет множество алгоритмов. Я думаю, это не те "блохи", которых стоит ловить в свете ожидаемой не высокой нагрузки по решению данных задач и можно охарактеризовать скорость как "очень быстро". По крайней мере на данном этапе вряд ли стоит обращать на это внимание. 

И Вам спасибо, что уделяете время и разжовываете для тех, кто не совсем в теме.

  • Like 1

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах

@Alex_DIY Все же это не дает мне покоя

В данный момент у меня нет доступа к домашнему серверу, но доступна другая машина с Linux Ubuntu 16.04 на борту и оснащенная:

  • Процессор Intel Core i3-3220 3300MHz/3Mb
  • Оперативная память DDR3 4x2Gb 1600MHz
13 часа назад, Alex_DIY сказал:

Алгоритм подсчета md5, мне кажется один и стандартизован, поэтому где он реализован быстрее в phyton или Mysql ... думаю скорости сопоставимы

Сам алгоритм безусловно один, иначе и быть не может, но то, как он реализован, это совсем другое. Допустим, мы имеем две функции перед которыми поставлена одинаковая задача, но реализация будет разная, при этом они будут возвращать идентичный результат. Пусть они будут написаны на Python, но это уже не имеет особого значения, если только они не будут оптимизированы третьей стороной перед исполнением.

def test1(a):
    b = a
    c = b + 1
    return c

def test2(a):
    return a + 1

Логично, что для их исполнения нужно разное количество операций. Конечно, чтобы увидеть результат и разницу по времени, придется вызвать их N-ное количество раз. Я вызвал их в порядке их описания по 10 000 000 раз каждую и получил следующее время.

0:00:01.461331
0:00:01.145653

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

Я понимаю, что возможно все это глупости, и я ловлю не тех "блох", но я отталкиваюсь от той мысли, что мой код далек от совершенства и дабы оптимизировать его, и тем самым ускорить исполнение, я пытаюсь переложить нагрузку на код написанный Программистами совсем другого уровня, чья цель максимально быстро выполнить необходимые вычисления и перейти к следующей операции. Ведь скорость вычисления в СУБД, это один из критических факторов.

Поэтому прошу проникнуться моей идеей и рассмотреть следующий тест.

Проведем по десять тестов с вычислением 1 000 000 MD5 хешей из одной фиксированной строки - "$SYS/broker/version".

mysql> select benchmark(1000000, md5('$SYS/broker/version'));

MySQL выполнил 10 тестов по 1 000 000 вычислений за следующее время

1 row in set (0,20 sec)
1 row in set (0,22 sec)
1 row in set (0,23 sec)
1 row in set (0,23 sec)
1 row in set (0,23 sec)
1 row in set (0,22 sec)
1 row in set (0,23 sec)
1 row in set (0,21 sec)
1 row in set (0,21 sec)
1 row in set (0,22 sec)

Лишний мусор я вырезал оставив только результат по затраченному времени.

Для Python я набросал следующий скрипт. Постарался упростить тест и до его начала произвел преобразование строки в UTF-8.

#!/usr/bin/env python
# coding=utf-8

import hashlib
import datetime

def speedTest(message):
    start = datetime.datetime.now()
    for i in range(0, 1000000):
        hashlib.md5(message).hexdigest()
    finish = datetime.datetime.now()
    print(finish - start)

message = "$SYS/broker/version".encode('utf-8')
for i in range(0, 10):
    speedTest(message)

Те же 10 тестов по 1 000 000 вычислений дают следующий результат.

root@asupmonitor:/media# ./speed.py
0:00:00.832224
0:00:00.833014
0:00:00.782348
0:00:00.788166
0:00:00.782401
0:00:00.785473
0:00:00.784118
0:00:00.782414
0:00:00.789315
0:00:00.788393

Получается, что MySQL производит вычисления MD5 хеша, примерно, в 3 раза быстрее.

Теперь вернемся к самому демону. Я упоминал о вызове функции on_mseeage при поступлении сообщения от MQTT брокера. Насколько я понял, все сообщения обрабатываются библиотекой paho по очереди и никакой многопоточности нет. Чем дольше работаем с сообщением, тем позже перейдем к следующему, а последнее сообщение в очереди будет уныло скучать. Назвать все сообщение равноценными я не могу т.к, температура на улице не имеет такую важность как тревога о протечке воды в квартире или превышение уровня газа в помещении с нагревательным котлом. И с ростом переправляемого трафика увеличится время на его обработку. Я старался оставить в on_message только самое важное.

Возможно я не прав, но позже я планирую уделить больше времени MQTT. Если у Вас есть идеи по оптимизации демона, обязательно пишите и мы вместе улучшим его работу. Еще раз спасибо, что уделили время.

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах
5 часов назад, Kitsum сказал:

Допустим, мы имеем две функции перед которыми поставлена одинаковая задача, но реализация будет разная, при этом они будут возвращать идентичный результат

Это значит, что у функций разный алгоритм. Следовательно, не стоит ожидать одинаковой скорости. Насколько разные - зависит от алгоритмов и оптимизаций, заложенных в компилятор или транслятор. В приведённом примере с test1 и test2 как раз все зависит от компилятора, очевидно, что он мог оптимизировать код, но не сделал этого. 

 

5 часов назад, Kitsum сказал:

я пытаюсь переложить нагрузку на код написанный Программистами совсем другого уровня, чья цель максимально быстро выполнить необходимые вычисления и перейти к следующей операции.

Вряд ли создатели python не ставили целью быстрое выполнение кода, написанного на этом языке. 

 

5 часов назад, Kitsum сказал:

Получается, что MySQL производит вычисления MD5 хеша, примерно, в 3 раза быстрее.

Цифры говорят, что да. А одинаковый ли приоритет на машинное время имеют СУБД и python? Может СУБД имеет приоритет над другим процессами, а phyton нет, поэтому имеет отставание простаивая  в очереди на процессорное время? Это не утверждение, а предположение. Просто как-то в голове не укладывается, что один и тот же алгоритм может давать трехкратную разницу в разных модулях. 

 

5 часов назад, Kitsum сказал:

Насколько я понял, все сообщения обрабатываются библиотекой paho по очереди и никакой многопоточности нет

На 99% уверен, что да, последовательно. Да приоритет важности у сообщений разный, но Вы мыслите глобально, а проект у нас всего лишь позиционируется как домашний. Вряд ли в домашнем проекте таблица со значениями всех топиков брокера превысит 50 строк. Да даже и сто, для СУБД это почти ничто. Плюс скорость получения данных(частота опроса)  она тоже не стремится к бесконечности, чтобы даже на этих 50 записях нагрузки СУБД(хотя тут ещё надо сделать оговорку на железо, где СУБД работает). Далее, стоит также оценить задержку из-за "неоптимальной" работы СУБД, ну пусть она будет 3 секунды, в домашнем применении это ни на что не влияет. Даже протечка- время закрытия крана больше. Поэтому по моему мнению, существенного влияния на домашнее применение задержки не окажут, да, чисто академический интерес в оптимизации присутствует, согласен. У самого частенько включается режим перфекциониста и хочется сделать максимально правильно, даже там, где этого не нужно. 

Вернусь к базе. Я слегка обновил  в памяти знания и  учитывая область применения и объём хранимой информации (не более 50 записей). Primary key у нас итак априори уникальный. Плюс ко всему primary key итак является индексируемым полем, то у меня сомнения в использовании отдельного индекса. Так как это потери времени на операциях вставки - обновления полей из-за перестроения таблицы индексов. Может ошибаюсь. 

Также,  учитывая малое количество записей индексирование вообще не даст заметных плюсов в выборке, а вот задержек в insert/update добавить может. Ошибаюсь? 

P.S.хотел описать, что  для поездки в булочну не годятся подходы, применяемые для проектирования самолета истребителя, да, истребитель быстр, но вряд ли оптимально на нем летать в булочную за углом дома. НО прочел название темы и не увидев в нем четкого позиционирования в низкопроизводительный класс, беру свои слова, касательно "не более 50 строк в табилце" обратно. Свое применение наложил на данной средство и с этой позиции веду обсуждение, что наверное не верно.

Изменено пользователем Alex_DIY
  • Like 1

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах
18 часов назад, Alex_DIY сказал:

А одинаковый ли приоритет на машинное время имеют СУБД и python?

md5_mysql_vs_python_r2.pngmd5_mysql_vs_python_r1.png

Сразу даже и не задумался об этом, но получается, что равный.

18 часов назад, Alex_DIY сказал:

Primary key у нас итак априори уникальный. Плюс ко всему primary key итак является индексируемым полем, то у меня сомнения в использовании отдельного индекса. Так как это потери времени на операциях вставки - обновления полей из-за перестроения таблицы индексов. Может ошибаюсь. 

Именно первичный ключ и остался после исправления, unique был выкинут за ненадобностью. Безусловно SELECT будет выполняться быстрее всего. INSERT медленнее, но со временем таблица будут наполнена, а новые записи хоть и будут появляться, но довольно редко. А вот с UPDATE все сложнее, каким именно образом СУБД решит производить обновление сказать сложно, это очень тонкая грань и требует хороших знаний. Я предполагаю, что отметка об удалении и последующее добавлении записи происходить не будет, произойдет фактическое обновление т.к мы не затрагиваем индексируемые поля. А также поиск обновляемой записи будет происходить с использованием индекс. Но я не компетентен в таких тонкостях и строить дальнейших догадок не буду, но вопрос очень интересный и стимулирующий.

18 часов назад, Alex_DIY сказал:

Также,  учитывая малое количество записей индексирование вообще не даст заметных плюсов в выборке, а вот задержек в insert/update добавить может. Ошибаюсь?

Если отказаться от индексов, то при поиске записи (SELECT/UPDATE) будет происходит сверка со всеми записями в таблице. Мне кажется, что это плохая идея.

18 часов назад, Alex_DIY сказал:

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

Соглашусь с данным высказыванием. Пожалуй, стоит уделить больше энергии и времени самому демону.

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах
В 30.11.2017 в 10:46, Kitsum сказал:

Сразу даже и не задумался об этом, но получается, что равный.

В таком случае очень непонятен отрыв Mysql от  phyton приотработке одного и того же алгоритма. Впрочем, в связи с тем, что у меня вместо Mysql SQLite3, а там функций нет, то это лишь в копилку знаний пойдет, если в будущем перейду на полноценную СУБД, то нужно будет переносить эту функцию в СУБД.

 

В 30.11.2017 в 10:46, Kitsum сказал:

Если отказаться от индексов

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

В 30.11.2017 в 10:46, Kitsum сказал:

А вот с UPDATE все сложнее

Я сперва тоже подумал, что вроде бы ничего не добавляем, зачем перестраивать, НО если учесть, что индексы могут быть не уникальными и UPDATE может изменять и индексируемое поле, а следовательно и индекс, то почему бы СУБД в этом случае не перестроить дерево индексов.Но у это не наш случай, поэтому , я думаю, нас будет затрагивать только INSERT.
P.S. ерунду сморозил про уникальность. Она тут не при чем. Операцией update мы и уникальный индекс можем поменять (если он уникальный, то на другое уникальное значение :-) ), что тоже вызовет перестроение дерева индексов.

Изменено пользователем Alex_DIY
грамматика
  • Like 1

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах

Отличная статья! Огромное спасибо! Помогите решить проблему

У меня при старте демона возникает проблема: 

Traceback (most recent call last):
  File "/media/mqttMySqlClient.py", line 9, in <module>
    import paho.mqtt.client as mqtt  # https://pypi.python.org/pypi/paho-mqtt
ImportError: No module named paho.mqtt.client
 

Пожалуйста, подскажите как её решить?

 

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах

Доброе время суток @makkirus

Ошибка явно указывает на то, что интерпретатор не знает ничего о библиотеки paho-mqtt, возможно ее попросту нет. Внимательно проверьте, все ли пункты инструкции Вы выполнили.

Например под Linux Ubuntu посмотреть установлена библиотека или нет можно следующей командой.

pip list | grep paho-mqtt

В ответе Вы должны получить что-то подобное

paho-mqtt (1.4.0)

А для отладки запускайте демона с параметром window

mqttMySqlClient.py window

Если Вы все правильно сделали, то результат должен быть примерно таким

image.png image.png

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах

Вы правы, не было в системе модуля. Теперь все ок. Данные в базе есть.

Для отладки запустил с параметром window  и вот что на экране в консоли.

Результат такой: sudo -u mqtt-agent /media/mqttMySqlClient.py window
2019-07-23 00:57:03 connecting to MQTT broker...
2019-07-23 00:57:03 mysql connected
2019-07-23 00:57:03 mqtt connected
client id: mqttMySqlClient
2019-07-23 00:57:03 mqtt subscribe on "#"
2019-07-23 00:57:03 mqtt subscribe on "$SYS/#"
2019-07-23 00:57:03 mysql disconnect
2019-07-23 00:57:03 connecting to MQTT broker...
2019-07-23 00:57:03 mqtt connected
client id: mqttMySqlClient
2019-07-23 00:57:03 mqtt subscribe on "#"
2019-07-23 00:57:03 mqtt subscribe on "$SYS/#"
2019-07-23 00:57:03 mysql [2006]
2019-07-23 00:57:03 mysql [2006]
2019-07-23 00:57:03 mysql [2006]
2019-07-23 00:57:03 mysql [2006]
2019-07-23 00:57:03 mysql ping fail [2006]
2019-07-23 00:57:03
unexpected termination of the program
Traceback (most recent call last):
  File "/media/mqttMySqlClient.py", line 305, in <module>
    start()
  File "/media/mqttMySqlClient.py", line 188, in start
    alert('\nunexpected termination of the program', True)
  File "/media/mqttMySqlClient.py", line 46, in alert
    with open(mysql_log_file, 'a+') as log:
IOError: [Errno 13] Permission denied: '/var/log/mqttMySqlClient.log'
esp-master@mqtt:~$
 

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах

@makkirus рад, что Вы получили данные, но в предоставленном логе присутствуют ошибки. Это как минимум разрыв соединения с MySQL сервером и отсутствие прав на запись логов. Но это уже не связано с демоном.

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах

Добрый день.

Скрипт я так понял не поддерживает mqtt работающий с SSL сертификатами ?

И планируете ли Вы добавить в скрипт что бы он работал  в обратном порядке  ? 

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах

@emaus доброе время суток.

09.11.2019 в 13:31, emaus сказал:

Скрипт я так понял не поддерживает mqtt работающий с SSL сертификатами ?

В данном примере нет, но функционал шифрования добавляется с помощью метода tls_set с соответствующими параметрами. Посмотреть их можно тут https://www.eclipse.org/paho/clients/python/docs/

09.11.2019 в 13:31, emaus сказал:

И планируете ли Вы добавить в скрипт что бы он работал  в обратном порядке  ?

К сожалению, это проблематично т.к единственный способ оперативно отслеживать изменения в СУБД это триггеры и по умолчанию они не позволяют вызывать внешние программы в самой ОС а только лишь исполняют код в пределах самой СУБД т.к это противоречит политике безопасности. Но сделать это возможно.

Но гораздо более простой вариант, это отправлять сообщение в нужную ветку на брокер в той программе которая должна была вносить изменения в БД. Это не противоречит политике безопасности и гораздо проще чем компилировать собственные модули для СУБД.

Или есть медленный вариант, это свой собственный механизм отслеживания изменений, когда демон периодически опрашивает таблицу в БД, находит изменения и уже потом отправляет изменения на брокер. Но этот метод будет иметь задержку равную периоду опроса БД и потребует строгого соблюдения правил обновления данных в таблице сторонними скриптами. Например, может потребоваться указывать признак обновления для определения статуса записи, по которому демон будет находить изменения, не копаясь во всех записях. Или для обновления записей должны будут использоваться только пользовательские функции, реализующие механизм идентификации записей. В общем, над этим можно подумать, но на сколько это будет востребовано...

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах
21 час назад, Kitsum сказал:

@emaus доброе время суток.

В данном примере нет, но функционал шифрования добавляется с помощью метода tls_set с соответствующими параметрами. Посмотреть их можно тут https://www.eclipse.org/paho/clients/python/docs/

К сожалению, это проблематично т.к единственный способ оперативно отслеживать изменения в СУБД это триггеры и по умолчанию они не позволяют вызывать внешние программы в самой ОС а только лишь исполняют код в пределах самой СУБД т.к это противоречит политике безопасности. Но сделать это возможно.

Но гораздо более простой вариант, это отправлять сообщение в нужную ветку на брокер в той программе которая должна была вносить изменения в БД. Это не противоречит политике безопасности и гораздо проще чем компилировать собственные модули для СУБД.

Или есть медленный вариант, это свой собственный механизм отслеживания изменений, когда демон периодически опрашивает таблицу в БД, находит изменения и уже потом отправляет изменения на брокер. Но этот метод будет иметь задержку равную периоду опроса БД и потребует строгого соблюдения правил обновления данных в таблице сторонними скриптами. Например, может потребоваться указывать признак обновления для определения статуса записи, по которому демон будет находить изменения, не копаясь во всех записях. Или для обновления записей должны будут использоваться только пользовательские функции, реализующие механизм идентификации записей. В общем, над этим можно подумать, но на сколько это будет востребовано...

Что-то не могу понять(наверно я не то делаю) пытаюсь запустить скрипт с шифрованием но не выходит.

Вот что сделал, просто добавил это ниже записи mqttc.connect(mqtt_server, mqtt_port, 60)

                mqttc.tls_set(ca_certs="mqtt.host.ru.pem", certfile=None, keyfile=None, cert_reqs=ssl.CERT_REQUIRED,
                       tls_version=ssl.PROTOCOL_TLSv1_2, ciphers=None)

Сертификат генерировал таким способом

echo -n | openssl s_client -connect mqtt.host.ru:8883 | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > mqtt.host.ru.pem

Подскажите что не так я дела(скорей всего все)

--

Я думаю будет востребовано обратный процесс, к примеру дергать релешки через веб браузер или приложение :)

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах

Большое спасибо, все заработало :)

Еще вопросик если можно)

php скриптом делаю json масив, но когда добавляю новый топик в скрипт массив ломается(индексы меняются)

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

Хотелось бы что бы они шли в столбик и при добавление нового топика он добавлялся в конец.

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах

@emaus не совсем понимаю, что Вы делаете т.к объяснение идет в контексте другой программы. Вам стоит предоставить Ваш код и более подробно описать что вы хотите сделать. И если этот вопрос никак не связан с текущим проектом, то лучше создать отдельную тему или напишите мне в приват.

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах
17.11.2019 в 01:07, Kitsum сказал:

@emaus не совсем понимаю, что Вы делаете т.к объяснение идет в контексте другой программы. Вам стоит предоставить Ваш код и более подробно описать что вы хотите сделать. И если этот вопрос никак не связан с текущим проектом, то лучше создать отдельную тему или напишите мне в приват.

Я про данный скрипт и говорю.

Покажу в картинках.

1й рисунок до добавление новых топиков

1.thumb.JPG.8bc892fc5c6dd64d687c40497ad53a71.JPG

2й после добавление новых топиков.

Добавил два новых топика (/villagebox/in/sensor1/temp/ и /villagebox/in/sensor1/hum/)

2.thumb.JPG.ab36544dc826807c9056580ba3ad9395.JPG

 

С помощью php скрипта я делаю json массив

<?php
// Set the JSON header
        header("Content-type: text/json");

        $mysqli = new mysqli("localhost", "mqtt-agent", "mqttpass", "mqttdb");
        $mysqli -> query("SET NAMES 'utf8'");

        if ($mysqli->connect_error) {
            echo("Connection failed: " . $mysqli->connect_error);}
        //else
          //  echo "Connection Ready\n\n";

    $data = array();
    $result = $mysqli -> query("SELECT md5,topic,value FROM topics");
    while(($row = $result->fetch_assoc())) {
        //$data[] = array($row['md5'],$row['topic'],(int)$row['value']);
        $data[]= $row;
    }
       echo json_encode($data, JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT | JSON_NUMERIC_CHECK | SORT_DESC);
?>

Проблема в том что и в выводе массива что и в базе, при добавление нового топика строки меняются местами меняются местами, а хотелось бы что бы при добавление нового топика он добавлялся в самый конец.

Мне кажется что они так выстраиваются по по значению в столбце md5

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах

@emaus нет, так не получится т.к ключи Вашего массива никак не связаны с его значениями. Сейчас, условно, в $data[5] лежат одни данные, а через секунду совершенно другие и это можно устроить даже не добавляя новые записи в таблицу.

Все, что Вам требуется это использовать понятные пары ключей и значений, например, так

while($row = $result->fetch_assoc()) {
	$data[$row['topic']] = $row['value'];
}

Далее Вы можете легко обращаться к значениям по имени топика и делать это хоть с массивом, хоть с объектом json. А если потребуется перебрать все, что там есть, то можно использовать foreach, благо он есть во всех современных языках.

Поделиться сообщением


Ссылка на сообщение
Поделиться на других сайтах

Пожалуйста, войдите для комментирования

Вы сможете оставить комментарий после входа



Войти сейчас

  • Похожие публикации

    • Автор: Kitsum
      Всем привет, в этой статье поговорим об уже надоевшей всем теме - "Метеостанция". Каждый пытается сделать что-то свое, вот и я не стал исключением и попытался материализовать свои эротические фантазии на контроллере ESP8266. Тема задумывалась уже давно как некое обновление для предыдущего проекта этой тематики, но из-за своей неспешности переросла в нечто самостоятельное.


      При всей привлекательности микроконтроллера ESP8266 с его большим объемом памяти, железной поддержкой Wi-Fi и массой разных плюшек, он не лишен недостатков. Самый основной - ограниченное количество поддерживаемых одновременных TCP соединений равное 5. Если превысить этот лимит, то контроллер потеряет связь с окружающим миром, при этом watchdog будет думать, что все в порядке, а следовательно, даже не попытается нам помочь. Будем стараться это помнить!
      Стоит начать с концепции
      Доступ к данным метеостанции нужно получать без установки внешних приложений и под любой операционной системой. Для этих целей подойдет практически любой современный браузер. Меня всем устраивает Chrome. Раз уж за основу взят HTTP протокол, стоит озаботиться экономией трафика и ограничением числа TCP соединений. Хорошим тоном будет передача всего необходимого для формирования страницы контента только при первом обращении, а все последующие операции, такие как отображение показаний с датчиков или настройку контроллера, производить через API. В этом нам поможет JQuery. А вот, чтобы ослабить болевые ощущения от передачи файлов с SPI Flash в браузер, стоит предусмотреть систему кэширования, например, Etag. Это позволит отдавать тяжелый контент единожды, а при последующих загрузках страницы просто подтверждать его актуальность на уровне Web сервера микроконтроллера и кэш браузера вступит в игру, неимоверно уменьшив время загрузки страницы! "Вы были правы в одном, Мастер: переговоры были недолгими." © Звездные войны. Эпизод 1 Из-за того, что метеостанция с датчиками и контроллером должна располагаться на улице, жизненно необходимо предусмотреть возможность обновлять прошивку ESP через Web интерфейс. Аналогичным образом должны обновляться файлы Web сервера расположенные на SPI Flash. Этот и предыдущий пункт вкупе позволят обновлять функционал микроконтроллера из домашней сети или из интернета, если конечно в этом возникнет острая необходимость. Чтобы никто посторонний не могу вмешаться в работу устройства или изменить файлы Web сервера, последний должен хотя бы как-то себя защищать. Пускать в панель управления только после авторизации, блокировать доступ при попытках брутфорса пароля. В конце концов, контроллер обязан самостоятельно генерировать ключи (salt) для авторизации, дабы сделать алгоритм непредсказуемым и исключить потенциальный взлом, в случае если злодей завладеет исходниками проекта. Понятно, что кому она там нужна, эта метеостанция, если её не завязывать с умным домом, если только из-за спортивного интереса, но как говориться “Береженого Бог бережет”. Датчики стоит расположить по уму - в метеобудке, а вот контроллер в сухом и закрытом боксе. Объединить их между собой, как мне кажется, удобнее по I2C шине - минимум проводов, максимум удобства. Практически на всех вариантах плат ESP-xx имеется штатный светодиод, можно воспользоваться им как для индикации режимов и состояния микроконтроллера, так и для вывода какой-либо промежуточной информации. Что касаемо режимов работы ESP8266, как ни странно, но он должен находить домашнюю Wi-Fi сеть и подключаться к ней. Если вдруг звезды не были к нам благосклонны, и домашняя беспроводная сеть приказала долго жить, контроллер обязан перейти в режим точки доступа (AP) дабы к нему можно было подключиться с какого-либо устройства и перенастроить его на другую сеть. А вот пока последнее не произошло, ESP должен периодически сканировать эфир в поисках долгожданной домашней точки доступа и, если боги были к нам милосердны, и домашняя сеть появилась в эфире, незамедлительно переключиться в режим клиента (STA) и в пылу страсти воссоединиться с ней. Ну и естественно, как же без отправки данных на внешние ресурсы, сейчас без этого не обходится ни одна уважающая себя кофеварка, не говоря уже о метеостанции. Думаю, что основным блюдом станет протокол MQTT, это уже облегчает возможность интеграции с умным домом, стулом или той же кофеваркой. Ну а на закуску добавим поддержку "ThingSpeak" и "Народного мониторинга". При желании можно нарастить функционал, благо памяти у микроконтроллера еще много. Как я себе это представляю
      Учтите, что на видео, данные с датчиков, эмитируются самим микроконтроллером, это нужно для наглядности. В жизни метеорологическая обстановка намного спокойнее слава Богу.
      Перейдем к физической сборки устройства
      Как по мне, так самый оптимальный вариант, это воспользоваться отладочной платой NodeMCU V3 и базой для неё. Таким образом, мы получим отличный комплект с разведенной на его борту всей необходимой обвязкой и возможностью питать устройство от 5 до 24 Вольт.

      Отладочная плата на базе, и смотрится хорошо, и удобства хоть отбавляй.

      Заливаем прошивку, образ SPI Flash и подключаем четырьмя проводами датчики. Справится даже ребенок.
      Ссылки:
      Базовая плата для NodeMCU V3 с преобразователем питания 5-24V в 5V Отладочная плата ESP8266 от NodeMCU Естественно никто не запрещает Вам развести свою плату. Если Вы это сделаете, скиньте нам свое творение, возможно мы перейдем на него. В идеале, все должно размещаться в метеобудке.
      Датчики взятые за основу
      Теперь настал момент озаботиться, где описанные выше ребята будут жить. В прошлый раз мы использовали для этих целей, найденную в подножном корме, электрическую распределительную коробку. Кроме дешевизны в этом решении нет ничего положительного.
      В этот раз мы воспользуемся более серьезным вариантом – "Метеорологическая будка Стивенсона". Она способна защитить датчики от прямых воздействий окружающей среды, но при этом имеет открытую структуру со стенками в виде жалюзи. Удобно, красиво и самое главное – правильно!
      Будка печатается на 3D принтере по эскизам опубликованным на Thingiverse неким kowomike, спасибо добрый человек! Архив с эскизами можно будет скачать в конце поста.

      Фото готовой будки

      Шпилька М8 крепится через зажимной хомут к мачте уличной антенны.
      Примерка. Шпилька практически не укорачивалась, чтобы не закрывать будку параболической Wi-Fi антенной.
      Хотя в моем случае все это сделано не правильно т.к это солнечная сторона дома. Доступа на теневую сторону дома у меня нет, поэтому приходиться довольствоваться тем, что имеем. По прошлой метеостанции мне говорили "на солнечной стороне все эти измерения - сферический конь в вакууме, слепи %описание-многА-букАв% и закрепи на теневой стороне дома".
      Я пока живу в панельном многоквартирном доме, как и не малая часть нашей страны. Доступ к теневой стороне дома (а для меня, по факту, это окна в подъезде) - прямой вызов всем гопникам района трущимся рядом, любопытным соседям с бегающими глазками и всей элите человечества скрашивающей фоном мою унылую и слишком простую, по их мнению, жизнь. Думаю, что мысль я донес.

      Датчики располагаются на разных уровнях. В основании находится датчик освещенности BH1750 и смотрит ровно вниз. Мне кажется, так он будет меньше пачкаться и покрываться пылью и при этом смотреть наружу сквозь минимальное количество препятствий для солнечного света. Вообще размещение этого датчика, это целая головная боль. Как не крути, все будет не то. Оставил так, ведь по сути важны не сами показания, а тенденция изменения. Хотя кого я пытаюсь обмануть, точность важна всегда! Предлагайте свои варианты.
      Намного проще обстоят дела с датчиком атмосферного давления BMP180 и влажности SI7021, кстати, с последнего мы также будем забирать данные о температуре. Их размещаем в оставшемся свободном пространстве будки, благо его там с избытком, но не в конусе т.к пространство в нем менее проветриваемое.

      Все хозяйство подключается между собой следующим образом
      NodeMCU | ESP 07/12 | Датчики ----------------------------- D2 | GPIO 4 | SDA D1 | GPIO 5 | SCL 3.3V | 3.3V | 3.3V GND | GND | GND ВАЖНО: при финальном монтаже устройства на его место службы, обязательно установите перемычку между пинами GPIO 0 (D3) и питанием 3.3 Вольта. Причины её установки описаны в закрепленном сообщении с описание обновления от 12.08.2017.
      Сам микроконтроллер будет спрятан в уже знаменитую распределительную коробку, закрепленную на шпильке, чуть ниже будки Стивенсона. У меня все находится на стадии неторопливой сборки с попутным поиском более удачных идей.
      Плата расширения, на которой будет установлена плата NodeMCU, закреплена через ножки для крепления компьютерных материнских плат в корпусах.

      Разъемы для подключения внешних датчиков и питающей линии установил на местах где была пара штатных заглушек. Закрепил все через переходную пластину, выпиленную из куска фольгированного текстолита. Естественно, предварительно пластина была протравлена, а вся медь искоренена, ибо в этом случае она нам не друг.
      Также была предусмотрена проставка из полиэтиленового поролона (используется в качестве упаковочного материала при транспортировке грузов) между текстолитом и корпусом, общей толщиной 5мм, а после затяжки крепежных винтов, его толщина не превышает 1мм. Это было сделано из-за опыта эксплуатации предыдущего (временного) бокса для этой метеостанции. Без проставки влага быстро найдет путь вовнутрь, и срок службы устройства снизится.
      Производим примерку.
      При окончательном монтаже обязательно необходимо удалить все не плотно прилегающие части полиэтиленового поролона, то есть те части, которые располагаются снаружи и не сдавлены крепежной текстолитовой пластиной. Это необходимо сделать для препятствования накоплению влаги в доступных для неё полостях. Также пришлось увеличить число крепежных болтов для более надежного прилегания текстолита, в противном случае он может выгибаться.
      Все самое сложное позади, остается только вывести на один разъем шину i2c с питание 3.3 Вольта, а на другой подвести пины питания платы расширения. Но т.к у меня валялся "хвост" отрезанный когда-то от не рабочего блока питания маршрутизатора, и я не побрезговал им воспользоваться по прямому назначению.

      Далее останется все подравнять, проверить качество монтажа, возможность замены платы NodeMCU, если это будет необходимо при эксплуатации и самое главное, дважды проверить, что и куда припаяно. Мои кривые руки и невнимательность уже наказывали меня, а т.к ждать новые запчасти долго, повторять не хочется.

      Общий вид получился таким
      А вот как все выглядит в боевых условиях. Кстати, могу предложить идею с помещением в бокс мешочка содержащий впитывающий влагу гель, они часто встречаются в коробках с обувью. Если все герметично, то он впитает остатки влаги, а если нет, то лишним уж точно не будет.


      Требования (!!!Читать обязательно!!!)
      Arduino IDE с поддержкой контроллера ESP8266, версия 2.6.2 (на версиях выше работоспособность не проверялась) Установленный модуль в Arduino IDE для загрузки файлов во Flash память микроконтроллера. Как установить описано тут. Для работы модуля загрузки файлов во Flash может понадобится последняя версия Python https://www.python.org/downloads/ Любой модуль на базе ESP8266 c Flash 4MB (3MB выделяем под SPIFFS) В параметрах выставляем lwIP версии 2 и максимальную производительность (lwIP v2 Higher Bandwidth) Сам архив с последней версией проекта. Скачать можно в конце статьи или по этой ссылке.   
      Обязательные библиотеки (!!!Читать обязательно!!!)
      ArduinoJson (v5.13.5) PubSubClient Ссылки на библиотеки сенсоров указаны в комментариях к коду. Сами библиотеки, как и обслуживаемые ими сенсоры, не являются обязательными. Вы вольны использовать любые датчики, как физические, так и программные.
      Порядок установки (!!!Читать обязательно!!!)
      Изучите файлы проекта с примерами использования тех или иных сенсоров. Все файлы с примерами начинаются с префикса users_, это users_auto.h, users_bme280_x2.h и т.д. Загрузите необходимые Вам библиотеки или используйте эти файлы как пример для добавления иных датчиков. Выставите необходимые настройки для контроллера в среде разработки Arduino IDE. Пример настроек указан на скриншоте выше. Обязательно убедитесь, что выбрано правильное распределение места для внутренней файловой системы, это значит, что 3MB должно быть выделено под файловую систему. Также проверяем, чтобы использовался lwIP v2 в режиме максимальной производительности (lwIP v2 Higher Bandwidth). Произведите загрузку программы с помощью среды разработки (Ctrl + U). Произведите загрузку содержимого каталога data в файловую систему. Меню/Инструменты/ESP8266 Sketch Data Upload Перед тем как устанавливать метеостанцию на постоянное место жительства, подтянуть GPIO-0 (пин D3 на плате NodeMCU) к питанию 3.3V. Во время данной процедуры, питание на контроллере должно отсутствовать. Первый запуск (!!!Читать обязательно!!!)
      Помните, что вся конфигурация микроконтроллера производится исключительно через web интерфейс. Никаких изменений значений тех или иных параметров в коде не требуется, а подобную практику будем считать плохим тоном.
      И так, после запуска микроконтроллера он сразу перейдет в аварийный режим и поднимет собственную точку доступа с именем WeatherStation. Это нормальное поведение т.к подразумевается использование метеостанции в домашней беспроводной сети, ну а раз о ней пока ничего не известно, то и подключаться не к чему.
      Подключитесь к данной сети с любого удобного устройства и перейдите в панель управления (для этого имеется соответствующая иконка, запутаться невозможно), контроллер будет доступен по адресу http://espws.local или http://192.168.4.1 При попытке входа в панель управления будет запрошено имя пользователя и пароль, по умолчанию admin/admin. После входа в панель управления перейдите в раздел "Основные настройки WiFi" и укажите имя и пароль Вашей домашней сети, а также, при необходимости, укажите пароль для подключения к точке доступа поднимаемой контроллером в аварийном режиме. Если все сделано правильно, то контроллер подключится к домашней сети в течении 5-и минут.
      Если Ваша домашняя сеть скрыта, то после первоначальной настройки необходимо перезагрузить контроллер. Это необходимо из-за частичной поддержки работы со скрытыми сетями. После перезагрузки контроллер увидит Вашу сеть и запомнит её MAC адрес. Помните об этом если захотите сменить домашний маршрутизатор.
      Хотите помочь проекту или спонсировать новый?
      Yandex.Money PayPal.me Файлы
       
    • Автор: Kitsum
      Модуль предназначен для системы мониторинга Zabbix. Работает в качестве отдельного демона и осуществляет транзит сообщений от брокера до хостов в системе мониторинга. Подробное описание модуля можно посмотреть в следующей теме.
       
    • Автор: Kitsum
      Демон осуществляет транзит данных с MQTT брокера в СУБД MySQL. Можно переправлять как все сообщения, так и конкретные топики. Подробное описание можно посмотреть в следующей теме.
       
    • Автор: Kitsum
      Update 03.12.2015
      Добавлена возможность отображать DNS имена для определенных хостов, это позволит явно и человеко-понятно идентифицировать соответствующие узлы в сети. Отсутствие записи на DNS сервере должно привлечь соответствующее внимание к хосту.  
      В конфигурацию добавлен параметр $domain позволяющий удалять из DNS имени хоста имя домена. Чтобы из name.domain.com сделать name, необходимо указать $domain = ".domain.com" Обновлена база производителей сетевого оборудования - oui.txt Мелки доработки интерфейса.  
      Update 26.11.2015
      Добавлена возможность отображать реальные имена портов (Fa1, Ethernet1/0/1, Port1 ...). Внимание: не все устройства способны передавать данную информацию, в связи с этим оставлены числовые идентификаторы, чтобы в таких случаях иметь систему идентификации.  
      Добавлена возможность описывать в конфигурации настройки для каждого устройства отдельно. Если не использовать эту возможность, то для оборудования будет применена общая конфигурация. Добавлена возможность описывать в конфигурации уникальные OID-ы для конкретного оборудования аналогично п.2. Мелкие доработки интерфейса и кода.  

      В данной теме мы будем рассуждать о мониторинге сетевого оборудования и подключенных к нему хостах. Следовало бы разместить её в другом разделе, но 90% всех манипуляций и дальнейшая эксплуатация будет производиться на UNIX-подобной платформе. В моем случае используется:
      Linux Ubuntu 14.04 Apache/2.4.7 PHP 5.5.9 Библиотека php5-snmp Также само сетевое оборудование (маршрутизаторы и свичи) должно поддерживать работу по протоколу SNMP и соответствовать стандартам ISO.
      Проект разработан отталкиваясь от топологии сети - звезда. Имеются множество отдельных подсетей 192.168.0.0 все они ходят во внешний мир через маршрутизаторы (в моем случае фирмы Cisco). В самих подсетях используются различные свичи, по большей части поддерживающие SNMP v1 и выше.
      Ранее я уже поднимал данную тему на другом, дружественном, форуме, но потом работа над проектом бала остановлена. На данный момент вопрос опять стал актуален и решено переделать как основную программу, так и внешний вид. Старая версия была написана на скорую руку и выглядела невзрачно и с большими изъянами в коде.
      На данный момент проект преобразился и выглядит следующим образом. Ваш браузер должен поддерживать HTML5.
      Чтобы избежать вопроса "зачем это нужно?" предлагаю Вам задачку. Попробуйте назвать номер порта на свиче к которому подключен компьютер дяди Васи зная лишь его IP. Или еще интереснее - зная лишь производителя его сетевого оборудования. Или вопрос от начальника "Скажи мне, кто подключен к этому свичу, а я пока узнаю, кого нужно искать".
      Раньше это делалось следующим образом. Подключаемся к маршрутизатору и спрашиваем, какой MAC принадлежит интересующему нас IP, пусть это будет 192.168.0.3
      cisco-router#sh arp | include Vlan1 Internet 192.168.0.1 210 0800.0694.d027 ARPA Vlan1 Internet 192.168.0.2 210 0800.bdf0.0010 ARPA Vlan1 Internet 192.168.0.3 210 000e.be08.001c ARPA Vlan1 Internet 192.168.0.4 210 000e.42ee.20cf ARPA Vlan1 Internet 192.168.0.5 210 0800.218e.be09 ARPA Vlan1 Internet 192.168.0.6 5 20cf.0800.000e ARPA Vlan1 Internet 192.168.0.7 - 001c.0010.000e ARPA Vlan1 Internet 192.168.0.8 6 0010.1fc4.0800 ARPA Vlan1 Internet 192.168.0.9 2 d027.0694.0800 ARPA Vlan1 ... Теперь мы знаем MAC, это 000e.be08.001c. Подключаемся к свичу и просим его показать нам таблицу MAC адресов с сортировкой по конкретному MAC-у.
      cisco-switch#sh mac-address-table | include 000e.be08.001c 000e.be08.001c Dynamic 1 FastEthernet21 Теперь мы знаем, что интересующий нас (или не нас) хост использует порт FastEthernet21
      Все прошло относительно быстро и гладко. Но что делать, если порт имеет порядковый номер превышающий общее число физических портов на свиче, оборудование разных производителей и оно не поддерживает одинаковый формат MAC адресов, отсутствуют различные фильтры, как например "include" в оборудовании Cisco. При этом может быть несколько свичей с десятками хостов. Все это повлияет на время поиска и на конечный результат.
      Реализация
      Мы знаем, что и у какого оборудования спрашивать. Так и давайте спрашивать это используя SNMP протокол. Первым делом необходимо настроить Ваше сетевое оборудование, выставить необходимые разрешения и community. Описывать этот пункт нету смысла т.к у Вас своё оборудования, а у дяди Васи совсем другое.
      На сервере, где установлен Apache необходимо доставить библиотеку реализующий работу snmpwalk в PHP
      apt-get install php5-snmp service apache2 restart На самом деле большая часть работы уже сделана. Теперь необходимо скачать сам скрипт, он будет прикреплен к данному посту и отредактировать его под себя.
      Редактируем .htaccess
      RewriteEngine On Options +FollowSymlinks Options -MultiViews RewriteBase /switch/ RewriteCond %{REQUEST_FILENAME} !-f RewriteCond %{REQUEST_FILENAME} !-d RewriteRule (.*) index.php?$1 [L] Убедитесь, что у Вас подключен модуль mod_rewrite и измените относительный путь указанный в RewriteBase на удобный Вам. Если закинуть каталог switch в корневую веб директории Apach, то вносить изменений в файл не нужно.
      Редактируем index.php
      $url = 'http://'.$_SERVER["HTTP_HOST"].'/switch/'; $domain = '.domain.com'; $community = 'public'; $unit = array( 'network_id' => array( 'name' => 'Желаемое для отображения имя подсети или объекта где сеть эксплуатируется', 'router' => '192.168.1.1', 'switch' => array('192.168.1.2'), ), 'object_name' => array( 'name' => 'Желаемое для отображения имя подсети или объекта где сеть эксплуатируется', 'router' => '192.168.2.1', 'switch' => array('192.168.2.2', '192.168.2.3', '192.168.2.4'), ), // UPDATE 26.11.2015 'new_object' => array( 'name' => 'new object name', 'router' => '192.168.3.1', 'switch' => array('192.168.3.2', '192.168.3.3'), // Уникальные настройки для маршрутизатора 192.168.3.2 '192.168.3.1' => array( 'community' => 'public2', ), // Уникальные настрокий для свича 192.168.3.3 '192.168.3.3' => array( 'community' => 'public3', 'ifName' => '.1.3.6.1.2.1.31.1.1.1.1', 'dot1dBasePortIfIndex' => '.1.3.6.1.2.1.17.1.4.1.2', ), ), ); Мы старались сделать конфигурацию интуитивно понятной, получилось это или нет, спорный вопрос. Поживем увидем, а пока разберем, что тут к чему.
      $url - содержит http адрес до каталога со скриптом. Измените /switch/ на используемый Вами network_id, object_name - любой понравившийся идентификатор для подсети. Используйте латиницу и\или цифры и не используйте пробелы. name - описание для подсети. Оно будет отображаться в списке на главной странице скрипта и в навигационном баре router - IP адрес маршрутизатора, через который подсеть ходит во внешний мир switch - список IP адресов свичей используемых в подсети Можно производить уникальную конфигурацию для любого сетевого устройства. Для этого в конфигурации нужного объекта необходимо указать адрес устройства и описать массив настроек для него. Можно изменять абсолютно любые существующие значения в скрипте. ВНИМАНИЕ: если Вы не понимаете, за что отвечает та или иная переменная и как устроена эта кухня, лучше обратитесь к нам, и мы Вам обязательно поможем!
      Список рекомендуемых для изменения значений (описание значений имеется в самом скрипте):
      community atPhysAddress sysDescr dot1dTpFdbAddress dot1dTpFdbPort dot1dBasePortIfIndex ifName Определение производителя по MAC адресу
      Каждому производителю выделяется определенный список MAC адресов для использования в его сетевом оборудовании. Ознакомиться с этим списком можно по адресу http://standards-oui.ieee.org/oui.txt Мы включили этот файл в состав архива, но советуем Вам периодически обновлять его.
      В скрипте используется shell_exec
      $vendor = explode('(hex)', shell_exec("cat ./oui.txt | grep ".str_replace(' ', '-', substr($mac, 0, 8)))); Это потенциально не безопасно, но мы не передаем ей данные полученные от пользовательского ввода, только MAC адреса. В любом случае shell_exec должен быть разрешен или часть кода, отвечающая за определения производителя, должна быть удалена.
      Есть очень интересная статья на сайте CISCO. Она поможет Вам разобраться в принципах работы данного скрипта. К сожалению я узнал об этой статье уже после написания большей части кода программы. Но уже имеются идеи для следующей версии!
      PS: Если все сделано правильно, то Вы сможете насладится нашим велосипедом. Проект будет дорабатываться и обрастать всяческими "свистульками". Приятного использования 😃
       
  • Сейчас на странице   0 пользователей

    Нет пользователей, просматривающих эту страницу.

×
×
  • Создать...