GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Query Routinely Crashing Server

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query Routinely Crashing Server

    I am having a problem where the following query crashes mySQL periodically. I can't see a reason for it in the error log, but suspect a memory issue due to the subselects or the views or a memory issue that is evoking a bug in mySQL. The OS is 32bit Redhat. Fairly standard settings. All tables in this query are innodb, innodb_buffer_pool = 2200m, tmp_table_size = 32m, max_heap_table = 16m, system has 8 gigs of ram, connections during failure are sometimes as low as 2 or 3, system is
    dedicated to mySQL only.

    The query is various forms of the following. This query is a key reporting query in our application and the underlying tables are highly optimized to make this query perform well at least from the standapoint of an explain. This query typically
    processes hundreds of thousands of rows in about .05 seconds so performance wise it is acceptable, but the crashes are not.

    =============== QUERY ============

    select SQL_NO_CACHE
    `vre`.`guild_id` AS `guild_id`,
    `cp`.`character_id` AS `character_id`,
    `cp`.`character_name` AS `character_name`,
    `cp`.`hier_1` AS `hier_1`,
    `cp`.`hier_2` AS `hier_2`,
    `cp`.`hier_3` AS `hier_3`,
    `cp`.`hier_4` AS `hier_4`,
    `cp`.`level` AS `level`,
    /* 8th field */
    (
    ifnull(sum(`vre`.`total`), 0) + ifnull((select sum(t1.earned_amount) from rr_adjustment_details t1, rr_adjustment t2
    where t1.pool_id = 35 and t2.pool_id = 35 and t1.adjust_id = t2.adjust_id and t2.guild_id = 48 and t1.character_id = cp.character_id group by t1.character_id), 0)
    )
    AS `earned`,
    /* 9th field */
    (
    ifnull((select sum(spent) from uv_rm_spent where guild_id = 48 and character_id = `cp`.`character_id`
    and pool_id = 35 group by guild_id, character_id),0)
    + ifnull((select sum(t1.spent_amount) from rr_adjustment_details t1, rr_adjustment t2 where t1.pool_id = 35 and t2.pool_id = 35 and t1.adjust_id = t2.adjust_id
    and t2.guild_id = 48 and t1.character_id = cp.character_id group by t1.character_id), 0)
    )
    AS `spent`,
    /* 10th field */
    /* REGULAR DKP */
    (
    sum(`vre`.`total`)
    - ifnull((select sum(spent) from uv_rm_spent where
    pool_id = 35 and guild_id = 48 and character_id = `cp`.`character_id` group by guild_id, character_id),0)
    + ifnull((select sum(t1.earned_amount) from rr_adjustment_details t1, rr_adjustment t2 where
    t1.pool_id = 35 and t2.pool_id = 35 and t1.adjust_id = t2.adjust_id and t2.guild_id = 48 and t1.character_id = cp.character_id group by t1.character_id), 0)
    - ifnull((select sum(t1.spent_amount) from rr_adjustment_details t1, rr_adjustment t2 where
    t1.pool_id = 35 and t2.pool_id = 35 and t1.adjust_id = t2.adjust_id and t2.guild_id = 48 and t1.character_id = cp.character_id group by t1.character_id) , 0)
    + ifnull((select sum(t1.total_amount) from rr_adjustment_details t1, rr_adjustment t2 where
    t1.pool_id = 35 and t2.pool_id = 35 and t1.adjust_id = t2.adjust_id and t2.guild_id = 48 and t1.character_id = cp.character_id group by t1.character_id) , 0)
    )
    AS `current` ,(
    select count(`uv_raid_event_character_detail`.`raid_event _id`) AS `total_events`
    from `uv_raid_event_character_detail`
    where pool_id = 35 and (`uv_raid_event_character_detail`.`date` >= (curdate() - interval 30 day)) and guild_id = 48 and `uv_raid_event_character_detail`.`character_id` = `cp`.`character_id`
    and attendance = 1
    group by character_id, `uv_raid_event_character_detail`.`guild_id`
    )
    as raids_attended_30,
    /* 12th field */
    (
    select count(`vw_new_guild_events`.`raid_event_id`)
    AS `total_events` from `vw_new_guild_events` where (`vw_new_guild_events`.`date` >= (curdate() - interval 30 day)) AND guild_id = 48 and pool_id = 35 and attendance = 1 group by guild_id
    )
    as guild_total_last_30,
    /* 13th field */
    (
    select count(`uv_raid_event_character_detail`.`raid_event _id`) AS `total_events` from `uv_raid_event_character_detail`
    where pool_id = 35 and (`uv_raid_event_character_detail`.`date` >= (curdate() - interval 60 day)) and guild_id = 48 and `uv_raid_event_character_detail`.`character_id` = `cp`.`character_id`
    and attendance = 1
    group by character_id, `uv_raid_event_character_detail`.`guild_id`
    )
    as raids_attended_60,
    /* 14th field */
    (
    select count(`vw_new_guild_events`.`raid_event_id`) AS `_` from `vw_new_guild_events`
    where (`vw_new_guild_events`.`date` >= (curdate() - interval 60 day)) AND guild_id = 48 and pool_id = 35 and attendance = 1 group by guild_id
    )
    as guild_total_last_60,
    /* 15th field */
    (
    select max(date) from uv_raid_event_character_detail where character_id = `cp`.`character_id` and guild_id = 48 and pool_id = 35
    )
    as last_raid ,`cp`.`game_id` AS `game_id`,`cp`.`gender` AS `gender` from `character_profile` `cp` join `uv_rm_earned` `vre` , character_raid_status crs where
    vre.pool_id = 35 and `cp`.`character_id` = `vre`.`character_id`
    and vre.guild_id = 48
    and cp.deleted != 1 and crs.character_id = cp.character_id and crs.guild_id = 48 and crs.active = 1 group by
    guild_id, character_id order by 2 asc

    =============== END QUERY ============

    Stacke traces for two crashes follow:

    First Stack Trace:

    0x8181560 handle_segfault + 656
    0x81cb0be store_val_in_field(Field*, Item*, enum_check_fields) + 238
    0x81cb1eb store_val_in_field(Field*, Item*, enum_check_fields) + 539
    0x81cb1eb store_val_in_field(Field*, Item*, enum_check_fields) + 539
    0x81cba79 JOIN::remove_subq_pushed_predicates(Item**) + 1785
    0x81dec0e JOIN:ptimize() + 2654
    0x8153fcf subselect_single_select_engine::exec() + 655
    0x815324e Item_subselect::exec() + 46
    0x8153455 Item_singlerow_subselect::val_real() + 21
    0x812a657 Item_func_ifnull::real_op() + 23
    0x811e0eb Item_func_numhybrid::val_real() + 59
    0x8102832 Item::save_in_field(Field*, bool) + 482
    0x810c4a0 Item_result_field::save_in_result_field(bool) + 32
    0x81cc5b5 copy_funcs(Item**) + 37
    0x81d830a create_virtual_tmp_table(THD*, List&) + 2234
    0x81d89e8 create_virtual_tmp_table(THD*, List&) + 3992
    0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
    0x81d89e8 create_virtual_tmp_table(THD*, List&) + 3992
    0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
    0x81d89e8 create_virtual_tmp_table(THD*, List&) + 3992
    0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
    0x81d89e8 create_virtual_tmp_table(THD*, List&) + 3992
    0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
    0x81d89e8 create_virtual_tmp_table(THD*, List&) + 3992
    0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
    0x81d89e8 create_virtual_tmp_table(THD*, List&) + 3992
    0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
    0x81d8d14 sub_select(JOIN*, st_join_table*, bool) + 756
    0x81e400f JOIN::exec() + 2015
    0x81e5d60 mysql_select(THD*, Item***, st_table_list*, unsigned int, List&, Item*, unsigned int, st_order*, st_order*, Item*, st_ord + 368
    0x81e668a handle_select(THD*, st_lex*, select_result*, unsigned long) + 314
    0x819825f mysql_execute_command(THD*) + 6527
    0x819dc4f mysql_parse(THD*, char*, unsigned int) + 495
    0x819e1d5 dispatch_command(enum_server_command, THD*, char*, unsigned int) + 1269
    0x819f66d do_command(THD*) + 173
    0x81a01a0 handle_one_connection + 2512
    0x73245b (?)
    0x64224e (?)

    Second Stack Trace:

    0x8181560 handle_segfault + 656
    0x81cb0be store_val_in_field(Field*, Item*, enum_check_fields) + 238
    0x81cb1eb store_val_in_field(Field*, Item*, enum_check_fields) + 539
    0x81cb1eb store_val_in_field(Field*, Item*, enum_check_fields) + 539
    0x81cba79 JOIN::remove_subq_pushed_predicates(Item**) + 1785
    0x81dec0e JOIN:ptimize() + 2654
    0x8153fcf subselect_single_select_engine::exec() + 655
    0x815324e Item_subselect::exec() + 46
    0x8153455 Item_singlerow_subselect::val_real() + 21
    0x812a657 Item_func_ifnull::real_op() + 23
    0x811e0eb Item_func_numhybrid::val_real() + 59
    0x8102832 Item::save_in_field(Field*, bool) + 482
    0x810c4a0 Item_result_field::save_in_result_field(bool) + 32
    0x81cc5b5 copy_funcs(Item**) + 37
    0x81d830a create_virtual_tmp_table(THD*, List&) + 2234
    0x81d89e8 create_virtual_tmp_table(THD*, List&) + 3992
    0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
    0x81d89e8 create_virtual_tmp_table(THD*, List&) + 3992
    0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
    0x81d89e8 create_virtual_tmp_table(THD*, List&) + 3992
    0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
    0x81d89e8 create_virtual_tmp_table(THD*, List&) + 3992
    0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
    0x81d89e8 create_virtual_tmp_table(THD*, List&) + 3992
    0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
    0x81d89e8 create_virtual_tmp_table(THD*, List&) + 3992
    0x81d8a8c sub_select(JOIN*, st_join_table*, bool) + 108
    0x81d8d14 sub_select(JOIN*, st_join_table*, bool) + 756
    0x81e400f JOIN::exec() + 2015
    0x81e5d60 mysql_select(THD*, Item***, st_table_list*, unsigned int, List&, Item*, unsigned int, st_order*, st_order*, Item*, st_ord + 368
    0x81e668a handle_select(THD*, st_lex*, select_result*, unsigned long) + 314
    0x819825f mysql_execute_command(THD*) + 6527
    0x819dc4f mysql_parse(THD*, char*, unsigned int) + 495
    0x819e1d5 dispatch_command(enum_server_command, THD*, char*, unsigned int) + 1269
    0x819f66d do_command(THD*) + 173
    0x81a01a0 handle_one_connection + 2512
    0x73245b (?)
    0x64224e (?)


    Any help would be great. I realize there are methods to improve this query, particularly the abundance of correlated subqueries. However, the correlated subqueries are there because the math done in the query is modular and breaking the query up improves our maintenance and adding of different formulas. For now I am stuck with this query format and need to determine what the bug/issue is that is leading to a crash.
Working...
X