Optimizare mysql cu mysqltuner
Încerc de mai bine de 2 săptămâni să optimizez mysql-ul și nu pot trece de 2 erori. Nici nu știu exact ce anume să urmăresc ca să văd ce-mi cauzează erorile. Dacă este cineva cu experiență sau are informații despre următoarele, vă rog să mă ajutați.
Rezultatele de la mysqltuner:
Cod:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.56-log
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 145M (Tables: 138)
[!!] Total fragmented tables: 2
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 42s (916 q [21.810 qps], 119 conn, TX: 9M, RX: 104K)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 320.0M global + 5.2M per thread (100 max threads)
[OK] Maximum possible memory usage: 838.8M (55% of installed RAM)
[OK] Slow queries: 0% (0/916)
[OK] Highest usage of available connections: 4% (4/100)
[OK] Key buffer size / total MyISAM indexes: 128.0M/40.1M
[!!] Key buffer hit rate: 94.8% (12K cached / 625 reads)
[OK] Query cache efficiency: 52.8% (399 cached / 755 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (1 temp sorts / 88 sorts)
[!!] Temporary tables created on disk: 36% (59 on disk / 162 total)
[OK] Thread cache hit rate: 96% (4 created / 119 connections)
[OK] Table cache hit rate: 95% (161 open / 168 opened)
[OK] Open file limit used: 9% (323/3K)
[OK] Table locks acquired immediately: 100% (469 immediate / 469 locks)
[!!] Connections aborted: 50%
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Your applications are not closing MySQL connections properly
Variables to adjust:
tmp_table_size (> 64M)
max_heap_table_size (> 64M)
Setările din my.cnf :
Cod:
[mysqld_safe]
log-error = /var/log/mysql/error.log
socket = /var/lib/mysql/mysql.sock
nice = 0
[mysqld]
log-error = /var/log/mysql/error.log
slow_query_log=1
slow_query_log_file=/var/lib/mysql/slow_queries.log
skip-innodb
skip-external-locking
skip-networking
max_allowed_packet = 1M
max_connections = 100
wait_timeout = 15
connect_timeout = 5
query_cache_size = 128M
query_cache_limit = 48M
table_open_cache = 1600
thread_cache_size = 16
tmp_table_size = 64M
max_heap_table_size = 64M
join_buffer_size = 1M
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
key_buffer_size = 128M
myisam_sort_buffer_size = 32M
open_files_limit = 3250
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
no-auto-rehash
#safe-updates
[isamchk]
key_buffer_size = 16M
[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 32M
read_buffer_size = 1M
write_buffer_size = 1M
[mysqlhotcopy]
interactive-timeout
Știu că nu sunt 24 de ore trecute, dar variabilele:
[!!] Temporary tables created on disk:
[!!] Connections aborted: 50%
nu se ameliorează după nici 3 zile, procentajele de mai sus sunt menținute și aici e îngrijorarea mea.
Folosesc wordpress pe toate site-urile de pe server, dacă mai aveți nevoie de alte date pentru a-mi răspunde mai clar, vă stau la dispoziție.
