ktakada42
commented
2 years ago Open ktakada42 opened 2 years ago
ktakada42
commented
2 years ago ktakada42
commented
2 years ago Reading from STDIN ...
# 590ms user time, 50ms system time, 1.00k rss, 34.00k vsz
# Current date: Sun Apr 17 03:05:20 2022
# Hostname: b5c9f6830f32
# Files: STDIN
# Overall: 1.61k total, 34 unique, 4.65 QPS, 3.14x concurrency ___________
# Time range: 2022-04-17T02:59:07 to 2022-04-17T03:04:54
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 1091s 2us 108s 676ms 27ms 8s 384us
# Lock time 3ms 0 64us 2us 3us 1us 1us
# Rows sent 2.10k 0 10 1.33 3.89 1.91 0.99
# Rows examine 15.51M 0 976.58k 9.85k 9.83 95.32k 0.99
# Query size 108.93k 8 414 69.16 174.84 52.65 44.60
# Profile
# Rank Query ID Response time Calls R/Call V
# ==== =================================== =============== ===== ======= =
# 1 0xEB5A41DEC047BE593B7F294C6E19DE0D 1032.2593 94.6% 11 93.8418 9.22 SELECT record
# 2 0x5CAEA8A2E94C70CA4D45BDA9D2892655 25.3697 2.3% 1 25.3697 0.00 SELECT record
# MISC 0xMISC 33.3074 3.1% 1601 0.0208 0.0 <32 ITEMS>
# Query 1: 0.07 QPS, 6.18x concurrency, ID 0xEB5A41DEC047BE593B7F294C6E19DE0D at byte 369121
# This item is included in the report because it matches --limit.
# Scores: V/M = 9.22
# Time range: 2022-04-17T03:01:51 to 2022-04-17T03:04:38
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 11
# Exec time 94 1032s 1ms 108s 94s 107s 29s 102s
# Lock time 0 29us 1us 4us 2us 3us 0 1us
# Rows sent 0 15 1 4 1.36 1.96 0.85 0.99
# Rows examine 61 9.54M 6 976.58k 887.80k 961.27k 276.34k 961.27k
# Query size 1 1.31k 120 123 121.91 118.34 0 118.34
# String:
# Databases app
# Hosts 172.18.0.5
# Users backend
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ######
# 10ms
# 100ms
# 1s
# 10s+ ################################################################
# Tables
# SHOW TABLE STATUS FROM `app` LIKE 'record'\G
# SHOW CREATE TABLE `app`.`record`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from record where created_by = 4962 and status = "open" order by updated_at desc, record_id asc limit 10 offset 0\G
# Query 2: 0 QPS, 0x concurrency, ID 0x5CAEA8A2E94C70CA4D45BDA9D2892655 at byte 66718
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2022-04-17T03:02:17
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 1
# Exec time 2 25s 25s 25s 25s 25s 0 25s
# Lock time 0 2us 2us 2us 2us 2us 0 2us
# Rows sent 0 4 4 4 4 4 0 4
# Rows examine 6 976.57k 976.57k 976.57k 976.57k 976.57k 0 976.57k
# Query size 0 173 173 173 173 173 0 173
# String:
# Databases app
# Hosts 172.18.0.5
# Users backend
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s
# 10s+ ################################################################
# Tables
# SHOW TABLE STATUS FROM `app` LIKE 'record'\G
# SHOW CREATE TABLE `app`.`record`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from record where status = "open" and (category_id, application_group) in ( (1, 1501), (2, 1501), (3, 1502) ) order by updated_at desc, record_id limit 10 offset 0\GError report
# occurrences Error
--------------------------------------------------------------------------------------------------------------------------------------------
59 GET /record-views/tomeActive: ReadTimeout(ReadTimeoutError("HTTPSConnectionPool(host='env2-basil.ftt2204.dabaas.net', port=443): Read timed out. (read timeout=50)"))
60 GET /records/[recordId]/comments: ReadTimeout(ReadTimeoutError("HTTPSConnectionPool(host='env2-basil.ftt2204.dabaas.net', port=443): Read timed out. (read timeout=50)"))
59 GET /record-views/allActive: ReadTimeout(ReadTimeoutError("HTTPSConnectionPool(host='env2-basil.ftt2204.dabaas.net', port=443): Read timed out. (read timeout=50)"))
60 GET /records/[recordId]: ReadTimeout(ReadTimeoutError("HTTPSConnectionPool(host='env2-basil.ftt2204.dabaas.net', port=443): Read timed out. (read timeout=50)"))
59 GET /record-views/allClosed: ReadTimeout(ReadTimeoutError("HTTPSConnectionPool(host='env2-basil.ftt2204.dabaas.net', port=443): Read timed out. (read timeout=50)"))
59 GET /record-views/mineActive: ReadTimeout(ReadTimeoutError("HTTPSConnectionPool(host='env2-basil.ftt2204.dabaas.net', port=443): Read timed out. (read timeout=50)"))
53 POST /records: ReadTimeout(ReadTimeoutError("HTTPSConnectionPool(host='env2-basil.ftt2204.dabaas.net', port=443): Read timed out. (read timeout=50)"))
52 POST /files: ReadTimeout(ReadTimeoutError("HTTPSConnectionPool(host='env2-basil.ftt2204.dabaas.net', port=443): Read timed out. (read timeout=50)"))
50 POST /records/[recordId]/comments: ReadTimeout(ReadTimeoutError("HTTPSConnectionPool(host='env2-basil.ftt2204.dabaas.net', port=443): Read timed out. (read timeout=50)"))
11 GET /records/[recordId]/files/[itemId]/thumbnail: ReadTimeout(ReadTimeoutError("HTTPSConnectionPool(host='env2-basil.ftt2204.dabaas.net', port=443): Read timed out. (read timeout=50)"))
10 GET /records/[recordId]/files/[itemId]: ReadTimeout(ReadTimeoutError("HTTPSConnectionPool(host='env2-basil.ftt2204.dabaas.net', port=443): Read timed out. (read timeout=50)"))
9 PUT /records/[recordId]: ReadTimeout(ReadTimeoutError("HTTPSConnectionPool(host='env2-basil.ftt2204.dabaas.net', port=443): Read timed out. (read timeout=50)"))
7 GET /categories: ReadTimeout(ReadTimeoutError("HTTPSConnectionPool(host='env2-basil.ftt2204.dabaas.net', port=443): Read timed out. (read timeout=50)"))
--------------------------------------------------------------------------------------------------------------------------------------------
GET:/categories success:63 subtotal: 45
POST:/files success:12 subtotal: 18
GET:/record-views/allActive success:0 subtotal: 0
GET:/record-views/allClosed success:0 subtotal: 0
GET:/record-views/mineActive success:1 subtotal: 2
GET:/record-views/tomeActive success:0 subtotal: 0
POST:/records success:9 subtotal: 11
GET:/records/[recordId] success:1 subtotal: 1
PUT:/records/[recordId] success:62 subtotal: 50
GET:/records/[recordId]/comments success:0 subtotal: 0
POST:/records/[recordId]/comments success:15 subtotal: 15
GET:/records/[recordId]/files/[itemId] success:59 subtotal: 59
GET:/records/[recordId]/files/[itemId]/thumbnail success:58 subtotal: 58
===============================================
スコアは 259 点
===============================================Reading from STDIN ...
# 1s user time, 50ms system time, 1.00k rss, 34.00k vsz
# Current date: Sun Apr 17 03:15:16 2022
# Hostname: 5483b8665970
# Files: STDIN
# Overall: 4.32k total, 34 unique, 11.26 QPS, 4.48x concurrency __________
# Time range: 2022-04-17T03:08:52 to 2022-04-17T03:15:16
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 1719s 2us 14s 398ms 105ms 2s 424us
# Lock time 10ms 0 2ms 2us 3us 23us 1us
# Rows sent 6.73k 0 10 1.59 9.83 2.39 0.99
# Rows examine 200.29M 0 976.65k 47.44k 9.83 206.66k 0.99
# Query size 338.98k 8 414 80.29 183.58 62.46 44.60
# Profile
# Rank Query ID Response time Calls R/Call V/
# ==== =================================== =============== ===== ====== ==
# 1 0xFE1B4AC9EFFC83215943A13BF37CF837 1504.3062 87.5% 175 8.5960 0.51 SELECT record
# 2 0x558167E9A6843F0636E81318073DB5D5 187.5101 10.9% 30 6.2503 0.59 SELECT record
# MISC 0xMISC 27.4549 1.6% 4118 0.0067 0.0 <32 ITEMS>
# Query 1: 0.80 QPS, 6.87x concurrency, ID 0xFE1B4AC9EFFC83215943A13BF37CF837 at byte 211021
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.51
# Time range: 2022-04-17T03:11:37 to 2022-04-17T03:15:16
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 4 175
# Exec time 87 1504s 986ms 14s 9s 12s 2s 8s
# Lock time 3 351us 1us 5us 2us 3us 0 1us
# Rows sent 25 1.68k 1 10 9.86 9.83 1.07 9.83
# Rows examine 83 166.90M 976.57k 976.65k 976.62k 961.27k 0 961.27k
# Query size 5 17.26k 99 102 100.98 97.36 0.00 97.36
# String:
# Databases app
# Hosts 172.19.0.4
# Users backend
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms #
# 1s ################################################################
# 10s+ #######################
# Tables
# SHOW TABLE STATUS FROM `app` LIKE 'record'\G
# SHOW CREATE TABLE `app`.`record`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from record where status = "closed" order by updated_at desc, record_id asc limit 10 offset 0\G
# Query 2: 0.18 QPS, 1.12x concurrency, ID 0x558167E9A6843F0636E81318073DB5D5 at byte 841071
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.59
# Time range: 2022-04-17T03:11:47 to 2022-04-17T03:14:34
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 30
# Exec time 10 188s 1s 9s 6s 8s 2s 7s
# Lock time 0 55us 1us 4us 1us 1us 0 1us
# Rows sent 4 300 10 10 10 10 0 10
# Rows examine 14 28.61M 976.58k 976.64k 976.61k 961.27k 0 961.27k
# Query size 1 5.54k 185 191 189.23 183.58 0 183.58
# String:
# Databases app
# Hosts 172.19.0.4
# Users backend
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `app` LIKE 'record'\G
# SHOW CREATE TABLE `app`.`record`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from record where status = "open" and (category_id, application_group) in ( (4, 116), (4, 117), (4, 118), (4, 119), (4, 120) ) order by updated_at desc, record_id limit 10 offset 0\G
kohkubo
commented
2 years ago root@4fb68c76207a:/# cd MySQLTuner-perl-master
root@4fb68c76207a:/MySQLTuner-perl-master# ./mysqltuner.pl --user root
>> MySQLTuner 1.9.8
* 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 passed on the command line
[OK] Currently running supported MySQL version 8.0.28
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[!!] log_error is set to stderr MT can't read stderr
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 3.0G (Tables: 12)
[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 ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 8m 29s (18K q [37.094 qps], 25 conn, TX: 6M, RX: 1M)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 7.8G
[--] Max MySQL memory : 9.9G
[--] Other process memory: 0B
[--] Total buffers: 168.0M global + 65.9M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 892.6M (11.22% of installed RAM)
[!!] Maximum possible memory usage: 9.9G (127.17% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[!!] Slow queries: 99% (18K/18K)
[OK] Highest usage of available connections: 7% (11/151)
[OK] Aborted connections: 0.00% (0/25)
[--] Query cache have been removed in MySQL 8
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 445 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 4 total)
[OK] Thread cache hit rate: 56% (11 created / 25 connections)
[OK] Table cache hit rate: 98% (22K hits / 23K requests)
[OK] table_definition_cache(2000) is upper than number of tables(338)
[OK] Open file limit used: 0% (3/1M)
[OK] Table locks acquired immediately: 100% (4 immediate / 4 locks)
[OK] Binlog cache memory access: 100.00% (706 Memory / 706 Total)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] 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
[!!] InnoDB buffer pool / data size: 128.0M/3.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] InnoDB Read buffer efficiency: 65.84% (29512441 hits/ 44824647 total)
[!!] InnoDB Write Log efficiency: 74.94% (31731 hits/ 42343 total)
[OK] InnoDB log waits: 0.00% (0 waits / 10612 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.
-------- 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:
MySQL was started within the last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Buffer Key MyISAM set to 0, no MyISAM table detected
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 ***
key_buffer_size=0
innodb_buffer_pool_size (>= 3.0G) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
root@4fb68c76207a:/MySQLTuner-perl-master#