> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # ClickHouse에서 쿼리 캐시 사용 여부 확인 방법 > `clickhouse-client`의 트레이스 로그나 SQL 명령을 사용해 ClickHouse에서 쿼리 캐시가 실제로 사용되고 있는지 확인하는 방법을 알아봅니다.
## 쿼리에서 쿼리 캐시가 사용되고 있는지 어떻게 확인할 수 있습니까?
[clickhouse client](/ko/concepts/features/interfaces/cli)와 ClickHouse Cloud 서비스를 사용하는 다음 예시를 참조하십시오. `query_cache_test` 테이블을 생성합니다
## clickhouse client 사용하기
```sql theme={null} clickhouse-cloud :) CREATE TABLE query_cache_test (name String, age UInt8) ENGINE =MergeTree ORDER BY name CREATE TABLE query_cache_test ( `name` String, `age` UInt8 ) ENGINE = MergeTree ORDER BY name Query id: 81c54f09-7de4-48ec-916f-c7c304a46931 Ok. 0 rows in set. Elapsed: 0.343 sec. ``` 테이블에 몇 개의 데이터를 입력하십시오: ```sql theme={null} clickhouse-cloud :) INSERT INTO query_cache_test SELECT * FROM generateRandom('name String, age UInt8',1,1) LIMIT 100000; INSERT INTO query_cache_test SELECT * FROM generateRandom('name String, age UInt8', 1, 1) LIMIT 100000 Query id: 90369105-bd67-494c-bdaf-d90dbfb6def9 Ok. 0 rows in set. Elapsed: 0.173 sec. Processed 327.05 thousand rows, 3.43 MB (1.89 million rows/s., 19.86 MB/s.) ``` 트레이스 로그 활성화: ```sql theme={null} clickhouse-cloud :) SET send_logs_level = 'trace' SET send_logs_level = 'trace' Query id: d65490b0-7960-4a85-a343-787e70e5e293 Ok. 0 rows in set. Elapsed: 0.134 sec. ``` 쿼리 캐시를 사용하도록 설정한 쿼리를 실행합니다(`SETTINGS use_query_cache=true`를 쿼리에 추가): ```sql theme={null} clickhouse-cloud :) SELECT name FROM query_cache_test WHERE age > 1000 FORMAT Null SETTINGS use_query_cache=true; SELECT name FROM query_cache_test WHERE age > 1000 FORMAT `Null` SETTINGS use_query_cache = 1 Query id: 3754a7fd-b786-47c1-a258-dfbc75e35a04 [c-red-qc-36-server-0] 2023.05.29 12:06:10.542408 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ executeQuery: (from 151.53.3.113:50412, user: tony) SELECT name FROM query_cache_test WHERE age > 1000 FORMAT Null SETTINGS use_query_cache=true; (stage: Complete) [c-red-qc-36-server-0] 2023.05.29 12:06:10.542744 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ InterpreterSelectQuery: MergeTreeWhereOptimizer: condition "age > 1000" moved to PREWHERE [c-red-qc-36-server-0] 2023.05.29 12:06:10.542900 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ InterpreterSelectQuery: MergeTreeWhereOptimizer: condition "age > 1000" moved to PREWHERE [c-red-qc-36-server-0] 2023.05.29 12:06:10.543020 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ ContextAccess (tony): Access granted: SELECT(name, age) ON tony.test [c-red-qc-36-server-0] 2023.05.29 12:06:10.543164 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ ContextAccess (tony): Access granted: SELECT(name, age) ON tony.test [c-red-qc-36-server-0] 2023.05.29 12:06:10.543226 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ InterpreterSelectQuery: FetchColumns -> Complete [c-red-qc-36-server-0] 2023.05.29 12:06:10.543337 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ tony.test (e61a107c-e7f8-4445-825f-88f85c72f7e9) (SelectExecutor): Key condition: unknown [c-red-qc-36-server-0] 2023.05.29 12:06:10.543395 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ tony.test (e61a107c-e7f8-4445-825f-88f85c72f7e9) (SelectExecutor): Selected 1/1 parts by partition key, 1 parts by primary key, 12/12 marks by primary key, 12 marks to read from 1 ranges [c-red-qc-36-server-0] 2023.05.29 12:06:10.543412 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ tony.test (e61a107c-e7f8-4445-825f-88f85c72f7e9) (SelectExecutor): Spreading mark ranges among streams (default reading) [c-red-qc-36-server-0] 2023.05.29 12:06:10.543461 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ MergeTreeBaseSelectProcessor: PREWHERE condition was split into 1 steps: "greater(age, 1000)" [c-red-qc-36-server-0] 2023.05.29 12:06:10.543484 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ MergeTreeInOrderSelectProcessor: Reading 1 ranges in order from part all_0_0_0, approx. 100000 rows starting from 0 [c-red-qc-36-server-0] 2023.05.29 12:06:10.543559 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ QueryCache: No entry found for query SELECT name FROM query_cache_test WHERE age > 1000 FORMAT `Null` SETTINGS [c-red-qc-36-server-0] 2023.05.29 12:06:10.547760 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ QueryCache: Stored result of query SELECT name FROM query_cache_test WHERE age > 1000 FORMAT `Null` SETTINGS [c-red-qc-36-server-0] 2023.05.29 12:06:10.547827 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ executeQuery: Read 100000 rows, 97.66 KiB in 0.005508 sec., 18155410.31227306 rows/sec., 17.31 MiB/sec. [c-red-qc-36-server-0] 2023.05.29 12:06:10.547913 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ MemoryTracker: Peak memory usage (for query): 451.89 KiB. [c-red-qc-36-server-0] 2023.05.29 12:06:10.547933 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ TCPHandler: Processed in 0.005911032 sec. Ok. 0 rows in set. Elapsed: 0.006 sec. Processed 100.00 thousand rows, 100.00 KB (17.56 million rows/s., 17.56 MB/s.) ``` 같은 쿼리를 다시 실행하세요: ```sql theme={null} clickhouse-cloud :) SELECT name FROM query_cache_test WHERE age > 1000 FORMAT Null SETTINGS use_query_cache=true; SELECT name FROM query_cache_test WHERE age > 1000 FORMAT `Null` SETTINGS use_query_cache = 1 Query id: a047527c-9d55-4e6b-9747-0ccad8787515 [c-red-qc-36-server-0] 2023.05.29 12:06:17.931007 [ 454 ] {a047527c-9d55-4e6b-9747-0ccad8787515} \ executeQuery: (from 151.53.3.113:50412, user: tony) SELECT name FROM query_cache_test WHERE age > 1000 FORMAT Null SETTINGS use_query_cache=true; (stage: Complete) [c-red-qc-36-server-0] 2023.05.29 12:06:17.931331 [ 454 ] {a047527c-9d55-4e6b-9747-0ccad8787515} \ InterpreterSelectQuery: MergeTreeWhereOptimizer: condition "age > 1000" moved to PREWHERE [c-red-qc-36-server-0] 2023.05.29 12:06:17.931468 [ 454 ] {a047527c-9d55-4e6b-9747-0ccad8787515} \ InterpreterSelectQuery: MergeTreeWhereOptimizer: condition "age > 1000" moved to PREWHERE [c-red-qc-36-server-0] 2023.05.29 12:06:17.931585 [ 454 ] {a047527c-9d55-4e6b-9747-0ccad8787515} \ ContextAccess (tony): Access granted: SELECT(name, age) ON tony.test [c-red-qc-36-server-0] 2023.05.29 12:06:17.931696 [ 454 ] {a047527c-9d55-4e6b-9747-0ccad8787515} \ ContextAccess (tony): Access granted: SELECT(name, age) ON tony.test [c-red-qc-36-server-0] 2023.05.29 12:06:17.931749 [ 454 ] {a047527c-9d55-4e6b-9747-0ccad8787515} \ InterpreterSelectQuery: FetchColumns -> Complete [c-red-qc-36-server-0] 2023.05.29 12:06:17.931857 [ 454 ] {a047527c-9d55-4e6b-9747-0ccad8787515} \ tony.test (e61a107c-e7f8-4445-825f-88f85c72f7e9) (SelectExecutor): Key condition: unknown [c-red-qc-36-server-0] 2023.05.29 12:06:17.931891 [ 454 ] {a047527c-9d55-4e6b-9747-0ccad8787515} \ tony.test (e61a107c-e7f8-4445-825f-88f85c72f7e9) (SelectExecutor): Selected 1/1 parts by partition key, 1 parts by primary key, 12/12 marks by primary key, 12 marks to read from 1 ranges [c-red-qc-36-server-0] 2023.05.29 12:06:17.931913 [ 454 ] {a047527c-9d55-4e6b-9747-0ccad8787515} \ tony.test (e61a107c-e7f8-4445-825f-88f85c72f7e9) (SelectExecutor): Spreading mark ranges among streams (default reading) [c-red-qc-36-server-0] 2023.05.29 12:06:17.931952 [ 454 ] {a047527c-9d55-4e6b-9747-0ccad8787515} \ MergeTreeBaseSelectProcessor: PREWHERE condition was split into 1 steps: "greater(age, 1000)" [c-red-qc-36-server-0] 2023.05.29 12:06:17.931975 [ 454 ] {a047527c-9d55-4e6b-9747-0ccad8787515} \ MergeTreeInOrderSelectProcessor: Reading 1 ranges in order from part all_0_0_0, approx. 100000 rows starting from 0 [c-red-qc-36-server-0] 2023.05.29 12:06:17.932043 [ 454 ] {a047527c-9d55-4e6b-9747-0ccad8787515} \ QueryCache: Entry found for query SELECT name FROM query_cache_test WHERE age > 1000 FORMAT `Null` SETTINGS [c-red-qc-36-server-0] 2023.05.29 12:06:17.932551 [ 454 ] {a047527c-9d55-4e6b-9747-0ccad8787515} \ MemoryTracker: Peak memory usage (for query): 5.19 KiB. [c-red-qc-36-server-0] 2023.05.29 12:06:17.932581 [ 454 ] {a047527c-9d55-4e6b-9747-0ccad8787515} \ TCPHandler: Processed in 0.001961411 sec. Ok. 0 rows in set. Elapsed: 0.002 sec. ``` 이제 다음 두 경우에서 `QueryCache`와 관련된 `TRACE` 로그의 차이를 확인해 보겠습니다. 첫 번째 실행: ``` [c-red-qc-36-server-0] 2023.05.29 12:06:10.543559 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ QueryCache: No entry found for query SELECT name FROM query_cache_test WHERE age > 1000 FORMAT `Null` SETTINGS [c-red-qc-36-server-0] 2023.05.29 12:06:10.547760 [ 454 ] {3754a7fd-b786-47c1-a258-dfbc75e35a04} \ QueryCache: Stored result of query SELECT name FROM query_cache_test WHERE age > 1000 FORMAT `Null` SETTINGS ``` 두 번째 실행 시: ``` [c-red-qc-36-server-0] 2023.05.29 12:06:17.932043 [ 454 ] {a047527c-9d55-4e6b-9747-0ccad8787515} \ QueryCache: Entry found for query SELECT name FROM query_cache_test WHERE age > 1000 FORMAT `Null` SETTINGS ``` 1번째 실행에서는 캐시 항목을 찾지 못했으므로 (`No entry found for query SELECT...`), ClickHouse가 해당 항목을 저장했습니다 (`Stored result of query SELECT...`). 2번째 실행에서는 이미 저장된 항목을 찾았으므로 (`Entry found for query SELECT...`), 해당 쿼리가 쿼리 캐시를 활용했습니다.
## SQL만 사용하기
`clickhouse client` 트레이스 로그를 확인하지 않고 SQL 명령만으로도 관련 `system` 테이블을 확인하여 쿼리 캐시가 사용되는지 검증할 수 있습니다: ```sql theme={null} clickhouse-cloud :) SELECT 1 SETTINGS use_query_cache=true; SELECT 1 SETTINGS use_query_cache = 1 Query id: a5a078c7-61e5-4036-a6f0-4d602d5b72d2 ┌─1─┐ │ 1 │ └───┘ 1 row in set. Elapsed: 0.001 sec. clickhouse-cloud :) SELECT 1 SETTINGS use_query_cache=true; SELECT 1 SETTINGS use_query_cache = 1 Query id: 322ae001-b1ab-463f-ac8d-dc5ba346f3f9 ┌─1─┐ │ 1 │ └───┘ 1 row in set. Elapsed: 0.001 sec. clickhouse-cloud :) SELECT * FROM clusterAllReplicas(default,system.query_cache); SELECT * FROM clusterAllReplicas(default, system.query_cache) Query id: c9b57eac-ba64-430e-8d51-8f865a13cc25 ┌─query──────────────┬─result_size─┬─stale─┬─shared─┬─compressed─┬──────────expires_at─┬─────────────key_hash─┐ │ SELECT 1 SETTINGS │ 136 │ 0 │ 1 │ 1 │ 2023-08-02 15:08:23 │ 12188185624808016954 │ └────────────────────┴─────────────┴───────┴────────┴────────────┴─────────────────────┴──────────────────────┘ 1 row in set. Elapsed: 0.005 sec. clickhouse-cloud :) SELECT * FROM clusterAllReplicas(default,system.events) WHERE event LIKE 'QueryCache%' SELECT * FROM clusterAllReplicas(default, system.events) WHERE event LIKE 'QueryCache%' Query id: d536555e-b8ab-4cd4-9741-c04e95612bec ┌─event────────────┬─value─┬─description────────────────────────────────────────────────────────────────────────────────────────────┐ │ QueryCacheHits │ 1 │ Number of times a query result has been found in the query cache (and query computation was avoided). │ │ QueryCacheMisses │ 1 │ Number of times a query result has not been found in the query cache (and required query computation). │ └──────────────────┴───────┴────────────────────────────────────────────────────────────────────────────────────────────────────────┘ 2 rows in set. Elapsed: 0.006 sec. ``` 마지막 결과에서는 쿼리 `SELECT 1 SETTINGS use_query_cache=true;`가 처음 실행될 때 `QueryCacheMisses`가 1회 발생했고, 쿼리를 두 번째로 실행할 때는 `QueryCacheHits` 이벤트가 발생한 것을 확인할 수 있습니다. 또한 기본 최대 캐시 항목 크기는 1048576바이트(= 1 MiB)이며, 기본적으로 결과는 60초 동안만 캐시에 저장된다는 점도 유의하십시오(예를 들어 `SETTINGS`에서 `query_cache_ttl=300`을 사용하면 쿼리 캐시 결과를 대신 5분 동안 저장할 수 있습니다). ClickHouse 쿼리 캐시에 대한 자세한 정보는 [여기](/ko/concepts/features/performance/caches/query-cache)에서 확인할 수 있습니다.