Copying to tmp table taking forever

Question

I migrated last week a rather big website to new servers. Everything seems to go fine, but since then the client noticed something we didn't see when testing before, one of the views is very slow.

I tried using the profiler and indeed, the query spends 43 seconds on "copying to tmp table" and the rest is almost instant. On the old server the same query took less than a second, so clearly it shouldn't take 43 on a bigger server. I assume something isn't configured properly, what could be the cause ? I disabled the query cache (with query cache enabled it seems to spend 43s copying to query cache, if that matters).

It's a replicated setup, with two MariaDB being master and slave for each other. The queries are run only on the first one, we use master / master for the ease of failover, not for load balancing. We have that exact config for tons of servers so I don't think that's it, but who knows.

I ran mysqltuner and increased what it told me to increase a few times, the only things left are the join without indexes and the tmp table to disks. Don't think I can ever do anything about those. I checked, that particular query doesn't seem to go to disk (which is an SSD), and anyway I've put tmpdir in a tmpfs to be sure. It's really "copying to tmp table", no "to disk" in there.

I checked with iotop, it doesn't seem to be reading from disk either.

I've used explain on the query, here is the part that takes forever :

*************************** 1. row ***************************
       id: 1
select_type: SIMPLE
    table: tbl_stock
     type: ALL
possible_keys: PRIMARY
      key: NULL
  key_len: NULL
      ref: NULL
     rows: 1
    Extra: Using where; Using temporary; Using filesort

Now I'm not sure what tbl_stock is, it's not even in the query. The query seems to be using another view (that one works fine) so maybe it's some syntax I'm not used to to designate the stock column in that ?

I should have access to the old server, if anyone has an idea of the config options I should compare with the new ones. I'm not looking for fix to the query itself, just hints on what differences I could have with the old servers that could explain huge copying to tmp times. Thanks !

EDIT : I think the problem is that on the old server the query works fine, and on the new it generates 4000 warnings. Guess that prevents it from being cached.

EDIT 2 : Here is the query

select erp_view_supplyneeds_base.product_id AS product_id,erp_view_supplyneeds_base.manufacturer_id AS manufacturer_id,erp_view_supplyneeds_base.sku AS sku,erp_view_supplyneeds_base.name AS name,erp_view_supplyneeds_base.waiting_for_delivery_qty AS waiting_for_delivery_qty,if(((sum(erp_view_supplyneeds_base.qty_needed_for_valid_orders) > 0) and (sum(erp_view_supplyneeds_base.qty_needed_for_valid_orders) > erp_view_supplyneeds_base.waiting_for_delivery_qty)),'1_valid_orders',if(((sum(erp_view_supplyneeds_base.qty_needed_for_orders) > 0) and ((sum(erp_view_supplyneeds_base.qty_needed_for_orders) - sum(erp_view_supplyneeds_base.qty_needed_for_valid_orders)) > (erp_view_supplyneeds_base.waiting_for_delivery_qty - sum(erp_view_supplyneeds_base.qty_needed_for_valid_orders)))),'2_orders',if(((sum(erp_view_supplyneeds_base.qty_needed_for_ideal_stock) > 0) and ((sum(erp_view_supplyneeds_base.qty_needed_for_orders) + sum(erp_view_supplyneeds_base.qty_needed_for_ideal_stock)) > erp_view_supplyneeds_base.waiting_for_delivery_qty)),'3_prefered_stock_level',if((sum(erp_view_supplyneeds_base.qty_needed_for_manual_supply_needs) > 0),'4_manual_supply_need','5_pending_delivery')))) AS status,if(((sum(erp_view_supplyneeds_base.qty_needed_for_valid_orders) - erp_view_supplyneeds_base.waiting_for_delivery_qty) > 0),(sum(erp_view_supplyneeds_base.qty_needed_for_valid_orders) - erp_view_supplyneeds_base.waiting_for_delivery_qty),0) AS qty_min,if(((sum(((erp_view_supplyneeds_base.qty_needed_for_orders + erp_view_supplyneeds_base.qty_needed_for_ideal_stock) + erp_view_supplyneeds_base.qty_needed_for_manual_supply_needs)) - erp_view_supplyneeds_base.waiting_for_delivery_qty) > 0),(sum(((erp_view_supplyneeds_base.qty_needed_for_orders + erp_view_supplyneeds_base.qty_needed_for_ideal_stock) + erp_view_supplyneeds_base.qty_needed_for_manual_supply_needs)) - erp_view_supplyneeds_base.waiting_for_delivery_qty),0) AS qty_max from erp_view_supplyneeds_base where ((erp_view_supplyneeds_base.qty_needed_for_valid_orders > 0) or (erp_view_supplyneeds_base.qty_needed_for_orders > 0) or (erp_view_supplyneeds_base.qty_needed_for_ideal_stock > 0) or (erp_view_supplyneeds_base.qty_needed_for_manual_supply_needs > 0)) group by erp_view_supplyneeds_base.product_id,erp_view_supplyneeds_base.manufacturer_id,erp_view_supplyneeds_base.sku,erp_view_supplyneeds_base.name;

Answer 1

Okay, that's solved. The module's developer answered, it's a known bug they have with MariaDB, just had to comment out the config about join_buffer_size, apparently anything higher than the default value somehow causes problem to that specific query. Commented it out, works perfectly now ! Thanks anyway