MySQL Forums
Forum List  »  InnoDB

Re: Deadlock Detection in InnoDB storage engine
Posted by: Manjunath C
Date: July 31, 2012 12:00AM

Hi,

Thanks for providing inputs.
We are working on getting the sequence number outside transaction.
We also created the unique on NAME and PARTY_ROLE_ID column of OB_SEQUENCE_INFO table.
ALTER TABLE OB_SEQUENCE_INFO ADD UNIQUE KEY IDX_SEQUENCE_INFO_NAME_PRTY_RL_ID (NAME,PARTY_ROLE_ID);
With this change so for we have not seen the deadlock.

We were testing the durability test and hit with bottleneck with the below mentioned query which is increasing linearly with amount of records inserted. We tried to insert more than 65K odd records and the query is taking 16+ seconds, which is logged in the slow query log.
Here are the details of the same:-

EXPLAIN select
this_.ID as ID15_6_,
this_.CREATED_DATE as CREATED2_15_6_,
this_.CREATED_BY as CREATED3_15_6_,
this_.LAST_MODIFIED_DATE as LAST4_15_6_,
this_.LAST_MODIFIED_BY as LAST5_15_6_,
this_.ACC_NUMBER as ACC6_15_6_,
this_.PARENT_ID as PARENT7_15_6_,
this_.ACTIVATION_START_DATE as ACTIVATION8_15_6_,
this_.STATUS as STATUS15_6_,
this_.TAX_EXEMPTED as TAX10_15_6_,
this_.NOTES as NOTES15_6_,
this_.PAYMENT_PROFILE_ID as PAYMENT12_15_6_,
this_.SPONSORED as SPONSORED15_6_,
this_.BUYER_ID as BUYER14_15_6_,
this_.SELLER_ID as SELLER15_15_6_,
accountbal6_.ACC_ID as ACC2_15_8_,
accountbal6_.ID as ID8_,
accountbal6_.ID as ID10_0_,
accountbal6_.ACC_ID as ACC2_10_0_,
accountbal6_.BILLING_PROFILE as BILLING3_10_0_,
accountbal6_.CREATED_DATE as CREATED4_10_0_,
accountbal6_.LAST_MODIFIED_DATE as LAST5_10_0_,
accountbal6_.CREATED_BY as CREATED6_10_0_,
accountbal6_.LAST_MODIFIED_BY as LAST7_10_0_,
obbillinga5_.ACC_ID as ACC2_15_9_,
obbillinga5_.ID as ID9_,
obbillinga5_.ID as ID16_1_,
obbillinga5_.ACC_ID as ACC2_16_1_,
obbillinga5_.CREATED_DATE as CREATED3_16_1_,
obbillinga5_.CREATED_BY as CREATED4_16_1_,
obbillinga5_.LAST_MODIFIED_DATE as LAST5_16_1_,
obbillinga5_.LAST_MODIFIED_BY as LAST6_16_1_,
obbillinga5_.IMAGE as IMAGE16_1_,
obbillinga5_.DESCRIPTION as DESCRIPT8_16_1_,
obpartyrol1_.ID as ID97_2_,
obpartyrol1_.PARTY_ID as PARTY2_97_2_,
obpartyrol1_.CREATED_DATE as CREATED3_97_2_,
obpartyrol1_.CREATED_BY as CREATED4_97_2_,
obpartyrol1_.LAST_MODIFIED_DATE as LAST5_97_2_,
obpartyrol1_.LAST_MODIFIED_BY as LAST6_97_2_,
obpartyrol1_.TYPE as TYPE97_2_,
obpartyrol1_.PRIMARY_CURRENCY_ID as PRIMARY8_97_2_,
obpartyrol1_.SECONDARY_CURRENCY_ID as SECONDARY9_97_2_,
obparty2_.ID as ID92_3_,
obparty2_.CREATED_DATE as CREATED2_92_3_,
obparty2_.CREATED_BY as CREATED3_92_3_,
obparty2_.LAST_MODIFIED_DATE as LAST4_92_3_,
obparty2_.LAST_MODIFIED_BY as LAST5_92_3_,
obparty2_.NAME as NAME92_3_,
obparty2_.PARTY_NUMBER as PARTY7_92_3_,
obparty2_.TYPE as TYPE92_3_,
obparty2_.VALID_FROM as VALID9_92_3_,
obparty2_.VALID_TO as VALID10_92_3_,
obpartyadd3_.PARTY_ID as PARTY2_92_10_,
obpartyadd3_.ID as ID10_,
obpartyadd3_.ID as ID93_4_,
obpartyadd3_.PARTY_ID as PARTY2_93_4_,
obpartyadd3_.CREATED_DATE as CREATED3_93_4_,
obpartyadd3_.CREATED_BY as CREATED4_93_4_,
obpartyadd3_.LAST_MODIFIED_DATE as LAST5_93_4_,
obpartyadd3_.LAST_MODIFIED_BY as LAST6_93_4_,
obpartyadd3_.ADD_LINE1 as ADD7_93_4_,
obpartyadd3_.ADD_LINE2 as ADD8_93_4_,
obpartyadd3_.ADD_LINE3 as ADD9_93_4_,
obpartyadd3_.COUNTY as COUNTY93_4_,
obpartyadd3_.CITY as CITY93_4_,
obpartyadd3_.STATE as STATE93_4_,
obpartyadd3_.COUNTRY as COUNTRY93_4_,
obpartyadd3_.ZIP as ZIP93_4_,
obpartyadd3_.ADD_TYPE as ADD15_93_4_,
obpartycon4_.PARTY_ID as PARTY2_92_11_,
obpartycon4_.ID as ID11_,
obpartycon4_.ID as ID95_5_,
obpartycon4_.PARTY_ID as PARTY2_95_5_,
obpartycon4_.CREATED_DATE as CREATED3_95_5_,
obpartycon4_.CREATED_BY as CREATED4_95_5_,
obpartycon4_.LAST_MODIFIED_DATE as LAST5_95_5_,
obpartycon4_.LAST_MODIFIED_BY as LAST6_95_5_,
obpartycon4_.SALUTATION as SALUTATION95_5_,
obpartycon4_.FIRST_NAME as FIRST8_95_5_,
obpartycon4_.MIDDLE_NAME as MIDDLE9_95_5_,
obpartycon4_.LAST_NAME as LAST10_95_5_,
obpartycon4_.CONTACT_TYPE as CONTACT11_95_5_
from OB_BILLING_ACCOUNT this_
left outer join OB_BALANCE accountbal6_ on this_.ID=accountbal6_.ACC_ID
left outer join OB_BILLING_ACC_IMAGE obbillinga5_ on this_.ID=obbillinga5_.ACC_ID
left outer join OB_PARTY_ROLE obpartyrol1_ on this_.BUYER_ID=obpartyrol1_.ID
left outer join OB_PARTY obparty2_ on obpartyrol1_.PARTY_ID=obparty2_.ID
left outer join OB_PARTY_ADDRESS obpartyadd3_ on obparty2_.ID=obpartyadd3_.PARTY_ID
left outer join OB_PARTY_CONTACT obpartycon4_ on obparty2_.ID=obpartycon4_.PARTY_ID
where this_.STATUS=1001 and
this_.SELLER_ID='21ce44c2d23a11e1a3bf68b599c22bdc'
order by obparty2_.NAME
asc limit 30\G

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: this_
type: ref
possible_keys: IDX_BILLING_ACCOUNT_STATUS,IDX_BILLING_ACCOUNT_SELLER_ID
key: IDX_BILLING_ACCOUNT_SELLER_ID
key_len: 99
ref: const
rows: 1
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: accountbal6_
type: ref
possible_keys: IDX_OB_BALANCE_ACC_ID
key: IDX_OB_BALANCE_ACC_ID
key_len: 98
ref: obcore2.this_.ID
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: obbillinga5_
type: ref
possible_keys: IDX_OB_BLLNG_ACC_IMG_ACC_ID
key: IDX_OB_BLLNG_ACC_IMG_ACC_ID
key_len: 99
ref: obcore2.this_.ID
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: obpartyrol1_
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 98
ref: obcore2.this_.BUYER_ID
rows: 1
Extra:
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: obparty2_
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 98
ref: obcore2.obpartyrol1_.PARTY_ID
rows: 1
Extra:
*************************** 6. row ***************************
id: 1
select_type: SIMPLE
table: obpartyadd3_
type: ref
possible_keys: IDX_OB_PARTY_ADDRESS_ACC_ID
key: IDX_OB_PARTY_ADDRESS_ACC_ID
key_len: 98
ref: obcore2.obparty2_.ID
rows: 1
Extra:
*************************** 7. row ***************************
id: 1
select_type: SIMPLE
table: obpartycon4_
type: ref
possible_keys: IDX_OB_PRTY_CNTCT_PRTY_ID
key: IDX_OB_PRTY_CNTCT_PRTY_ID
key_len: 98
ref: obcore2.obparty2_.ID
rows: 1
Extra:
7 rows in set (0.00 sec)

+++++++++++++++++++++++++++++++++


CREATE TABLE `OB_BILLING_ACCOUNT` (
`CREATED_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`CREATED_BY` varchar(32) NOT NULL,
`LAST_MODIFIED_DATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`LAST_MODIFIED_BY` varchar(32) NOT NULL,
`ID` varchar(32) NOT NULL,
`BUYER_ID` varchar(32) DEFAULT NULL COMMENT 'Source party role id.',
`SELLER_ID` varchar(32) DEFAULT NULL COMMENT 'Seller party role id.',
`ACC_NUMBER` varchar(32) DEFAULT NULL COMMENT 'Specifies',
`PARENT_ID` varchar(32) DEFAULT NULL,
`PAYMENT_PROFILE_ID` varchar(32) DEFAULT NULL,
`ACTIVATION_START_DATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`STATUS` int(11) DEFAULT NULL,
`TAX_EXEMPTED` char(1) DEFAULT NULL,
`NOTES` longtext,
`SPONSORED` char(1) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ACC_NUMBER_UC` (`ACC_NUMBER`),
KEY `IDX_BILLING_ACCOUNT_STATUS` (`STATUS`),
KEY `IDX_BILLING_ACCOUNT_SELLER_ID` (`SELLER_ID`),
KEY `IDX_BILLING_ACCOUNT_BUYER_ID` (`BUYER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

CREATE TABLE `OB_BALANCE` (
`CREATED_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`CREATED_BY` varchar(32) NOT NULL,
`LAST_MODIFIED_DATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`LAST_MODIFIED_BY` varchar(32) NOT NULL,
`ID` varchar(32) NOT NULL,
`ACC_ID` varchar(32) NOT NULL,
`BILLING_PROFILE` varchar(32) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `IDX_OB_BALANCE_ACC_ID` (`ACC_ID`),
CONSTRAINT `BALANCE_ACCOUNT_FK` FOREIGN KEY (`ACC_ID`) REFERENCES `OB_BILLING_ACCOUNT` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

CREATE TABLE `OB_BILLING_ACC_IMAGE` (
`CREATED_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`CREATED_BY` varchar(32) NOT NULL,
`LAST_MODIFIED_DATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`LAST_MODIFIED_BY` varchar(32) NOT NULL,
`ID` varchar(32) NOT NULL,
`IMAGE` mediumblob,
`ACC_ID` varchar(32) DEFAULT NULL,
`DESCRIPTION` varchar(256) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `IDX_OB_BLLNG_ACC_IMG_ACC_ID` (`ACC_ID`),
CONSTRAINT `OB_BILLING_ACC_IMAGE_FK` FOREIGN KEY (`ACC_ID`) REFERENCES `OB_BILLING_ACCOUNT` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `OB_PARTY_ROLE` (
`CREATED_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`CREATED_BY` varchar(32) NOT NULL,
`LAST_MODIFIED_DATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`LAST_MODIFIED_BY` varchar(32) NOT NULL,
`ID` varchar(32) NOT NULL,
`PARTY_ID` varchar(32) NOT NULL,
`TYPE` int(11) DEFAULT NULL COMMENT 'Refers to Customer',
`PRIMARY_CURRENCY_ID` varchar(32) DEFAULT NULL,
`SECONDARY_CURRENCY_ID` varchar(32) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `OB_PARTY_ROLE_FK` (`PARTY_ID`),
KEY `IDX_OB_PRTY_RL_PRMRY_CRRNCY_ID` (`PRIMARY_CURRENCY_ID`),
CONSTRAINT `OB_PARTY_ROLE_FK` FOREIGN KEY (`PARTY_ID`) REFERENCES `OB_PARTY` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `OB_PARTY` (
`CREATED_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`CREATED_BY` varchar(32) NOT NULL,
`LAST_MODIFIED_DATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`LAST_MODIFIED_BY` varchar(32) NOT NULL,
`ID` varchar(32) NOT NULL,
`NAME` varchar(255) DEFAULT NULL,
`PARTY_NUMBER` varchar(32) DEFAULT NULL,
`TYPE` int(11) DEFAULT NULL COMMENT 'References OB_PARTY_TYPE.ID. Specifies the type of the party. Could be Individual or Business.',
`VALID_FROM` timestamp NULL DEFAULT NULL,
`VALID_TO` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `IDX_OB_PARTY_NAME` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `OB_PARTY_ADDRESS` (
`CREATED_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`CREATED_BY` varchar(32) NOT NULL,
`LAST_MODIFIED_DATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`LAST_MODIFIED_BY` varchar(32) NOT NULL,
`ID` varchar(32) NOT NULL,
`PARTY_ID` varchar(32) NOT NULL,
`ADD_LINE1` varchar(256) DEFAULT NULL,
`ADD_LINE2` varchar(256) DEFAULT NULL,
`ADD_LINE3` varchar(256) DEFAULT NULL,
`COUNTY` varchar(32) DEFAULT NULL,
`CITY` varchar(32) DEFAULT NULL,
`STATE` varchar(32) DEFAULT NULL,
`COUNTRY` varchar(32) DEFAULT NULL,
`ZIP` varchar(32) DEFAULT NULL,
`ADD_TYPE` varchar(256) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `IDX_OB_PARTY_ADDRESS_ACC_ID` (`PARTY_ID`),
KEY `IDX_OB_PRTY_ADDRSS_UPR_ADD_TYP` (`ADD_TYPE`(255)),
CONSTRAINT `OB_PARTY_ADDRESS_FK` FOREIGN KEY (`PARTY_ID`) REFERENCES `OB_PARTY` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `OB_PARTY_CONTACT` (
`CREATED_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`CREATED_BY` varchar(32) NOT NULL,
`LAST_MODIFIED_DATE` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`LAST_MODIFIED_BY` varchar(32) NOT NULL,
`ID` varchar(32) NOT NULL,
`PARTY_ID` varchar(32) NOT NULL,
`SALUTATION` varchar(32) DEFAULT NULL,
`FIRST_NAME` varchar(64) DEFAULT NULL,
`MIDDLE_NAME` varchar(64) DEFAULT NULL,
`LAST_NAME` varchar(64) DEFAULT NULL,
`CONTACT_TYPE` varchar(32) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `IDX_OB_PRTY_CNTCT_PRTY_ID` (`PARTY_ID`),
KEY `IDX_OB_PRTY_CNTCT_UPR_CNT_TYP` (`CONTACT_TYPE`),
CONSTRAINT `OB_PARTY_CONTACT_FK` FOREIGN KEY (`PARTY_ID`) REFERENCES `OB_PARTY` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SHOW TABLE STATUS;
++++++++++++++++++++++++
Name,Engine,Version,Row_format,Rows,Avg_row_length,Data_length,Max_data_length,Index_length,Data_free,Auto_increment,Collation
OB_BALANCE,InnoDB,10,Compact,50727,218,11075584,0,5816320,2749366272,NULL,utf8_general_ci
OB_BILLING_ACCOUNT,InnoDB,10,Compact,66394,246,16334848,0,21102592,2749366272,NULL,utf8_general_ci
OB_BILLING_ACC_IMAGE,InnoDB,10,Compact,0,0,16384,0,16384,2749366272,NULL,utf8_general_ci
OB_PARTY,InnoDB,10,Compact,66979,181,12124160,0,5799936,2749366272,NULL,utf8_general_ci
OB_PARTY_ADDRESS,InnoDB,10,Compact,66394,246,16334848,0,10584064,2749366272,NULL,utf8_general_ci
OB_PARTY_CONTACT,InnoDB,10,Compact,66392,214,14221312,0,9502720,2749366272,NULL,utf8_general_ci
OB_PARTY_ROLE,InnoDB,10,Compact,66403,198,13172736,0,9502720,2749366272,NULL,utf8_general_ci

We are currently using ID column as primary key in all the table which is storing GUID of 32 characters. We are also exploring further to change this to using AUTO_INCREMENT feature provided by mysql.

Please let us know is it good practice to use INT as primary key few tables and BIG INT as primary key for other set of tables in a single database with sequencing supported by AUTO_INCREMENT feature.

We also worked on table sizing and found that using INTEGERS as primary key was drastically reducing the table size.

Please provide your valuable inputs with which we can re-write or improve the performance of the above query.



Edited 1 time(s). Last edit at 07/31/2012 12:23AM by Manjunath C.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Deadlock Detection in InnoDB storage engine
1451
July 31, 2012 12:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.