is being developed by the Oracle company and is available in several commercial versions but also as MySQL Community Edition verze 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 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 a 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 configured 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 PROMOTIC system for writing trends
, alarms and events
Recommended settings of the MySQL database for usage in the PROMOTIC system
For more demanding applications (high number of PmTrend
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 setting defines the physical data writing process in the database.
0 - writes data and transaction log to the disc approx. once a second - this lowers the number of disc operations
1 - writes the transaction log to the disc 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 is really completed approx. once a second - this lowers the number of disc 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.
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.
The database can be filled:
- by the PmDatabase
(obsolete) object via named ODBC
For next descriptions to the MySQL