MySQL is being developed by the
Oracle company and is available in several commercial versions but also as
MySQL Community Edition version that is free of charge. The installation files of
MySQL can be obtained for example from
http://www.mysql.com.
Included in the installation package (since version
MySQL 5.6) there is the administration tool
Workbench and also the
ODBC drivers.
In order to work with the
MySQL database in the PROMOTIC system it is necessary to have the
32-bit drivers installed on the computer even if the installed database itself is 64-bit.
The database can be filled:
- by the
PmaDatabase object (obsolete) via named
ODBC data source
- The community version is available free of charge.
- The functionality is not limited in the free version.
- From the functionality and configuration point of view, the
MySQL is simpler than
MS SQL Server.
- The database runs as Windows service.
- It allows easy backups, setup of access permissions, transactions, replications, etc.
- It is functional on most operating systems Windows OS, Linux and OS X.
- PROMOTIC can be set in order to have the
trends and
alarms/events writing its data into
MySQL.
- It has its own programming language for trigger definition and procedures.
- It is possible to choose one of two standard database engines: MyISAM or InnoDB.
- It is often used for Web applications
Properties of basic database engines
MyISAM
MyISAM database engine is not suitable for frequent writing. It is more optimized for frequent reading from the database and does not support transactions. When multiple users acces the table it uses a lock on the whole table level.
Therefore this engine is not recommended to be used in the PROMOTIC system for writing
trends,
alarms and
events.
InnoDB
InnoDB engine is better for frequent writing, it supports transactions, and if multiple users access the table it locks only the corresponding records.
This database engine is recommended to be used in the PROMOTIC system for writing
trends,
alarms and
events.
Caution
There are also some disadvantages of the
InnoDB engine. One of them is e.g. the problem of
SELECT COUNT(*) command type. The speed of execution of such command by this engine highly depends on the table size. If the table contains approx. 300-400 thousands records then this command execution may take up to one second. Therefore keep in mind that using this command may in some cases significantly slow down your application.
Recommended settings of the MySQL database for usage in the PROMOTIC system
For more demanding applications (high number of
PmaTrendGroup objects, frequent writing
of alarms etc.) it is necessary to set the
MySQL database properly to prevent overload:
1. In the configuration file
my.ini it is necessary to modify the entry "
default-storage-engine" to
InnoDB so all new created tables use the
InnoDB engine. If there are existing tables it is recommended to convert them into InnoDB e.g. by the
Workbench administration tool.
2. Then set the
InnoDB engine entry "
innodb_flush_log_at_trx_commit" to
0 or
2 (it is set to 1 by default). This option defines the physical data writing process to the database.
0 - writes data and transaction log to disk approx. once a second - this lowers the number of disc operations
1 - writes the transaction log to disk on every writing - it is safe but slow if a large number of writing operations is demanded
2 - writes into transaction log after the transaction is confirmed, but the physical writing to disk is really completed approx. once a second - this lowers the number of disk operations
3. It is also recommended to set the cache memory capacity for data and table indexes by entry "
innodb_buffer_pool_size". For dedicated database servers the recommended value is 70% - 80% of installed operating memory.
4.
MySQL is configured by default to maintain client connection for
8 hours (
28800 seconds) unless the client performs any activity. After that time, the connection is automatically closed by the server. Such behavior causes PROMOTIC application to lose connection with the database server and then can not write necessary data to the database.
Possible solutions:
A) On the MySQL server side - increasing the time period (parameters wait_timeout and/or interactive_timeout) in MySQL server
B)
On the PROMOTIC application side - making the PROMOTIC application perform periodically any activity with the database before the connection times-out (i.e. write or read data to/from the database)
Solution procedure for different objects in the PROMOTIC application:
1. Alarms/Events - all
PmaAlarmGroup/
PmaEventGroup objects with the same "ConnectionString" parameter share a single connection to the database. When using
MySQL, it is necessary to ensure that something is written to the database at least once within the timeout period (interaction_timeout). In practice, just create one event group and create an "app alive" event in the application every
8 hours (or even more often, e.g. every hour). This will ensure that the connection to the alarm database is not closed.
2. Trends - all
PmaTrendGroup objects with the same "ConnectionString" parameter share a single connection to the database. Therefore, it is necessary to ensure that at least one
PmaTrendGroup writes something into the database at least once within the timeout period. This is ensured in most applications - the situation that the application writes trends only once every
8 hours or even less often is very rare in practice.
3. PmaAdo - here the designer must ensure that some MySQL server query is activated at least once in a defined timeout period (or more often). Additionally, the
PmaAdo object can be disconnected/reconnected to the database if the server automatically disconnects.
An example of command that can be used to maintain connection while keeping the MySQL server load at minimum:
SHOW GLOBAL STATUS LIKE 'Uptime'
For next descriptions to the
MySQL, see: