Troubleshooting performance issues in SQL, OpenERP and OpenELIS

Context and Problem StatementA particular implementation of Bahmni, running in India for the last 8+ years, on a single machine instance was facing slowness issues while syncing events from Bahmni to OpenERP (now called Odoo).

They encountered overall system slowness, majorly in OpenERP quotation sync from EMR to OpenERP and frequent failed events for OpenElis.

Failed events in OpenMRS & OpenERP tables had majorly entries related to Socket timeout - Read timeout

The average CPU load / CPU percentage was too high specifically when there was more number of patients in OPD days

Command to check CPU usage - htop

• To ascertain processes occupying most of the CPU usage percentage at every instant during the monitoring time, a bash script was created and ran in the background . The bash script is to monitor System load average and CPU % usage every 2 minutes by different processes running in the system as stated below:bash> /var/log/cpulogs/uptime.log # Get the current date and time timestamp=$(date +"%Y-%m-%d %H:%M:%S") # Get the CPU usage by top 20 processes using ps command and awk ps_output=$(ps -eo pid,pcpu,comm --sort=-pcpu | awk 'NR> /var/log/cpulogs/cpu_usage.log sleep 60 done]]>

  #!/bin/bash
  mkdir -p /var/log/cpulogs
  while true; do
  uptime >> /var/log/cpulogs/uptime.log
     # Get the current date and time
     timestamp=$(date +"%Y-%m-%d %H:%M:%S")
     # Get the CPU usage by top 20 processes using ps command and awk
     ps_output=$(ps -eo pid,pcpu,comm --sort=-pcpu | awk 'NR<=20 {print $1, $2, $3}')
     # Log the CPU usage along with timestamp
     echo -e "$timestamp \n $ps_output \n" >> /var/log/cpulogs/cpu_usage.log
  sleep 60
  done

Execute the script: ./filename.sh & to run it in the background.

It was found that mysql and postgres processes were consuming maximum CPU.

• To find out exact mysql processes/ queries consuming more time and CPU at any instant, execute this in OpenMRS database:

• To find out exact psql processes / queries consuming more time and CPU at any instant, execute this query in OpenERP database :

• To ascertain at every instant which mysql queries have longer execution time. Enable the Slow Query Log configuration:

1. By default, the slow query log may not be enabled. To enable it, you need to modify your MySQL server's configuration file (usually my.cnf or my.ini), typically found in the MySQL data directory. Open this file in a text editor, and add or modify the following lines:

slow_query_log: Set it to 1 to enable the slow query log. slow_query_log_file: Specify the path to the log file. long_query_time: Set the threshold (in seconds) for what constitutes a "slow" query. You can adjust this value as needed.

Execute these command in terminal -

1. Restart MySQL:

After making the changes to the configuration file, you'll need to restart the MySQL server to apply the settings.

5 secs ->

Check for "sale_order_line"."external_order_id" indexing

7.39 secs ->

Check for "processed_drug_order"."order_uuid" indexing

2mins ->

Check for “id” indexing

1 min 27 secs ->

Check for “id” indexing

Note: The query execution times mentioned here are based on our client's system database, and they may vary depending on the data in the particular system database.

emrapi.sqlSearch.todaysPatientsByProvider in OpenMRS : -

With the configurations, scripts, and continuous process/query monitoring, a few MySQL/PSQL queries were found that were consuming maximum CPU and had a longer execution time.

• MySQL/OpenMRS DB: There was a “My Patients” tab in the Clinical dashboard which was executing the above mentioned MySql query. We noticed that the query was searching for all patients in the encounter table which has the most number of rows in it so we added an AND clause to only check for patients who have an active visit and hence displaying only active patients in the "My Patients" tab. The optimized query is mentioned below. 
• OpenERP Postgres DB: For OpenERP queries which were taking more time we indexed the order_uuid column of processed_drug_order table and external_order_id of sale_order_line table in the postgres database. With these query optimisations, query execution time was reduced from minutes/seconds to milliseconds, which significantly improved the OpenERP system responsiveness and reduced the overall average CPU load for the system.
• OpenElis Failed Events: For OpenElis, failed events were mostly related to SocketTimeoutException - Read timeout error thus reducing average CPU load of the system enhanced its performance as well.