Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

migrated from stackoverflow.com Jun 3 '13 at 19:29

This question came from our site for professional and enthusiast programmers.

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.