Re: [DASH] Количество запросов в БД
Добавлено: Ср мар 24, 2021 2:25 pm
Количество нулей и количество запросов совсем разные вещи.
В одном запросе могут находится разные данные и не важно сколько там нулей, запрос всё равно один.
Страница 14 из 20
Re: [DASH] Количество запросов в БД
Добавлено: Ср мар 24, 2021 2:40 pm
Re: [DASH] Количество запросов в БД
Добавлено: Ср мар 24, 2021 8:16 pm
сегодня озаботился чуток этой темой,
у меня 300 запросов в минут? много это или мало?
давайте разбираться....
все запросы пытаться разобрать не буду.
mariadb включаем log-queries-not-using-indexes
лог собирался 8,5 часов.
тут результаты анализа:
всего около 60 тыс. запросов.
1.
из них 11 тыс запросов на чистку буквально нескольких таблиц:
DELETE FROM shouts WHERE (TO_DAYS(NOW())-TO_DAYS(ADDED))>7;
DELETE FROM operations_queue WHERE EXPIRE<NOW();
а действительно ли надо каждые 5 секунд чистить список сообщений?
про второй запрос ничего не скажу пока не разбирался.
2. 30 тыс. запросов SELECT * FROM cached_ws;
слава богу таблица мемори. тем не менее 58 запросов в минуту!
дальше по списку можно еще много поискать интересного.
интересно последнее: из 60 тыс запросов всего 715 имеют природу отличную от 8 стандартных вещей.
ну и как результат сегодняшней разбора ИНДЕКСЫ!:
и отдельный привет разработчику модулей mercury, yaweather, starline
в модулях дикий баг в таблицах *_config - отсутствует первичный ключ.
как следствие данные при каждом рестарте мажордомо дублируются. у меня сегодня было 1815 строк вместо 5.
там все быстро и легко чинится. но надо лечить не только таблицу но и код. буквально в 1 месте.
у меня 300 запросов в минут? много это или мало?
давайте разбираться....
все запросы пытаться разобрать не буду.
mariadb включаем log-queries-not-using-indexes
лог собирался 8,5 часов.
тут результаты анализа:
СпойлерПоказать
начало:15:11:07
конец: 23:52:34
cat slow_queries.log|grep SELECT|wc -l
51635
cat slow_queries.log|grep DELETE|wc -l
11859
cat slow_queries.log|grep SELECT|grep cached_ws|wc -l
29988
cat slow_queries.log|grep SELECT|grep shouts|wc -l
7696
cat slow_queries.log|grep SELECT|grep "TOTAL FROM phistory_queue"|wc -l
3940
cat slow_queries.log|grep SELECT|grep miio_queue|wc -l
4512
cat slow_queries.log|grep SELECT|grep "phistory_queue ORDER BY ID LIMIT 200"|wc -l
3960
cat slow_queries.log|grep SELECT|grep "INFORMATION_SCHEMA"|wc -l
705
cat slow_queries.log|grep SELECT|grep -v shouts|grep -v cached_ws|grep -v "TOTAL FROM phistory_queue"|grep -v "miio_queue"|grep -v "phistory_queue ORDER BY ID LIMIT 200"|grep -v "INFORMATION_SCHEMA"|grep -v "information_schema"|wc -l
715
конец: 23:52:34
cat slow_queries.log|grep SELECT|wc -l
51635
cat slow_queries.log|grep DELETE|wc -l
11859
cat slow_queries.log|grep SELECT|grep cached_ws|wc -l
29988
cat slow_queries.log|grep SELECT|grep shouts|wc -l
7696
cat slow_queries.log|grep SELECT|grep "TOTAL FROM phistory_queue"|wc -l
3940
cat slow_queries.log|grep SELECT|grep miio_queue|wc -l
4512
cat slow_queries.log|grep SELECT|grep "phistory_queue ORDER BY ID LIMIT 200"|wc -l
3960
cat slow_queries.log|grep SELECT|grep "INFORMATION_SCHEMA"|wc -l
705
cat slow_queries.log|grep SELECT|grep -v shouts|grep -v cached_ws|grep -v "TOTAL FROM phistory_queue"|grep -v "miio_queue"|grep -v "phistory_queue ORDER BY ID LIMIT 200"|grep -v "INFORMATION_SCHEMA"|grep -v "information_schema"|wc -l
715
1.
из них 11 тыс запросов на чистку буквально нескольких таблиц:
DELETE FROM shouts WHERE (TO_DAYS(NOW())-TO_DAYS(ADDED))>7;
DELETE FROM operations_queue WHERE EXPIRE<NOW();
а действительно ли надо каждые 5 секунд чистить список сообщений?
про второй запрос ничего не скажу пока не разбирался.
2. 30 тыс. запросов SELECT * FROM cached_ws;
слава богу таблица мемори. тем не менее 58 запросов в минуту!
дальше по списку можно еще много поискать интересного.
интересно последнее: из 60 тыс запросов всего 715 имеют природу отличную от 8 стандартных вещей.
ну и как результат сегодняшней разбора ИНДЕКСЫ!:
СпойлерПоказать
ALTER TABLE `dev_httpbrige_devices` ADD INDEX(`CHTIME`);
ALTER TABLE `idevices` ADD INDEX(`GET_LOCATION`);
ALTER TABLE `idevices` ADD INDEX(`UPDATED`);
ALTER TABLE `pinghosts` ADD INDEX(`CHECK_NEXT`);
ALTER TABLE `webvars` ADD INDEX(`CHECK_NEXT`);
ALTER TABLE `obj_prptransfer` ADD INDEX( `LINKED_OBJECT`, `LINKED_PROPERTY`);
ALTER TABLE `zontcommands` ADD INDEX( `LINKED_OBJECT`, `LINKED_PROPERTY`);
ALTER TABLE `zontcommands` ADD INDEX( `SYSTEM`, `DEVICE_ID`);
ALTER TABLE `plugins` ADD INDEX(`MODULE_NAME`);
ALTER TABLE `xicommands` ADD INDEX( `LINKED_OBJECT`, `LINKED_PROPERTY`);
ALTER TABLE `scripts` ADD INDEX(`TITLE`);
ALTER TABLE `mercury_costs` ADD INDEX( `IDDEV`);
ALTER TABLE `mercury_costs` ADD INDEX( `IDDEV`, `YEAR`, `MONTH`);
ALTER TABLE `AliIPRelays` ADD INDEX(`IP`);
ALTER TABLE `zigbee2mqtt` ADD INDEX( `LINKED_OBJECT`, `LINKED_PROPERTY`);
ALTER TABLE `terminals` ADD INDEX(`IS_ONLINE`);
ALTER TABLE `terminals` ADD INDEX(`HOST`);
ALTER TABLE `camshoter_config` ADD INDEX(`parametr`);
ALTER TABLE `safe_execs` ADD INDEX(`EXCLUSIVE`);
ALTER TABLE `camshoter_devices` ADD INDEX(`ENABLE`);
ALTER TABLE `camshoter_devices` ADD INDEX(`HOURLY`);
ALTER TABLE `locations` ADD INDEX(`TITLE`);
нужна смена движка на myisam obj_prptransfer, чтобы сделать фултекст индексы
ALTER TABLE `obj_prptransfer` ADD FULLTEXT( `LINKED_OBJECT`, `LINKED_PROPERTY`);
ALTER TABLE `obj_prptransfer` ADD FULLTEXT(`LINKED_OBJECT`);
ALTER TABLE `obj_prptransfer` ADD FULLTEXT(`LINKED_PROPERTY`);
ALTER TABLE `zigbee2mqtt_log` ADD INDEX(`FIND`);
ALTER TABLE `terminals` ADD INDEX(`LATEST_ACTIVITY`);
ALTER TABLE `terminals` ADD INDEX(`CANTTS`);
ALTER TABLE `terminals` ADD INDEX(`TTS_TYPE`);
ALTER TABLE `settings` ADD INDEX(`NAME`);
ALTER TABLE `properties` ADD FULLTEXT(`TITLE`);
ALTER TABLE `patterns` ADD INDEX(`PARENT_ID`);
ALTER TABLE `patterns` ADD FULLTEXT(`TITLE`);
ALTER TABLE `performance_log` ADD INDEX(`ADDED`);
ALTER TABLE `users` ADD FULLTEXT(`USERNAME`);
ALTER TABLE `layouts` ADD INDEX(`HIDDEN`);
ALTER TABLE `layouts` ADD INDEX(`PRIORITY`);
ALTER TABLE `layouts` ADD INDEX(`TITLE`);
ALTER TABLE `commands` ADD INDEX(`PARENT_ID`);
ALTER TABLE `megadproperties` ADD INDEX(`COMMAND`);
ALTER TABLE `megadproperties` ADD INDEX(`TYPE`);
ALTER TABLE `idevices` ADD INDEX(`GET_LOCATION`);
ALTER TABLE `idevices` ADD INDEX(`UPDATED`);
ALTER TABLE `pinghosts` ADD INDEX(`CHECK_NEXT`);
ALTER TABLE `webvars` ADD INDEX(`CHECK_NEXT`);
ALTER TABLE `obj_prptransfer` ADD INDEX( `LINKED_OBJECT`, `LINKED_PROPERTY`);
ALTER TABLE `zontcommands` ADD INDEX( `LINKED_OBJECT`, `LINKED_PROPERTY`);
ALTER TABLE `zontcommands` ADD INDEX( `SYSTEM`, `DEVICE_ID`);
ALTER TABLE `plugins` ADD INDEX(`MODULE_NAME`);
ALTER TABLE `xicommands` ADD INDEX( `LINKED_OBJECT`, `LINKED_PROPERTY`);
ALTER TABLE `scripts` ADD INDEX(`TITLE`);
ALTER TABLE `mercury_costs` ADD INDEX( `IDDEV`);
ALTER TABLE `mercury_costs` ADD INDEX( `IDDEV`, `YEAR`, `MONTH`);
ALTER TABLE `AliIPRelays` ADD INDEX(`IP`);
ALTER TABLE `zigbee2mqtt` ADD INDEX( `LINKED_OBJECT`, `LINKED_PROPERTY`);
ALTER TABLE `terminals` ADD INDEX(`IS_ONLINE`);
ALTER TABLE `terminals` ADD INDEX(`HOST`);
ALTER TABLE `camshoter_config` ADD INDEX(`parametr`);
ALTER TABLE `safe_execs` ADD INDEX(`EXCLUSIVE`);
ALTER TABLE `camshoter_devices` ADD INDEX(`ENABLE`);
ALTER TABLE `camshoter_devices` ADD INDEX(`HOURLY`);
ALTER TABLE `locations` ADD INDEX(`TITLE`);
нужна смена движка на myisam obj_prptransfer, чтобы сделать фултекст индексы
ALTER TABLE `obj_prptransfer` ADD FULLTEXT( `LINKED_OBJECT`, `LINKED_PROPERTY`);
ALTER TABLE `obj_prptransfer` ADD FULLTEXT(`LINKED_OBJECT`);
ALTER TABLE `obj_prptransfer` ADD FULLTEXT(`LINKED_PROPERTY`);
ALTER TABLE `zigbee2mqtt_log` ADD INDEX(`FIND`);
ALTER TABLE `terminals` ADD INDEX(`LATEST_ACTIVITY`);
ALTER TABLE `terminals` ADD INDEX(`CANTTS`);
ALTER TABLE `terminals` ADD INDEX(`TTS_TYPE`);
ALTER TABLE `settings` ADD INDEX(`NAME`);
ALTER TABLE `properties` ADD FULLTEXT(`TITLE`);
ALTER TABLE `patterns` ADD INDEX(`PARENT_ID`);
ALTER TABLE `patterns` ADD FULLTEXT(`TITLE`);
ALTER TABLE `performance_log` ADD INDEX(`ADDED`);
ALTER TABLE `users` ADD FULLTEXT(`USERNAME`);
ALTER TABLE `layouts` ADD INDEX(`HIDDEN`);
ALTER TABLE `layouts` ADD INDEX(`PRIORITY`);
ALTER TABLE `layouts` ADD INDEX(`TITLE`);
ALTER TABLE `commands` ADD INDEX(`PARENT_ID`);
ALTER TABLE `megadproperties` ADD INDEX(`COMMAND`);
ALTER TABLE `megadproperties` ADD INDEX(`TYPE`);
в модулях дикий баг в таблицах *_config - отсутствует первичный ключ.
как следствие данные при каждом рестарте мажордомо дублируются. у меня сегодня было 1815 строк вместо 5.
там все быстро и легко чинится. но надо лечить не только таблицу но и код. буквально в 1 месте.
СпойлерПоказать
TRUNCATE!!!
ALTER TABLE `mercury_config` ADD PRIMARY KEY(`parametr`);
INSERT INTO `mercury_config` (`parametr`, `value`) VALUES
('EVERY', '30'),
('LASTCYCLE_TS', '1616563975'),
('CURRENT', ''),
('LASTCYCLE_TXT', '0'),
('DEBUG', '')
TRUNCATE!!!
ALTER TABLE `yaweather_config` CHANGE `parametr` `parametr` VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `yaweather_config` ADD PRIMARY KEY(`parametr`);
TRUNCATE!!!
ALTER TABLE `starline_config` CHANGE `parametr` `parametr` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `starline_config` ADD PRIMARY KEY(`parametr`);
ALTER TABLE `mercury_config` ADD PRIMARY KEY(`parametr`);
INSERT INTO `mercury_config` (`parametr`, `value`) VALUES
('EVERY', '30'),
('LASTCYCLE_TS', '1616563975'),
('CURRENT', ''),
('LASTCYCLE_TXT', '0'),
('DEBUG', '')
TRUNCATE!!!
ALTER TABLE `yaweather_config` CHANGE `parametr` `parametr` VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `yaweather_config` ADD PRIMARY KEY(`parametr`);
TRUNCATE!!!
ALTER TABLE `starline_config` CHANGE `parametr` `parametr` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `starline_config` ADD PRIMARY KEY(`parametr`);
Re: [DASH] Количество запросов в БД
Добавлено: Ср мар 24, 2021 9:30 pm
Re: [DASH] Количество запросов в БД
Добавлено: Чт мар 25, 2021 12:56 am
Вот эти циклы незапущены, нужны ли они и если нет то наверное как-то надо их удалить? Может тоже поможет разгрызить БД.
cycle_connect
cycle_rss
cycle_skype
cycle_sys_date_format
cycle_X101_skype
phistory 80984 с этим без понятия что делать
sqlqueries 48339
camshoter_log 1884
zigbee2mqtt_log 51381 а тут наверное каждая принятая строчка по mqtt общитывается, то тогда Sonoff работает как спамер от одного Sonoff basic прилетает около 10 всякого рода сообщений, когда нужно всего одно - о состоянии реле
cycle_connect
cycle_rss
cycle_skype
cycle_sys_date_format
cycle_X101_skype
phistory 80984 с этим без понятия что делать
sqlqueries 48339
camshoter_log 1884
zigbee2mqtt_log 51381 а тут наверное каждая принятая строчка по mqtt общитывается, то тогда Sonoff работает как спамер от одного Sonoff basic прилетает около 10 всякого рода сообщений, когда нужно всего одно - о состоянии реле
Re: [DASH] Количество запросов в БД
Добавлено: Пт апр 23, 2021 5:03 pm
Приветствую. Дочитал таки до конца, только вопросы нарыл, а не ответы. Во первых, что такое ПУ? Чем оно так страшно?
У меня было 230 с лишним запросов в секунду. Почистил минутный таймер немного, стало 330. Закоментировал и поудалял там почти всё, вплоть до режимов работы, мне они не нужны. Запросов стало 660. После некоторой оптимизации уже в системе целиком. количество запросов зашкалило за 1300. Через путти обновил систему, запросы вернулись к 330, но система работает, как хромая блоха на трёх лапах. Может минимум запросов самоцель просто? У меня больше дюжины esp, xiaomi кое что, яндекс колонка. Я хочу сразу видеть состояние некоторых вещей, и всё это уместить в 23 запроса в секунду? Лично меня старый мажор устраивал больше, чем новый. Если всё и везде писать ручками, не проще ли перейти туда, где это по умолчанию. Меня больше волнует phistory. Что в ней так накуралесили? Почему некоторые параметры хотят хранить историю год? Я меняю на 30 дней, через какое то время получаю системную ошибку, а срок возвращается в 365. Возвращаясь к началу, да, некоторые датчики опрашиваются слишком часто, но настроек для чайников это регулировать нет, вроде. А знаний не хватает, что то править после программистов. Даст бог, что то допилят, обнова придёт, опять всё слетит. Опять всё переделывать? У меня малинка и так недавно греться начала, и нагрузка 150-200 %.
Re: [DASH] Количество запросов в БД
Добавлено: Пт апр 23, 2021 8:09 pm
Уже не помню к чему вопрос был, но, ПУ - это простые устройства модуль девайсес. По поводу запросов, почему у вас они возрастают? Не понятно. Смотрите для начала в системном логе, возможно у вас имеются крослинки, это такая штука, когда один скрипт зацепляет запуск другог, а то в свою очередь первый скрипт , но и внутренних посредников таких скриптов может быть хот Мульйон...
Теперь теория запросов к бд... Первое это выливается в износ материальных ценностей таких как хдд, цпу, елекироенергиЯ, и соответственно нагревание железки ну и т.д.
Теория 2 - сколько устройств у вас ежесекундно передает данные - ну точно не 300, даже с учётом 5 запросов к бд на 1 устройство - точно не 60...
Так вот к чему я веду - что каждый запрос к бд означает какую-то роботу цпу. А выходит в итоге что это пустая работа вашего устройства... Тогда вопрос нафига нужен такой уд ( умный дом) который не может даже за себя подумать (судя по вашей нагрузке на цпу) а не то что за остальной дом. Поэтому лучше все таки пересмотреть все ваши методы, скрипты и ТД - для понимания почему такая нагрузка. А все как сказано выше надо начинать с логов МДМ... Там много можно полезного найти плюс поставить модуль измерялку запросов к бд чтобы и там можно было бы покопаться. Ну сумбурно и не по порядку но где то так...
Re: [DASH] Количество запросов в БД
Добавлено: Пт апр 23, 2021 8:33 pm
история подрезается в оптимайзере без программированияКарл Маркс писал(а): ↑Пт апр 23, 2021 5:03 pm... А знаний не хватает, что то править после программистов. Даст бог, что то допилят, обнова придёт, опять всё слетит. Опять всё переделывать? У меня малинка и так недавно греться начала, и нагрузка 150-200 %.
Re: [DASH] Количество запросов в БД
Добавлено: Пт апр 23, 2021 10:12 pm
Спасибо за разъяснения. Логи смотреть у меня не получается, или мозила тупит порой. Я и сейчас mqtt потерял. Дополнение работает, москита улетела куда то. От часов статусы приходят. Чинить не умею. Бакап родной не открывается, точнее страница обновления. Там кнопка "Очистить временную папку", которая нажимается, но не реагирует. Приложение бакап недавно пыхтело, пыхтело, так и смогло создать копию. Вот и сижу, что можно переношу вручную на третью малинку. Потом придётся всё обратно переносить. Оптимизировал, называется.tarasfrompir писал(а): ↑Пт апр 23, 2021 8:09 pmУже не помню к чему вопрос был, но, ПУ - это простые устройства модуль девайсес. По поводу запросов, почему у вас они возрастают? Не понятно. Смотрите для начала в системном логе, возможно у вас имеются крослинки, это такая штука, когда один скрипт зацепляет запуск другог, а то в свою очередь первый скрипт , но и внутренних посредников таких скриптов может быть хот Мульйон...
Теперь теория запросов к бд... Первое это выливается в износ материальных ценностей таких как хдд, цпу, елекироенергиЯ, и соответственно нагревание железки ну и т.д.
Теория 2 - сколько устройств у вас ежесекундно передает данные - ну точно не 300, даже с учётом 5 запросов к бд на 1 устройство - точно не 60...
Так вот к чему я веду - что каждый запрос к бд означает какую-то роботу цпу. А выходит в итоге что это пустая работа вашего устройства... Тогда вопрос нафига нужен такой уд ( умный дом) который не может даже за себя подумать (судя по вашей нагрузке на цпу) а не то что за остальной дом. Поэтому лучше все таки пересмотреть все ваши методы, скрипты и ТД - для понимания почему такая нагрузка. А все как сказано выше надо начинать с логов МДМ... Там много можно полезного найти плюс поставить модуль измерялку запросов к бд чтобы и там можно было бы покопаться. Ну сумбурно и не по порядку но где то так...
Я пробовал. Если выбрать в настройках размер поменьше, что то долго делает, ждать надоедает, торможу, всё на месте. Если по одной строчке оптимизировать, в конце пишет, ничего не удалено.xor писал(а): ↑Пт апр 23, 2021 8:33 pmистория подрезается в оптимайзере без программированияКарл Маркс писал(а): ↑Пт апр 23, 2021 5:03 pm... А знаний не хватает, что то править после программистов. Даст бог, что то допилят, обнова придёт, опять всё слетит. Опять всё переделывать? У меня малинка и так недавно греться начала, и нагрузка 150-200 %.
СпойлерПоказать
- 365.JPG (71.41 КБ) 2360 просмотров
Re: [DASH] Количество запросов в БД
Добавлено: Пт апр 23, 2021 10:29 pm
указываете своему объекту/свойству нужную глубину, без агрегации и долго? дождитесь хоть раз окончания с одним свойствомКарл Маркс писал(а): ↑Пт апр 23, 2021 10:12 pmЯ пробовал. Если выбрать в настройках размер поменьше, что то долго делает, ждать надоедает, торможу, всё на месте. Если по одной строчке оптимизировать, в конце пишет, ничего не удалено.
- 2021-04-23_22-26-54.png (8.77 КБ) 2358 просмотров