Hi, I’m struggling after upgraded to 1.53. slow login\ slow WebUI\ we were experiencing slow performance in all activities.
| Version | 1.53.1-42-gc0c3ad2 - Mon Jul 22 2019 03:41:04 GMT+0800 |
|---|---|
| DB Schema | 2019_05_30_225937_device_groups_rewrite (135) |
| Web Server | nginx/1.12.2 |
| PHP | 7.2.16 |
| MySQL | 5.7.26-0ubuntu0.18.04.1 |
| RRDtool | 1.7.1 |
no problems here,
your discription… is a bit global…
TTFB
Time To First Byte
Look webserver
or on graph look your load on your databases from rrdtools (rrdcahed) ( aka your storage / iops / etc)
Else i dont know
You didn’t post the full output of validate
Yes all those fails And warns need to be fixed don’t just ignore things. the poller not completing needs to be addressed and discovery not running also.
Look in the docs for performance help.
thank you kevin. i will focus on rrdcached
we opened the slow_sql.log and we found this below:
# Time: 2019-07-24T12:01:55.966733Z
# User@Host: librenms[librenms] @ [10.1.0.213] Id: 226576
# Query_time: 29.131667 Lock_time: 0.000035 Rows_sent: 1 Rows_examined: 109419476
SET timestamp=1563969715;
select count() as aggregate from notifications left join notifications_attribs on
notifications_attribs.notifications_id = notifications.notifications_id where
(notifications_attribs.key = ‘sticky’ and notifications_attribs.value = 1) or (not exists (select 1
from notifications_attribs where notifications.notifications_id = notifications_attribs.notifications_id
and notifications_attribs.user_id = 26));
# Time: 2019-07-24T12:02:22.652229Z
# User@Host: librenms[librenms] @ [10.1.0.213] Id: 226850
# Query_time: 29.477817 Lock_time: 0.000033 Rows_sent: 1 Rows_examined: 109419476
SET timestamp=1563969742;
select count() as aggregate from notifications left join notifications_attribs on
notifications_attribs.notifications_id = notifications.notifications_id where
(notifications_attribs.key = ‘sticky’ and notifications_attribs.value = 1) or (not exists (select 1
from notifications_attribs where notifications.notifications_id = notifications_attribs.notifications_id
and notifications_attribs.user_id = 26));
1、what’s this sql for？
2、How do we will solve this problem,by Index or something else ?
seems solved。according to this issue and thx acritox:
using this sql to build the indexes
CREATE INDEX notifications_attribs_user_idx ON notifications_attribs (notifications_id,user_id);
By the way,Do we have a DBA to optimize our sqls and tables？
