mysql - expensive query takes down database server -- looking for ways to mitigate -
i have expensive query creates temporary table reporting purposes. having problem denial of service attack takes place 50+ of these queries pile (all same user). causes database server pretty taken down. query can take 1-10 seconds run , can have lot of rows in resulting temporary table.
i not sure if there easy way improve query performance without re-architecting entire reporting piece of application.
what solutions can use apply solve problem these queries piling , taking down server.
my questions are:
can think of solution improving query performance (query below)
does make sense place session limit of 1 query can run once , prevent pile up
any other ideas on how prevent attack.
here query:
create temporary table phppos_sales_items_temp (select phppos_sales.deleted deleted,phppos_sales.deleted_by deleted_by, sale_time, date(sale_time) sale_date, phppos_sales_items.sale_id, comment,payment_type, customer_id, employee_id, phppos_items.item_id, null item_kit_id, supplier_id, quantity_purchased, item_cost_price, item_unit_price, category, discount_percent, (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100) subtotal, phppos_sales_items.line line, serialnumber, phppos_sales_items.description description, (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)+(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(sum(case when cumulative != 1 percent else 0 end)/100) +(((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(sum(case when cumulative != 1 percent else 0 end)/100) + (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)) *(sum(case when cumulative = 1 percent else 0 end))/100) total, (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(sum(case when cumulative != 1 percent else 0 end)/100) +(((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(sum(case when cumulative != 1 percent else 0 end)/100) + (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)) *(sum(case when cumulative = 1 percent else 0 end))/100) tax, (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100) - (item_cost_price*quantity_purchased) profit phppos_sales_items inner join phppos_sales on phppos_sales_items.sale_id=phppos_sales.sale_id inner join phppos_items on phppos_sales_items.item_id=phppos_items.item_id left outer join phppos_suppliers on phppos_items.supplier_id=phppos_suppliers.person_id left outer join phppos_sales_items_taxes on phppos_sales_items.sale_id=phppos_sales_items_taxes.sale_id , phppos_sales_items.item_id=phppos_sales_items_taxes.item_id , phppos_sales_items.line=phppos_sales_items_taxes.line sale_time between "2014-04-01 00:00:00" , "2014-04-30 23:59:59" , phppos_sales.location_id='1' , phppos_sales.store_account_payment=0 group sale_id, item_id, line) union (select phppos_sales.deleted deleted,phppos_sales.deleted_by deleted_by, sale_time, date(sale_time) sale_date, phppos_sales_item_kits.sale_id, comment,payment_type, customer_id, employee_id, null item_id, phppos_item_kits.item_kit_id, '' supplier_id, quantity_purchased, item_kit_cost_price, item_kit_unit_price, category, discount_percent, (item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100) subtotal, phppos_sales_item_kits.line line, '' serialnumber, phppos_sales_item_kits.description description, (item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)+(item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(sum(case when cumulative != 1 percent else 0 end)/100) +(((item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(sum(case when cumulative != 1 percent else 0 end)/100) + (item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)) *(sum(case when cumulative = 1 percent else 0 end))/100) total, (item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(sum(case when cumulative != 1 percent else 0 end)/100) +(((item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(sum(case when cumulative != 1 percent else 0 end)/100) + (item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)) *(sum(case when cumulative = 1 percent else 0 end))/100) tax, (item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100) - (item_kit_cost_price*quantity_purchased) profit phppos_sales_item_kits inner join phppos_sales on phppos_sales_item_kits.sale_id=phppos_sales.sale_id inner join phppos_item_kits on phppos_sales_item_kits.item_kit_id=phppos_item_kits.item_kit_id left outer join phppos_sales_item_kits_taxes on phppos_sales_item_kits.sale_id=phppos_sales_item_kits_taxes.sale_id , phppos_sales_item_kits.item_kit_id=phppos_sales_item_kits_taxes.item_kit_id , phppos_sales_item_kits.line=phppos_sales_item_kits_taxes.line sale_time between "2014-04-01 00:00:00" , "2014-04-30 23:59:59" , phppos_sales.location_id='1' , phppos_sales.store_account_payment=0 group sale_id, item_kit_id, line) order sale_id, line explain:
+----------+--------+----------------------------------+--------------+---------+-----------------------------------------------------------------------------------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | | +----+--------------+------------------------------+--------+----------------------------------+--------------+---------+-----------------------------------------------------------------------------------------------------------+------+----------------------------------------------+ | 1 | primary | phppos_sales | range | primary,location_id,sales_search | sales_search | 12 | null | 113 | using where; using temporary; using filesort | | 1 | primary | phppos_sales_items | ref | primary,item_id | primary | 4 | pos.phppos_sales.sale_id | 1 | | | 1 | primary | phppos_items | eq_ref | primary | primary | 4 | pos.phppos_sales_items.item_id | 1 | | | 1 | primary | phppos_suppliers | ref | person_id | person_id | 4 | pos.phppos_items.supplier_id | 1 | using index | | 1 | primary | phppos_sales_items_taxes | ref | primary,item_id | primary | 12 | pos.phppos_sales_items.sale_id,pos.phppos_sales_items.item_id,pos.phppos_sales_items.line | 1 | | | 2 | union | phppos_sales_item_kits | | primary,item_kit_id | null | null | null | 1 | using temporary; using filesort | | 2 | union | phppos_item_kits | eq_ref | primary | primary | 4 | pos.phppos_sales_item_kits.item_kit_id | 1 | | | 2 | union | phppos_sales_item_kits_taxes | ref | primary,item_id | primary | 12 | pos.phppos_sales_item_kits.sale_id,pos.phppos_sales_item_kits.item_kit_id,pos.phppos_sales_item_kits.line | 1 | | | 2 | union | phppos_sales | eq_ref | primary,location_id,sales_search | primary | 4 | pos.phppos_sales_item_kits.sale_id | 1 | using | | null | union result | <union1,2> | | null | null | null | null | null | using filesort | +----+--------------+------------------------------+--------+----------------------------------+--------------+---------+-----------------------------------------------------------------------------------------------------------+------+----------------------------------------------+ 10 rows in set (0.00 sec)
create tables:
mysql> show create table phppos_sales_items; | table | create table | +--------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | phppos_sales_items | create table `phppos_sales_items` ( `sale_id` int(10) not null default '0', `item_id` int(10) not null default '0', `description` varchar(255) collate utf8_unicode_ci default null, `serialnumber` varchar(255) collate utf8_unicode_ci default null, `line` int(3) not null default '0', `quantity_purchased` decimal(23,10) not null default '0.0000000000', `item_cost_price` decimal(23,10) not null, `item_unit_price` decimal(23,10) not null, `discount_percent` int(11) not null default '0', primary key (`sale_id`,`item_id`,`line`), key `item_id` (`item_id`), constraint `phppos_sales_items_ibfk_1` foreign key (`item_id`) references `phppos_items` (`item_id`), constraint `phppos_sales_items_ibfk_2` foreign key (`sale_id`) references `phppos_sales` (`sale_id`) ) engine=innodb default charset=utf8 collate=utf8_unicode_ci | 1 row in set (0.01 sec) mysql> show create table phppos_sales; +--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | table | create table | +--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | phppos_sales | create table `phppos_sales` ( `sale_time` timestamp not null default current_timestamp, `customer_id` int(10) default null, `employee_id` int(10) not null default '0', `comment` text collate utf8_unicode_ci not null, `show_comment_on_receipt` int(1) not null default '0', `sale_id` int(10) not null auto_increment, `payment_type` varchar(255) collate utf8_unicode_ci default null, `cc_ref_no` varchar(255) collate utf8_unicode_ci not null, `auth_code` varchar(255) collate utf8_unicode_ci default '', `deleted_by` int(10) default null, `deleted` int(1) not null default '0', `suspended` int(1) not null default '0', `store_account_payment` int(1) not null default '0', `location_id` int(11) not null, primary key (`sale_id`), key `customer_id` (`customer_id`), key `employee_id` (`employee_id`), key `deleted` (`deleted`), key `location_id` (`location_id`), key `phppos_sales_ibfk_4` (`deleted_by`), key `sales_search` (`location_id`,`store_account_payment`,`sale_time`,`sale_id`), constraint `phppos_sales_ibfk_3` foreign key (`location_id`) references `phppos_locations` (`location_id`), constraint `phppos_sales_ibfk_4` foreign key (`deleted_by`) references `phppos_employees` (`person_id`), constraint `phppos_sales_ibfk_1` foreign key (`employee_id`) references `phppos_employees` (`person_id`), constraint `phppos_sales_ibfk_2` foreign key (`customer_id`) references `phppos_customers` (`person_id`) ) engine=innodb default charset=utf8 collate=utf8_unicode_ci | 1 row in set (0.00 sec) mysql> show create table phppos_items; +--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | table | create table | +--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | phppos_items | create table `phppos_items` ( `name` varchar(255) collate utf8_unicode_ci not null, `category` varchar(255) collate utf8_unicode_ci not null, `supplier_id` int(11) default null, `item_number` varchar(255) collate utf8_unicode_ci default null, `product_id` varchar(255) collate utf8_unicode_ci default null, `description` varchar(255) collate utf8_unicode_ci not null, `tax_included` int(1) not null default '0', `cost_price` decimal(23,10) not null, `unit_price` decimal(23,10) not null, `promo_price` decimal(23,10) default null, `start_date` date default null, `end_date` date default null, `reorder_level` decimal(23,10) default null, `item_id` int(10) not null auto_increment, `allow_alt_description` tinyint(1) not null, `is_serialized` tinyint(1) not null, `image_id` int(10) default null, `override_default_tax` int(1) not null default '0', `is_service` int(1) not null default '0', `deleted` int(1) not null default '0', primary key (`item_id`), unique key `item_number` (`item_number`), unique key `product_id` (`product_id`), key `phppos_items_ibfk_1` (`supplier_id`), key `name` (`name`), key `category` (`category`), key `deleted` (`deleted`), key `phppos_items_ibfk_2` (`image_id`), constraint `phppos_items_ibfk_1` foreign key (`supplier_id`) references `phppos_suppliers` (`person_id`), constraint `phppos_items_ibfk_2` foreign key (`image_id`) references `phppos_app_files` (`file_id`) ) engine=innodb default charset=utf8 collate=utf8_unicode_ci | 1 row in set (0.00 sec) mysql> show create table phppos_suppliers; +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | table | create table | +------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | phppos_suppliers | create table `phppos_suppliers` ( `person_id` int(10) not null, `company_name` varchar(255) collate utf8_unicode_ci not null, `account_number` varchar(255) collate utf8_unicode_ci default null, `deleted` int(1) not null default '0', unique key `account_number` (`account_number`), key `person_id` (`person_id`), key `deleted` (`deleted`), constraint `phppos_suppliers_ibfk_1` foreign key (`person_id`) references `phppos_people` (`person_id`) ) engine=innodb default charset=utf8 collate=utf8_unicode_ci | 1 row in set (0.00 sec) mysql> show create table phppos_sales_items_taxes; +--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | table | create table | +--------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | phppos_sales_items_taxes | create table `phppos_sales_items_taxes` ( `sale_id` int(10) not null, `item_id` int(10) not null, `line` int(3) not null default '0', `name` varchar(255) collate utf8_unicode_ci not null, `percent` decimal(15,3) not null, `cumulative` int(1) not null default '0', primary key (`sale_id`,`item_id`,`line`,`name`,`percent`), key `item_id` (`item_id`), constraint `phppos_sales_items_taxes_ibfk_1` foreign key (`sale_id`) references `phppos_sales_items` (`sale_id`), constraint `phppos_sales_items_taxes_ibfk_2` foreign key (`item_id`) references `phppos_items` (`item_id`) ) engine=innodb default charset=utf8 collate=utf8_unicode_ci | 1 row in set (0.00 sec)
hmm, might try writing 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, null 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) 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)) linetotal, sale_item.linesubtotal - (sale_item.item_cost_price * sale_item.quantity_purchased) profit (select sale.deleted, sale.deleted_by, sale.sale_time, date(sale.sale_time) sale_date, sale.comment, sale.payment_type, sale.customer_id, sale.employee_id, item.category, sale_item.sale_id, sale_item.item_id, null 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) linesubtotal phppos_sales_items sale_item join phppos_sales sale on sale.sale_id = sale_item.sale_id , sale.sale_time >= timestamp('2014-04-01') , sale.sale_time < timestampadd(month, 1, '2014-04-01') , sale.location_id = 1 , sale.store_account_payment = 0) sale_item left join (select tax.sale_id, tax.item_id, tax.line, sum(case when tax.cumulative = 1 tax.percent else 0 end) cumulative, sum(case when tax.cumulative <> 1 tax.percent else 0 end) non_cumulative phppos_sales_item_taxes tax join phppos_sales sale on sale.sale_id = tax.sale_id , sale.sale_time >= timestamp('2014-04-01') , sale.sale_time < timestampadd(month, 1, '2014-04-01') , sale.location_id = 1 , sale.store_account_payment = 0 group tax.sale_id, tax.item_id, tax.line) tax on tax.sale_id = sale_item.sale_id , tax.item_id = sale_item.sale_id , tax.line =sale_item.line moved several columns organizational purposes. should have no large effect on processing time.
i removed reference phppos_suppliers as:
- you don't use columns table
- it's
left join, meaning don't require rows exist there.
i moved group by new subquery, because phppos_sales_item_taxes table have duplicate rows given criteria. included reference phppos_sales because i'm not sure if mysql's optimizer (or any, really) smart enough push citeria down.
the main part of query has been moved subquery wouldn't need type formula linesubtotal multiple times. i've used same formulas throughout, there simplified versions available:
sale_item.item_unit_price * sale_item.quantity_purchased * (1 - (sale_item.discount_percent / 100)) linesubtotal sale_item.linesubtotal * coalesce(tax.non_cumulative + tax.cumulative + tax.non_cumulative * tax.cumulative, 0) tax .... may have run these accounting, though, tend (understandably) touchy order of operations. may result in faster runtime doubt it; simplification of terms more readable.
you didn't provide table layouts other half of query, presume it's similar. related modification left exercise reader.
general mitigation strategies
beyond potential speedup changing query might have, there's number of things curtail problem:
- in application layer, force query (and possibly others) go through job submission process results can retrieved later. new copy of query can't run until previous 1 completes. assume php has existing library this. throttling submission in general may need.
- the data being retrieved appears amenable caching - store prior recent processed
sale_date, , new information on-the-fly (although transformation isn't that different original - however, not doing more joins may help). - disallow queries on current processing timespan. should keep system attempting access rows haven't been committed yet, , potentially away index pages under modification. sort of trick works best if storage laid out take advantage of concurrent i/o.
Comments
Post a Comment