Re: MYSQL Update performance
Posted by: sree iyer
Date: May 01, 2017 01:11PM
Date: May 01, 2017 01:11PM
show engine innodb status;
=====================================
2017-05-01 19:06:11 7e92a9487700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 52 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 938383 srv_active, 0 srv_shutdown, 1 srv_idle
srv_master_thread log flush and writes: 938384
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 45870378
OS WAIT ARRAY INFO: signal count 301567272
Mutex spin waits 4064274948, rounds 4276944255, OS waits 7822398
RW-shared spins 104812234, rounds 1081717850, OS waits 22390620
RW-excl spins 79611330, rounds 1185806757, OS waits 13961428
Spin rounds per wait: -18.54 mutex, 10.32 RW-shared, 14.89 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-04-21 06:49:17 7e9227fff700
*** (1) TRANSACTION:
TRANSACTION 2538697215, ACTIVE 7 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 53934 lock struct(s), heap size 5109288, 5436123 row lock(s)
MySQL thread id 204026, OS thread handle 0x7e92a9ae0700, query id 295820635 localhost provider Sending data
Replace into data_quality_results(employer_id, imported_claim_file_id, metric_id, created_at, insurance_company_id,metric_value)
Select 281,1005184,138, NOW(),c.insurance_company_id as Payer, FORMAT(sum(if(c.user_id = -1,1,0)) / count(*),3) as 'Claims Without User ID To Total Claims Ratio'
from claims c
where imported_claim_file_id = 1005184 group by 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 283400 page no 20866 n bits 360 index `PRIMARY` of table `claims_master_prod`.`data_quality_results` trx id 2538697215 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 2538697265, ACTIVE 5 sec fetching rows
mysql tables in use 3, locked 3
35406 lock struct(s), heap size 4224552, 8402363 row lock(s), undo log entries 108
MySQL thread id 204270, OS thread handle 0x7e9227fff700, query id 295821175 localhost provider Creating sort index
update data_quality_results dr,( select employer_id, insurance_company_id, created_at, metric_value from data_quality_results where metric_id = 38 group by 1,2,3) dr2, validation_sql vs set percent_value = IFNULL((Case when vs.percentage = 1 then FORMAT((dr.metric_value/dr2.metric_value),4) end),'*') where dr.employer_id = dr2.employer_id and dr.insurance_company_id = dr2.insurance_company_id and dr.created_at = dr2.created_at and dr.metric_id = vs.id and dr.percent_value is null
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 283400 page no 20866 n bits 360 index `PRIMARY` of table `claims_master_prod`.`data_quality_results` trx id 2538697265 lock mode S
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 283400 page no 20866 n bits 408 index `PRIMARY` of table `claims_master_prod`.`data_quality_results` trx id 2538697265 lock_mode X waiting
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 2553885881
Purge done for trx's n:o < 2553856639 undo n:o < 0 state: running but idle
History list length 2908
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 1436295, OS thread handle 0x7e92a8fb4700, query id 1993021124 localhost provider cleaning up
---TRANSACTION 2553883724, not started
MySQL thread id 1436146, OS thread handle 0x7e92a8794700, query id 1993016939 localhost provider cleaning up
---TRANSACTION 0, not started
MySQL thread id 1433850, OS thread handle 0x7e92a8ca8700, query id 1993070756 den-jh01 10.11.33.99 wsamuel cleaning up
---TRANSACTION 2553871002, not started
MySQL thread id 1434170, OS thread handle 0x7e92aa139700, query id 1993084081 10.25.30.101 ashankar cleaning up
---TRANSACTION 0, not started
MySQL thread id 1433852, OS thread handle 0x7e922ffff700, query id 1993020789 den-jh01 10.11.33.99 wsamuel cleaning up
---TRANSACTION 2553872963, not started
MySQL thread id 1432299, OS thread handle 0x7e92aa300700, query id 1992945765 localhost provider cleaning up
---TRANSACTION 2553811472, not started
MySQL thread id 1430734, OS thread handle 0x7e92a8ff5700, query id 1991992311 den-jh01 10.11.33.99 wsamuel cleaning up
---TRANSACTION 0, not started
MySQL thread id 1430737, OS thread handle 0x7e92a8ce9700, query id 1991988840 den-jh01 10.11.33.99 wsamuel cleaning up
---TRANSACTION 0, not started
MySQL thread id 1428955, OS thread handle 0x7e92a9509700, query id 1990761065 localhost provider cleaning up
---TRANSACTION 2553736743, not started
MySQL thread id 1428949, OS thread handle 0x7e92a9405700, query id 1990761029 localhost provider cleaning up
---TRANSACTION 2553603006, not started
MySQL thread id 1424150, OS thread handle 0x7e922fa69700, query id 1993024629 10.25.30.101 ashankar cleaning up
---TRANSACTION 0, not started
MySQL thread id 1423445, OS thread handle 0x7e92a899c700, query id 1993021141 localhost 127.0.0.1 provider cleaning up
---TRANSACTION 2553600681, not started
MySQL thread id 1423243, OS thread handle 0x7e92a9487700, query id 1993086758 localhost 127.0.0.1 provider init
show engine innodb status
---TRANSACTION 2553873146, not started
MySQL thread id 1420026, OS thread handle 0x7e92aa2bf700, query id 1993012584 den-wh07.ch.int 10.11.21.77 provider cleaning up
---TRANSACTION 2553849506, not started
MySQL thread id 1420025, OS thread handle 0x7f2f5c2e6700, query id 1993012583 den-wh07.ch.int 10.11.21.77 provider cleaning up
---TRANSACTION 2553548360, not started
MySQL thread id 1419528, OS thread handle 0x7e92a927f700, query id 1993084083 10.25.30.101 ashankar cleaning up
---TRANSACTION 2553600993, not started
MySQL thread id 1419391, OS thread handle 0x7e92a813b700, query id 1993066704 10.25.30.101 ashankar cleaning up
---TRANSACTION 2553632330, not started
MySQL thread id 1418038, OS thread handle 0x7e92a8447700, query id 1990410310 den-jh01 10.11.33.99 wsamuel cleaning up
---TRANSACTION 0, not started
MySQL thread id 1418039, OS thread handle 0x7e92a9a9f700, query id 1990408518 den-jh01 10.11.33.99 wsamuel cleaning up
---TRANSACTION 2553885624, not started
MySQL thread id 2, OS thread handle 0x7f2f5c8bb700, query id 1993086757 System lock
flush privileges
---TRANSACTION 2553885880, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 1428954, OS thread handle 0x7e92a85cd700, query id 1993086720 localhost provider update
INSERT INTO cvs_caremark_rx_imported_claims (record_type,record_indicator,filler,filler_2,filler_3,filler_4,filler_5,filler_6,filler_7,filler_8,cardholder_id,last_name,first_name,middle_initial,filler_9,filler_10,filler_11,filler_12,filler_13,filler_14,cardholder_date_of_birth,filler_15,filler_16,filler_17,filler_18,filler_19,patient_last_name,patient_first_name,patient_middle_initial,filler_20,filler_21,filler_22,filler_23,filler_24,filler_25,patient_date_of_birth,patient_gender_code,patient_relationship_code,patient_age,filler_26,filler_27,filler_28,filler_29,group_id,carrier_number,policy_n
---TRANSACTION 2553883730, ACTIVE 172 sec inserting
mysql tables in use 3, locked 3
2 lock struct(s), heap size 360, 0 row lock(s), undo log entries 7332
MySQL thread id 1436149, OS thread handle 0x7f2f5c05c700, query id 1993086747 localhost provider update
REPLACE INTO `claims_master_prod`.`_wellpoint_imported_claim_members_new` (`id`, `sbscrbr_id`, `mbr_sqnc_nbr`, `st_prvnc_cd`, `mbr_md_nm`, `mbr_zip_cd`, `sub_ssn`, `hcid`, `mbr_alph_prfx_id`, `hlth_class_cov_cd`, `imported_claim_dimension_file_id`, `imported_claim_file_id`, `created_at`, `updated_at`, `md5_checksum`, `duplicate_of_claim_dimension_id`) VALUES (NEW.`id`, NEW.`sbscrbr_id`, NEW.`mbr_sqnc_nbr`, NEW.`st_prvnc_cd`, NEW.`mbr_md_nm`, NEW.`mbr_zip_cd`, NEW.`sub_ssn`, NEW.`hcid`, NEW.`mbr_alph_prfx_id`, NEW.`hlth_class_cov_cd`, NEW.`imported_claim_dimension_file_id`, NEW.`imported_claim_
---TRANSACTION 2553875563, ACTIVE 652 sec
MySQL thread id 1435478, OS thread handle 0x7f2f5c264700, query id 1993020919 den-wh08.ch.int 10.11.21.78 provider cleaning up
Trx read view will not see trx with id >= 2553875564, sees < 2552590236
---TRANSACTION 2552590236, ACTIVE 71869 sec updating or deleting
mysql tables in use 2, locked 2
5837486 lock struct(s), heap size 904869416, 1511140424 row lock(s), undo log entries 504006382
MySQL thread id 1337862, OS thread handle 0x7e92aa486700, query id 1960582320 den-wh03.ch.int 10.11.21.25 provider updating reference tables
update claim_provider_association cp
inner join
provider_attributes_lookup pa
on cp.provider_attributes_lookup_id = pa.id
and pa.old_provider_id is not null
SET cp.provider_id = pa.old_provider_id
where cp.provider_id is null
---TRANSACTION 2553856520, ACTIVE 1626 sec
MySQL thread id 1434110, OS thread handle 0x7e92a9077700, query id 1992547512 10.24.30.103 rscribner cleaning up
Trx read view will not see trx with id >= 2553856521, sees < 2552590236
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: complete io for buf page (read thread)
I/O thread 3 state: complete io for buf page (read thread)
I/O thread 4 state: complete io for buf page (read thread)
I/O thread 5 state: complete io for buf page (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 46 [4, 24, 10, 8] , 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
337634292 OS file reads, 517627086 OS file writes, 26587719 OS fsyncs
0 pending preads, 1 pending pwrites
50.13 reads/s, 16384 avg bytes/read, 3386.40 writes/s, 107.69 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 60417, free list len 2393902, seg size 2454320, 17177406 merges
merged operations:
insert 88591523, delete mark 16586285, delete 5784971
discarded operations:
insert 31, delete mark 0, delete 0
1497.36 hash searches/s, 13556.57 non-hash searches/s
---
LOG
---
Log sequence number 68646648643764
Log flushed up to 68646648643624
Pages flushed up to 68646244100950
Last checkpoint at 68646242693356
Max checkpoint age 434154333
Checkpoint age target 420587011
Modified age 404542814
Checkpoint age 405950408
0 pending log writes, 0 pending chkp writes
8712864 log i/o's done, 22.46 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 659344588800; in additional pool allocated 0
Total memory allocated by read views 2056
Internal hash tables (constant factor + variable factor)
Adaptive hash index 41100392192 (10199885464 + 30900506728)
Page hash 79687528 (buffer pool 0 only)
Dictionary cache 2592054207 (2549972816 + 42081391)
File system 4380016 (812272 + 3567744)
Lock system 2498611200 (1593733144 + 904878056)
Recovery system 0 (0 + 0)
Dictionary memory allocated 42081391
Buffer pool size 39321592
Buffer pool size, bytes 644244963328
Free buffers 417817
Database pages 36962530
Old database pages 13644240
Modified db pages 541273
Pending reads 50
Pending writes: LRU 0, flush list 121, single page 0
Pages made young 107910535, not young 14533428341
49.58 youngs/s, 123.11 non-youngs/s
Pages read 336617505, created 55894297, written 496513267
49.58 reads/s, 53.10 creates/s, 3304.96 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 36962530, unzip_LRU len: 0
I/O sum[1333928]:cur[3560], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 4915199
Buffer pool size, bytes 80530620416
Free buffers 52452
Database pages 4620553
Old database pages 1705620
Modified db pages 68100
Pending reads 9
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 13504655, not young 1815168303
6.13 youngs/s, 12.83 non-youngs/s
Pages read 42009625, created 7032902, written 62655816
6.04 reads/s, 7.48 creates/s, 410.74 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4620553, unzip_LRU len: 0
I/O sum[166741]:cur[445], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 4915199
Buffer pool size, bytes 80530620416
Free buffers 53646
Database pages 4618510
Old database pages 1704860
Modified db pages 66625
Pending reads 2
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 13496196, not young 1782068820
4.56 youngs/s, 12.87 non-youngs/s
Pages read 41708264, created 6967313, written 61326745
6.00 reads/s, 5.98 creates/s, 401.43 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: 4618510, unzip_LRU len: 0
I/O sum[166741]:cur[445], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 4915199
Buffer pool size, bytes 80530620416
Free buffers 50311
Database pages 4622508
Old database pages 1706344
Modified db pages 66730
Pending reads 14
Pending writes: LRU 0, flush list 32, single page 0
Pages made young 13442969, not young 1834891369
5.29 youngs/s, 10.98 non-youngs/s
Pages read 42300504, created 6991953, written 61499777
5.46 reads/s, 7.52 creates/s, 412.84 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4622508, unzip_LRU len: 0
I/O sum[166741]:cur[445], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 4915199
Buffer pool size, bytes 80530620416
Free buffers 52068
Database pages 4620624
Old database pages 1705641
Modified db pages 68719
Pending reads 1
Pending writes: LRU 0, flush list 51, single page 0
Pages made young 13454949, not young 1847139361
5.52 youngs/s, 20.92 non-youngs/s
Pages read 42171714, created 6937230, written 61953823
6.60 reads/s, 4.00 creates/s, 435.18 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4620624, unzip_LRU len: 0
I/O sum[166741]:cur[445], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 4915199
Buffer pool size, bytes 80530620416
Free buffers 53567
Database pages 4619057
Old database pages 1705062
Modified db pages 66670
Pending reads 2
Pending writes: LRU 0, flush list 38, single page 0
Pages made young 13559921, not young 1781072384
5.56 youngs/s, 17.38 non-youngs/s
Pages read 41542691, created 6983666, written 63568220
6.65 reads/s, 5.00 creates/s, 385.01 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4619057, unzip_LRU len: 0
I/O sum[166741]:cur[445], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 4915199
Buffer pool size, bytes 80530620416
Free buffers 51295
Database pages 4621145
Old database pages 1705835
Modified db pages 69850
Pending reads 6
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 13519861, not young 1841208612
12.19 youngs/s, 18.69 non-youngs/s
Pages read 42285578, created 7002329, written 62627867
6.46 reads/s, 7.31 creates/s, 444.86 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: 4621145, unzip_LRU len: 0
I/O sum[166741]:cur[445], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 4915199
Buffer pool size, bytes 80530620416
Free buffers 53101
Database pages 4619316
Old database pages 1705164
Modified db pages 66057
Pending reads 12
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 13487693, not young 1816718183
5.50 youngs/s, 13.71 non-youngs/s
Pages read 42331925, created 6991809, written 61312609
6.42 reads/s, 7.00 creates/s, 407.68 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4619316, unzip_LRU len: 0
I/O sum[166741]:cur[445], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 4915199
Buffer pool size, bytes 80530620416
Free buffers 51377
Database pages 4620817
Old database pages 1705714
Modified db pages 68522
Pending reads 4
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 13444291, not young 1815161309
4.83 youngs/s, 15.73 non-youngs/s
Pages read 42267204, created 6987095, written 61568410
5.94 reads/s, 8.81 creates/s, 407.20 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4620817, unzip_LRU len: 0
I/O sum[166741]:cur[445], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
5 RW transactions active inside InnoDB
1 RO transactions active inside InnoDB
5 out of 1000 descriptors used
---OLDEST VIEW---
Normal read view
Read view low limit trx n:o 2553856521
Read view up limit trx id 2552590236
Read view low limit trx id 2553856521
Read view individually stored trx ids:
Read view trx id 2552590236
Read view trx id 2553736737
Read view trx id 2553856199
Read view trx id 2553856504
Read view trx id 2553856519
-----------------
Main thread process no. 129557, id 139168463288064, state: sleeping
Number of rows inserted 4265290449, updated 1036250710, deleted 4345057, read 236000586326
127.00 inserts/s, 6806.56 updates/s, 0.00 deletes/s, 6806.56 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
select
concat(round(sum(table_rows)/1000000,2),'m') rows,
concat(round(sum(data_length)/(1024*1024*1024),2),'g') data,
concat(round(sum(index_length)/(1024*1024*1024),2),'g') idx,
concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'g') total_size,
round(sum(index_length)/sum(data_length),2) idxfrac
from information_schema.tables
where table_name in( 'claim_provider_association','provider_attributes_lookup' );
rows data idx total_size idxfrac
982.88m 32.97g 42.86g 75.83g 1.30
You question on test server - I need to copy this huge volume of data and that might take time plus i don't think we kind of space in test server.
This update query is currently running (more than 20 hrs now).
can I kill it and after re-run the same query with foreign key disabled.
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.