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
In order to work with the MySQL
database in the PROMOTIC system it is necessary to have the 32-bit drivers
installed on your computer even if the installed database itself is 64-bit.
The database can be filled:
- by the PmaDatabase
object (obsolete) via named ODBC
- 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 service of the operating system.
- It allows easy backups, setup of access permissions, transactions, replications, etc.
- It is functional on most operating systems OS Windows, Linux and OS X.
- PROMOTIC can be set in order to have the trends
writing its data into MySQL
- It has its own programming language for trigger definition and procedures.
- It is possible to select one of two standard database engines: MyISAM or InnoDB.
- It is often used for WEB applications
Properties of basic database engines
database engine is not suitable for frequent writing. It is more optimised 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
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
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 configure 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
2. Then set the InnoDB
engine entry "innodb_flush_log_at_trx_commit
" to 0
(it is set to 1 by default). This option defines the physical data writing process in 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 (28800s) unless the client performs any activity. After that time, the connection is automatically closed by the server. Such behavior causes PROMOTIC to lose connection with the database server and then can not write necessary data to the database.
A) On the MySQL serveru 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:
- all PmaAlarmGroup
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 into 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.
- 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 an application writes trends only once every 8 hours or even less often is very rare in practice.
- 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