my.cnf에 대한 Mysqltuner 제안 및 변경 사항

my.cnf에 대한 Mysqltuner 제안 및 변경 사항

서버 장애에 대해 며칠 동안 이 질문을 했지만 운이 없었다.

VPS에서 mysqltuner.pl를 실행한 적이 있는데, 변경할 변수에 대한 제안에 대해 여러 가지 질문이 있습니다.저는 이 질문들이 복잡한 답변을 가진 일반적인 질문들이라고 확신합니다.

쿼리 작성 및 서버와의 비교 테스트에는 익숙하지 않지만, WordPress 사이트를 5개 가동하고 있는 서버에서 월 200,000 페이지뷰를 넘는 퍼포먼스를 얻을 수 있도록 노력하고 있습니다.

phpmyadmin을 통해 데이터베이스를 최적화했지만(그리고 정기적으로 그렇게 합니다), 튜너에서는 여전히 조각난 테이블이 있다고 합니다.WordPress이기 때문에 코어 코드의 쿼리를 변경할 수 없습니다.

그러나 query_cache_size 및 innodb_buffer_pool_size와 같은 변수를 얼마나 늘려야 합니까?다른 빈정거리는 변수는요?

table_cache와 같이 my.cnf에 존재하지 않는 변수도 있으며 튜너 보고서 등에 플래그가 표시되어 있습니다.my.cnf에 추가할 수 있습니까?

(이 블록은 왜 my.cnf에 중복되어 있습니까?복제를 삭제할 수 있습니까?

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

my.cnf와 mysqltuner의 출력을 다음에 나타냅니다.

my.cnf 내용:

query-cache-type = 1
query-cache-size = 8M

set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

old_passwords=1

skip-bdb

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-bdb

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

mysqltuner 출력:

------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.45
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 133M (Tables: 637)
[--] Data in InnoDB tables: 10M (Tables: 344)
[--] Data in MEMORY tables: 126K (Tables: 2)
[!!] Total fragmented tables: 69

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 6h 24m 13s (2M q [22.135 qps], 116K conn, TX: 4B, RX: 530M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 35.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 303.7M (8% of installed RAM)
[OK] Slow queries: 0% (4/2M)
[OK] Highest usage of available connections: 53% (53/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/46.1M
[OK] Key buffer hit rate: 99.6% (749M cached / 2M reads)
[OK] Query cache efficiency: 32.2% (685K cached / 2M selects)
[!!] Query cache prunes per day: 948863
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 660K sorts)
[!!] Temporary tables created on disk: 46% (400K on disk / 869K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 24K opened)
[OK] Open file limit used: 10% (109/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[!!] InnoDB data size / buffer pool: 10.6M/2.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 8M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 10M)

최선을 다해 도와드리겠습니다.MysqlTuner 리포트는 이 VPS에 4GB의 RAM을 탑재하고 있다는 것을 나타내고 있으므로, 그것을 바탕으로 한 제안입니다.

query_cache_size - MySQL이 데이터베이스 쿼리 결과를 캐시하기 위해 사용할 수 있는 RAM의 양입니다.쿼리 캐시에 저장된 결과는 일반 선택보다 훨씬 빠르게 반환되므로 이 변수는 작업 속도를 크게 높일 수 있습니다(다른 제안된 변경 사항보다 더 높음).

정확한 값이 무엇인지에 대해서는 몇 가지 실험을 실시합니다.현재 이 설정은 8M으로 설정되어 있습니다.이 박스에 4GB의 RAM을 탑재하고 있는 경우는 64M부터 시작하여 128M, 필요에 따라 256M까지 늘릴 수 있습니다.변경 후 며칠간 그대로 두었다가 MysqlTuner를 다시 실행하여 'Query Cache Efficiency' 비율을 이전과 비교합니다.주로 5개의 Wordpress 블로그를 호스팅하는 서버의 경우 256M 이상의 향상을 기대할 수 없습니다.또, RAM의 8분의 1을 넘는 것은 추천하지 않습니다.

개인적으로 Munin(무료 서버 감시 도구)은 캐시 적중과 다른 쿼리를 그래프로 표시하기 때문에 이러한 종류의 작업을 감시하는 데 매우 편리합니다.

tmp_table_size - 일부 복잡한 쿼리(특히 GROUP BY 또는 복잡한 정렬을 사용하는 쿼리)의 경우 MySQL은 먼저 데이터를 포함하는 임시 테이블을 작성한 후 결과 세트를 작성하기 위해 몇 가지 작업을 수행해야 합니다.이러한 임시 테이블을 디스크에 작성하는 것보다 훨씬 빠르기 때문에 이 테이블을 메모리에 작성하려고 합니다.그러나 큰 결과 세트의 경우 항상 이것이 가능한 것은 아닙니다.tmp_table_size는 이 임계값을 제어합니다.

워드프레스가 너무 복잡한 질문을 하는 건 상상할 수 없어요. 그래서 이 질문을 너무 많이 하지 않으려고요.MysqlTuner는 32MB보다 큰 값을 제안하므로 64M부터 시작하여 며칠 후 '디스크에 생성된 임시 테이블' 값에 어떤 영향을 미치는지 확인하십시오.권장하는 대로 max_heap_table_size를 설정합니다.

thread_cache_size - Wordpress는 기본적으로 영속적인 연결을 사용하지 않기 때문에 각 요청은 데이터베이스에 새로운 연결을 만들고 페이지가 생성되면 이 연결을 닫습니다.이 오버헤드는 크지 않지만, thread_cache_size를 사용하면 MySQL이 이러한 연결 스레드를 재사용할 수 있으므로 조금 도움이 됩니다.

동시접속자 수가 많지 않으면 4의 권장치로 하겠습니다.

table_cache - 이것은 MySQL의 테이블 구조 캐시와 관련이 있는 것 같습니다.이건 128로 하겠습니다.

innodb_buffer_pool_size - MySQL이 InnoDB 테이블의 인덱스 및 데이터를 캐시하기 위해 사용할 수 있는 메모리 양입니다.Wordpress는 InnoDB를 전혀 사용하지 않는 것 같아서 조금 당황스럽습니다.이 서버에 다른 사이트도 있나요?

기타 질문에 대한 답변으로 다음 설정 후[mysqld_safe]MySQL 데몬에만 적용되며 MySQL 전체에는 적용되지 않기 때문에 일부 변수가 중복됩니다.innodb_buffer_pool_size를 변경해야 합니다.파일에는 없는 변수도 추가할 수 있지만 [mysqld_safe]블록 위에 추가할 수도 있습니다.

마지막으로 최적화를 원하신다면 APC와 같은 PHP 바이트 코드 캐시를 아직 사용하지 않으셨다면 검토해 볼 가치가 있습니다.APC는 부정적인 영향 없이 PHP 앱에 상당한 속도 향상을 제공할 수 있습니다.

mysql 데이터베이스를 조정하는 툴은 http://www.day32.com/MySQL/, http://www.maatkit.org/doc/, http://hackmysql.com/mysqlsla 등이 있습니다.

대부분의 경우 쿼리를 작성하고 서버에 대해 테스트할 필요가 없습니다.느린 쿼리 로그를 활성화하여 느린 쿼리를 mysqlsla로 집계하고 maatkit으로 설명합니다.

mysqla 결과에서 가장 느린 쿼리를 텍스트 파일에 붙여넣고 maatkit을 사용하여 실행할 수 있습니다.

mk-visual-explain –host hostname –user username –password passwort –database \
databasename -c query1.sql >> query1_data.txt

-

mk-query-profiler –host hostname –user username –password passwort –database \
databasename query1_data.txt >> query1_data.txt

대부분의 경우 새로운 mysql 버전을 조합하는 것이 성능에 매우 중요합니다.예를 들어 mysql 5.0.23과 5.1.4를 비교할 때 복잡한 쿼리의 실행 계획이 매우 다르다는 것을 경험했습니다.5.1.4를 통해 당사의 환경에서 훨씬 빠르게 실행됩니다.

mysql에 대한 유용한 정보는 http://www.mysqlperformanceblog.com/ 및 "High Performance MySQL"에서 찾을 수 있습니다.

Tabe Cache: "테이블 캐시는 테이블을 나타내는 객체를 저장합니다.캐시의 각 개체에는 테이블의 스토리지 엔진에 따라 관련 테이블의 구문 분석된 .frm 파일과 기타 데이터가 포함됩니다.

테이블 캐시의 설계는 MyISAM 중심입니다.이는 서버와 스토리지 엔진 간의 분리가 완전히 해소되지 않은 영역 중 하나입니다.이 영역은 과거의 이유로 인한 것입니다.InnoDB는 파일 기술자를 보유하는 등 다양한 목적으로 테이블 캐시에 의존하지 않기 때문에 테이블 캐시는 InnoDB에게 조금 덜 중요합니다.그러나 InnoDB도 구문 분석된 .frm 파일을 캐싱하는 것이 좋습니다.

테이블 캐시를 올리면 열려 있는 파일 제한에 오류가 있을 수 있습니다.또한 서버에서 open_files_limit 변수를 늘려야 합니다.또한 운영체제의 오픈파일 제한(http://www.cyberciti.biz/faq/linux-increase-the-maximum-number-of-open-files/)도 필요합니다.

스레드 캐시:

스레드 캐시는 현재 연결과 연결되어 있지 않지만 새 연결을 제공할 준비가 된 스레드를 유지합니다.MySQL은 캐시에 사용 가능한 스레드가 있는 한 각 연결에 대해 새 스레드를 만들 필요가 없으므로 연결 요청에 매우 빠르게 응답할 수 있습니다.

[!!] 디스크에 작성된 임시 테이블: 46%(디스크에 400,000/총 869,000)tmp_table_size 및 max_heap_table_size가 아직 설정되지 않은 경우 테이블을 늘립니다.디스크 동작은 RAM 동작에 비해 매우 느립니다.워드프레스는 blob/text 열을 많이 사용합니까?BLOB 및 텍스트 열은 메모리 테이블에서 사용할 수 없기 때문에 많은 이점을 얻을 수 없습니다.

[OK] 사용 가능한 최대 연결 사용량: 53%(53/100) RAM을 절약하기 위해 허용되는 최대 연결 수를 줄일 수 있습니다.한편, 피크시에 접속이 끊어질 가능성이 있습니다.

PHP용 opcode 캐시를 사용하는 것은 매우 좋은 생각입니다!

WP용 MySQL 튜닝을 위한 권장 사항:

최적의 성능을 위해 데이터베이스 테이블을 정기적으로 최적화(및 필요에 따라 복구)해야 합니다.

이 기능과 데이터베이스 백업을 제공하는 WP-DBManager 플러그인을 사용하는 것이 좋습니다.이 모든 것은 블로그 설치에 매우 중요합니다.

WP-DBManager는 일정을 잡고 잊어버릴 수 있으며 모든 작업을 자동으로 처리합니다.

다른 대안은 phpmyadmin과 같은 도구를 사용하여 수동으로 테이블을 최적화하고 복구하는 것입니다.

MySQL 쿼리 캐시는 쿼리가 다시 올 경우를 대비해 쿼리 결과를 저장합니다.단, 쿼리의 바이트 텍스트 저장 방법만 알고 컴파일된 버전은 모르기 때문에 쿼리에 대한 작은 변경으로 다른 캐시 엔트리가 생성됩니다.모든 쿼리에 고유 ID가 없는 경우 이 옵션을 설정하십시오./etc/my.cnf에 다음을 추가하여 활성화할 수 있습니다.

query_cache_type = 1
query_cache_size = 26214400

이것은 당신의 질문에 대한 직접적인 답변은 아니지만, 제 경험상 워드프레스는 프런트 엔드 서버의 캐시를 사용하여 매우 잘 최적화될 수 있습니다.또한 대부분의 워드프레스는 데이터베이스가 아닌 프런트엔드 머신에 CPU가 바인드되어 있는 것 같습니다.(실제로 병목현상을 최적화하고 있는지 다시 한 번 확인할 수 있습니다).

예를 들어 "w3 총 캐시"를 살펴 보십시오.APC에서 사용하는 것이 가장 간단한 방법입니다.apc 사이즈를 확인해 주세요.shm_size(pp.ini 단위) 및 캐시 히트율(일부 apc info 유틸리티는 w3tc와 함께 제공되어야 합니다).

이러한 셋업을 통해 단일 서버에서 워드프레스 인스턴스가 원활하게 실행되고 있으며, 한 달에 200.000 페이지뷰를 훨씬 넘는 것을 보았습니다.

언급URL : https://stackoverflow.com/questions/3753504/mysqltuner-suggestions-and-changes-to-my-cnf