mysqlinnodb
Improving InnoDB settings - causes unexplainable locks
We have InnoDB on dedicated server and from time to time a simple join query (normally very fast) causes an infinite wait and dies after 30 seconds.
The query looks like "SELECT * FROM orders LEFT JOIN orders_products USING(code)"
This is EXPLAIN:
I am convinced InnoDB is f this up and I don't know why, but it's when more people access the script at the same time. I am NOT doing any updates or inserts in that script, so it makes no sense.
It could be that somewhere is a script that does a concurrent write and maybe then this main script fails? I have no clue. I never had issue on MyIsam and I don't use transactions.
Can we change settings somehow to fix this?
We have a big server, but neither the admin or I know how to set this properly.
Suggestions are welcome.
This is current settings:
Variable_name Value innodb_adaptive_flushing ON innodb_adaptive_flushing_lwm 10.000000 innodb_adaptive_hash_index ON innodb_adaptive_hash_index_partitions 1 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_background_scrub_data_check_interval 3600 innodb_background_scrub_data_compressed OFF innodb_background_scrub_data_interval 604800 innodb_background_scrub_data_uncompressed OFF innodb_buf_dump_status_frequency 0 innodb_buffer_pool_dump_at_shutdown OFF innodb_buffer_pool_dump_now OFF innodb_buffer_pool_dump_pct 100 innodb_buffer_pool_filename ib_buffer_pool innodb_buffer_pool_instances 2 innodb_buffer_pool_load_abort OFF innodb_buffer_pool_load_at_startup OFF innodb_buffer_pool_load_now OFF innodb_buffer_pool_populate OFF innodb_buffer_pool_size 2147483648 innodb_change_buffer_max_size 25 innodb_change_buffering all innodb_checksum_algorithm CRC32 innodb_checksums ON innodb_cleaner_lsn_age_factor HIGH_CHECKPOINT innodb_cmp_per_index_enabled OFF innodb_commit_concurrency 0 innodb_compression_algorithm zlib innodb_compression_failure_threshold_pct 5 innodb_compression_level 6 innodb_compression_pad_pct_max 50 innodb_concurrency_tickets 5000 innodb_corrupt_table_action assert innodb_data_file_path ibdata1:12M:autoextend innodb_data_home_dir innodb_default_encryption_key_id 1 innodb_default_row_format compact innodb_defragment OFF innodb_defragment_fill_factor 0.900000 innodb_defragment_fill_factor_n_recs 20 innodb_defragment_frequency 40 innodb_defragment_n_pages 7 innodb_defragment_stats_accuracy 0 innodb_disable_sort_file_cache OFF innodb_disallow_writes OFF innodb_doublewrite ON innodb_empty_free_list_algorithm BACKOFF innodb_encrypt_log OFF innodb_encrypt_tables OFF innodb_encryption_rotate_key_age 1 innodb_encryption_rotation_iops 100 innodb_encryption_threads 0 innodb_fake_changes OFF innodb_fast_shutdown 1 innodb_fatal_semaphore_wait_threshold 600 innodb_file_format Barracuda 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 2 innodb_flush_method O_DIRECT innodb_flush_neighbors 1 innodb_flushing_avg_loops 30 innodb_force_load_corrupted OFF innodb_force_primary_key OFF innodb_force_recovery 0 innodb_foreground_preflush EXPONENTIAL_BACKOFF innodb_ft_aux_table 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_server_stopword_table innodb_ft_sort_pll_degree 2 innodb_ft_total_cache_size 640000000 innodb_ft_user_stopword_table innodb_idle_flush_pct 100 innodb_immediate_scrub_data_uncompressed OFF innodb_instrument_semaphores OFF innodb_io_capacity 200 innodb_io_capacity_max 2000 innodb_kill_idle_transaction 0 innodb_large_prefix ON innodb_lock_schedule_algorithm fcfs innodb_lock_wait_timeout 50 innodb_locking_fake_changes ON innodb_locks_unsafe_for_binlog OFF innodb_log_arch_dir ./ Variable_name Value innodb_log_arch_expire_sec 0 innodb_log_archive OFF innodb_log_block_size 512 innodb_log_buffer_size 16777216 innodb_log_checksum_algorithm INNODB innodb_log_compressed_pages ON innodb_log_file_size 268435456 innodb_log_files_in_group 2 innodb_log_group_home_dir ./ innodb_lru_scan_depth 1024 innodb_max_bitmap_file_size 104857600 innodb_max_changed_pages 1000000 innodb_max_dirty_pages_pct 75.000000 innodb_max_dirty_pages_pct_lwm 0.001000 innodb_max_purge_lag 0 innodb_max_purge_lag_delay 0 innodb_mirrored_log_groups 1 innodb_monitor_disable innodb_monitor_enable innodb_monitor_reset innodb_monitor_reset_all innodb_mtflush_threads 8 innodb_old_blocks_pct 37 innodb_old_blocks_time 1000 innodb_online_alter_log_max_size 134217728 innodb_open_files 5000 innodb_optimize_fulltext_only OFF innodb_page_size 16384 innodb_prefix_index_cluster_optimization OFF innodb_print_all_deadlocks OFF innodb_print_lock_wait_timeout_info 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_sched_priority_cleaner 19 innodb_scrub_log OFF innodb_scrub_log_speed 256 innodb_show_locks_held 10 innodb_show_verbose_locks 0 innodb_simulate_comp_failures 0 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_modified_counter 0 innodb_stats_on_metadata OFF innodb_stats_persistent ON innodb_stats_persistent_sample_pages 20 innodb_stats_sample_pages 8 innodb_stats_traditional ON 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_tmpdir innodb_track_changed_pages OFF innodb_undo_directory . innodb_undo_logs 128 innodb_undo_tablespaces 0 innodb_use_atomic_writes OFF innodb_use_fallocate OFF innodb_use_global_flush_log_at_trx_commit ON innodb_use_mtflush OFF innodb_use_native_aio ON innodb_use_stacktrace OFF innodb_use_sys_malloc ON innodb_use_trim OFF innodb_version 5.6.46-86.2 innodb_write_io_threads 4
Solution
So, I just split the query to 2 queries (many rows/conditions) and it works now.
Probably was difficult query (despite the fact it was quick) and when having more hits, it stalled. Idk.
- is there any way to check with cumulative way to get month based on the amount fields
- How to compare using bcrypt in a db query using node.js and express
- SQL query to check if a name begins and ends with a vowel
- MySQL JSON_OBJECT() datetime format
- Why is MySQL Sorting by mp.id in This Query Execution Plan?
- PHPExcel download not working properly
- brew start and brew restart wont start service
- MySqlCommand().ExecuteReader().GetString() does not work
- how to sum rows together but also leave the original rows in the results?
- Postgres vs MySQL index locking: Should I expect MySQL to be more tolerant to parallel write-heavy applications?
- Java - Having a huge ArrayList (1 million +), how to create a String of it in a acceptable amount of time?
- SQL filter for same values in different columns
- ERROR: SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it
- MySQL - Does the READ UNCOMMITTED isolation level use locks?
- convert mysql timestamp into actual date and time?
- Optimize order by rand()
- Change max_heap_table_size value?
- wordpress - Big post IDs, is it Okay in the future?
- Incorrect string value error for unconventional characters
- Cannot open include file: 'mysql.h': No such file or directory
- How do I connect Biostar V1.8 Suprema to MySQL?
- How to convert a string to date in MySQL?
- Duplicate records in MySQL. EXISTS check for the same data not working properly?
- How to define a custom ORDER BY order in mySQL
- On duplicate key ignore?
- How to fix MySQL providing duplicates that do not exist?
- What's the best MySQL alternative to dual?
- mysql SELECT issue with HAVING and/or WHERE
- How can I connect an externally hosted MySQL database to BigQuery?
- DBeaver error resolving maven dependencies