Description

MySQL is a relational database used in SoftWLC project to store key data required for the management system operation. The version mysql-server-5.5 is used.
The project includes the following databases:

Service management

Configuration

MySQL configuration is done via /etc/mysql/my.cnf (default file installed with mysql-server package) and /etc/mysql/conf.d/eltex-ems.cnf (the file installed with eltex-ems-db package and containing additional information which is important for the management system work) configuration files.

The number of connections

Depending on the estimated number of access points, the maximum number of AP connections should be specified via max_connections parameter. The parameter is specified in /etc/mysql/mysql.conf.d/mysql.cnf by default, but commented out. Uncomment it and enter the required number, for example, like this:

max_connections        = 500

The number can be selected from Calculating the number of database connections depending on server load (last row).

Memory size

MyISAM is used in eltex_alert database and syslog. InnoDB is used in all other databases, including radius.radacct high-load table.
After the database is installed, configure buffer size for MyISAM and InnoDB services (/etc/mysql/conf.d/eltex-ems.cnf parameters key_buffer_size and innodb_buffer_pool_size, respectively).Configuration recommendations:

• If the database is installed on a dedicated host, the recommended buffer size is 30-40% of total RAM for each service.
• If all the services are installed on the same host, buffer size available for each service is calculated assuming that the main Java application (eltex-ems), tomcat7 web server, NBI, Admin Panel and eltex-apb also require big virtual memory size. They should be specified for each service in relevant configuration files.
• Memory size available for InnoDB may be 2-3 times bigger then one for MyISAM, if there are no plans to collect syslog from many devices.

For example, if all SoftWLC services are installed on the same host, the following amounts of memory are allocated:
For 16G server:

key_buffer_size = 2G
innodb_buffer_pool_size = 4G

For 64G server:

key_buffer_size=8G
innodb_buffer_pool_size=24G

 When /etc/mysql/conf.d/eltex-ems.cnf configuration file is modified, restart MySQL with the command:

service mysql restart

Additional database user accounts

Collecting rsyslog

To collect logs from DHCP and RADIUS servers, rsyslog user account should be created in the database.
Commands:

CREATE USER 'rsyslog'@'HOST' identified by 'root';
GRANT ALL on Syslog.* TO 'rsyslog'@'HOST' identified by 'root';

where HOST is an address of RADIUS or DHCP server in management network. For each server, a separate entry should be created.

Checking replication ststus in GUI EMS

For EMS server to be able to check replication state, additional user accounts are required. They can be created via the MySQL console:
commands:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'HOST' IDENTIFIED BY 'root';
FLUSH PRIVILEGES;

where HOST is an address of EMS server in VRRP network. For each server, a separate entry should be created.

Database connection number calculation depending on server load

mysql server operation analysis using MySQLTuner

MySQLTuner is a perl script that analyses MySQL statistics and provides recommendations on MySQL server configuration optimization.  

Note! MySQL server should operate without reloadings and configuration parameters changes for some period of time (at least 24 hours according to MysqTuner).

To start the script, run the command:

/usr/lib/eltex-ems-db/tools/mysqltuner.pl -- user root --pass root

 When started, the script will conduct an analysis and give the following information:

>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.43-0ubuntu0.14.04.1-log
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive BDB -Federated +InnoDB -ISAM -NDBCluster
[-] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in InnoDB tables: 3G (Tables: 39)
[--] Data in MyISAM tables: 22G (Tables: 5)
[!!] Total fragmented tables: 10
 
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 127d 22h 20m 14s (746M q [67.539 qps], 25M conn, TX: 302B, RX: 411B)
[--] Reads / Writes: 21% / 79%
[--] Total buffers: 48.1G global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 48.5G (77% of installed RAM)
[OK] Slow queries: 0% (13K/746M)
[!!] Highest connection usage: 100% (152/151)
[OK] Key buffer size / total MyISAM indexes: 24.0G/9.9G
[OK] Key buffer hit rate: 99.9% (4B cached / 5M reads)
[OK] Query cache efficiency: 33.6% (114M cached / 340M selects)
[!!] Query cache prunes per day: 735211
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 5M sorts)
[OK] Temporary tables created on disk: 0% (3K on disk / 394K total)
[OK] Thread cache hit rate: 99% (8K created / 25M connections)
[!!] Table cache hit rate: 8% (324 open / 3K opened)
[OK] Open file limit used: 19% (3K/16K)
[OK] Table locks acquired immediately: 99% (9B immediate / 9B locks)
[OK] InnoDB data size / buffer pool: 3.6G/24.0G
 
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate persistent connections to reduce connection usage
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
max_connections (> 151)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (> 16M)
table_cache (> 400)