MySQL Forums
Forum List  »  Performance

Re: Slower machine 100's of times faster??
Posted by: David Wynter
Date: December 16, 2005 08:14AM

I grabbed this SHOW INNODB STATUS\G while it was running the corax_event query (the 2nd one in this thread). I added a Processed flag column, but it has made no difference to the performance.

I cut out the FOREIGN key part to save space of the SATAUS off.

*************************** 1. row ***************************
Status:
=====================================
051216 14:05:58 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 17 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 177587, signal count 178388
Mutex spin waits 47638792, rounds 73812742, OS waits 14805
RW-shared spins 21759, OS waits 10235; RW-excl spins 7997, OS waits 4020


.....

------------
TRANSACTIONS
------------
Trx id counter 0 107005533
Purge done for trx's n:o < 0 107005459 undo n:o < 0 0
History list length 6
Total number of lock structs in row lock hash table 287
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 106911187, not started, process no 26089, OS thread id 1160870240
MySQL thread id 1339, query id 6645454 192.168.0.21 tLoader
---TRANSACTION 0 106695384, not started, process no 26089, OS thread id 1162074464
MySQL thread id 1022, query id 6758312 localhost root
SHOW INNODB STATUS
---TRANSACTION 0 106638354, not started, process no 26089, OS thread id 1162676576
MySQL thread id 324, query id 506593 192.168.0.21 tLoader
---TRANSACTION 0 107005449, not started, process no 26089, OS thread id 1159866720
MySQL thread id 16, query id 6756149 192.168.0.21 tLoader
---TRANSACTION 0 107005466, not started, process no 26089, OS thread id 1159666016
MySQL thread id 15, query id 6758250 192.168.0.21 tLoader
---TRANSACTION 0 106703625, not started, process no 26089, OS thread id 1159264608
MySQL thread id 11, query id 6405836 192.168.0.21 tLoader
---TRANSACTION 0 107005532, not started, process no 26089, OS thread id 1159063904
MySQL thread id 9, query id 6758302 192.168.0.21 tLoader
---TRANSACTION 0 106188049, not started, process no 26089, OS thread id 1158863200
MySQL thread id 7, query id 231 192.168.0.21 tLoader
---TRANSACTION 0 107005467, ACTIVE 1194 sec, process no 26089, OS thread id 1163479392 fetching rows, thread declared inside InnoDB 265
mysql tables in use 9, locked 9
288 lock struct(s), heap size 47088
MySQL thread id 1472, query id 6756308 192.168.0.21 tLoader Copying to tmp tableINSERT INTO linked_corax
SELECT ce1.Event_Id, MAX(ce2.Event_Id) FROM
corax_event ce1, corax_event ce2
WHERE ce1.Event_Id > ce2.Event_Id
AND ce1.Cap_Chng_Market_Lvl_Id = ce2.Cap_Chng_Market_Lvl_Id
AND ce1.Processed = 1
GROUP BY ce1.Event_Id
UNION
SELECT ce1.Event_Id, MAX(ce2.Event_Id) FROM
corax_event ce1, corax_event ce2
WHERE ce1.Event_Id > ce2.Event_Id
AND ce1.Div_Market_Lvl_Id = ce2.Div_Market_Lvl_Id
AND ce1.Processed = 1
GROUP BY ce1.Event_Id
UNION
SELECT ce1.Event_Id, MAX(ce2.Event_Id) FROM
corax_event ce1, corax_event ce2
WHERE ce1.Event_Id > ce2.Event_Id
AND (ce1.ASSET
--------
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
215469 OS file reads, 952035 OS file writes, 888923 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 5, seg size 7, is empty
Ibuf for space 0: size 1, free list len 5, seg size 7,
11120 inserts, 11120 merged recs, 1331 merges
Hash table size 3112859, used cells 2089359, node heap has 5110 buffer(s)
321484.21 hash searches/s, 1841.54 non-hash searches/s
---
LOG
---
Log sequence number 7 3818756835
Log flushed up to 7 3818756835
Last checkpoint at 7 3818756835
0 pending log writes, 0 pending chkp writes
881974 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1754059699; in additional pool allocated 15946752
Buffer pool size 96000
Free buffers 1
Database pages 90887
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 406463, created 18307, written 282304
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 26089, id 1157658976, state: sleeping
Number of rows inserted 2264390, updated 688877, deleted 408394, read 59582393480.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 323325.80 reads/s
----------------------------

David

Options: ReplyQuote


Subject
Views
Written By
Posted
2196
December 09, 2005 11:45AM
1428
December 09, 2005 11:51AM
1502
December 09, 2005 11:53AM
1577
December 09, 2005 12:11PM
1364
December 09, 2005 12:12PM
1359
December 09, 2005 12:33PM
1392
December 10, 2005 10:50AM
1461
December 12, 2005 05:14AM
1391
December 13, 2005 03:16AM
1531
December 14, 2005 11:17PM
Re: Slower machine 100's of times faster??
1745
December 16, 2005 08:14AM
1494
December 16, 2005 11:46AM
1421
December 26, 2005 08:32PM
1411
December 14, 2005 10:47PM
1425
December 23, 2005 12:29PM


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.