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.

We occassionally have our servers stall and back up queres for 3-5 minutes. then back to normal but locks up all users.

We made a new server with binlogs and log fiels on seperate disk from data. Made data be RAID10 and made config chnages usggested by Oracle. Probelm is not as often but still happens.

We have a query that ones once per minute for each user (about 150 usually). They run all day long very quickly but those backup quickly when this stall happens. We have analyzed our slow query log and indexed more files and fixed soem slow queries and we see no more causing this. We can go days now without it but then bam it is back.

We occassionally have our servers stall and back up queres for 3-5 minutes. then back to normal but locks up all users.

We made a new server with binlogs and log fiels on seperate disk from data. Made data be RAID10 and made config chnages usggested by Oracle. Probelm is not as often but still happens.

We have a query that ones once per minute for each user (about 150 usually). They run all day long very quickly but those backup quickly when this stall happens. We have analyzed our slow query log and indexed more files and fixed soem slow queries and we see no more causing this. We can go days now without it but then bam it is back.

SHOW GLOBAL STATUS

Variable_name   Value
Aborted_clients 4407
Aborted_connects    1551
Binlog_cache_disk_use   184072
Binlog_cache_use    661894
Binlog_stmt_cache_disk_use  0
Binlog_stmt_cache_use   0
Bytes_received  19063442840
Bytes_sent  132853133182
Com_admin_commands  6634187
Com_assign_to_keycache  0
Com_alter_db    0
Com_alter_db_upgrade    0
Com_alter_event 0
Com_alter_function  0
Com_alter_procedure 0
Com_alter_server    0
Com_alter_table 8
Com_alter_tablespace    0
Com_analyze 0
Com_begin   704470
Com_binlog  0
Com_call_procedure  6722493
Com_change_db   6658277
Com_change_master   0
Com_check   0
Com_checksum    0
Com_commit  1430211
Com_create_db   0
Com_create_event    0
Com_create_function 0
Com_create_index    62
Com_create_procedure    2882
Com_create_server   0
Com_create_table    2
Com_create_trigger  1
Com_create_udf  0
Com_create_user 0
Com_create_view 0
Com_dealloc_sql 6112846
Com_delete  11113
Com_delete_multi    0
Com_do  0
Com_drop_db 0
Com_drop_event  0
Com_drop_function   0
Com_drop_index  9
Com_drop_procedure  2882
Com_drop_server 0
Com_drop_table  0
Com_drop_trigger    1
Com_drop_user   0
Com_drop_view   0
Com_empty_query 0
Com_execute_sql 6112973
Com_flush   0
Com_grant   1
Com_ha_close    0
Com_ha_open 0
Com_ha_read 0
Com_help    0
Com_insert  89369
Com_insert_select   1
Com_install_plugin  0
Com_kill    521
Com_load    0
Com_lock_tables 0
Com_optimize    0
Com_preload_keys    0
Com_prepare_sql 6112973
Com_purge   0
Com_purge_before_date   0
Com_release_savepoint   0
Com_rename_table    0
Com_rename_user 0
Com_repair  0
Com_replace 0
Com_replace_select  0
Com_reset   0
Com_resignal    0
Com_revoke  0
Com_revoke_all  0
Com_rollback    218
Com_rollback_to_savepoint   0
Com_savepoint   0
Com_select  43871825
Com_set_option  8940769
Com_signal  0
Com_show_authors    0
Com_show_binlog_events  0
Com_show_binlogs    0
Com_show_charsets   1145
Com_show_collations 24150
Com_show_contributors   0
Com_show_create_db  0
Com_show_create_event   0
Com_show_create_func    0
Com_show_create_proc    6170775
Com_show_create_table   0
Com_show_create_trigger 0
Com_show_databases  8
Com_show_engine_logs    0
Com_show_engine_mutex   174
Com_show_engine_status  174
Com_show_events 0
Com_show_errors 0
Com_show_fields 253582
Com_show_function_status    1
Com_show_grants 1
Com_show_keys   4359
Com_show_master_status  0
Com_show_open_tables    174
Com_show_plugins    88
Com_show_privileges 0
Com_show_procedure_status   1
Com_show_processlist    16374
Com_show_profile    0
Com_show_profiles   0
Com_show_relaylog_events    0
Com_show_slave_hosts    0
Com_show_slave_status   0
Com_show_status 23158
Com_show_storage_engines    0
Com_show_table_status   4356
Com_show_tables 85
Com_show_triggers   0
Com_show_variables  25102
Com_show_warnings   69
Com_slave_start 0
Com_slave_stop  0
Com_stmt_close  6112851
Com_stmt_execute    6112975
Com_stmt_fetch  0
Com_stmt_prepare    6112975
Com_stmt_reprepare  0
Com_stmt_reset  0
Com_stmt_send_long_data 0
Com_truncate    3
Com_uninstall_plugin    0
Com_unlock_tables   0
Com_update  665414
Com_update_multi    301
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 28988
Created_tmp_disk_tables 997343
Created_tmp_files   3428
Created_tmp_tables  1103738
Delayed_errors  0
Delayed_insert_threads  0
Delayed_writes  0
Flush_commands  1
Handler_commit  40551793
Handler_delete  21962
Handler_discover    0
Handler_prepare 1183972
Handler_read_first  2803361
Handler_read_key    1817223370
Handler_read_last   299
Handler_read_next   2192159478
Handler_read_prev   2918171
Handler_read_rnd    4979417
Handler_read_rnd_next   1025367957
Handler_rollback    3129
Handler_savepoint   0
Handler_savepoint_rollback  0
Handler_update  480507
Handler_write   69142091
Innodb_buffer_pool_pages_data   424551
Innodb_buffer_pool_pages_dirty  13
Innodb_buffer_pool_pages_flushed    1382087
Innodb_buffer_pool_pages_free   2185371
Innodb_buffer_pool_pages_misc   11518
Innodb_buffer_pool_pages_total  2621440
Innodb_buffer_pool_read_ahead_rnd   0
Innodb_buffer_pool_read_ahead   57049
Innodb_buffer_pool_read_ahead_evicted   0
Innodb_buffer_pool_read_requests    1032232686
Innodb_buffer_pool_reads    353952
Innodb_buffer_pool_wait_free    0
Innodb_buffer_pool_write_requests   4456721
Innodb_data_fsyncs  1218430
Innodb_data_pending_fsyncs  0
Innodb_data_pending_reads   0
Innodb_data_pending_writes  0
Innodb_data_read    2617249792
Innodb_data_reads   434696
Innodb_data_writes  2030236
Innodb_data_written 1855358976
Innodb_dblwr_pages_written  0
Innodb_dblwr_writes 0
Innodb_have_atomic_builtins ON
Innodb_log_waits    0
Innodb_log_write_requests   978061
Innodb_log_writes   624738
Innodb_os_log_fsyncs    647788
Innodb_os_log_pending_fsyncs    0
Innodb_os_log_pending_writes    0
Innodb_os_log_written   674286592
Innodb_page_size    16384
Innodb_pages_created    4042
Innodb_pages_read   421754
Innodb_pages_written    1382087
Innodb_row_lock_current_waits   0
Innodb_row_lock_time    265740
Innodb_row_lock_time_avg    20441
Innodb_row_lock_time_max    34256
Innodb_row_lock_waits   13
Innodb_rows_deleted 20577
Innodb_rows_inserted    486519
Innodb_rows_read    552198569
Innodb_rows_updated 252163
Innodb_truncated_status_writes  0
Key_blocks_not_flushed  0
Key_blocks_unused   201000
Key_blocks_used 13343
Key_read_requests   182851753
Key_reads   13242
Key_write_requests  8998
Key_writes  8990
Last_query_cost 0.000000
Max_used_connections    606
Not_flushed_delayed_rows    0
Open_files  177
Open_streams    0
Open_table_definitions  10000
Open_tables 10000
Opened_files    4008968
Opened_table_definitions    15849
Opened_tables   18859
Performance_schema_cond_classes_lost    0
Performance_schema_cond_instances_lost  0
Performance_schema_file_classes_lost    0
Performance_schema_file_handles_lost    0
Performance_schema_file_instances_lost  0
Performance_schema_locker_lost  0
Performance_schema_mutex_classes_lost   0
Performance_schema_mutex_instances_lost 0
Performance_schema_rwlock_classes_lost  0
Performance_schema_rwlock_instances_lost    0
Performance_schema_table_handles_lost   0
Performance_schema_table_instances_lost 0
Performance_schema_thread_classes_lost  0
Performance_schema_thread_instances_lost    0
Prepared_stmt_count 25
Qcache_free_blocks  0
Qcache_free_memory  0
Qcache_hits 0
Qcache_inserts  0
Qcache_lowmem_prunes    0
Qcache_not_cached   0
Qcache_queries_in_cache 0
Qcache_total_blocks 0
Queries 89665502
Questions   62813329
Rpl_status  AUTH_MASTER
Select_full_join    10321
Select_full_range_join  2428
Select_range    7770260
Select_range_check  0
Select_scan 2796956
Slave_heartbeat_period  0.000
Slave_open_temp_tables  0
Slave_received_heartbeats   0
Slave_retried_transactions  0
Slave_running   OFF
Slow_launch_threads 0
Slow_queries    2382879
Sort_merge_passes   23
Sort_range  153943
Sort_rows   223361444
Sort_scan   255572
Ssl_accept_renegotiates 0
Ssl_accepts 0
Ssl_callback_cache_hits 0
Ssl_cipher  
Ssl_cipher_list 
Ssl_client_connects 0
Ssl_connect_renegotiates    0
Ssl_ctx_verify_depth    0
Ssl_ctx_verify_mode 0
Ssl_default_timeout 0
Ssl_finished_accepts    0
Ssl_finished_connects   0
Ssl_session_cache_hits  0
Ssl_session_cache_misses    0
Ssl_session_cache_mode  NONE
Ssl_session_cache_overflows 0
Ssl_session_cache_size  0
Ssl_session_cache_timeouts  0
Ssl_sessions_reused 0
Ssl_used_session_cache_entries  0
Ssl_verify_depth    0
Ssl_verify_mode 0
Ssl_version 
Table_locks_immediate   47028873
Table_locks_waited  214
Tc_log_max_pages_used   0
Tc_log_page_size    0
Tc_log_page_waits   0
Threads_cached  261
Threads_connected   345
Threads_created 606
Threads_running 34
Uptime  309942
Uptime_since_flush_status   309942

SHOW ENGINE INNODB STATUS

*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
121219 17:19:36 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds

BACKGROUND THREAD

srv_master_thread loops: 239642 1_second, 239639 sleeps, 23598 10_second, 3673 background, 3673 flush
srv_master_thread log flush and writes: 240469

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 78879, signal count 3065681
Mutex spin waits 702797, rounds 2282568, OS waits 49863
RW-shared spins 842193, rounds 2862837, OS waits 26976
RW-excl spins 434256, rounds 3777756, OS waits 1580
Spin rounds per wait: 3.25 mutex, 3.40 RW-shared, 8.70 RW-excl

FILE I/O

I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (read thread)
I/O thread 7 state: wait Windows aio (read thread)
I/O thread 8 state: wait Windows aio (read thread)
I/O thread 9 state: wait Windows aio (read thread)
I/O thread 10 state: wait Windows aio (read thread)
I/O thread 11 state: wait Windows aio (read thread)
I/O thread 12 state: wait Windows aio (read thread)
I/O thread 13 state: wait Windows aio (read thread)
I/O thread 14 state: wait Windows aio (read thread)
I/O thread 15 state: wait Windows aio (read thread)
I/O thread 16 state: wait Windows aio (read thread)
I/O thread 17 state: wait Windows aio (read thread)
I/O thread 18 state: wait Windows aio (write thread)
I/O thread 19 state: wait Windows aio (write thread)
I/O thread 20 state: wait Windows aio (write thread)
I/O thread 21 state: wait Windows aio (write thread)
I/O thread 22 state: wait Windows aio (write thread)
I/O thread 23 state: wait Windows aio (write thread)
I/O thread 24 state: wait Windows aio (write thread)
I/O thread 25 state: wait Windows aio (write thread)
I/O thread 26 state: wait Windows aio (write thread)
I/O thread 27 state: wait Windows aio (write thread)
I/O thread 28 state: wait Windows aio (write thread)
I/O thread 29 state: wait Windows aio (write thread)
I/O thread 30 state: wait Windows aio (write thread)
I/O thread 31 state: wait Windows aio (write thread)
I/O thread 32 state: wait Windows aio (write thread)
I/O thread 33 state: wait Windows aio (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 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
434696 OS file reads, 2030236 OS file writes, 1218430 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 19.19 writes/s, 10.50 fsyncs/s

INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: size 1, free list len 27, seg size 29, 11681 merges
merged operations:
 insert 15777, delete mark 12559, delete 16
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 84999163, node heap has 11512 buffer(s)
26842.82 hash searches/s, 2386.60 non-hash searches/s

LOG

Log sequence number 15116880820
Log flushed up to   15116880820
Last checkpoint at  15116877234
0 pending log writes, 0 pending chkp writes
647781 log i/o's done, 5.56 log i/o's/second

BUFFER POOL AND MEMORY

Total memory allocated 43956305920; in additional pool allocated 0
Dictionary memory allocated 181322283
Buffer pool size   2621440
Free buffers       2185371
Database pages     424551
Old database pages 156874
Modified db pages  13
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 978, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 421754, created 4042, written 1382087
0.00 reads/s, 0.06 creates/s, 13.62 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: 424551, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

INDIVIDUAL BUFFER POOL INFO

---BUFFER POOL 0
Buffer pool size   327680
Free buffers       275235
Database pages     51011
Old database pages 18850
Modified db pages  2
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 125, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 50633, created 461, written 181321
0.00 reads/s, 0.00 creates/s, 1.69 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: 51011, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   327680
Free buffers       274121
Database pages     52133
Old database pages 19264
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 120, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 51823, created 442, written 98744
0.00 reads/s, 0.00 creates/s, 0.31 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: 52133, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   327680
Free buffers       272641
Database pages     53580
Old database pages 19798
Modified db pages  1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 117, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 53212, created 531, written 156145
0.00 reads/s, 0.00 creates/s, 1.94 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: 53580, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   327680
Free buffers       272101
Database pages     54139
Old database pages 20004
Modified db pages  1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 109, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 53880, created 451, written 232510
0.00 reads/s, 0.00 creates/s, 1.94 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: 54139, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   327680
Free buffers       270671
Database pages     55579
Old database pages 20536
Modified db pages  4
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 122, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 55213, created 534, written 219942
0.00 reads/s, 0.00 creates/s, 2.25 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: 55579, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   327680
Free buffers       272982
Database pages     53257
Old database pages 19678
Modified db pages  1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 118, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 52863, created 567, written 186229
0.00 reads/s, 0.06 creates/s, 2.06 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: 53257, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   327680
Free buffers       273770
Database pages     52463
Old database pages 19386
Modified db pages  3
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 117, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 52078, created 577, written 181167
0.00 reads/s, 0.00 creates/s, 1.56 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: 52463, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   327680
Free buffers       273850
Database pages     52389
Old database pages 19358
Modified db pages  1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 150, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 52052, created 479, written 126029
0.00 reads/s, 0.00 creates/s, 1.87 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: 52389, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue
26 read views open inside InnoDB
Main thread id 4032, state: sleeping
Number of rows inserted 486519, updated 252163, deleted 20577, read 4847166839
1.37 inserts/s, 1.87 updates/s, 0.06 deletes/s, 54865.57 reads/s

SHOW ENGINE INNODB MUTEX

Type    Name    Status
InnoDB  ibuf0ibuf.c:534 os_waits=6
InnoDB  dict0dict.c:699 os_waits=53
InnoDB  log0log.c:775   os_waits=4
InnoDB  log0log.c:771   os_waits=11076
InnoDB  buf0buf.c:1166  os_waits=15
InnoDB  buf0buf.c:1166  os_waits=13
InnoDB  buf0buf.c:1166  os_waits=6
InnoDB  buf0buf.c:1166  os_waits=10
InnoDB  buf0buf.c:1166  os_waits=34
InnoDB  buf0buf.c:1166  os_waits=5
InnoDB  buf0buf.c:1166  os_waits=4
InnoDB  buf0buf.c:1166  os_waits=24
InnoDB  fil0fil.c:1610  os_waits=26671
InnoDB  srv0srv.c:1018  os_waits=11942
InnoDB  sync0sync.c:1527    os_waits=2
InnoDB  dict0dict.c:1726    os_waits=1
InnoDB  dict0dict.c:1726    os_waits=3
InnoDB  dict0dict.c:1726    os_waits=1
InnoDB  dict0dict.c:1726    os_waits=4
InnoDB  dict0dict.c:1726    os_waits=4
InnoDB  dict0dict.c:1726    os_waits=4
InnoDB  dict0dict.c:1726    os_waits=1
InnoDB  dict0dict.c:1726    os_waits=1
InnoDB  dict0dict.c:1726    os_waits=5
InnoDB  dict0dict.c:1726    os_waits=7
InnoDB  dict0dict.c:1726    os_waits=6
InnoDB  dict0dict.c:1726    os_waits=9
InnoDB  dict0dict.c:1726    os_waits=11
InnoDB  dict0dict.c:1726    os_waits=4
InnoDB  dict0dict.c:1726    os_waits=13
InnoDB  dict0dict.c:1726    os_waits=1
InnoDB  dict0dict.c:1726    os_waits=1
InnoDB  dict0dict.c:1726    os_waits=4
InnoDB  dict0dict.c:1726    os_waits=1
InnoDB  dict0dict.c:1726    os_waits=1
InnoDB  dict0dict.c:1726    os_waits=2
InnoDB  dict0dict.c:1726    os_waits=2
InnoDB  dict0dict.c:1726    os_waits=3
InnoDB  dict0dict.c:1726    os_waits=4
InnoDB  dict0dict.c:1726    os_waits=2
InnoDB  dict0dict.c:1726    os_waits=2
InnoDB  dict0dict.c:1726    os_waits=3
InnoDB  dict0dict.c:1726    os_waits=16
InnoDB  dict0dict.c:722 os_waits=14
InnoDB  dict0dict.c:722 os_waits=6
InnoDB  dict0dict.c:722 os_waits=7
InnoDB  dict0dict.c:722 os_waits=6
InnoDB  dict0dict.c:722 os_waits=22
InnoDB  dict0dict.c:722 os_waits=18
InnoDB  dict0dict.c:722 os_waits=10
InnoDB  dict0dict.c:722 os_waits=23
InnoDB  dict0dict.c:722 os_waits=18
InnoDB  dict0dict.c:722 os_waits=20
InnoDB  dict0dict.c:722 os_waits=15
InnoDB  dict0dict.c:722 os_waits=13
InnoDB  dict0dict.c:722 os_waits=5
InnoDB  dict0dict.c:722 os_waits=12
InnoDB  dict0dict.c:722 os_waits=13
InnoDB  dict0dict.c:722 os_waits=309
InnoDB  dict0dict.c:722 os_waits=6
InnoDB  dict0dict.c:722 os_waits=16
InnoDB  dict0dict.c:722 os_waits=15
InnoDB  dict0dict.c:722 os_waits=22
InnoDB  dict0dict.c:722 os_waits=33
InnoDB  dict0dict.c:722 os_waits=21
InnoDB  dict0dict.c:722 os_waits=10
InnoDB  dict0dict.c:722 os_waits=5
InnoDB  dict0dict.c:722 os_waits=20
InnoDB  dict0dict.c:722 os_waits=6
InnoDB  dict0dict.c:722 os_waits=12
InnoDB  dict0dict.c:722 os_waits=7
InnoDB  dict0dict.c:722 os_waits=35
InnoDB  dict0dict.c:722 os_waits=13
InnoDB  dict0dict.c:722 os_waits=3
InnoDB  dict0dict.c:722 os_waits=14
InnoDB  dict0dict.c:722 os_waits=9
InnoDB  dict0dict.c:722 os_waits=20
InnoDB  dict0dict.c:722 os_waits=10
InnoDB  dict0dict.c:722 os_waits=11
InnoDB  dict0dict.c:722 os_waits=11
InnoDB  dict0dict.c:722 os_waits=13
InnoDB  dict0dict.c:722 os_waits=6
InnoDB  dict0dict.c:722 os_waits=13
InnoDB  dict0dict.c:722 os_waits=11
InnoDB  dict0dict.c:722 os_waits=5
InnoDB  dict0dict.c:722 os_waits=14
InnoDB  dict0dict.c:722 os_waits=10
InnoDB  dict0dict.c:722 os_waits=28
InnoDB  dict0dict.c:722 os_waits=17
InnoDB  dict0dict.c:722 os_waits=10
InnoDB  dict0dict.c:722 os_waits=10
InnoDB  dict0dict.c:722 os_waits=13
InnoDB  dict0dict.c:722 os_waits=18
InnoDB  dict0dict.c:722 os_waits=7
InnoDB  dict0dict.c:722 os_waits=13
InnoDB  dict0dict.c:722 os_waits=7
InnoDB  dict0dict.c:722 os_waits=22
InnoDB  dict0dict.c:722 os_waits=8
InnoDB  dict0dict.c:722 os_waits=32
InnoDB  dict0dict.c:722 os_waits=18
InnoDB  dict0dict.c:722 os_waits=10
InnoDB  dict0dict.c:722 os_waits=16
InnoDB  dict0dict.c:722 os_waits=10
InnoDB  dict0dict.c:722 os_waits=7
InnoDB  dict0dict.c:722 os_waits=13
InnoDB  dict0dict.c:722 os_waits=22
InnoDB  dict0dict.c:722 os_waits=15
InnoDB  log0log.c:832   os_waits=23038
InnoDB  btr0sea.c:178   os_waits=2965
InnoDB  combined buf0buf.c:916  os_waits=1261
share|improve this question
    
Please, upload these logs to somewhere like pastebin.com and put the links here instead of these unformatted monsters :) (and possibly remove unnecessary parts as well). Easier to get someone read your post and eventually help you that way. –  dezso Dec 20 '12 at 16:35
    
Thank you for the help on posting. I was looking for way to upload a file with that information. –  karl lauritzen Dec 20 '12 at 19:43
    
Sorry forgot my machine config. 16 core. 96GB RAM Windows –  karl lauritzen Dec 20 '12 at 21:18
    
Have you captured SHOW FULL PROCESSLIST while the server is backlogged? There should be something valuable in that output, assuming you can run it. –  Michael - sqlbot Dec 21 '12 at 2:43
1  
the user with SUPER privilege should be able to connect to MySQL even if you hit the too many connections MySQL error. Please provide SHOW VARIABLES and/or my.cnf. MySQL error log might also help. –  redguy Dec 21 '12 at 18:22
show 2 more comments

1 Answer

You didn't provide your configuration (especially your innodb_* settings), and I can't be sure, but it seems to me that you can be hitting the InnoDB flushing issue.

The article on mysqlperformanceblog (and perhaps articles linked within) and the article by DimitriK can give you insight on what might be happening.

You didn't specify what configuration changes were specified by Oracle, but your options are to experiment with innodb_log_buffer_size (using tips from Percona) and/or tune the innodb_io_capacity. You might also consider changing MySQL to Percona Server, since they've put some effort to improve the flushing issue.

Also, please note that most of the articles linked assume you are running MySQL in Linux, and since you are running on Windows some advices might not apply, but on the other hand Windows has some decent monitoring utilities built-in so you could have more insight on what is actually happening during the stall (lots of I/O operations). Any such info might help in properly diagnosing your issue.

share|improve this answer
    
thank you for your reply. Oracle did look at flushing at first. When we had this hapepning every day on every server (we had 4 servers all the same). We increased dirty pages pct frm 20 to 75. We made bufffer have 8 instances, we increased bufffer pool from 640MB to 1GB, we increased io read and write to 16 and io capacity to 300, we turend off doiuble write, we moved logs to different disk partition, moved database on disk partition to RAID10 (was on RADI5). We also added a 5th server and reduced load on each from 1100 connections to about 600 connections per. –  karl lauritzen Dec 21 '12 at 16:35
    
We have a somewhat unique environment. Our application was desktop MS Access app local to users early 90's. We moved it to a hosted environment in 2010 to MySQL and .net and they now remote desktop in then run our app. Every user thus has their own database not one multi tenant database. We have also run slow query log and indexed more programs. No query over 1 second any more. About 700-1000 QPS per server. Thread concurrency is now 0 and tickets at 5000. –  karl lauritzen Dec 21 '12 at 16:39
    
If flushing has been dealt with you might be hitting some platform limitation. Perhaps your architecture and/or environment is to blame? According to MySQL docs there is a limitation in open ports on some windows versions. Do you have any performance data during the stall and normal operation? –  redguy Dec 21 '12 at 18:12
    
You are thinking like I am. I am thinking we have hit some limit as well. Becuase off loading some peoepl was the biggest thing to help. But why would it shut the INNODB down to not process anything then pick up and catch up? And we made no changes to server, apps or MySQL when this started out of the blue. We did look at windows when it happend (ORacle too) noting odd. no scheduled task, IO issue, memory issue etc. We hve no other apps on these servers just our MySQL app. –  karl lauritzen Dec 21 '12 at 19:26
    
Elaborate on "shut the INNODB down to not process anything then pick up and catch up". Can you connect to MySQL and query it? If so, are you seeing "too many connections" in mysql error log at some moment? Do you see any errors in the client application? Can you eliminate the client application and test how many connections your mysql can handle by some other means? Are the clients run from the same machine? How many open tcp porst does the machine running clients have? –  redguy Dec 21 '12 at 20:48
show 5 more comments

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.