한국어 번역 완료

SQL, OpenERP 및 OpenELIS 성능 문제 해결

배경 및 문제 설명

인도에서 8년 이상 단일 머신 인스턴스로 운영된 특정 Bahmni 구현에서 Bahmni와 OpenERP(현재 Odoo) 간 이벤트 동기화가 느려지는 문제가 발생했습니다.

시스템 전반이 느려졌으며, 특히 EMR에서 OpenERP로의 OpenERP 견적 동기화가 지연되고 OpenELIS 이벤트가 자주 실패했습니다.

잠재적 원인을 파악하기 위한 관찰 및 조치

OpenMRS 및 OpenERP 테이블의 실패 이벤트 대부분이 Socket timeout - Read timeout과 관련되어 있었습니다.

특히 OPD 진료일에 환자 수가 많을 때 평균 CPU 부하와 CPU 사용률이 지나치게 높았습니다.

CPU 사용량 확인 명령어 - htop

모니터링 시간 동안 매 순간 CPU 사용률이 가장 높은 프로세스를 확인하기 위해 Bash 스크립트를 작성하여 백그라운드에서 실행했습니다. 이 스크립트는 아래와 같이 시스템 load average와 실행 중인 각 프로세스의 CPU % usage를 2분마다 모니터링합니다.

#!/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

스크립트를 백그라운드에서 실행하려면 ./filename.sh & 명령을 실행합니다.

mysql과 postgres 프로세스가 CPU를 가장 많이 사용하고 있었습니다.

특정 시점에 더 많은 시간과 CPU를 소비하는 정확한 mysql 프로세스/쿼리를 찾으려면 OpenMRS 데이터베이스에서 다음을 실행합니다.

SHOW processlist;
SHOW FULL processlist;

특정 시점에 더 많은 시간과 CPU를 소비하는 정확한 psql 프로세스/쿼리를 찾으려면 OpenERP 데이터베이스에서 다음 쿼리를 실행합니다.

SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age
FROM pg_stat_activity WHERE state <> 'idle' AND query NOT LIKE '% FROM pg_stat_activity %' ORDER BY age;

각 시점에 실행 시간이 긴 mysql 쿼리를 확인하려면 느린 쿼리 로그 구성을 활성화합니다.

느린 쿼리 로그는 기본적으로 활성화되지 않을 수 있습니다. 활성화하려면 일반적으로 MySQL 데이터 디렉터리에 있는 MySQL 서버 구성 파일(my.cnf 또는 my.ini)을 수정해야 합니다. 텍스트 편집기로 파일을 열고 다음 줄을 추가하거나 수정합니다.

slow_query_log = 1
slow_query_log_file = /var/log/cpulogs/mysql-slow.log # /path/to/slow-query.log
long_query_time = 2  # This value defines what is considered a "slow" query, in seconds.

slow_query_log: 느린 쿼리 로그를 활성화하려면 1로 설정합니다. slow_query_log_file: 로그 파일 경로를 지정합니다. long_query_time: "느린" 쿼리로 간주할 기준 시간(초)을 설정합니다. 필요에 따라 값을 조정할 수 있습니다.

터미널에서 다음 명령을 실행합니다.

touch /var/log/cpulogs/mysql-slow.log
chown mysql:mysql /var/log/cpulogs/mysql-slow.log

MySQL을 다시 시작합니다.

구성 파일을 변경한 후 설정을 적용하려면 MySQL 서버를 다시 시작해야 합니다.

OpenERP 데이터베이스에서 실행 시간이 오래 걸린 Postgres 쿼리

5초 ->

SELECT "sale_order_line".id FROM "sale_order_line" WHERE ("sale_order_line"."external_order_id" = 'f6e15ec3-f0a0-4408-a4b4-5ae445a7482f') ORDER BY "sale_order_line"."id"

"sale_order_line"."external_order_id" 인덱싱을 확인합니다.

7.39초 ->

SELECT "processed_drug_order".id FROM "processed_drug_order" WHERE (("processed_drug_order"."order_uuid" = '480b63e8-d1d8-422c-bd2f-1dbe33e44e74')  AND  ("processed_drug_order"."dispensed_status" = 'false')) ORDER BY "processed_drug_order"."id"

"processed_drug_order"."order_uuid" 인덱싱을 확인합니다.

2분 ->

update res_partner set "last_reconciliation_date"='2023-11-13 05:57:05',write_uid=37,write_date=(now() at time zone 'UTC') where id IN (294732)

"id" 인덱싱을 확인합니다.

1분 27초 ->

update sale_order set "care_setting"='opd',write_uid=1,write_date=(now() at time zone 'UTC') where id IN (1295656)

"id" 인덱싱을 확인합니다.

참고: 여기에 언급된 쿼리 실행 시간은 고객 시스템의 데이터베이스를 기준으로 하며, 각 시스템 데이터베이스의 데이터에 따라 달라질 수 있습니다.

실행 시간이 지나치게 길었던 MySQL 쿼리

OpenMRS의 emrapi.sqlSearch.todaysPatientsByProvider:

select distinct concat(pn.given_name," ", ifnull(pn.family_name,'')) as name, pi.identifier as identifier, concat("",p.uuid) as uuid,
           concat("",v.uuid) as activeVisitUuid,
           IF(va.value_reference = "Admitted", "true", "false") as hasBeenAdmitted
           from
           visit v join person_name pn on v.patient_id = pn.person_id and pn.voided = 0 and v.voided=0
           join patient_identifier pi on v.patient_id = pi.patient_id and pi.voided=0
           join person p on p.person_id = v.patient_id  and p.voided=0
           join encounter en on en.visit_id = v.visit_id and en.voided=0
           join encounter_provider ep on ep.encounter_id = en.encounter_id  and ep.voided=0
           join provider pr on ep.provider_id=pr.provider_id and pr.retired=0
           join person per on pr.person_id=per.person_id and per.voided=0
           left outer join visit_attribute va on va.visit_id = v.visit_id and va.voided = 0 and va.attribute_type_id = (
           select visit_attribute_type_id from visit_attribute_type where name="Admission Status"
           )
           where
           date(en.encounter_datetime)=curdate() and
           pr.uuid=${provider_uuid}
           order by en.encounter_datetime desc;

결과

구성, 스크립트 및 지속적인 프로세스/쿼리 모니터링을 통해 CPU를 가장 많이 사용하고 실행 시간이 긴 몇 가지 MySQL/PSQL 쿼리를 발견했습니다.

MySQL/OpenMRS DB: 임상 대시보드의 "My Patients" 탭에서 위 MySQL 쿼리가 실행되고 있었습니다. 이 쿼리가 행 수가 가장 많은 encounter 테이블의 모든 환자를 검색했으므로, 활성 방문이 있는 환자만 확인하도록 AND 절을 추가하여 "My Patients" 탭에 활성 환자만 표시했습니다. 최적화된 쿼리는 아래에 있습니다.
OpenERP Postgres DB: 실행 시간이 긴 OpenERP 쿼리를 위해 postgres 데이터베이스에서 processed_drug_order 테이블의 order_uuid 열과 sale_order_line 테이블의 external_order_id를 인덱싱했습니다. 이 쿼리 최적화로 실행 시간이 분/초 단위에서 밀리초 단위로 줄어 OpenERP 응답성이 크게 향상되고 시스템 전체 평균 CPU 부하가 감소했습니다.
OpenELIS 실패 이벤트: OpenELIS 실패 이벤트는 대부분 SocketTimeoutException - Read timeout 오류와 관련되어 있었습니다. 시스템의 평균 CPU 부하를 낮추자 성능도 개선되었습니다.

OpenERP 데이터베이스의 postgres 테이블을 인덱싱하는 명령

CREATE INDEX processed_drug_order_order_uuid_index ON processed_drug_order(order_uuid);
CREATE INDEX sale_order_line_external_order_id_index ON sale_order_line(external_order_id);

My Patients 탭을 위한 최적화된 MySQL 쿼리

select distinct concat(pn.given_name," ", ifnull(pn.family_name,'')) as name, pi.identifier as identifier, concat("",p.uuid) as uuid,
           concat("",v.uuid) as activeVisitUuid,
           IF(va.value_reference = "Admitted", "true", "false") as hasBeenAdmitted
           from
           visit v join person_name pn on v.patient_id = pn.person_id and pn.voided = 0 and v.voided=0 and v.date_stopped IS NULL
           join patient_identifier pi on v.patient_id = pi.patient_id and pi.voided=0
           join person p on p.person_id = v.patient_id  and p.voided=0
           join encounter en on en.visit_id = v.visit_id and en.voided=0
           join encounter_provider ep on ep.encounter_id = en.encounter_id  and ep.voided=0
           join provider pr on ep.provider_id=pr.provider_id and pr.retired=0
           join person per on pr.person_id=per.person_id and per.voided=0
           left join visit_attribute va on va.visit_id = v.visit_id and va.voided = 0
           left join visit_attribute_type vat on va.attribute_type_id = vat.visit_attribute_type_id and vat.name = "Admission Status"
           where
           date(en.encounter_datetime)=curdate() and
           pr.uuid=${provider_uuid}
           order by en.encounter_datetime desc

원문 정보

Bahmni Wiki · CC BY-SA 4.0

원문 보기 ↗