My.cnf Optimizasyonu Hakkında

arkadaşlar mysql siteyi çok kastırmaya başladı

anlık hit 400-1000 arasında gidip geliyor



Mysql yüzüden site ucuyor




my.cnf içerigim

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
default-storage-engine = MyISAM
innodb_file_per_table=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Merhaba,

@victories kullanıcısı r10 da hasan bey var kendisi uzmandır konuda ulaşır iseniz size yardımcı olacaktır kesin .

MSN Adresi : hasan@hasangul.com

Saygılar

Hızlı kolay çözüm :
http://mysqltuner.pl

tam olarak nasıl yapayım hocam ?

cd /
mkdir dosyalar
cd dosyalar
wget http://mysqltuner.pl
chmod +x mysqltun*
./mysqltuner.pl


kolay gelsin

çok teşekkürler şimdi hocam biraz fazla oluyorum ama bunu ilk defa kullandım bazı öneriler verdi anlam veremedim çıktı budur? ne demek istiyor

 >>  MySQLTuner 1.2.0 - 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.1.66-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 435M (Tables: 195)
[--] Data in MEMORY tables: 6M (Tables: 3)
[!!] Total fragmented tables: 7

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 7m 38s (890K q [116.274 qps], 90K conn, TX: 1B, RX: 133M)
[--] Reads / Writes: 57% / 43%
[--] Total buffers: 528.0M global + 15.2M per thread (750 max threads)
[!!] Maximum possible memory usage: 11.7G (162% of installed RAM)
[OK] Slow queries: 0% (0/890K)
[OK] Highest usage of available connections: 16% (123/750)
[OK] Key buffer size / total MyISAM indexes: 256.0M/51.8M
[OK] Key buffer hit rate: 99.2% (32M cached / 247K reads)
[OK] Query cache efficiency: 27.3% (108K cached / 396K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (100 temp sorts / 60K sorts)
[!!] Joins performed without indexes: 968
[!!] Temporary tables created on disk: 27% (1K on disk / 4K total)
[OK] Thread cache hit rate: 99% (123 created / 90K connections)
[!!] Table cache hit rate: 0% (16 open / 124K opened)
[OK] Open file limit used: 0% (25/3K)
[!!] Table locks acquired immediately: 92%

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    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
    Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 3.0M, or always use indexes with joins)
    tmp_table_size (> 256M)
    max_heap_table_size (> 16M)
    table_cache (> 16)

Sonda yer alan :

join_buffer_size (> 3.0M, or always use indexes with joins)
tmp_table_size (> 256M)
max_heap_table_size (> 16M)
table_cache (> 16)


my cnf içinde
join_buffer_size = 3.0M
tmp_table_size = 256M
max_heap_table_size = 16M
table_cache = 16

gibi ekleyerek

service mysqld restart yapın bu işlemi yapmadan once my.cnf yedeğini mutlaka

cp /etc/my.cnf /etc/my.cnf.bak

gibi oluşturun kesinlikle
Sunucunuzun özelliklerine göre yukarıdaki değerleri esnetebilirsiniz. Bu size mysqlin kullanım durumuna göre tavsiyelerde bulunan bir scripttir