MySQL Forums
Forum List  »  Italian

Re: CPU sopra il 100% innodb engine
Posted by: fabio rossi
Date: March 05, 2010 02:59AM

-------- Performance Metrics -------------------------------------------------
[--] Up for: 20h 55m 42s (1M q [17.246 qps], 109K conn, TX: 1B, RX: 506M)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 3.6G global + 16.6M per thread (100 max threads)
[!!] Maximum possible memory usage: 5.3G (90% of installed RAM)
[!!] Slow queries: 10% (140K/1M)
[OK] Highest usage of available connections: 35% (35/100)
[OK] Key buffer size / total MyISAM indexes: 512.0M/76.0K
[OK] Key buffer hit rate: 99.8% (3M cached / 6K reads)
[OK] Query cache efficiency: 69.8% (652K cached / 933K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (678 temp sorts / 62K sorts)
[!!] Temporary tables created on disk: 42% (4K on disk / 11K total)
[OK] Thread cache hit rate: 99% (35 created / 109K connections)
[OK] Table cache hit rate: 66% (583 open / 874 opened)
[OK] Open file limit used: 1% (23/2K)
[OK] Table locks acquired immediately: 100% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 2.6G/3.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
tmp_table_size (> 512M)
max_heap_table_size (> 64M)

------------------->

MySQL 5.0.45-log uptime 0 20:51:48 Fri Mar 5 09:31:06 2010

__ Key _________________________________________________________________
Buffer used 3.21M of 512.00M %Used: 0.63
Current 93.36M %Usage: 18.24
Write hit 100.00%
Read hit 99.81%

__ Questions ___________________________________________________________
Total 1.29M 17.1/s
QC Hits 645.23k 8.6/s %Total: 50.14
DMS 360.66k 4.8/s 28.03
Com_ 206.04k 2.7/s 16.01
COM_QUIT 109.03k 1.5/s 8.47
-Unknown 34.05k 0.5/s 2.65
Slow 10 s 138.69k 1.8/s 10.78 %DMS: 38.45 Log: ON
DMS 360.66k 4.8/s 28.03
SELECT 278.75k 3.7/s 21.66 77.29
UPDATE 74.70k 1.0/s 5.80 20.71
INSERT 7.16k 0.1/s 0.56 1.99
DELETE 58 0.0/s 0.00 0.02
REPLACE 0 0/s 0.00 0.00
Com_ 206.04k 2.7/s 16.01
set_option 124.87k 1.7/s 9.70
stmt_prepar 34.55k 0.5/s 2.68
stmt_execut 34.55k 0.5/s 2.68

__ SELECT and Sort _____________________________________________________
Scan 142.15k 1.9/s %SELECT: 51.00
Range 6.85k 0.1/s 2.46
Full join 45 0.0/s 0.02
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 43.28k 0.6/s
Sort range 18.19k 0.2/s
Sort mrg pass 674 0.0/s

Query Cache _________________________________________________________
Memory usage 2.18M of 64.00M %Used: 3.41
Block Fragmnt 18.74%
Hits 645.23k 8.6/s
Inserts 235.56k 3.1/s
Insrt:Prune 235.56k:1 3.1/s
Hit:Insert 2.74:1

__ Table Locks _________________________________________________________
Waited 0 0/s %Total: 0.00
Immediate 2.64M 35.2/s

__ Tables ______________________________________________________________
Open 583 of 1024 %Cache: 56.93
Opened 874 0.0/s

__ Connections _________________________________________________________
Max used 35 of 100 %Max: 35.00
Total 109.03k 1.5/s

__ Created Temp ________________________________________________________
Disk table 4.75k 0.1/s
Table 6.50k 0.1/s Size: 512.0M
File 1.35k 0.0/s

__ Threads _____________________________________________________________
Running 3 of 4
Cached 31 of 40 %Hit: 99.97
Created 35 0.0/s
Slow 0 0/s

__ Aborted _____________________________________________________________
Clients 2 0.0/s
Connects 2 0.0/s

__ Bytes _______________________________________________________________
Sent 1.22G 16.2k/s
Received 501.46M 6.7k/s

__ InnoDB Buffer Pool __________________________________________________
Usage 1.66G of 3.00G %Used: 55.39
Read hit 100.00%
Pages
Free 87.70k %Total: 44.61
Data 107.33k 54.59 %Drty: 0.01
Misc 1571 0.80
Latched 1 0.00
Reads 11.73G 156.2k/s
From file 50.26k 0.7/s 0.00
Ahead Rnd 7 0.0/s
Ahead Sql 464 0.0/s
Writes 4.11M 54.7/s
Flushes 190.11k 2.5/s
Wait Free 0 0/s

__ InnoDB Lock _________________________________________________________
Waits 17 0.0/s
Current 0
Time acquiring
Total 9522 ms
Average 560 ms
Max 3882 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 59.46k 0.8/s
Writes 216.32k 2.9/s
fsync 26.70k 0.4/s
Pending
Reads 0
Writes 0
fsync 0

Pages
Created 27.44k 0.4/s
Read 79.89k 1.1/s
Written 190.11k 2.5/s

Rows
Deleted 50 0.0/s
Inserted 679.99k 9.1/s
Read 16.73G 222.7k/s
Updated 74.16k 1.0/s


Questi invece sono i comandi che mi hai chiesto.

mysql> SHOW ENGINE INNODB STATUS \G
*************************** 1. row ***************************
Status:
=====================================
100305 9:55:15 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 12 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 935983, signal count 661172
Mutex spin waits 0, rounds 1138102607, OS waits 631306
RW-shared spins 313286, OS waits 47318; RW-excl spins 332871, OS waits 14016
------------------------
LATEST DETECTED DEADLOCK
------------------------
100304 17:58:17
*** (1) TRANSACTION:
TRANSACTION 0 103720813, ACTIVE 0 sec, process no 15042, OS thread id 1163913536 setting auto-inc lock
mysql tables in use 2, locked 2
LOCK WAIT 3 lock struct(s), heap size 368
MySQL thread id 42093, query id 882648 localhost crm update
INSERT INTO activities_hst
(ACTIVITIES_ID, DETT_DOCUMENT, NAME, ACCOUNT_ID, AREA, OWNER_ID, OWNER_DT, PR_GRP_ID, SR_ID, STATUS, ESITO, ESITO_DETT, PRIORITY, DESC_TEXT, EXP_DT, RECALL_DT, RECALL_HR, PROGR_ID, CREATED, CREATED_BY, UPDATED, UPDATED_BY)
values
(OLD.ID, OLD.DETT_DOCUMENT, OLD.NAME, OLD.ACCOUNT_ID, OLD.AREA, OLD.OWNER_ID, OLD.OWNER_DT, OLD.PR_GRP_ID, OLD.SR_ID, OLD.STATUS, OLD.ESITO, OLD.ESITO_DETT, OLD.PRIORITY, OLD.DESC_TEXT, OLD.EXP_DT, OLD.RECALL_DT, OLD.RECALL_HR, OLD.PROGR_ID, OLD.CREATED, OLD.CREATED_BY, OLD.UPDATED, OLD.UPDATED_BY)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `crm_fase2/activities_hst` trx id 0 103720813 lock mode AUTO-INC waiting
*** (2) TRANSACTION:
TRANSACTION 0 103720802, ACTIVE 1 sec, process no 15042, OS thread id 1162049856 fetching rows, thread declared inside InnoDB 388
mysql tables in use 2, locked 2
5342 lock struct(s), heap size 489456, undo log entries 2
MySQL thread id 42090, query id 882600 localhost crm
UPDATE activities SET OWNER_ID='c2bffed8-98a0-102b-90b9-005056801340', OWNER_DT=NOW(), UPDATED=NOW(), UPDATED_BY='c2bffed8-98a0-102b-90b9-005056801340', MODIFICATION_NUM=+1 where SR_ID='0d3d506c-77f4-102d-affc-00215e6f8903'
*** (2) HOLDS THE LOCK(S):
TABLE LOCK table `crm_fase2/activities_hst` trx id 0 103720802 lock mode AUTO-INC
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 72206 n bits 96 index `PRIMARY` of table `crm_fase2/activities` trx id 0 103720802 lock_mode X waiting
Record lock, heap no 30 PHYSICAL RECORD: n_fields 28; compact format; info bits 0
0: len 30; hex 63313832343234342d356433632d313032642d383531652d303032313565; asc c1824244-5d3c-102d-851e-00215e;...(truncated); 1: len 6; hex 000005f7db3f; asc ?;; 2: len 7; hex 000003400e17a6; asc @ ;; 3: len 8; hex 80001247ecfed23a; asc G :;; 4: len 30; hex 63326266666564382d393861302d313032622d393062392d303035303536; asc c2bffed8-98a0-102b-90b9-005056;...(truncated); 5: len 8; hex 80001247f24dfbaf; asc G M ;; 6: len 30; hex 37633030343261342d396461312d313032622d393062392d303035303536; asc 7c0042a4-9da1-102b-90b9-005056;...(truncated); 7: len 4; hex 80000002; asc ;; 8: len 1; hex 53; asc S;; 9: len 3; hex 414354; asc ACT;; 10: len 4; hex 4f50454e; asc OPEN;; 11: len 30; hex 32382f303120636869616d6f20636c69656e746520646963652068612064; asc 28/01 chiamo cliente dice ha d;...(truncated); 12: len 8; hex 80001247f77ad400; asc G z ;; 13: len 30; hex 37633030343261342d396461312d313032622d393062392d303035303536; asc 7c0042a4-9da1-102b-90b9-005056;...(truncated); 14: SQL NULL; 15: len 30; hex 61386236633265362d356262392d313032642d383238332d303032313565; asc a8b6c2e6-5bb9-102d-8283-00215e;...(truncated); 16: len 21; hex 5249434849414d41524520494c20434c49454e5445; asc RICHIAMARE IL CLIENTE;; 17: SQL NULL; 18: len 30; hex 39303961363262382d353132332d313032622d623637322d616431373661; asc 909a62b8-5123-102b-b672-ad176a;...(truncated); 19: SQL NULL; 20: SQL NULL; 21: len 30; hex 61393939356231302d356262392d313032642d383238332d303032313565; asc a9995b10-5bb9-102d-8283-00215e;...(truncated); 22: SQL NULL; 23: SQL NULL; 24: len 10; hex 41313233434531463845; asc A123CE1F8E;; 25: SQL NULL; 26: SQL NULL; 27: SQL NULL;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 103935518
Purge done for trx's n:o < 0 103935450 undo n:o < 0 0
History list length 11
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 15042, OS thread id 1166043456
MySQL thread id 112735, query id 1350627 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 103935517, COMMITTED IN MEMORY, process no 15042, OS thread id 1163114816 committing, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
, undo log entries 1
MySQL thread id 112739, query id 1350621 localhost crm update
INSERT INTO sk_product_items (ID, SK_PRODUCT_ID, PRODUCT_ID, NEEDED, REQUIRED, SENT_CLI, CARE_OF, FILE_ID, EXPIRY_DT, RECEIVED, DESC_TEXT, CREATED, CREATED_BY, UPDATED, UPDATED_BY) VALUES( 'ff82cdac-7984-102d-89d1-00215e6f8903', 'fec228d6-7984-102d-89d1-00215e6f8903', '00fda4a1-5a01-102c-9dd9-1107ed9317b7', NULL, NULL, NULL, 'Cliente', NULL, NULL, NULL, NULL, '2010-03-05 09:55:15', 'f7f0083c-9247-102b-90b9-005056801340', '2010-03-05 09:55:15', 'f7f0083c-9247-102b-90b9-005056801340' )
---TRANSACTION 0 103935516, ACTIVE 0 sec, process no 15042, OS thread id 1169770816 fetching rows, thread declared inside InnoDB 333
mysql tables in use 6, locked 0
MySQL thread id 112737, query id 1350618 localhost crm Sorting result
SELECT T0.ID AS ID, T0.CREATED AS CREATED, T0.CREATED_BY AS CREATED_BY, T9.NAME AS CREATED_BY_NAME, T0.UPDATED AS UPDATED, T0.UPDATED_BY AS UPDATED_BY, T0.MODIFICATION_NUM AS MODIFICATION_NUM, T0.ACTIVE AS ACTIVE, T0.NAME AS NAME, T0.STATUS AS STATUS, T0.DESC_TEXT AS DESC_TEXT, T0.EXP_DT AS EXP_DT, T0.OWNER_ID AS OWNER_ID, T2.NAME AS OWNER_NAME, T0.OWNER_ID AS OWNER_GROUP, T0.TYPE AS TYPE, T0.ACCOUNT_ID AS ACCOUNT_ID, TACCOUNT.NAME AS ACCOUNT_NAME, T0.AREA AS AREA, T0.RESULT AS RESULT, T0.PROGR_ID AS PROGR_ID, T0.SR_ID AS SR_ID, TSR.PROGR_ID AS SR_NAME, T0.CONTACT_PHONE AS CONTACT_PHONE, T0.CO
Trx read view will not see trx with id >= 0 103935517, sees < 0 103935499
---TRANSACTION 0 103935505, ACTIVE 1 sec, process no 15042, OS thread id 1169238336 fetching rows, thread declared inside InnoDB 347
mysql tables in use 6, locked 0
MySQL thread id 112732, query id 1350563 localhost crm Sorting result
SELECT T0.ID AS ID, T0.CREATED AS CREATED, T0.CREATED_BY AS CREATED_BY, T9.NAME AS CREATED_BY_NAME, T0.UPDATED AS UPDATED, T0.UPDATED_BY AS UPDATED_BY, T0.MODIFICATION_NUM AS MODIFICATION_NUM, T0.ACTIVE AS ACTIVE, T0.NAME AS NAME, T0.STATUS AS STATUS, T0.DESC_TEXT AS DESC_TEXT, T0.EXP_DT AS EXP_DT, T0.OWNER_ID AS OWNER_ID, T2.NAME AS OWNER_NAME, T0.OWNER_ID AS OWNER_GROUP, T0.TYPE AS TYPE, T0.ACCOUNT_ID AS ACCOUNT_ID, TACCOUNT.NAME AS ACCOUNT_NAME, T0.AREA AS AREA, T0.RESULT AS RESULT, T0.PROGR_ID AS PROGR_ID, T0.SR_ID AS SR_ID, TSR.PROGR_ID AS SR_NAME, T0.CONTACT_PHONE AS CONTACT_PHONE, T0.CO
Trx read view will not see trx with id >= 0 103935506, sees < 0 103935499
---TRANSACTION 0 103935499, ACTIVE 1 sec, process no 15042, OS thread id 1164978496
MySQL thread id 112736, query id 1350626 localhost crm
Trx read view will not see trx with id >= 0 103935500, sees < 0 103935474
--------
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 (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
62330 OS file reads, 222188 OS file writes, 27130 OS fsyncs
0 pending preads, 1 pending pwrites
3.33 reads/s, 16384 avg bytes/read, 10.67 writes/s, 0.33 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
21047 inserts, 20994 merged recs, 7139 merges
Hash table size 6375037, used cells 982845, node heap has 1575 buffer(s)
35824.93 hash searches/s, 2798.68 non-hash searches/s
---
LOG
---
Log sequence number 4 2189287849
Log flushed up to 4 2189287849
Last checkpoint at 4 2189228451
0 pending log writes, 0 pending chkp writes
100328 log i/o's done, 2.42 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 3645074986; in additional pool allocated 1048576
Buffer pool size 196608
Free buffers 84711
Database pages 110322
Modified db pages 110
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 82825, created 27497, written 193837
3.33 reads/s, 0.25 creates/s, 8.67 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
3 queries inside InnoDB, 0 queries in queue
4 read views open inside InnoDB
Main thread process no. 15042, id 1160984896, state: sleeping
Number of rows inserted 681205, updated 75966, deleted 51, read 17611969406
6.67 inserts/s, 1.00 updates/s, 0.00 deletes/s, 766986.08 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.03 sec)




vmstat -S M 10 3
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 1 0 82 401 2605 0 0 1 30 5 1 18 0 81 1 0
2 0 0 88 401 2606 0 0 27 223 1157 1191 54 1 37 8 0
1 2 0 91 401 2606 0 0 0 408 1124 676 34 0 57 9 0

Options: ReplyQuote


Subject
Views
Written By
Posted
3986
March 03, 2010 05:39AM
2668
March 04, 2010 11:16PM
Re: CPU sopra il 100% innodb engine
6145
March 05, 2010 02:59AM
3842
March 06, 2010 01:55AM


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.