MySQL 쿼리 속도가 매우 느림 - 가끔

MySQL 쿼리 속도가 매우 느림 - 가끔

Ubuntu 18.4.4 LTS에서 MariaDB 10.2.31을 실행하고 있습니다.정기적으로 다음과 같은 문제가 발생합니다. 특히 아침에 시작할 때는 DEV 환경이 밤 동안 유휴 상태일 때입니다.

약 15,000개의 행이 있는 테이블(다른 테이블에도 적용됨)과 평균 5~10자의 VARCHAR 열에 인덱스가 있습니다. 이 을 포함한 의 열은 특히, 이열포대열의은입니다.GENERATED ALWAYS AS (JSON_EXTRACT(....)) STORED99%의 데이터가 REST API에서 JSON 인코딩 문자열로 제공되기 때문에 편리하게 한 열에 저장하고 다른 모든 데이터를 추출할 수 있습니다.

열에서 할 때WHERE colname LIKE 'text%'쿼리 결과 지속 시간은 0.006초입니다. 때. 제가 질문이 있을 때EXPLAIN있는 알수.ed, 인덱가사있것는알다있수습.그러나 앞서 언급했듯이 아침에 시작할 때는 시간이 훨씬 더 오래 걸립니다(오늘 아침 14초).는 쿼리 캐시에 있고 둘 다 나쿼리캐둘대해알있고고쿼캐끈다시통도니해상습했서태에다를시는를리시에둘▁via▁i▁and)통해(▁this▁turnedboth다▁with▁query다를니▁tried▁cache▁cache▁i▁()SET GLOBAL query_cache_type=OFF그리고.RESET QUERY CACHE이 경우 예상대로 약 0.3초의 일정한 시간을 얻을 수 있습니다.

그래서, 제가 무엇을 조사해야 한다고 추천하시겠습니까?제 DB는 자고 있나요?그런 것이 있습니까?

다음과 같은 두 가지 일이 발생할 수 있습니다.

콜드 캐쉬(밤샘 백업, mysqld 재시작 또는 대용량 처리 작업으로 인해 이 특정 인덱스 및 테이블 데이터가 메모리에서 제거됨)

테이블에 대한 일부 쿼리를 실행하고 통계를 새로 고칠 때까지 테이블의 통계가 오래되고 쿼리 플래너가 혼동됩니다.분석 테이블_name을(를) 사용하여 강제로 업데이트할 수 있습니다.

쿼리 플래너 하이젠버그입니다.MySQL 5.7 이상에서는 매우 일반적이지만 MariaDB에서 본 적이 없기 때문에 가능성이 거의 없습니다.

구성에서 다음을 활성화하여 이 문제의 진상을 파악할 수 있습니다.

log_output='FILE'
log_slow_queries=1
log_slow_verbosity='query_plan,explain'
long_query_time=1

그런 다음 느리게 발생하는 것을 확인한 직후 느린 로그에 있는 내용을 검토합니다.기록된 설명 계획이 느린 경우와 빠른 경우 모두 동일하게 보이면 콜드 캐시 문제가 발생합니다. 경우 하여 cron을 .ANALYZE TABLE해당 테이블에 많은 양을 읽고 쓰는 작업의 마지막 부분입니다.도움이를 쿼리에 하여 " 그 도 도 움 이 인 힌 쿼 를 코 하 합 딩 니 다 드 에 리 트 스 래 로 덱 으 단 수 막 되 마 지 않 지 면 으 ▁into ▁if ▁with ▁hint ▁an ▁code 니FORCE INDEX (index_name).

느린 쿼리 로그 사용log_slow_verbosity=query_plan,explain 리고그고.long_query_time결과를 파악하기에 충분합니다.때때로 다른(또는 없음) 인덱스를 사용하는지 확인합니다.

다음 날을 시작하기 전에, 다음을 보세요.SHOW GLOBAL STATUS LIKE "innodb_buffer_pool%"쿼리 후 값을 다시 확인합니다.이 상태 출력에 있는 버퍼 풀 읽기 수와 읽기 요청 수를 확인하여 모든 버퍼 풀 읽기가 디스크에서 해제되는지 확인합니다.

@Solarflare에서 언급했듯이 백업 및 야간 작업은 캐시된 데이터의 innodb 버퍼 풀을 제거하고 Disk로 복구하여 다시 느려지는 것일 수 있습니다.야간 활동의 일부로 스크립트 작업 전에 핫 페이지를 저장하도록 innodb_dll_pool_load_now=1을 설정하고 복원하려면 innodb_dll_pool_load_now=1을 설정할 수 있습니다.

모두에게 소중한 통찰력을 주셔서 감사합니다!여러분이 알려주신 모든 조언을 통해 저는 문제를 더 잘 이해하고 범위를 좁히기 시작했다고 생각합니다.

내가 처음 발견한 것은 나의 기본값이었습니다.innodb_buffer_pool_size134MB입니다. 처리하는 데이터의 종류와 양은 터무니없이 낮습니다. 그래서 저는 그것을 늘릴 수 있었습니다.매우 유용한 게시물: https://dba.stackexchange.com/a/27341 및 문서: https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-resize.html

2GB에 가까운 용량으로 늘리고 일반적인 사용량 및 RAM 사용량(cli: cat/proc/meminfo)을 모니터링할 수 있게 되었으므로 4GB RAM의 성능이 저하되고 있음을 알게 되었습니다.사용되지 않는 오버헤드(버퍼 사용량은 99%, RAM은 100MB 정도)를 거의 볼 수 없습니다.

다음으로 데몬의 RAM 사용 최적화를 시작하여 이것이 어디로 이어질지 알아보겠습니다. 하지만 이것이 충분한 RAM을 확보하지는 못할 것입니다.

@danblack ㅠㅠinnodb_buffer_pool_dump_now그리고.innodb_buffer_pool_load_now이것은 데몬이 DB에 액세스할 때마다 사용할 수 있는 흥미로운 접근 방식입니다. 데몬의 버퍼 사용량을 프런트 엔드의 버퍼 사용량과 분리하고 싶습니다(분명히 불가능합니다!).자세한 내용은 알아보겠습니다. 하지만 데몬이 항상 실행되고 있기 때문에(밤에만 실행되는 것이 아니라) 이것은 불가능할 수 있습니다.

@은 @Gordan Bobic을 사용하여 " 고침"를 언급했습니다ANALYZE TABLE tableName저는 이것이 매우 빠르다는 것을 알게 되었고, 그것이 광범위한 읽기/쓰기를 할 때마다 데몬에 통합되었습니다.이렇게 하면 데몬 실행 시간이 몇 초 증가하지만 전혀 문제가 되지 않습니다.그리고 나는 내가 그것을 잘못할 수 없다는 것을 알아요 :)

그래서 결국 저는 제 문제가 여러 가지가 합쳐진 것이라고 생각합니다.버퍼 크기가 너무 작거나 RAM이 너무 작거나 해당 환경에 대한 읽기/쓰기 작업이 너무 많습니다(버퍼된 인덱스 제거 등).또한 메모리 할당 등에 대해 더 많이 배우고 이를 더 잘 최적화해야 합니다(큰 페이지=1 등).

언급URL : https://stackoverflow.com/questions/61518750/mysql-queries-very-slow-occasionally