首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >更新操作期间的Mysql CPU使用率

更新操作期间的Mysql CPU使用率
EN

Stack Overflow用户
提问于 2018-06-19 17:54:01
回答 2查看 987关注 0票数 0

我正面临着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)

任何帮助都是非常感谢的。谢谢

EN

回答 2

Stack Overflow用户

发布于 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

要获得更多帮助,请在我的个人资料、网络资料中找到联系信息。

票数 0
EN

Stack Overflow用户

发布于 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.
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/50925436

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档