更新操作期间mysql cpu使用率

gwo2fgha  于 2021-06-20  发布在  Mysql
关注(0)|答案(5)|浏览(265)

我面临着一个问题，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)

非常感谢您的帮助。谢谢

mysqlcpuquery-performanceImport

5条答案

按热度按时间
ffscu2ro

ffscu2ro1#

关于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版需要特殊处理，请参阅refman了解下一个2。

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）分享  回复(0）举报  2021-06-20
gijlo24d

gijlo24d2#

（是的，这是我的第三个答案。这一次，我们要详细说明新发现 SHOW GLOBAL STATUS .)
六羟甲基三聚氰胺六甲醚。。。我用的是旧的 SHOW VARIABLEs ，所以有些建议是基于这些建议的值。如果你听了我以前的建议，那我需要一份新的 SHOW GLOBAL VARIABLES .
观察：
版本：5.5.47-log
40 gb内存
正常运行时间=11天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秒。
删除比插入频繁得多？怎么回事？
9千 SETs 每秒？怎么回事？
你为什么选择 READ-COMMITTED ?
数以百计 Threads_running 通常意味着mysql在自我绊倒，最好限制客户机中的连接数。一些基准表明，5.6和5.7实际上更好地处理了这种情况；5.5在经过几十次连接后，吞吐量（查询/秒）趋于下降。考虑升级，因为你落后于3个主要的修订。
细节和其他观察： ( Opened_tables ) = 13,001,730 / 1034942 = 13 /sec --打开表的频率--增加表\u open \u缓存 ( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 30720M / 1 = 30720MB --每个缓冲池示例的大小。-示例应至少为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 --从5.6.8开始的innodb日志循环间隔分钟，可以动态更改；一定要同时更改我的.cnf。-(旋转间隔60分钟的建议有些随意。）调整innodb\u log\u file\u大小(无法在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可能会减慢某些节目和信息的访问。 ( innodb_thread_concurrency ) = 0 --0=让innodb决定并发的最佳票证。-设置为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\u infle=on是一个潜在的安全问题 ( bulk_insert_buffer_size / _ram ) = 8M / 40960M = 0.02% --用于多行插入和加载数据的缓冲区—太大可能会威胁ram大小。规模过小可能会妨碍此类行动。 ( 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 --作为复杂选择的一部分，创建“临时”表的频率。 ( (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 --大量排序—增加排序缓冲区大小和/或优化复杂查询。 ( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (4031334 + 13451091 + 34494 + 0 + 54661875 + 0) / 1034942 = 70 /sec

赞(0）分享  回复(0）举报  2021-06-20
ngynwnxp

ngynwnxp3#

（第二个答案——回答问题。）
没有主键？淘气的。该表是否有“自然”唯一的列（或列的组合）？如果是这样，请考虑从it/他们那里进行pk。 INDEX(p_pkid, p_billingaddress, p_shippingaddress, TypeGKString )，以任何顺序，都将有助于 SELECT 显著提高性能，从而加快速度并减少cpu消耗。 INDEX(GK) 是需要的 UPDATE ，（电话在哪里 SHOW CREATE TABLE cronjobs ?)
不要使用 BIGINT （8字节）用于需要它的列(有多少不同的“性别” CHAR(2) CHARACTER SET ascii 为了 country ）见 TINYINT （1字节）及其朋友。

赞(0）分享  回复(0）举报  2021-06-20
muk1a3rh

muk1a3rh4#

全球状况和变量分析：
观察：
版本：5.5.47-log
30 gb内存
正常运行时间=13d 00:04:54
您没有在windows上运行。
运行64位版本
您似乎正在运行全部（或大部分）innodb。
更重要的问题是：
你所说的“30g分配给mysql”是什么意思？我看到缓冲池设置为。如果所有mysql都有超过30g的ram可用，这是合理的。如果40gb的机器主要用于mysql，那么30g的缓冲池就很好了。
是时候升级到5.6了。表缓存受到的冲击相当大，但是 table_open_cache_hits 以及 _misses 在5.6之前不可用。同时，看看这是否有帮助：

table_open_cache = 500

innodb的日志乱七八糟。建议如下。注意——改变很复杂：

innodb_log_file_size = 128M

你在做什么导致这么多行被删除？也许你是在“重装”一张table？有比使用 DELETE . 告诉我们发生了什么，这样我们就可以定制一个解决方案。
有很多关于频繁和/或缓慢查询的线索。请打开slowlog并进行以下操作

log_queries_not_using_indexes = OFF   -- ON is just clutter

不要减少 long_query_time 直到修复了每秒362个慢速查询中的一些。
一天之后，让我们总结和分析一下slowlog中的内容。
每分钟创建和删除一次表？怎么了？ 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% --使用的密钥缓冲区百分比。高水位线。-降低键缓冲区大小以避免不必要的内存使用。 ( innodb_buffer_pool_size / _ram ) = 30720M / 30720M = 100.0% --innodb缓冲池使用的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 --打开表的频率--增加表\u open \u缓存 ( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 30720M / 1 = 30720MB --每个缓冲池示例的大小。-示例应至少为1gb。在非常大的ram中，有16个示例。 ( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 901,470 / 1966080 = 45.9% --缓冲池的pct当前未使用--innodb\U缓冲池\U大小是否大于所需大小？ ( 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 --从5.6.8开始的innodb日志循环间隔分钟，可以动态更改；一定要同时更改我的.cnf。-(旋转间隔60分钟的建议有些随意。）调整innodb\u log\u file\u大小(无法在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可能会减慢某些节目和信息的访问。 ( innodb_thread_concurrency ) = 0 --0=让innodb决定并发的最佳票证。-设置为0或64。这可能会减少cpu。 ( innodb_print_all_deadlocks ) = OFF --是否记录所有死锁。-如果你被死锁困扰，打开这个。注意：如果你有很多死锁，这可能会写很多到磁盘。 ( join_buffer_size / _ram ) = 131,072 / 30720M = 0.00% --每个线程0-n。可能会加快用于索引扫描、范围索引扫描、完整表扫描、每个完整连接等的连接（最好修复查询/索引）（所有引擎）--如果连接较大，请减小连接缓冲区大小以避免内存压力。建议小于ram的1%。如果较小，则增加到ram的0.01%，以改进某些查询。 ( query_prealloc_size / _ram ) = 8,192 / 30720M = 0.00% --用于解析。ram百分比 ( query_alloc_block_size / _ram ) = 8,192 / 30720M = 0.00% --用于解析。ram百分比
( net_buffer_length / max_allowed_packet ) = 16,384 / 35M = 0.04% ( local_infile ) = ON --local\u infle=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 --作为复杂选择的一部分，创建“临时”表的频率。 ( 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 --大量排序—增加排序缓冲区大小和/或优化复杂查询。 ( innodb_autoinc_lock_mode ) = 1 --galera:desires 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 --是否要在slowlog中包含这样的内容。-这使慢行日志混乱不堪；关闭它，这样您就可以看到真正缓慢的查询。并减少长时间的查询以捕获最感兴趣的查询。 ( max_connect_errors ) = 100,000 --对黑客的小小保护可能不超过200个。 ( Threads_running - 1 ) = 21 - 1 = 20 --活动线程（收集数据时并发）--优化查询和/或模式 ( Threads_created / Connections ) = 7,052 / 392731 = 1.8% --进程创建的快速性--增加线程\u缓存\u大小（非windows） ( thread_cache_size / max_connections ) = 8 / 910 = 0.88% --（0用于windows） ( 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
赞(0）分享  回复(0）举报  2021-06-20
dgiusagp

dgiusagp5#

关于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）分享  回复(0）举报  2021-06-20
我来回答

相关问题

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com