MySQL Forums
Forum List  »  InnoDB

Mysql sometimes doen not handle transactions
Posted by: Eduard
Date: January 09, 2007 04:00PM

I have a big problem with my mysql server. Everything works fine, but sometimes mysql doen not hande transactions and everything hangs. I have an web-service which use mysql as database. First I thinked that it is a DoS attack, but after anaysing some log files I do not think so.

Here is a show innodb status output:

=====================================
070109 15:53:06 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 0 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1428222, signal count 600499
Mutex spin waits 443835016, rounds 1735394295, OS waits 1192390
RW-shared spins 40872, OS waits 20659; RW-excl spins 22938, OS waits 16495
------------
TRANSACTIONS
------------
Trx id counter 0 4628617
Purge done for trx's n:o < 0 4625074 undo n:o < 0 0
History list length 426
Total number of lock structs in row lock hash table 0
... truncated...
28122, sees < 0 4625068
---TRANSACTION 0 4628120, ACTIVE 1055 sec, OS thread id 5653881856 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 72797, query id 2152367 mahaon 10.100.10.11 community_slave1 Sending data
SELECT user_group_permission.ID, user_group_permission.GROUP_ID, user_group_permission.USER_PERMISSION_ID, user_group_permission.FORUM_ID, user_group_permission.GLOBAL_PERMISSION_FLAG, user_group_permission.FORUM_DEFAULT_FLAG FROM user_group_permission WHERE user_group_permission.GROUP_ID=775680294
Trx read view will not see trx with id >= 0 4628121, sees < 0 4625068

(a lot of transaltions is skipped)

---TRANSACTION 0 4627234, ACTIVE 2352 sec, OS thread id 5652135936 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 72177, query id 2142291 mahaon 10.100.10.11 community_slave2 Sending data
SELECT user_group_permission.ID, user_group_permission.GROUP_ID, user_group_permission.USER_PERMISSION_ID, user_group_permission.FORUM_ID, user_group_permission.GLOBAL_PERMISSION_FLAG, user_group_permission.FORUM_DEFAULT_FLAG FROM user_group_permission WHERE user_group_permission.GROUP_ID=41309764
Trx read view will not see trx with id >= 0 4627235, sees < 0 4622989
---TRANSACTION 0 4626929, ACTIVE 2723 sec, OS thread id 5666313216 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 72023, query id 2139341 mahaon 10.100.10.11 community_slave1 Sending data
SELECT user_group_permission.ID, user_group_permission.GROUP_ID, user_group_permission.USER_PERMISSION_ID, user_group_permission.FORUM_ID, user_group_permission.GLOBAL_PERMISSION_FLAG, user_group_permission.FORUM_DEFAULT_FLAG FROM user_group_permission WHERE user_group_permission.GROUP_ID=775863985
Trx read view will not see trx with id >= 0 4626930, sees < 0 4622989
---TRANSACTION 0 4626738, ACTIVE 3029 sec, OS thread id 5696134144 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 71935, query id 2137368 mahaon 10.100.10.11 community_slave2 Sending data
SELECT user_group_permission.ID, user_group_permission.GROUP_ID, user_group_permission.USER_PERMISSION_ID, user_group_permission.FORUM_ID, user_group_permission.GLOBAL_PERMISSION_FLAG, user_group_permission.FORUM_DEFAULT_FLAG FROM user_group_permission WHERE user_group_permission.GROUP_ID=442662719
Trx read view will not see trx with id >= 0 4626739, sees < 0 4622989
---TRANSACTION 0 4626121, ACTIVE 4096 sec, OS thread id 5716919296 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 71578, query id 2130092 mahaon 10.100.10.11 community_slave2 Sending data
SELECT user_group_permission.ID, user_group_permission.GROUP_ID, user_group_permission.USER_PERMISSION_ID, user_group_permission.FORUM_ID, user_group_permission.GLOBAL_PERMISSION_FLAG, user_group_permission.FORUM_DEFAULT_FLAG FROM user_group_permission WHERE user_group_permission.GROUP_ID=84724425
Trx read view will not see trx with id >= 0 4626122, sees < 0 4622334
---TRANSACTION 0 4625068, ACTIVE 5836 sec, OS thread id 5733607424 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 71162, query id 2117270 mahaon 10.100.10.11 community_slave1 Sending data
SELECT user_group_permission.ID, user_group_permission.GROUP_ID, user_group_permission.USER_PERMISSION_ID, user_group_permission.FORUM_ID, user_group_permission.GLOBAL_PERMISSION_FLAG, user_group_permission.FORUM_DEFAULT_FLAG FROM user_group_permission WHERE user_group_permission.GROUP_ID=442662719
Trx read view will not see trx with id >= 0 4625069, sees < 0 4622334
--------
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
27795 OS file reads, 454679 OS file writes, 178989 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: size 1, free list len 5, seg size 7,
209878 inserts, 209878 merged recs, 7695 merges
Hash table size 8850487, used cells 1349677, node heap has 2161 buffer(s)
436000.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 3128568194
Log flushed up to 0 3128568194
Last checkpoint at 0 3128568194
0 pending log writes, 0 pending chkp writes
136092 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4663901496; in additional pool allocated 1048576
Buffer pool size 262144
Free buffers 217347
Database pages 42636
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 29268, created 13368, written 369009
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
8 queries inside InnoDB, 235 queries in queue
243 read views open inside InnoDB
Main thread id 5245039616, state: waiting for server activity
Number of rows inserted 511361, updated 106467, deleted 42251, read 340779294
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 646000.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================


And here is output from show status:


Aborted_clients 0
Aborted_connects 255
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 9589
Bytes_sent 199640
Com_admin_commands 0
Com_alter_db 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 1
Com_change_master 0
Com_check 0
Com_checksum 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 0
Com_dealloc_sql 0
Com_delete 0
Com_delete_multi 0
Com_do 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_drop_user 0
Com_execute_sql 0
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 0
Com_insert_select 0
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 0
Com_preload_keys 0
Com_prepare_sql 0
Com_purge 0
Com_purge_before_date 0
Com_rename_table 0
Com_repair 0
Com_replace 0
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_revoke_all 0
Com_rollback 0
Com_savepoint 0
Com_select 1
Com_set_option 4
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_charsets 0
Com_show_collations 0
Com_show_column_types 0
Com_show_create_db 0
Com_show_create_table 0
Com_show_databases 1
Com_show_errors 0
Com_show_fields 0
Com_show_grants 0
Com_show_innodb_status 1
Com_show_keys 0
Com_show_logs 0
Com_show_master_status 0
Com_show_ndb_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_privileges 0
Com_show_processlist 0
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 1
Com_show_storage_engines 0
Com_show_tables 0
Com_show_triggers 0
Com_show_variables 1
Com_show_warnings 0
Com_slave_start 0
Com_slave_stop 0
Com_stmt_close 0
Com_stmt_execute 0
Com_stmt_fetch 0
Com_stmt_prepare 0
Com_stmt_reset 0
Com_stmt_send_long_data 0
Com_truncate 0
Com_unlock_tables 0
Com_update 0
Com_update_multi 0
Com_xa_commit 0
Com_xa_end 0
Com_xa_prepare 0
Com_xa_recover 0
Com_xa_rollback 0
Com_xa_start 0
Compression OFF
Connections 73576
Created_tmp_disk_tables 0
Created_tmp_files 27
Created_tmp_tables 4
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 0
Handler_discover 0
Handler_prepare 0
Handler_read_first 0
Handler_read_key 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 67
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 194
Innodb_buffer_pool_pages_data 42725
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 369149
Innodb_buffer_pool_pages_free 217258
Innodb_buffer_pool_pages_latched 5
Innodb_buffer_pool_pages_misc 2161
Innodb_buffer_pool_pages_total 262144
Innodb_buffer_pool_read_ahead_rnd 18
Innodb_buffer_pool_read_ahead_seq 77
Innodb_buffer_pool_read_requests 520802171
Innodb_buffer_pool_reads 20524
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 6673368
Innodb_data_fsyncs 179102
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 483168256
Innodb_data_reads 27841
Innodb_data_writes 454874
Innodb_data_written 12437687808
Innodb_dblwr_pages_written 369149
Innodb_dblwr_writes 18488
Innodb_log_waits 0
Innodb_log_write_requests 654850
Innodb_log_writes 123033
Innodb_os_log_fsyncs 136128
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 334701056
Innodb_page_size 16384
Innodb_pages_created 13368
Innodb_pages_read 29357
Innodb_pages_written 369149
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 0
Innodb_row_lock_time_avg 0
Innodb_row_lock_time_max 0
Innodb_row_lock_waits 0
Innodb_rows_deleted 42251
Innodb_rows_inserted 511361
Innodb_rows_read 342979613
Innodb_rows_updated 106494
Key_blocks_not_flushed 0
Key_blocks_unused 323359
Key_blocks_used 60446
Key_read_requests 4394139
Key_reads 98152
Key_write_requests 452165
Key_writes 447703
Last_query_cost 0.000000
Max_used_connections 501
Not_flushed_delayed_rows 0
Open_files 6
Open_streams 0
Open_tables 596
Opened_tables 50
Qcache_free_blocks 79
Qcache_free_memory 509458632
Qcache_hits 482379
Qcache_inserts 289574
Qcache_lowmem_prunes 0
Qcache_not_cached 23976
Qcache_queries_in_cache 5486
Qcache_total_blocks 11030
Questions 2163837
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 0
Select_range_check 0
Select_scan 4
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running ON
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 0
Sort_rows 0
Sort_scan 0
Table_locks_immediate 1076410
Table_locks_waited 13
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 0
Threads_connected 489
Threads_created 1023
Threads_running 244
Uptime 246279


Please help me! What is the reason for this problem?

Options: ReplyQuote


Subject
Views
Written By
Posted
Mysql sometimes doen not handle transactions
3051
January 09, 2007 04:00PM


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.