#1
5 февраля 2013 в 19:33
Сервер с 16ГБ оперативки и двумя 4х ядерными Xeon процами по 3,2
mysqltuner.pl подскажет
#3
5 февраля 2013 в 22:28
еще нужно статистику смотреть, удобно в phpmyadmin, и в зависмости от того что там красным шрифтом выделено изменять параметры и снова статистику собирать
Сегодня в 09:48
#4
6 февраля 2013 в 07:17
mysqltuner.pl подскажет
— General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.29-cll
[OK] Operating on 64-bit architecture
— Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 78M (Tables: 309)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 8
— Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
— Performance Metrics -------------------------------------------------
[--] Up for: 11h 45m 18s (6M q [141.937 qps], 153K conn, TX: 7B, RX: 1B)
[--] Reads / Writes: 62% / 38%
[--] Total buffers: 1.5G global + 1.0G per thread (151 max threads)
[!!] Maximum possible memory usage: 154.3G (2003% of installed RAM)
[OK] Slow queries: 0% (23/6M)
[OK] Highest usage of available connections: 15% (24/151)
[OK] Key buffer size / total MyISAM indexes: 384.0M/14.2M
[OK] Key buffer hit rate: 100.0% (69M cached / 2K reads)
[OK] Query cache efficiency: 89.1% (4M cached / 5M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 86K sorts)
[!!] Temporary tables created on disk: 36% (611 on disk / 1K total)
[OK] Thread cache hit rate: 99% (97 created / 153K connections)
[OK] Table cache hit rate: 98% (375 open / 382 opened)
[OK] Open file limit used: 16% (711/4K)
[OK] Table locks acquired immediately: 99% (951K immediate / 956K locks)
— Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
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
Temporary table size is already large — reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.29-cll
[OK] Operating on 64-bit architecture
— Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 78M (Tables: 309)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 8
— Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
— Performance Metrics -------------------------------------------------
[--] Up for: 11h 45m 18s (6M q [141.937 qps], 153K conn, TX: 7B, RX: 1B)
[--] Reads / Writes: 62% / 38%
[--] Total buffers: 1.5G global + 1.0G per thread (151 max threads)
[!!] Maximum possible memory usage: 154.3G (2003% of installed RAM)
[OK] Slow queries: 0% (23/6M)
[OK] Highest usage of available connections: 15% (24/151)
[OK] Key buffer size / total MyISAM indexes: 384.0M/14.2M
[OK] Key buffer hit rate: 100.0% (69M cached / 2K reads)
[OK] Query cache efficiency: 89.1% (4M cached / 5M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 86K sorts)
[!!] Temporary tables created on disk: 36% (611 on disk / 1K total)
[OK] Thread cache hit rate: 99% (97 created / 153K connections)
[OK] Table cache hit rate: 98% (375 open / 382 opened)
[OK] Open file limit used: 16% (711/4K)
[OK] Table locks acquired immediately: 99% (951K immediate / 956K locks)
— Recommendations -----------------------------------------------------
General recommendations:
Add skip-innodb to MySQL configuration to disable InnoDB
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
Temporary table size is already large — reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
2) Таблицы оптимизировал, почему-то они через некоторое время (буквально через минуту-две) снова требуют оптимизации.
3) Тут все ясно что MySQL стартован менее 24 часов назад.
4) Temporary table size is already large — reduce result set size — тут что сделать?
5) Reduce your SELECT DISTINCT queries without LIMIT clauses — это тоже не понятно!
#5
6 февраля 2013 в 18:24
подскажет кто по 2) 4) и 5) пункту?