Package: mariadb-server-10.5
Version: 1:10.5.21-0+deb11u1
Severity: normal
Dear Maintainer,
after upgrading from 10.5.19-0+deb11u2 to 10.5.21-0+deb11u1, execution
of one particular SELECT query became extremely slow (approx 1000 to
10000 times).
The configuration (server, client) remained the same.
Before upgrade (expected):
MariaDB [test]> SELECT ...
...
4 rows in set (1.014 sec)
After upgrade (actual):
MariaDB [test]> SELECT ...
...
4 rows in set (25 min 54.964 sec)
SHOW CREATE statements of tables included in query (these are standard
WordPress tables), ANALYZE SELECT for both versions and full server
config are attached.
All tables have ~50k to 400k rows.
FYI, the same problem appears in latest version of MariaDB 10.11 (probably).
Best regards,
Pavel
-- System Information:
Debian Release: 11.8
APT prefers oldstable-updates
APT policy: (500, 'oldstable-updates'), (500, 'oldstable-security'),
(500, 'oldstable')
Architecture: amd64 (x86_64)
Kernel: Linux 5.10.0-25-amd64 (SMP w/4 CPU threads)
Locale: LANG=en_US.UTF-8, LC_CTYPE=en_US.UTF-8 (charmap=UTF-8),
LANGUAGE=en_US:en
Shell: /bin/sh linked to /usr/bin/dash
Init: systemd (via /run/systemd/system)
Versions of packages mariadb-server-10.5 depends on:
ii adduser 3.118+deb11u1
ii debconf [debconf-2.0] 1.5.77
ii galera-4 26.4.11-0+deb11u1
ii gawk 1:5.1.0-1
ii iproute2 5.10.0-4
ii libc6 2.31-13+deb11u7
ii libdbi-perl 1.643-3+b1
ii libpam0g 1.4.0-9+deb11u1
ii libssl1.1 1.1.1w-0+deb11u1
ii libstdc++6 10.2.1-6
ii lsb-base 11.1.0
ii lsof 4.93.2+dfsg-1.1
ii mariadb-client-10.5 1:10.5.21-0+deb11u1
ii mariadb-common 1:10.5.21-0+deb11u1
ii mariadb-server-core-10.5 1:10.5.21-0+deb11u1
ii passwd 1:4.8.1-1
ii perl 5.32.1-4+deb11u2
ii procps 2:3.3.17-5
ii psmisc 23.4-2
ii rsync 3.2.3-4+deb11u1
ii socat 1.7.4.1-3
ii zlib1g 1:1.2.11.dfsg-2+deb11u2
Versions of packages mariadb-server-10.5 recommends:
ii libhtml-template-perl 2.97-1.1
Versions of packages mariadb-server-10.5 suggests:
pn mailx <none>
pn mariadb-test <none>
pn netcat-openbsd <none>
-- debconf information:
mariadb-server-10.5/old_data_directory_saved:
mariadb-server-10.5/nis_warning:
mariadb-server-10.5/postrm_remove_databases: false
alter_algorithm = DEFAULT
analyze_sample_percentage = 100.000000
aria_block_size = 8192
aria_checkpoint_interval = 30
aria_checkpoint_log_activity = 1048576
aria_encrypt_tables = OFF
aria_force_start_after_recovery_failures = 0
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_dir_path = /var/lib/mysql/
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_pagecache_file_hash_size = 512
aria_recover_options = BACKUP,QUICK
aria_repair_threads = 1
aria_sort_buffer_size = 268434432
aria_stats_method = nulls_unequal
aria_sync_log_dir = NEWFILE
aria_used_for_temp_tables = ON
auto_increment_increment = 1
auto_increment_offset = 1
autocommit = ON
automatic_sp_privileges = ON
back_log = 150
basedir = /usr
big_tables = OFF
bind_address = ::
binlog_annotate_row_events = ON
binlog_cache_size = 32768
binlog_checksum = CRC32
binlog_commit_wait_count = 0
binlog_commit_wait_usec = 100000
binlog_direct_non_transactional_updates = OFF
binlog_file_cache_size = 16384
binlog_format = MIXED
binlog_optimize_thread_scheduling = ON
binlog_row_image = FULL
binlog_row_metadata = NO_LOG
binlog_stmt_cache_size = 32768
bulk_insert_buffer_size = 8388608
character_set_client = utf8mb4
character_set_connection = utf8mb4
character_set_database = utf8mb4
character_set_filesystem = binary
character_set_results = utf8mb4
character_set_server = utf8mb4
character_set_system = utf8
character_sets_dir = /usr/share/mysql/charsets/
check_constraint_checks = ON
collation_connection = utf8mb4_general_ci
collation_database = utf8mb4_general_ci
collation_server = utf8mb4_general_ci
column_compression_threshold = 100
column_compression_zlib_level = 6
column_compression_zlib_strategy = DEFAULT_STRATEGY
column_compression_zlib_wrap = OFF
completion_type = NO_CHAIN
concurrent_insert = AUTO
connect_timeout = 10
core_file = OFF
datadir = /var/lib/mysql/
date_format = %Y-%m-%d
datetime_format = %Y-%m-%d = %H:%i:%s
deadlock_search_depth_long = 15
deadlock_search_depth_short = 4
deadlock_timeout_long = 50000000
deadlock_timeout_short = 10000
debug_no_thread_alarm = OFF
default_password_lifetime = 0
default_regex_flags =
default_storage_engine = InnoDB
default_tmp_storage_engine =
default_week_format = 0
delay_key_write = ON
delayed_insert_limit = 100
delayed_insert_timeout = 300
delayed_queue_size = 1000
disconnect_on_expired_password = OFF
div_precision_increment = 4
encrypt_binlog = OFF
encrypt_tmp_disk_tables = OFF
encrypt_tmp_files = OFF
enforce_storage_engine =
eq_range_index_dive_limit = 200
event_scheduler = OFF
expensive_subquery_limit = 100
expire_logs_days = 10
explicit_defaults_for_timestamp = OFF
extra_max_connections = 1
extra_port = 0
flush = OFF
flush_time = 0
foreign_key_checks = ON
ft_boolean_syntax = + = -><()~*:""&
ft_max_word_len = 84
ft_min_word_len = 4
ft_query_expansion_limit = 20
ft_stopword_file = (built-in)
general_log = OFF
general_log_file = myserver.log
group_concat_max_len = 1048576
gtid_binlog_pos =
gtid_binlog_state =
gtid_cleanup_batch_size = 64
gtid_current_pos =
gtid_domain_id = 0
gtid_ignore_duplicates = OFF
gtid_pos_auto_engines =
gtid_slave_pos =
gtid_strict_mode = OFF
have_compress = YES
have_crypt = YES
have_dynamic_loading = YES
have_geometry = YES
have_openssl = YES
have_profiling = YES
have_query_cache = YES
have_rtree_keys = YES
have_ssl = DISABLED
have_symlink = YES
histogram_size = 254
histogram_type = DOUBLE_PREC_HB
host_cache_size = 628
hostname = myserver
idle_readonly_transaction_timeout = 0
idle_transaction_timeout = 0
idle_write_transaction_timeout = 0
ignore_builtin_innodb = OFF
ignore_db_dirs = lost+found
in_predicate_conversion_threshold = 1000
init_connect =
init_file =
init_slave =
innodb_adaptive_flushing = ON
innodb_adaptive_flushing_lwm = 10.000000
innodb_adaptive_hash_index = OFF
innodb_adaptive_hash_index_parts = 8
innodb_adaptive_max_sleep_delay = 0
innodb_autoextend_increment = 64
innodb_autoinc_lock_mode = 1
innodb_background_scrub_data_check_interval = 0
innodb_background_scrub_data_compressed = OFF
innodb_background_scrub_data_interval = 0
innodb_background_scrub_data_uncompressed = OFF
innodb_buf_dump_status_frequency = 0
innodb_buffer_pool_chunk_size = 134217728
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_dump_now = OFF
innodb_buffer_pool_dump_pct = 25
innodb_buffer_pool_filename = ib_buffer_pool
innodb_buffer_pool_instances = 1
innodb_buffer_pool_load_abort = OFF
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_load_now = OFF
innodb_buffer_pool_size = 8589934592
innodb_change_buffer_max_size = 25
innodb_change_buffering = none
innodb_checksum_algorithm = full_crc32
innodb_cmp_per_index_enabled = OFF
innodb_commit_concurrency = 0
innodb_compression_algorithm = zlib
innodb_compression_default = OFF
innodb_compression_failure_threshold_pct = 5
innodb_compression_level = 6
innodb_compression_pad_pct_max = 50
innodb_concurrency_tickets = 0
innodb_data_file_path = ibdata1:12M:autoextend
innodb_data_home_dir =
innodb_deadlock_detect = ON
innodb_default_encryption_key_id = 1
innodb_default_row_format = dynamic
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_doublewrite = ON
innodb_encrypt_log = OFF
innodb_encrypt_tables = OFF
innodb_encrypt_temporary_tables = OFF
innodb_encryption_rotate_key_age = 1
innodb_encryption_rotation_iops = 100
innodb_encryption_threads = 0
innodb_fast_shutdown = 1
innodb_fatal_semaphore_wait_threshold = 600
innodb_file_format =
innodb_file_per_table = ON
innodb_fill_factor = 100
innodb_flush_log_at_timeout = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = fsync
innodb_flush_neighbors = 1
innodb_flush_sync = ON
innodb_flushing_avg_loops = 30
innodb_force_load_corrupted = OFF
innodb_force_primary_key = OFF
innodb_force_recovery = 0
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_immediate_scrub_data_uncompressed = OFF
innodb_instant_alter_column_allowed = add_drop_reorder
innodb_io_capacity = 200
innodb_io_capacity_max = 2000
innodb_large_prefix =
innodb_lock_schedule_algorithm = fcfs
innodb_lock_wait_timeout = 50
innodb_log_buffer_size = 33554432
innodb_log_checksums = ON
innodb_log_compressed_pages = ON
innodb_log_file_size = 67108864
innodb_log_files_in_group = 1
innodb_log_group_home_dir = ./
innodb_log_optimize_ddl = OFF
innodb_log_write_ahead_size = 8192
innodb_lru_flush_size = 32
innodb_lru_scan_depth = 1536
innodb_max_dirty_pages_pct = 90.000000
innodb_max_dirty_pages_pct_lwm = 0.000000
innodb_max_purge_lag = 0
innodb_max_purge_lag_delay = 0
innodb_max_purge_lag_wait = 4294967295
innodb_max_undo_log_size = 10485760
innodb_monitor_disable =
innodb_monitor_enable =
innodb_monitor_reset =
innodb_monitor_reset_all =
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_cleaners = 1
innodb_page_size = 16384
innodb_prefix_index_cluster_optimization = OFF
innodb_print_all_deadlocks = OFF
innodb_purge_batch_size = 300
innodb_purge_rseg_truncate_frequency = 128
innodb_purge_threads = 4
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_scrub_log = OFF
innodb_scrub_log_speed = 256
innodb_sort_buffer_size = 1048576
innodb_spin_wait_delay = 4
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_traditional = ON
innodb_stats_transient_sample_pages = 8
innodb_status_output = OFF
innodb_status_output_locks = OFF
innodb_strict_mode = ON
innodb_sync_array_size = 1
innodb_sync_spin_loops = 30
innodb_table_locks = ON
innodb_temp_data_file_path = ibtmp1:12M:autoextend
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 0
innodb_tmpdir =
innodb_undo_directory = ./
innodb_undo_log_truncate = OFF
innodb_undo_logs = 128
innodb_undo_tablespaces = 0
innodb_use_atomic_writes = ON
innodb_use_native_aio = ON
innodb_version = 10.5.21
innodb_write_io_threads = 4
interactive_timeout = 28800
join_buffer_size = 262144
join_buffer_space_limit = 2097152
join_cache_level = 2
keep_files_on_create = OFF
key_buffer_size = 402653184
key_cache_age_threshold = 300
key_cache_block_size = 1024
key_cache_division_limit = 100
key_cache_file_hash_size = 512
key_cache_segments = 0
large_files_support = ON
large_page_size = 0
large_pages = OFF
lc_messages = en_US
lc_messages_dir =
lc_time_names = en_US
license = GPL
local_infile = ON
lock_wait_timeout = 86400
locked_in_memory = OFF
log_bin = OFF
log_bin_basename =
log_bin_compress = OFF
log_bin_compress_min_len = 256
log_bin_index =
log_bin_trust_function_creators = OFF
log_disabled_statements = sp
log_error =
log_output = FILE
log_queries_not_using_indexes = OFF
log_slave_updates = OFF
log_slow_admin_statements = ON
log_slow_disabled_statements = sp
log_slow_filter =
admin,filesort,filesort_on_disk,filesort_priority_queue,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
log_slow_rate_limit = 1
log_slow_slave_statements = ON
log_slow_verbosity =
log_tc_size = 24576
log_warnings = 2
long_query_time = 15.000000
low_priority_updates = OFF
lower_case_file_system = OFF
lower_case_table_names = 0
master_verify_checksum = OFF
max_allowed_packet = 16777216
max_binlog_cache_size = 18446744073709547520
max_binlog_size = 104857600
max_binlog_stmt_cache_size = 18446744073709547520
max_connect_errors = 100
max_connections = 501
max_delayed_threads = 20
max_digest_length = 1024
max_error_count = 64
max_heap_table_size = 16777216
max_insert_delayed_threads = 20
max_join_size = 18446744073709551615
max_length_for_sort_data = 1024
max_password_errors = 4294967295
max_prepared_stmt_count = 16382
max_recursive_iterations = 4294967295
max_relay_log_size = 104857600
max_rowid_filter_size = 131072
max_seeks_for_key = 4294967295
max_session_mem_used = 9223372036854775807
max_sort_length = 1024
max_sp_recursion_depth = 0
max_statement_time = 0.000000
max_tmp_tables = 32
max_user_connections = 50
max_write_lock_count = 4294967295
metadata_locks_cache_size = 1024
metadata_locks_hash_instances = 8
min_examined_row_limit = 0
mrr_buffer_size = 262144
myisam_block_size = 1024
myisam_data_pointer_size = 6
myisam_max_sort_file_size = 9223372036853727232
myisam_mmap_size = 18446744073709551615
myisam_recover_options = BACKUP
myisam_repair_threads = 1
myisam_sort_buffer_size = 134217728
myisam_stats_method = NULLS_UNEQUAL
myisam_use_mmap = OFF
mysql56_temporal_format = ON
net_buffer_length = 16384
net_read_timeout = 30
net_retry_count = 10
net_write_timeout = 120
old = OFF
old_alter_table = DEFAULT
old_mode =
old_passwords = OFF
open_files_limit = 32596
optimizer_max_sel_arg_weight = 32000
optimizer_prune_level = 1
optimizer_search_depth = 62
optimizer_selectivity_sampling_limit = 100
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=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off
optimizer_trace = enabled=off
optimizer_trace_max_mem_size = 1048576
optimizer_use_condition_selectivity = 4
performance_schema = OFF
performance_schema_accounts_size = -1
performance_schema_digests_size = -1
performance_schema_events_stages_history_long_size = -1
performance_schema_events_stages_history_size = -1
performance_schema_events_statements_history_long_size = -1
performance_schema_events_statements_history_size = -1
performance_schema_events_transactions_history_long_size = -1
performance_schema_events_transactions_history_size = -1
performance_schema_events_waits_history_long_size = -1
performance_schema_events_waits_history_size = -1
performance_schema_hosts_size = -1
performance_schema_max_cond_classes = 90
performance_schema_max_cond_instances = -1
performance_schema_max_digest_length = 1024
performance_schema_max_file_classes = 80
performance_schema_max_file_handles = 32768
performance_schema_max_file_instances = -1
performance_schema_max_index_stat = -1
performance_schema_max_memory_classes = 320
performance_schema_max_metadata_locks = -1
performance_schema_max_mutex_classes = 210
performance_schema_max_mutex_instances = -1
performance_schema_max_prepared_statements_instances = -1
performance_schema_max_program_instances = -1
performance_schema_max_rwlock_classes = 50
performance_schema_max_rwlock_instances = -1
performance_schema_max_socket_classes = 10
performance_schema_max_socket_instances = -1
performance_schema_max_sql_text_length = 1024
performance_schema_max_stage_classes = 160
performance_schema_max_statement_classes = 222
performance_schema_max_statement_stack = 10
performance_schema_max_table_handles = -1
performance_schema_max_table_instances = -1
performance_schema_max_table_lock_stat = -1
performance_schema_max_thread_classes = 50
performance_schema_max_thread_instances = -1
performance_schema_session_connect_attrs_size = -1
performance_schema_setup_actors_size = -1
performance_schema_setup_objects_size = -1
performance_schema_users_size = -1
pid_file = /var/run/mysqld/mysqld.pid
plugin_dir = /usr/lib/mysql/plugin/
plugin_maturity = gamma
port = 3306
preload_buffer_size = 32768
profiling = OFF
profiling_history_size = 15
progress_report_time = 5
protocol_version = 10
proxy_protocol_networks =
query_alloc_block_size = 16384
query_cache_limit = 1048576
query_cache_min_res_unit = 4096
query_cache_size = 1073741824
query_cache_strip_comments = OFF
query_cache_type = ON
query_cache_wlock_invalidate = OFF
query_prealloc_size = 24576
range_alloc_block_size = 4096
read_binlog_speed_limit = 0
read_buffer_size = 1048576
read_only = OFF
read_rnd_buffer_size = 262144
relay_log =
relay_log_basename =
relay_log_index =
relay_log_info_file = relay-log.info
relay_log_purge = ON
relay_log_recovery = OFF
relay_log_space_limit = 0
replicate_annotate_row_events = ON
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 =
report_host =
report_password =
report_port = 3306
report_user =
require_secure_transport = OFF
rowid_merge_buff_size = 8388608
rpl_semi_sync_master_enabled = OFF
rpl_semi_sync_master_timeout = 10000
rpl_semi_sync_master_trace_level = 32
rpl_semi_sync_master_wait_no_slave = ON
rpl_semi_sync_master_wait_point = AFTER_COMMIT
rpl_semi_sync_slave_delay_master = OFF
rpl_semi_sync_slave_enabled = OFF
rpl_semi_sync_slave_kill_conn_timeout = 5
rpl_semi_sync_slave_trace_level = 32
secure_auth = ON
secure_file_priv =
secure_timestamp = NO
server_id = 1
session_track_schema = ON
session_track_state_change = OFF
session_track_system_variables =
autocommit,character_set_client,character_set_connection,character_set_results,time_zone
session_track_transaction_info = OFF
skip_external_locking = ON
skip_name_resolve = OFF
skip_networking = OFF
skip_show_database = OFF
slave_compressed_protocol = OFF
slave_ddl_exec_mode = IDEMPOTENT
slave_domain_parallel_threads = 0
slave_exec_mode = STRICT
slave_load_tmpdir = /tmp
slave_max_allowed_packet = 1073741824
slave_net_timeout = 60
slave_parallel_max_queued = 131072
slave_parallel_mode = optimistic
slave_parallel_threads = 0
slave_parallel_workers = 0
slave_run_triggers_for_rbr = NO
slave_skip_errors = OFF
slave_sql_verify_checksum = ON
slave_transaction_retries = 10
slave_transaction_retry_errors = 1158,1159,1160,1161,1205,1213,1429,2013,12701
slave_transaction_retry_interval = 0
slave_type_conversions =
slow_launch_time = 2
slow_query_log = ON
slow_query_log_file = /var/log/mariadb/mariadb-slow.log
socket = /var/run/mysqld/mysqld.sock
sort_buffer_size = 2097152
sql_auto_is_null = OFF
sql_big_selects = ON
sql_buffer_result = OFF
sql_if_exists = OFF
sql_log_bin = ON
sql_log_off = OFF
sql_mode =
STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql_notes = ON
sql_quote_show_create = ON
sql_safe_updates = OFF
sql_select_limit = 18446744073709551615
sql_slave_skip_counter = 0
sql_warnings = OFF
ssl_ca =
ssl_capath =
ssl_cert =
ssl_cipher =
ssl_crl =
ssl_crlpath =
ssl_key =
standard_compliant_cte = ON
storage_engine = InnoDB
stored_program_cache = 256
strict_password_validation = ON
sync_binlog = 0
sync_frm = ON
sync_master_info = 10000
sync_relay_log = 10000
sync_relay_log_info = 10000
system_time_zone = CEST
system_versioning_alter_history = ERROR
system_versioning_asof = DEFAULT
table_definition_cache = 400
table_open_cache = 2000
table_open_cache_instances = 8
tcp_keepalive_interval = 0
tcp_keepalive_probes = 0
tcp_keepalive_time = 0
tcp_nodelay = ON
thread_cache_size = 8
thread_handling = one-thread-per-connection
thread_pool_dedicated_listener = OFF
thread_pool_exact_stats = OFF
thread_pool_idle_timeout = 60
thread_pool_max_threads = 65536
thread_pool_oversubscribe = 3
thread_pool_prio_kickup_timer = 1000
thread_pool_priority = auto
thread_pool_size = 64
thread_pool_stall_limit = 500
thread_stack = 196608
time_format = %H:%i:%s
time_zone = SYSTEM
tls_version = TLSv1.1,TLSv1.2,TLSv1.3
tmp_disk_table_size = 18446744073709551615
tmp_memory_table_size = 16777216
tmp_table_size = 16777216
tmpdir = /tmp
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
tx_isolation = REPEATABLE-READ
tx_read_only = OFF
unique_checks = ON
updatable_views_with_limit = YES
use_stat_tables = PREFERABLY_FOR_QUERIES
userstat = ON
version = 10.5.21-MariaDB-0+deb11u1-log
version_comment = Debian = 11
version_compile_machine = x86_64
version_compile_os = debian-linux-gnu
version_malloc_library = system
version_source_revision = bed70468ea08c2820647f5e3ac006a9ff88144ac
version_ssl_library = OpenSSL = 1.1.1w = 11 = Sep = 2023
wait_timeout = 28800
wsrep_osu_method = TOI
wsrep_sr_store = table
wsrep_auto_increment_control = ON
wsrep_causal_reads = OFF
wsrep_certification_rules = strict
wsrep_certify_nonpk = ON
wsrep_cluster_address =
wsrep_cluster_name = my_wsrep_cluster
wsrep_convert_lock_to_trx = OFF
wsrep_data_home_dir = /var/lib/mysql/
wsrep_dbug_option =
wsrep_debug = NONE
wsrep_desync = OFF
wsrep_dirty_reads = OFF
wsrep_drupal_282555_workaround = OFF
wsrep_forced_binlog_format = NONE
wsrep_gtid_domain_id = 0
wsrep_gtid_mode = OFF
wsrep_ignore_apply_errors = 7
wsrep_load_data_splitting = OFF
wsrep_log_conflicts = OFF
wsrep_max_ws_rows = 0
wsrep_max_ws_size = 2147483647
wsrep_mysql_replication_bundle = 0
wsrep_node_address =
wsrep_node_incoming_address = AUTO
wsrep_node_name = myserver
wsrep_notify_cmd =
wsrep_on = OFF
wsrep_patch_version = wsrep_26.22
wsrep_provider = none
wsrep_provider_options =
wsrep_recover = OFF
wsrep_reject_queries = NONE
wsrep_replicate_myisam = OFF
wsrep_restart_slave = OFF
wsrep_retry_autocommit = 1
wsrep_slave_fk_checks = ON
wsrep_slave_uk_checks = OFF
wsrep_slave_threads = 1
wsrep_sst_auth =
wsrep_sst_donor =
wsrep_sst_donor_rejects_queries = OFF
wsrep_sst_method = rsync
wsrep_sst_receive_address = AUTO
wsrep_start_position = 00000000-0000-0000-0000-000000000000:-1
wsrep_strict_ddl = OFF
wsrep_sync_wait = 0
wsrep_trx_fragment_size = 0
wsrep_trx_fragment_unit = bytes
Server version: 10.5.19-MariaDB-0+deb11u2-log Debian 11
MariaDB [test]> ANALYZE SELECT SQL_CALC_FOUND_ROWS be_posts.ID FROM be_posts
LEFT JOIN be_term_relationships ON (be_posts.ID =
be_term_relationships.object_id) LEFT JOIN be_postmeta ON ( be_posts.ID =
be_postmeta.post_id AND be_postmeta.meta_key = '_menu_order' ) LEFT JOIN
be_postmeta AS mt1 ON ( be_posts.ID = mt1.post_id ) WHERE 1=1 AND
(be_term_relationships.term_taxonomy_id IN (10)) AND (be_postmeta.post_id IS
NULL OR mt1.meta_key = '_menu_order') AND ((be_posts.post_type = 'post' AND
(be_posts.post_status = 'publish'))) GROUP BY be_posts.ID ORDER BY
be_posts.menu_order DESC, be_posts.post_date DESC LIMIT 0, 30;
+------+-------------+-----------------------+--------+--------------------------+------------------+---------+----------------------------------+-------+----------+----------+------------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows |
r_rows | filtered | r_filtered | Extra
|
+------+-------------+-----------------------+--------+--------------------------+------------------+---------+----------------------------------+-------+----------+----------+------------+---------------------------------------------------------------------+
| 1 | SIMPLE | be_posts | ref |
PRIMARY,type_status_date | type_status_date | 164 | const,const
| 28670 | 88946.00 | 100.00 | 100.00 | Using index condition;
Using where; Using temporary; Using filesort |
| 1 | SIMPLE | be_postmeta | ref | post_id,meta_key
| meta_key | 767 | const | 1 | 0.00
| 100.00 | 100.00 | Using where
|
| 1 | SIMPLE | be_term_relationships | eq_ref |
PRIMARY,term_taxonomy_id | PRIMARY | 16 |
bloodyelbowcom.be_posts.ID,const | 1 | 0.39 | 100.00 | 100.00 |
Using index |
| 1 | SIMPLE | mt1 | ref | post_id
| post_id | 8 | bloodyelbowcom.be_posts.ID | 2 | 4.68
| 100.00 | 100.00 | Using where
|
+------+-------------+-----------------------+--------+--------------------------+------------------+---------+----------------------------------+-------+----------+----------+------------+---------------------------------------------------------------------+
4 rows in set (1.071 sec)
=================================================================================================================
Server version: 10.5.21-MariaDB-0+deb11u1-log Debian 11
MariaDB [test]> ANALYZE SELECT SQL_CALC_FOUND_ROWS be_posts.ID FROM be_posts
LEFT JOIN be_term_relationships ON (be_posts.ID =
be_term_relationships.object_id) LEFT JOIN be_postmeta ON ( be_posts.ID =
be_postmeta.post_id AND be_postmeta.meta_key = '_menu_order' ) LEFT JOIN
be_postmeta AS mt1 ON ( be_posts.ID = mt1.post_id ) WHERE 1=1 AND
(be_term_relationships.term_taxonomy_id IN (10)) AND (be_postmeta.post_id IS
NULL OR mt1.meta_key = '_menu_order') AND ((be_posts.post_type = 'post' AND
(be_posts.post_status = 'publish'))) GROUP BY be_posts.ID ORDER BY
be_posts.menu_order DESC, be_posts.post_date DESC LIMIT 0, 30;
+------+-------------+-----------------------+------------+--------------------------+------------------+---------+--------------------------------+--------+-----------+----------+------------+--------------------------------------------------------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows |
r_rows | filtered | r_filtered | Extra
|
+------+-------------+-----------------------+------------+--------------------------+------------------+---------+--------------------------------+--------+-----------+----------+------------+--------------------------------------------------------------+
| 1 | SIMPLE | be_posts | ref |
PRIMARY,type_status_date | type_status_date | 164 | const,const
| 35528 | 88946.00 | 100.00 | 100.00 | Using index condition;
Using temporary; Using filesort |
| 1 | SIMPLE | be_postmeta | ref|filter | post_id,meta_key
| post_id|meta_key | 8|767 | arsenal-maniacom02.be_posts.ID | 2 (0%) |
0.00 (0%) | 0.00 | 100.00 | Using where; Using rowid filter
|
| 1 | SIMPLE | mt1 | ref | post_id
| post_id | 8 | arsenal-maniacom02.be_posts.ID | 2 |
4.58 | 100.00 | 100.00 | Using where
|
| 1 | SIMPLE | be_term_relationships | range |
PRIMARY,term_taxonomy_id | term_taxonomy_id | 8 | NULL
| 59096 | 34940.00 | 100.00 | 0.00 | Using where; Using index;
Using join buffer (flat, BNL join) |
+------+-------------+-----------------------+------------+--------------------------+------------------+---------+--------------------------------+--------+-----------+----------+------------+--------------------------------------------------------------+
4 rows in set (25 min 54.964 sec)
CREATE TABLE `be_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_author` bigint(20) unsigned NOT NULL DEFAULT 0,
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content` longtext NOT NULL,
`post_title` text NOT NULL,
`post_excerpt` text NOT NULL,
`post_status` varchar(20) NOT NULL DEFAULT 'publish',
`comment_status` varchar(20) NOT NULL DEFAULT 'open',
`ping_status` varchar(20) NOT NULL DEFAULT 'open',
`post_password` varchar(255) NOT NULL DEFAULT '',
`post_name` varchar(200) NOT NULL DEFAULT '',
`to_ping` text NOT NULL,
`pinged` text NOT NULL,
`post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content_filtered` longtext NOT NULL,
`post_parent` bigint(20) unsigned NOT NULL DEFAULT 0,
`guid` varchar(255) NOT NULL DEFAULT '',
`menu_order` int(11) NOT NULL DEFAULT 0,
`post_type` varchar(20) NOT NULL DEFAULT 'post',
`post_mime_type` varchar(100) NOT NULL DEFAULT '',
`comment_count` bigint(20) NOT NULL DEFAULT 0,
PRIMARY KEY (`ID`),
KEY `post_name` (`post_name`(191)),
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
KEY `post_parent` (`post_parent`),
KEY `post_author` (`post_author`)
) ENGINE=InnoDB AUTO_INCREMENT=109509 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci
CREATE TABLE `be_postmeta` (
`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_id` bigint(20) unsigned NOT NULL DEFAULT 0,
`meta_key` varchar(255) DEFAULT NULL,
`meta_value` longtext DEFAULT NULL,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`),
KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=533070 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci
CREATE TABLE `be_term_relationships` (
`object_id` bigint(20) unsigned NOT NULL DEFAULT 0,
`term_taxonomy_id` bigint(20) unsigned NOT NULL DEFAULT 0,
`term_order` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`object_id`,`term_taxonomy_id`),
KEY `term_taxonomy_id` (`term_taxonomy_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
