maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #02090
Spider - query end
Hi list,
I am using Spider Storage Engine for sharding data and remote access to
some innodb tables on 3 different MariaDB 10.0.14 instances.
It performs quite well in production except that sometimes, some queries
(a minority of them fortunately) do not respond and stay indefinitely in
"query end" state in processlist.
What can make a query stay indefinitely in "query end" state?
Maybe a bug in Spider Storage Engine?
Any advice is welcome!
Some more details:
db01 =# show full processlist\G
*************************** 4. row ***************************
Id: 1699242
User: np
Host: 10.0.0.1:24220
db: db01
Command: Query
Time: 155777
State: query end
Info: SELECT s.id,s.title,sum(s.cpt) AS sumcpt
FROM s
INNER JOIN u ON u.id = s.u_id
INNER JOIN d ON s.d_id = d.id
WHERE u.str = 'abc123456' AND d.str = 'def456789'
GROUP BY s.title
ORDER BY sumcpt DESC
LIMIT 0,10
Progress: 0.000
About tables used in query:
s is a spider table pointing on 24 innodb shards over 3 remote
servers (PARTITION BY LIST (mod(id,24)))
u is a spider table referencing one remote innodb table (not sharded,
millions rows)
d is a spider table referencing one remote innodb table (not sharded,
<100 rows)
Spider config:
(none) =# show variables like 'spider%';
+---------------------------------------+--------+
| Variable_name | Value |
+---------------------------------------+--------+
| spider_auto_increment_mode | -1 |
| spider_bgs_first_read | -1 |
| spider_bgs_mode | -1 |
| spider_bgs_second_read | -1 |
| spider_bka_engine | |
| spider_bka_mode | -1 |
| spider_block_size | 16384 |
| spider_bulk_size | -1 |
| spider_bulk_update_mode | -1 |
| spider_bulk_update_size | -1 |
| spider_casual_read | 1 |
| spider_conn_recycle_mode | 1 |
| spider_conn_recycle_strict | 0 |
| spider_connect_mutex | OFF |
| spider_connect_retry_count | 1000 |
| spider_connect_retry_interval | 1000 |
| spider_connect_timeout | 10 |
| spider_crd_bg_mode | 1 |
| spider_crd_interval | 4 |
| spider_crd_mode | 1 |
| spider_crd_sync | 0 |
| spider_crd_type | -1 |
| spider_crd_weight | -1 |
| spider_delete_all_rows_type | -1 |
| spider_direct_dup_insert | 1 |
| spider_direct_order_limit | -1 |
| spider_dry_access | OFF |
| spider_error_read_mode | -1 |
| spider_error_write_mode | -1 |
| spider_first_read | -1 |
| spider_force_commit | 1 |
| spider_general_log | OFF |
| spider_init_sql_alloc_size | -1 |
| spider_internal_limit | -1 |
| spider_internal_offset | -1 |
| spider_internal_optimize | -1 |
| spider_internal_optimize_local | -1 |
| spider_internal_sql_log_off | ON |
| spider_internal_unlock | OFF |
| spider_internal_xa | OFF |
| spider_internal_xa_id_type | 0 |
| spider_internal_xa_snapshot | 0 |
| spider_local_lock_table | OFF |
| spider_lock_exchange | OFF |
| spider_log_result_error_with_sql | 0 |
| spider_log_result_errors | 0 |
| spider_low_mem_read | -1 |
| spider_max_order | -1 |
| spider_multi_split_read | -1 |
| spider_net_read_timeout | 30 |
| spider_net_write_timeout | 60 |
| spider_ping_interval_at_trx_start | 3600 |
| spider_quick_mode | -1 |
| spider_quick_page_size | -1 |
| spider_read_only_mode | -1 |
| spider_remote_access_charset | |
| spider_remote_autocommit | 0 |
| spider_remote_default_database | |
| spider_remote_sql_log_off | 1 |
| spider_remote_time_zone | |
| spider_remote_trx_isolation | -1 |
| spider_reset_sql_alloc | 0 |
| spider_same_server_link | ON |
| spider_second_read | -1 |
| spider_select_column_mode | -1 |
| spider_selupd_lock_mode | -1 |
| spider_semi_split_read | -1 |
| spider_semi_split_read_limit | -1 |
| spider_semi_table_lock | 1 |
| spider_semi_table_lock_connection | -1 |
| spider_semi_trx | ON |
| spider_semi_trx_isolation | -1 |
| spider_skip_default_condition | -1 |
| spider_split_read | -1 |
| spider_sts_bg_mode | -1 |
| spider_sts_interval | -1 |
| spider_sts_mode | -1 |
| spider_sts_sync | 0 |
| spider_support_xa | OFF |
| spider_sync_autocommit | OFF |
| spider_sync_time_zone | OFF |
| spider_sync_trx_isolation | OFF |
| spider_table_init_error_interval | 1 |
| spider_udf_ct_bulk_insert_interval | -1 |
| spider_udf_ct_bulk_insert_rows | -1 |
| spider_udf_ds_bulk_insert_rows | -1 |
| spider_udf_ds_table_loop_mode | -1 |
| spider_udf_ds_use_real_table | -1 |
| spider_udf_table_lock_mutex_count | 20 |
| spider_udf_table_mon_mutex_count | 20 |
| spider_use_all_conns_snapshot | OFF |
| spider_use_consistent_snapshot | OFF |
| spider_use_default_database | ON |
| spider_use_flash_logs | OFF |
| spider_use_handler | 0 |
| spider_use_pushdown_udf | -1 |
| spider_use_snapshot_with_flush_tables | 0 |
| spider_use_table_charset | -1 |
| spider_version | 3.2.11 |
+---------------------------------------+--------+
99 rows in set (0.00 sec)
--
Nicolas Payart
Follow ups
