Helo.
I have a problem with ODBC connection. I’\m using realtime connection with mysql (extensions, cdr, sipusers).
During the day (when there is highest traffic - 160 channels, 600 devices registering) everything works.
But during the night (between 3 - 5 a.m.) asterisk stops registering phones.
I have to restart mysql and do a ‘core reload’ and everything works again.
I’ve tried to change SQL server (hardware), moving Mysql to Madiadb … still nothing.
Strange is that even after asterisk looses connection I can still log in to mysql.
Mysqltuner shows:
[OK] Currently running supported MySQL version 5.5.56-MariaDB
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[–] Log file: /var/log/mariadb/mariadb.log(30K)
[OK] Log file /var/log/mariadb/mariadb.log exists
[OK] Log file /var/log/mariadb/mariadb.log is readable.
[OK] Log file /var/log/mariadb/mariadb.log is not empty
[OK] Log file /var/log/mariadb/mariadb.log is smaller than 32 Mb
[!!] /var/log/mariadb/mariadb.log contains 5 warning(s).
[!!] /var/log/mariadb/mariadb.log contains 30 error(s).
[–] 11 start(s) detected in /var/log/mariadb/mariadb.log
[–] 1) 170916 15:45:52 [Note] /usr/libexec/mysqld: ready for connections.
[–] 2) 170916 15:44:31 [Note] /usr/libexec/mysqld: ready for connections.
[–] 3) 170916 15:42:45 [Note] /usr/libexec/mysqld: ready for connections.
[–] 4) 170916 15:42:34 [Note] /usr/libexec/mysqld: ready for connections.
[–] 5) 170916 15:42:23 [Note] /usr/libexec/mysqld: ready for connections.
[–] 6) 170916 15:41:38 [Note] /usr/libexec/mysqld: ready for connections.
[–] 7) 170916 15:18:32 [Note] /usr/libexec/mysqld: ready for connections.
[–] 8) 170915 11:04:42 [Note] /usr/libexec/mysqld: ready for connections.
[–] 9) 170913 21:37:33 [Note] /usr/libexec/mysqld: ready for connections.
[–] 10) 170913 21:35:27 [Note] /usr/libexec/mysqld: ready for connections.
[–] 14 shutdown(s) detected in /var/log/mariadb/mariadb.log
[–] 1) 170916 15:45:50 [Note] /usr/libexec/mysqld: Shutdown complete
[–] 2) 170916 15:43:44 [Note] /usr/libexec/mysqld: Shutdown complete
[–] 3) 170916 15:43:38 [Note] /usr/libexec/mysqld: Shutdown complete
[–] 4) 170916 15:42:54 [Note] /usr/libexec/mysqld: Shutdown complete
[–] 5) 170916 15:42:54 [Note] /usr/libexec/mysqld: Shutdown complete
[–] 6) 170916 15:42:43 [Note] /usr/libexec/mysqld: Shutdown complete
[–] 7) 170916 15:42:33 [Note] /usr/libexec/mysqld: Shutdown complete
[–] 8) 170916 15:42:07 [Note] /usr/libexec/mysqld: Shutdown complete
[–] 9) 170916 15:42:07 [Note] /usr/libexec/mysqld: Shutdown complete
[–] 10) 170916 15:41:36 [Note] /usr/libexec/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[–] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[–] Data in MyISAM tables: 3G (Tables: 103)
[!!] InnoDB is enabled but isn’t being used
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, ‘@’, host) FROM mysql.user WHERE (password = ‘’ OR password IS NULL) AND plugin NOT IN (‘unix_socket’, ‘win_socket’)
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[–] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[–] Up for: 2d 15h 0m 30s (20M q [90.197 qps], 4K conn, TX: 47G, RX: 1G)
[–] Reads / Writes: 86% / 14%
[–] Binary logging is disabled
[–] Physical Memory : 2.9G
[–] Max MySQL memory : 772.0M
[–] Other process memory: 70.9M
[–] Total buffers: 352.0M global + 2.8M per thread (151 max threads)
[–] P_S Max memory usage: 0B
[–] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 527.2M (17.53% of installed RAM)
[OK] Maximum possible memory usage: 772.0M (25.66% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (2K/20M)
[OK] Highest usage of available connections: 41% (63/151)
[OK] Aborted connections: 0.15% (7/4805)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 200K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 2% (20 on disk / 934 total)
[OK] Thread cache hit rate: 98% (75 created / 4K connections)
[OK] Table cache hit rate: 111% (208 open / 187 opened)
[OK] Open file limit used: 31% (321/1K)
[OK] Table locks acquired immediately: 99% (10M immediate / 10M locks)
-------- Performance schema ------------------------------------------------------------------------
[–] Performance schema is disabled.
[–] Memory used by P_S: 0B
[–] Sys schema isn’t installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[–] ThreadPool stat is enabled.
[–] Thread Pool Size: 4 thread(s).
[–] Using default value is good enough for your version (5.5.56-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (67M used / 67M cache)
[OK] Key buffer size / total MyISAM indexes: 64.0M/352.5M
[OK] Read Key buffer hit rate: 100.0% (379M cached / 149K reads)
[OK] Write Key buffer hit rate: 100.0% (390K cached / 390K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[–] InnoDB is disabled.
[!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine
-------- AriaDB Metrics ----------------------------------------------------------------------------
[–] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
-------- 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.
[–] This is a standalone server.
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mariadb/mariadb.log file
Control error line(s) into /var/log/mariadb/mariadb.log file
Add skip-innodb to MySQL configuration to disable InnoDB
Restrict Host for user@% to user@SpecificDNSorIp
Consider installing Sys schema from https://github.com/mysql/mysql-sys
I would appreciate any help.