Re: InnoDB Writes blocking Reads

[kernel]

Andy,
Can you send me the table layout and the script ? I'd like to try it 
here on one my test systems running 4.0.23.

walt

Andy McCurdy wrote:

>I haven't seen any other replies as of yet.  After looking closer at
>the innodb status dump, the last known deadlock occured several days
>ago... so I don't think deadlocks are my main issue..
>
>The confusing part of this is that when writes happen, it seems the
>entire database is locking.  To test this, I created a new inno table
>with a single column on my production env.  I used a script insert a
>record into this table every second.  When any production write
>happened, this script would block for 4-5 seconds until the other
>write finished.  The only things this test inno table had in common
>with our prod tables were:
>
>- in the same database
>- in the same datafile
>- governed by the same mysqld server settings
>
>No other query was touching this test table.  So one of those three
>things seems to be the cause... I just don't know what to do at this
>point.
>
>-andy
>
>On 4/22/05, kernel <kernel@stripped> wrote:
>  
>
>>Andy McCurdy wrote:
>>
>>    
>>
>>>I forgot to mention:  we're running mysql version 4.0.23-standard-log --
>>>Official MySQL-standard binary.  Here's the innodb status output during a
>>>problematic period.
>>>
>>>=====================================
>>>050421 15:29:46 INNODB MONITOR OUTPUT
>>>=====================================
>>>Per second averages calculated from the last 26 seconds
>>>----------
>>>SEMAPHORES
>>>----------
>>>OS WAIT ARRAY INFO: reservation count 1330878, signal count 1241079
>>>Mutex spin waits 16157526, rounds 105045131, OS waits 692467
>>>RW-shared spins 691802, OS waits 328867; RW-excl spins 91394, OS waits 34657
>>>------------------------
>>>LATEST DETECTED DEADLOCK
>>>------------------------
>>>050418 14:46:01
>>>*** (1) TRANSACTION:
>>>TRANSACTION 0 52471953, ACTIVE 0 sec, process no 5468, OS thread id
>>>2625171473 starting index read
>>>mysql tables in use 1, locked 1
>>>LOCK WAIT 2 lock struct(s), heap size 320
>>>MySQL thread id 7832890, query id 51121416 host1 10.15.0.76 username
>>>Updating
>>>UPDATE pm_message SET receiver_deleted = 1 WHERE id = 2241787
>>>*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
>>>RECORD LOCKS space id 0 page no 306793 n bits 120 index `PRIMARY` of
>>>table `gne/pm_message` trx id 0 52471953 lock_mode X waiting
>>>Record lock, heap no 31 PHYSICAL RECORD: n_fields 12; 1-byte offs
>>>FALSE; info bits 32
>>>0: len 4; hex 002234fb; asc  "4 ;; 1: len 6; hex 00000320a88f; asc
>>> ;; 2: len 7; hex 000003801315f4; asc        ;; 3: len 30; hex
>>>3139343432333
>>>33535353937373535313334383739393833323236393236; asc
>>>194423355597755134879983226926;...(truncated); 4: len 30; hex
>>>3230333739383339383930343339303733
>>>30363132343136363636363637; asc
>>>203798398904390730612416666667;...(truncated); 5: len 17; hex
>>>417474656e74696f6e204d656d62657273; asc Attention Membe
>>>rs;; 6: len 30; hex
>>>492077616e7420796f7520746f20737461727420706f7374696e67206865; asc I
>>>want you to start posting he;...(truncated); 7: len 13; hex 3
>>>231362e37372e3139322e3135; asc 216.77.192.15;; 8: len 1; hex 81; asc
>>>;; 9: len 1; hex 81; asc  ;; 10: len 8; hex 8000123c5a0d1524; asc
>>><Z  $;; 11
>>>: len 8; hex 8000123c59fd8369; asc    <Y  i;;
>>>
>>>*** (2) TRANSACTION:
>>>TRANSACTION 0 52471952, ACTIVE 0 sec, process no 5476, OS thread id
>>>2625204248 starting index read, thread declared inside InnoDB 0
>>>mysql tables in use 1, locked 1
>>>3 lock struct(s), heap size 320
>>>MySQL thread id 7832891, query id 51121414 host2 10.15.0.71 username
>>>Updating
>>>UPDATE pm_message SET receiver_deleted = 1 WHERE id = 2241787
>>>*** (2) HOLDS THE LOCK(S):
>>>RECORD LOCKS space id 0 page no 306793 n bits 120 index `PRIMARY` of
>>>table `gne/pm_message` trx id 0 52471952 lock_mode X locks rec but not
>>>gap
>>>Record lock, heap no 31 PHYSICAL RECORD: n_fields 12; 1-byte offs
>>>FALSE; info bits 32
>>>0: len 4; hex 002234fb; asc  "4 ;; 1: len 6; hex 00000320a88f; asc
>>> ;; 2: len 7; hex 000003801315f4; asc        ;; 3: len 30; hex
>>>3139343432333
>>>33535353937373535313334383739393833323236393236; asc
>>>194423355597755134879983226926;...(truncated); 4: len 30; hex
>>>3230333739383339383930343339303733
>>>30363132343136363636363637; asc
>>>203798398904390730612416666667;...(truncated); 5: len 17; hex
>>>417474656e74696f6e204d656d62657273; asc Attention Membe
>>>rs;; 6: len 30; hex
>>>492077616e7420796f7520746f20737461727420706f7374696e67206865; asc I
>>>want you to start posting he;...(truncated); 7: len 13; hex 3
>>>231362e37372e3139322e3135; asc 216.77.192.15;; 8: len 1; hex 81; asc
>>>;; 9: len 1; hex 81; asc  ;; 10: len 8; hex 8000123c5a0d1524; asc
>>><Z  $;; 11
>>>: len 8; hex 8000123c59fd8369; asc    <Y  i;;
>>>
>>>*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
>>>RECORD LOCKS space id 0 page no 306793 n bits 120 index `PRIMARY` of
>>>table `gne/pm_message` trx id 0 52471952 lock_mode X waiting
>>>Record lock, heap no 31 PHYSICAL RECORD: n_fields 12; 1-byte offs
>>>FALSE; info bits 32
>>>0: len 4; hex 002234fb; asc  "4 ;; 1: len 6; hex 00000320a88f; asc
>>> ;; 2: len 7; hex 000003801315f4; asc        ;; 3: len 30; hex
>>>3139343432333
>>>33535353937373535313334383739393833323236393236; asc
>>>194423355597755134879983226926;...(truncated); 4: len 30; hex
>>>3230333739383339383930343339303733
>>>30363132343136363636363637; asc
>>>203798398904390730612416666667;...(truncated); 5: len 17; hex
>>>417474656e74696f6e204d656d62657273; asc Attention Membe
>>>rs;; 6: len 30; hex
>>>492077616e7420796f7520746f20737461727420706f7374696e67206865; asc I
>>>want you to start posting he;...(truncated); 7: len 13; hex 3
>>>231362e37372e3139322e3135; asc 216.77.192.15;; 8: len 1; hex 81; asc
>>>;; 9: len 1; hex 81; asc  ;; 10: len 8; hex 8000123c5a0d1524; asc
>>><Z  $;; 11
>>>: len 8; hex 8000123c59fd8369; asc    <Y  i;;
>>>
>>>*** WE ROLL BACK TRANSACTION (2)
>>>------------
>>>TRANSACTIONS
>>>------------
>>>Trx id counter 0 58483796
>>>Purge done for trx's n:o < 0 58483765 undo n:o < 0 0
>>>Total number of lock structs in row lock hash table 0
>>>LIST OF TRANSACTIONS FOR EACH SESSION:
>>>---TRANSACTION 0 0, not started, process no 13939, OS thread id 46686284
>>>MySQL thread id 12345108, query id 80171520 localhost mccurdya
>>>show innodb status
>>>---TRANSACTION 0 58483795, not started, process no 13872, OS thread id
>>>46432286
>>>mysql tables in use 1, locked 1
>>>MySQL thread id 12345042, query id 80171208 host3 10.15.0.86 username update
>>>INSERT INTO review (ref_type_id, ref_id, parent_ref_type_id,
>>>parent_ref_id, classification_id, user_id, ip_address, added_date,
>>>last_modified_date, v
>>>isible, score, max_score, summary, review)
>>>                  values ('1', '4643', '1', '4643', '13',
>>>'20551867262481444235511631583140', '68.220.165.213', now(),
>>>--------
>>>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
>>>13680715 OS file reads, 1685444 OS file writes, 643001 OS fsyncs
>>>8.65 reads/s, 17184 avg bytes/read, 1.62 writes/s, 0.81 fsyncs/s
>>>-------------------------------------
>>>INSERT BUFFER AND ADAPTIVE HASH INDEX
>>>-------------------------------------
>>>Ibuf for space 0: size 1, free list len 93, seg size 95,
>>>104033 inserts, 104033 merged recs, 89411 merges
>>>Hash table size 2212699, used cells 499153, node heap has 556 buffer(s)
>>>25.61 hash searches/s, 62.77 non-hash searches/s
>>>---
>>>LOG
>>>---
>>>Log sequence number 2 1464330013
>>>Log flushed up to   2 1464330013
>>>Last checkpoint at  2 1464330013
>>>0 pending log writes, 0 pending chkp writes
>>>485488 log i/o's done, 0.58 log i/o's/second
>>>----------------------
>>>BUFFER POOL AND MEMORY
>>>----------------------
>>>Total memory allocated 578377246; in additional pool allocated 1045248
>>>Buffer pool size   32768
>>>Free buffers       0
>>>Database pages     32212
>>>Modified db pages  0
>>>Pending reads 0
>>>Pending writes: LRU 0, flush list 0, single page 0
>>>Pages read 16075726, created 138074, written 1683729
>>>9.08 reads/s, 0.00 creates/s, 1.04 writes/s
>>>Buffer pool hit rate 973 / 1000
>>>--------------
>>>ROW OPERATIONS
>>>--------------
>>>0 queries inside InnoDB, 0 queries in queue
>>>Main thread process no. 13583, id 28680, state: sleeping
>>>Number of rows inserted 3104145, updated 422273, deleted 285163, read
>>>113759794
>>>0.15 inserts/s, 0.19 updates/s, 0.00 deletes/s, 51.15 reads/s
>>>----------------------------
>>>END OF INNODB MONITOR OUTPUT
>>>============================
>>>"Andy McCurdy" <andy.mccurdy@stripped> wrote in message
>>>news:d48m8o$7ja$1@ style="color:#666">stripped...
>>>
>>>
>>>      
>>>
>>>>I've been seeing some weirdness w/ MySQL and InnoDB over the past few
>>>>days. I have a database that's entirely using InnoDB.  The database is
>>>>roughly 4.5G (one datafile) and contains a little over 50 tables.  The
> web
>>>>applications that hit this database do about 85-90% reads and 10-15%
>>>>writes. Whenever a write comes in, it seems that subsequent reads get
>>>>blocked to the _ENTIRE DATABASE_.  What really confuses me is that reads
>>>>even to other tables that aren't any way involved with the table that's
>>>>being written to are waiting until the write completes.  I do not use any
>>>>foreign key relationships which could block a parent row.
>>>>
>>>>Whenever I run "show processlist", everything looks fine until a write
>>>>comes in.  Reads are answered < 1 sec, and generally don't even appear
> on
>>>>the processlist.  However, as soon as a write hits, all of the reads are
>>>>in a state of "NULL" until the write completes.  This problem is
>>>>compounded when several different writes happen from different
> connections
>>>>at the same time. It leads to the MySQL server blocking long enough that
>>>>the number of reads in the queue exceed the number of max connections.
>>>>Once the writes happen, all of the read queries in the queue get answered
>>>>and the normal processing continues.
>>>>
>>>>I've included two samples from the "show processlist" command.  They were
>>>>taken approx. 1 second apart and show the growing amount of reads when
>>>>writes are in the queue.  I've removed the User and Host columns as they
>>>>don't seem to be relevant as well as trimmed the state column down.  I've
>>>>also included the my.cnf config file and an output of "show status".
>>>>
>>>>Additionally, I'm running Redhat 7.3 - kernel ver. 2.4.20-28.7bigmem #1
>>>>SMP
>>>>
>>>>Any help on eliminating this bottleneck would be greatly appreciated!
>>>>Thanks.
>>>>-andy
>>>>
>>>>
>>>>=================================
>>>>process list 1
>>>>=================================
>>>>
>>>>
>>>>+----------+------+-------------+--------+---------+------------------------------------
>>>>| Id       | db   | Command     | Time   | State   | Info
>>>>+----------+------+-------------+--------+---------+------------------------------------
>>>>|  2972920 | NULL | Binlog Dump | 463909 | Has sen | NULL
>>>>|  2972930 | NULL | Binlog Dump | 463909 | Has sen | NULL
>>>>| 10954259 | NULL | Query       | 0      | NULL    | show processlist
>>>>| 10962986 | gne  | Sleep       | 4      |         | NULL
>>>>| 10962987 | gne  | Sleep       | 4      |         | NULL
>>>>| 10962997 | gne  | Query       | 3      | NULL    | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963003 | gne  | Query       | 2      | NULL    | SELECT * FROM clan
>>>>WHERE id='142'
>>>>| 10963018 | gne  | Query       | 3      | NULL    | SELECT * FROM clan
>>>>WHERE id='274'
>>>>| 10963019 | gne  | Query       | 3      | update  | INSERT INTO rating
>>>>(ref_type_id, re
>>>>| 10963020 | gne  | Query       | 2      | NULL    | SELECT * FROM clan
>>>>WHERE id='55'
>>>>| 10963021 | gne  | Query       | 3      | NULL    | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963022 | gne  | Query       | 3      | NULL    | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963023 | gne  | Query       | 3      | NULL    | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963024 | gne  | Query       | 2      | NULL    | SELECT count(*) AS
>>>>count FROM pm_me
>>>>| 10963025 | gne  | Query       | 2      | NULL    | SELECT * FROM
> contact
>>>>WHERE user_id
>>>>| 10963026 | gne  | Query       | 2      | NULL    | SELECT * FROM clan
>>>>WHERE id='729'
>>>>| 10963027 | gne  | Query       | 2      | NULL    | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963028 | gne  | Query       | 2      | NULL    | select r.review_id
>>>>        
>>>>
>>>>from review r wh
>>>      
>>>
>>>>| 10963029 | gne  | Query       | 2      | NULL    | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963030 | gne  | Query       | 2      | update  | INSERT INTO
>>>>pm_message SET id=NULL,
>>>>| 10963031 | gne  | Query       | 2      | NULL    | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963032 | gne  | Query       | 2      | NULL    | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963033 | gne  | Query       | 2      | NULL    | select r.review_id
>>>>        
>>>>
>>>>from review r wh
>>>      
>>>
>>>>| 10963034 | gne  | Query       | 2      | NULL    | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963035 | gne  | Query       | 2      | NULL    | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963036 | gne  | Query       | 2      | NULL    | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963037 | gne  | Query       | 2      | NULL    | SELECT c.* FROM
>>>>clan_pid cp, clan c
>>>>| 10963038 | gne  | Query       | 2      | NULL    | SELECT * FROM
>>>>pm_message WHERE id =
>>>>| 10963039 | gne  | Query       | 2      | NULL    | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963041 | gne  | Query       | 2      | NULL    | SELECT c.* FROM
>>>>clan_pid cp, clan c
>>>>| 10963042 | gne  | Query       | 1      | NULL    | select r.review_id
>>>>        
>>>>
>>>>from review r wh
>>>      
>>>
>>>>| 10963043 | gne  | Query       | 1      | NULL    | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963045 | gne  | Query       | 1      | NULL    | SELECT count(*) AS
>>>>count FROM pm_me
>>>>| 10963046 | gne  | Query       | 1      | NULL    | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963047 | gne  | Query       | 1      | NULL    | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963048 | gne  | Query       | 1      | NULL    | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963049 | gne  | Query       | 1      | NULL    | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963050 | gne  | Query       | 1      | NULL    | select r.review_id
>>>>        
>>>>
>>>>from review r wh
>>>      
>>>
>>>>| 10963051 | gne  | Query       | 1      | NULL    | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963052 | gne  | Query       | 1      | NULL    | SELECT * FROM clan
>>>>WHERE id='331'
>>>>| 10963053 | gne  | Query       | 1      | NULL    | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963054 | gne  | Query       | 1      | NULL    | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963055 | gne  | Query       | 1      | NULL    | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963056 | gne  | Query       | 1      | NULL    | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963057 | gne  | Query       | 1      | NULL    | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963058 | gne  | Query       | 1      | NULL    | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963059 | gne  | Query       | 1      | NULL    | SELECT c.* FROM
>>>>clan_pid cp, clan c
>>>>+----------+------+-------------+--------+---------+------------------------------------
>>>>
>>>>
>>>>=================================
>>>>process list 2
>>>>=================================
>>>>
>>>>
>>>>+----------+------+-------------+--------+--------+------------------------------------
>>>>| Id       | db   | Command     | Time   | State  | Info
>>>>+----------+------+-------------+--------+--------+------------------------------------
>>>>|  2972920 | NULL | Binlog Dump | 463909 | Has sen| NULL
>>>>|  2972930 | NULL | Binlog Dump | 463909 | Has sen| NULL
>>>>| 10954259 | NULL | Query       | 0      | NULL   | show processlist
>>>>| 10962986 | gne  | Sleep       | 4      |        | NULL
>>>>| 10962987 | gne  | Sleep       | 4      |        | NULL
>>>>| 10962997 | gne  | Query       | 3      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963003 | gne  | Query       | 2      | NULL   | SELECT * FROM clan
>>>>WHERE id='142'
>>>>| 10963018 | gne  | Query       | 3      | NULL   | SELECT * FROM clan
>>>>WHERE id='274'
>>>>| 10963019 | gne  | Query       | 3      | update | INSERT INTO rating
>>>>(ref_type_id, re
>>>>| 10963020 | gne  | Query       | 2      | NULL   | SELECT * FROM clan
>>>>WHERE id='55'
>>>>| 10963021 | gne  | Query       | 3      | NULL   | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963022 | gne  | Query       | 3      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963023 | gne  | Query       | 3      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963024 | gne  | Query       | 2      | NULL   | SELECT count(*) AS
>>>>count FROM pm_me
>>>>| 10963025 | gne  | Query       | 2      | NULL   | SELECT * FROM contact
>>>>WHERE user_id
>>>>| 10963026 | gne  | Query       | 2      | NULL   | SELECT * FROM clan
>>>>WHERE id='729'
>>>>| 10963027 | gne  | Query       | 2      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963028 | gne  | Query       | 2      | NULL   | select r.review_id
>>>>        
>>>>
>>>>from review r wh
>>>      
>>>
>>>>| 10963029 | gne  | Query       | 2      | NULL   | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963030 | gne  | Query       | 2      | update | INSERT INTO
> pm_message
>>>>SET id=NULL,
>>>>| 10963031 | gne  | Query       | 2      | NULL   | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963032 | gne  | Query       | 2      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963033 | gne  | Query       | 2      | NULL   | select r.review_id
>>>>        
>>>>
>>>>from review r wh
>>>      
>>>
>>>>| 10963034 | gne  | Query       | 2      | NULL   | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963035 | gne  | Query       | 2      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963036 | gne  | Query       | 2      | NULL   | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963037 | gne  | Query       | 2      | NULL   | SELECT c.* FROM
>>>>clan_pid cp, clan c
>>>>| 10963038 | gne  | Query       | 2      | NULL   | SELECT * FROM
>>>>pm_message WHERE id =
>>>>| 10963039 | gne  | Query       | 2      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963041 | gne  | Query       | 2      | NULL   | SELECT c.* FROM
>>>>clan_pid cp, clan c
>>>>| 10963042 | gne  | Query       | 1      | NULL   | select r.review_id
>>>>        
>>>>
>>>>from review r wh
>>>      
>>>
>>>>| 10963043 | gne  | Query       | 1      | NULL   | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963045 | gne  | Query       | 1      | NULL   | SELECT count(*) AS
>>>>count FROM pm_me
>>>>| 10963046 | gne  | Query       | 1      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963047 | gne  | Query       | 1      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963048 | gne  | Query       | 1      | NULL   | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963049 | gne  | Query       | 1      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963050 | gne  | Query       | 1      | NULL   | select r.review_id
>>>>        
>>>>
>>>>from review r wh
>>>      
>>>
>>>>| 10963051 | gne  | Query       | 1      | NULL   | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963052 | gne  | Query       | 1      | NULL   | SELECT * FROM clan
>>>>WHERE id='331'
>>>>| 10963053 | gne  | Query       | 1      | NULL   | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963054 | gne  | Query       | 1      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963055 | gne  | Query       | 1      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963056 | gne  | Query       | 1      | NULL   | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963057 | gne  | Query       | 1      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963058 | gne  | Query       | 1      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963059 | gne  | Query       | 1      | NULL   | SELECT c.* FROM
>>>>clan_pid cp, clan c
>>>>| 10963061 | gne  | Query       | 0      | NULL   | SELECT urs_id FROM
>>>>user_info_new WH
>>>>| 10963062 | gne  | Query       | 0      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963063 | gne  | Query       | 0      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963064 | gne  | Query       | 0      | NULL   | SELECT c.* FROM
>>>>clan_pid cp, clan c
>>>>| 10963065 | gne  | Query       | 0      | NULL   | SELECT * FROM
>>>>clan_member where uni
>>>>| 10963066 | gne  | Query       | 0      | NULL   | SELECT r.*,
>>>>ui.username FROM review
>>>>| 10963067 | gne  | Query       | 0      | NULL   | SELECT * FROM clan c,
>>>>clan_member c
>>>>| 10963068 | gne  | Query       | 0      | NULL   | SELECT c.* FROM
>>>>clan_pid cp, clan c
>>>>| 10963069 | gne  | Query       | 0      | NULL   | SELECT count(*) AS
>>>>count FROM pm_me
>>>>+----------+------+-------------+--------+--------+------------------------------------
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>======================
>>>>my.cnf
>>>>======================
>>>>[mysqld]
>>>>log-error = /var/opt/mysql/error.log
>>>>log-bin
>>>>server-id = 600
>>>>
>>>>set-variable = slave_net_timeout=3600
>>>>set-variable = net_read_timeout=3600
>>>>set-variable = query_cache_size=512M
>>>>set-variable = wait_timeout=10
>>>>set-variable = key_buffer_size=128M
>>>>set-variable = max_allowed_packet=1024000000
>>>>set-variable = table_cache=512
>>>>set-variable = sort_buffer=2M
>>>>set-variable = record_buffer=4M
>>>>set-variable = thread_cache=8
>>>>set-variable = tmp_table_size=64M
>>>>set-variable = thread_concurrency=4
>>>>set-variable = myisam_sort_buffer_size=256M
>>>>set-variable = max_binlog_size=1024000000
>>>>set-variable = ft_min_word_len=3
>>>>set-variable = ft_stopword_file=''
>>>>set-variable = long_query_time=1
>>>>set-variable = max_connections=400
>>>>set-variable = innodb_buffer_pool_size=512M
>>>>set-variable = max_connect_errors=999999999
>>>>
>>>>
>>>>====================================
>>>>server status values (show status)
>>>>====================================
>>>>+--------------------------+------------+
>>>>| Aborted_clients          | 335329     |
>>>>| Aborted_connects         | 373166     |
>>>>| Bytes_received           | 1943964913 |
>>>>| Bytes_sent               | 1037459639 |
>>>>| Com_admin_commands       | 61         |
>>>>| Com_alter_table          | 1          |
>>>>| Com_analyze              | 0          |
>>>>| Com_backup_table         | 0          |
>>>>| Com_begin                | 0          |
>>>>| Com_change_db            | 11518911   |
>>>>| Com_change_master        | 0          |
>>>>| Com_check                | 0          |
>>>>| Com_commit               | 0          |
>>>>| Com_create_db            | 0          |
>>>>| Com_create_function      | 0          |
>>>>| Com_create_index         | 0          |
>>>>| Com_create_table         | 8          |
>>>>| Com_delete               | 19952      |
>>>>| Com_delete_multi         | 0          |
>>>>| Com_drop_db              | 0          |
>>>>| Com_drop_function        | 0          |
>>>>| Com_drop_index           | 0          |
>>>>| Com_drop_table           | 2          |
>>>>| Com_flush                | 1          |
>>>>| Com_grant                | 2          |
>>>>| Com_ha_close             | 0          |
>>>>| Com_ha_open              | 0          |
>>>>| Com_ha_read              | 0          |
>>>>| Com_insert               | 237294     |
>>>>| Com_insert_select        | 2400       |
>>>>| Com_kill                 | 758        |
>>>>| Com_load                 | 0          |
>>>>| Com_load_master_data     | 0          |
>>>>| Com_load_master_table    | 0          |
>>>>| Com_lock_tables          | 0          |
>>>>| Com_optimize             | 0          |
>>>>| Com_purge                | 0          |
>>>>| Com_rename_table         | 0          |
>>>>| Com_repair               | 0          |
>>>>| Com_replace              | 31107      |
>>>>| Com_replace_select       | 0          |
>>>>| Com_reset                | 0          |
>>>>| Com_restore_table        | 0          |
>>>>| Com_revoke               | 0          |
>>>>| Com_rollback             | 0          |
>>>>| Com_savepoint            | 0          |
>>>>| Com_select               | 22301021   |
>>>>| Com_set_option           | 49         |
>>>>| Com_show_binlog_events   | 0          |
>>>>| Com_show_binlogs         | 0          |
>>>>| Com_show_create          | 2          |
>>>>| Com_show_databases       | 31         |
>>>>| Com_show_fields          | 1129       |
>>>>| Com_show_grants          | 0          |
>>>>| Com_show_keys            | 13         |
>>>>| Com_show_logs            | 0          |
>>>>| Com_show_master_status   | 0          |
>>>>| Com_show_new_master      | 0          |
>>>>| Com_show_open_tables     | 0          |
>>>>| Com_show_processlist     | 34450      |
>>>>| Com_show_slave_hosts     | 6          |
>>>>| Com_show_slave_status    | 0          |
>>>>| Com_show_status          | 47         |
>>>>| Com_show_innodb_status   | 4          |
>>>>| Com_show_tables          | 51         |
>>>>| Com_show_variables       | 12         |
>>>>| Com_slave_start          | 0          |
>>>>| Com_slave_stop           | 0          |
>>>>| Com_truncate             | 18         |
>>>>| Com_unlock_tables        | 0          |
>>>>| Com_update               | 253907     |
>>>>| Connections              | 11927865   |
>>>>| Created_tmp_disk_tables  | 6751       |
>>>>| Created_tmp_tables       | 6753       |
>>>>| Created_tmp_files        | 358        |
>>>>| Delayed_insert_threads   | 0          |
>>>>| Delayed_writes           | 0          |
>>>>| Delayed_errors           | 0          |
>>>>| Flush_commands           | 1          |
>>>>| Handler_commit           | 2          |
>>>>| Handler_delete           | 17202      |
>>>>| Handler_read_first       | 991        |
>>>>| Handler_read_key         | 541415295  |
>>>>| Handler_read_next        | 340543222  |
>>>>| Handler_read_prev        | 0          |
>>>>| Handler_read_rnd         | 25605878   |
>>>>| Handler_read_rnd_next    | 1794754881 |
>>>>| Handler_rollback         | 3868360    |
>>>>| Handler_update           | 9501       |
>>>>| Handler_write            | 3358752    |
>>>>| Key_blocks_used          | 124690     |
>>>>| Key_read_requests        | 1091453021 |
>>>>| Key_reads                | 174764     |
>>>>| Key_write_requests       | 176791     |
>>>>| Key_writes               | 166926     |
>>>>| Max_used_connections     | 400        |
>>>>| Not_flushed_key_blocks   | 0          |
>>>>| Not_flushed_delayed_rows | 0          |
>>>>| Open_tables              | 512        |
>>>>| Open_files               | 208        |
>>>>| Open_streams             | 0          |
>>>>| Opened_tables            | 56254      |
>>>>| Questions                | 77415469   |
>>>>| Qcache_queries_in_cache  | 207102     |
>>>>| Qcache_inserts           | 19047398   |
>>>>| Qcache_hits              | 31627612   |
>>>>| Qcache_lowmem_prunes     | 2766958    |
>>>>| Qcache_not_cached        | 3253542    |
>>>>| Qcache_free_memory       | 317973448  |
>>>>| Qcache_free_blocks       | 94546      |
>>>>| Qcache_total_blocks      | 508910     |
>>>>| Rpl_status               | NULL       |
>>>>| Select_full_join         | 0          |
>>>>| Select_full_range_join   | 0          |
>>>>| Select_range             | 1026865    |
>>>>| Select_range_check       | 0          |
>>>>| Select_scan              | 11300      |
>>>>| Slave_open_temp_tables   | 0          |
>>>>| Slave_running            | OFF        |
>>>>| Slow_launch_threads      | 3          |
>>>>| Slow_queries             | 305224     |
>>>>| Sort_merge_passes        | 0          |
>>>>| Sort_range               | 1291192    |
>>>>| Sort_rows                | 26291581   |
>>>>| Sort_scan                | 7175       |
>>>>| Table_locks_immediate    | 38768307   |
>>>>| Table_locks_waited       | 45913      |
>>>>| Threads_cached           | 0          |
>>>>| Threads_created          | 1190079    |
>>>>| Threads_connected        | 53         |
>>>>| Threads_running          | 43         |
>>>>| Uptime                   | 692494     |
>>>>+--------------------------+------------+
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>--
>>>>MySQL General Mailing List
>>>>For list archives: http://lists.mysql.com/mysql
>>>>To unsubscribe:
>>>>http://lists.mysql.com/mysql?unsub=1
>>>>
>>>>
>>>>
>>>>
>>>>        
>>>>
>>>
>>>
>>>
>>>
>>>      
>>>
>>Andy,
>>I was looking at this again just trying to figure it out. Has anyone
>>replied back to you but not the list ? I looked at
>>http://forums.mysql.com/read.php?22,10258,10258 . I wonder if innodb
>>locks the entire table if it hits a deadlock and begins a roll back ...
>>
>>walt
>>
>>    
>>
>>
>
>  
>