I have a huge query being run by one of my developers which seems to hang no matter which performance tunables I adjust. Though I'm somewhat of novice at writing SQL queries, at first glance the query seems like it could use a lot of work. I've already set tmpdir to a RAM disk, but this doesn't seem to help. It would also appear that even though the mysql process state is in "copying to tmp table", nothing is actually being written there. This is on an Intel Xeon L5520 (8-core machine w/HT) with 72GB RAM.
Here is the query:
Here is an explain statement run on that query:
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 397520 Using temporary; Using filesort 2 DERIVED <derived3> ALL NULL NULL NULL NULL 397520 Using temporary; Using filesort 2 DERIVED o eq_ref PRIMARY,ndx_local_order_date,ndx_merchant_id PRIMARY 4 order_stats.master_ticket_id 1 Using where 2 DERIVED m1 eq_ref PRIMARY,fk_merchant3 PRIMARY 4 ticketing_brevard_prod.o.merchant_id 1 Using where 2 DERIVED oi ref order_items1 order_items1 4 order_stats.master_ticket_id 4 2 DERIVED th ref PRIMARY,ticket_header2 ticket_header2 4 order_stats.master_ticket_id 2 Using where 3 DERIVED m index_merge PRIMARY,fk_merchant3 PRIMARY,fk_merchant3 4,5 NULL 2 Using union(PRIMARY,fk_merchant3); Using where; Using index; Using temporary; Using filesort 3 DERIVED o2 ref PRIMARY,ndx_order_date,ndx_merchant_id ndx_merchant_id 4 ticketing_brevard_prod.m.merchant_id 40603 Using where 3 DERIVED th2 ref ticket_header2,ticket_header3,fk_ticket_header7 ticket_header2 4 ticketing_brevard_prod.o2.master_ticket_id 2 3 DERIVED customer_type eq_ref PRIMARY PRIMARY 4 ticketing_brevard_prod.th2.customer_type 1 3 DERIVED package_header eq_ref PRIMARY PRIMARY 4 ticketing_brevard_prod.th2.package_id 1 4 DEPENDENT SUBQUERY mpc eq_ref PRIMARY,merchant_package_characs2,merchant_package_characs3 PRIMARY 60 ticketing_brevard_prod.th2.merchant_id,ticketing_brevard_prod.th2.package_id 1 Using where
I think it's clear to see from the EXPLAIN output that copying 397520 rows of data to tmp is the likely culprit, however I'm not sure what to suggest to the dev who wrote query on how to improve it. It'd be great if someone could point me in the right direction. Thanks in advance!
SHOW VARIABLES LIKE '%buffer%'; Variable_name Value bulk_insert_buffer_size 8388608 innodb_buffer_pool_shm_checksum ON innodb_buffer_pool_shm_key 0 innodb_buffer_pool_size 60129542144 innodb_change_buffering inserts innodb_log_buffer_size 4194304 join_buffer_size 16777216 key_buffer_size 67108864 myisam_sort_buffer_size 134217728 net_buffer_length 16384 preload_buffer_size 32768 read_buffer_size 16777216 read_rnd_buffer_size 33554432 sort_buffer_size 1073741824 sql_buffer_result OFF