We have a problem with peak loading while writing data to database This is example of error

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction

SQL: insert into `responses` (`participant_id`, `survey_id`, `question_id`, `question_option_id`) values (2204017, 188131, 5, 23)

The table is quite big, 4.5 millions rows, per day at peak we write about 150-200 thousands rows to it. MySQL version is 5.6.39 Table uses InnoDb engine

Table structure

id  int(10) unsigned Auto Increment 
participant_id  int(10) unsigned    
survey_id   int(10) unsigned    
question_id int(10) unsigned    
question_option_id  int(10) unsigned NULL   
response_answer text NULL   
created_at  timestamp NULL  
updated_at  timestamp NULL


INDEX   participant_id
INDEX   survey_id
INDEX   question_id
INDEX   question_option_id

Foreign keys

participant_id -> participants(id)
question_id -> questions(id)
question_option_id -> question_options(id)
survey_id -> surveys(id)

MySQL instance configuration at RDS (Amazon service)

Instance class db.t2.xlarge
vCPU 4

InnoDB Settings

innodb_adaptive_flushing    ON
innodb_adaptive_flushing_lwm    10
innodb_adaptive_hash_index  ON
innodb_adaptive_max_sleep_delay 150000
innodb_additional_mem_pool_size 8388608
innodb_api_bk_commit_interval   5
innodb_api_disable_rowlock  OFF
innodb_api_enable_binlog    OFF
innodb_api_enable_mdl   OFF
innodb_api_trx_level    0
innodb_autoextend_increment 64
innodb_autoinc_lock_mode    1
innodb_buffer_pool_dump_at_shutdown OFF
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_instances    8
innodb_buffer_pool_load_abort   OFF
innodb_buffer_pool_load_at_startup  OFF
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_size 12465471488
innodb_change_buffer_max_size   25
innodb_change_buffering all
innodb_checksum_algorithm   innodb
innodb_checksums    ON
innodb_cmp_per_index_enabled    OFF
innodb_commit_concurrency   0
innodb_compression_failure_threshold_pct    5
innodb_compression_level    6
innodb_compression_pad_pct_max  50
innodb_concurrency_tickets  5000
innodb_data_file_path   ibdata1:12M:autoextend
innodb_data_home_dir    /rdsdbdata/db/innodb
innodb_disable_sort_file_cache  OFF
innodb_doublewrite  ON
innodb_fast_shutdown    1
innodb_file_format  Antelope
innodb_file_format_check    ON
innodb_file_format_max  Antelope
innodb_file_per_table   ON
innodb_flush_log_at_timeout 1
innodb_flush_log_at_trx_commit  1
innodb_flush_method O_DIRECT
innodb_flush_neighbors  1
innodb_flushing_avg_loops   30
innodb_force_load_corrupted OFF
innodb_force_recovery   0
innodb_ft_cache_size    8000000
innodb_ft_enable_diag_print OFF
innodb_ft_enable_stopword   ON
innodb_ft_max_token_size    84
innodb_ft_min_token_size    3
innodb_ft_num_word_optimize 2000
innodb_ft_result_cache_limit    2000000000
innodb_ft_sort_pll_degree   2
innodb_ft_total_cache_size  640000000
innodb_io_capacity  200
innodb_io_capacity_max  2000
innodb_large_prefix OFF
innodb_lock_wait_timeout    50
innodb_locks_unsafe_for_binlog  OFF
innodb_log_buffer_size  8388608
innodb_log_compressed_pages ON
innodb_log_file_size    134217728
innodb_log_files_in_group   2
innodb_log_group_home_dir   /rdsdbdata/log/innodb
innodb_lru_scan_depth   1024
innodb_max_dirty_pages_pct  75
innodb_max_dirty_pages_pct_lwm  0
innodb_max_purge_lag    0
innodb_max_purge_lag_delay  0
innodb_mirrored_log_groups  1
innodb_numa_interleave  OFF
innodb_old_blocks_pct   37
innodb_old_blocks_time  1000
innodb_online_alter_log_max_size    134217728
innodb_open_files   2000
innodb_optimize_fulltext_only   OFF
innodb_page_size    16384
innodb_print_all_deadlocks  OFF
innodb_purge_batch_size 300
innodb_purge_threads    1
innodb_random_read_ahead    OFF
innodb_read_ahead_threshold 56
innodb_read_io_threads  4
innodb_read_only    OFF
innodb_replication_delay    0
innodb_rollback_on_timeout  OFF
innodb_rollback_segments    128
innodb_sort_buffer_size 1048576
innodb_spin_wait_delay  6
innodb_stats_auto_recalc    ON
innodb_stats_include_delete_marked  OFF
innodb_stats_method nulls_equal
innodb_stats_on_metadata    OFF
innodb_stats_persistent ON
innodb_stats_persistent_sample_pages    20
innodb_stats_sample_pages   8
innodb_stats_transient_sample_pages 8
innodb_status_output    OFF
innodb_status_output_locks  OFF
innodb_strict_mode  OFF
innodb_support_xa   ON
innodb_sync_array_size  1
innodb_sync_spin_loops  30
innodb_table_locks  ON
innodb_thread_concurrency   0
innodb_thread_sleep_delay   10000
innodb_undo_directory   .
innodb_undo_logs    128
innodb_undo_tablespaces 0
innodb_use_native_aio   ON
innodb_use_sys_malloc   ON
innodb_version  5.6.39
innodb_write_io_threads 4

What can be a problem? Can i solve it by tuning settings of MySQL/InnoDB?

  • Seams that transaction is timeout by a running query/transaction ... run SHOW FULL PROCESSLIST and SHOW ENGINE INNODB STATUS – Raymond Nijland Oct 22 '19 at 13:03
  • i already rebooted mysql server and now it is mostly idle 442 rdsadmin localhost:12869 NULL Sleep 10 NULL 4215 ebroot ebdb Query 0 init SHOW FULL PROCESSLIST 4216 ebroot ebdb Sleep 0 NULL – ifrond Oct 22 '19 at 13:54
  • SHOW ENGINE INNODB STATUS has very big output, can you please specify what is required from it – ifrond Oct 22 '19 at 13:57
  • Well you needed to find the Process ID and transaction ID in there.. But that is now pretty much impossible after the restart.. – Raymond Nijland Oct 22 '19 at 14:17
  • Is that 150K-200K rows per day? Are they inserted one row at a time? Do they come at the about the same time of day? How many clients are feeding data into the database. What is the value of max_connections? – Rick James Oct 23 '19 at 1:05

This is not a complete answer, but I need 50 rep to comment. Check out this similar question - > https://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded-on-mysql . The first two answers have some steps you can follow to nail down what query is causing the lock and a possible resolution with setting your innodb_lock_wait_timeout value higher.

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