MySQL 설치를 어떻게 최적화할 수 있나요?

MySQL 설치를 어떻게 최적화할 수 있나요?

(512MB RAM을 갖춘 Debian 7 32비트 KVM - SSD 스토리지 - CPU 1개)

서버는 mysql 전용이 아닙니다.

그것에 대해 작은 포럼을 운영합니다. (Mysql -> 대부분 읽기)

MySQL 설치를 최적화하는 방법에 대한 조언을 갖고 있는 사람이 있습니까?

서버는 피크 시간대에 지연이 없습니다.

감사해요

my.cnf 파일의 현재 구성은 다음과 같습니다.

[mysqld]
#skip-locking
#skip-innodb
#skip-name-resolve
#skip-networking
#skip-bdb
local-infile=0

# Logs
log_error=/var/log/mysql/mysql_error.log
#log_slow_queries=/var/log/mysql/mysql_slow.log
#long_query_time=10
#log-queries-not-using-indexes

# Buffers
key_buffer_size=16M
sort_buffer_size=1M
join_buffer_size=1M
max_join_size=2M

#Threads
thread_concurrency=2
thread_cache_size=1

#Table_cache
table_cache=1024

#Temp tables
max_heap_table_size=128M
tmp_table_size=128M

#Query_cache
query_cache_limit=1M
query_cache_size=16M

#Timeouts
interactive_timeout=90
wait_timeout=90

#Connections
max_connections=50

#[innodb]
innodb_buffer_pool_size=128M
innodb_file_per_table=1
innodb_log_file_size=5M
innodb_thread_concurrency=1

MySQLTuner를 실행하면 다음과 같은 결과가 나타납니다.

>>  MySQLTuner 1.3.0 - Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.36-34.2-648.wheezy
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 5M (Tables: 64)
[--] Data in InnoDB tables: 82M (Tables: 195)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 252K (Tables: 3)
[!!] Total fragmented tables: 26

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 4h 46m 25s (2M q [3.459 qps], 181K conn, TX: 7B, RX: 521M)
[--] Reads / Writes: 54% / 46%
[--] Total buffers: 304.0M global + 2.6M per thread (50 max threads)
[!!] Maximum possible memory usage: 432.1M (86% of installed RAM)
[OK] Slow queries: 0% (0/2M)
[OK] Highest usage of available connections: 62% (31/50)
[OK] Key buffer size / total MyISAM indexes: 16.0M/5.1M
[OK] Key buffer hit rate: 99.8% (1M cached / 2K reads)
[OK] Query cache efficiency: 74.4% (1M cached / 1M selects)
[!!] Query cache prunes per day: 3959
[OK] Sorts requiring temporary tables: 0% (21 temp sorts / 81K sorts)
[!!] Joins performed without indexes: 30762
[!!] Temporary tables created on disk: 46% (74K on disk / 160K total)
[OK] Thread cache hit rate: 86% (25K created / 181K connections)
[!!] Table cache hit rate: 10% (347 open / 3K opened)
[OK] Open file limit used: 8% (178/2K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB buffer pool / data size: 128.0M/82.8M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    query_cache_size (> 16M)
    join_buffer_size (> 1.0M, or always use indexes with joins)
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)
    table_cache (> 1024)

TUNING PRIMER를 실행하면 다음과 같은 결과가 나타납니다.

 -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.5.36-34.2-648.wheezy i686

Uptime = 8 days 4 hrs 47 min 29 sec
Avg. qps = 3
Total Questions = 2450393
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 0 out of 2450414 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 1
Current threads_cached = 0
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 50
Current threads_connected = 1
Historic max_used_connections = 31
The number of used connections is 62% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 25 M
Current InnoDB data space = 82 M
Current InnoDB buffer pool free = 13 %
Current innodb_buffer_pool_size = 128 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 255 M
Configured Max Per-thread Buffers : 128 M
Configured Max Global Buffers : 176 M
Configured Max Memory Limit : 304 M
Physical Memory : 502 M
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 5 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 513
Key buffer free ratio = 67 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 16 M
Current query_cache_used = 9 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 60.50 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 1 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 30765 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 2109 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 1024 tables
Current table_definition_cache = 400 tables
You have a total of 303 tables
You have 348 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 128 M
Current tmp_table_size = 128 M
Of 86516 temp tables, 46% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 489 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 178950
Your table locking seems to be fine

답변1

몇 가지 고려 사항:

  • 데이터베이스 사용량이 가장 많은 동안에는 문제가 없다는 것을 알았습니다. 고장나지도 않은 것을 왜 고치나요?
  • 성능 튜닝은 보유하고 있는 스키마/데이터베이스에서 시작됩니다. 데이터 유형, 제약 조건, 인덱스는 모두 튜닝을 시작하기도 전에 성능에 영향을 미칩니다.
  • 읽기/쓰기 %는 얼마입니까?
  • 실행한 성능 검사 중 일부는 왜곡된 데이터를 표시할 수 있습니다. 예를 들어 디스크의 tmp 테이블은 스키마에 blob 데이터 유형 등이 있다는 사실로 인해 발생할 수 있습니다.
  • 하드웨어는 또 다른 것입니다. SSD를 저장용으로 사용하신다고 하셨습니다. 이렇게 하면 해당 크기/부하의 데이터베이스 서버에 대한 모든 구성 조정이 물 밖으로 날아갑니다. 데이터베이스는 ram을 좋아합니다 :) 특히 innodb와 같은 엔진입니다.
  • 데이터베이스에 저장하는 데이터 종류와 스토리지 엔진을 일치시키세요.
  • 시스템 메모리에 따라 버퍼를 조정하십시오. 예를 들어 최대 50개의 연결이 있고 모든 연결은 2.6m를 얻습니다. 따라서 이는 이미 서버에서 +100m이고 이미 전체 RAM의 25%입니다.
  • 사용하지 않는 엔진과 기능을 끕니다.
  • 쿼리 캐시는 정말 좋을 수도 있고 나쁠 수도 있습니다. 고유한 쿼리가 많으면 이 기능을 끄는 것이 좋습니다. 자두의 양은 캐시가 작다는 것을 나타낼 수도 있습니다. 쉽게 오버헤드를 유발할 수 있는 큰 문제를 해결하지 마십시오.
  • 느린 쿼리 로그를 사용하여 어떤 쿼리가 서버에 문제를 일으킬 수 있는지 확인하고 해당 쿼리를 분석하세요.
  • 실제로 물건을 아는 사람들에 대해 읽어보세요;) 예를 들어여기
  • "테스트"할 장소가 한 곳뿐이라면 매우 조심하세요!

당신이 묻는 것은 다른 사람이 대답하기가 정말 어렵습니다. 당신이 실행한 스크립트는 좋은 시작입니다. 몇 가지 다른 스크립트를 사용하여 반환된 데이터가 예상한 데이터와 서로 일치하는지 테스트합니다.

현재 내가 제안하는 기본 제안은 서버가 현재 시스템의 RAM 대부분을 사용할 수 있기 때문에 서버의 나머지 부분이 불안정해지지 않도록 하는 것입니다. 물론 512m은 작업할 것이 많지는 않지만 그래도... 성능 저하 없이 전역 버퍼를 낮추는 것부터 시작하세요.

mysqltuner 스크립트의 A는 디스크의 tmp 테이블입니다. 이는 일반적으로 대규모 환경에서 성능을 저하시킵니다.

SSD를 사용하고 계신 것을 보니 최소 구성과 슈퍼 튜닝 cnf의 차이가 눈에 띄는 차이를 만들지 않을 것 같습니다. 물론 잘못 설계된 스키마/데이터베이스가 없는 경우입니다.

성능이 좋다고 생각되면 성능 튜닝을 중지하세요. 실제로는 결코 완료되지 않는 작업입니다. 그것은 하드코어한 주제이고 가능성은 무궁무진합니다. 데이터베이스의 작은 부분을 조정하는 데 몇 시간을 낭비할 수 있습니다. 반면 인간은 규모가 큰 환경의 차이를 결코 알아차리지 못할 것입니다.

제가 전문가가 아니기 때문에 틀릴 수도 있습니다. 하지만 저는 그곳에 가봤고 제 경험을 공유하고 싶었습니다. 나는 그들이 도움이되기를 바랍니다

관련 정보