|
Задавайте вопросы, мы ответим
Вы не зашли.
Всем форумчанам доброго вермени суток!
Как из названия темы можете догодаться, у меня СУБД в режиме простоя загружает очень ЦПУ.
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)
Неактивен
