Calem uses MySQL as its database. For customers using Calem Cloud Services the database tuning is handled by the Calem Support Team. Using MySQLTuner to set optimal settings of MySQL helps handle larger data sets and prevents Calem from slowdown.
For customers with on-premise deployment of Calem the database turning may be performed every six month or every year when the one of the following condition is observed:
- Data screens operations take long time. The operations include screen opening, data search, and data editing.
- There are warnings of database operations in the Calem log (Calem_Home/logs/calem.log) that take too long. For instance, the following warnings indicate that database tuning may be necessary.
2019-07-02 14:31:01,901 WARN CmPDOStatement - Query time:8.7887, the query=SELECT count(*) FROM cm_in_imp_pc LEFT OUTER JOIN cm_in_imp_tran cm_in_imp_tran_imp_id ON cm_in_imp_pc.imp_id=cm_in_imp_tran_imp_id.id LEFT OUTER JOIN cm_in cm_in_in_id ON cm_in_imp_pc.in_id=cm_in_in_id.id LEFT OUTER JOIN cm_in_locator cm_in_locator_locator_id ON cm_in_imp_pc.locator_id=cm_in_locator_locator_id.id LEFT OUTER JOIN cm_in_serial cm_in_serial_serial_id ON cm_in_imp_pc.serial_id=cm_in_serial_serial_id.id LEFT OUTER JOIN cm_in_tran cm_in_tran_in_tran_id ON cm_in_imp_pc.in_tran_id=cm_in_tran_in_tran_id.id LEFT OUTER JOIN cm_in_lot cm_in_lot_lot_id ON cm_in_imp_pc.lot_id=cm_in_lot_lot_id.id LEFT OUTER JOIN cm_project cm_project_project_id ON cm_in_imp_pc.project_id=cm_project_project_id.id WHERE cm_in_imp_pc.created_id = '3dfb2c74-1449-01ec-9456-89dff4308eb2'
- You have a larger data set such as tens of thousands of assets, tens of thousands of inventory items, and many years of work order history using Calem.
MySQLTuner for Linux
Customers with Calem on-premise in Linux may use the guide from Linode to do MySQL tuning: https://www.linode.com/docs/databases/mysql/how-to-optimize-mysql-performance-using-mysqltuner/
MySQLTuner for Windows
Customers with Calem on-premise in Windows may use the GitHub site below to do MySQL tuning: https://github.com/pmachapman/mysqltuner/