2

I have been struggling with a slow update query. Have already tried using indexes and other query optimization techniques. I have been using MYSQL database with INNODB engine.

The issue is that, sometimes the same query on the same set of data is executed very fast but some time it takes as much as 5 min. to execute. I am beginning to think this might be because of mysql misconfiguration. Kindly comment based on the information below:

This query is updating a column in table1 by joining 3 other tables on two different databases. Following re the table sizes that are being joined:

S No    Table Name  Database    Size
=====================================
1   Table1  Database 1  0.01 mb
2   Table2  Database 2  100 mb
3   Table3  Database 1  0.01 mb
4   Table4  Database 2  5 mb


UPDATE IGNORE database1.table1, database2.table2, database2.table4
            ,database1.table3
SET     database1.table1.userID=database2.table2.user_id
WHERE   database1.table1.unUsedid=database2.table2.id
AND     database1.table1.userid<>database2.table2.userid
AND     database2.table4.id=database2.table2.userid
AND     database2.table2.id=database1.table3.id
AND     database2.table2.status=1
AND     database2.table4.status=1

Following is the extract of the INNODB engine status output on running this command. I found the read operations going extremely high when this query runs.

--------
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: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (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: 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
2787619 OS file reads, 42910052 OS file writes, 29490108 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 57.84 writes/s, 42.27 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 92862, seg size 92864, 26062 merges
merged operations:
 insert 292696, delete mark 4632491, delete 175711
discarded operations:
 insert 0, delete mark 10321, delete 0
Hash table size 4425293, node heap has 1 buffer(s)
0.00 hash searches/s, 112.57 non-hash searches/s
---
LOG
---
Log sequence number 543149010523
Log flushed up to   543149010523
Last checkpoint at  543148607470
0 pending log writes, 0 pending chkp writes
28836197 log i/o's done, 39.61 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2197815296; in additional pool allocated 0
Dictionary memory allocated 13763309
Buffer pool size   131071
Free buffers       1
Database pages     131032
Old database pages 48349
Modified db pages  292
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3422551, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2785862, created 2360297, written 13635885
0.00 reads/s, 0.00 creates/s, 17.81 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: 131032, unzip_LRU len: 0
I/O sum[908]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 12068, id 140452093859584, state: sleeping
Number of rows inserted 217161950, updated 31316722, deleted 9246496, read 30381374191
0.38 inserts/s, 39.50 updates/s, 0.38 deletes/s, 2025027.50 reads/s
----------------------------

Following is data size based on @Rolando’s query:

mysql> SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(FORMAT(
-> B.DSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",
-> CONCAT(LPAD(FORMAT(B.ISize/POWER(1024,pw),3),17,' '),' ',
-> SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",CONCAT(LPAD(FORMAT(B.TSize/
-> POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"
-> FROM (SELECT engine,SUM(data_length) DSize,
-> SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables
-> WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND
-> engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 2 pw) A ORDER BY TSize;
+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| CSV            |             0.000 MB |             0.000 MB |             0.000 MB |
| MEMORY         |             0.121 MB |             0.000 MB |             0.121 MB |
| MyISAM         |            20.886 MB |             0.312 MB |            21.199 MB |
| InnoDB         |         2,054.281 MB |         1,993.156 MB |         4,047.438 MB |
| Total          |         2,075.288 MB |         1,993.469 MB |         4,068.757 MB |
+----------------+----------------------+----------------------+----------------------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.