Заметка из цикла оптимизации веб-сервера на базе apache+nginx+mysql+php.
Первым делом решил заняться именно оптимизацией MySQL.
Стоит заранее сказать, что все нижеуказанные параметры, подходящие для моего сервера, могут не подойти для использования другого, так как они являются сугубо индивидуальными и подобраны в соответствии с характеристиками сервера.
На сервере установлен восьмиядерный процессор Xeon и 24Гб оперативной памяти, на котором работает более 200 сайтов, в основном на Joomla и WordPress.
MySQL версии 5.1
Можно воспользоваться утилитой sysbench до и после оптимизации, для сравнения:
# yum install sysbench
# mysql -uroot -ppassword
> create database dbtest;
> quit
# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=dbtest --mysql-user=root --mysql-password=password --oltp-test-mode=complex --oltp-read-only=off --num-threads=8 --max-requests=0 prepare
# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=dbtest --mysql-user=root --mysql-password=password --oltp-test-mode=complex --oltp-read-only=off --num-threads=8 --max-requests=0 --max-time=180 run
# curl mysqltuner.ru > tuner.pl
Если нужно отключить запрос пароля для MySQL, это можно сделать так:
# vim /etc/my.cnf
skip-grant-tables
# service mysqld restart
# perl tuner.pl
>> MySQLTuner 1.6.13 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[!!] Successfully authenticated with no password - SECURITY RISK!
[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 123M (Tables: 1704)
[--] Data in InnoDB tables: 77M (Tables: 1140)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (password = '' OR password IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket')
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] User 'zaoaes_dizgen@%' hasn't specific host restriction.
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 17s (16 q [0.941 qps], 8 conn, TX: 23K, RX: 638B)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory : 15.7G
[--] Max MySQL memory : 148.7M
[--] Other process memory: 557.1M
[--] Total buffers: 26.0M global + 832.0K per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 26.8M (0.17% of installed RAM)
[OK] Maximum possible memory usage: 148.7M (0.93% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/16)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Aborted connections: 0.00% (0/8)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache is disabled
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 4 total)
[!!] Table cache hit rate: 16% (1 open / 6 opened)
[OK] Open file limit used: 0% (2/1K)
[OK] Table locks acquired immediately: 100% (6 immediate / 6 locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.8% (3K used / 16K cache)
[!!] Key buffer size / total MyISAM indexes: 16.0K/54.2M
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 8.0M/77.5M
[!!] InnoDB Used buffer: 9.96% (51 used/ 512 total)
[OK] InnoDB Read buffer efficiency: 96.70% (1291 hits/ 1335 total)
[!!] InnoDB Write Log efficiency: 0% (1 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 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:
Restrict Host for user@% to user@SpecificDNSorIp
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (1024) variable
should be greater than table_open_cache ( 4)
Variables to adjust:
query_cache_size (>= 8M)
table_open_cache (> 4)
key_buffer_size (> 54.2M)
innodb_buffer_pool_size (>= 77M) if possible.
Утилита анализирует параметры конфига MySQL, показывает состояние фрагментации баз и рекомендует использование оптимальных значений.
Тут стоит обратить внимание на все пункты, вне зависимости от [!!] или [OK].
Утилита рекомендует нам оптимизировать базы данных.
Это можно сделать так:
# mysqlcheck --auto-repair --optimize --all-databases
Как известно для InnoDB оптимизация не работает, поэтому это сработает только MyISAM-таблиц.
Теперь нужно привести нужные значения параметров в /etc/my.cnf (конфиг не полный, привожу лишь основные изменения):
# vim /etc/mysql/my.cnf
query_cache_size = 16M
query_cache_limit = 2M
table_open_cache = 8192
key_buffer_size = 64M
innodb_buffer_pool_size = 128M
open_files_limit = 8192
skip-grant-tables
Для применения новых настроек, нужно перезагрузить MySQL:
# service mysqld restart
И заново запустить mysqltuner.pl, для сравнения результатов до оптимизации и после:
# perl tuner.pl
>> MySQLTuner 1.6.13 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[!!] Successfully authenticated with no password - SECURITY RISK!
[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 120M (Tables: 1704)
[--] Data in InnoDB tables: 63M (Tables: 1140)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (password = '' OR password IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket')
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] User 'zaoaes_dizgen@%' hasn't specific host restriction.
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2m 49s (4K q [23.775 qps], 187 conn, TX: 10M, RX: 1M)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory : 15.7G
[--] Max MySQL memory : 348.7M
[--] Other process memory: 627.0M
[--] Total buffers: 226.0M global + 832.0K per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 227.6M (1.42% of installed RAM)
[OK] Maximum possible memory usage: 348.7M (2.17% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/4K)
[OK] Highest usage of available connections: 1% (2/151)
[OK] Aborted connections: 0.00% (0/187)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache efficiency: 58.7% (2K cached / 3K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 339 sorts)
[!!] Joins performed without indexes: 1
[OK] Temporary tables created on disk: 20% (54 on disk / 264 total)
[OK] Table cache hit rate: 99% (2K open / 2K opened)
[OK] Open file limit used: 20% (3K/16K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 19.3% (12M used / 67M cache)
[OK] Key buffer size / total MyISAM indexes: 64.0M/52.4M
[OK] Read Key buffer hit rate: 99.6% (170K cached / 708 reads)
[!!] Write Key buffer hit rate: 75.0% (32 cached / 8 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/63.8M
[!!] InnoDB Used buffer: 41.83% (3427 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 99.77% (1282526 hits/ 1285531 total)
[!!] InnoDB Write Log efficiency: 35% (7 hits/ 20 total)
[OK] InnoDB log waits: 0.00% (0 waits / 27 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:
Restrict Host for user@% to user@SpecificDNSorIp
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
Удалить созданную в тесте sysbench базу можно так:
# mysql -uroot -ppassword
> drop database dbtest;
> quit
Полезные ссылки:
Калькулятор, подсчитывает максимальное количество занятой памяти, исходя из значений параметров: jvdev.lv/mysqlmaxmem
Описание параметров MySQL: dev.mysql.com/doc/refman/5.5/en/server-parameters.html
Оптимизация связки nginx, Apache, PHP, MySQL: habr.ru/post/146179/
Оптимизация сервера MySQL. Шаг первый. Утилита mysqltuner: dedicatesupport.com/content/optimizatsiya-servera-mysql-shag-pervyi-utilita-mysqltuner
Онлайн тюнер от Percona: https://tools.percona.com/
Конечно, это не всё, что можно оптимизировать, но и этого не мало. Для более высокой производительности нужно мониторить медленные запросы и оптимизировать их.
Более подробно в документации по MySQL.
Первым делом решил заняться именно оптимизацией MySQL.
Стоит заранее сказать, что все нижеуказанные параметры, подходящие для моего сервера, могут не подойти для использования другого, так как они являются сугубо индивидуальными и подобраны в соответствии с характеристиками сервера.
На сервере установлен восьмиядерный процессор Xeon и 24Гб оперативной памяти, на котором работает более 200 сайтов, в основном на Joomla и WordPress.
MySQL версии 5.1
Можно воспользоваться утилитой sysbench до и после оптимизации, для сравнения:
# yum install sysbench
# mysql -uroot -ppassword
> create database dbtest;
> quit
# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=dbtest --mysql-user=root --mysql-password=password --oltp-test-mode=complex --oltp-read-only=off --num-threads=8 --max-requests=0 prepare
# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=dbtest --mysql-user=root --mysql-password=password --oltp-test-mode=complex --oltp-read-only=off --num-threads=8 --max-requests=0 --max-time=180 run
Пройдёмся по нашим БД утилитой MySQLTuner.
Не стоит полность доверять ей, стоит лишь только проанализировать полученные результаты и сделать для себя выводы.
Для запуска скрипта необходимо установить perl.Не стоит полность доверять ей, стоит лишь только проанализировать полученные результаты и сделать для себя выводы.
# curl mysqltuner.ru > tuner.pl
Если нужно отключить запрос пароля для MySQL, это можно сделать так:
# vim /etc/my.cnf
skip-grant-tables
# service mysqld restart
# perl tuner.pl
>> MySQLTuner 1.6.13 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[!!] Successfully authenticated with no password - SECURITY RISK!
[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 123M (Tables: 1704)
[--] Data in InnoDB tables: 77M (Tables: 1140)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (password = '' OR password IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket')
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] User 'zaoaes_dizgen@%' hasn't specific host restriction.
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 17s (16 q [0.941 qps], 8 conn, TX: 23K, RX: 638B)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory : 15.7G
[--] Max MySQL memory : 148.7M
[--] Other process memory: 557.1M
[--] Total buffers: 26.0M global + 832.0K per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 26.8M (0.17% of installed RAM)
[OK] Maximum possible memory usage: 148.7M (0.93% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/16)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Aborted connections: 0.00% (0/8)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache is disabled
[OK] No Sort requiring temporary tables
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 4 total)
[!!] Table cache hit rate: 16% (1 open / 6 opened)
[OK] Open file limit used: 0% (2/1K)
[OK] Table locks acquired immediately: 100% (6 immediate / 6 locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.8% (3K used / 16K cache)
[!!] Key buffer size / total MyISAM indexes: 16.0K/54.2M
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 8.0M/77.5M
[!!] InnoDB Used buffer: 9.96% (51 used/ 512 total)
[OK] InnoDB Read buffer efficiency: 96.70% (1291 hits/ 1335 total)
[!!] InnoDB Write Log efficiency: 0% (1 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 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:
Restrict Host for user@% to user@SpecificDNSorIp
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (1024) variable
should be greater than table_open_cache ( 4)
Variables to adjust:
query_cache_size (>= 8M)
table_open_cache (> 4)
key_buffer_size (> 54.2M)
innodb_buffer_pool_size (>= 77M) if possible.
Утилита анализирует параметры конфига MySQL, показывает состояние фрагментации баз и рекомендует использование оптимальных значений.
Тут стоит обратить внимание на все пункты, вне зависимости от [!!] или [OK].
Утилита рекомендует нам оптимизировать базы данных.
Это можно сделать так:
# mysqlcheck --auto-repair --optimize --all-databases
Как известно для InnoDB оптимизация не работает, поэтому это сработает только MyISAM-таблиц.
Теперь нужно привести нужные значения параметров в /etc/my.cnf (конфиг не полный, привожу лишь основные изменения):
# vim /etc/mysql/my.cnf
query_cache_size = 16M
query_cache_limit = 2M
table_open_cache = 8192
key_buffer_size = 64M
innodb_buffer_pool_size = 128M
open_files_limit = 8192
skip-grant-tables
Для применения новых настроек, нужно перезагрузить MySQL:
# service mysqld restart
И заново запустить mysqltuner.pl, для сравнения результатов до оптимизации и после:
# perl tuner.pl
>> MySQLTuner 1.6.13 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[!!] Successfully authenticated with no password - SECURITY RISK!
[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 120M (Tables: 1704)
[--] Data in InnoDB tables: 63M (Tables: 1140)
[OK] Total fragmented tables: 0
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (password = '' OR password IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket')
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] User 'zaoaes_dizgen@%' hasn't specific host restriction.
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2m 49s (4K q [23.775 qps], 187 conn, TX: 10M, RX: 1M)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory : 15.7G
[--] Max MySQL memory : 348.7M
[--] Other process memory: 627.0M
[--] Total buffers: 226.0M global + 832.0K per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 227.6M (1.42% of installed RAM)
[OK] Maximum possible memory usage: 348.7M (2.17% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/4K)
[OK] Highest usage of available connections: 1% (2/151)
[OK] Aborted connections: 0.00% (0/187)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache efficiency: 58.7% (2K cached / 3K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 339 sorts)
[!!] Joins performed without indexes: 1
[OK] Temporary tables created on disk: 20% (54 on disk / 264 total)
[OK] Table cache hit rate: 99% (2K open / 2K opened)
[OK] Open file limit used: 20% (3K/16K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 19.3% (12M used / 67M cache)
[OK] Key buffer size / total MyISAM indexes: 64.0M/52.4M
[OK] Read Key buffer hit rate: 99.6% (170K cached / 708 reads)
[!!] Write Key buffer hit rate: 75.0% (32 cached / 8 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/63.8M
[!!] InnoDB Used buffer: 41.83% (3427 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 99.77% (1282526 hits/ 1285531 total)
[!!] InnoDB Write Log efficiency: 35% (7 hits/ 20 total)
[OK] InnoDB log waits: 0.00% (0 waits / 27 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:
Restrict Host for user@% to user@SpecificDNSorIp
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
# mysql -uroot -ppassword
> drop database dbtest;
> quit
Полезные ссылки:
Калькулятор, подсчитывает максимальное количество занятой памяти, исходя из значений параметров: jvdev.lv/mysqlmaxmem
Описание параметров MySQL: dev.mysql.com/doc/refman/5.5/en/server-parameters.html
Оптимизация связки nginx, Apache, PHP, MySQL: habr.ru/post/146179/
Оптимизация сервера MySQL. Шаг первый. Утилита mysqltuner: dedicatesupport.com/content/optimizatsiya-servera-mysql-shag-pervyi-utilita-mysqltuner
Онлайн тюнер от Percona: https://tools.percona.com/
Конечно, это не всё, что можно оптимизировать, но и этого не мало. Для более высокой производительности нужно мониторить медленные запросы и оптимизировать их.
Более подробно в документации по MySQL.
4 комментария:
Выводы конечно "прекрасны" особенно учитывая время после старта базы "[--] Up for: 6s ...", если уж начали пользоваться советчиками, то читайте хоть что они пишут "MySQL started within last 24 hours - recommendations may be inaccurate", а еще лучше, чтоб база поработала неделю или 2-е (для набора статистики), а потом только делать какие либо выводы. Но уж точно не по 6-ти секундам работы базы.
Очевидно, что я и это смотрел. Но в заметку это не вошло.
Полная бредятина и Ахинея ! Некомпетенция это мягко сказано !
--- Максимальное использование памяти уменьшилось с 152.5G (646%) до 10.3G (43%);
это произошло из за изменения параметра join_buffer_size , так как этот параметр указывается для сессиии ! А у Вас он был 1 GB!
Именно поэтому стоит использовать тюнер, когда не уверен в том, что изначально неверно настроил MySQL.
Отправить комментарий