Page open very slowly

Help
1

Hi
We have use libreNMS to monitor our Network DEVS.
devs:4000+
ports:310000+

system install like this：

image
image894×653 55.3 KB

Server has high perfomace.(cpu:E5-2680 mem:384G disk:ssd)
Mysql + Mancache + Nginx + Php7.0 php-fpm
I had done some tuning on mysql,but page is still open very slowlly(every page over 10s).

Could you show me some tuning ways to help this slow issue.

Thanks.

2

Hi,

how many users logs into front in the same time? Did you try varnish configuration or multi front instances balanced via haproxy?

3

image

4

Hi,

Hi,

how many users log to web interface in the same time? Did you try varnish configuration or multi front instances balanced via haproxy?

5

All pages are really slow? If so, sounds like it’s timing out on connecting to something.

Run ./validate.php and check /opt/librenms/logs/librenms.log on the web server

6

Hi

Thanks for your reply.
We don’t have many user,almost 10 - 15 users and they won’t online at same time.
I don’t think user due to slow page.
varnish is a good hint for me.
I will try it,and report later.

Thanks

7

Hi

Thanks for your reply.
Page will show sucessfully at last.
Maby not time out case.

Here is the result:
fatal: No names found, cannot describe anything.

Component Version
LibreNMS
DB Schema 252
PHP 7.0.27-0+deb9u1
MySQL 5.7.22-log
RRDTool 1.6.0
SNMP NET-SNMP 5.7.3

====================================

[OK] Composer Version: 1.6.5
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database schema correct
[WARN] Some devices have not been polled in the last 5 minutes. You may have performance issues.
[FIX] Check your poll log and see: http://docs.librenms.org/Support/Performance/
Devices:
10.163.104.151
10.163.105.120
10.60.6.44
43.230.90.126
10.163.72.18
10.80.163.137
10.163.103.35
[FAIL] Some devices have not completed their polling run in 5 minutes, this will create gaps in data.
[FIX] Check your poll log and see: http://docs.librenms.org/Support/Performance/
Devices:
10.80.163.118
fatal: No names found, cannot describe anything.
[WARN] Your install is over 24 hours out of date, last update: Thu, 14 Jun 2018 07:31:14 +0000
[FIX] Make sure your daily.sh cron is running and run ./daily.sh by hand to see if there are any errors.
[WARN] Your local git contains modified files, this could prevent automatic updates.
[FIX] You can fix this with ./scripts/github-remove
Modified Files:
includes/defaults.inc.php
[FAIL] We have found some files that are owned by a different user than librenms, this will stop you updating automatically and / or rrd files being updated causing graphs to fail.
[FIX] chown -R librenms:librenms /home/librenms
Files:
/home/librenms/tmp/PqN64LeySfxnTvEw
/home/librenms/tmp/iorLwzjV8yeuItAE
/home/librenms/tmp/pei3XmUhwqe7h20c
/home/librenms/tmp/sjJDNowWwmUXcaUN
/home/librenms/tmp/wf8gIaG7ET3NrEBB
/home/librenms/tmp/3XvC6ppvMVTsiemJ
/home/librenms/tmp/4LRa0Y9l0g9fxQrH
/home/librenms/tmp/cRFnsatqLVSic6Wd
/home/librenms/tmp/yZRex8f3jvuedENU

librenms.log
/home/librenms/poller.php 3029 2018-06-27 09:50:16 - 1 devices polled in 19.66 secs
/home/librenms/poller.php 2245 2018-06-27 09:50:17 - 1 devices polled in 4.146 secs
/home/librenms/poller.php 5547 2018-06-27 09:50:17 - 1 devices polled in 13.06 secs
/home/librenms/poller.php 1327 2018-06-27 09:50:17 - 1 devices polled in 4.632 secs
/home/librenms/poller.php 5503 2018-06-27 09:50:17 - 1 devices polled in 18.11 secs
/home/librenms/poller.php 557 2018-06-27 09:50:18 - 1 devices polled in 9.998 secs
/home/librenms/poller.php 4057 2018-06-27 09:50:18 - 1 devices polled in 13.02 secs
/home/librenms/poller.php 3251 2018-06-27 09:50:18 - 1 devices polled in 18.35 secs
/home/librenms/poller.php 4635 2018-06-27 09:50:19 - 1 devices polled in 17.22 secs
/home/librenms/poller.php 955 2018-06-27 09:50:19 - 1 devices polled in 4.579 secs
/home/librenms/poller.php 1037 2018-06-27 09:50:19 - 1 devices polled in 5.341 secs
/home/librenms/poller.php 6851 2018-06-27 09:50:19 - 1 devices polled in 1.787 secs
/home/librenms/poller.php 7039 2018-06-27 09:50:20 - 1 devices polled in 3.360 secs
/home/librenms/poller.php 4519 2018-06-27 09:50:20 - 1 devices polled in 22.87 secs
/home/librenms/poller.php 7733 2018-06-27 09:50:20 - 1 devices polled in 10.63 secs
/home/librenms/poller.php 6813 2018-06-27 09:50:20 - 1 devices polled in 13.81 secs
/home/librenms/poller.php 4613 2018-06-27 09:50:21 - 1 devices polled in 30.58 secs
/home/librenms/poller.php 859 2018-06-27 09:50:21 - 1 devices polled in 6.442 secs

Thanks

8

Hi

I set the slowlog at mysql and php-fpm.
Then I didn’t find anything strange at log,but page is slowly.

mysql slowlog:
…skipping…
SET timestamp=1530064175;
SELECT COUNT(*) FROM ports AS I, devices AS D WHERE I.deleted = ‘0’ AND D.device_id = I.device_id AND I.ignore = ‘0’ AND D.ignore = ‘0’ AND I.ifOperStatus = ‘up’;

Time: 2018-06-27T09:49:45.235788+08:00

User@Host: librenms[librenms] @ [10.200.191.68] Id: 7038219

Query_time: 1.049544 Lock_time: 0.000057 Rows_sent: 1 Rows_examined: 487351

SET timestamp=1530064185;
SELECT COUNT(*) FROM ports AS I, devices AS D WHERE I.deleted = ‘0’ AND D.device_id = I.device_id AND I.ignore = ‘0’ AND D.ignore = ‘0’ AND I.ifOperStatus = ‘down’ AND I.ifAdminStatus = ‘up’;

Time: 2018-06-27T09:49:48.340428+08:00

User@Host: librenms[librenms] @ [10.200.191.68] Id: 7038219

Query_time: 1.056974 Lock_time: 0.008415 Rows_sent: 1 Rows_examined: 487351

SET timestamp=1530064188;
SELECT COUNT(*) FROM ports AS I, devices AS D WHERE I.deleted = ‘0’ AND D.device_id = I.device_id AND I.ignore = ‘0’ AND D.ignore = ‘0’ AND I.ifAdminStatus = ‘down’;

Time: 2018-06-27T09:50:35.799993+08:00

User@Host: librenms[librenms] @ [10.200.191.68] Id: 7038946

Query_time: 1.084204 Lock_time: 0.050164 Rows_sent: 1 Rows_examined: 487351

SET timestamp=1530064235;
SELECT COUNT(*) FROM ports AS I, devices AS D WHERE I.deleted = ‘0’ AND D.device_id = I.device_id AND I.ignore = ‘0’ AND D.ignore = ‘0’ AND I.ifOperStatus = ‘up’;

Time: 2018-06-27T09:50:40.888077+08:00

User@Host: librenms[librenms] @ [10.200.191.68] Id: 7039022

Query_time: 1.035736 Lock_time: 0.000064 Rows_sent: 1 Rows_examined: 487351

SET timestamp=1530064240;
SELECT COUNT(*) FROM ports AS I, devices AS D WHERE I.deleted = ‘0’ AND D.device_id = I.device_id AND I.ignore = ‘0’ AND D.ignore = ‘0’ AND (I.ifInErrors_delta > ‘0’ OR I.ifOutErrors_delta > ‘0’);

Time: 2018-06-27T09:50:45.263946+08:00

User@Host: librenms[librenms] @ [10.200.191.68] Id: 7039022

Query_time: 3.781053 Lock_time: 0.000087 Rows_sent: 171387 Rows_examined: 654352

SET timestamp=1530064245;
SELECT * FROM ports AS P, devices as D WHERE P.deleted = ‘1’ AND D.device_id = P.device_id;

Time: 2018-06-27T09:50:47.587251+08:00

User@Host: librenms[librenms] @ [10.200.191.68] Id: 7039100

Query_time: 1.023150 Lock_time: 0.000067 Rows_sent: 1 Rows_examined: 487351

SET timestamp=1530064247;
SELECT COUNT(*) FROM ports AS I, devices AS D WHERE I.deleted = ‘0’ AND D.device_id = I.device_id AND I.ignore = ‘0’ AND D.ignore = ‘0’ AND I.ifAdminStatus = ‘down’;

php-fpd (seted 500 child already)

[27-Jun-2018 09:43:46] WARNING: [pool www] child 60322, script ‘/home/librenms/html/ajax_dash.php’ (request: “POST /ajax_dash.php”) executing too slow (5.064481 sec), logging
[27-Jun-2018 09:43:46] NOTICE: child 60322 stopped for tracing
[27-Jun-2018 09:43:46] NOTICE: about to trace 60322
[27-Jun-2018 09:43:46] NOTICE: finished trace of 60322
[27-Jun-2018 09:45:33] WARNING: [pool www] child 60307, script ‘/home/librenms/html/index.php’ (request: “GET /index.php”) executing too slow (5.927246 sec), logging
[27-Jun-2018 09:45:33] NOTICE: child 60307 stopped for tracing
[27-Jun-2018 09:45:33] NOTICE: about to trace 60307
[27-Jun-2018 09:45:33] NOTICE: finished trace of 60307
[27-Jun-2018 09:50:45] WARNING: [pool www] child 60372, script ‘/home/librenms/html/index.php’ (request: “GET /index.php”) executing too slow (5.636717 sec), logging
[27-Jun-2018 09:50:45] NOTICE: child 60372 stopped for tracing
[27-Jun-2018 09:50:45] NOTICE: about to trace 60372
[27-Jun-2018 09:50:45] NOTICE: finished trace of 60372
[27-Jun-2018 09:55:58] WARNING: [pool www] child 60387, script ‘/home/librenms/html/index.php’ (request: “GET /index.php”) executing too slow (6.030887 sec), logging
[27-Jun-2018 09:55:58] NOTICE: child 60387 stopped for tracing
[27-Jun-2018 09:55:58] NOTICE: about to trace 60387
[27-Jun-2018 09:55:58] NOTICE: finished trace of 60387

9

I think SELECT COUNT(*) is slow place in InnoDB

Look at my results:
MariaDB [librenms]> SELECT COUNT() FROM ports AS I, devices AS D WHERE I.deleted = ‘0’ AND D.device_id = I.device_id AND I.ignore = ‘0’ AND D.ignore = ‘0’ AND (I.ifInErrors_delta > ‘0’ OR I.ifOutErrors_delta > ‘0’);
±---------+
| COUNT() |
±---------+
| 69 |
±---------+
1 row in set (0.13 sec)

With Adding USE INDEX (PRIMARY)

MariaDB [librenms]> SELECT COUNT(*) FROM ports AS I USE INDEX (PRIMARY), devices AS D WHERE I.deleted = '0' AND D.device_id = I.device_id AND I.ignore = '0' AND D.ignore = '0' AND (I.ifInErrors_delta > '0' OR I.ifOutErrors_delta > '0') ;

±---------+
| COUNT(*) |
±---------+
| 70 |
±---------+
1 row in set (0.04 sec)

Second query 3 times faster

SQL code need to some optimizations in my opinion

UPD:
same here

MariaDB [librenms]> SELECT COUNT() FROM ports AS I, devices AS D WHERE I.deleted = ‘0’ AND D.device_id = I.device_id AND I.ignore = ‘0’ AND D.ignore = ‘0’ AND I.ifAdminStatus = ‘down’;
±---------+
| COUNT() |
±---------+
| 310 |
±---------+
1 row in set (0.15 sec)

MariaDB [librenms]> SELECT COUNT() FROM ports AS I USE INDEX (PRIMARY), devices AS D WHERE I.deleted = ‘0’ AND D.device_id = I.device_id AND I.ignore = ‘0’ AND D.ignore = ‘0’ AND I.ifAdminStatus = ‘down’;
±---------+
| COUNT() |
±---------+
| 310 |
±---------+
1 row in set (0.04 sec)

10

when you load a page, check atop and see if any problems like if your waiting on disk

11

Hi

Thanks.
It is a good point,but i don’t think it is the reason for us.
We don’t have a plan to change the source code at first.

12

Hi

It took loog time at index page (over 10s).

slow log:

[29-Jun-2018 11:46:52] WARNING: [pool www] child 41836, script ‘/home/librenms/html/index.php’ (request: “GET /index.php”) executing too slow (6.632603 sec), logging
[29-Jun-2018 11:46:52] NOTICE: child 41836 stopped for tracing
[29-Jun-2018 11:46:52] NOTICE: about to trace 41836
[29-Jun-2018 11:46:52] NOTICE: finished trace of 41836

[29-Jun-2018 11:44:03] [pool www] pid 41807
script_filename = /home/librenms/html/index.php
[0x00007face1c16f70] dbFetchRows() /home/librenms/html/includes/print-menubar.php:343
[0x00007face1c16be0] INCLUDE_OR_EVAL /home/librenms/html/legacy_index.php:194
[0x00007face1c169a0] INCLUDE_OR_EVAL /home/librenms/app/Http/Controllers/LegacyController.php:10
[0x00007face1c16900] index() /home/librenms/vendor/laravel/framework/src/Illuminate/Routing/Controller.php:55
[0x00007face1c16880] call_user_func_array() /home/librenms/vendor/laravel/framework/src/Illuminate/Routing/Controller.php:55
[0x00007face1c167e0] callAction() /home/librenms/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php:44
[0x00007face1c16720] dispatch() /home/librenms/vendor/laravel/framework/src/Illuminate/Routing/Route.php:203
[0x00007face1c16690] runController() /home/librenms/vendor/laravel/framework/src/Illuminate/Routing/Route.php:160
[0x00007face1c165f0] run() /home/librenms/vendor/laravel/framework/src/Illuminate/Routing/Router.php:572
[0x00007face1c164b0] Illuminate\Routing{closure}() /home/librenms/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:30
[0x00007face1c16400] Illuminate\Routing{closure}() /home/librenms/vendor/laravel/framework/src/Illuminate/Routing/Middleware/SubstituteBindings.php:41
[0x00007face1c16330] handle() /home/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:148
[0x00007face1c16250] Illuminate\Pipeline{closure}() /home/librenms/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
[0x00007face1c16170] Illuminate\Routing{closure}() /home/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/VerifyCsrfToken.php:65
[0x00007face1c15fe0] handle() /home/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:148
[0x00007face1c15f00] Illuminate\Pipeline{closure}() /home/librenms/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
[0x00007face1c15e20] Illuminate\Routing{closure}() /home/librenms/vendor/laravel/framework/src/Illuminate/Session/Middleware/AuthenticateSession.php:39
[0x00007face1c15d50] handle() /home/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:148
[0x00007face1c15c70] Illuminate\Pipeline{closure}() /home/librenms/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
[0x00007face1c15b90] Illuminate\Routing{closure}() /home/librenms/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php:49

Is there any idea can time up process? Does libre have debug mode which can check out which part cost times?

Thanks

13

You should really at least fix the validate issues before trying to work anything else out.

14

hi

Thanks for your reply.

We use poller service.I think the validate issues may be ignore.

15

I think event log sql should be tunning.

mysql> explain SELECT COUNT(event_id) FROM eventlog AS E LEFT JOIN devices AS D ON E.host=D.device_id WHERE 1;
±—±------------±------±-----------±-------±--------------±--------±--------±----------------±---------±---------±-------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±------±-----------±-------±--------------±--------±--------±----------------±---------±---------±-------------------------+
| 1 | SIMPLE | E | NULL | index | NULL | host | 4 | NULL | 44501962 | 100.00 | Using index |
| 1 | SIMPLE | D | NULL | eq_ref | PRIMARY | PRIMARY | 4 | librenms.E.host | 1 | 100.00 | Using where; Using index |
±—±------------±------±-----------±-------±--------------±--------±--------±----------------±---------±---------±-------------------------+
2 rows in set, 1 warning (0.03 sec)

mysql> SELECT COUNT(event_id) FROM eventlog AS E LEFT JOIN devices AS D ON E.host=D.device_id WHERE 1;
±----------------+
| COUNT(event_id) |
±----------------+
| 47873039 |
±----------------+
1 row in set (2 min 35.92 sec)

mysql> SELECT COUNT(event_id) FROM eventlog;
±----------------+
| COUNT(event_id) |
±----------------+
| 47873425 |
±----------------+
1 row in set (7.69 sec)

mysql> desc eventlog;
±----------±-------------±-----±----±--------------------±---------------+
| Field | Type | Null | Key | Default | Extra |
±----------±-------------±-----±----±--------------------±---------------+
| event_id | int(11) | NO | PRI | NULL | auto_increment |
| host | int(11) | NO | MUL | 0 | |
| device_id | int(11) | NO | MUL | NULL | |
| datetime | datetime | NO | MUL | 1970-01-02 00:00:01 | |
| message | text | YES | | NULL | |
| type | varchar(64) | YES | | NULL | |
| reference | varchar(64) | NO | | NULL | |
| username | varchar(128) | YES | | NULL | |
| severity | int(1) | YES | | 2 | |
±----------±-------------±-----±----±--------------------±---------------+
9 rows in set (0.00 sec)

mysql> desc devices
-> ;
±--------------------------±---------------------------------------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±--------------------------±---------------------------------------------±-----±----±--------±---------------+
| device_id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| hostname | varchar(128) | NO | MUL | NULL | |
| sysName | varchar(128) | YES | MUL | NULL | |
| ip | varbinary(16) | YES | | NULL | |
| community | varchar(255) | YES | | NULL | |
| authlevel | enum(‘noAuthNoPriv’,‘authNoPriv’,‘authPriv’) | YES | | NULL | |
| authname | varchar(64) | YES | | NULL | |
| authpass | varchar(64) | YES | | NULL | |
| authalgo | enum(‘MD5’,‘SHA’) | YES | | NULL | |
| cryptopass | varchar(64) | YES | | NULL | |
| cryptoalgo | enum(‘AES’,‘DES’,’’) | YES | | NULL | |
| snmpver | varchar(4) | NO | | v2c | |
| port | smallint(5) unsigned | NO | | 161 | |
| transport | varchar(16) | NO | | udp | |
| timeout | int(11) | YES | | NULL | |
| retries | int(11) | YES | | NULL | |
| snmp_disable | tinyint(1) | NO | | 0 | |
| bgpLocalAs | varchar(16) | YES | | NULL | |
| sysObjectID | varchar(128) | YES | | NULL | |
| sysDescr | text | YES | | NULL | |
| sysContact | text | YES | | NULL | |
| version | text | YES | | NULL | |
| hardware | text | YES | | NULL | |
| features | text | YES | | NULL | |
| location | text | YES | | NULL | |
| os | varchar(32) | YES | MUL | NULL | |
| status | tinyint(4) | NO | MUL | 0 | |
| status_reason | varchar(50) | NO | | NULL | |
| ignore | tinyint(4) | NO | | 0 | |
| disabled | tinyint(1) | NO | | 0 | |
| uptime | bigint(20) | YES | | NULL | |
| agent_uptime | int(11) | NO | | 0 | |
| last_polled | timestamp | YES | MUL | NULL | |
| last_poll_attempted | timestamp | YES | MUL | NULL | |
| last_polled_timetaken | double(5,2) | YES | | NULL | |
| last_discovered_timetaken | double(5,2) | YES | | NULL | |
| last_discovered | timestamp | YES | | NULL | |
| last_ping | timestamp | YES | | NULL | |
| last_ping_timetaken | double(5,2) | YES | | NULL | |
| purpose | text | YES | | NULL | |
| type | varchar(20) | NO | | | |
| serial | text | YES | | NULL | |
| icon | varchar(255) | YES | | NULL | |
| poller_group | int(11) | NO | | 0 | |
| override_sysLocation | tinyint(1) | YES | | 0 | |
| notes | text | YES | | NULL | |
| port_association_mode | int(11) | NO | | 1 | |
±--------------------------±---------------------------------------------±-----±----±--------±---------------+
47 rows in set (0.00 sec)

16

Hi
Is there official test data available? For example, 2000 devices can also be applied to this system.
We are currently working on libre to replace our existing monitoring environment.
Now that this page has been found to open slowly, I would like to know if it was caused by too many devices or because of configuration problems. I monitored the resources of the server and it was determined that it was not due to resource issues.
Another topic, I think the database has a lot of optimization space, especially the switch log, the number is particularly large, whether you can consider using parttion, in the deletion of old data, it should be particularly good. Of course, this is my personal opinion.

Thank you

17

MySQL needs to tuning on large data amount, as you have (47 mln rows)
May be indexes doesn’t fit mysql memory allocation. Can you show output of mysqltuner.pl ?

18

Hi

Thanks for your reply.
Here is result.
I have done the optimit except query_cache.

MySQLTuner 1.7.9 - Major Hayden [email protected]
Bug reports, feature requests, and downloads at http://mysqltuner.com/
Run with ‘–help’ for additional options and output filtering

[–] Skipped version check for MySQLTuner script
[!!] Attempted to use login credentials from debian maintena
nce account, but they failed.
root@librenms-db-1:~# /root/mysqltuner.pl --user librenms --pass librenms

MySQLTuner 1.7.9 - Major Hayden [email protected]
Bug reports, feature requests, and downloads at http://mysqltuner.com/
Run with ‘–help’ for additional options and output filtering

[–] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[!!] failed to execute: SHOW SLAVE STATUS\G
[!!] FAIL Execute SQL / return code: 256
[!!] failed to execute: SHOW SLAVE HOSTS
[!!] FAIL Execute SQL / return code: 256
[OK] Currently running supported MySQL version 5.7.22-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[–] Log file: /var/log/mysql/error.log(0B)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[!!] Log file /var/log/mysql/error.log is empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn’t contain any warning.
[OK] /var/log/mysql/error.log doesn’t contain any error.
[–] 0 start(s) detected in /var/log/mysql/error.log
[–] 0 shutdown(s) detected in /var/log/mysql/error.log

-------- Storage Engine Statistics -----------------------------------------------------------------
[–] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[–] Data in InnoDB tables: 11G (Tables: 117)
[!!] Total fragmented tables: 1

-------- Security Recommendations ------------------------------------------------------------------
[!!] failed to execute: SELECT CONCAT(user, ‘@’, host) FROM mysql.user WHERE TRIM(USER) = ‘’ OR USER IS NULL
[!!] FAIL Execute SQL / return code: 256
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, ‘@’, host) FROM mysql.user WHERE (IF(plugin=‘mysql_native_password’, authentication_string, ‘password’) = ‘’ OR IF(plugin=‘mysql_native_password’, authentication_string, ‘password’) IS NULL) AND plugin NOT IN (‘unix_socket’, ‘win_socket’, ‘auth_pam_compat’)
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] failed to execute: SELECT CONCAT(user, ‘@’, host) FROM mysql.user WHERE CAST(IF(plugin=‘mysql_native_password’, authentication_string, ‘password’) as Binary) = PASSWORD(user) OR CAST(IF(plugin=‘mysql_native_password’, authentication_string, ‘password’) as Binary) = PASSWORD(UPPER(user)) OR CAST(IF(plugin=‘mysql_native_password’, authentication_string, ‘password’) as Binary) = PASSWORD(CONCAT(UPPER(LEFT(User, 1)), SUBSTRING(User, 2, LENGTH(User))))
[!!] FAIL Execute SQL / return code: 256
[!!] failed to execute: SELECT CONCAT(user, ‘@’, host) FROM mysql.user WHERE HOST=‘%’
[!!] FAIL Execute SQL / return code: 256
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[–] Skipped due to --cvefile option undefined
[!!] failed to execute: SHOW ENGINE PERFORMANCE_SCHEMA STATUS
[!!] FAIL Execute SQL / return code: 256
[!!] failed to execute: SHOW ENGINE PERFORMANCE_SCHEMA STATUS
[!!] FAIL Execute SQL / return code: 256

-------- Performance Metrics -----------------------------------------------------------------------
[–] Up for: 10d 21h 32m 25s (3B q [3K qps], 14M conn, TX: 3020G, RX: 2032G)
[–] Reads / Writes: 23% / 77%
[–] Binary logging is enabled (GTID MODE: ON)
[–] Physical Memory : 377.8G
[–] Max MySQL memory : 37.2G
[–] Other process memory: 153.7M
[–] Total buffers: 32.6G global + 1.1M per thread (4190 max threads)
[!!] failed to execute: SHOW ENGINE PERFORMANCE_SCHEMA STATUS
[!!] FAIL Execute SQL / return code: 256
[–] P_S Max memory usage: 0B
[–] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 34.7G (9.19% of installed RAM)
[OK] Maximum possible memory usage: 37.2G (9.84% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (163K/3B)
[OK] Highest usage of available connections: 46% (1948/4190)
[OK] Aborted connections: 0.00% (11/14045427)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 19.8% (174M cached / 881M selects)
[!!] Query cache prunes per day: 79785
[OK] Sorts requiring temporary tables: 0% (40K temp sorts / 52M sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 1% (225K on disk / 19M total)
[OK] Thread cache hit rate: 99% (72K created / 14M connections)
[OK] Table cache hit rate: 21% (4K open / 18K opened)
[OK] Open file limit used: 0% (7/5K)
[OK] Table locks acquired immediately: 100% (188K immediate / 188K locks)
[OK] Binlog cache memory access: 100.00% (2080597835 Memory / 2080597964 Total)

-------- Performance schema ------------------------------------------------------------------------
[!!] failed to execute: SHOW ENGINE PERFORMANCE_SCHEMA STATUS
[!!] FAIL Execute SQL / return code: 256
[–] Memory used by P_S: 0B
[–] Sys schema isn’t installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[–] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (1M used / 8M cache)
[!!] Cannot calculate MyISAM index size - re-run script as root user

-------- InnoDB Metrics ----------------------------------------------------------------------------
[–] InnoDB is enabled.
[–] InnoDB Thread Concurrency: 80
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 32.0G/11.9G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 1.0G * 8/32.0G should be equal 25%
[OK] InnoDB buffer pool instances: 32
[–] Number of InnoDB Buffer Pool Chunk : 32 for 32 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (2007844893456 hits/ 2007845234870 total)
[OK] InnoDB Write log efficiency: 99.92% (3345341016 hits/ 3347895931 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2554915 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[–] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[–] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[–] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[–] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[–] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[–] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[–] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[–] Galera Synchronous replication: NO
[–] No replication slave(s) for this server.
[–] Binlog format: ROW
[–] XA support enabled: ON
[–] Semi synchronous replication Master: Not Activated
[–] Semi synchronous replication Slave: Not Activated
[–] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE librenms.device_perf; – can free 668 MB
Total freed space after theses OPTIMIZE TABLE : 668 Mb
Increasing the query_cache size over 128M may reduce performance
Consider installing Sys schema from GitHub - mysql/mysql-sys: The MySQL sys schema
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
query_cache_size (> 512M) [see warning above]

19

Hi

I found a strange phenomenon and hope to answer questions.
What does the Deteted meaning?
image

I check the php code and database.

sql:

SELECT * FROM ports AS P, devices as D WHERE P.deleted = ‘1’ AND D.device_id = P.device_id

mysql> SELECT count() FROM ports AS P, devices as D WHERE P.deleted = ‘1’ AND D.device_id = P.device_id;
±---------+
| count() |
±---------+
| 170610 |
±---------+
1 row in set (0.52 sec)

PHP:
$deleted_ports = 0;
foreach (dbFetchRows(“SELECT * FROM ports AS P, devices as D WHERE P.deleted = ‘1’ AND D.device_id = P.device_id”) as $interface) {
if (port_permitted($interface[‘port_id’], $interface[‘device_id’])) {
$deleted_ports++;
}
}
?>

        <li><a href="ports/state=down/"><i class="fa fa-arrow-circle-down fa-fw fa-lg" aria-hidden="true"></i> Down</a></li>
        <li><a href="ports/state=admindown/"><i class="fa fa-arrow-circle-o-down fa-fw fa-lg" aria-hidden="true"></i> Disabled</a></li>
<?php if ($deleted_ports) { echo('
  • Deleted ('.$deleted_ports.')
    • '); } ?>

    It will run for 170610 times loop.
    Is there any porblems ?

    Thanks

    20

    Hi

    I have done with varnish.
    But it still can’t fix this issue.

    Thanks