Hello All,
Thank you in advance for reading this thread.
First of All, validation result:
====================================
Component Version LibreNMS 1.69-1-gbc02ab3f6 DB Schema 2020_07_27_00522_alter_devices_snmp_algo_columns (188) PHP 7.2.24-0ubuntu0.18.04.7 Python 3.6.9 MySQL 10.1.47-MariaDB-0ubuntu0.18.04.1 RRDTool 1.7.0 SNMP NET-SNMP 5.7.3 OpenSSL ==================================== [OK] Composer Version: 1.10.20
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database schema correct
[WARN] PHP version 7.3 is the minimum supported version as of November, 2020. We recommend you update PHP to a supported version (7.4 suggested) to continue to receive updates. If you do not update PHP, LibreNMS will continue to function but stop receiving bug fixes and updates.[WARN] Your local git contains modified files, this could prevent automatic updates.
[FIX]:
You can fix this with ./scripts/github-remove
Modified Files:
config/database.php
rrd/.gitignore
I’ve got a problem with database related to LibreNMS.
I tried to find out solution but unfortunately I couldn’t, maybe one of You could help.
To the point…
I have about 800 devices added, and unfortunately there is a problem with the database being too heavy. The connection limit at the moment is 200, but the number of threads still exceeds this number.
Until some time everything worked fine (you will ask what happened that it broke - I don’t know ^^).
The first devices started to ‘lose’ the graphs, which was already suspicious, but now it keeps practically the entire database.
I’ve suspect that in show processlistI there is sooooooo many sooo many ‘sleep’ commands, probably it overload my DB and it can cause my problem…
Some data:
> Blockquote
+-------+----------------------+-----------+----------+---------+------+------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+----------------------+-----------+----------+---------+------+------------------+------------------+----------+
| 19943 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |
| 21807 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 21902 | librenms | localhost | librenms | Sleep | 4 | | NULL | 0.000 |
| 21908 | librenms | localhost | librenms | Sleep | 24 | | NULL | 0.000 |
| 21939 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22012 | librenms | localhost | librenms | Sleep | 7 | | NULL | 0.000 |
| 22015 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 22028 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 22131 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 22146 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22147 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22155 | librenms | localhost | librenms | Sleep | 3 | | NULL | 0.000 |
| 22157 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 22160 | librenms | localhost | librenms | Sleep | 3 | | NULL | 0.000 |
| 22165 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22166 | librenms | localhost | librenms | Sleep | 5 | | NULL | 0.000 |
| 22167 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22168 | librenms | localhost | librenms | Sleep | 3 | | NULL | 0.000 |
| 22169 | librenms | localhost | librenms | Sleep | 9 | | NULL | 0.000 |
| 22170 | librenms | localhost | librenms | Sleep | 5 | | NULL | 0.000 |
| 22175 | librenms | localhost | librenms | Sleep | 4 | | NULL | 0.000 |
| 22184 | librenms | localhost | librenms | Sleep | 8 | | NULL | 0.000 |
| 22186 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22187 | librenms | localhost | librenms | Sleep | 6 | | NULL | 0.000 |
| 22188 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 22205 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 22215 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 22261 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22271 | librenms | localhost | librenms | Sleep | 4 | | NULL | 0.000 |
| 22272 | librenms | localhost | librenms | Sleep | 5 | | NULL | 0.000 |
| 22276 | librenms | localhost | librenms | Sleep | 8 | | NULL | 0.000 |
| 22277 | librenms | localhost | librenms | Sleep | 3 | | NULL | 0.000 |
| 22278 | librenms | localhost | librenms | Sleep | 3 | | NULL | 0.000 |
| 22279 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 22280 | librenms | localhost | librenms | Sleep | 4 | | NULL | 0.000 |
| 22281 | librenms | localhost | librenms | Sleep | 5 | | NULL | 0.000 |
| 22291 | librenms | localhost | librenms | Sleep | 2 | | NULL | 0.000 |
| 22302 | librenms | localhost | librenms | Sleep | 2 | | NULL | 0.000 |
| 22317 | librenms | localhost | librenms | Sleep | 7 | | NULL | 0.000 |
| 22327 | librenms | localhost | librenms | Sleep | 4 | | NULL | 0.000 |
| 22328 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 22336 | librenms | localhost | librenms | Sleep | 3 | | NULL | 0.000 |
| 22341 | librenms | localhost | librenms | Sleep | 4 | | NULL | 0.000 |
| 22342 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22346 | librenms | localhost | librenms | Sleep | 3 | | NULL | 0.000 |
| 22357 | librenms | localhost | librenms | Sleep | 2 | | NULL | 0.000 |
| 22361 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 22362 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22363 | librenms | localhost | librenms | Sleep | 5 | | NULL | 0.000 |
| 22364 | librenms | localhost | librenms | Sleep | 5 | | NULL | 0.000 |
| 22365 | librenms | localhost | librenms | Sleep | 4 | | NULL | 0.000 |
| 22366 | librenms | localhost | librenms | Sleep | 4 | | NULL | 0.000 |
| 22367 | librenms | localhost | librenms | Sleep | 5 | | NULL | 0.000 |
| 22368 | librenms | localhost | librenms | Sleep | 4 | | NULL | 0.000 |
| 22369 | librenms | localhost | librenms | Sleep | 2 | | NULL | 0.000 |
| 22376 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 22389 | librenms | localhost | librenms | Sleep | 2 | | NULL | 0.000 |
| 22396 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 22397 | librenms | localhost | librenms | Sleep | 3 | | NULL | 0.000 |
| 22406 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22419 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22420 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 22421 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22422 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22441 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22448 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22455 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 22456 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 22457 | librenms | localhost | librenms | Sleep | 1 | | NULL | 0.000 |
| 22458 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22459 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22460 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22461 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22462 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22463 | librenms | localhost | librenms | Sleep | 0 | | NULL | 0.000 |
| 22479 | unauthenticated user | localhost | NULL | Connect | 0 | Reading from net | NULL | 0.000 |
+-------+----------------------+-----------+----------+---------+------+------------------+------------------+----------+
76 rows in set (0.00 sec)
>
> MariaDB [(none)]> show status like '%onn%';
> +-----------------------------------------------+-------+
> | Variable_name | Value |
> +-----------------------------------------------+-------+
> | Aborted_connects | 14427 |
> | Connection_errors_accept | 0 |
> | Connection_errors_internal | 0 |
> | Connection_errors_max_connections | 3020 |
> | Connection_errors_peer_address | 0 |
> | Connection_errors_select | 0 |
> | Connection_errors_tcpwrap | 0 |
> | Connections | 22643 |
> | Max_used_connections | 76 |
> | Performance_schema_session_connect_attrs_lost | 0 |
> | Slave_connections | 0 |
> | Slaves_connected | 0 |
> | Ssl_client_connects | 0 |
> | Ssl_connect_renegotiates | 0 |
> | Ssl_finished_connects | 0 |
> | Threads_connected | 74 |
> | wsrep_connected | OFF |
> +-----------------------------------------------+-------+
>
> **Basic Settings**
> user = mysql
> pid-file = /var/run/mysqld/mysqld.pid
> socket = /var/run/mysqld/mysqld.sock
> port = 3306
> basedir = /usr
> datadir = /var/lib/mysql
> tmpdir = /tmp
> lc-messages-dir = /usr/share/mysql
> skip-external-locking
>
>**#Instead of skip-networking the default is now to listen only on**
>**#localhost which is more compatible and is not less secure.**
> bind-address = 0.0.0.0
>
> **CACHE AND LIMITS**
> tmp-table-size = 32M
> max-heap-table-size = 32M
> query-cache-type = 0
> query-cache-size = 1M
> query_cache_limit = 256M
> max-connections = 75
> thread-cache-size = 10
> open-files-limit = 65535
> table-definition-cache = 1024
> table-open-cache = 2048
> #sort_buffer_size = 1M
> #join_buffer_size = 1M
> #read_buffer_size = 1M
> wait_timeout = 360
> interactive_timeout = 360
>
> **INNODB ENGINE**
> lower_case_table_names=0
> innodb_flush_log_at_trx_commit=0
> innodb_buffer_pool_instances=1
> innodb_log_file_size=100M
> innodb_buffer_pool_size=800M
> innodb_buffer_pool_instances=1
> innodb_file_per_table=1
> innodb_lock_wait_timeout=100
> innodb_flush_method=O_DIRECT
>
> **Fine Tuning**
> key_buffer_size = 16M
> max_allowed_packet = 64M
> thread_stack = 192K
> thread_cache_size = 8
>
> **#This replaces the startup script and checks MyISAM tables if needed**
> **#the first time they are touched**
> myisam_recover_options = BACKUP
> #max_connections = 100
> #table_cache = 64
> #thread_concurrency = 10
>
>
>**Crontab:**
>**Using this cron file requires an additional user on your system, please see install docs.**
>
> **> # -------------------------------------------------------------------------------------**
> **> 33 */6 * * * librenms /opt/librenms/discovery-wrapper.py 1 >> /dev/null 2>&1**
> **> # -------------------------------------------------------------------------------------**
> **> #*/15 * * * * librenms /opt/librenms/discovery.php -h new >> /dev/null 2>&1**
> **> # -------------------------------------------------------------------------------------**
> **> */15 * * * * librenms /opt/librenms/poller-wrapper.py 1**
> **> # -------------------------------------------------------------------------------------**
> **> * * * * * librenms /opt/librenms/alerts.php >> /dev/null 2>&1**
> **> # -------------------------------------------------------------------------------------**
> **> */10 * * * * librenms /opt/librenms/poll-billing.php >> /dev/null 2>&1**
> **> # -------------------------------------------------------------------------------------**
> **> 01 * * * * librenms /opt/librenms/billing-calculate.php >> /dev/null 2>&1**
> **> # -------------------------------------------------------------------------------------**
> **> */5 * * * * librenms /opt/librenms/check-services.php >> /dev/null 2>&1**
> **> # -------------------------------------------------------------------------------------**
Thanks in advance for any solution… 