0

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;

  • The query will be doing an order by or group by that's too big to fit in memory, making it spill out to disk – Philᵀᴹ Oct 31 '16 at 8:28
  • What setting should I increase to avoid that ? It is indeed doing a group by, but I have plenty of ram free to give mariadb. – Ulrar Oct 31 '16 at 8:42
  • could You show the text of query? – a_vlad Oct 31 '16 at 9:11
  • 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) - exactly for this reason, good to have and query text and view definition, and may be more after.It is normal when with changing versions (even native mysql) - it start work different – a_vlad Oct 31 '16 at 10:42
  • I just added the query. Yeah, the old server was a Percona 5.5 and the new is a MariaDB 10.1.18, I'm guessing that's why there is warnings on the new and not the old, something must have changed. The warnings are "Truncated incorrect INTEGER value ..." (at least the first 30, didn't see the full 4000 of them). Is there a way maybe to know which part of the query throws the warnings ? – Ulrar Oct 31 '16 at 11:00
0

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.