Настройка производительности базы данных MySQL
Я разработал веб-приложение на PHP и MySQL. В последнее время это показывает признаки замедления. Я запустил mysql-tuner на 090516 с временем безотказной работы 12 дней.
Спецификации сервера следующие:
- Linux CentOS 6
- 48 процессоров
- 64 ГБ ОЗУ
Вот вывод журнала mysql-tuner от 090516 с 12 d работоспособностью:
root@layer1 [~/mysqltuner]# perl mysqltuner.pl --outputfile /~/mysqltuner/result_mysqltuner.txt
String found where operator expected at mysqltuner.pl line 3096, near
"get_wsrep_option 'gcache.mem_size'" (#1)
(S syntax) The Perl lexer knows whether to expect a term or an operator.
If it sees what it knows to be a term when it was expecting to see an
operator, it gives you this warning. Usually it indicates that an
operator or delimiter was omitted, such as a semicolon.
(Do you need to predeclare get_wsrep_option?)
Use of implicit split to @_ is deprecated at mysqltuner.pl line 3179 (#2)
(D deprecated, W syntax) It makes a lot of work for the compiler when you
clobber a subroutine's argument list, so it's better if you assign the results
of a split() explicitly to an array (or list).
>> MySQLTuner 1.6.10 - Major Hayden <major@mhtx.net>
>> 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
[OK] Currently running supported MySQL version 5.6.31-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 913M (Tables: 11)
[--] Data in InnoDB tables: 12M (Tables: 63)
[!!] Total fragmented tables: 11
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'munin@localhost' has user name as password.
[--] There are 605 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
Argument "gcache.mem_size" isn't numeric in addition (+) at mysqltuner.pl line
1965 (#3)
(W numeric) The indicated string was fed as an argument to an operator
that expected a numeric value instead. If you're fortunate the message
will identify which operator was so unfortunate.
Argument "gcache.mem_size" isn't numeric in addition (+) at mysqltuner.pl line
1972 (#3)
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 12d 6h 21m 24s (2 q [0.000 qps], 1M conn, TX: 192B, RX: 240B)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory : 62.8G
[--] Max MySQL memory : 338.9M
[--] Other process memory: 505.0M
[--] Total buffers: 169.0M global + 1.1M 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 (#3)
Argument "*main::get_wsrep_option" isn't numeric in numeric ge (>=) at
mysqltuner.pl line 288 (#3)
Argument "*main::get_wsrep_option" isn't numeric in numeric ge (>=) at
mysqltuner.pl line 291 (#3)
[--] Galera GCache Max memory usage: *main::get_wsrep_optionB
[OK] Maximum reached memory usage: 340.0M (0.53% of installed RAM)
[OK] Maximum possible memory usage: 338.9M (0.53% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/2)
[!!] Highest connection usage: 100% (152/151)
[OK] Aborted connections: 0.00% (34/1098331)
[OK] Query cache is 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: 98% (17K created / 1M connections)
[OK] Table cache hit rate: 100% (381 open / 0 opened)
[OK] Open file limit used: 2% (228/10K)
[!!] Table locks acquired immediately: 94%
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (8M used / 8M cache)
[!!] Key buffer size / total MyISAM indexes: 8.0M/147.7M
[!!] Read Key buffer hit rate: 87.6% (16M cached / 1M reads)
[!!] Write Key buffer hit rate: 56.8% (1M cached / 777K writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/12.1M
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[!!] InnoDB Used buffer: 17.82% (1460 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 100.00% (290756388 hits/ 290757478 total)
[!!] InnoDB Write Log efficiency: 12.12% (3691 hits/ 30446 total)
[OK] InnoDB log waits: 0.00% (0 waits / 34137 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
Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Reduce or eliminate persistent connections to reduce connection usage
Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
max_connections (> 151)
wait_timeout (< 28800)
interactive_timeout (< 28800)
key_buffer_size (> 147.7M)
innodb_buffer_pool_instances (=1)
У меня несколько вещей, которые бросаются в глаза, однако я не опытный администратор баз данных MySQL и поэтому ищу совет от сообщества стеков. Посоветуйте, пожалуйста, какие настройки мне нужно изменить в my.cnf, и если мне нужно дефрагментировать таблицы в PHPMYADMIN, нужно ли это делать в нерабочее время.
Я не замечал, что мое веб-приложение замедлялось до недавнего времени, а недавно я перезагрузил сервер после обновления ядра. Текущее время работы по состоянию на этот пост 19 часов.
** Дополнительная информация: ** В веб-приложении есть интерфейс, в котором пользователи входят в систему с помощью мобильных устройств, а также интерфейс, в котором сотрудники офиса управляют представлениями (которые являются формами) и отправляют их в различные отделы.
Я могу обновить это с помощью диаграмм Мунина, если это необходимо. Я понимаю, что сервер сильно перегружен (это означает, что у него гораздо больше ОЗУ и процессоров, чем необходимо, но я хотел, чтобы в будущем было более, чем недостаточно). У меня вопрос, могу ли я просто изменить некоторые настройки my.cnf (как предложено mysqltuner и / или как предложено phpmyadmin) для повышения эффективности?
1 ответ
В ближайшие десять лет MySQL вряд ли будет подвергнут рефакторингу таким образом, чтобы он эффективно использовал более четырех процессоров. Таким образом, ваше выделение ресурсов приводит к потере 44 процессорных ядер. Это кажется слишком много. Кроме того, похоже, что MySQL использует менее 1 ГБ вашей памяти.
Обычно MySQL создается, а не работает. Значение: большинство людей, которые масштабируют приложения на основе MySQL, делают это с помощью зеркал базы данных. Последние версии MariaDB имеют мультимастерную схему зеркалирования.
Единственная очевидная область, где у вас проблемы, - это сбивание соединения. Вам, вероятно, следует отредактировать файл my.cnf, чтобы увеличить количество одновременных подключений, которые вы используете.
Вам необходимо выяснить, использует ли ваше веб-приложение пул соединений. Веб-приложению требуется некоторое время, чтобы установить соединение с базой данных, и если вы переустанавливаете их для каждого веб-запроса, операция открытия соединения может стать узким местом. Ваша статистика показывает более одного нового соединения в секунду в течение 24 часов в день, двенадцать дней. Если ваша рабочая нагрузка не будет постоянно распределяться в течение всех 24 часов в день, это означает, что ваше веб-приложение достигает пика с очень высокой частотой новых подключений. Это может быть причиной вашего замедления.
Вы не сказали, какую платформу веб-приложений вы используете. Большинство платформ имеют операции пула соединений. Большинство веб-приложений используют один набор или, возможно, два набора учетных данных СУБД (имен пользователей), которые выполняют все операции веб-приложения, поэтому они могут повторно использовать соединения в пуле. Ваша статистика говорит, что у вас есть 600+ различных пользователей в базе данных. Это много для веб-приложения.
Также (это нелогично) иногда слишком большое количество потоков веб-приложений может снизить производительность. Иногда для удобства пользователей лучше ограничить количество потоков и позволить веб-серверам ставить в очередь запросы от клиентов, а не пытаться выполнять слишком много операций одновременно. Стеки TCP на современных серверах допускают достаточно большую очередь запросов SYN, поэтому очередь не будет заметна.
Это мнение основано на ограниченных данных, которые вы показали: Ваше узкое место не MySQL.