Switching Database to MySQL

Several versions of Iotellect Server contain a dedicated MySQL bundle inside the distribution package. If such a bundle was selected for installation, the server will be auto-configured to use MySQL database, and the below steps are not necessary.

If a dedicated instance of MySQL was installed, its files are located in the mysql/ subfolder of Iotellect Server installation. The database data is located in the mysql/data/ subfolder.

The installer configures dedicated MySQL instance for auto-launch during OS startup. Default configurations of Iotellect Server and MySQL are optimized for maximum interaction performance.

Note, that dedicated MySQL instance is pre-optimized for performance and its memory requirements are quite high. You'll probably need to reconfigure bundled MySQL installation if your server has less than 4 Gb of RAM.

To use MySQL as Iotellect Server's database engine, follow the generic instructions from Switching To Another Database Engine article. Here is a list of notes specific to MySQL:

  • MySQL's JDBC driver (MySQL Connector for Java) is already included in Iotellect Server distribution. The driver file is called MySQL-connector-java-5.X.XX-ga-bin.jar, it is located in the /jar subfolder of Iotellect Server installation folder. To ensure maximum performance and compatibility with the latest versions of MySQL, you may want to update MySQL JDBC driver to the most recent version. At the time of this writing, it is available at http://www.mysql.com/products/connector/j/.
  • Set Database Driver in Iotellect Server Global Configuration Setting to com.mysql.jdbc.Driver. This is the java class name for the MySQL driver.
  • The Database URL setting for the MySQL database has the following format: jdbc:mysql://[host][:port][/database], where host is the IP or hostname of the MySQL server (can be an empty string or localhost), port is port name on which MySQL server is running (omit this part of URL to use the default value), and database name is the name of database that contains Iotellect Server's data. You can use server for the database name. For example, if your MySQL server runs at 192.168.0.1 with its default port, use the following URL: jdbc:mysql://192.168.0.1/server.
  • Set Database Dialect setting to MySQL 5 (MySQL5InnoDBDialect) is you are using MySQL 5 or MySQL (MySQLInnoDBDialect) for older versions.

    • If you want to use non-Unicode symbols, you should specify characterEncoding parameter. There are two ways to do it:

    • Through Database URL: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
    • Through Additional Properties: useUnicode = true, characterEncoding = UTF‌-8

    If you have MySQL 8, you should specify the following parameter: useSSL=false in your Database URL or by using Additional Properties.

    MySQL Setup

    Iotellect requires several configuration changes to be made in MySQL configuration. These changes are normally applied using MySQL configuration file (my.ini by default).

    1. Add max_allowed_packet=100M to the configuration to allow large queries.

    2. Set max_connections to the value higher than Maximum Connection Pool Size global Iotellect Server configuration setting (e.g. max_connections=1000).

    3. Set innodb_buffer_pool_size to maximum amount of RAM that MySQL should use. In the case of dedicated database server, set this option to 80% of available RAM.

    4. Set innodb_log_file_size to 25-50% of innodb_buffer_pool_size.

    5. Add character-set-server = utf8 and collation-server = utf8_unicode_ci lines to the configuration.

    MySQL Configuration File

    An example of MySQL 5.7 configuration file suitable for large Iotellect installations with thousands of devices is provided below.

    [mysqld]

    port = 3306

    # You can use socket settings from your own configuration file

    socket = /tmp/mysql.sock

    default-storage-engine = INNODB

    datadir = /data/mysql

    max_connections = 1000

    max_allowed_packet = 100M

    innodb_flush_log_at_trx_commit = 0

    innodb_buffer_pool_size = 2G

    innodb_log_file_size = 500M

    innodb_log_buffer_size = 8M

    innodb_lock_wait_timeout = 1200

    innodb_file_per_table = 1

    sort_buffer_size = 20M

    query_cache_size = 100M

    table_open_cache = 10000

    table_definition_cache = 10000

    thread_cache_size = 32

    innodb_thread_concurrency = 0

    character-set-server = utf8

    collation-server = utf8_unicode_ci

    It is necessary to change /data/mysql to actual data directory for your MySQL server installation. By default, the MySQL data directory path is /var/lib/mysql in Linux.

    Changing Buffer Sizes

    MySQL installation bundled with Iotellect Server for Windows is configured for high performance servers. If the server is installed on a low-grade machine (e.g. on a laptop), the MySQL server may fail to start due to the buffer memory allocation problem. In this case, it's necessary to decrease buffer sizes:

    • Make sure that MySQL for Iotellect service/process is not running on the server machine or stop it if it's running (you can control the service from Control Panel > Administration > Services)
    • Edit mysql/my.ini file located in the Iotellect Server distribution folder
    • Decrease innodb_buffer_pool_size, e.g. set it to 500M
    • Decrease innodb_log_file_size to 25-50% of the innodb_buffer_pool_size, e.g. set it to 200M
    • Delete MySQL log files (mysql/ib_logfile0 and mysql/ib_logfile1)
    • Start MySQL for Iotellect service again

    Was this page helpful?