lightman555
Гуру форума
- Регистрация
- 7 Мар 2013
- Сообщения
- 375
- Реакции
- 65
- Автор темы
- #41
получается тогда вообще на этот тюнер полагаться особо и нельзя?
Помощь Поделитесь опытом настройки my.cnf для Престы?
- Автор темы lightman555
- Дата начала
savvato
Кодер
- Регистрация
- 3 Янв 2012
- Сообщения
- 530
- Реакции
- 571
Это гугл транслейт. Для набивки постов наши англоязычные друзья юзают.
lightman555
Гуру форума
- Регистрация
- 7 Мар 2013
- Сообщения
- 375
- Реакции
- 65
- Автор темы
- #43
Да я уже понял!
Получается что кеш не используется как таковой и результат проделанной работы получается не эффективнымнужно было оставлять формат таблиц как есть - тогда кешировалось явно лучше
lightman555
Гуру форума
- Регистрация
- 7 Мар 2013
- Сообщения
- 375
- Реакции
- 65
- Автор темы
- #44
спустя примерно 10-ть дней после перехода полностью на InnoDB и перестройки конфигурационного файла
тюнер показывает следующее:
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 184M (Tables: 183)
[--] Data in InnoDB tables: 400M (Tables: 509)
[!!] Total fragmented tables: 509
-------- Security Recommendations -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
-------- CVE Security Recommendations ---------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -------------------------------------------------
[--] Up for: 11d 1h 3m 50s (35M q [36.966 qps], 331K conn, TX: 41B, RX: 8B)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Total buffers: 2.4G global + 36.5M per thread (151 max threads)
[OK] Maximum reached memory usage: 3.2G (40.03% of installed RAM)
[!!] Maximum possible memory usage: 7.8G (97.50% of installed RAM)
[OK] Slow queries: 0% (2K/35M)
[OK] Highest usage of available connections: 14% (22/151)
[OK] Aborted connections: 0.17% (565/331357)
[OK] Query cache efficiency: 67.4% (22M cached / 33M selects)
[!!] Query cache prunes per day: 329903
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5M sorts)
[OK] Temporary tables created on disk: 7% (50K on disk / 703K total)
[OK] Thread cache hit rate: 99% (22 created / 331K connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 4% (415/8K)
[OK] Table locks acquired immediately: 100% (15M immediate / 15M locks)
-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 18.3% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/78.3M
[OK] Read Key buffer hit rate: 100.0% (134M cached / 4 reads)
[OK] Write Key buffer hit rate: 100.0% (36M cached / 10 writes)
-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 2.0G/400.0M
[OK] InnoDB buffer pool instances: 2
[!!] InnoDB Used buffer: 18.79% (24631 used/ 131071 total)
[OK] InnoDB Read buffer efficiency: 100.00% (19269783584 hits/ 19269804303 total)
[!!] InnoDB Write Log efficiency: 6.21% (5547 hits/ 89301 total)
[OK] InnoDB log waits: 0.00% (0 waits / 83754 writes)
-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.
-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.
-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Restrict Host for user@% to user@SpecificDNSorIp
Reduce your overall MySQL memory footprint for system stability
Increasing the query_cache size over 128M may reduce performance
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 256M) [see warning above]
тюнер показывает следующее:
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 184M (Tables: 183)
[--] Data in InnoDB tables: 400M (Tables: 509)
[!!] Total fragmented tables: 509
-------- Security Recommendations -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
-------- CVE Security Recommendations ---------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -------------------------------------------------
[--] Up for: 11d 1h 3m 50s (35M q [36.966 qps], 331K conn, TX: 41B, RX: 8B)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Total buffers: 2.4G global + 36.5M per thread (151 max threads)
[OK] Maximum reached memory usage: 3.2G (40.03% of installed RAM)
[!!] Maximum possible memory usage: 7.8G (97.50% of installed RAM)
[OK] Slow queries: 0% (2K/35M)
[OK] Highest usage of available connections: 14% (22/151)
[OK] Aborted connections: 0.17% (565/331357)
[OK] Query cache efficiency: 67.4% (22M cached / 33M selects)
[!!] Query cache prunes per day: 329903
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5M sorts)
[OK] Temporary tables created on disk: 7% (50K on disk / 703K total)
[OK] Thread cache hit rate: 99% (22 created / 331K connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[OK] Open file limit used: 4% (415/8K)
[OK] Table locks acquired immediately: 100% (15M immediate / 15M locks)
-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 18.3% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/78.3M
[OK] Read Key buffer hit rate: 100.0% (134M cached / 4 reads)
[OK] Write Key buffer hit rate: 100.0% (36M cached / 10 writes)
-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 2.0G/400.0M
[OK] InnoDB buffer pool instances: 2
[!!] InnoDB Used buffer: 18.79% (24631 used/ 131071 total)
[OK] InnoDB Read buffer efficiency: 100.00% (19269783584 hits/ 19269804303 total)
[!!] InnoDB Write Log efficiency: 6.21% (5547 hits/ 89301 total)
[OK] InnoDB log waits: 0.00% (0 waits / 83754 writes)
-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.
-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.
-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Restrict Host for user@% to user@SpecificDNSorIp
Reduce your overall MySQL memory footprint for system stability
Increasing the query_cache size over 128M may reduce performance
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 256M) [see warning above]
lightman555
Гуру форума
- Регистрация
- 7 Мар 2013
- Сообщения
- 375
- Реакции
- 65
- Автор темы
- #45
Прошло 39-ть дней с момента последней оптимизации - данные теперь вот такие. Подскажите что нужно подправить теперь?
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 39d 8h 6m 45s (2 q [0.000 qps], 1M conn, TX: 181B, RX: 149B)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory : 8.0G
[--] Max MySQL memory : 11.0G
[--] Other process memory: 1.4G
[--] Total buffers: 2.7G global + 56.5M per thread (151 max threads)
[--] P_S Max memory usage: 0B
Argument "*main::get_wsrep_option" isn't numeric in numeric ge (>=) at
mysqltuner.pl line 285 (#2)
Argument "*main::get_wsrep_option" isn't numeric in numeric ge (>=) at
mysqltuner.pl line 288 (#2)
Argument "*main::get_wsrep_option" isn't numeric in numeric ge (>=) at
mysqltuner.pl line 291 (#2)
[--] Galera GCache Max memory usage: *main::get_wsrep_optionB
[OK] Maximum reached memory usage: 4.5G (56.11% of installed RAM)
[!!] Maximum possible memory usage: 11.0G (137.49% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/2)
[OK] Highest usage of available connections: 21% (33/151)
[OK] Aborted connections: 0.01% (132/1002978)
[!!] Query cache should be disabled by default due to mutex contention.
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] No tmp tables created on disk
[OK] Thread cache hit rate: 99% (33 created / 1M connections)
[OK] Table cache hit rate: 100% (1K open / 0 opened)
[OK] Open file limit used: 3% (448/12K)
[OK] Table locks acquired immediately: 100% (43M immediate / 43M locks)
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/78.3M
[OK] Read Key buffer hit rate: 100.0% (341M cached / 5 reads)
[OK] Write Key buffer hit rate: 100.0% (98M cached / 4 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 2.0G/451.2M
[OK] InnoDB buffer pool instances: 2
[!!] InnoDB Used buffer: 59.66% (78200 used/ 131071 total)
[OK] InnoDB Read buffer efficiency: 100.00% (32426650467 hits/ 32426671365 total)
[!!] InnoDB Write Log efficiency: 65.55% (1794591 hits/ 2737623 total)
[OK] InnoDB log waits: 0.00% (0 waits / 943032 writes)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Restrict Host for user@% to user@SpecificDNSorIp
Reduce your overall MySQL memory footprint for system stability
Dedicated this server to your database for highest performance.
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_type (=0)
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 39d 8h 6m 45s (2 q [0.000 qps], 1M conn, TX: 181B, RX: 149B)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory : 8.0G
[--] Max MySQL memory : 11.0G
[--] Other process memory: 1.4G
[--] Total buffers: 2.7G global + 56.5M per thread (151 max threads)
[--] P_S Max memory usage: 0B
Argument "*main::get_wsrep_option" isn't numeric in numeric ge (>=) at
mysqltuner.pl line 285 (#2)
Argument "*main::get_wsrep_option" isn't numeric in numeric ge (>=) at
mysqltuner.pl line 288 (#2)
Argument "*main::get_wsrep_option" isn't numeric in numeric ge (>=) at
mysqltuner.pl line 291 (#2)
[--] Galera GCache Max memory usage: *main::get_wsrep_optionB
[OK] Maximum reached memory usage: 4.5G (56.11% of installed RAM)
[!!] Maximum possible memory usage: 11.0G (137.49% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/2)
[OK] Highest usage of available connections: 21% (33/151)
[OK] Aborted connections: 0.01% (132/1002978)
[!!] Query cache should be disabled by default due to mutex contention.
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] No tmp tables created on disk
[OK] Thread cache hit rate: 99% (33 created / 1M connections)
[OK] Table cache hit rate: 100% (1K open / 0 opened)
[OK] Open file limit used: 3% (448/12K)
[OK] Table locks acquired immediately: 100% (43M immediate / 43M locks)
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/78.3M
[OK] Read Key buffer hit rate: 100.0% (341M cached / 5 reads)
[OK] Write Key buffer hit rate: 100.0% (98M cached / 4 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 2.0G/451.2M
[OK] InnoDB buffer pool instances: 2
[!!] InnoDB Used buffer: 59.66% (78200 used/ 131071 total)
[OK] InnoDB Read buffer efficiency: 100.00% (32426650467 hits/ 32426671365 total)
[!!] InnoDB Write Log efficiency: 65.55% (1794591 hits/ 2737623 total)
[OK] InnoDB log waits: 0.00% (0 waits / 943032 writes)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Restrict Host for user@% to user@SpecificDNSorIp
Reduce your overall MySQL memory footprint for system stability
Dedicated this server to your database for highest performance.
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_type (=0)
У меня вообще такое не пишет
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 512.0M/51.8M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 12.50% (4096 used/ 32767 total)
[OK] InnoDB Read buffer efficiency: 100.00% (484599167 hits/ 484602448 total)
[!!] InnoDB Write Log efficiency: 66.75% (139843 hits/ 209504 total)
[OK] InnoDB log waits: 0.00% (0 waits / 69661 writes)
это все по разделу InnoDB... скрипт тюнера скачан на прошлой неделе
uptime 3 days