5

Edit #2: I have now tried two MariaDB databases, one MySQL, and one Percona, and only the MariaDB instances are locking.

I'm having this lock wait timeout exceeded issue with a MariadB (version 5.5.56) on a remote server that I'm not having on a local MySQL (version 5.5.57) instance. I know this because I exported the remote database, then imported it locally, and the problem goes away.

To be sure, the query is very large. Let's just say it's a very large select query with a ton of left and inner joins (see below).

I have tried setting the remote my.cnf file up to be nearly identical to my local server, but running SHOW VARIABLES; there is a pretty big difference, and I really would rather not go through the differences (shown below) line-by-line.

I'm just wondering if anyone has any clue where to go from here? I have tried some of the suggestions like the ones posted here without any luck.

If someone thinks the easiest thing to do is just try to upgrade to MariadB 10.x, I'm game to give that a go.

Thanks.

EDIT: Attaching Diff and SQL Query

SQL Query

SELECT commerce_product.product_id AS product_id, commerce_product.sku AS commerce_product_sku, taxonomy_term_data_field_data_field_sample_name.name AS taxonomy_term_data_field_data_field_sample_name_name, taxonomy_term_data_field_data_field_sample_name.vid AS taxonomy_term_data_field_data_field_sample_name_vid, taxonomy_term_data_field_data_field_sample_name.tid AS taxonomy_term_data_field_data_field_sample_name_tid, taxonomy_term_data_field_data_field_sample_name__taxonomy_vocabulary.machine_name AS taxonomy_term_data_field_data_field_sample_name__taxonomy_vo, taxonomy_term_data_field_data_field_sample_origin.name AS taxonomy_term_data_field_data_field_sample_origin_name, taxonomy_term_data_field_data_field_sample_origin.vid AS taxonomy_term_data_field_data_field_sample_origin_vid, taxonomy_term_data_field_data_field_sample_origin.tid AS taxonomy_term_data_field_data_field_sample_origin_tid, taxonomy_term_data_field_data_field_sample_origin__taxonomy_vocabulary.machine_name AS taxonomy_term_data_field_data_field_sample_origin__taxonomy_, taxonomy_term_data_field_data_field_sample_matrix.name AS taxonomy_term_data_field_data_field_sample_matrix_name, taxonomy_term_data_field_data_field_sample_matrix.vid AS taxonomy_term_data_field_data_field_sample_matrix_vid, taxonomy_term_data_field_data_field_sample_matrix.tid AS taxonomy_term_data_field_data_field_sample_matrix_tid, taxonomy_term_data_field_data_field_sample_matrix__taxonomy_vocabulary.machine_name AS taxonomy_term_data_field_data_field_sample_matrix__taxonomy_, taxonomy_term_data_field_data_field_sample_volume.name AS taxonomy_term_data_field_data_field_sample_volume_name, taxonomy_term_data_field_data_field_sample_volume.vid AS taxonomy_term_data_field_data_field_sample_volume_vid, taxonomy_term_data_field_data_field_sample_volume.tid AS taxonomy_term_data_field_data_field_sample_volume_tid, taxonomy_term_data_field_data_field_sample_volume__taxonomy_vocabulary.machine_name AS taxonomy_term_data_field_data_field_sample_volume__taxonomy_, 'commerce_product' AS field_data_title_field_commerce_product_entity_type, 'commerce_product' AS field_data_commerce_stock_commerce_product_entity_type, 'commerce_product' AS field_data_field_minimum_order_commerce_product_entity_type, 'commerce_product' AS field_data_commerce_price_commerce_product_entity_type, 'commerce_product' AS field_data_field_sample_patient_age_commerce_product_entity_, 'commerce_product' AS field_data_field_sample_patient_gender_commerce_product_enti, 'commerce_product' AS field_data_field_sample_patient_ethnicity_commerce_product_e, 'commerce_product' AS field_data_field_measurement_commerce_product_entity_type, 'commerce_product' AS field_data_field_unit_of_measure_commerce_product_entity_typ, 'commerce_product' AS field_data_field_icd10_code_commerce_product_entity_type, 'commerce_product' AS field_data_field_sample_storage_temp_commerce_product_entity, 'commerce_product' AS field_data_field_sample_date_commerce_product_entity_type, 'commerce_product' AS field_data_field_sample_test_1_commerce_product_entity_type, 'commerce_product' AS field_data_field_sample_test_data_1_commerce_product_entity_, 'commerce_product' AS field_data_field_sample_detail_1_commerce_product_entity_typ, 'commerce_product' AS field_data_field_sample_test_2_commerce_product_entity_type, 'commerce_product' AS field_data_field_sample_test_data_2_commerce_product_entity_, 'commerce_product' AS field_data_field_sample_detail_2_commerce_product_entity_typ, 'commerce_product' AS field_data_field_sample_test_3_commerce_product_entity_type, 'commerce_product' AS field_data_field_sample_test_data_3_commerce_product_entity_, 'commerce_product' AS field_data_field_sample_detail_3_commerce_product_entity_typ, 'commerce_product' AS field_data_field_pdf_commerce_product_entity_type, 'commerce_product' AS field_data_field_additional_information_1_commerce_product_e, 'commerce_product' AS field_data_field_additional_data_1_commerce_product_entity_t, 'commerce_product' AS field_data_field_additional_detail_1_commerce_product_entity, 'commerce_product' AS field_data_field_additional_information_2_commerce_product_e, 'commerce_product' AS field_data_field_additional_data_2_commerce_product_entity_t, 'commerce_product' AS field_data_field_additional_detail_2_commerce_product_entity, 'commerce_product' AS field_data_field_additional_information_3_commerce_product_e, 'commerce_product' AS field_data_field_additional_data_3_commerce_product_entity_t, 'commerce_product' AS field_data_field_additional_detail_3_commerce_product_entity, 'commerce_product' AS field_data_field_additional_information_4_commerce_product_e, 'commerce_product' AS field_data_field_additional_data_4_commerce_product_entity_t, 'commerce_product' AS field_data_field_additional_detail_4_commerce_product_entity, 'commerce_product' AS field_data_field_additional_information_5_commerce_product_e, 'commerce_product' AS field_data_field_additional_data_5_commerce_product_entity_t, 'commerce_product' AS field_data_field_additional_detail_5_commerce_product_entity, 'commerce_product' AS field_data_field_additional_information_6_commerce_product_e, 'commerce_product' AS field_data_field_additional_data_6_commerce_product_entity_t, 'commerce_product' AS field_data_field_additional_detail_6_commerce_product_entity, 'commerce_product' AS field_data_field_additional_information_7_commerce_product_e, 'commerce_product' AS field_data_field_additional_data_7_commerce_product_entity_t, 'commerce_product' AS field_data_field_additional_detail_7_commerce_product_entity, 'commerce_product' AS field_data_field_sample_matrix_commerce_product_entity_type, 'commerce_product' AS field_data_field_sample_origin_commerce_product_entity_type
FROM 
{commerce_product} commerce_product
LEFT JOIN {field_data_field_sample_category} field_data_field_sample_category ON commerce_product.product_id = field_data_field_sample_category.entity_id AND (field_data_field_sample_category.entity_type = 'commerce_product' AND field_data_field_sample_category.deleted = '0')
INNER JOIN {taxonomy_term_data} taxonomy_term_data_field_data_field_sample_category ON field_data_field_sample_category.field_sample_category_tid = taxonomy_term_data_field_data_field_sample_category.tid
LEFT JOIN {field_data_field_sample_name} field_data_field_sample_name ON commerce_product.product_id = field_data_field_sample_name.entity_id AND (field_data_field_sample_name.entity_type = 'commerce_product' AND field_data_field_sample_name.deleted = '0')
INNER JOIN {taxonomy_term_data} taxonomy_term_data_field_data_field_sample_name ON field_data_field_sample_name.field_sample_name_tid = taxonomy_term_data_field_data_field_sample_name.tid
LEFT JOIN {field_data_field_sample_matrix} field_data_field_sample_matrix ON commerce_product.product_id = field_data_field_sample_matrix.entity_id AND (field_data_field_sample_matrix.entity_type = 'commerce_product' AND field_data_field_sample_matrix.deleted = '0')
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_field_data_field_sample_matrix ON field_data_field_sample_matrix.field_sample_matrix_tid = taxonomy_term_data_field_data_field_sample_matrix.tid
LEFT JOIN {field_data_field_sample_origin} field_data_field_sample_origin ON commerce_product.product_id = field_data_field_sample_origin.entity_id AND (field_data_field_sample_origin.entity_type = 'commerce_product' AND field_data_field_sample_origin.deleted = '0')
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_field_data_field_sample_origin ON field_data_field_sample_origin.field_sample_origin_tid = taxonomy_term_data_field_data_field_sample_origin.tid
LEFT JOIN {field_data_field_sample_volume} field_data_field_sample_volume ON commerce_product.product_id = field_data_field_sample_volume.entity_id AND (field_data_field_sample_volume.entity_type = 'commerce_product' AND field_data_field_sample_volume.deleted = '0')
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_field_data_field_sample_volume ON field_data_field_sample_volume.field_sample_volume_tid = taxonomy_term_data_field_data_field_sample_volume.tid
LEFT JOIN {field_data_commerce_stock} field_data_commerce_stock ON commerce_product.product_id = field_data_commerce_stock.entity_id AND (field_data_commerce_stock.entity_type = 'commerce_product' AND field_data_commerce_stock.deleted = '0')
INNER JOIN {field_data_field_qc_sample_verified} field_data_field_qc_sample_verified ON commerce_product.product_id = field_data_field_qc_sample_verified.entity_id AND (field_data_field_qc_sample_verified.entity_type = 'commerce_product' AND field_data_field_qc_sample_verified.deleted = '0')
LEFT JOIN {field_data_field_sample_results} field_data_field_sample_results ON commerce_product.product_id = field_data_field_sample_results.entity_id AND (field_data_field_sample_results.entity_type = 'commerce_product' AND field_data_field_sample_results.deleted = '0')
LEFT JOIN {field_data_field_sample_detail_1} field_data_field_sample_detail_1 ON commerce_product.product_id = field_data_field_sample_detail_1.entity_id AND (field_data_field_sample_detail_1.entity_type = 'commerce_product' AND field_data_field_sample_detail_1.deleted = '0')
LEFT JOIN {field_data_field_sample_detail_2} field_data_field_sample_detail_2 ON commerce_product.product_id = field_data_field_sample_detail_2.entity_id AND (field_data_field_sample_detail_2.entity_type = 'commerce_product' AND field_data_field_sample_detail_2.deleted = '0')
LEFT JOIN {field_data_field_sample_detail_3} field_data_field_sample_detail_3 ON commerce_product.product_id = field_data_field_sample_detail_3.entity_id AND (field_data_field_sample_detail_3.entity_type = 'commerce_product' AND field_data_field_sample_detail_3.deleted = '0')
LEFT JOIN {field_data_field_sample_test_1} field_data_field_sample_test_1 ON commerce_product.product_id = field_data_field_sample_test_1.entity_id AND (field_data_field_sample_test_1.entity_type = 'commerce_product' AND field_data_field_sample_test_1.deleted = '0')
LEFT JOIN {field_data_field_sample_test_2} field_data_field_sample_test_2 ON commerce_product.product_id = field_data_field_sample_test_2.entity_id AND (field_data_field_sample_test_2.entity_type = 'commerce_product' AND field_data_field_sample_test_2.deleted = '0')
LEFT JOIN {field_data_field_sample_test_3} field_data_field_sample_test_3 ON commerce_product.product_id = field_data_field_sample_test_3.entity_id AND (field_data_field_sample_test_3.entity_type = 'commerce_product' AND field_data_field_sample_test_3.deleted = '0')
LEFT JOIN {field_data_field_sample_test_data_1} field_data_field_sample_test_data_1 ON commerce_product.product_id = field_data_field_sample_test_data_1.entity_id AND (field_data_field_sample_test_data_1.entity_type = 'commerce_product' AND field_data_field_sample_test_data_1.deleted = '0')
LEFT JOIN {field_data_field_sample_test_data_2} field_data_field_sample_test_data_2 ON commerce_product.product_id = field_data_field_sample_test_data_2.entity_id AND (field_data_field_sample_test_data_2.entity_type = 'commerce_product' AND field_data_field_sample_test_data_2.deleted = '0')
LEFT JOIN {field_data_field_sample_test_data_3} field_data_field_sample_test_data_3 ON commerce_product.product_id = field_data_field_sample_test_data_3.entity_id AND (field_data_field_sample_test_data_3.entity_type = 'commerce_product' AND field_data_field_sample_test_data_3.deleted = '0')
LEFT JOIN {field_data_field_additional_detail_1} field_data_field_additional_detail_1 ON commerce_product.product_id = field_data_field_additional_detail_1.entity_id AND (field_data_field_additional_detail_1.entity_type = 'commerce_product' AND field_data_field_additional_detail_1.deleted = '0')
LEFT JOIN {field_data_field_additional_detail_2} field_data_field_additional_detail_2 ON commerce_product.product_id = field_data_field_additional_detail_2.entity_id AND (field_data_field_additional_detail_2.entity_type = 'commerce_product' AND field_data_field_additional_detail_2.deleted = '0')
LEFT JOIN {field_data_field_additional_detail_3} field_data_field_additional_detail_3 ON commerce_product.product_id = field_data_field_additional_detail_3.entity_id AND (field_data_field_additional_detail_3.entity_type = 'commerce_product' AND field_data_field_additional_detail_3.deleted = '0')
LEFT JOIN {field_data_field_additional_detail_4} field_data_field_additional_detail_4 ON commerce_product.product_id = field_data_field_additional_detail_4.entity_id AND (field_data_field_additional_detail_4.entity_type = 'commerce_product' AND field_data_field_additional_detail_4.deleted = '0')
LEFT JOIN {field_data_field_additional_detail_5} field_data_field_additional_detail_5 ON commerce_product.product_id = field_data_field_additional_detail_5.entity_id AND (field_data_field_additional_detail_5.entity_type = 'commerce_product' AND field_data_field_additional_detail_5.deleted = '0')
LEFT JOIN {field_data_field_additional_detail_6} field_data_field_additional_detail_6 ON commerce_product.product_id = field_data_field_additional_detail_6.entity_id AND (field_data_field_additional_detail_6.entity_type = 'commerce_product' AND field_data_field_additional_detail_6.deleted = '0')
LEFT JOIN {field_data_field_additional_detail_7} field_data_field_additional_detail_7 ON commerce_product.product_id = field_data_field_additional_detail_7.entity_id AND (field_data_field_additional_detail_7.entity_type = 'commerce_product' AND field_data_field_additional_detail_7.deleted = '0')
LEFT JOIN {field_data_field_additional_data_1} field_data_field_additional_data_1 ON commerce_product.product_id = field_data_field_additional_data_1.entity_id AND (field_data_field_additional_data_1.entity_type = 'commerce_product' AND field_data_field_additional_data_1.deleted = '0')
LEFT JOIN {field_data_field_additional_data_2} field_data_field_additional_data_2 ON commerce_product.product_id = field_data_field_additional_data_2.entity_id AND (field_data_field_additional_data_2.entity_type = 'commerce_product' AND field_data_field_additional_data_2.deleted = '0')
LEFT JOIN {field_data_field_additional_data_3} field_data_field_additional_data_3 ON commerce_product.product_id = field_data_field_additional_data_3.entity_id AND (field_data_field_additional_data_3.entity_type = 'commerce_product' AND field_data_field_additional_data_3.deleted = '0')
LEFT JOIN {field_data_field_additional_data_4} field_data_field_additional_data_4 ON commerce_product.product_id = field_data_field_additional_data_4.entity_id AND (field_data_field_additional_data_4.entity_type = 'commerce_product' AND field_data_field_additional_data_4.deleted = '0')
LEFT JOIN {field_data_field_additional_data_5} field_data_field_additional_data_5 ON commerce_product.product_id = field_data_field_additional_data_5.entity_id AND (field_data_field_additional_data_5.entity_type = 'commerce_product' AND field_data_field_additional_data_5.deleted = '0')
LEFT JOIN {field_data_field_additional_data_6} field_data_field_additional_data_6 ON commerce_product.product_id = field_data_field_additional_data_6.entity_id AND (field_data_field_additional_data_6.entity_type = 'commerce_product' AND field_data_field_additional_data_6.deleted = '0')
LEFT JOIN {field_data_field_additional_data_7} field_data_field_additional_data_7 ON commerce_product.product_id = field_data_field_additional_data_7.entity_id AND (field_data_field_additional_data_7.entity_type = 'commerce_product' AND field_data_field_additional_data_7.deleted = '0')
LEFT JOIN {field_data_field_additional_information_1} field_data_field_additional_information_1 ON commerce_product.product_id = field_data_field_additional_information_1.entity_id AND (field_data_field_additional_information_1.entity_type = 'commerce_product' AND field_data_field_additional_information_1.deleted = '0')
LEFT JOIN {field_data_field_additional_information_2} field_data_field_additional_information_2 ON commerce_product.product_id = field_data_field_additional_information_2.entity_id AND (field_data_field_additional_information_2.entity_type = 'commerce_product' AND field_data_field_additional_information_2.deleted = '0')
LEFT JOIN {field_data_field_additional_information_3} field_data_field_additional_information_3 ON commerce_product.product_id = field_data_field_additional_information_3.entity_id AND (field_data_field_additional_information_3.entity_type = 'commerce_product' AND field_data_field_additional_information_3.deleted = '0')
LEFT JOIN {field_data_field_additional_information_4} field_data_field_additional_information_4 ON commerce_product.product_id = field_data_field_additional_information_4.entity_id AND (field_data_field_additional_information_4.entity_type = 'commerce_product' AND field_data_field_additional_information_4.deleted = '0')
LEFT JOIN {field_data_field_additional_information_5} field_data_field_additional_information_5 ON commerce_product.product_id = field_data_field_additional_information_5.entity_id AND (field_data_field_additional_information_5.entity_type = 'commerce_product' AND field_data_field_additional_information_5.deleted = '0')
LEFT JOIN {field_data_field_additional_information_6} field_data_field_additional_information_6 ON commerce_product.product_id = field_data_field_additional_information_6.entity_id AND (field_data_field_additional_information_6.entity_type = 'commerce_product' AND field_data_field_additional_information_6.deleted = '0')
LEFT JOIN {field_data_field_additional_information_7} field_data_field_additional_information_7 ON commerce_product.product_id = field_data_field_additional_information_7.entity_id AND (field_data_field_additional_information_7.entity_type = 'commerce_product' AND field_data_field_additional_information_7.deleted = '0')
LEFT JOIN {taxonomy_vocabulary} taxonomy_term_data_field_data_field_sample_name__taxonomy_vocabulary ON taxonomy_term_data_field_data_field_sample_name.vid = taxonomy_term_data_field_data_field_sample_name__taxonomy_vocabulary.vid
LEFT JOIN {taxonomy_vocabulary} taxonomy_term_data_field_data_field_sample_origin__taxonomy_vocabulary ON taxonomy_term_data_field_data_field_sample_origin.vid = taxonomy_term_data_field_data_field_sample_origin__taxonomy_vocabulary.vid
LEFT JOIN {taxonomy_vocabulary} taxonomy_term_data_field_data_field_sample_matrix__taxonomy_vocabulary ON taxonomy_term_data_field_data_field_sample_matrix.vid = taxonomy_term_data_field_data_field_sample_matrix__taxonomy_vocabulary.vid
LEFT JOIN {taxonomy_vocabulary} taxonomy_term_data_field_data_field_sample_volume__taxonomy_vocabulary ON taxonomy_term_data_field_data_field_sample_volume.vid = taxonomy_term_data_field_data_field_sample_volume__taxonomy_vocabulary.vid
WHERE (( (taxonomy_term_data_field_data_field_sample_category.tid = '1510' ) AND (taxonomy_term_data_field_data_field_sample_name.tid = '1001' ) )AND(( (commerce_product.status <> '0') AND (field_data_commerce_stock.commerce_stock_value > '0') AND (field_data_field_qc_sample_verified.field_qc_sample_verified_value = '1') )))
ORDER BY commerce_product_sku DESC

Variables Diff

"> = MariadB"
"< = MySQL"

> | aria_block_size | 8192|
> | aria_checkpoint_interval| 30|
> | aria_checkpoint_log_activity| 1048576 |
> | aria_force_start_after_recovery_failures| 0 |
> | aria_group_commit | none|
> | aria_group_commit_interval| 0 |
> | aria_log_file_size| 1073741824|
> | aria_log_purge_type | immediate |
> | aria_max_sort_file_size | 9223372036853727232 |
> | aria_page_checksum| ON|
> | aria_pagecache_age_threshold| 300 |
> | aria_pagecache_buffer_size| 134217728 |
> | aria_pagecache_division_limit | 100 |
> | aria_recover| NORMAL|
> | aria_repair_threads | 1 |
> | aria_sort_buffer_size | 134217728 |
> | aria_stats_method | nulls_unequal |
> | aria_sync_log_dir | NEWFILE |
> | aria_used_for_temp_tables | ON|
> | binlog_annotate_row_events| OFF |
> | binlog_checksum | NONE|
> | binlog_optimize_thread_scheduling | ON|
< | character_set_database| latin1 |
> | character_set_database| utf8|
< | collation_database| latin1_swedish_ci|
> | collation_database| utf8_general_ci |
> | deadlock_search_depth_long| 15|
> | deadlock_search_depth_short | 4 |
> | deadlock_timeout_long | 50000000|
> | deadlock_timeout_short| 10000 |
> | debug_no_thread_alarm | OFF |
< | engine_condition_pushdown | ON |
> | engine_condition_pushdown | OFF |
< | expire_logs_days| 10 |
> | expensive_subquery_limit| 100 |
> | expire_logs_days| 0 |
> | extra_max_connections | 1 |
> | extra_port| 0 |
< | have_symlink| YES|
> | have_symlink| DISABLED|
> | ignore_db_dirs| |
> | in_transaction| 0 |
> | innodb_adaptive_flushing_method | estimate|
> | innodb_adaptive_hash_index_partitions | 1 |
> | innodb_blocking_buffer_pool_restore | OFF |
> | innodb_buffer_pool_populate | OFF |
> | innodb_buffer_pool_restore_at_startup | 0 |
> | innodb_buffer_pool_shm_checksum | ON|
> | innodb_buffer_pool_shm_key| 0 |
> | innodb_checkpoint_age_target| 0 |
> | innodb_corrupt_table_action | assert|
> | innodb_dict_size_limit| 0 |
> | innodb_doublewrite_file | |
> | innodb_fake_changes | OFF |
> | innodb_fast_checksum| OFF |
< | innodb_flush_log_at_trx_commit| 1|
> | innodb_flush_log_at_trx_commit| 2 |
> | innodb_flush_neighbor_pages | area|
> | innodb_ibuf_accel_rate| 100 |
> | innodb_ibuf_active_contract | 1 |
> | innodb_ibuf_max_size| 67092480|
> | innodb_import_table_from_xtrabackup | 0 |
> | innodb_kill_idle_transaction| 0 |
< | innodb_lock_wait_timeout| 50 |
> | innodb_lazy_drop_table| 0 |
> | innodb_lock_wait_timeout| 180 |
> | innodb_locking_fake_changes | ON|
> | innodb_log_block_size | 512 |
> | innodb_max_bitmap_file_size | 104857600 |
> | innodb_max_changed_pages| 1000000 |
> | innodb_merge_sort_block_size| 1048576 |
> | innodb_page_size| 16384 |
< | innodb_purge_threads| 0|
> | innodb_purge_threads| 1 |
> | innodb_read_ahead | linear|
> | innodb_recovery_stats | OFF |
> | innodb_recovery_update_relay_log| OFF |
> | innodb_show_locks_held| 10|
> | innodb_show_verbose_locks | 0 |
> | innodb_simulate_comp_failures | 0 |
> | innodb_stats_auto_update| 1 |
> | innodb_stats_modified_counter | 0 |
> | innodb_stats_traditional| ON|
> | innodb_stats_update_need_lock | 1 |
> | innodb_thread_concurrency_timer_based | OFF |
> | innodb_track_changed_pages| OFF |
> | innodb_use_atomic_writes| OFF |
> | innodb_use_fallocate| OFF |
> | innodb_use_global_flush_log_at_trx_commit | ON|
> | innodb_use_stacktrace | OFF |
< | innodb_version| 5.5.57 |
> | innodb_use_sys_stats_table| OFF |
> | innodb_version| 5.5.52-MariaDB-38.3 |
< | interactive_timeout | 28800|
< | join_buffer_size| 131072 |
> | interactive_timeout | 25|
> | join_buffer_size| 2097152 |
> | join_buffer_space_limit | 2097152 |
> | join_cache_level| 2 |
< | key_buffer_size | 16777216 |
> | key_buffer_size | 268435456 |
> | key_cache_segments| 0 |
> | lc_messages_dir | |
< | long_query_time | 2.000000 |
> | long_query_time | 10.000000 |
< | max_allowed_packet| 1073741824 |
> | master_verify_checksum| OFF |
> | max_allowed_packet| 16777216|
< | max_binlog_size | 104857600|
> | max_binlog_size | 1073741824|
< | max_connect_errors| 10 |
< | max_connections | 151|
> | max_connect_errors| 1000|
> | max_connections | 150 |
< | max_long_data_size| 1073741824 |
> | max_long_data_size| 16777216|
< | max_user_connections| 0|
> | max_user_connections| 150 |
> | mrr_buffer_size | 262144|
> | myisam_block_size | 1024|
< | myisam_max_sort_file_size | 2146435072 |
< | myisam_mmap_size| 4294967295 |
< | myisam_recover_options| BACKUP |
> | myisam_max_sort_file_size | 9223372036853727232 |
> | myisam_mmap_size| 18446744073709551615|
> | myisam_recover_options| DEFAULT |
< | myisam_sort_buffer_size | 8388608|
> | myisam_sort_buffer_size | 67108864|
< | new | OFF|
> | old_mode| |
< | optimizer_switch| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on |
> | optimizer_switch| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off |
> | query_cache_strip_comments| OFF |
< | read_buffer_size| 131072 |
> | read_buffer_size| 1048576 |
< | read_rnd_buffer_size| 262144 |
> | read_rnd_buffer_size| 4194304 |
> | replicate_annotate_row_events | OFF |
> | replicate_do_db | |
> | replicate_do_table| |
> | replicate_events_marked_for_skip| replicate |
> | replicate_ignore_db | |
> | replicate_ignore_table| |
> | replicate_wild_do_table | |
> | replicate_wild_ignore_table | |
> | rowid_merge_buff_size | 8388608 |
> | secure_file_priv| |
> | skip_replication| OFF |
> | slave_sql_verify_checksum | ON|
< | slow_query_log| ON |
< | sort_buffer_size| 2097152|
> | slow_query_log| OFF |
> | sort_buffer_size| 1048576 |
< | table_open_cache| 400|
> | table_open_cache| 256 |
< | thread_stack| 196608 |
> | thread_pool_idle_timeout| 60|
> | thread_pool_max_threads | 500 |
> | thread_pool_oversubscribe | 3 |
> | thread_pool_size| 2 |
> | thread_pool_stall_limit | 500 |
> | thread_stack| 294912|
< | version | 5.5.57-0ubuntu0.14.04.1-log|
< | version_comment | (Ubuntu) |
< | version_compile_machine | i686 |
< | version_compile_os| debian-linux-gnu |
< | wait_timeout| 28800|
> | userstat| OFF |
> | version | 5.5.56-MariaDB|
> | version_comment | MariaDB Server|
> | version_compile_machine | x86_64|
> | version_compile_os| Linux |
> | wait_timeout| 25|

I realized now that this diff was probably before I added the following to the my.cnf file for MariaDB:

skip-external-locking
key_buffer              = 16M
key_buffer_size         = 256M
max_allowed_packet      = 1024M
table_open_cache        = 256
sort_buffer_size        = 1M
read_buffer_size        = 1M
read_rnd_buffer_size    = 4M
myisam_sort_buffer_size = 64M
thread_stack            = 192K
thread_cache_size       = 8
query_cache_limit       = 1M
query_cache_size        = 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency      = 8
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Webdrips
  • 265
  • 2
  • 17
  • 5
    A lock happens if *another* connection is locking a resource that your query needs. If it runs on your local server can be because the *other* connection that is blocking is running only on the remote server or maybe only active by coincidence at the same time. You'll have to find out which other query is blocking it *on the remote server*, see e.g. [here](https://serverfault.com/q/36260) (it will be relevant if you are using InnoDB or MyISAM). You can kill the other process for a one time fix and/or try to rewrite the query or reorganize which connections do what so it will not happen again. – Solarflare Sep 22 '17 at 09:58
  • I would like to see the query, but _not a screenshot_. Make it machine readable. How about a diff of the `innodb%` variables. – Rick James Sep 22 '17 at 13:39
  • Your versions are about when MariaDB and MySQL made significant diversions. I don't recall if anything substantive happened in 5.5. Upgrading to 10.x and/or 5.6/5.7 would _possibly_ reach into relevant Optimizer changes. – Rick James Sep 22 '17 at 13:43
  • Please provide `EXPLAIN SELECT ...` from the two machines. (Again, not screenshot.) – Rick James Sep 22 '17 at 13:44
  • Okay Done @Rick James. Had to leave a few variables out to allow me to post, so I removed things like log file locations etc. – Webdrips Sep 22 '17 at 18:18
  • It sounds like a 32-bit version of 5.5.57; could that be? – Rick James Sep 22 '17 at 19:03
  • 4
    Do you really have tables named like `field_data_field_additional_data_7`? – Rick James Sep 22 '17 at 19:08
  • Yes, although the field naming wasn't my call. I don't see how 32-bit vs. 64-bit or field names would matter? – Webdrips Sep 22 '17 at 19:49
  • @solarflare, the engine is InnoDB. I'm not really sure what you're getting at. I'm the only one using the remote database, and I'm only working on one web page at once (although there's PHP to load the other items on the page and the aforementioned huge query.) – Webdrips Sep 22 '17 at 20:23
  • 1
    I mean: you get a lock if: "connection 1 (e.g. php/website/...): lock something", "connection 2 (your big query): try to use the locked resource, waiting". It sounds to me that your test on the local server is: "connection 1 (your big query): runs fine, no locked resource, as php is not using local server (or did not execute the problematic *other* query)". So the test on local (as far as I understood what you did) doesn't tell you much. You'll need to check which other processes are running on the remote server while the query is locked (see the link in my first comment for that). – Solarflare Sep 23 '17 at 07:52
  • I'm loading the exact same web page, so all the queries would be identical. – Webdrips Sep 23 '17 at 08:01
  • Also, as Rick has hinted at: the database design seems weird. I would make at least sure that these tables are not e.g. views that are all using the same base table (which they should). – Solarflare Sep 23 '17 at 08:02
  • 1
    Please check the process list. It can be that just by chance that the execution order is different. I repeat: a single query/connection cannot lock itself. It needs a "partner" that locked something. So the obvious first place to look at is the other query. This might at least give you/us a first idea what issue it could be. *Then* you can start to check if the issue is because of something particular to your mariadb-server or would happen on the local server too under the same circumstances. – Solarflare Sep 23 '17 at 08:08
  • You are using XtraDB (which is default in MariaDB 5.5). Try to switch to InnoDB plugin instead to see if the problem goes away. To do it, you need to add `ignore-builtin-innodb` and `plugin-load=ha_innodb` to your cnf file and restart. – elenst Sep 29 '17 at 23:08
  • if you are making such huge queries you should know by now that the database design is very bad ! – Mehdi Karamosly Oct 03 '17 at 20:14
  • Enable mysql slow log and post the entries. Maybe a slow query having exclusive lock is preventing anything from accessing locked rows. – ad4s Oct 03 '17 at 21:53
  • Is there a reason why you're joining one and the same table multiple times but with different aliases? All these joins can be consolidated into single join. Also, is there a reason why in JOINs and WHERE you're matching clearly numeric values or booleans as strings? This creates a performance penalty due to string<>numeric conversion. But if you are actually storing numeric values in text-type columns, I strongly suggest you consult MySQL reference manual's Data Types section and use appropriate column types and compare numeric values.. – Harly Hallikas Sep 10 '18 at 01:30
  • To see what wrong post us the output from **show engine innodb status;** . Execute this query directly after the error (LOCK) occoured. – Bernd Buffen Nov 13 '18 at 21:31

0 Answers0