How to Tune MySQL with MySQLTuner

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/

Additional resources

• User Guide and Admin Guide (customer account required)
• Calem Enterprise Training Site
  
• Calem Enterprise Blogs
  
• Calem Enterprise demo