Promotic
WikipediaLinkedInYoutubeTwitterFacebook

MySql

MySQL 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 drivers.
 
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.
 
MySQL characteristics
- 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 and alarms/events 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
 
MyISAM
 
MyISAM 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.
 
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 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 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 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.
 
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.
 
The database can be filled:
- by the PmTrend object, if the Storage type configurator is set in this object to Database MySQL cyclic
- by the PmAdo object
- by the PmDatabase (obsolete) object via named ODBC source
 
For next descriptions to the MySQL, see:
PROMOTIC 8.3.21 SCADA system documentation - MICROSYS, spol. s r.o.

Send page remarkContact responsible person
© MICROSYS, spol. s r. o.Tavičská 845/21 703 00 Ostrava-Vítkovice