There is only one table in MySQL that has two columns like id(int) and data(blob) And, the problem I've faced is that it looks like there's something wrong with insert/update operations. For some reason, some records look like not updated correctly.
Here is the part of innodb status:
mysql> show engine innodb status \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
130603 5:06:23 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 33 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 253076 1_second, 253011 sleeps, 25303 10_second, 85 background, 85 flush
srv_master_thread log flush and writes: 255521
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 23139745, signal count 12127191
Mutex spin waits 26081499, rounds 774455467, OS waits 20345589
RW-shared spins 1316296, rounds 59544350, OS waits 1919181
RW-excl spins 291699, rounds 12292400, OS waits 361778
Spin rounds per wait: 29.69 mutex, 45.24 RW-shared, 42.14 RW-excl
--------
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 (read thread)
I/O thread 7 state: waiting for i/o request (read thread)
I/O thread 8 state: waiting for i/o request (read thread)
I/O thread 9 state: waiting for i/o request (read thread)
I/O thread 10 state: waiting for i/o request (write thread)
I/O thread 11 state: waiting for i/o request (write thread)
I/O thread 12 state: waiting for i/o request (write thread)
I/O thread 13 state: waiting for i/o request (write thread)
I/O thread 14 state: waiting for i/o request (write thread)
I/O thread 15 state: waiting for i/o request (write thread)
I/O thread 16 state: waiting for i/o request (write thread)
I/O thread 17 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 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
1427452 OS file reads, 117919816 OS file writes, 666542 OS fsyncs
3.06 reads/s, 16384 avg bytes/read, 413.93 writes/s, 2.30 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 17700827, node heap has 3193 buffer(s)
149.21 hash searches/s, 270.69 non-hash searches/s
---
LOG
---
Log sequence number 23973842024172
Log flushed up to 23973841102477
Last checkpoint at 23973477520334
Max checkpoint age 678766142
Checkpoint age target 657554701
Modified age 362252074
Checkpoint age 364503838
0 pending log writes, 0 pending chkp writes
108960780 log i/o's done, 387.26 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 8824815616; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
Adaptive hash index 193924944 (141606616 + 52318328)
Page hash 8851208 (buffer pool 0 only)
Dictionary cache 35445060 (35403184 + 41876)
File system 83992 (82672 + 1320)
Lock system 21349080 (21250568 + 98512)
Recovery system 0 (0 + 0)
Dictionary memory allocated 41876
Buffer pool size 524287
Buffer pool size, bytes 8589918208
Free buffers 1
Database pages 521093
Old database pages 192336
Modified db pages 13035
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1392808, not young 0
5.33 youngs/s, 0.00 non-youngs/s
Pages read 1474660, created 27547, written 10564200
3.06 reads/s, 0.12 creates/s, 31.64 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 1 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 521093, unzip_LRU len: 0
I/O sum[157]:cur[4], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
---OLDEST VIEW---
Normal read view
Read view low limit trx n:o 2BB606DDA
Read view up limit trx id 2BB606DD9
Read view low limit trx id 2BB606DDA
Read view individually stored trx ids:
Read view trx id 2BB606DD9
-----------------
Main thread id 25, state: flushing log
Number of rows inserted 139727, updated 54896533, deleted 0, read 56269276
0.36 inserts/s, 194.18 updates/s, 0.00 deletes/s, 200.02 reads/s
Here is the info for innodb mutex:
mysql> show engine innodb mutex;
+--------+---------------------------------+-------------------+
| Type | Name | Status |
+--------+---------------------------------+-------------------+
| InnoDB | &purge_sys->bh_mutex | os_waits=3 |
| InnoDB | &rseg->mutex | os_waits=69 |
| InnoDB | &rseg->mutex | os_waits=76 |
| InnoDB | &rseg->mutex | os_waits=61 |
| InnoDB | &rseg->mutex | os_waits=64 |
| InnoDB | &rseg->mutex | os_waits=61 |
| InnoDB | &rseg->mutex | os_waits=66 |
| InnoDB | &rseg->mutex | os_waits=81 |
| InnoDB | &rseg->mutex | os_waits=82 |
| InnoDB | &rseg->mutex | os_waits=72 |
| InnoDB | &rseg->mutex | os_waits=65 |
| InnoDB | &rseg->mutex | os_waits=75 |
| InnoDB | &rseg->mutex | os_waits=91 |
| InnoDB | &rseg->mutex | os_waits=77 |
| InnoDB | &rseg->mutex | os_waits=65 |
| InnoDB | &rseg->mutex | os_waits=102 |
| InnoDB | &rseg->mutex | os_waits=94 |
| InnoDB | &rseg->mutex | os_waits=112 |
| InnoDB | &rseg->mutex | os_waits=61 |
| InnoDB | &rseg->mutex | os_waits=82 |
| InnoDB | &rseg->mutex | os_waits=81 |
| InnoDB | &rseg->mutex | os_waits=62 |
| InnoDB | &rseg->mutex | os_waits=54 |
| InnoDB | &rseg->mutex | os_waits=120 |
| InnoDB | &rseg->mutex | os_waits=103 |
| InnoDB | &rseg->mutex | os_waits=57 |
| InnoDB | &rseg->mutex | os_waits=74 |
| InnoDB | &rseg->mutex | os_waits=94 |
| InnoDB | &rseg->mutex | os_waits=101 |
| InnoDB | &rseg->mutex | os_waits=80 |
| InnoDB | &rseg->mutex | os_waits=65 |
| InnoDB | &rseg->mutex | os_waits=84 |
| InnoDB | &rseg->mutex | os_waits=90 |
| InnoDB | &rseg->mutex | os_waits=98 |
| InnoDB | &rseg->mutex | os_waits=85 |
| InnoDB | &rseg->mutex | os_waits=65 |
| InnoDB | &rseg->mutex | os_waits=82 |
| InnoDB | &rseg->mutex | os_waits=71 |
| InnoDB | &rseg->mutex | os_waits=64 |
| InnoDB | &rseg->mutex | os_waits=81 |
| InnoDB | &rseg->mutex | os_waits=77 |
| InnoDB | &rseg->mutex | os_waits=72 |
| InnoDB | &rseg->mutex | os_waits=76 |
| InnoDB | &rseg->mutex | os_waits=99 |
| InnoDB | &rseg->mutex | os_waits=64 |
| InnoDB | &rseg->mutex | os_waits=101 |
| InnoDB | &rseg->mutex | os_waits=73 |
| InnoDB | &rseg->mutex | os_waits=71 |
| InnoDB | &rseg->mutex | os_waits=89 |
| InnoDB | &rseg->mutex | os_waits=79 |
| InnoDB | &rseg->mutex | os_waits=85 |
| InnoDB | &rseg->mutex | os_waits=74 |
| InnoDB | &rseg->mutex | os_waits=68 |
| InnoDB | &rseg->mutex | os_waits=74 |
| InnoDB | &rseg->mutex | os_waits=71 |
| InnoDB | &rseg->mutex | os_waits=60 |
| InnoDB | &rseg->mutex | os_waits=87 |
| InnoDB | &rseg->mutex | os_waits=90 |
| InnoDB | &rseg->mutex | os_waits=75 |
| InnoDB | &rseg->mutex | os_waits=71 |
| InnoDB | &rseg->mutex | os_waits=116 |
| InnoDB | &rseg->mutex | os_waits=120 |
| InnoDB | &rseg->mutex | os_waits=118 |
| InnoDB | &rseg->mutex | os_waits=118 |
| InnoDB | &rseg->mutex | os_waits=116 |
| InnoDB | &rseg->mutex | os_waits=104 |
| InnoDB | &rseg->mutex | os_waits=95 |
| InnoDB | &rseg->mutex | os_waits=81 |
| InnoDB | &rseg->mutex | os_waits=81 |
| InnoDB | &rseg->mutex | os_waits=92 |
| InnoDB | &rseg->mutex | os_waits=84 |
| InnoDB | &rseg->mutex | os_waits=77 |
| InnoDB | &rseg->mutex | os_waits=101 |
| InnoDB | &rseg->mutex | os_waits=95 |
| InnoDB | &rseg->mutex | os_waits=80 |
| InnoDB | &rseg->mutex | os_waits=60 |
| InnoDB | &rseg->mutex | os_waits=58 |
| InnoDB | &rseg->mutex | os_waits=101 |
| InnoDB | &rseg->mutex | os_waits=85 |
| InnoDB | &rseg->mutex | os_waits=80 |
| InnoDB | &rseg->mutex | os_waits=92 |
| InnoDB | &rseg->mutex | os_waits=78 |
| InnoDB | &rseg->mutex | os_waits=68 |
| InnoDB | &rseg->mutex | os_waits=94 |
| InnoDB | &rseg->mutex | os_waits=76 |
| InnoDB | &rseg->mutex | os_waits=82 |
| InnoDB | &rseg->mutex | os_waits=74 |
| InnoDB | &rseg->mutex | os_waits=69 |
| InnoDB | &rseg->mutex | os_waits=97 |
| InnoDB | &rseg->mutex | os_waits=68 |
| InnoDB | &rseg->mutex | os_waits=90 |
| InnoDB | &rseg->mutex | os_waits=76 |
| InnoDB | &rseg->mutex | os_waits=72 |
| InnoDB | &rseg->mutex | os_waits=81 |
| InnoDB | &rseg->mutex | os_waits=78 |
| InnoDB | &rseg->mutex | os_waits=70 |
| InnoDB | &rseg->mutex | os_waits=93 |
| InnoDB | &rseg->mutex | os_waits=68 |
| InnoDB | &rseg->mutex | os_waits=73 |
| InnoDB | &rseg->mutex | os_waits=79 |
| InnoDB | &rseg->mutex | os_waits=73 |
| InnoDB | &rseg->mutex | os_waits=95 |
| InnoDB | &rseg->mutex | os_waits=76 |
| InnoDB | &rseg->mutex | os_waits=70 |
| InnoDB | &rseg->mutex | os_waits=91 |
| InnoDB | &rseg->mutex | os_waits=82 |
| InnoDB | &rseg->mutex | os_waits=70 |
| InnoDB | &rseg->mutex | os_waits=77 |
| InnoDB | &rseg->mutex | os_waits=54 |
| InnoDB | &rseg->mutex | os_waits=75 |
| InnoDB | &rseg->mutex | os_waits=75 |
| InnoDB | &rseg->mutex | os_waits=86 |
| InnoDB | &rseg->mutex | os_waits=87 |
| InnoDB | &rseg->mutex | os_waits=82 |
| InnoDB | &rseg->mutex | os_waits=91 |
| InnoDB | &rseg->mutex | os_waits=90 |
| InnoDB | &rseg->mutex | os_waits=95 |
| InnoDB | &rseg->mutex | os_waits=75 |
| InnoDB | &rseg->mutex | os_waits=102 |
| InnoDB | &rseg->mutex | os_waits=83 |
| InnoDB | &rseg->mutex | os_waits=107 |
| InnoDB | &rseg->mutex | os_waits=95 |
| InnoDB | &rseg->mutex | os_waits=59 |
| InnoDB | &rseg->mutex | os_waits=97 |
| InnoDB | &rseg->mutex | os_waits=104 |
| InnoDB | &rseg->mutex | os_waits=88 |
| InnoDB | &rseg->mutex | os_waits=98 |
| InnoDB | &rseg->mutex | os_waits=76 |
| InnoDB | &dict_sys->mutex | os_waits=1 |
| InnoDB | &log_sys->log_flush_order_mutex | os_waits=2630 |
| InnoDB | &log_sys->mutex | os_waits=17853596 |
| InnoDB | &buf_pool->flush_list_mutex | os_waits=1 |
| InnoDB | &buf_pool->free_list_mutex | os_waits=28 |
| InnoDB | &buf_pool->LRU_list_mutex | os_waits=68802 |
| InnoDB | &buf_pool->mutex | os_waits=34 |
| InnoDB | &fil_system->mutex | os_waits=275 |
| InnoDB | &kernel_mutex | os_waits=2723277 |
| InnoDB | combined &block->mutex | os_waits=2071 |
| InnoDB | &new_index->lock | os_waits=1985918 |
| InnoDB | &space->latch | os_waits=261758 |
| InnoDB | &log_sys->checkpoint_lock | os_waits=26258 |
| InnoDB | btr_search_latch_part[i] | os_waits=3169 |
| InnoDB | &buf_pool->page_hash_latch | os_waits=11161 |
| InnoDB | combined &block->lock | os_waits=24317 |
+--------+---------------------------------+-------------------+
144 rows in set (0.06 sec)
I have some questions:
1) Does the innodb status look good?
2) Is there a way to figure out records are being updated correctly or not?
3) I have only one table with 10M records. And, there are apprx. 260 concurrent connections. Should I tune up my.cnf for better performance?
I would appreciate any help and/or any hints!
Thank you.