Re: Recurring nightly stall - Looking for direction please
Posted by: Sam Bates
Date: March 23, 2009 08:50AM
Date: March 23, 2009 08:50AM
Hi Rick and Aftab,
thanks for your help and replies.
To answer your questions:
90% of the table are InnoDB. The queries I see being backed up at 8:00pm are only using InnoDB tables though.
We are on Mysql 5.0.45, which I don't believe supports events, and we wouldn't use them.
Yes, there are queries in the slow query log from this time (and the log is only populated from queries at this time), however they are exactly the same as the queries I see in "SHOW INNODB STATUS \G".
They are only SELECT queries, no UPDATE or INSERTS (nothing ever shows locked).
These queries run from all users, pretty much 24/7, but still, we only see any issues starting at exactly 8:00:00 pm. I can run a combination of these queries at any other time, with no problems.
My current thought is that there is some external process/cleanup being performed by the (OS/Mysql), that causes the queries to back up - vs the queries causing Mysql to backup.
I have attached the info you requested below - sorry if I sound like I am on the wrong track, but I appreciate you taking your time to respond to people on the forums and I just don't want to waste your time if it doesn't sound like Mysql is the root cause.
Note: We have 64GB RAM, and I have set the innodb_thread_concurrency=4 because it seems to help the server recover quicker during the stall.
mysql> SHOW VARIABLES LIKE 'innodb%';
+---------------------------------+-------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------+
| innodb_additional_mem_pool_size | 268435456 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 25769803776 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:100M:autoextend |
| innodb_data_home_dir | /var/lib/mysql/ |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | /var/lib/mysql/ |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 33554432 |
| innodb_log_file_size | 536870912 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | /var/lib/mysql/ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 4 |
| innodb_thread_sleep_delay | 10000 |
+---------------------------------+-------------------------+
34 rows in set (0.00 sec)
mysql> SHOW GLOBAL STATUS\G
*************************** 1. row ***************************
Variable_name: Aborted_clients
Value: 5048
*************************** 2. row ***************************
Variable_name: Aborted_connects
Value: 7
*************************** 3. row ***************************
Variable_name: Binlog_cache_disk_use
Value: 0
*************************** 4. row ***************************
Variable_name: Binlog_cache_use
Value: 17895734
*************************** 5. row ***************************
Variable_name: Bytes_received
Value: 506756490178
*************************** 6. row ***************************
Variable_name: Bytes_sent
Value: 3493442799144
*************************** 7. row ***************************
Variable_name: Com_admin_commands
Value: 16
*************************** 8. row ***************************
Variable_name: Com_alter_db
Value: 0
*************************** 9. row ***************************
Variable_name: Com_alter_table
Value: 14
*************************** 10. row ***************************
Variable_name: Com_analyze
Value: 0
*************************** 11. row ***************************
Variable_name: Com_backup_table
Value: 0
*************************** 12. row ***************************
Variable_name: Com_begin
Value: 0
*************************** 13. row ***************************
Variable_name: Com_call_procedure
Value: 0
*************************** 14. row ***************************
Variable_name: Com_change_db
Value: 1152447099
*************************** 15. row ***************************
Variable_name: Com_change_master
Value: 0
*************************** 16. row ***************************
Variable_name: Com_check
Value: 0
*************************** 17. row ***************************
Variable_name: Com_checksum
Value: 0
*************************** 18. row ***************************
Variable_name: Com_commit
Value: 0
*************************** 19. row ***************************
Variable_name: Com_create_db
Value: 0
*************************** 20. row ***************************
Variable_name: Com_create_function
Value: 0
*************************** 21. row ***************************
Variable_name: Com_create_index
Value: 0
*************************** 22. row ***************************
Variable_name: Com_create_table
Value: 4
*************************** 23. row ***************************
Variable_name: Com_create_user
Value: 0
*************************** 24. row ***************************
Variable_name: Com_dealloc_sql
Value: 0
*************************** 25. row ***************************
Variable_name: Com_delete
Value: 24853797
*************************** 26. row ***************************
Variable_name: Com_delete_multi
Value: 84232
*************************** 27. row ***************************
Variable_name: Com_do
Value: 0
*************************** 28. row ***************************
Variable_name: Com_drop_db
Value: 0
*************************** 29. row ***************************
Variable_name: Com_drop_function
Value: 0
*************************** 30. row ***************************
Variable_name: Com_drop_index
Value: 0
*************************** 31. row ***************************
Variable_name: Com_drop_table
Value: 0
*************************** 32. row ***************************
Variable_name: Com_drop_user
Value: 0
*************************** 33. row ***************************
Variable_name: Com_execute_sql
Value: 0
*************************** 34. row ***************************
Variable_name: Com_flush
Value: 10
*************************** 35. row ***************************
Variable_name: Com_grant
Value: 0
*************************** 36. row ***************************
Variable_name: Com_ha_close
Value: 0
*************************** 37. row ***************************
Variable_name: Com_ha_open
Value: 0
*************************** 38. row ***************************
Variable_name: Com_ha_read
Value: 0
*************************** 39. row ***************************
Variable_name: Com_help
Value: 0
*************************** 40. row ***************************
Variable_name: Com_insert
Value: 53113416
*************************** 41. row ***************************
Variable_name: Com_insert_select
Value: 60330
*************************** 42. row ***************************
Variable_name: Com_kill
Value: 121
*************************** 43. row ***************************
Variable_name: Com_load
Value: 0
*************************** 44. row ***************************
Variable_name: Com_load_master_data
Value: 0
*************************** 45. row ***************************
Variable_name: Com_load_master_table
Value: 0
*************************** 46. row ***************************
Variable_name: Com_lock_tables
Value: 0
*************************** 47. row ***************************
Variable_name: Com_optimize
Value: 0
*************************** 48. row ***************************
Variable_name: Com_preload_keys
Value: 0
*************************** 49. row ***************************
Variable_name: Com_prepare_sql
Value: 0
*************************** 50. row ***************************
Variable_name: Com_purge
Value: 0
*************************** 51. row ***************************
Variable_name: Com_purge_before_date
Value: 0
*************************** 52. row ***************************
Variable_name: Com_rename_table
Value: 0
*************************** 53. row ***************************
Variable_name: Com_repair
Value: 0
*************************** 54. row ***************************
Variable_name: Com_replace
Value: 0
*************************** 55. row ***************************
Variable_name: Com_replace_select
Value: 0
*************************** 56. row ***************************
Variable_name: Com_reset
Value: 0
*************************** 57. row ***************************
Variable_name: Com_restore_table
Value: 0
*************************** 58. row ***************************
Variable_name: Com_revoke
Value: 0
*************************** 59. row ***************************
Variable_name: Com_revoke_all
Value: 0
*************************** 60. row ***************************
Variable_name: Com_rollback
Value: 0
*************************** 61. row ***************************
Variable_name: Com_savepoint
Value: 0
*************************** 62. row ***************************
Variable_name: Com_select
Value: 980306222
*************************** 63. row ***************************
Variable_name: Com_set_option
Value: 8504
*************************** 64. row ***************************
Variable_name: Com_show_binlog_events
Value: 0
*************************** 65. row ***************************
Variable_name: Com_show_binlogs
Value: 176
*************************** 66. row ***************************
Variable_name: Com_show_charsets
Value: 1986
*************************** 67. row ***************************
Variable_name: Com_show_collations
Value: 1986
*************************** 68. row ***************************
Variable_name: Com_show_column_types
Value: 0
*************************** 69. row ***************************
Variable_name: Com_show_create_db
Value: 16
*************************** 70. row ***************************
Variable_name: Com_show_create_table
Value: 841
*************************** 71. row ***************************
Variable_name: Com_show_databases
Value: 318
*************************** 72. row ***************************
Variable_name: Com_show_errors
Value: 0
*************************** 73. row ***************************
Variable_name: Com_show_fields
Value: 152325
*************************** 74. row ***************************
Variable_name: Com_show_grants
Value: 1107
*************************** 75. row ***************************
Variable_name: Com_show_innodb_status
Value: 208
*************************** 76. row ***************************
Variable_name: Com_show_keys
Value: 699
*************************** 77. row ***************************
Variable_name: Com_show_logs
Value: 0
*************************** 78. row ***************************
Variable_name: Com_show_master_status
Value: 4
*************************** 79. row ***************************
Variable_name: Com_show_ndb_status
Value: 0
*************************** 80. row ***************************
Variable_name: Com_show_new_master
Value: 0
*************************** 81. row ***************************
Variable_name: Com_show_open_tables
Value: 0
*************************** 82. row ***************************
Variable_name: Com_show_privileges
Value: 0
*************************** 83. row ***************************
Variable_name: Com_show_processlist
Value: 1503
*************************** 84. row ***************************
Variable_name: Com_show_slave_hosts
Value: 5
*************************** 85. row ***************************
Variable_name: Com_show_slave_status
Value: 1
*************************** 86. row ***************************
Variable_name: Com_show_status
Value: 7
*************************** 87. row ***************************
Variable_name: Com_show_storage_engines
Value: 22
*************************** 88. row ***************************
Variable_name: Com_show_tables
Value: 521388
*************************** 89. row ***************************
Variable_name: Com_show_triggers
Value: 549
*************************** 90. row ***************************
Variable_name: Com_show_variables
Value: 4149
*************************** 91. row ***************************
Variable_name: Com_show_warnings
Value: 0
*************************** 92. row ***************************
Variable_name: Com_slave_start
Value: 0
*************************** 93. row ***************************
Variable_name: Com_slave_stop
Value: 0
*************************** 94. row ***************************
Variable_name: Com_stmt_close
Value: 0
*************************** 95. row ***************************
Variable_name: Com_stmt_execute
Value: 0
*************************** 96. row ***************************
Variable_name: Com_stmt_fetch
Value: 0
*************************** 97. row ***************************
Variable_name: Com_stmt_prepare
Value: 0
*************************** 98. row ***************************
Variable_name: Com_stmt_reset
Value: 0
*************************** 99. row ***************************
Variable_name: Com_stmt_send_long_data
Value: 0
*************************** 100. row ***************************
Variable_name: Com_truncate
Value: 1
*************************** 101. row ***************************
Variable_name: Com_unlock_tables
Value: 0
*************************** 102. row ***************************
Variable_name: Com_update
Value: 114614914
*************************** 103. row ***************************
Variable_name: Com_update_multi
Value: 4812991
*************************** 104. row ***************************
Variable_name: Com_xa_commit
Value: 0
*************************** 105. row ***************************
Variable_name: Com_xa_end
Value: 0
*************************** 106. row ***************************
Variable_name: Com_xa_prepare
Value: 0
*************************** 107. row ***************************
Variable_name: Com_xa_recover
Value: 0
*************************** 108. row ***************************
Variable_name: Com_xa_rollback
Value: 0
*************************** 109. row ***************************
Variable_name: Com_xa_start
Value: 0
*************************** 110. row ***************************
Variable_name: Compression
Value: OFF
*************************** 111. row ***************************
Variable_name: Connections
Value: 82087768
*************************** 112. row ***************************
Variable_name: Created_tmp_disk_tables
Value: 72973686
*************************** 113. row ***************************
Variable_name: Created_tmp_files
Value: 126
*************************** 114. row ***************************
Variable_name: Created_tmp_tables
Value: 110070619
*************************** 115. row ***************************
Variable_name: Delayed_errors
Value: 0
*************************** 116. row ***************************
Variable_name: Delayed_insert_threads
Value: 0
*************************** 117. row ***************************
Variable_name: Delayed_writes
Value: 0
*************************** 118. row ***************************
Variable_name: Flush_commands
Value: 7
*************************** 119. row ***************************
Variable_name: Handler_commit
Value: 349842333
*************************** 120. row ***************************
Variable_name: Handler_delete
Value: 3774296
*************************** 121. row ***************************
Variable_name: Handler_discover
Value: 0
*************************** 122. row ***************************
Variable_name: Handler_prepare
Value: 349572594
*************************** 123. row ***************************
Variable_name: Handler_read_first
Value: 28211124
*************************** 124. row ***************************
Variable_name: Handler_read_key
Value: 173376493216
*************************** 125. row ***************************
Variable_name: Handler_read_next
Value: 656018630883
*************************** 126. row ***************************
Variable_name: Handler_read_prev
Value: 26139618895
*************************** 127. row ***************************
Variable_name: Handler_read_rnd
Value: 6032665333
*************************** 128. row ***************************
Variable_name: Handler_read_rnd_next
Value: 466130609590
*************************** 129. row ***************************
Variable_name: Handler_rollback
Value: 18
*************************** 130. row ***************************
Variable_name: Handler_savepoint
Value: 0
*************************** 131. row ***************************
Variable_name: Handler_savepoint_rollback
Value: 0
*************************** 132. row ***************************
Variable_name: Handler_update
Value: 603046953
*************************** 133. row ***************************
Variable_name: Handler_write
Value: 28065551339
*************************** 134. row ***************************
Variable_name: Innodb_buffer_pool_pages_data
Value: 1426670
*************************** 135. row ***************************
Variable_name: Innodb_buffer_pool_pages_dirty
Value: 22931
*************************** 136. row ***************************
Variable_name: Innodb_buffer_pool_pages_flushed
Value: 26149023
*************************** 137. row ***************************
Variable_name: Innodb_buffer_pool_pages_free
Value: 8
*************************** 138. row ***************************
Variable_name: Innodb_buffer_pool_pages_latched
Value: 0
*************************** 139. row ***************************
Variable_name: Innodb_buffer_pool_pages_misc
Value: 146186
*************************** 140. row ***************************
Variable_name: Innodb_buffer_pool_pages_total
Value: 1572864
*************************** 141. row ***************************
Variable_name: Innodb_buffer_pool_read_ahead_rnd
Value: 3528
*************************** 142. row ***************************
Variable_name: Innodb_buffer_pool_read_ahead_seq
Value: 12253
*************************** 143. row ***************************
Variable_name: Innodb_buffer_pool_read_requests
Value: 1990063504595
*************************** 144. row ***************************
Variable_name: Innodb_buffer_pool_reads
Value: 1364800
*************************** 145. row ***************************
Variable_name: Innodb_buffer_pool_wait_free
Value: 0
*************************** 146. row ***************************
Variable_name: Innodb_buffer_pool_write_requests
Value: 2303430235
*************************** 147. row ***************************
Variable_name: Innodb_data_fsyncs
Value: 317846287
*************************** 148. row ***************************
Variable_name: Innodb_data_pending_fsyncs
Value: 0
*************************** 149. row ***************************
Variable_name: Innodb_data_pending_reads
Value: 0
*************************** 150. row ***************************
Variable_name: Innodb_data_pending_writes
Value: 0
*************************** 151. row ***************************
Variable_name: Innodb_data_read
Value: 29383479296
*************************** 152. row ***************************
Variable_name: Innodb_data_reads
Value: 1469520
*************************** 153. row ***************************
Variable_name: Innodb_data_writes
Value: 334441126
*************************** 154. row ***************************
Variable_name: Innodb_data_written
Value: 1129152164352
*************************** 155. row ***************************
Variable_name: Innodb_dblwr_pages_written
Value: 26149023
*************************** 156. row ***************************
Variable_name: Innodb_dblwr_writes
Value: 718121
*************************** 157. row ***************************
Variable_name: Innodb_log_waits
Value: 0
*************************** 158. row ***************************
Variable_name: Innodb_log_write_requests
Value: 234958439
*************************** 159. row ***************************
Variable_name: Innodb_log_writes
Value: 315837631
*************************** 160. row ***************************
Variable_name: Innodb_os_log_fsyncs
Value: 316422853
*************************** 161. row ***************************
Variable_name: Innodb_os_log_pending_fsyncs
Value: 0
*************************** 162. row ***************************
Variable_name: Innodb_os_log_pending_writes
Value: 0
*************************** 163. row ***************************
Variable_name: Innodb_os_log_written
Value: 272007970816
*************************** 164. row ***************************
Variable_name: Innodb_page_size
Value: 16384
*************************** 165. row ***************************
Variable_name: Innodb_pages_created
Value: 242000
*************************** 166. row ***************************
Variable_name: Innodb_pages_read
Value: 1793292
*************************** 167. row ***************************
Variable_name: Innodb_pages_written
Value: 26149023
*************************** 168. row ***************************
Variable_name: Innodb_row_lock_current_waits
Value: 0
*************************** 169. row ***************************
Variable_name: Innodb_row_lock_time
Value: 2739954
*************************** 170. row ***************************
Variable_name: Innodb_row_lock_time_avg
Value: 29
*************************** 171. row ***************************
Variable_name: Innodb_row_lock_time_max
Value: 19472
*************************** 172. row ***************************
Variable_name: Innodb_row_lock_waits
Value: 93702
*************************** 173. row ***************************
Variable_name: Innodb_rows_deleted
Value: 25042555
*************************** 174. row ***************************
Variable_name: Innodb_rows_inserted
Value: 42300390
*************************** 175. row ***************************
Variable_name: Innodb_rows_read
Value: 781235682144
*************************** 176. row ***************************
Variable_name: Innodb_rows_updated
Value: 169987894
*************************** 177. row ***************************
Variable_name: Key_blocks_not_flushed
Value: 0
*************************** 178. row ***************************
Variable_name: Key_blocks_unused
Value: 26
*************************** 179. row ***************************
Variable_name: Key_blocks_used
Value: 857368
*************************** 180. row ***************************
Variable_name: Key_read_requests
Value: 7421308578
*************************** 181. row ***************************
Variable_name: Key_reads
Value: 6344459
*************************** 182. row ***************************
Variable_name: Key_write_requests
Value: 429851659
*************************** 183. row ***************************
Variable_name: Key_writes
Value: 6346002
*************************** 184. row ***************************
Variable_name: Last_query_cost
Value: 0.000000
*************************** 185. row ***************************
Variable_name: Max_used_connections
Value: 361
*************************** 186. row ***************************
Variable_name: Not_flushed_delayed_rows
Value: 0
*************************** 187. row ***************************
Variable_name: Open_files
Value: 1857
*************************** 188. row ***************************
Variable_name: Open_streams
Value: 0
*************************** 189. row ***************************
Variable_name: Open_tables
Value: 4058
*************************** 190. row ***************************
Variable_name: Opened_tables
Value: 9776
*************************** 191. row ***************************
Variable_name: Prepared_stmt_count
Value: 0
*************************** 192. row ***************************
Variable_name: Qcache_free_blocks
Value: 0
*************************** 193. row ***************************
Variable_name: Qcache_free_memory
Value: 0
*************************** 194. row ***************************
Variable_name: Qcache_hits
Value: 0
*************************** 195. row ***************************
Variable_name: Qcache_inserts
Value: 0
*************************** 196. row ***************************
Variable_name: Qcache_lowmem_prunes
Value: 0
*************************** 197. row ***************************
Variable_name: Qcache_not_cached
Value: 0
*************************** 198. row ***************************
Variable_name: Qcache_queries_in_cache
Value: 0
*************************** 199. row ***************************
Variable_name: Qcache_total_blocks
Value: 0
*************************** 200. row ***************************
Variable_name: Questions
Value: 2413200847
*************************** 201. row ***************************
Variable_name: Rpl_status
Value: NULL
*************************** 202. row ***************************
Variable_name: Select_full_join
Value: 4521186
*************************** 203. row ***************************
Variable_name: Select_full_range_join
Value: 1108581
*************************** 204. row ***************************
Variable_name: Select_range
Value: 127272799
*************************** 205. row ***************************
Variable_name: Select_range_check
Value: 0
*************************** 206. row ***************************
Variable_name: Select_scan
Value: 26013654
*************************** 207. row ***************************
Variable_name: Slave_open_temp_tables
Value: 0
*************************** 208. row ***************************
Variable_name: Slave_retried_transactions
Value: 0
*************************** 209. row ***************************
Variable_name: Slave_running
Value: OFF
*************************** 210. row ***************************
Variable_name: Slow_launch_threads
Value: 0
*************************** 211. row ***************************
Variable_name: Slow_queries
Value: 63336
*************************** 212. row ***************************
Variable_name: Sort_merge_passes
Value: 865
*************************** 213. row ***************************
Variable_name: Sort_range
Value: 54945626
*************************** 214. row ***************************
Variable_name: Sort_rows
Value: 36524365319
*************************** 215. row ***************************
Variable_name: Sort_scan
Value: 81906067
*************************** 216. row ***************************
Variable_name: Ssl_accept_renegotiates
Value: 0
*************************** 217. row ***************************
Variable_name: Ssl_accepts
Value: 0
*************************** 218. row ***************************
Variable_name: Ssl_callback_cache_hits
Value: 0
*************************** 219. row ***************************
Variable_name: Ssl_cipher
Value:
*************************** 220. row ***************************
Variable_name: Ssl_cipher_list
Value:
*************************** 221. row ***************************
Variable_name: Ssl_client_connects
Value: 0
*************************** 222. row ***************************
Variable_name: Ssl_connect_renegotiates
Value: 0
*************************** 223. row ***************************
Variable_name: Ssl_ctx_verify_depth
Value: 0
*************************** 224. row ***************************
Variable_name: Ssl_ctx_verify_mode
Value: 0
*************************** 225. row ***************************
Variable_name: Ssl_default_timeout
Value: 0
*************************** 226. row ***************************
Variable_name: Ssl_finished_accepts
Value: 0
*************************** 227. row ***************************
Variable_name: Ssl_finished_connects
Value: 0
*************************** 228. row ***************************
Variable_name: Ssl_session_cache_hits
Value: 0
*************************** 229. row ***************************
Variable_name: Ssl_session_cache_misses
Value: 0
*************************** 230. row ***************************
Variable_name: Ssl_session_cache_mode
Value: NONE
*************************** 231. row ***************************
Variable_name: Ssl_session_cache_overflows
Value: 0
*************************** 232. row ***************************
Variable_name: Ssl_session_cache_size
Value: 0
*************************** 233. row ***************************
Variable_name: Ssl_session_cache_timeouts
Value: 0
*************************** 234. row ***************************
Variable_name: Ssl_sessions_reused
Value: 0
*************************** 235. row ***************************
Variable_name: Ssl_used_session_cache_entries
Value: 0
*************************** 236. row ***************************
Variable_name: Ssl_verify_depth
Value: 0
*************************** 237. row ***************************
Variable_name: Ssl_verify_mode
Value: 0
*************************** 238. row ***************************
Variable_name: Ssl_version
Value:
*************************** 239. row ***************************
Variable_name: Table_locks_immediate
Value: 265807598
*************************** 240. row ***************************
Variable_name: Table_locks_waited
Value: 16684
*************************** 241. row ***************************
Variable_name: Tc_log_max_pages_used
Value: 0
*************************** 242. row ***************************
Variable_name: Tc_log_page_size
Value: 0
*************************** 243. row ***************************
Variable_name: Tc_log_page_waits
Value: 0
*************************** 244. row ***************************
Variable_name: Threads_cached
Value: 0
*************************** 245. row ***************************
Variable_name: Threads_connected
Value: 26
*************************** 246. row ***************************
Variable_name: Threads_created
Value: 82087767
*************************** 247. row ***************************
Variable_name: Threads_running
Value: 17
*************************** 248. row ***************************
Variable_name: Uptime
Value: 5720715
*************************** 249. row ***************************
Variable_name: Uptime_since_flush_status
Value: 634447
249 rows in set (0.53 sec)
thanks for your help and replies.
To answer your questions:
90% of the table are InnoDB. The queries I see being backed up at 8:00pm are only using InnoDB tables though.
We are on Mysql 5.0.45, which I don't believe supports events, and we wouldn't use them.
Yes, there are queries in the slow query log from this time (and the log is only populated from queries at this time), however they are exactly the same as the queries I see in "SHOW INNODB STATUS \G".
They are only SELECT queries, no UPDATE or INSERTS (nothing ever shows locked).
These queries run from all users, pretty much 24/7, but still, we only see any issues starting at exactly 8:00:00 pm. I can run a combination of these queries at any other time, with no problems.
My current thought is that there is some external process/cleanup being performed by the (OS/Mysql), that causes the queries to back up - vs the queries causing Mysql to backup.
I have attached the info you requested below - sorry if I sound like I am on the wrong track, but I appreciate you taking your time to respond to people on the forums and I just don't want to waste your time if it doesn't sound like Mysql is the root cause.
Note: We have 64GB RAM, and I have set the innodb_thread_concurrency=4 because it seems to help the server recover quicker during the stall.
mysql> SHOW VARIABLES LIKE 'innodb%';
+---------------------------------+-------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------+
| innodb_additional_mem_pool_size | 268435456 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 25769803776 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:100M:autoextend |
| innodb_data_home_dir | /var/lib/mysql/ |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | /var/lib/mysql/ |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 33554432 |
| innodb_log_file_size | 536870912 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | /var/lib/mysql/ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 4 |
| innodb_thread_sleep_delay | 10000 |
+---------------------------------+-------------------------+
34 rows in set (0.00 sec)
mysql> SHOW GLOBAL STATUS\G
*************************** 1. row ***************************
Variable_name: Aborted_clients
Value: 5048
*************************** 2. row ***************************
Variable_name: Aborted_connects
Value: 7
*************************** 3. row ***************************
Variable_name: Binlog_cache_disk_use
Value: 0
*************************** 4. row ***************************
Variable_name: Binlog_cache_use
Value: 17895734
*************************** 5. row ***************************
Variable_name: Bytes_received
Value: 506756490178
*************************** 6. row ***************************
Variable_name: Bytes_sent
Value: 3493442799144
*************************** 7. row ***************************
Variable_name: Com_admin_commands
Value: 16
*************************** 8. row ***************************
Variable_name: Com_alter_db
Value: 0
*************************** 9. row ***************************
Variable_name: Com_alter_table
Value: 14
*************************** 10. row ***************************
Variable_name: Com_analyze
Value: 0
*************************** 11. row ***************************
Variable_name: Com_backup_table
Value: 0
*************************** 12. row ***************************
Variable_name: Com_begin
Value: 0
*************************** 13. row ***************************
Variable_name: Com_call_procedure
Value: 0
*************************** 14. row ***************************
Variable_name: Com_change_db
Value: 1152447099
*************************** 15. row ***************************
Variable_name: Com_change_master
Value: 0
*************************** 16. row ***************************
Variable_name: Com_check
Value: 0
*************************** 17. row ***************************
Variable_name: Com_checksum
Value: 0
*************************** 18. row ***************************
Variable_name: Com_commit
Value: 0
*************************** 19. row ***************************
Variable_name: Com_create_db
Value: 0
*************************** 20. row ***************************
Variable_name: Com_create_function
Value: 0
*************************** 21. row ***************************
Variable_name: Com_create_index
Value: 0
*************************** 22. row ***************************
Variable_name: Com_create_table
Value: 4
*************************** 23. row ***************************
Variable_name: Com_create_user
Value: 0
*************************** 24. row ***************************
Variable_name: Com_dealloc_sql
Value: 0
*************************** 25. row ***************************
Variable_name: Com_delete
Value: 24853797
*************************** 26. row ***************************
Variable_name: Com_delete_multi
Value: 84232
*************************** 27. row ***************************
Variable_name: Com_do
Value: 0
*************************** 28. row ***************************
Variable_name: Com_drop_db
Value: 0
*************************** 29. row ***************************
Variable_name: Com_drop_function
Value: 0
*************************** 30. row ***************************
Variable_name: Com_drop_index
Value: 0
*************************** 31. row ***************************
Variable_name: Com_drop_table
Value: 0
*************************** 32. row ***************************
Variable_name: Com_drop_user
Value: 0
*************************** 33. row ***************************
Variable_name: Com_execute_sql
Value: 0
*************************** 34. row ***************************
Variable_name: Com_flush
Value: 10
*************************** 35. row ***************************
Variable_name: Com_grant
Value: 0
*************************** 36. row ***************************
Variable_name: Com_ha_close
Value: 0
*************************** 37. row ***************************
Variable_name: Com_ha_open
Value: 0
*************************** 38. row ***************************
Variable_name: Com_ha_read
Value: 0
*************************** 39. row ***************************
Variable_name: Com_help
Value: 0
*************************** 40. row ***************************
Variable_name: Com_insert
Value: 53113416
*************************** 41. row ***************************
Variable_name: Com_insert_select
Value: 60330
*************************** 42. row ***************************
Variable_name: Com_kill
Value: 121
*************************** 43. row ***************************
Variable_name: Com_load
Value: 0
*************************** 44. row ***************************
Variable_name: Com_load_master_data
Value: 0
*************************** 45. row ***************************
Variable_name: Com_load_master_table
Value: 0
*************************** 46. row ***************************
Variable_name: Com_lock_tables
Value: 0
*************************** 47. row ***************************
Variable_name: Com_optimize
Value: 0
*************************** 48. row ***************************
Variable_name: Com_preload_keys
Value: 0
*************************** 49. row ***************************
Variable_name: Com_prepare_sql
Value: 0
*************************** 50. row ***************************
Variable_name: Com_purge
Value: 0
*************************** 51. row ***************************
Variable_name: Com_purge_before_date
Value: 0
*************************** 52. row ***************************
Variable_name: Com_rename_table
Value: 0
*************************** 53. row ***************************
Variable_name: Com_repair
Value: 0
*************************** 54. row ***************************
Variable_name: Com_replace
Value: 0
*************************** 55. row ***************************
Variable_name: Com_replace_select
Value: 0
*************************** 56. row ***************************
Variable_name: Com_reset
Value: 0
*************************** 57. row ***************************
Variable_name: Com_restore_table
Value: 0
*************************** 58. row ***************************
Variable_name: Com_revoke
Value: 0
*************************** 59. row ***************************
Variable_name: Com_revoke_all
Value: 0
*************************** 60. row ***************************
Variable_name: Com_rollback
Value: 0
*************************** 61. row ***************************
Variable_name: Com_savepoint
Value: 0
*************************** 62. row ***************************
Variable_name: Com_select
Value: 980306222
*************************** 63. row ***************************
Variable_name: Com_set_option
Value: 8504
*************************** 64. row ***************************
Variable_name: Com_show_binlog_events
Value: 0
*************************** 65. row ***************************
Variable_name: Com_show_binlogs
Value: 176
*************************** 66. row ***************************
Variable_name: Com_show_charsets
Value: 1986
*************************** 67. row ***************************
Variable_name: Com_show_collations
Value: 1986
*************************** 68. row ***************************
Variable_name: Com_show_column_types
Value: 0
*************************** 69. row ***************************
Variable_name: Com_show_create_db
Value: 16
*************************** 70. row ***************************
Variable_name: Com_show_create_table
Value: 841
*************************** 71. row ***************************
Variable_name: Com_show_databases
Value: 318
*************************** 72. row ***************************
Variable_name: Com_show_errors
Value: 0
*************************** 73. row ***************************
Variable_name: Com_show_fields
Value: 152325
*************************** 74. row ***************************
Variable_name: Com_show_grants
Value: 1107
*************************** 75. row ***************************
Variable_name: Com_show_innodb_status
Value: 208
*************************** 76. row ***************************
Variable_name: Com_show_keys
Value: 699
*************************** 77. row ***************************
Variable_name: Com_show_logs
Value: 0
*************************** 78. row ***************************
Variable_name: Com_show_master_status
Value: 4
*************************** 79. row ***************************
Variable_name: Com_show_ndb_status
Value: 0
*************************** 80. row ***************************
Variable_name: Com_show_new_master
Value: 0
*************************** 81. row ***************************
Variable_name: Com_show_open_tables
Value: 0
*************************** 82. row ***************************
Variable_name: Com_show_privileges
Value: 0
*************************** 83. row ***************************
Variable_name: Com_show_processlist
Value: 1503
*************************** 84. row ***************************
Variable_name: Com_show_slave_hosts
Value: 5
*************************** 85. row ***************************
Variable_name: Com_show_slave_status
Value: 1
*************************** 86. row ***************************
Variable_name: Com_show_status
Value: 7
*************************** 87. row ***************************
Variable_name: Com_show_storage_engines
Value: 22
*************************** 88. row ***************************
Variable_name: Com_show_tables
Value: 521388
*************************** 89. row ***************************
Variable_name: Com_show_triggers
Value: 549
*************************** 90. row ***************************
Variable_name: Com_show_variables
Value: 4149
*************************** 91. row ***************************
Variable_name: Com_show_warnings
Value: 0
*************************** 92. row ***************************
Variable_name: Com_slave_start
Value: 0
*************************** 93. row ***************************
Variable_name: Com_slave_stop
Value: 0
*************************** 94. row ***************************
Variable_name: Com_stmt_close
Value: 0
*************************** 95. row ***************************
Variable_name: Com_stmt_execute
Value: 0
*************************** 96. row ***************************
Variable_name: Com_stmt_fetch
Value: 0
*************************** 97. row ***************************
Variable_name: Com_stmt_prepare
Value: 0
*************************** 98. row ***************************
Variable_name: Com_stmt_reset
Value: 0
*************************** 99. row ***************************
Variable_name: Com_stmt_send_long_data
Value: 0
*************************** 100. row ***************************
Variable_name: Com_truncate
Value: 1
*************************** 101. row ***************************
Variable_name: Com_unlock_tables
Value: 0
*************************** 102. row ***************************
Variable_name: Com_update
Value: 114614914
*************************** 103. row ***************************
Variable_name: Com_update_multi
Value: 4812991
*************************** 104. row ***************************
Variable_name: Com_xa_commit
Value: 0
*************************** 105. row ***************************
Variable_name: Com_xa_end
Value: 0
*************************** 106. row ***************************
Variable_name: Com_xa_prepare
Value: 0
*************************** 107. row ***************************
Variable_name: Com_xa_recover
Value: 0
*************************** 108. row ***************************
Variable_name: Com_xa_rollback
Value: 0
*************************** 109. row ***************************
Variable_name: Com_xa_start
Value: 0
*************************** 110. row ***************************
Variable_name: Compression
Value: OFF
*************************** 111. row ***************************
Variable_name: Connections
Value: 82087768
*************************** 112. row ***************************
Variable_name: Created_tmp_disk_tables
Value: 72973686
*************************** 113. row ***************************
Variable_name: Created_tmp_files
Value: 126
*************************** 114. row ***************************
Variable_name: Created_tmp_tables
Value: 110070619
*************************** 115. row ***************************
Variable_name: Delayed_errors
Value: 0
*************************** 116. row ***************************
Variable_name: Delayed_insert_threads
Value: 0
*************************** 117. row ***************************
Variable_name: Delayed_writes
Value: 0
*************************** 118. row ***************************
Variable_name: Flush_commands
Value: 7
*************************** 119. row ***************************
Variable_name: Handler_commit
Value: 349842333
*************************** 120. row ***************************
Variable_name: Handler_delete
Value: 3774296
*************************** 121. row ***************************
Variable_name: Handler_discover
Value: 0
*************************** 122. row ***************************
Variable_name: Handler_prepare
Value: 349572594
*************************** 123. row ***************************
Variable_name: Handler_read_first
Value: 28211124
*************************** 124. row ***************************
Variable_name: Handler_read_key
Value: 173376493216
*************************** 125. row ***************************
Variable_name: Handler_read_next
Value: 656018630883
*************************** 126. row ***************************
Variable_name: Handler_read_prev
Value: 26139618895
*************************** 127. row ***************************
Variable_name: Handler_read_rnd
Value: 6032665333
*************************** 128. row ***************************
Variable_name: Handler_read_rnd_next
Value: 466130609590
*************************** 129. row ***************************
Variable_name: Handler_rollback
Value: 18
*************************** 130. row ***************************
Variable_name: Handler_savepoint
Value: 0
*************************** 131. row ***************************
Variable_name: Handler_savepoint_rollback
Value: 0
*************************** 132. row ***************************
Variable_name: Handler_update
Value: 603046953
*************************** 133. row ***************************
Variable_name: Handler_write
Value: 28065551339
*************************** 134. row ***************************
Variable_name: Innodb_buffer_pool_pages_data
Value: 1426670
*************************** 135. row ***************************
Variable_name: Innodb_buffer_pool_pages_dirty
Value: 22931
*************************** 136. row ***************************
Variable_name: Innodb_buffer_pool_pages_flushed
Value: 26149023
*************************** 137. row ***************************
Variable_name: Innodb_buffer_pool_pages_free
Value: 8
*************************** 138. row ***************************
Variable_name: Innodb_buffer_pool_pages_latched
Value: 0
*************************** 139. row ***************************
Variable_name: Innodb_buffer_pool_pages_misc
Value: 146186
*************************** 140. row ***************************
Variable_name: Innodb_buffer_pool_pages_total
Value: 1572864
*************************** 141. row ***************************
Variable_name: Innodb_buffer_pool_read_ahead_rnd
Value: 3528
*************************** 142. row ***************************
Variable_name: Innodb_buffer_pool_read_ahead_seq
Value: 12253
*************************** 143. row ***************************
Variable_name: Innodb_buffer_pool_read_requests
Value: 1990063504595
*************************** 144. row ***************************
Variable_name: Innodb_buffer_pool_reads
Value: 1364800
*************************** 145. row ***************************
Variable_name: Innodb_buffer_pool_wait_free
Value: 0
*************************** 146. row ***************************
Variable_name: Innodb_buffer_pool_write_requests
Value: 2303430235
*************************** 147. row ***************************
Variable_name: Innodb_data_fsyncs
Value: 317846287
*************************** 148. row ***************************
Variable_name: Innodb_data_pending_fsyncs
Value: 0
*************************** 149. row ***************************
Variable_name: Innodb_data_pending_reads
Value: 0
*************************** 150. row ***************************
Variable_name: Innodb_data_pending_writes
Value: 0
*************************** 151. row ***************************
Variable_name: Innodb_data_read
Value: 29383479296
*************************** 152. row ***************************
Variable_name: Innodb_data_reads
Value: 1469520
*************************** 153. row ***************************
Variable_name: Innodb_data_writes
Value: 334441126
*************************** 154. row ***************************
Variable_name: Innodb_data_written
Value: 1129152164352
*************************** 155. row ***************************
Variable_name: Innodb_dblwr_pages_written
Value: 26149023
*************************** 156. row ***************************
Variable_name: Innodb_dblwr_writes
Value: 718121
*************************** 157. row ***************************
Variable_name: Innodb_log_waits
Value: 0
*************************** 158. row ***************************
Variable_name: Innodb_log_write_requests
Value: 234958439
*************************** 159. row ***************************
Variable_name: Innodb_log_writes
Value: 315837631
*************************** 160. row ***************************
Variable_name: Innodb_os_log_fsyncs
Value: 316422853
*************************** 161. row ***************************
Variable_name: Innodb_os_log_pending_fsyncs
Value: 0
*************************** 162. row ***************************
Variable_name: Innodb_os_log_pending_writes
Value: 0
*************************** 163. row ***************************
Variable_name: Innodb_os_log_written
Value: 272007970816
*************************** 164. row ***************************
Variable_name: Innodb_page_size
Value: 16384
*************************** 165. row ***************************
Variable_name: Innodb_pages_created
Value: 242000
*************************** 166. row ***************************
Variable_name: Innodb_pages_read
Value: 1793292
*************************** 167. row ***************************
Variable_name: Innodb_pages_written
Value: 26149023
*************************** 168. row ***************************
Variable_name: Innodb_row_lock_current_waits
Value: 0
*************************** 169. row ***************************
Variable_name: Innodb_row_lock_time
Value: 2739954
*************************** 170. row ***************************
Variable_name: Innodb_row_lock_time_avg
Value: 29
*************************** 171. row ***************************
Variable_name: Innodb_row_lock_time_max
Value: 19472
*************************** 172. row ***************************
Variable_name: Innodb_row_lock_waits
Value: 93702
*************************** 173. row ***************************
Variable_name: Innodb_rows_deleted
Value: 25042555
*************************** 174. row ***************************
Variable_name: Innodb_rows_inserted
Value: 42300390
*************************** 175. row ***************************
Variable_name: Innodb_rows_read
Value: 781235682144
*************************** 176. row ***************************
Variable_name: Innodb_rows_updated
Value: 169987894
*************************** 177. row ***************************
Variable_name: Key_blocks_not_flushed
Value: 0
*************************** 178. row ***************************
Variable_name: Key_blocks_unused
Value: 26
*************************** 179. row ***************************
Variable_name: Key_blocks_used
Value: 857368
*************************** 180. row ***************************
Variable_name: Key_read_requests
Value: 7421308578
*************************** 181. row ***************************
Variable_name: Key_reads
Value: 6344459
*************************** 182. row ***************************
Variable_name: Key_write_requests
Value: 429851659
*************************** 183. row ***************************
Variable_name: Key_writes
Value: 6346002
*************************** 184. row ***************************
Variable_name: Last_query_cost
Value: 0.000000
*************************** 185. row ***************************
Variable_name: Max_used_connections
Value: 361
*************************** 186. row ***************************
Variable_name: Not_flushed_delayed_rows
Value: 0
*************************** 187. row ***************************
Variable_name: Open_files
Value: 1857
*************************** 188. row ***************************
Variable_name: Open_streams
Value: 0
*************************** 189. row ***************************
Variable_name: Open_tables
Value: 4058
*************************** 190. row ***************************
Variable_name: Opened_tables
Value: 9776
*************************** 191. row ***************************
Variable_name: Prepared_stmt_count
Value: 0
*************************** 192. row ***************************
Variable_name: Qcache_free_blocks
Value: 0
*************************** 193. row ***************************
Variable_name: Qcache_free_memory
Value: 0
*************************** 194. row ***************************
Variable_name: Qcache_hits
Value: 0
*************************** 195. row ***************************
Variable_name: Qcache_inserts
Value: 0
*************************** 196. row ***************************
Variable_name: Qcache_lowmem_prunes
Value: 0
*************************** 197. row ***************************
Variable_name: Qcache_not_cached
Value: 0
*************************** 198. row ***************************
Variable_name: Qcache_queries_in_cache
Value: 0
*************************** 199. row ***************************
Variable_name: Qcache_total_blocks
Value: 0
*************************** 200. row ***************************
Variable_name: Questions
Value: 2413200847
*************************** 201. row ***************************
Variable_name: Rpl_status
Value: NULL
*************************** 202. row ***************************
Variable_name: Select_full_join
Value: 4521186
*************************** 203. row ***************************
Variable_name: Select_full_range_join
Value: 1108581
*************************** 204. row ***************************
Variable_name: Select_range
Value: 127272799
*************************** 205. row ***************************
Variable_name: Select_range_check
Value: 0
*************************** 206. row ***************************
Variable_name: Select_scan
Value: 26013654
*************************** 207. row ***************************
Variable_name: Slave_open_temp_tables
Value: 0
*************************** 208. row ***************************
Variable_name: Slave_retried_transactions
Value: 0
*************************** 209. row ***************************
Variable_name: Slave_running
Value: OFF
*************************** 210. row ***************************
Variable_name: Slow_launch_threads
Value: 0
*************************** 211. row ***************************
Variable_name: Slow_queries
Value: 63336
*************************** 212. row ***************************
Variable_name: Sort_merge_passes
Value: 865
*************************** 213. row ***************************
Variable_name: Sort_range
Value: 54945626
*************************** 214. row ***************************
Variable_name: Sort_rows
Value: 36524365319
*************************** 215. row ***************************
Variable_name: Sort_scan
Value: 81906067
*************************** 216. row ***************************
Variable_name: Ssl_accept_renegotiates
Value: 0
*************************** 217. row ***************************
Variable_name: Ssl_accepts
Value: 0
*************************** 218. row ***************************
Variable_name: Ssl_callback_cache_hits
Value: 0
*************************** 219. row ***************************
Variable_name: Ssl_cipher
Value:
*************************** 220. row ***************************
Variable_name: Ssl_cipher_list
Value:
*************************** 221. row ***************************
Variable_name: Ssl_client_connects
Value: 0
*************************** 222. row ***************************
Variable_name: Ssl_connect_renegotiates
Value: 0
*************************** 223. row ***************************
Variable_name: Ssl_ctx_verify_depth
Value: 0
*************************** 224. row ***************************
Variable_name: Ssl_ctx_verify_mode
Value: 0
*************************** 225. row ***************************
Variable_name: Ssl_default_timeout
Value: 0
*************************** 226. row ***************************
Variable_name: Ssl_finished_accepts
Value: 0
*************************** 227. row ***************************
Variable_name: Ssl_finished_connects
Value: 0
*************************** 228. row ***************************
Variable_name: Ssl_session_cache_hits
Value: 0
*************************** 229. row ***************************
Variable_name: Ssl_session_cache_misses
Value: 0
*************************** 230. row ***************************
Variable_name: Ssl_session_cache_mode
Value: NONE
*************************** 231. row ***************************
Variable_name: Ssl_session_cache_overflows
Value: 0
*************************** 232. row ***************************
Variable_name: Ssl_session_cache_size
Value: 0
*************************** 233. row ***************************
Variable_name: Ssl_session_cache_timeouts
Value: 0
*************************** 234. row ***************************
Variable_name: Ssl_sessions_reused
Value: 0
*************************** 235. row ***************************
Variable_name: Ssl_used_session_cache_entries
Value: 0
*************************** 236. row ***************************
Variable_name: Ssl_verify_depth
Value: 0
*************************** 237. row ***************************
Variable_name: Ssl_verify_mode
Value: 0
*************************** 238. row ***************************
Variable_name: Ssl_version
Value:
*************************** 239. row ***************************
Variable_name: Table_locks_immediate
Value: 265807598
*************************** 240. row ***************************
Variable_name: Table_locks_waited
Value: 16684
*************************** 241. row ***************************
Variable_name: Tc_log_max_pages_used
Value: 0
*************************** 242. row ***************************
Variable_name: Tc_log_page_size
Value: 0
*************************** 243. row ***************************
Variable_name: Tc_log_page_waits
Value: 0
*************************** 244. row ***************************
Variable_name: Threads_cached
Value: 0
*************************** 245. row ***************************
Variable_name: Threads_connected
Value: 26
*************************** 246. row ***************************
Variable_name: Threads_created
Value: 82087767
*************************** 247. row ***************************
Variable_name: Threads_running
Value: 17
*************************** 248. row ***************************
Variable_name: Uptime
Value: 5720715
*************************** 249. row ***************************
Variable_name: Uptime_since_flush_status
Value: 634447
249 rows in set (0.53 sec)
Subject
Views
Written By
Posted
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.