Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM
Hi Aftab,
Aftab Khan Wrote:
-------------------------------------------------------
> please also provide Show Create table ... output
> for all tables involved in slow queries.
CREATE TABLE `genericitems` (
`hjmpTS` bigint(20) default NULL,
`OwnerPkString` bigint(20) default NULL,
`TypePkString` bigint(20) NOT NULL,
`createdTS` datetime NOT NULL,
`modifiedTS` datetime default NULL,
`aCLTS` bigint(20) default '0',
`PK` bigint(20) NOT NULL,
`propTS` bigint(20) default '0',
`p_validto` datetime default NULL,
`p_query` text,
`p_previewcatalogversion` bigint(20) default NULL,
`p_createdby` bigint(20) default NULL,
`p_invertedlogo` bigint(20) default NULL,
`p_params` longblob,
`p_paramtypes` text,
`p_resulttype` bigint(20) default NULL,
`p_logo` bigint(20) default NULL,
`p_code` varchar(255) default NULL,
`p_user` bigint(20) default NULL,
`p_validfrom` datetime default NULL,
`p_invertedlogosmall` bigint(20) default NULL,
`p_date` datetime default NULL,
`p_product` bigint(20) default NULL,
`p_image` bigint(20) default NULL,
`p_label` bigint(20) default NULL,
`p_price` double default NULL,
`p_ordercode` varchar(255) default NULL,
`p_processed` tinyint(1) default NULL,
`p_identnumber` varchar(255) default NULL,
`p_amount` double default NULL,
`p_change` int(11) default NULL,
`p_remark` varchar(255) default NULL,
`p_cart` bigint(20) default NULL,
`p_stockamount` int(11) default NULL,
`p_productdescription` varchar(255) default NULL,
PRIMARY KEY (`PK`),
KEY `owneridx_99` (`OwnerPkString`),
KEY `typepkindex_99` (`TypePkString`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `taxrows` (
`hjmpTS` bigint(20) default NULL,
`TypePkString` bigint(20) NOT NULL,
`modifiedTS` datetime default NULL,
`aCLTS` bigint(20) default '0',
`PK` bigint(20) NOT NULL,
`createdTS` datetime NOT NULL,
`OwnerPkString` bigint(20) default NULL,
`propTS` bigint(20) default '0',
`EndDate` datetime default NULL,
`StartDate` datetime default NULL,
`CG` bigint(20) default NULL,
`matchValue` int(11) default '0',
`UserPK` bigint(20) default NULL,
`ProductPK` bigint(20) default NULL,
`PG` bigint(20) default NULL,
`taxPK` bigint(20) default NULL,
`value` decimal(30,8) default NULL,
`absoluteFlag` tinyint(1) default '0',
PRIMARY KEY (`PK`),
KEY `matchindex_1054` (`UserPK`,`ProductPK`),
KEY `typepkindex_1054` (`TypePkString`),
KEY `owneridx_1054` (`OwnerPkString`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `taxes` (
`hjmpTS` bigint(20) default NULL,
`OwnerPkString` bigint(20) default NULL,
`TypePkString` bigint(20) NOT NULL,
`createdTS` datetime NOT NULL,
`modifiedTS` datetime default NULL,
`aCLTS` bigint(20) default '0',
`PK` bigint(20) NOT NULL,
`propTS` bigint(20) default '0',
`Value` double default '0',
`absolute` tinyint(1) default '0',
`Code` varchar(255) NOT NULL,
PRIMARY KEY (`PK`),
KEY `owneridx_47` (`OwnerPkString`),
KEY `typepkindex_47` (`TypePkString`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `enumerationvalues` (
`hjmpTS` bigint(20) default NULL,
`OwnerPkString` bigint(20) default NULL,
`TypePkString` bigint(20) NOT NULL,
`createdTS` datetime NOT NULL,
`modifiedTS` datetime default NULL,
`aCLTS` bigint(20) default '0',
`PK` bigint(20) NOT NULL,
`propTS` bigint(20) default '0',
`Editable` tinyint(1) default '0',
`codeLowerCase` varchar(255) NOT NULL,
`SequenceNumber` int(11) default '0',
`Code` varchar(255) NOT NULL,
`p_extensionname` varchar(255) default NULL,
PRIMARY KEY (`PK`),
KEY `owneridx_91` (`OwnerPkString`),
KEY `typepkindex_91` (`TypePkString`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `users` (
`hjmpTS` bigint(20) default NULL,
`OwnerPkString` bigint(20) default NULL,
`TypePkString` bigint(20) NOT NULL,
`createdTS` datetime NOT NULL,
`modifiedTS` datetime default NULL,
`aCLTS` bigint(20) default '0',
`PK` bigint(20) NOT NULL,
`propTS` bigint(20) default '0',
`UniqueID` varchar(255) NOT NULL,
`Name` varchar(255) default NULL,
`Description` varchar(255) default NULL,
`DefaultShippingAddress` bigint(20) default NULL,
`Passwd` varchar(255) default NULL,
`DefaultPaymentAddress` bigint(20) default NULL,
`Encode` varchar(255) default NULL,
`p_birthday` datetime default NULL,
`p_personalmessage` varchar(255) default NULL,
`p_newsletteremail` tinyint(1) default NULL,
`p_passwordanswer` varchar(255) default NULL,
`p_ldapaccount` tinyint(1) default NULL,
`p_sessionlanguage` bigint(20) default NULL,
`p_newsletterhtml` tinyint(1) default NULL,
`p_ldaplogin` varchar(255) default NULL,
`p_europe1pricefactory_utg` bigint(20) default NULL,
`p_newslettercategories` longblob,
`p_europe1pricefactory_upg` bigint(20) default NULL,
`p_branch` varchar(255) default NULL,
`p_recommendationmessage` varchar(255) default NULL,
`p_sessioncurrency` bigint(20) default NULL,
`p_passwordquestion` varchar(255) default NULL,
`p_europe1pricefactory_udg` bigint(20) default NULL,
`p_defaultpaymentinfo` bigint(20) default NULL,
`p_previewcatalogversions` text,
`p_lastlogin` datetime default NULL,
`p_customerid` varchar(255) default NULL,
`p_newslettersms` tinyint(1) default NULL,
`p_ldapsearchbase` varchar(255) default NULL,
`p_cn` text,
`p_failedlogins` int(11) default NULL,
`p_profession` varchar(255) default NULL,
`p_recommendationcounter` int(11) default NULL,
`p_invitedby` varchar(255) default NULL,
`p_hmclogindisabled` tinyint(1) default NULL,
`p_domain` varchar(255) default NULL,
`p_logindisabled` tinyint(1) default NULL,
`p_declarationsent` tinyint(1) default NULL,
`p_preferredviewtype` varchar(255) default NULL,
`p_eventmessages` text,
`p_vip` tinyint(1) default NULL,
`p_privacy` tinyint(1) default NULL,
PRIMARY KEY (`PK`),
UNIQUE KEY `uid_4` (`UniqueID`),
KEY `owneridx_4` (`OwnerPkString`),
KEY `typepkindex_4` (`TypePkString`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `medias` (
`hjmpTS` bigint(20) default NULL,
`OwnerPkString` bigint(20) default NULL,
`TypePkString` bigint(20) NOT NULL,
`createdTS` datetime NOT NULL,
`modifiedTS` datetime default NULL,
`aCLTS` bigint(20) default '0',
`PK` bigint(20) NOT NULL,
`propTS` bigint(20) default '0',
`RealFileName` varchar(255) default NULL,
`Mime` varchar(255) default NULL,
`url` varchar(255) default NULL,
`Code` varchar(255) NOT NULL,
`p_inputmimetype` varchar(255) default NULL,
`p_description` varchar(255) default NULL,
`p_sourceitem` bigint(20) default NULL,
`p_catalog` bigint(20) default NULL,
`p_encoding` bigint(20) default NULL,
`p_alttext` varchar(255) default NULL,
`p_fieldseparator` smallint(6) default NULL,
`p_zipentry` varchar(255) default NULL,
`p_commentcharacter` smallint(6) default NULL,
`p_linestoskip` int(11) default NULL,
`p_size` bigint(20) default NULL,
`p_format` bigint(20) default NULL,
`p_catalogversion` bigint(20) default NULL,
`p_quotecharacter` smallint(6) default NULL,
`p_itemtimestamp` datetime default NULL,
`p_removeonsuccess` tinyint(1) default NULL,
`p_outputmimetype` varchar(255) default NULL,
`p_removable` tinyint(1) default NULL,
PRIMARY KEY (`PK`),
KEY `owneridx_30` (`OwnerPkString`),
KEY `typepkindex_30` (`TypePkString`),
KEY `media_code_30` (`Code`),
KEY `versionIDX_30` (`p_catalogversion`),
KEY `codeVersionIDX_30` (`Code`,`p_catalogversion`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `pricerows` (
`hjmpTS` bigint(20) default NULL,
`TypePkString` bigint(20) NOT NULL,
`modifiedTS` datetime default NULL,
`aCLTS` bigint(20) default '0',
`PK` bigint(20) NOT NULL,
`createdTS` datetime NOT NULL,
`OwnerPkString` bigint(20) default NULL,
`propTS` bigint(20) default '0',
`EndDate` datetime default NULL,
`StartDate` datetime default NULL,
`CG` bigint(20) default NULL,
`matchValue` int(11) default '0',
`UserPK` bigint(20) default NULL,
`ProductPK` bigint(20) default NULL,
`PG` bigint(20) default NULL,
`currencyPK` bigint(20) default NULL,
`unitPK` bigint(20) default NULL,
`minQtd` bigint(20) default '0',
`unitFactor` int(11) default '0',
`price` double default '0',
`netFlag` tinyint(1) default '0',
`p_giveawayprice` tinyint(1) default NULL,
PRIMARY KEY (`PK`),
KEY `typepkindex_1055` (`TypePkString`),
KEY `matchindex_1055` (`UserPK`,`ProductPK`),
KEY `owneridx_1055` (`OwnerPkString`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `products` (
`hjmpTS` bigint(20) default NULL,
`OwnerPkString` bigint(20) default NULL,
`TypePkString` bigint(20) NOT NULL,
`createdTS` datetime NOT NULL,
`modifiedTS` datetime default NULL,
`aCLTS` bigint(20) default '0',
`PK` bigint(20) NOT NULL,
`propTS` bigint(20) default '0',
`UnitPK` bigint(20) default NULL,
`Code` varchar(255) NOT NULL,
`p_order` int(11) default NULL,
`p_erpgroupbuyer` varchar(255) default NULL,
`p_identno` varchar(255) default NULL,
`p_detail` text,
`p_storageposition` varchar(255) default NULL,
`p_approvalstatus` bigint(20) default NULL,
`p_catalogversion` bigint(20) default NULL,
`p_others` text,
`p_ean` varchar(255) default NULL,
`p_logo` text,
`p_picture` bigint(20) default NULL,
`p_onlinedate` datetime default NULL,
`p_catalog` bigint(20) default NULL,
`p_manufacturername` varchar(255) default NULL,
`p_offlinedate` datetime default NULL,
`p_label` bigint(20) default NULL,
`p_specialtreatmentclasses` longblob,
`p_thumbnails` text,
`p_smalldetails` text,
`p_numbercontentunits` double default NULL,
`p_orderquantityinterval` int(11) default NULL,
`p_colorcode` varchar(255) default NULL,
`p_stock` int(11) default NULL,
`p_catwalk` bigint(20) default NULL,
`p_pricequantity` double default NULL,
`p_endlinenumber` int(11) default NULL,
`p_deliverytime` double default NULL,
`p_varianttype` bigint(20) default NULL,
`p_smallpicture` bigint(20) default NULL,
`p_barcodelabel` bigint(20) default NULL,
`p_size` varchar(255) default NULL,
`p_buyerids` longblob,
`p_europe1pricefactory_pdg` bigint(20) default NULL,
`p_contentunit` bigint(20) default NULL,
`p_erpgroupsupplier` varchar(255) default NULL,
`p_largedetails` text,
`p_largepicture` bigint(20) default NULL,
`p_normal` text,
`p_women` tinyint(1) default NULL,
`p_men` tinyint(1) default NULL,
`p_supplieralternativeaid` varchar(255) default NULL,
`p_manufactureraid` varchar(255) default NULL,
`p_minorderquantity` int(11) default NULL,
`p_europe1pricefactory_ptg` bigint(20) default NULL,
`p_maxorderquantity` int(11) default NULL,
`p_recommendedretailprice` double default NULL,
`p_data_sheet` text,
`p_thumbnail` bigint(20) default NULL,
`p_startlinenumber` int(11) default NULL,
`p_europe1pricefactory_ppg` bigint(20) default NULL,
`p_navisionarticleno` varchar(255) default NULL,
`p_mediumpicture` bigint(20) default NULL,
`p_baseproduct` bigint(20) default NULL,
`p_recreatelabel` int(11) default NULL,
`p_approvaldate` datetime default NULL,
`p_disablepriceupdates` tinyint(1) default NULL,
`p_babies` tinyint(1) default NULL,
`p_comments` text,
`p_boys` tinyint(1) default NULL,
`p_girls` tinyint(1) default NULL,
`p_xxldetails` text,
`p_tip` bigint(20) default NULL,
`p_gallery` bigint(20) default NULL,
`p_shoppingcart` bigint(20) default NULL,
`p_colorname` varchar(255) default NULL,
`p_mygolf` bigint(20) default NULL,
PRIMARY KEY (`PK`),
KEY `owneridx_1` (`OwnerPkString`),
KEY `product_code_1` (`Code`),
KEY `typepkindex_1` (`TypePkString`),
KEY `baseIDX_1` (`p_baseproduct`),
KEY `catalogIDX_1` (`p_catalog`),
KEY `versionIDX_1` (`p_catalogversion`),
KEY `visibilityIDX_1` (`p_approvalstatus`,`p_onlinedate`,`p_offlinedate`),
KEY `codeVersionIDX_1` (`Code`,`p_catalogversion`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `categories` (
`hjmpTS` bigint(20) default NULL,
`OwnerPkString` bigint(20) default NULL,
`modifiedTS` datetime default NULL,
`aCLTS` bigint(20) default '0',
`TypePkString` bigint(20) NOT NULL,
`PK` bigint(20) NOT NULL,
`createdTS` datetime NOT NULL,
`propTS` bigint(20) default '0',
`p_order` int(11) default NULL,
`p_catalog` bigint(20) default NULL,
`p_showemptyattributes` tinyint(1) default NULL,
`p_normal` text,
`p_revision` varchar(255) default NULL,
`p_women` tinyint(1) default NULL,
`p_detail` text,
`p_men` tinyint(1) default NULL,
`p_code` varchar(255) default NULL,
`p_externalid` varchar(255) default NULL,
`p_data_sheet` text,
`p_thumbnails` text,
`p_thumbnail` bigint(20) default NULL,
`p_catalogversion` bigint(20) default NULL,
`p_others` text,
`p_logo` text,
`p_picture` bigint(20) default NULL,
`p_babies` tinyint(1) default NULL,
`p_boys` tinyint(1) default NULL,
`p_girls` tinyint(1) default NULL,
`p_image_women` bigint(20) default NULL,
`p_headline_women` varchar(255) default NULL,
`p_headline_men` varchar(255) default NULL,
`p_image_men` bigint(20) default NULL,
`p_description_women` text,
`p_description_men` text,
PRIMARY KEY (`PK`),
KEY `typepkindex_142` (`TypePkString`),
KEY `owneridx_142` (`OwnerPkString`),
KEY `versionIDX_142` (`p_catalogversion`),
KEY `codeIDX_142` (`p_code`),
KEY `codeVersionIDX_142` (`p_code`,`p_catalogversion`),
KEY `extID_142` (`p_externalid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `cat2prodrel` (
`hjmpTS` bigint(20) default NULL,
`OwnerPkString` bigint(20) default NULL,
`modifiedTS` datetime default NULL,
`aCLTS` bigint(20) default '0',
`TypePkString` bigint(20) NOT NULL,
`PK` bigint(20) NOT NULL,
`createdTS` datetime NOT NULL,
`propTS` bigint(20) default '0',
`Qualifier` varchar(255) NOT NULL,
`LanguagePK` bigint(20) default NULL,
`TargetPK` bigint(20) NOT NULL,
`SequenceNumber` int(11) default '0',
`SourcePK` bigint(20) NOT NULL,
`RSequenceNumber` int(11) default '0',
PRIMARY KEY (`PK`),
KEY `linktarget_143` (`TargetPK`),
KEY `typepkindex_143` (`TypePkString`),
KEY `linksource_143` (`SourcePK`),
KEY `qualifier_143` (`Qualifier`),
KEY `owneridx_143` (`OwnerPkString`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `cat2princrel` (
`hjmpTS` bigint(20) default NULL,
`OwnerPkString` bigint(20) default NULL,
`createdTS` datetime NOT NULL,
`aCLTS` bigint(20) default '0',
`modifiedTS` datetime default NULL,
`TypePkString` bigint(20) NOT NULL,
`PK` bigint(20) NOT NULL,
`propTS` bigint(20) default '0',
`SequenceNumber` int(11) default '0',
`TargetPK` bigint(20) NOT NULL,
`Qualifier` varchar(255) NOT NULL,
`RSequenceNumber` int(11) default '0',
`SourcePK` bigint(20) NOT NULL,
`LanguagePK` bigint(20) default NULL,
PRIMARY KEY (`PK`),
KEY `typepkindex_613` (`TypePkString`),
KEY `linktarget_613` (`TargetPK`),
KEY `qualifier_613` (`Qualifier`),
KEY `owneridx_613` (`OwnerPkString`),
KEY `linksource_613` (`SourcePK`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> we will also like to see
>
> SHOW INDEX FROM genericitems;
mysql> SHOW INDEX FROM genericitems;
+--------------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| genericitems | 0 | PRIMARY | 1 | PK | A | 567049 | NULL | NULL | | BTREE | |
| genericitems | 1 | owneridx_99 | 1 | OwnerPkString | A | 15 | NULL | NULL | YES | BTREE | |
| genericitems | 1 | typepkindex_99 | 1 | TypePkString | A | 15 | NULL | NULL | | BTREE | |
+--------------+------------+----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
>
> Try to Use UNION where you have OR in where for
> the same column
>
http://dev.mysql.com/doc/refman/5.0/en/union.html
>
I'll pass this on to the programmers and see if they can change the query.
> And please report back if you see any imporvment
Of course :)
Regards,
--Tim