|
Several versions of AggreGate 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 AggreGate 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 AggreGate Server and MySQL are optimized for maximum interaction performance. |
To use MySQL as AggreGate Server's database engine, follow the generic instructions from Switching To Another Database Engine article. Here is a list of notes specific to MySQL:
| 1. | MySQL's JDBC driver (MySQL Connector for Java) is already included in AggreGate Server distribution. The driver file is called MySQL-connector-java-5.X.XX-ga-bin.jar, it is located in the /lib subfolder of AggreGate Server installation folder. To unsure 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/. |
| 2. | Set Database Driver in AggreGate Server Global Configuration Setting to com.mysql.jdbc.Driver. This is the java class name for the MySQL driver. |
| 3. | 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 AggreGate Server's data. You can use linkserver 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/linkserver. |
| 4. | Set Database Dialect setting to MySQL 5 (MySQL5InnoDBDialect) is you are using MySQL 5 or MySQL (MySQLInnoDBDialect) for older versions. |
MySQL Setup
AggreGate 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 AggreGate 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.
MySQL Configuration File
An example of MySQL configuration file suitable for large AggreGate installations with thousands of devices is provided below.
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
default-storage-engine = INNODB
basedir = <some dir>
datadir = <some dir>
innodb_data_home_dir = <some dir>
innodb_log_group_home_dir = <some dir>
skip-locking
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_additional_mem_pool_size = 20M
innodb_lock_wait_timeout = 50
key_buffer_size = 384M
sort_buffer_size = 20M
read_buffer_size = 20M
read_rnd_buffer_size = 20M
query_cache_size = 100M
table_open_cache = 10000
table_definition_cache = 10000
thread_cache_size = 32
innodb_file_io_threads = 8
innodb_thread_concurrency = 0