我正面临着mysql高负载的问题,特别是整个CPU都被消耗了。我们有16核的cpu。一个作业运行,将某些数据以csv格式导入到数据库中。当它启动时,CPU会变得很高,mysql会挂起。下面
INNODB ENGINE STATUS
mysql> show engine innodb status\G;
*************************** 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)
任何帮助都是非常感谢的。谢谢
发布于 2018-06-20 04:44:47
要考虑的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
要获得更多帮助,请在我的个人资料、网络资料中找到联系信息。
发布于 2018-06-21 22:55:47
为您的Linux和my.cnf-ini mysqld部分考虑的其他建议(2018年6月21日
。对于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.
https://stackoverflow.com/questions/50925436
复制相似问题