SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 26.10.2019 16:46:09

unoc_u34
Участник
Зарегистрирован: 26.10.2019
Сообщений: 1

MySQL загружает ЦПУ за 100 процентов

Всем форумчанам доброго вермени суток!
Как из названия темы можете догодаться, у меня СУБД в режиме простоя загружает очень ЦПУ.

PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
4268 mysql     20   0 3886384 665264  31164 S 143.8 17.6  28:05.54 mysqld


Типы таблиц, которые использую: InnoDB
Конфиг MySQL собственно:

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#Logging
slow-query-log=1
slow-query-log-file=/var/log/slow-log
#MysqlTuner
skip-name-resolve=1
innodb_buffer_pool_size=1024M
innodb_log_file_size=128M
innodb_log_buffer_size=32M
innodb_flush_log_at_trx_commit=1
innodb_buffer_pool_instances=1
join_buffer_size=12M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=2M


Вывод: SHOW STATUS LIKE 'Handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 7     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 122   |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 2     |
| Handler_read_key           | 166   |
| Handler_read_last          | 0     |
| Handler_read_next          | 197   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 2328  |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 1162  |
+----------------------------+-------+
18 rows in set (0.00 sec)

Вывод SHOW PROCESSLIST;
+------+-----------------+-----------+---------+---------+------+------------------------+------------------+
| Id   | User            | Host      | db      | Command | Time | State                  | Info             |
+------+-----------------+-----------+---------+---------+------+------------------------+------------------+
|    4 | event_scheduler | localhost | NULL    | Daemon  | 1479 | Waiting on empty queue | NULL             |
|    9 | playsms         | localhost | playsms | Sleep   | 1472 |                        | NULL             |
|   10 | playsms         | localhost | playsms | Sleep   | 1472 |                        | NULL             |
|   11 | playsms         | localhost | playsms | Sleep   | 1472 |                        | NULL             |
|   12 | playsms         | localhost | playsms | Sleep   | 1472 |                        | NULL             |
|   15 | playsms         | localhost | playsms | Sleep   | 1471 |                        | NULL             |
| 5871 | root            | localhost | NULL    | Query   |    0 | starting               | SHOW PROCESSLIST |
| 5910 | playsms         | localhost | playsms | Sleep   |    0 |                        | NULL             |
| 5912 | playsms         | localhost | playsms | Sleep   |    1 |                        | NULL             |
| 5913 | playsms         | localhost | playsms | Sleep   |    1 |                        | NULL             |
| 5914 | playsms         | localhost | playsms | Sleep   |    1 |                        | NULL             |
| 5915 | playsms         | localhost | playsms | Sleep   |    0 |                        | NULL             |
+------+-----------------+-----------+---------+---------+------+------------------------+------------------+
12 rows in set (0.00 sec)

Вывод mysqltuner.pl
>>  MySQLTuner 1.7.19 - 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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 8.0.18
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysqld.log exists
[--] Log file: /var/log/mysqld.log(483K)
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 1491 warning(s).
[!!] /var/log/mysqld.log contains 812 error(s).
[--] 349 start(s) detected in /var/log/mysqld.log
[--] 1) 2019-10-26T14:08:06.374778Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
[--] 2) 2019-10-26T14:08:06.201549Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.18'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
[--] 3) 2019-10-26T13:52:29.471068Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
[--] 4) 2019-10-26T13:52:29.319109Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.18'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
[--] 5) 2019-10-26T13:19:12.933808Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
[--] 6) 2019-10-26T13:19:12.785938Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.18'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
[--] 7) 2019-10-26T13:01:15.200287Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
[--] 8) 2019-10-26T13:01:15.047859Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.18'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
[--] 9) 2019-10-26T12:59:36.179767Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
[--] 10) 2019-10-26T12:59:36.040200Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.18'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
[--] 207 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2019-10-26T14:07:49.546150Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.
[--] 2) 2019-10-26T14:04:55.161900Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.
[--] 3) 2019-10-26T14:00:52.378120Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.
[--] 4) 2019-10-26T14:00:40.831491Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.
[--] 5) 2019-10-26T14:00:37.921574Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.
[--] 6) 2019-10-26T13:52:27.017004Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.
[--] 7) 2019-10-26T13:19:09.867580Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.
[--] 8) 2019-10-26T13:01:10.940697Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.
[--] 9) 2019-10-25T15:27:29.899877Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.
[--] 10) 2019-10-25T15:25:51.827325Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.18)  MySQL Community Server - GPL.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 100.7M (Tables: 66)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3m 16s (19K q [100.648 qps], 771 conn, TX: 16M, RX: 928K)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 3.6G
[--] Max MySQL memory    : 3.0G
[--] Other process memory: 0B
[--] Total buffers: 1.1G global + 12.9M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.2G (33.53% of installed RAM)
[OK] Maximum possible memory usage: 3.0G (82.22% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/19K)
[OK] Highest usage of available connections: 7% (12/151)
[OK] Aborted connections: 0.26%  (2/771)
[--] Query cache have been removed in MySQL 8
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8 sorts)
[!!] Joins performed without indexes: 18
[OK] Temporary tables created on disk: 0% (0 on disk / 15 total)
[OK] Thread cache hit rate: 98% (12 created / 771 connections)
[OK] Table cache hit rate: 89% (651 open / 731 opened)
[OK] table_definition_cache(2000) is upper than number of tables(370)
[OK] Open file limit used: 0% (2/10K)
[OK] Table locks acquired immediately: 100% (8 immediate / 8 locks)
[OK] Binlog cache memory access: 100.00% (108 Memory / 108 Total)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] MyISAM Metrics are disabled on last MySQL versions.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 1.0G/100.7M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 128.0M * 2/1.0G should be equal to 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% (214985963 hits/ 214988103 total)
[!!] InnoDB Write Log efficiency: 66.71% (934 hits/ 1400 total)
[OK] InnoDB log waits: 0.00% (0 waits / 466 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- 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: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mysqld.log file
    Control error line(s) into /var/log/mysqld.log file
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/ … -size.html
             (specially the conclusions at the bottom of the page).
Variables to adjust:
    join_buffer_size (> 12.0M, or always use indexes with JOINs)



Юзер я не особо продвинутый, следовательно очень нуждаюсь в помощи.
Пожалуйста, помогите, если сталкивались с такой проблемой.
За ранее спасибо Всем за выделенное время.

Отредактированно unoc_u34 (26.10.2019 17:15:40)

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson