Optymalizacja mysql

[hosthebest 0]

Posiadam VPS z 2GB pamięci ram co waszym zdaniem najlepiej pozmieniać w pliku my.cnf

 

 

Konfiguracja:

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

set-variable = max_connections=100

safe-show-database

skip-locking

skip-innodb

skip-bdb

skip-external-locking

join_buffer_size = 256K

tmp_table_size = 128M

query_cache_limit = 16M

query_cache_size = 16M

long_query_time = 5

open_files_limit = 5000

log-slow-queries = /var/log/mysql-slow-queries.log

query_cache_type = 1

thread_concurrency = 4

default-character-set = utf8

read_rnd_buffer_size = 768K

thread_cache_size = 256

table_cache = 2000

key_buffer = 8M

max_allowed_packet = 16M

max_heap_table_size = 50M

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 

 

>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>

>> Bug reports, feature requests, and downloads at http://mysqltuner.com/

>> Run with '--help' for additional options and output filtering

 

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.77-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: 51M (Tables: 112)

[OK] Total fragmented tables: 0

 

-------- Performance Metrics -------------------------------------------------

[--] Up for: 2h 12m 14s (37K q [4.779 qps], 3K conn, TX: 11M, RX: 2M)

[--] Reads / Writes: 75% / 25%

[--] Total buffers: 84.0M global + 3.3M per thread (100 max threads)

[OK] Maximum possible memory usage: 415.2M (20% of installed RAM)

[OK] Slow queries: 0% (0/37K)

[OK] Highest usage of available connections: 6% (6/100)

[OK] Key buffer size / total MyISAM indexes: 8.0M/3.4M

[OK] Key buffer hit rate: 98.5% (215K cached / 3K reads)

[OK] Query cache efficiency: 72.4% (18K cached / 25K selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)

[!!] Temporary tables created on disk: 45% (2K on disk / 4K total)

[OK] Thread cache hit rate: 99% (6 created / 3K connections)

[OK] Table cache hit rate: 91% (135 open / 147 opened)

[OK] Open file limit used: 5% (264/5K)

[OK] Table locks acquired immediately: 99% (9K immediate / 9K locks)

 

-------- Recommendations -----------------------------------------------------

General recommendations:

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

Variables to adjust:

tmp_table_size (> 128M)

max_heap_table_size (> 50M)