MySQL Forums
Forum List  »  Performance

Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM
Posted by: Timothy Karl
Date: March 20, 2009 07:25AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM
3604
March 20, 2009 07:25AM


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.