Expensive Query Takes Down Database Server -- Looking For Ways To Mitigate

I have a very expensive query that creates a temporary table for reporting purposes. I am having a problem where a denial of service attack takes place where 50+ of these queries p

Solution 1:

Hmm, I might try writing your query along these lines:

SELECT Sale_Item.deleted, Sale_Item.deleted_by,
       Sale_Item.sale_time, Sale_Item.sale_date,
       Sale_Item.comment,
       Sale_Item.payment_type,
       Sale_Item.customer_id,
       Sale_Item.employee_id,
       Sale_Item.category,
       Sale_Item.sale_id, Sale_Item.item_id, NULLas item_kit_id, Sale_Item.line, 
       Sale_Item.supplier_id,
       Sale_Item.serialnumber, Sale_Item.description,
       Sale_Item.quantity_purchased, Sale_Item.item_cost_price, Sale_Item.item_unit_price,
       Sale_Item.discount_percent,
       Sale_Item.lineSubtotal,
       Sale_Item.lineSubtotal *COALESCE(Tax.non_cumulative, 0) + (Sale_Item.lineSubtotal *COALESCE(Tax.non_cumulative, 0) + Sale_Item.non_cumulative) *COALESCE(Tax.cumulative, 0) AS lineTax,
       Sale_Item.lineSubtotal + (Sale_Item.lineSubtotal *COALESCE(Tax.non_cumulative, 0) + (Sale_Item.lineSubtotal *COALESCE(Tax.non_cumulative, 0) + Sale_Item.non_cumulative) *COALESCE(Tax.cumulative, 0)) AS lineTotal,
       Sale_Item.lineSubtotal - (Sale_Item.item_cost_price * Sale_Item.quantity_purchased) AS profit

FROM (SELECT Sale.deleted, Sale.deleted_by,
             Sale.sale_time, DATE(Sale.sale_time) AS sale_date,
             Sale.comment,
             Sale.payment_type,
             Sale.customer_id,
             Sale.employee_id,
             Item.category,
             Sale_Item.sale_id, Sale_Item.item_id, NULLas item_kit_id, Sale_Item.line, 
             Sale_Item.supplier_id,
             Sale_Item.serialnumber, Sale_Item.description,
             Sale_Item.quantity_purchased, Sale_Item.item_cost_price, Sale_Item.item_unit_price,
             Sale_Item.discount_percent,
             (Sale_Item.item_unit_price * Sale_Item.quantity_purchased) - (Sale_Item.item_unit_price * Sale_Item.quantity_purchased * Sale_Item.discount_percent /100) as lineSubtotal                 
      FROM phppos_sales_items Sale_Item
      JOIN phppos_sales Sale
        ON Sale.sale_id = Sale_Item.sale_id
           AND Sale.sale_time >=TIMESTAMP('2014-04-01')
           AND Sale.sale_time < TIMESTAMPADD(MONTH, 1, '2014-04-01')
           AND Sale.location_id =1AND Sale.store_account_payment =0) Sale_Item

LEFTJOIN (SELECT Tax.sale_id, Tax.item_id, Tax.line,
                  SUM(CASEWHEN Tax.cumulative =1THEN Tax.percent ELSE0END) as cumulative,
                  SUM(CASEWHEN Tax.cumulative <>1THEN Tax.percent ELSE0END) as non_cumulative
           FROM phppos_sales_item_taxes Tax
           JOIN phppos_sales Sale
             ON Sale.sale_id = Tax.sale_id
                AND Sale.sale_time >=TIMESTAMP('2014-04-01')
                AND Sale.sale_time < TIMESTAMPADD(MONTH, 1, '2014-04-01')
                AND Sale.location_id =1AND Sale.store_account_payment =0GROUPBY Tax.sale_id, Tax.item_id, Tax.line) Tax
       ON Tax.sale_id = Sale_Item.sale_id
          AND Tax.item_id = Sale_Item.sale_id
          AND Tax.line =Sale_Item.line 

Moved several columns for organizational purposes. This should have no large effect on processing time.

I removed the reference to phppos_suppliers as:

1. You don't use any columns from the table
2. It's a LEFT JOIN, meaning you don't require rows to exist there.

I moved the GROUP BY into a new subquery, because phppos_sales_item_taxes is the only table that could have duplicate rows for the given criteria. I included the reference to phppos_sales because I'm not sure if MySQL's optimizer (or any, really) is smart enough to push citeria down.

The main part of the query has been moved to a subquery simply so I wouldn't need to type the formula for lineSubtotal multiple times. I've used the same formulas throughout, but there are simplified versions available:

Sale_Item.item_unit_price * Sale_Item.quantity_purchased * (1 - (Sale_Item.discount_percent / 100)) as lineSubtotal  

Sale_Item.lineSubtotal * COALESCE(Tax.non_cumulative + Tax.cumulative + Tax.non_cumulative * Tax.cumulative, 0) as Tax

.... you may have to run these by accounting, though, as they tend to be (understandably) touchy about order of operations. This may result in a faster runtime but I doubt it; mostly this is about simplification of the terms to something more readable.

You didn't provide any table layouts for the other half of the query, but I presume it's similar. The related modification is left as an exercise for the reader.

---

General Mitigation Strategies

Beyond any potential speedup changing the query might have, there's a number of things you could do to curtail the problem:

1. In your application layer, force this query (and possibly others) to go through a job submission process whose results can be retrieved later. A new copy of this query can't be run until the previous one completes. I assume php has an existing library for this. Simply throttling submission in general may be all you need.
2. The data being retrieved appears amenable to caching - store everything prior to the most recent processed sale_date, and then only get new information on-the-fly (although the transformation isn't really that different from the original - however, simply not doing more joins may help).
3. Disallow queries over the current processing timespan. This should keep the system from attempting to access rows that haven't been committed yet, and potentially away from index pages under modification. This sort of trick works best if your storage is laid out to take advantage of concurrent I/O.