mariaDB cache 설정
2019.08.04 16:59
https://easyengine.io/tutorials/mysql/query-cache/
안하면 엄청 느리다.
Important Note: From MySQL 5.6.8,query_cache_type
is set to OFF by default. So if you haven’t explicitly turned it ON on old version, it may not work anymore!
Check current status of query_cache
mysql -e "show variables like 'query_cache_%'"
Will output something like:
+------------------------------+-----------+ | Variable_name | Value | +------------------------------+-----------+ | query_cache_limit | 2097152 | | query_cache_min_res_unit | 4096 | | query_cache_size | 268435456 | | query_cache_strip_comments | ON | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+-----------+
Query Cache Config
Add something like below following to your /etc/my/my.cnf
query_cache_type = 1 query_cache_size = 256M query_cache_limit = 2M query_cache_strip_comments =1
Please note that query_cache_strip_comments
variable is available on Percona & MariaDB mysql variants as of now.
Meaning of variables
query_cache_type
Just turn it ON. From mysql 5.6.8 its OFF by default.
If this is OFF, you will see error “[!!] Query cache is disabled” when running mysqltuner.
Earlier mysql version used to setquery_cache_type
= 1 butquery_cache_size
= 0. If either ofquery_cache_type
andquery_cache_size
is set to 0, query_cache gets disabled.
query_cache_size
Default is 1MB. You can set it upto 4GB but very high values are not recommend for sites where tables are modified quite frequently.
In WordPress scenario, if you have a multi-author blog, or some other custom post types, then query cache prunes might be frequent. Query cache is invalidated for entire table even if a small value is modified.
query_cache_limit
Default is 1MB. You can set it upto 4GB. Again very high values are not recommended.
Better optimize your codes to not fetch too much data in one query. If you need 10 rows, add pagination, WHERE conditions rather than fetching all rows from mysql and then using only 10 rows out of them!
InnoDB Buffer & Query Cache
Many references on Internet will tell you that query cache is useless if InnoDB is being used.
Well if you are using InnoDB only and have limited RAM, InnoDB buffer pool without a doubt should get first priority.
If you have some spare RAM, it is highly recommended to use query_cache for WordPress sites. Even for big WordPress sites, most likely percentage of SELECT queries will be much higher as compared to INSERT or UPDATE.
Best way is to monitor query cache efficiency using mysqltuner. Look for a line like:
[OK] Query cache efficiency: 71.5% (8K cached / 11K selects)
100% Query cache efficiency may not be possible for 100% read-only sites but try to stay above 50%.
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
1686 | 나야~ 혜진... 오랜만인걸.. | 김혜진 | 2000.06.22 | 3381 |
1685 | 홈페이지 넘 멋있어요~ | 한양의대본4 | 2000.06.24 | 3316 |
1684 | 승석아 | 김재호 | 2000.06.27 | 3361 |
1683 | 앗 다시보니..새로운 문제점들이 | 김재호 | 2000.06.27 | 3311 |
1682 | Re: 앗 다시보니..새로운 문제점들이 | 한승석 | 2000.06.27 | 3621 |
1681 | Re: 승석아 | 한승석 | 2000.06.27 | 3302 |
1680 | 들어와본 소감 ...이거 대회에 도움되려나? | 강승민 | 2000.06.27 | 3160 |
1679 | 사진들이 업데이트 되었습니다. | 한승석 | 2000.06.28 | 3206 |
1678 | 승석아 홈페이지 참 멋있다. | 정석원 | 2000.07.03 | 3638 |
1677 | 버전은 어떻게 높이나요? | 박병선 | 2000.07.04 | 3361 |
1676 | 소스를 고쳐보세요. | 한승석 | 2000.07.05 | 3573 |
1675 | 사진 더 추가해줘잉 | 재호 | 2000.07.07 | 3608 |
1674 | 홈페이지가 너무 멋있군요, | kraye | 2000.07.10 | 3343 |
1673 | .승무. | 나비가루 | 2000.07.10 | 3476 |
1672 | 음란전화.... | 윤재원 | 2000.07.10 | 3648 |