我面临着一个高mysql负载的问题,特别是整个CPU都被消耗了。我们有 16 核 CPU。运行一个作业,它将某些数据以 csv 格式导入数据库。当这启动时,CPU会变高,mysql被挂起。下面
INNODB ENGINE STATUS
mysql> show engine innodb statusG;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
180614 21:27:28 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 27 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1113759 1_second, 1113758 sleeps, 111273 10_second, 1177 background, 1177 flush
srv_master_thread log flush and writes: 1120485
----------
SpgaPHORES
----------
OS WAIT ARRAY INFO: reservation count 6324138, signal count 163497140
Mutex spin waits 3563712357, rounds 2684759736, OS waits 3547098
RW-shared spins 28930883, rounds 72633117, OS waits 977277
RW-excl spins 8651762, rounds 274697595, OS waits 1300580
Spin rounds per wait: 0.75 mutex, 2.51 RW-shared, 31.75 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
180609 5:00:01
*** (1) TRANSACTION:
TRANSACTION 192A64A2D3, ACTIVE 1 sec starting index read
UPDATE products SET hjmpTS = 80060 ,modifiedTS='2018-06-09 05:00:01' WHERE GK = 8816144056321
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 971922 page no 31354 n bits 136 index `PRIMARY` of table `pgagmcb_prd`.`products` trx id 192A64ACB2 lock_mode X locks rec but not gap
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 19C4CE52B2
Purge done for trx's n:o < 19C4CE1594 undo n:o < 0
History list length 821
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 392730, OS thread handle 0x7f09af186700, query id 5877276140 localhost root
show engine innodb status
---TRANSACTION 19C4CE4A6D, not started
MySQL thread id 392695, OS thread handle 0x7f09c5bef700, query id 5877273919 prgkptomcat02 118.21.1.11 pgag_oauth_prd
---TRANSACTION 19C4CA0181, not started
MySQL thread id 392677, OS thread handle 0x7f09c8a5d700, query id 5876980085 prgkptomcat04 118.21.1.13 pgag_oauth_prd
---TRANSACTION 19C4CE52B1, not started
MySQL thread id 392667, OS thread handle 0x7f09aebf0700, query id 5877276137 prgkptomcat04 118.21.1.13 pgagmcb_prd
---TRANSACTION 19C4CDC68D, not started
MySQL thread id 392642, OS thread handle 0x7f11a4161700, query id 5877238586 118.21.1.14 pgagmcb_prd
---TRANSACTION 19C4CDBF35, not started
MySQL thread id 392641, OS thread handle 0x7f09af0c3700, query id 5877236562 118.21.1.14 pgagmcb_prd
---TRANSACTION 19C4C412C3, not started
MySQL thread id 392586, OS thread handle 0x7f09c5a28700, query id 5876568407 prgkptomcat02 118.21.1.11 pgagmcb_prd
---TRANSACTION 19C4C70266, not started
MySQL thread id 392584, OS thread handle 0x7f09ae966700, query id 5876773008 prgkptomcat02 118.21.1.11 pgagmcb_prd
---TRANSACTION 19C4CAFCC7, not started
MySQL thread id 392525, OS thread handle 0x7f09c43d0700, query id 5877046769 prgkptomcat02 118.21.1.11 pgag_oauth_prd
---TRANSACTION 19C4CAFD1F, not started
MySQL thread id 392523, OS thread handle 0x7f09c8daa700, query id 5877046862 prgkptomcat02 118.21.1.11 pgag_oauth_prd
---TRANSACTION 19C4CAFD07, not started
MySQL thread id 392524, OS thread handle 0x7f09c5820700, query id 5877046837 prgkptomcat02 118.21.1.11 pgag_oauth_prd
---TRANSACTION 19C4CDE3C3, not started
MySQL thread id 392490, OS thread handle 0x7f09c5d75700, query id 5877246447 118.21.1.14 pgag_oauth_prd
---TRANSACTION 19C4CDCB36, not started
MySQL thread id 392485, OS thread handle 0x7f09aefbf700, query id 5877239893 prgkptomcat03 118.21.1.12 pgagmcb_prd
---TRANSACTION 19C4CE52A3, not started
MySQL thread id 392451, OS thread handle 0x7f09af71c700, query id 5877276127 prgkptomcat04 118.21.1.13 pgagmcb_prd
---TRANSACTION 19C4C92CF9, not started
MySQL thread id 392449, OS thread handle 0x7f09c5f7d700, query id 5876922937 prgkptomcat04 118.21.1.13 pgagmcb_prd
---TRANSACTION 19C4CE52A6, not started
MySQL thread id 392442, OS thread handle 0x7f09af38e700, query id 5877276129 prgkptomcat04 118.21.1.13 pgagmcb_prd
---TRANSACTION 19C4CE4F03, not started
MySQL thread id 392421, OS thread handle 0x7f09af30c700, query id 5877275162 prgkptomcat02 118.21.1.11 pgagmcb_prd
---TRANSACTION 19C4C9B259, not started
MySQL thread id 392415, OS thread handle 0x7f09c4597700, query id 5876958837 prgkptomcat02 118.21.1.11 pgagmcb_prd
---TRANSACTION 19C4CAE196, not started
MySQL thread id 392402, OS thread handle 0x7f09c8c65700, query id 5877039508 prgkptomcat01 118.21.1.10 pgagmcb_prd
---TRANSACTION 19C4CE52A1, not started
MySQL thread id 392383, OS thread handle 0x7f09aeaab700, query id 5877276126 prgkptomcat04 118.21.1.13 pgagmcb_prd
---TRANSACTION 19C4CE52AA, not started
MySQL thread id 392382, OS thread handle 0x7f09c4c72700, query id 5877276132 prgkptomcat04 118.21.1.13 pgagmcb_prd
---TRANSACTION 19C4CDBDE9, not started
MySQL thread id 392352, OS thread handle 0x7f09c5082700, query id 5877236203 118.21.1.14 pgagmcb_prd
---TRANSACTION 19C4CE5279, not started
MySQL thread id 392350, OS thread handle 0x7f09c4db7700, query id 5877276084 118.21.1.14 pgagmcb_prd
---TRANSACTION 19C4CDEAEF, not started
MySQL thread id 392323, OS thread handle 0x7f09c48e4700, query id 5877248380 118.21.1.14 pgagmcb_prd
---TRANSACTION 19C4CE52A5, not started
MySQL thread id 392322, OS thread handle 0x7f09c56db700, query id 5877276112 118.21.1.14 pgagmcb_prd
---TRANSACTION 19C4CE4BD9, not started
MySQL thread id 392319, OS thread handle 0x7f09c6523700, query id 5877274310 prgkptomcat02 118.21.1.11 pgagmcb_prd
---TRANSACTION 19C4CC77FB, not started
MySQL thread id 392297, OS thread handle 0x7f09c538e700, query id 5877148868 prgkptomcat02 118.21.1.11 pgagmcb_prd
---TRANSACTION 19C4C70074, not started
MySQL thread id 392296, OS thread handle 0x7f09c8a9e700, query id 5876772482 prgkptomcat02 118.21.1.11 pgagmcb_prd
---TRANSACTION 19C4CD042D, not started
MySQL thread id 392295, OS thread handle 0x7f09ae5d8700, query id 5877186230 prgkptomcat02 118.21.1.11 pgagmcb_prd
---TRANSACTION 19C4CE2C0A, not started
MySQL thread id 392293, OS thread handle 0x7f09c5aeb700, query id 5877265779 118.21.1.14 pgagmcb_prd
---TRANSACTION 19C4CE3571, not started
MySQL thread id 392283, OS thread handle 0x7f09c6627700, query id 5877268313 prgkptomcat04 118.21.1.13 pgagmcb_prd
---TRANSACTION 19C4CC96A6, not started
MySQL thread id 392282, OS thread handle 0x7f09c40c4700, query id 5877156935 prgkptomcat04 118.21.1.13 pgagmcb_prd
---TRANSACTION 19C4C930C7, not started
MySQL thread id 392281, OS thread handle 0x7f09c8e2c700, query id 5876923943 prgkptomcat04 118.21.1.13 pgagmcb_prd
---TRANSACTION 19C4C6F0A5, not started
MySQL thread id 392278, OS thread handle 0x7f09c569a700, query id 5876768228 prgkptomcat02 118.21.1.11 pgagmcb_prd
---TRANSACTION 19C4CC399F, not started
MySQL thread id 392276, OS thread handle 0x7f09c471d700, query id 5877132036 prgkptomcat02 118.21.1.11 pgagmcb_prd
---TRANSACTION 19C4CE52A0, not started
MySQL thread id 392234, OS thread handle 0x7f09c5249700, query id 5877276138 118.21.1.14 pgagmcb_prd Opening tables
SELECT item_t0.GK FROM cartentries item_t0 WHERE ( item_t0.p_order =8995720658987) AND (item_t0.TypeGKString=8796094496850 ) order by item_t0.EntryNumber ASC, item_t0.createdTS ASC
---TRANSACTION 19C4CE369D, not started
MySQL thread id 392232, OS thread handle 0x7f09c8be3700, query id 5877268619 prgkptomcat04 118.21.1.13 pgagmcb_prd
---TRANSACTION 19C4CAE1B0, not started
MySQL thread id 392136, OS thread handle 0x7f09c4d76700, query id 5877039534 prgkptomcat01 118.21.1.10 pgagmcb_prd
---TRANSACTION 19C4CAE1AA, not started
MySQL thread id 392135, OS thread handle 0x7f09c4fbf700, query id 5877039527 prgkptomcat01 118.21.1.10 pgagmcb_prd
---TRANSACTION 19C4CE5272, not started
MySQL thread id 392114, OS thread handle 0x7f09c50c3700, query id 5877276077 prgkptomcat01 118.21.1.10 pgagmcb_prd
---TRANSACTION 19C4CE32D8, not started
MySQL thread id 392027, OS thread handle 0x7f11a409e700, query id 5877267604 prgkptomcat03 118.21.1.12 pgagmcb_prd
---TRANSACTION 19C4CDBDEE, not started
MySQL thread id 391944, OS thread handle 0x7f11a4224700, query id 5877236208 118.21.1.14 pgagmcb_prd
---TRANSACTION 19C4CA0332, not started
MySQL thread id 391874, OS thread handle 0x7f09c5aaa700, query id 5876980541 prgkptomcat04 118.21.1.13 pgag_oauth_prd
---TRANSACTION 19C4CE50B6, not started
MySQL thread id 391814, OS thread handle 0x7f09c5eba700, query id 5877275617 prgkptomcat02 118.21.1.11 pgagmcb_prd
---TRANSACTION 19C4CE1588, not started
MySQL thread id 391776, OS thread handle 0x7f09c5d34700, query id 5877259853 118.21.1.14 pgagmcb_prd
---TRANSACTION 19C4CE52AF, not started
mysql tables in use 1, locked 1
MySQL thread id 391692, OS thread handle 0x7f11a40df700, query id 5877276141 118.21.1.14 pgagmcb_prd init
UPDATE cronjobs SET hjmpTS = 8629763 ,modifiedTS='2018-06-14 21:27:28',p_endtime='2018-06-14 21:27:28' WHERE GK = 8811756159477
Trx read view will not see trx with id >= 19C4CE527F, sees < 19C4CE4C4D
---TRANSACTION 19C4CE524E, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 0
MySQL thread id 392064, OS thread handle 0x7f09afb2c700, query id 5877276038 prgkptomcat01 118.21.1.10 pgagmcb_prd Sending data
MySQL thread id 392062, OS thread handle 0x7f09c55d7700, query id 5877275109 prgkptomcat01 118.21.1.10 pgagmcb_prd Sending data
SELECT item_t0.GK FROM addresses item_t0 WHERE ( item_t0.p_pkid ='29001499' AND item_t0.p_billingaddress =0 AND item_t0.p_shippingaddress =1) AND (item_t0.TypeGKString=8796094103634 ) UNION ALL SELECT item_t0.GK FROM pointofserviceaddress item_t0 WHERE ( item_t0.p_pkid ='29001499' AND item_t0.p_billingaddress =0 AND item_t0.p_shippingaddress =1) AND (item_t0.TypeGKString=8797338533970 )
Trx read view will not see trx with id >= 19C4CE4ECF, sees < 19C4CE4A1C
---TRANSACTION 19C4CE4EC4, ACTIVE 0 sec
mysql tables in use 2, locked 0
MySQL thread id 392067, OS thread handle 0x7f09affff700, query id 5877275099 prgkptomcat01 118.21.1.10 pgagmcb_prd Sending data
h id >= 19C4CE4C62, sees < 19C4CE48E5
---TRANSACTION 19C4CE4C4D, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 0
MySQL thread id 392059, OS thread handle 0x7f09c430d700, query id 5877274440 prgkptomcat01 118.21.1.10 emagmcb_prd Sending data
SELECT item_t0.GK FROM addresses item_t0 WHERE ( item_t0.p_pkid ='20087352' AND item_t0.p_billingaddress =0 AND item_t0.p_shippingaddress =1) AND (item_t0.TypeGKString=8796094103634 ) UNION ALL SELECT item_t0.GK FROM pointofserviceaddress item_t0 WHERE ( item_t0.p_pkid ='20087352' AND item_t0.p_billingaddress =0 AND item_t0.p_shippingaddress =1) AND (item_t0.TypeGKString=8797338533970 )
Trx read view will not see trx with id >= 19C4CE4C4E, sees < 19C4CE48E5
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
689458 OS file reads, 21884164 OS file writes, 5163807 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 22.07 writes/s, 5.22 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 156723, seg size 156725, 39554 merges
merged operations:
insert 106864, delete mark 147895, delete 47175
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 63749393, node heap has 66107 buffer(s)
11918.00 hash searches/s, 20228.44 non-hash searches/s
---
LOG
---
Log sequence number 3872974342573
Log flushed up to 3872974307599
Last checkpoint at 3872972346157
0 pending log writes, 0 pending chkp writes
1520749 log i/o's done, 1.33 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 32967229440; in additional pool allocated 0
Dictionary memory allocated 5343676
Buffer pool size 1966080
Free buffers 901470
Database pages 998503
Old database pages 368568
Modified db pages 1090
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4341, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 889753, created 108750, written 32815581
0.00 reads/s, 0.15 creates/s, 30.44 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 998503, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
22 read views open inside InnoDB
Main thread process no. 11805, id 139679969232640, state: sleeping
Number of rows inserted 4605702, updated 110022489, deleted 3897802, read 309526373829
0.81 inserts/s, 49.85 updates/s, 0.56 deletes/s, 1280085.96 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
[client]
port = 3306
socket = /AB/mysql/logs/mysql.sock
[mysqld]
port = 3306
bind-address = 0.0.0.0
socket = /AB/mysql/logs/mysql.sock
basedir = /AB/mysql
user = mysql
tmpdir = /AB/mysql_temp_files
character-set-server = utf8
datadir = /AB/mysql/data
default-storage-engine = InnoDB
expire_logs_days = 10
general_log = 0
general_log_file = /AB/mysql/logs/general-log.log
innodb_buffer_pool_size = 30G
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
key_buffer_size = 16M
log_error = /AB/mysql/logs/mysqld-error.log
log_queries_not_using_indexes = 1
long_query_time = 5
log-bin = /AB/mysql/binlog/mysql-bin
max_allowed_packet = 35M
max_binlog_size = 100M
max_connect_errors = 100000
max_connections = 910
myisam_recover = BACKUP
query_cache_limit = 2M
query_cache_size = 0
query_cache_type = 0
skip-external-locking
slow_query_log = 0
slow_query_log_file = /AB/mysql/logs/mysqld-slow-queries.log
table_open_cache = 256
thread_cache_size = 8
thread_stack = 256K
transaction-isolation = READ-COMMITTED
############################
# MySql replication - Master
############################
server_id = 1
binlog_format = row
binlog-ignore-db=mysql # input the database that should be ignored for replication
binlog-ignore-db=performance_schema
binlog-ignore-db=pkgmcb_eldas
replicate-wild-ignore-table=mysql.% #disable slave replication for mysql db
relay-log = /AB/mysql/mysql-relay-bin
relay-log-index = /AB/mysql/mysql-relay-bin.index
master-info-file = /AB/mysql/mysql-master.info
relay-log-info-file = /AB/mysql/mysql-relay-log-info
[mysqld_safe]
log-error = /AB/mysql/logs/mysqld.log
socket = /AB/mysql/logs/mysql.sock
pid-file = /AB/mysql/logs/mysqld.pid
[mysqldump]
max_allowed_packet = 16M
quick
[mysql]
no_auto_rehash
mysql> show global status;
+------------------------------------------+---------------+
| Variable_name | Value |
+------------------------------------------+---------------+
| Aborted_clients | 13 |
| Aborted_connects | 3753 |
| Binlog_cache_disk_use | 32815 |
| Binlog_cache_use | 92017182 |
| Bytes_received | 1617086123131 |
| Bytes_sent | 2825568326969 |
| Com_admin_commands | 37462 |
| Com_change_db | 2 |
| Com_commit | 128435552 |
| Com_create_table | 18730 |
| Com_delete | 15234374 |
| Com_delete_multi | 37450 |
| Com_drop_table | 18725 |
| Com_insert | 4603989 |
| Com_insert_select | 18725 |
| Com_purge_before_date | 13 |
| Com_rollback | 15159 |
| Com_select | 5360296742 |
| Com_set_option | 257428949 |
| Com_show_charsets | 4 |
| Com_show_collations | 6371 |
| Com_show_engine_status | 4870 |
| Com_show_processlist | 1187 |
| Com_show_status | 95630 |
| Com_show_tables | 21 |
| Com_show_triggers | 0 |
| Com_show_variables | 21400 |
| Com_update | 110599528 |
| Compression | OFF |
| Connections | 392731 |
| Created_tmp_disk_tables | 77030 |
| Created_tmp_files | 445171 |
| Created_tmp_tables | 665507495 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 5774785433 |
| Handler_delete | 3897802 |
| Handler_discover | 0 |
| Handler_prepare | 402020246 |
| Handler_read_first | 161347921 |
| Handler_read_key | 32213287627 |
| Handler_read_last | 2 |
| Handler_read_next | 47210732684 |
| Handler_read_prev | 32726 |
| Handler_read_rnd | 215354676 |
| Handler_read_rnd_next | 482467384071 |
| Handler_rollback | 170 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 221011669 |
| Handler_write | 21797614285 |
| Innodb_buffer_pool_pages_data | 998503 |
| Innodb_buffer_pool_bytes_data | 16359473152 |
| Innodb_buffer_pool_pages_dirty | 1029 |
| Innodb_buffer_pool_bytes_dirty | 16859136 |
| Innodb_buffer_pool_pages_flushed | 32815553 |
| Innodb_buffer_pool_pages_free | 901470 |
| Innodb_buffer_pool_pages_misc | 66107 |
| Innodb_buffer_pool_pages_total | 1966080 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 207758 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 390826699904 |
| Innodb_buffer_pool_reads | 645677 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 887071687 |
| Innodb_data_fsyncs | 5163787 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 14588825600 |
| Innodb_data_reads | 689458 |
| Innodb_data_writes | 21884132 |
| Innodb_data_written | 1155002425856 |
| Innodb_dblwr_pages_written | 32815553 |
| Innodb_dblwr_writes | 918315 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 234974909 |
| Innodb_log_writes | 1390975 |
| Innodb_os_log_fsyncs | 1505727 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 79636049408 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 108750 |
| Innodb_pages_read | 889753 |
| Innodb_pages_written | 32815553 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 195660 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 2243 |
| Innodb_row_lock_waits | 1286068 |
| Innodb_rows_deleted | 3897802 |
| Innodb_rows_inserted | 4605700 |
| Innodb_rows_read | 309520074518 |
| Innodb_rows_updated | 110022225 |
| Innodb_truncated_status_writes | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 13396 |
| Key_blocks_used | 5921 |
| Key_read_requests | 1782635934 |
| Key_reads | 0 |
| Key_write_requests | 292962271 |
| Key_writes | 0 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 266 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 5 |
| Open_streams | 0 |
| Open_table_definitions | 400 |
| Open_tables | 256 |
| Opened_files | 959559 |
| Opened_table_definitions | 43623 |
| Opened_tables | 21935444 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Queries | 5877263773 |
| Questions | 5877218009 |
| Rpl_status | AUTH_MASTER |
| Select_full_join | 1085194 |
| Select_full_range_join | 0 |
| Select_range | 106321329 |
| Select_range_check | 0 |
| Select_scan | 491596929 |
| Slave_heartbeat_period | 0.000 |
| Slave_open_temp_tables | 0 |
| Slave_received_heartbeats | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 407546261 |
| Sort_merge_passes | 631000 |
| Sort_range | 4293576025 |
| Sort_rows | 17877617809 |
| Sort_scan | 13049200 |
| Table_locks_immediate | 7035027122 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 7 |
| Threads_cached | 5 |
| Threads_connected | 102 |
| Threads_created | 7052 |
| Threads_running | 21 |
| Uptime | 1123494 |
| Uptime_since_flush_status | 1123494 |
+------------------------------------------+---------------+
289 rows in set (0.00 sec)
任何帮助真的非常感谢。谢谢
全局状态和变量分析:
观察:
- 版本: 5.5.47-log
- 30 GB 内存
- 正常运行时间 = 13d 00:04:54
- 您不是在 Windows 上运行的。
- 运行 64 位版本
- 您似乎完全(或大部分)运行InnoDB。
更重要的问题:
"为 mysql 分配 30G"是什么意思? 我看到buffer_pool设置为这样。 如果您有超过 30G 的 RAM 可用于所有MySQL,这是合理的。 如果 40GB 机器主要专用于 MySQL,那么 30G 用于buffer_pool是好的。
是时候升级到 5.6 了。 table_cache受到的打击相当严重,但table_open_cache_hits
和_misses
直到 5.6 才可用。 同时,看看这是否有帮助:
table_open_cache = 500
InnoDB的日志正在疯狂地翻腾。 推荐以下内容。 注意 - 更改很复杂:
innodb_log_file_size = 128M
您正在执行什么操作才能删除如此多的行? 也许您正在"重新加载"表格? 有比使用DELETE
更快的方法。 告诉我们发生了什么,以便我们量身定制解决方案。
有很多频繁和/或缓慢查询的线索。 请打开慢日志并执行以下操作
log_queries_not_using_indexes = OFF -- ON is just clutter
在修复了 362 个慢查询/秒中的一些之前,不要减少long_query_time
。
一天后,让我们总结和分析慢日志中的内容。
每分钟创建和删除一次表? 怎么了?
tx_isolation = READ-COMMITTED
-- 怎么回事?
这些将帮助一些人:
innodb_buffer_pool_instances = 16
thread_cache_size = 20
join_buffer_size = 256K
细节和其他观察:
( Key_blocks_used * 1024 / key_buffer_size ) = 5,921 * 1024 / 16M = 36.1%
-- 使用key_buffer的百分比。高水位线。 -- 降低key_buffer_size以避免不必要的内存使用。
( innodb_buffer_pool_size / _ram ) = 30720M / 30720M = 100.0%
-- 用于 InnoDB buffer_pool 的 RAM 百分比
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (16M / 0.20 + 30720M / 0.70) / 30720M = 143.1%
-- 大多数可用的 ram 应该可用于缓存。 -- http://mysql.rjweb.org/doc.php/memory
( Opened_tables ) = 21,935,444 / 1123494 = 20 /sec
-- 打开表的频率 -- 增加table_open_cache
( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 30720M / 1 = 30720MB
-- 每个buffer_pool实例的大小。 -- 实例应至少为 1GB。在非常大的 RAM 中,有 16 个实例。
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 901,470 / 1966080 = 45.9%
-- 目前未使用的buffer_pool的Pct -- innodb_buffer_pool_size比必要的大?
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 79,636,049,408 / (1123494 / 3600) / 2 / 5M = 24.3
-- 比率 --(见会议记录)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,123,494 / 60 * 5M / 79636049408 = 1.23
-- InnoDB 日志轮换之间的分钟数 从 5.6.8 开始,可以动态更改;请务必同时更改 my.cnf。 --(建议轮换之间间隔60分钟有点武断。调整innodb_log_file_size。(无法在 AWS 中更改。
( Innodb_rows_deleted / Innodb_rows_inserted ) = 3,897,802 / 4605700 = 0.846
-- 流失 ——"不要排队,就做吧。"(如果MySQL被用作队列。
( Innodb_row_lock_waits ) = 1,286,068 / 1123494 = 1.1 /sec
-- 获取行锁延迟的频率。 -- 可能是由可以优化的复杂查询引起的。
( innodb_stats_on_metadata ) = ON
-- 在接触统计数据时重新分析表格。 -- ON 可能会减慢某些 SHOW 和information_schema访问的速度。
( innodb_thread_concurrency ) = 0
-- 0 = 让 InnoDB 决定最适合concurrency_tickets。 -- 设置为 0 或 64。这可能会减少 CPU。
( innodb_print_all_deadlocks ) = OFF
-- 是否记录所有死锁。 -- 如果您受到死锁的困扰,请打开此功能。警告:如果存在大量死锁,这可能会写入大量磁盘。
( join_buffer_size / _ram ) = 131,072 / 30720M = 0.00%
-- 每个线程 0-N。可以加快 JOIN(更好地修复查询/索引)(所有引擎) 用于索引扫描、范围索引扫描、全表扫描、每个完整 JOIN 等。 -- 如果很大,请降低join_buffer_size以避免内存压力。建议小于 1% 的内存。如果很小,请增加到 RAM 的 0.01% 以改进某些查询。
( query_prealloc_size / _ram ) = 8,192 / 30720M = 0.00%
-- 用于解析。三分之三
( query_alloc_block_size / _ram ) = 8,192 / 30720M = 0.00%
-- 用于解析。三分之三
( net_buffer_length / max_allowed_packet ) = 16,384 / 35M = 0.04%
( local_infile ) = ON
-- local_infile = ON 是一个潜在的安全问题
( Questions ) = 5,877,218,009 / 1123494 = 5231 /sec
-- 查询(SP 外部) -- "qps" -->2000可能给服务器带来压力
( Queries ) = 5,877,263,773 / 1123494 = 5231 /sec
-- 查询(包括 SP 内部) -->3000可能正在给服务器带来压力
( Created_tmp_tables ) = 665,507,495 / 1123494 = 592 /sec
-- 创建"临时"表作为复杂 SELECT 的一部分的频率。
( Com_delete / Com_insert ) = 15,234,374 / 4603989 = 330.9%
-- 删除/插入(作为 pct)。(忽略加载、替换等)
( Select_scan ) = 491,596,929 / 1123494 = 437 /sec
-- 全表扫描 -- 添加索引/优化查询(除非它们是小表)
( Select_scan / Com_select ) = 491,596,929 / 5360296742 = 9.2%
-- 执行全表扫描的选择的百分比。 (可能被存储例程所愚弄。 -- 添加索引/优化查询
( Sort_merge_passes ) = 631,000 / 1123494 = 0.56 /sec
-- 重物排序 -- 增加sort_buffer_size和/或优化复杂查询。
( innodb_autoinc_lock_mode ) = 1
-- Galera:欲望 2 -- 2 = "交错"; 1 = "连续"是典型的; 0 = "传统"。
( log_slow_queries ) = OFF
-- 是否记录慢查询。(5.1.29、5.6.1 之前)
( slow_query_log ) = OFF
-- 是否记录慢查询。(5.1.12)
( long_query_time ) = 5.000000 = 5
-- 用于定义"慢速"查询的截止时间(秒)。 -- 建议 2
( Slow_queries ) = 407,546,261 / 1123494 = 362 /sec
-- 频率(每秒慢查询数) - 返工慢人;改善指标;监视磁盘空间以查找慢速日志文件
( Slow_queries / Questions ) = 407,546,261 / 5877218009 = 6.9%
-- 频率(占所有查询的百分比) -- 查找慢查询;检查索引。
( log_queries_not_using_indexes ) = ON
-- 是否将其包含在慢日志中。 -- 这会使慢日志变得混乱;将其关闭,以便您可以看到真正的慢查询。并降低long_query_time以捕获最有趣的查询。
( max_connect_errors ) = 100,000
-- 针对黑客的小型保护措施。 ——也许不超过200个。
( Threads_running - 1 ) = 21 - 1 = 20
-- 活动线程(收集数据时的并发性) -- 优化查询和/或架构
( Threads_created / Connections ) = 7,052 / 392731 = 1.8%
-- 流程创建速度快 -- 增加thread_cache_size(非窗口)
( thread_cache_size / max_connections ) = 8 / 910 = 0.88%
-- (0 表示视窗)
( Threads_running / thread_cache_size ) = 21 / 8 = 2.62
-- 线程:当前/缓存(使用线程池时无关紧要) -- 优化查询
异常大:
Binlog_cache_use = 82 /sec
Bytes_received = 1439336 /sec
Com_commit = 114 /sec
Com_create_table = 60 /HR
Com_delete = 14 /sec
Com_delete_multi = 0.033 /sec
Com_drop_table = 60 /HR
Com_purge_before_date = 0.042 /HR
Com_select = 4771 /sec
Com_set_option = 229 /sec
Com_update = 98 /sec
Created_tmp_files = 0.4 /sec
Handler_commit = 5140 /sec
Handler_prepare = 357 /sec
Handler_read_first = 143 /sec
Handler_write = 19401 /sec
Innodb_buffer_pool_pages_free = 901,470
Select_range = 95 /sec
Sort_range = 3821 /sec
Sort_rows = 15912 /sec
Tc_log_page_waits = 7
Threads_running = 21
其他建议(2018 年 6 月 21 日)要考虑用于 Linux 和 my.cnf-ini [mysqld] 部分
对于 Linux,因为您的限制是 1024,在 Linux 命令中, ulimit -n 30000 # 增加打开文件句柄计数
.为您的 my.cnf
table_open_cache=10000 # from 256 to lower 20 opened_tables per SECOND
table_definition_cache=800 # from 400 to lower 43,623 opened_table_definitions
open_files_limit=13000 # from 4550 to lower opened_files of 959,559 in 13 days, 1 RPS.
对 my.cnf-ini [mysqld] 部分要考虑的建议
max_connections=500 # from 910 only 266 max_used in 13 days
thread_cache_size=100 # from 8 to minimize thread churn and reduce threads_created
read_rnd_buffer_size=128K # from 256K to lower handler_read_rnd_next RPS
key_cache_age_threshold=64800 # from 300 seconds to read same data again
key_cache_division_limit=50 # from 100 for Hot/Warm cache
innodb_buffer_pool_size=24G # from 32G 17G was in use when GLOBAL STATUS recorded
。对于需要对版本 5.5.nn 进行特殊处理的情况下,请参阅下一个 2 的 REFMAN。
innodb_log_buffer_size=128M # from 8M to support ~30 minutes
innodb_log_file_size=512M # from 5M for rotation every couple hours
max_write_lock_count=16 # to allow RD after nn locks vs possible wait on 4Billion locks
sort_buffer_size=3M # from 2M to reduce sort_merge_passes
thread_concurrency=30 # from 10 for expedited completion
如需其他帮助,请在"我的个人资料"、"网络个人资料"中找到联系信息。
(第二个答案 - 解决查询。
- 没有主键? 淘气。 该表是否具有"自然"唯一的列(或列的组合)? 如果是这样,请考虑从它/他们进行PK。
INDEX(p_pkid, p_billingaddress, p_shippingaddress, TypeGKString
),无论按何种顺序,都将显着提高SELECT
性能,从而加快速度并降低 CPU 消耗。UPDATE
需要INDEX(GK)
,(SHOW CREATE TABLE cronjobs
在哪里?- 不要将
BIGINT
(8 字节)用于需要它的列。 (有多少种不同的"性别"?? (考虑CHAR(2) CHARACTER SET ascii
country
。请参阅TINYINT
(1 字节)及其好友。
(是的,这是我的第三个答案。 这次是为了说明新SHOW GLOBAL STATUS
的发现。
嗯...... 我使用了旧SHOW VARIABLEs
,所以一些建议是基于其中的值。 如果您遵循了我以前的建议,那么我需要一份新的SHOW GLOBAL VARIABLES
副本。
观察:
- 版本: 5.5.47-log
- 40 GB 内存
- 正常运行时间 = 11d 23:29:02
- 您不是在 Windows 上运行的。
- 运行 64 位版本
- 您似乎完全(或大部分)运行InnoDB。
更重要的问题:
请参阅有关更改innodb_log_file_size
的信息。 (或参见 5.5 手册等)
innodb_log_file_size = 80M -- too much waiting for the log. See above
innodb_buffer_pool_instances = 16 -- minor contention relief
table_open_cache = 512 -- bumping entries out of this cache has some impact
thread_cache_size = 200 -- process creation is somewhat costly
log_queries_not_using_indexes = OFF -- otherwise clutter
请参阅此处,查找"最差"查询,以便我们帮助您进行优化。 Yikes,268 次查询/秒,耗时超过 5 秒。
删除比插入更频繁? 这是怎么回事?
每秒 9KSETs
? 这是怎么回事?
你为什么选择READ-COMMITTED
?
数百Threads_running
通常意味着MySQL正在绊倒自己,最好限制客户端中的连接数。一些基准测试表明,5.6 和 5.7 实际上可以更好地处理这种情况;5.5 在几十个连接后,吞吐量(查询/秒)往往会下降。 考虑升级,也因为你落后了 3 个主要修订。
细节和其他观察:
( Opened_tables ) = 13,001,730 / 1034942 = 13 /sec
-- 打开表的频率 -- 增加table_open_cache
( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 30720M / 1 = 30720MB
-- 每个buffer_pool实例的大小。 -- 实例应至少为 1GB。在非常大的 RAM 中,有 16 个实例。
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 45,704,499,200 / (1034942 / 3600) / 2 / 5M = 15.2
-- 比率 --(见会议记录)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,034,942 / 60 * 5M / 45704499200 = 1.98
-- InnoDB 日志轮换之间的分钟数 从 5.6.8 开始,可以动态更改;请务必同时更改 my.cnf。 --(建议轮换之间间隔60分钟有点武断。调整innodb_log_file_size。(无法在 AWS 中更改。
( Innodb_rows_deleted / Innodb_rows_inserted ) = 3,412,394 / 4031801 = 0.846
-- 流失 ——"不要排队,就做吧。"(如果MySQL被用作队列。
( Innodb_row_lock_waits ) = 256,120 / 1034942 = 0.25 /sec
-- 获取行锁延迟的频率。 -- 可能是由可以优化的复杂查询引起的。
( innodb_stats_on_metadata ) = innodb_stats_on_metadata = ON
-- 在接触统计数据时重新分析表格。 -- ON 可能会减慢某些 SHOW 和information_schema访问的速度。
( innodb_thread_concurrency ) = 0
-- 0 = 让 InnoDB 决定最适合concurrency_tickets。 -- 设置为 0 或 64。这可能会减少 CPU。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF
-- 是否记录所有死锁。 -- 如果您受到死锁的困扰,请打开此功能。警告:如果存在大量死锁,这可能会写入大量磁盘。
( net_buffer_length / max_allowed_packet ) = 16,384 / 35M = 0.04%
( local_infile ) = local_infile = ON
-- local_infile = ON 是一个潜在的安全问题
( bulk_insert_buffer_size / _ram ) = 8M / 40960M = 0.02%
-- 多行插入和加载数据的缓冲区 -- 太大可能会威胁到内存大小。太小可能会阻碍此类操作。
( Questions ) = 15,894,065,519 / 1034942 = 15357 /sec
-- 查询(SP 外部) -- "qps" -->2000可能给服务器带来压力
( Queries ) = 15,894,107,993 / 1034942 = 15357 /sec
-- 查询(包括 SP 内部) -->3000可能正在给服务器带来压力
( Created_tmp_tables ) = 501,042,067 / 1034942 = 484 /sec
-- 创建"临时"表作为复杂 SELECT 的一部分的频率。
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (4031334 + 54661875 + 13451091 + 0) / 4930449084 = 0.0146
-- 每次提交的语句数(假设所有 InnoDB) -- 低:可能有助于在事务中将查询分组在一起;高:多头交易使各种事情变得紧张。
( Com_delete / Com_insert ) = 13,451,091 / 4031334 = 333.7%
-- 删除/插入(作为 pct)。(忽略加载、替换等)
( Select_scan ) = 348,481,657 / 1034942 = 336 /sec
-- 全表扫描 -- 添加索引/优化查询(除非它们是小表)
( Select_scan / Com_select ) = 348,481,657 / 1029506656 = 33.8%
-- 执行全表扫描的选择的百分比。 (可能被存储例程愚弄。 -- 添加索引/优化查询
( Sort_merge_passes ) = 297,216 / 1034942 = 0.29 /sec
-- 重排序 -- 增加sort_buffer_size和/或优化复杂查询。
( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (4031334 + 13451091 + 34494 + 0 + 54661875 + 0) / 1034942 = 70 /sec
-- 写入/秒 -- 50 次写入/秒 + 日志刷新可能会使普通驱动器的 I/O 写入容量最大化
( log_slow_queries ) = log_slow_queries = OFF
-- 是否记录慢查询。(5.1.29、5.6.1 之前)
( slow_query_log ) = slow_query_log = OFF
-- 是否记录慢查询。(5.1.12)
( long_query_time ) = 5
-- 用于定义"慢速"查询的截止时间(秒)。 -- 建议 2
( Slow_queries ) = 277,531,287 / 1034942 = 268 /sec
-- 频率(每秒慢查询) - 返工慢人;改善指标;监视磁盘空间以查找慢速日志文件
( Slow_queries / Questions ) = 277,531,287 / 15894065519 = 1.7%
-- 频率(占所有查询的百分比) -- 查找慢查询;检查索引。
( log_queries_not_using_indexes ) = log_queries_not_using_indexes = ON
-- 是否将其包含在慢日志中。 -- 这会使慢日志变得混乱;将其关闭,以便您可以看到真正的慢查询。并降低long_query_time以捕获最有趣的查询。
( max_connect_errors ) = 100,000
-- 针对黑客的小型保护措施。 ——也许不超过200个。
( Threads_running - 1 ) = 215 - 1 = 214
-- 活动线程(收集数据时的并发性) -- 优化查询和/或架构
( Threads_created / Connections ) = 6,390 / 361954 = 1.8%
-- 流程创建速度快 -- 增加thread_cache_size(非窗口)
( thread_cache_size / max_connections ) = 8 / 910 = 0.88%
-- (0 表示视窗)
( Threads_running / thread_cache_size ) = 215 / 8 = 26.9
-- 线程:当前/缓存(使用线程池时不相关) -- 优化查询
在162 个计算变量/状态/表达式中标记了 36 个问题
异常大:
Bytes_received = 820009 /sec
Com_commit = 4763 /sec
Com_delete = 13 /sec
Com_delete_multi = 0.033 /sec
Com_drop_table = 60 /HR
Com_purge_before_date = 0.042 /HR
Com_set_option = 9528 /sec
Com_show_engine_status = 21 /HR
Created_tmp_files = 0.21 /sec
Handler_commit = 1207 /sec
Handler_read_first = 105 /sec
Handler_read_key = 72824 /sec
Handler_write = 25131 /sec
Innodb_buffer_pool_pages_misc = 100,245
Key_write_requests = 324 /sec
Select_range = 73 /sec
Sort_range = 221 /sec
Tc_log_page_waits = 1
Threads_running = 215
异常字符串:
innodb_fast_shutdown = 1
innodb_use_native_aio = OFF
relay_log_info_file = /PK/mysql/mysql-relay-log-info
time_zone = SYSTPK
tx_isolation = READ-COMMITTED