Оптимизация конфигурации Mysql, через mysqltuner

wget mysqltuner.pl

chmod u+x mysqltuner.pl

./mysqltuner.pl

Please enter your MySQL administrative login:

Please enter your MySQL administrative password:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script

[!!] Your MySQL version 4.1.21-log is EOL software!  Upgrade soon!
[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: 1G (Tables: 2268)
[--] Data in InnoDB tables: 896K (Tables: 56)

[--] Data in HEAP tables: 1M (Tables: 2)
[!!] Total fragmented tables: 200

 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 16h 1m 16s (7M q [30.639 qps], 587K conn, TX: 2B, RX: 1B)

[--] Reads / Writes: 66% / 34%
[--] Total buffers: 458.0M global + 27.2M per thread (70 max threads)

[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.3G (115% of installed RAM)

[OK] Slow queries: 0% (1K/7M)
[!!] Highest connection usage: 100%  (71/70)

[OK] Key buffer size / total MyISAM indexes: 64.0M/360.6K
[OK] Key buffer hit rate: 99.9% (155M cached / 165K reads)

[OK] Query cache efficiency: 52.3% (1M cached / 3M selects)
[!!] Query cache prunes per day: 37386

[OK] Sorts requiring temporary tables: 0% (24 temp sorts / 149K sorts)
[!!] Joins performed without indexes: 59355

[!!] Temporary tables created on disk: 31% (8K on disk / 25K total)
[OK] Thread cache hit rate: 98% (6K created / 587K connections)

[OK] Table cache hit rate: 20% (2K open / 9K opened)
[OK] Open file limit used: 34% (3K/11K)

[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 896.0K/8.0M

 
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce or eliminate persistent connections to reduce connection usage
    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
Variables to adjust:

  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    max_connections (> 70)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (> 128M)
    join_buffer_size (> 1020.0K, or always use indexes with joins)
    tmp_table_size (> 256M)
    max_heap_table_size (> 255M)

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections

wget http://www.day32.com/MySQL/tuning-primer.sh

chmod u+x tuning-primer.sh

./tuning-primer.sh

Would you like to provide a different socket?: [y/N] y
Do you have your login handy ? [y/N] : y
User:

Password:
Would you like me to create a ~/.my.cnf file for you? [y/N] : y

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

 
MySQL Version 4.1.21-log i386
 
Uptime = 2 days 16 hrs 42 min 46 sec
Avg. qps = 30

Total Questions = 7156747
Threads Connected = 27
 
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/4.1/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 enabled.
Current long_query_time = 1 sec.
You have 1410 out of 7156763 that take longer than 1 sec. to complete
Your long_query_time seems to be fine
.............................................
TABLE SCANS
Current read_buffer_size = 1 M
Current table scan ratio = 541 : 1
read_buffer_size seems to be fine
 
TABLE LOCKING
Current Lock Wait ratio = 1 : 612
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'

cp -a /etc/my.cnf /etc/my.cnf.bak

/etc/rc.d/init.d/mysqld restart