Как снизить нагрузку на MySQL?

Здравствуйте,

есть сервер статистики веб приложения, на нем крутится nginx, php-fpm, memcached, mysql.
Все работает стабильно, но mysql по ТОПу почти постоянно 112-118% загрузки LA в районе 15-30 в зависимости от нагрузок.

Сервер 8 ядер по 2,4Ghz, 2Gb RAM

запросов на nginx в районе 2-3 тысяч в секунду

нагрузка на базу:
mysqladmin status -u root -p
Uptime: 1306437  Threads: 5  Questions: 1826124730  Slow queries: 0  Opens: 3274  Flush tables: 1  Open tables: 1900  Queries per second avg: 1397.790


Версия mysql:
mysql  Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using  EditLine wrapper


Cама база очень маленькая, около 75мб

Конфиг mysql:
cat /etc/mysql/my.cnf
[client]
port                        = 3306
socket                      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket                      = /var/run/mysqld/mysqld.sock
nice                        = 0
[mysqld]
user                        = mysql
pid-file                    = /var/run/mysqld/mysqld.pid
socket                      = /var/run/mysqld/mysqld.sock
port                        = 3306
basedir                     = /usr
datadir                     = /var/lib/mysql
tmpdir                      = /tmp
language                    = /usr/share/mysql/english
old_passwords               = 0
bind-address                = 127.0.0.1

skip-external-locking
skip-name-resolve	    = 1

max_allowed_packet          = 16M
key_buffer_size             = 16M
innodb_buffer_pool_size     = 1024M
innodb_file_per_table       = 1
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit  = 0
innodb_log_file_size        = 128M
innodb_buffer_pool_instances =1

max_connections             = 136

query_cache_size            = 0
slow_query_log              = /var/log/mysql/mysql-slow.log
long_query_time             = 1
expire_logs_days            = 10
max_binlog_size             = 100M

[mysqldump]
quick
quote-names
max_allowed_packet          = 16M


Вывод mysql-tuner:
>>  MySQLTuner 1.7.9 - 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] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.7.24-0ubuntu0.18.04.1
[OK] Operating on 64-bit architecture
 
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/ff.err(0B)
[OK] Log file /var/lib/mysql/ff.err exists
[OK] Log file /var/lib/mysql/ff.err is readable.
[!!] Log file /var/lib/mysql/ff.err is empty
[OK] Log file /var/lib/mysql/ff.err is smaller than 32 Mb
[OK] /var/lib/mysql/ff.err doesn't contain any warning.
[OK] /var/lib/mysql/ff.err doesn't contain any error.
[--] 0 start(s) detected in /var/lib/mysql/ff.err
[--] 0 shutdown(s) detected in /var/lib/mysql/ff.err
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in InnoDB tables: 75.0M (Tables: 173)
[OK] Total fragmented tables: 0
 
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'debian-sys-maint@localhost' has no password set.
[--] Bug #80860 MySQL 5.7: Avoid testing password when validate_password is activated
 
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 15d 2h 57m 16s (1B q [1K qps], 96M conn, TX: 1545G, RX: 215G)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory     : 1.9G
[--] Max MySQL memory    : 1.2G
[--] Other process memory: 553.8M
[--] Total buffers: 1.0G global + 1.1M per thread (136 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.1G (55.26% of installed RAM)
[OK] Maximum possible memory usage: 1.2G (61.47% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/1B)
[OK] Highest usage of available connections: 19% (26/136)
[OK] Aborted connections: 0.00%  (29/96128595)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (562 temp sorts / 335M sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (36 on disk / 344K total)
[OK] Thread cache hit rate: 96% (3M created / 96M connections)
[OK] Table cache hit rate: 58% (1K open / 3K opened)
[OK] Open file limit used: 0% (32/5K)
[OK] Table locks acquired immediately: 100% (148 immediate / 148 locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/43.0K
[!!] Read Key buffer hit rate: 92.2% (90 cached / 7 reads)
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 1.0G/75.0M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 128.0M * 2/1.0G should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 8 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (15943986118 hits/ 15943992341 total)
[!!] InnoDB Write Log efficiency: 87.68% (10367439 hits/ 11824430 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1456991 writes)


Что можно подкрутить не трогая железо? или это упор и надо менять железо?
  • Вопрос задан
  • 1391 просмотр
Пригласить эксперта
Ответы на вопрос 3
@stratosmi
Начать с профилирования - понять какие именно запросы нагружают и почему они происходят.
Возможно, у вас это https://habr.com/post/113298/
Ответ написан
Shutik
@Shutik
Погромист халявщик
Как снизить нагрузку на MySQL?

Попробуйте multiple insert c задержкой. Например - накапливаете статистику в redis, потом раз в N сек. одним запросом вставляете в бд накопившиеся записи. Ну и ssd конечно.
Ответ написан
@MechanID
Админ хостинг провайдера
Исходя из того что у вас Reads / Writes: 99% / 1%
то стоит попробывать включить Query cache, если запросы на чтение повторяющиеся то вам это поможет.
все остальное из MySQLTuner выглядит нормально
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через TM ID
Похожие вопросы