Administrace komentářů

Nazdarek, potreboval by som pomoct pri vyladeni MySQL. Pouzil som MySQLtuner, ktory mi co to ukazal. Nieco som uz nastavoval, nieco nie. V prvom rade je problem, ze mam 32-bit system a teda vyuzijem bez PAE len 2GB RAM. Coskoro pojdem do noveho servera, ale zatial by som potreboval vyladit toho, lebo mi pada MySQL.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.88
[!!] 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: 596M (Tables: 568)
[!!] Total fragmented tables: 20

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 20m 48s (244K q [50.493 qps], 25K conn, TX: 457M, RX: 30M)
[--] Reads / Writes: 84% / 16%
[--] Total buffers: 1.1G global + 9.2M per thread (96 max threads)
[OK] Maximum possible memory usage: 1.9G (65% of installed RAM)
[OK] Slow queries: 0% (3/244K)
[OK] Highest usage of available connections: 26% (25/96)
[OK] Key buffer size / total MyISAM indexes: 1.1G/723.8M
[OK] Key buffer hit rate: 99.9% (32M cached / 33K reads)
[OK] Query cache efficiency: 35.0% (53K cached / 151K selects)
[!!] Query cache prunes per day: 29120
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8K sorts)
[!!] Joins performed without indexes: 55
[OK] Temporary tables created on disk: 5% (438 on disk / 7K total)
[OK] Thread cache hit rate: 98% (417 created / 25K connections)
[OK] Table cache hit rate: 98% (288 open / 293 opened)
[OK] Open file limit used: 4% (461/11K)
[OK] Table locks acquired immediately: 99% (134K immediate / 135K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
Variables to adjust:
    query_cache_size (> 6M)
    join_buffer_size (> 1.0M, or always use indexes with joins)

Co by som mohol upravit, lebo teraz databaza bezi len hodinu a pol a tak hoci sa zda, ze by som mohol znizit max_connections a zvysit tak buffre pre jednotlive spojenia, opak je zrejme pravdou. Ked to bezi dlhsie, tak tych spojeni je tam viacej, niekedy sa naplni aj tych momentalne nastavenych 98 spojeni. Na konci vypisu mi pise nieco o hodnotach query_cache_size a join_buffer_size, ak teda dobre chapem, tieto hodnoty potrebujem zvysit?

Tu je moj config z MySQL:

[mysqld]
bind-address    = 127.0.0.1
port		= 3306
socket		= /tmp/mysql.sock
skip-locking
key_buffer = 1080M
max_allowed_packet = 4M
table_cache = 450
sort_buffer_size = 3M
read_buffer_size = 2M
read_rnd_buffer_size = 3M
myisam_sort_buffer_size = 32M
thread_cache_size = 8
query_cache_size = 6M
join_buffer_size = 1M
expire_logs_days = 2
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 4