Задача: Хочу привести настройки Mysql своего сервера к максимальной производительности купленной VPS (ссылка) которая обслуживаем мои блоги на базе CMS WordPress: https://{www,net,win,lin,home}.ekzorchik.ru
Текущие характеристики виртуальной машины за 190руб в месяц
RAM = 1 GbCPU = 1 (2 cores)HDD = 30GbOS = 16.04.5
Но все ниже также будет справедливо и для последующих долговременных релизов Ubuntu LTS моих других проектов и проектов компании, которые обслуживаю. Опыт настройки параметров приходит в каждом случае отдельно и формируется собственная база знаний, как сейчас с помощью данного инструмента решается задача по оптимизации настроек mysql:
Шаг №1: В роли оптимизации настроек буду использовать скрипт mysqltuner, его можно найти, как дефолтных репозитариях так и скачать с github:
ekzorchik@ekzorchik:~$ uname -a && lsb_release -a Linux ekzorchik.ru 4.4.0-141-generic #167-Ubuntu SMP Wed Dec 5 10:40:15 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 16.04.5 LTS Release: 16.04 Codename: xenial ekzorchik@ekzorchik:~$ ekzorchik@ekzorchik:~$ apt-cache search mysqltuner mysqltuner - high-performance MySQL tuning script ekzorchik@ekzorchik:~$ apt-cache show mysqltuner | grep Version Version: 1.6.0-1 ekzorchik@ekzorchik:~$
если стоит ОСЬ Ubuntu 18.04.5, то версия 1.7.2-1
ekzorchik@ekzorchik:~$ wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
На заметку: Чтобы скачать данный скрипт под Windows задействуем утилиту заметку "Скачать файл встроенными средствами Windows"
Отработало увы только через "Способ №2" и "Способ №3"
А скрипт позиционирует себя как версия 2.0.4:
ekzorchik@ekzorchik:~$ sudo perl mysqltuner.pl | head -n1 >> MySQLTuner 2.0.4 ekzorchik@ekzorchik:~$
Шаг №2: Запускаю Perl—скрипт с целью выявления текущих настроек и рекомендаций, что нужно подправить дабы сервис работал с текущими ресурсами более продуктивно:
ekzorchik@ekzorchik:~$ sudo perl mysqltuner.pl [sudo] password for ekzorchik: >> MySQLTuner 2.0.4 * Jean-Marie Renouard <jmrenouard@gmail.com> * Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.pl/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [OK] Logged in using credentials from Debian maintenance account. [!!] Your MySQL version 10.0.36-MariaDB-0ubuntu0.16.04.1 is EOL software. Upgrade soon! [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [!!] Log file doesn't exist -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA [--] Data in InnoDB tables: 341.7M (Tables: 110) [--] Data in MyISAM tables: 131.7M (Tables: 38) [OK] Total fragmented tables: 0 -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA. -------- Views Metrics ----------------------------------------------------------------------------- -------- Triggers Metrics -------------------------------------------------------------------------- -------- Routines Metrics -------------------------------------------------------------------------- -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [!!] There is no basic password file list! -------- CVE Security Recommendations -------------------------------------------------------------- [--] Skipped due to --cvefile option undefined -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 34d 21h 0m 35s (37M q [12.586 qps], 403K conn, TX: 187G, RX: 10G) [--] Reads / Writes: 98% / 2% [--] Binary logging is disabled [--] Physical Memory : 992.0M [--] Max MySQL memory : 3.1G [--] Other process memory: 0B [--] Total buffers: 320.0M global + 18.7M per thread (151 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 413.4M (41.68% of installed RAM) [!!] Maximum possible memory usage: 3.1G (316.70% of installed RAM) [!!] Overall possible memory usage with other process exceeded memory [OK] Slow queries: 0% (9/37M) [OK] Highest usage of available connections: 3% (5/151) [OK] Aborted connections: 0.00% (1/403620) [!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance [!!] Query cache may be disabled by default due to mutex contention. [OK] Query cache efficiency: 45.2% (29M cached / 65M selects) [!!] Query cache prunes per day: 24540 [OK] Sorts requiring temporary tables: 0% (108 temp sorts / 3M sorts) [OK] No joins without indexes [OK] Temporary tables created on disk: 17% (613K on disk / 3M total) [OK] Thread cache hit rate: 99% (5 created / 403K connections) [OK] Table cache hit rate: 114% (400 hits / 348 requests) [OK] table_definition_cache (400) is greater than number of tables (300) [OK] Open file limit used: 18% (185/1K) [OK] Table locks acquired immediately: 99% (12M immediate / 12M locks) -------- Performance schema ------------------------------------------------------------------------ [!!] Performance_schema should be activated. [--] Sys schema is not installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 33.2% (5.3M used / 16.0M cache) [OK] Key buffer size / total MyISAM indexes: 16.0M/5.9M [OK] Read Key buffer hit rate: 100.0% (390M cached / 2K reads) [OK] Write Key buffer hit rate: 99.0% (14K cached / 14K writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [!!] InnoDB buffer pool / data size: 128.0M / 341.7M [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 48.0M * 2 / 128.0M should be equal to 25% [!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1). [--] InnoDB Buffer Pool Chunk Size not used or defined in your version [OK] InnoDB Read buffer efficiency: 99.98% (768631455 hits/ 768808251 total) [!!] InnoDB Write Log efficiency: 50.3% (309209 hits/ 614745 total) [OK] InnoDB log waits: 0.00% (0 waits / 305536 writes) -------- Aria Metrics ------------------------------------------------------------------------------ [--] Aria Storage Engine is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/0B [OK] Aria pagecache hit rate: 100.0% (1B cached / 286K reads) -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] Binlog format: STATEMENT [--] XA support enabled: ON [--] Semi synchronous replication Master: Not Activated [--] Semi synchronous replication Slave: Not Activated [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: You are using an unsupported version for production environments Upgrade as soon as possible to a supported version ! Reduce your overall MySQL memory footprint for system stability Dedicate this server to your database for highest performance. Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 Performance schema should be activated for better diagnostics Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** skip-name-resolve=1 query_cache_size (=0) query_cache_type (=0) query_cache_size (> 16M) performance_schema=ON key_buffer_size (~ 5M) innodb_buffer_pool_size (>= 341.7M) if possible. innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals 25% of buffer pool size. innodb_buffer_pool_instances (=1) ekzorchik@ekzorchik:~$
Шаг №3: Из вывода скрипта нужно все что ниже "Variables to adjust:" можно применить к себе:
ekzorchik@ekzorchik:~$ sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf [mysqld] skip-name-resolve = 1 performance_schema = ON #key_buffer_size = 16M key_buffer_size = 5M innodb_buffer_pool_size = 341M innodb_log_file_size = 2M innodb_buffer_pool_instances = 1 ekzorchik@ekzorchik:~$ sudo systemctl restart mysql
После еще раз запускаю скрипт или по прошествии некоторого времени и модернизирую настройки, затем оставляю как получилось. Мы на работе проделали все тоже самое, но с выданными настройками Perl скриптом и буквально через несколько часов почувствовали изменения в работе сервиса:
ekzorchik@customizer:~$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf skip-name-resolve = 1 key_buffer_size = 0 innodb_buffer_pool_size = 2G innodb_log_file_size = 16M innodb_buffer_pool_instances = 2 ekzorchik@customizer:~$ sudo systemctl restart mysql
Шаг №4: Также на сервисе организации — вот так вот скрипт не запустился через sudo:
ekzorchik@customizer:~$ sudo perl mysqltuner.pl >> MySQLTuner 2.0.4 * Jean-Marie Renouard <jmrenouard@gmail.com> * Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.pl/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [!!] Attempted to use login credentials from Debian maintenance account, but they failed. ekzorchik@customizer:~$
Применил опцию --help и выявил, что нужно указать логин и пароль
ekzorchik@customizer:~$ sudo perl mysqltuner.pl --user root --pass PASSWORD
Шаг №5: Я себе в свой репозитарий кода и наработок даже скачал (hg clone http://srv-code.polygon.lan/hg/mysql) его и теперь буду использовать в проектах домашнего назначения и самообразования.
Итого, чем больше сталкиваешь с различными задачами, тем больше узнаешь думая, что раньше знал много, а тут облом и тем интереснее становится все познать.
На этом заметка завершена, с уважением автор блога Олло Александр aka ekzorchik.ru