Reporting query blocks other query, but ISOLATION LEVEL READ UNCOMMITTED SET

Question

On MySQL 5.7.11 running on Amazon RDS with InnoDB

I have a fairly heavy reporting query that takes about 3 minutes to run. During this time I can't access my reporting screen, which reads some min and max dates from one of the tables that is included in the report.

I would have thought by setting "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" read-only queries would not affect each other at all? I have this set both at session level and my param group on RDS

Is there anything I'm missing?

SHOW ENGINE INNODB STATUS

http://pastebin.com/XvU1AdNM

show create table ...

auto_increment number indicate rough row counts

CREATE TABLE `transaction` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file_id` int(11) DEFAULT NULL,
  `countid` int(11) NOT NULL,
  `txn_date` datetime NOT NULL,
  `txn_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `user_rmn` bigint(20) NOT NULL,
  `customer_no` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `aggregator_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `trans_amount` decimal(15,4) NOT NULL,
  `incoming_commission` decimal(15,4) NOT NULL,
  `mmplt_txn_id` int(11) NOT NULL,
  `product_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `txn_category` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `circle` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `status` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `role` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `number` int(11) DEFAULT NULL,
  `user_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `city_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `state_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `retailer_commission` decimal(15,4) NOT NULL,
  `total_commission` decimal(15,4) NOT NULL,
  `net_revenue` decimal(15,4) NOT NULL,
  `ad_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ad_commission` decimal(15,4) DEFAULT NULL,
  `md_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `md_commission` decimal(15,4) DEFAULT NULL,
  `cnf_name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cnf_commission` decimal(15,4) DEFAULT NULL,
  `ad_id` bigint(20) DEFAULT NULL,
  `md_id` bigint(20) DEFAULT NULL,
  `cnf_id` bigint(20) DEFAULT NULL,
  `operator_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `txnId` (`txn_id`),
  KEY `IDX_723705D193CB796C` (`file_id`),
  KEY `date_idx` (`txn_date`),
  KEY `user_idx` (`user_id`),
  KEY `cnf_idx` (`cnf_id`),
  KEY `md_idx` (`md_id`),
  KEY `ad_idx` (`ad_id`),
  KEY `user_rmn_idx` (`user_rmn`),
  KEY `trans_amount_idx` (`trans_amount`),
  KEY `incoming_commission_idx` (`incoming_commission`),
  KEY `retailer_commission_idx` (`retailer_commission`),
  KEY `ad_commission_idx` (`ad_commission`),
  KEY `md_commission_idx` (`md_commission`),
  KEY `cnf_commission_idx` (`cnf_commission`),
  KEY `cnf_date_idx` (`txn_date`,`cnf_id`),
  KEY `md_date_idx` (`txn_date`,`md_id`),
  KEY `ad_date_idx` (`txn_date`,`ad_id`),
  KEY `user_rmn_date_idx` (`txn_date`,`user_rmn`),
  CONSTRAINT `FK_723705D193CB796C` FOREIGN KEY (`file_id`) REFERENCES `file_to_sync` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11370410 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `operator` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `category_low_id` int(11) DEFAULT NULL,
  `category_medium_id` int(11) DEFAULT NULL,
  `category_high_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_D7A6A781B596C062` (`category_low_id`),
  KEY `IDX_D7A6A78125326495` (`category_medium_id`),
  KEY `IDX_D7A6A7818196AB83` (`category_high_id`),
  CONSTRAINT `FK_D7A6A78125326495` FOREIGN KEY (`category_medium_id`) REFERENCES `operator_category_medium` (`id`),
  CONSTRAINT `FK_D7A6A7818196AB83` FOREIGN KEY (`category_high_id`) REFERENCES `operator_category_high` (`id`),
  CONSTRAINT `FK_D7A6A781B596C062` FOREIGN KEY (`category_low_id`) REFERENCES `operator_category_low` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `operator` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `category_low_id` int(11) DEFAULT NULL,
  `category_medium_id` int(11) DEFAULT NULL,
  `category_high_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_D7A6A781B596C062` (`category_low_id`),
  KEY `IDX_D7A6A78125326495` (`category_medium_id`),
  KEY `IDX_D7A6A7818196AB83` (`category_high_id`),
  CONSTRAINT `FK_D7A6A78125326495` FOREIGN KEY (`category_medium_id`) REFERENCES `operator_category_medium` (`id`),
  CONSTRAINT `FK_D7A6A7818196AB83` FOREIGN KEY (`category_high_id`) REFERENCES `operator_category_high` (`id`),
  CONSTRAINT `FK_D7A6A781B596C062` FOREIGN KEY (`category_low_id`) REFERENCES `operator_category_low` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


CREATE TABLE `operator_category_medium` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `operator_category_high` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `depositor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `depositor_id` bigint(20) NOT NULL,
  `name` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `amount` decimal(15,4) NOT NULL,
  `deposited` datetime NOT NULL,
  `details` longtext COLLATE utf8_unicode_ci NOT NULL,
  `netsuite_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `depositor_idx` (`depositor_id`),
  KEY `netsuite_id_idx` (`netsuite_id`),
  KEY `deposited_idx` (`deposited`)
) ENGINE=InnoDB AUTO_INCREMENT=62650 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Queries

DROP temporary TABLE IF EXISTS `depositor_type`;

CREATE temporary TABLE `depositor_type` (
  `depositor_id` bigint(20) NOT NULL,
  `type` varchar(4) NOT NULL,
  amount decimal(20,4) NULL,
  count_deposit int(11) NULL,
  PRIMARY KEY (depositor_id, type),
  KEY type_idx (type),
  KEY amount_idx (amount)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into depositor_type (type, depositor_id )  select distinct 'u',user_rmn as depositor_id from transaction where txn_date between :from and :to and user_rmn is not null  union DISTINCT  select distinct 'a',ad_id as depositor_id from transaction where txn_date between :from and :to  and ad_id is not null union DISTINCT  select distinct 'm',md_id as depositor_id from transaction where txn_date between :from and :to  and md_id is not null union DISTINCT  select distinct 'c',cnf_id as depositor_id from transaction where txn_date between :from and :to  and cnf_id is not null;

update depositor_type set  amount=(select sum(amount) from depositor d where d.depositor_id=depositor_type.depositor_id),  count_deposit=(select count(amount) from depositor d where d.depositor_id=depositor_type.depositor_id) ;

DROP  TABLE IF EXISTS `9bf92fsums`;
CREATE TABLE `9bf92fsums` (  `cnf_id` bigint(20) NOT NULL,
                             `md_id` bigint(20) NOT NULL,
                             `ad_id` bigint(20) NOT NULL,
                             `user_rmn` bigint(20) NOT NULL,
                             `operator_id` int(11) not null,
                             `trans_amount` decimal(20,4) NOT NULL,
                             `incoming_commission` decimal(20,4) NOT NULL,
                             `retailer_commission` decimal(20,4) NOT NULL,
                             `ad_commission` decimal(20,4) NOT NULL,
                             `md_commission` decimal(20,4) NOT NULL,
                             `cnf_commission` decimal(20,4) NOT NULL,
                             `count_trans` int(11) not null,

  PRIMARY KEY (`cnf_id`,`md_id`,`ad_id`,`user_rmn`, `operator_id`),
  KEY `md_id_idx` (`cnf_id`),
  KEY `ad_id_idx` (`ad_id`) USING BTREE,
  KEY `user_rmn_idx` (`user_rmn`) USING BTREE,
  KEY `operator_id_idx` (`operator_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into 9bf92fsums select distinct coalesce(cnf_id,0), coalesce(md_id,0), coalesce(ad_id,0), coalesce(user_rmn,0), operator_id,  sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission,  sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), count(txn_id) as count_trans from transaction  where txn_date between :from and :to  group by coalesce(cnf_id,0), coalesce(md_id,0), coalesce(ad_id,0), coalesce(user_rmn,0), operator_id

select 'User' as type, user_rmn as phone, t.amount,  sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission,  sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), sum(count_deposit) as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s      inner join depositor_type t on s.user_rmn=t.depositor_id and t.type='u'      inner join operator o on s.operator_id=o.id      left join operator_category_high och on (o.category_high_id=och.id)     left join operator_category_medium ocm on (o.category_medium_id=ocm.id)     left join operator_category_low ocl on (o.category_low_id=ocl.id) where t.amount > 0  group by user_rmn, och.name, ocm.name, ocl.name

select 'AD' as type, ad_id as phone, t.amount,  sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission,  sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), sum(count_deposit) as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s      inner join depositor_type t on s.ad_id=t.depositor_id and t.type='a'      inner join operator o on s.operator_id=o.id      left join operator_category_high och on (o.category_high_id=och.id)     left join operator_category_medium ocm on (o.category_medium_id=ocm.id)     left join operator_category_low ocl on (o.category_low_id=ocl.id) where t.amount > 0  group by ad_id, och.name, ocm.name, ocl.name

select 'MD' as type, md_id as phone, t.amount,  sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission,  sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), sum(count_deposit) as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s      inner join depositor_type t on s.md_id=t.depositor_id and t.type='m'      inner join operator o on s.operator_id=o.id      left join operator_category_high och on (o.category_high_id=och.id)     left join operator_category_medium ocm on (o.category_medium_id=ocm.id)     left join operator_category_low ocl on (o.category_low_id=ocl.id) where t.amount > 0  group by md_id, och.name, ocm.name, ocl.name

select 'CNF' as type, cnf_id as phone, t.amount,  sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission,  sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), sum(count_deposit) as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s      inner join depositor_type t on s.cnf_id=t.depositor_id and t.type='c'      inner join operator o on s.operator_id=o.id      left join operator_category_high och on (o.category_high_id=och.id)     left join operator_category_medium ocm on (o.category_medium_id=ocm.id)     left join operator_category_low ocl on (o.category_low_id=ocl.id) where t.amount > 0  group by cnf_id, och.name, ocm.name, ocl.name

select distinct 'no deposits' as type, null as depositor_id, 0 as sum_dep_amount,  sum(trans_amount), sum(incoming_commission) as incoming_commission, sum(retailer_commission) as retailer_commission,  sum(ad_commission) as ad_commission, sum(md_commission) as md_commission, sum(cnf_commission), 0 as cnt_depositors, sum(count_trans) as count_trans, och.name as operator_category_high, ocm.name as operator_category_medium, ocl.name as operator_category_low from 9bf92fsums s      inner join operator o on s.operator_id=o.id      left join operator_category_high och on (o.category_high_id=och.id)     left join operator_category_medium ocm on (o.category_medium_id=ocm.id)     left join operator_category_low ocl on (o.category_low_id=ocl.id) group by 'no deposits', och.name, ocm.name, ocl.name

select distinct 'no transactions' as type, depositor_id, sum(amount) as sum_dep_amount, 0 as trans_amount,  0 as incoming_commission, 0 as retailer_commission, 0 as ad_commission, 0 as md_commission, 0 as cnf_commission, count(d.id) as cnt_depositors, 0 as cnt_txn_id,  'n/a' as operator_category_high, 'n/a' as operator_category_medium, 'n/a' as operator_category_low from depositor d where  depositor_id not in (select user_rmn from 9bf92fsums)  and depositor_id not in (select ad_id from 9bf92fsums)  and depositor_id not in (select md_id from 9bf92fsums)  and depositor_id not in (select cnf_id from 9bf92fsums) group by 'no transactions', depositor_id

Answer 1

Rather than address the question as asked, I will address some optimizations.

• Don't mix charsets -- I see latin1 and utf8. Charset conversion can prevent usage of indexes.
• Smaller leads to faster...
• Shrink datatypes where practical -- do you really need BIGINT (8 bytes)? The "operators" could probably do with TINYINT UNSIGNED (1 byte). Etc.
• decimal(20,4) -- nice commission! Up to 9999999999999999.9999; I can't think of any currency where that would not be bigger than all the money in the world. And it takes 10 bytes.

I presume you are augmenting, not rebuilding, the "Summary tables"? More discussion here. Proper use of Summary tables is the main way to get performance from "reports".

Time each statement -- perhaps one of them (the UPDATE with the subqueries?) is taking most of the time.

Answer 2

From your output:

LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 328780908334688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328780908336512, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328780908335600, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328780908333776, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 328780908332864, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

These 5 "idle" (note the quotes, the definition of an active transaction on InnoDB is obvious, you can run START TRANSACTION and you would not be in an active transaction until you run your first query) probably indicates one of 2: You must have gotten that output after the problem is gone OR the problem doesn't have to do with InnoDB transaction row writes/serialization. I would need to see SHOW PROCESSLIST to confirm the latter, however, based on your queries, I would suspect the issue is an old friend called metadata locking (which is only indirectly related to transaction processing).

Metadata locking issues (something that you can confirm by running SHOW PROCESSLIST, and getting a bunch of queries in "Waiting for Metadata Lock" state) happen when you execute a DDL-like statement (CREATE, DROP, etc.) and there are (long) running queries (even reads!). DDLs have to wait for all reads to finish to acquire the metadata locks, which in turn block all subsequent queries (SELECTs too).

These are strategies to mitigate these issues:

• Do not run DDLs except on low load (in particular, outside of long running transactions like analytics-like queries or backups).
• Do not drop or modify existing tables, do calculations on temporary (for your session-only) or process-exclusive tables
• Limit the amount of time a DDL can get blocked until the query fails. You can reduce that to, lets say, lock_wait_timeout from the default 1 year(!), to a few seconds. We do that for online schema changes at Wikipedia: https://phabricator.wikimedia.org/diffusion/OSOF/browse/master/dbtools/osc_host.sh;2cd94501c64236d871a19ceec6173d3f6c5ec197$132 The query will fail, but you can capture that and try 2 or 3 times before complaining (better than creating a horrible pileup of all queries using that table). This is a very common problem- we suffer from it on the revision table of the mediawiki installation for enwiki, and we plan to roll-in some kind of partitioning to mitigate that.

Later versions of MySQL started introducing Metadata Lock monitoring as part of performance_schema, but it was (and is, not every body has the latest version) a pain to debug in the past. Enable P_S, if you haven't already, its 1-4% overhead is worth the amount of time spent on debugging issues blindly.