Too much time in Copy to Tmp Table
Posted by: Ricardo Manfrinato
Date: October 01, 2015 11:55AM
Date: October 01, 2015 11:55AM
I am having several occurrences of this query in 'Copying to tmp table' State and a High CPU Load.
System: RHEL 6 - MEM 48GB RAM - CPU 12 cores
Query in question
MySQL Configuration
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
character-set-server = utf8
collation-server = utf8_bin
default-storage-engine = INNODB
event-scheduler = 1
expire_logs_days=4
general_log=1
innodb_additional_mem_pool_size=40M
innodb_buffer_pool_size = 36G
innodb_buffer_pool_instances = 2
innodb_data_file_path = ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity=2000
innodb_lock_wait_timeout = 50
innodb_log_buffer_size = 16M
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_read_io_threads=64
innodb_use_native_aio=1
innodb_file_per_table=1
innodb_write_io_threads=64
innodb_thread_concurrency=0
innodb_print_all_deadlocks=1
key_buffer_size = 256M
log_bin=/var/log/mysql/mysql-bin.log
log-output=TABLE
long_query_time = 60
lower-case-table-names = 1
max_allowed_packet = 32M
max_connections = 1600
net_buffer_length = 2K
query_cache_limit = 32M
query_cache_size = 128M
query_cache_type = 1
read_buffer_size = 256K
read_rnd_buffer_size = 256K
slow-query-log = 1
sort_buffer_size = 64K
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
table_open_cache = 175000
tmpdir=/tmp/mysqltmp/
tmp_table_size=2G
max_heap_table_size=2G
thread_cache_size = 8
thread_stack = 256k
wait_timeout=180
server-id = 1
binlog_format=mixed
I am using a 3GB tempfs for RAM Disk
Any thoughts or suggestions?
Edited 1 time(s). Last edit at 10/01/2015 10:47PM by Peter Brawley.
System: RHEL 6 - MEM 48GB RAM - CPU 12 cores
Query in question
SELECT
COUNT(*) AS y0_
FROM
Supplier this_
WHERE
this_.SupplierSN IN (SELECT DISTINCT
supplierin1_.SupplierSN AS y0_
FROM
SupplierProvisioning this_
INNER JOIN
Supplier supplierin1_ ON this_.SupplierSN = supplierin1_.SupplierSN
INNER JOIN
CatalogProvisioning supplierin5_ ON supplierin1_.SupplierSN = supplierin5_.SupplierSN
INNER JOIN
ProductCatalog supplierin6_ ON supplierin5_.ProductCatalogSN = supplierin6_.ProductCatalogSN
INNER JOIN
CatalogUsage supplierin7_ ON supplierin6_.ProductCatalogSN = supplierin7_.ProductCatalogSN
INNER JOIN
ContractProvisioning supplierin2_ ON supplierin1_.SupplierSN = supplierin2_.SupplierSN
INNER JOIN
Contract supplierin3_ ON supplierin2_.ContractSN = supplierin3_.ContractSN
INNER JOIN
ContractUsage supplierin4_ ON supplierin3_.ContractSN = supplierin4_.ContractSN
WHERE
this_.CustomerSN = supplierin4_.CustomerSN
AND this_.CustomerSN = supplierin7_.CustomerSN
AND this_.CustomerSN IN (1)
AND (supplierin3_.ContractSN IN (SELECT DISTINCT
userentitl1_.ContractSN AS y0_
FROM
Entitlement this_
INNER JOIN
Contract userentitl1_ ON this_.EntitledContract = userentitl1_.ContractSN
WHERE
this_.RefererType = 'Contract'
AND ((this_.UserID = 'xprs128@v25db114.mkm.can.com'
AND this_.CountryID IS NULL)
OR this_.UserID IS NULL)
AND (this_.CountryID = 'US'
OR this_.CountryID IS NULL)
AND (this_.Company = '0147'
OR this_.Company IS NULL)
AND (this_.WorkLocation = '1501'
OR this_.WorkLocation IS NULL)
AND (this_.BusinessUnit = 'BU00'
OR this_.BusinessUnit IS NULL)
AND (this_.CostCenter = 'C6YC'
OR this_.CostCenter IS NULL)
AND this_.PurchaseType IS NULL
AND this_.ProjectCode IS NULL
AND this_.CustomerID IS NULL
AND this_.SupplierID IS NULL
AND this_.ClassificationID IS NULL
AND this_.ClassificationGroupID IS NULL
AND this_.CatalogID IS NULL
AND this_.ProductID IS NULL
AND this_.ContractId IS NULL)
AND supplierin3_.ContractSN IN (SELECT DISTINCT
userpurcha1_.ContractSN AS y0_
FROM
Entitlement this_
INNER JOIN
Contract userpurcha1_ ON this_.EntitledContract = userpurcha1_.ContractSN
WHERE
this_.RefererType = 'Contract'
AND (this_.PurchaseType = 'Internal'
OR this_.PurchaseType = 'InternalOnly')))
AND ((supplierin3_.ValidFrom IS NULL
OR supplierin3_.ValidFrom < '2015-10-01 15:57:49')
AND (supplierin3_.ValidTo IS NULL
OR supplierin3_.ValidTo > '2015-10-01 15:57:49')))
Query Explain
id select_type table type possible_keys key key_len ref rows filtered Extra
--------------------------------------------------------------------------------------------------------------------------------------------------------------
1 PRIMARY this_ index NULL FK000000000000270 9 NULL 19122 100 Using where; Using index
2 DEPENDENT SUBQUERY this_ eq_ref UK000000000000060,FK000000000000287,FK000000000000289 UK000000000000060 16 const,func 1 100 Using index; Using temporary
2 DEPENDENT SUBQUERY supplierin1_ eq_ref PRIMARY PRIMARY 8 func 1 100 Using where; Using index
2 DEPENDENT SUBQUERY supplierin5_ ref UK000000000000009,FK000000000000032,FK000000000000031 FK000000000000031 8 func 2 100 Using where; Distinct
2 DEPENDENT SUBQUERY supplierin7_ ref UK000000000000010,FK000000000000033,FK000000000000034 UK000000000000010 17 test.supplierin5_.ProductCatalogSN,
const 1 100 Using where; Using index; Distinct
2 DEPENDENT SUBQUERY supplierin6_ eq_ref PRIMARY PRIMARY 8 test.supplierin7_.ProductCatalogSN 1 100 Using where; Using index; Distinct
2 DEPENDENT SUBQUERY supplierin2_ ref UK000000000000021,FK000000000000081,FK000000000000080 FK000000000000080 8 func 2 100 Using where; Distinct
2 DEPENDENT SUBQUERY supplierin4_ ref UK000000000000022,FK000000000000082,FK000000000000083 UK000000000000022 17 test.supplierin2_.ContractSN,
const 1 100 Using where; Using index; Distinct
2 DEPENDENT SUBQUERY supplierin3_ eq_ref PRIMARY PRIMARY 8 test.supplierin4_.ContractSN
1 100 Using where; Distinct
4 DEPENDENT SUBQUERY userpurcha1_ eq_ref PRIMARY PRIMARY 8 func 1 100 Using index; Using temporary
4 DEPENDENT SUBQUERY this_ ref FK000000000001184,Entitlement_idx_customer, FK000000000001184 9 func 2 100 Using where
Entitlement_idx_product,Entitlement_idx_supplier,
Entitlement_idx_classification,
Entitlement_idx_contract
3 DEPENDENT SUBQUERY userentitl1_ eq_ref PRIMARY PRIMARY 8 func 1 100 Using index; Using temporary
3 DEPENDENT SUBQUERY this_ ref FK000000000001184,Entitlement_idx_customer, FK000000000001184 9 func 2 100 Using where
Entitlement_idx_product,Entitlement_idx_supplier,
Entitlement_idx_classification,
Entitlement_idx_contract
Tables
Catalogprovisioning
CREATE TABLE `catalogprovisioning` (
`CatalogProvisioningSN` bigint(20) NOT NULL AUTO_INCREMENT,
`ProductCatalogSN` bigint(20) NOT NULL,
`SupplierSN` bigint(20) NOT NULL,
`CurrentVersion` int(11) DEFAULT NULL,
`ReleasedVersion` int(11) DEFAULT NULL,
`StatusSN` bigint(20) NOT NULL,
`CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`CreatedOn` datetime NOT NULL,
`ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`ChangedOn` datetime NOT NULL,
PRIMARY KEY (`CatalogProvisioningSN`),
UNIQUE KEY `UK000000000000009` (`ProductCatalogSN`,`SupplierSN`),
KEY `FK000000000000030` (`StatusSN`),
KEY `FK000000000000032` (`ProductCatalogSN`),
KEY `FK000000000000031` (`SupplierSN`),
CONSTRAINT `FK000000000000030` FOREIGN KEY (`StatusSN`) REFERENCES `status` (`StatusSN`),
CONSTRAINT `FK000000000000031` FOREIGN KEY (`SupplierSN`) REFERENCES `supplier` (`SupplierSN`),
CONSTRAINT `FK000000000000032` FOREIGN KEY (`ProductCatalogSN`) REFERENCES `productcatalog` (`ProductCatalogSN`)
) ENGINE=InnoDB AUTO_INCREMENT=46869 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
CatalogUsage
CREATE TABLE `catalogusage` (
`CatalogUsageSN` bigint(20) NOT NULL AUTO_INCREMENT,
`ProductCatalogSN` bigint(20) NOT NULL,
`CustomerSN` bigint(20) DEFAULT NULL,
`CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`CreatedOn` datetime NOT NULL,
`ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`ChangedOn` datetime NOT NULL,
PRIMARY KEY (`CatalogUsageSN`),
UNIQUE KEY `UK000000000000010` (`ProductCatalogSN`,`CustomerSN`),
KEY `FK000000000000033` (`ProductCatalogSN`),
KEY `FK000000000000034` (`CustomerSN`),
CONSTRAINT `FK000000000000033` FOREIGN KEY (`ProductCatalogSN`) REFERENCES `productcatalog` (`ProductCatalogSN`),
CONSTRAINT `FK000000000000034` FOREIGN KEY (`CustomerSN`) REFERENCES `customer` (`CustomerSN`)
) ENGINE=InnoDB AUTO_INCREMENT=45407 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
Contract
CREATE TABLE `contract` (
`ContractSN` bigint(20) NOT NULL AUTO_INCREMENT,
`ContractID` varchar(100) COLLATE utf8_bin NOT NULL,
`ValidFrom` datetime DEFAULT NULL,
`ValidTo` datetime DEFAULT NULL,
`StatusSN` bigint(20) NOT NULL,
`HierarchyCode` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`ExtContractID` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`ExtContractLineID` varchar(10) COLLATE utf8_bin DEFAULT NULL,
`Description` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`Type` varchar(4) COLLATE utf8_bin DEFAULT NULL,
`isPreferred` char(1) COLLATE utf8_bin DEFAULT NULL,
`NumberOfPrices` int(11) DEFAULT NULL,
`ParentContractSN` bigint(20) DEFAULT NULL,
`IsStandard` char(1) COLLATE utf8_bin DEFAULT NULL,
`IsOffer` char(1) COLLATE utf8_bin DEFAULT NULL,
`OfferId` varchar(35) COLLATE utf8_bin DEFAULT NULL,
`TermsOfPaymentID` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`TermsOfDeliveryID` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`MinOrderValue` decimal(19,2) DEFAULT NULL,
`FreeShippingBoundary` decimal(19,2) DEFAULT NULL,
`FreightSurcharge` decimal(19,2) DEFAULT NULL,
`SmallVolumeSurcharge` decimal(19,2) DEFAULT NULL,
`CurrencyID` varchar(3) COLLATE utf8_bin DEFAULT NULL,
`IsMinOrderValueRequired` char(1) COLLATE utf8_bin DEFAULT NULL,
`CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`CreatedOn` datetime NOT NULL,
`ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`ChangedOn` datetime NOT NULL,
`TagID` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`RequireSafetyCheck` char(1) COLLATE utf8_bin DEFAULT 'N',
`RequireChemicalCheck` char(1) COLLATE utf8_bin DEFAULT 'N',
`RequireHazardousCheck` char(1) COLLATE utf8_bin DEFAULT 'N',
`ContainsChemicalItems` varchar(11) COLLATE utf8_bin DEFAULT 'don''t known',
`ContainsHazardousItems` varchar(11) COLLATE utf8_bin DEFAULT 'don''t known',
`ContainsSafetyItems` varchar(11) COLLATE utf8_bin DEFAULT 'don''t known',
`IsInternal` char(1) COLLATE utf8_bin DEFAULT 'N',
`MaxOrderValue` decimal(19,2) DEFAULT NULL,
`TotalContractedAmount` decimal(19,2) DEFAULT NULL,
`IsFrameContract` char(1) COLLATE utf8_bin DEFAULT 'N',
`ContractCategoryId` varchar(20) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`ContractSN`),
UNIQUE KEY `UK000000000000020` (`ContractID`),
KEY `FK000000000000070` (`StatusSN`),
KEY `FK000000000000071` (`ParentContractSN`),
KEY `FK000000000000072` (`CurrencyID`),
KEY `FK000000000000073` (`TermsOfPaymentID`),
KEY `FK000000000000074` (`TermsOfDeliveryID`),
KEY `FK000000000001177` (`TagID`),
KEY `FK000000000011310` (`ContractCategoryId`),
CONSTRAINT `FK000000000000070` FOREIGN KEY (`StatusSN`) REFERENCES `status` (`StatusSN`),
CONSTRAINT `FK000000000000071` FOREIGN KEY (`ParentContractSN`) REFERENCES `contract` (`ContractSN`),
CONSTRAINT `FK000000000000072` FOREIGN KEY (`CurrencyID`) REFERENCES `currency` (`CurrencyID`),
CONSTRAINT `FK000000000000073` FOREIGN KEY (`TermsOfPaymentID`) REFERENCES `termsofpayment` (`TermsOfPaymentID`),
CONSTRAINT `FK000000000000074` FOREIGN KEY (`TermsOfDeliveryID`) REFERENCES `termsofdelivery` (`TermsOfDeliveryID`),
CONSTRAINT `FK000000000001177` FOREIGN KEY (`TagID`) REFERENCES `tag` (`TagID`),
CONSTRAINT `FK000000000011310` FOREIGN KEY (`ContractCategoryId`) REFERENCES `contractcategory` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=53383 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
ContractProvisioning
CREATE TABLE `contractprovisioning` (
`ContractProvisioningSN` bigint(20) NOT NULL AUTO_INCREMENT,
`ContractSN` bigint(20) NOT NULL,
`SupplierSN` bigint(20) NOT NULL,
`CurrentVersion` int(11) DEFAULT NULL,
`ReleasedVersion` int(11) DEFAULT NULL,
`StatusSN` bigint(20) NOT NULL,
`CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`CreatedOn` datetime NOT NULL,
`ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`ChangedOn` datetime NOT NULL,
`CurrentValidFrom` datetime DEFAULT NULL,
`CurrentValidTo` datetime DEFAULT NULL,
`NewValidFrom` datetime DEFAULT NULL,
`NewValidTo` datetime DEFAULT NULL,
`SentMailConfirmation` int(11) DEFAULT NULL,
PRIMARY KEY (`ContractProvisioningSN`),
UNIQUE KEY `UK000000000000021` (`ContractSN`,`SupplierSN`),
KEY `FK000000000000079` (`StatusSN`),
KEY `FK000000000000081` (`ContractSN`),
KEY `FK000000000000080` (`SupplierSN`),
CONSTRAINT `FK000000000000079` FOREIGN KEY (`StatusSN`) REFERENCES `status` (`StatusSN`),
CONSTRAINT `FK000000000000080` FOREIGN KEY (`SupplierSN`) REFERENCES `supplier` (`SupplierSN`),
CONSTRAINT `FK000000000000081` FOREIGN KEY (`ContractSN`) REFERENCES `contract` (`ContractSN`)
) ENGINE=InnoDB AUTO_INCREMENT=48976 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
ContractUsage
CREATE TABLE `contractusage` (
`ContractUsageSN` bigint(20) NOT NULL AUTO_INCREMENT,
`ContractSN` bigint(20) NOT NULL,
`CustomerSN` bigint(20) DEFAULT NULL,
`CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`CreatedOn` datetime NOT NULL,
`ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`ChangedOn` datetime NOT NULL,
PRIMARY KEY (`ContractUsageSN`),
UNIQUE KEY `UK000000000000022` (`ContractSN`,`CustomerSN`),
KEY `FK000000000000082` (`CustomerSN`),
KEY `FK000000000000083` (`ContractSN`),
CONSTRAINT `FK000000000000082` FOREIGN KEY (`CustomerSN`) REFERENCES `customer` (`CustomerSN`),
CONSTRAINT `FK000000000000083` FOREIGN KEY (`ContractSN`) REFERENCES `contract` (`ContractSN`)
) ENGINE=InnoDB AUTO_INCREMENT=47047 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
Entitlement
CREATE TABLE `entitlement` (
`EntitlementSN` bigint(20) NOT NULL AUTO_INCREMENT,
`RefererType` varchar(40) COLLATE utf8_bin NOT NULL,
`EntitledContentId` bigint(20) DEFAULT NULL,
`EntitledContract` bigint(20) DEFAULT NULL,
`CustomerID` varchar(30) COLLATE utf8_bin DEFAULT NULL,
`SupplierID` varchar(30) COLLATE utf8_bin DEFAULT NULL,
`ClassificationID` varchar(30) COLLATE utf8_bin DEFAULT NULL,
`ClassificationGroupID` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`CatalogID` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`ProductID` varchar(35) COLLATE utf8_bin DEFAULT NULL,
`CountryID` varchar(2) COLLATE utf8_bin DEFAULT NULL,
`Company` varchar(256) COLLATE utf8_bin DEFAULT NULL,
`UserID` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`BusinessUnit` varchar(256) COLLATE utf8_bin DEFAULT NULL,
`WorkLocation` varchar(256) COLLATE utf8_bin DEFAULT NULL,
`PurchaseType` varchar(256) COLLATE utf8_bin DEFAULT NULL,
`ProjectCode` varchar(256) COLLATE utf8_bin DEFAULT NULL,
`CostCenter` varchar(256) COLLATE utf8_bin DEFAULT NULL,
`ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`ChangedOn` datetime NOT NULL,
`CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`CreatedOn` datetime NOT NULL,
`ContractId` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`EntitledHtmlContentUri` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`EntitledClassificationID` varchar(30) COLLATE utf8_bin DEFAULT NULL,
`EntitledClassificationGroupID` varchar(100) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`EntitlementSN`),
KEY `FK000000000001184` (`EntitledContract`),
KEY `Entitlement_idx_customer` (`RefererType`,`CustomerID`),
KEY `Entitlement_idx_product` (`RefererType`,`CatalogID`,`ProductID`),
KEY `Entitlement_idx_supplier` (`RefererType`,`SupplierID`),
KEY `Entitlement_idx_classification` (`RefererType`,`ClassificationID`,`ClassificationGroupID`),
KEY `Entitlement_idx_contract` (`RefererType`,`ContractId`),
CONSTRAINT `FK000000000001184` FOREIGN KEY (`EntitledContract`) REFERENCES `contract` (`ContractSN`)
) ENGINE=InnoDB AUTO_INCREMENT=435959 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
ProductCatalog
CREATE TABLE `productcatalog` (
`ProductCatalogSN` bigint(20) NOT NULL AUTO_INCREMENT,
`ProductCatalogID` varchar(50) COLLATE utf8_bin NOT NULL,
`ValidFrom` datetime DEFAULT NULL,
`ValidTo` datetime DEFAULT NULL,
`StatusSN` bigint(20) NOT NULL,
`Version` varchar(7) COLLATE utf8_bin DEFAULT NULL,
`Description` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`Type` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`NumberOfProducts` int(11) DEFAULT NULL,
`CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`CreatedOn` datetime NOT NULL,
`ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`ChangedOn` datetime NOT NULL,
`RequireSafetyCheck` char(1) COLLATE utf8_bin DEFAULT 'N',
`RequireChemicalCheck` char(1) COLLATE utf8_bin DEFAULT 'N',
`RequireHazardousCheck` char(1) COLLATE utf8_bin DEFAULT 'N',
`ContainsChemicalItems` varchar(11) COLLATE utf8_bin DEFAULT 'don''t known',
`ContainsHazardousItems` varchar(11) COLLATE utf8_bin DEFAULT 'don''t known',
`ContainsSafetyItems` varchar(11) COLLATE utf8_bin DEFAULT 'don''t known',
`IsPunchoutEditSupported` char(1) COLLATE utf8_bin DEFAULT NULL,
`IsPunchoutOnly` char(1) COLLATE utf8_bin DEFAULT 'N',
`CatalogCategoryId` varchar(20) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`ProductCatalogSN`),
UNIQUE KEY `UK000000000000045` (`ProductCatalogID`),
KEY `FK000000000000175` (`StatusSN`),
KEY `FK000000000011311` (`CatalogCategoryId`),
CONSTRAINT `FK000000000000175` FOREIGN KEY (`StatusSN`) REFERENCES `status` (`StatusSN`),
CONSTRAINT `FK000000000011311` FOREIGN KEY (`CatalogCategoryId`) REFERENCES `catalogcategory` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=49881 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
Supplier -
CREATE TABLE `supplier` (
`SupplierSN` bigint(20) NOT NULL AUTO_INCREMENT,
`SupplierID` varchar(30) COLLATE utf8_bin NOT NULL,
`DUNSNo` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`SupplierGroupSN` bigint(20) DEFAULT NULL,
`SupplierName` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`StatusSN` bigint(20) DEFAULT NULL,
`TermsOfPaymentID` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`TermsOfDeliveryID` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`TaxIdentificationNo` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`ExtSupplierID` varchar(30) COLLATE utf8_bin DEFAULT NULL,
`MethodOfPaymentID` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`AccountNumber` varchar(35) COLLATE utf8_bin DEFAULT NULL,
`BankIdentificationCode` varchar(15) COLLATE utf8_bin DEFAULT NULL,
`SwiftCode` varchar(11) COLLATE utf8_bin DEFAULT NULL,
`BankCountryKey` varchar(3) COLLATE utf8_bin DEFAULT NULL,
`ExtBankControllKey` varchar(2) COLLATE utf8_bin DEFAULT NULL,
`CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`CreatedOn` datetime NOT NULL,
`ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`ChangedOn` datetime NOT NULL,
`LanguageID` varchar(4) COLLATE utf8_bin DEFAULT NULL,
`CurrencyID` varchar(3) COLLATE utf8_bin DEFAULT NULL,
`ExtAccountCode` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`ExtName` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`ExtShortName` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`ExtGlobalID` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`Logo` varchar(50) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`SupplierSN`),
UNIQUE KEY `UK000000000000056` (`SupplierID`),
KEY `FK000000000000270` (`StatusSN`),
KEY `FK000000000000271` (`CurrencyID`),
KEY `FK000000000000272` (`TermsOfPaymentID`),
KEY `FK000000000000273` (`MethodOfPaymentID`),
KEY `FK000000000000274` (`LanguageID`),
KEY `FK000000000000275` (`TermsOfDeliveryID`),
KEY `FK000000000000276` (`SupplierGroupSN`),
CONSTRAINT `FK000000000000270` FOREIGN KEY (`StatusSN`) REFERENCES `status` (`StatusSN`),
CONSTRAINT `FK000000000000271` FOREIGN KEY (`CurrencyID`) REFERENCES `currency` (`CurrencyID`),
CONSTRAINT `FK000000000000272` FOREIGN KEY (`TermsOfPaymentID`) REFERENCES `termsofpayment` (`TermsOfPaymentID`),
CONSTRAINT `FK000000000000273` FOREIGN KEY (`MethodOfPaymentID`) REFERENCES `methodofpayment` (`MethodOfPaymentID`),
CONSTRAINT `FK000000000000274` FOREIGN KEY (`LanguageID`) REFERENCES `isolanguage` (`LanguageID`),
CONSTRAINT `FK000000000000275` FOREIGN KEY (`TermsOfDeliveryID`) REFERENCES `termsofdelivery` (`TermsOfDeliveryID`),
CONSTRAINT `FK000000000000276` FOREIGN KEY (`SupplierGroupSN`) REFERENCES `suppliergroup` (`SupplierGroupSN`)
) ENGINE=InnoDB AUTO_INCREMENT=19096 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
SupplierProvisioning
CREATE TABLE `supplierprovisioning` (
`SupplierProvisioningSN` bigint(20) NOT NULL AUTO_INCREMENT,
`CustomerSN` bigint(20) NOT NULL,
`SupplierSN` bigint(20) NOT NULL,
`CurrencyID` varchar(3) COLLATE utf8_bin DEFAULT NULL,
`CustomerNo` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`EMailForOrder` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`FaxForOrder` varchar(100) COLLATE utf8_bin DEFAULT NULL,
`GeneralOrderText` varchar(500) COLLATE utf8_bin DEFAULT NULL,
`ManualProdReceipt` char(1) COLLATE utf8_bin DEFAULT NULL,
`MinOrderValue` int(11) DEFAULT NULL,
`ValidFrom` datetime DEFAULT NULL,
`ValidTo` datetime DEFAULT NULL,
`VendorNo` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`StatusSN` bigint(20) NOT NULL,
`CustomerSupplierID` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`SupplierCustomerID` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`AccountNo` varchar(50) COLLATE utf8_bin DEFAULT NULL,
`Type` varchar(20) COLLATE utf8_bin DEFAULT NULL,
`CreatedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`CreatedOn` datetime NOT NULL,
`ChangedBy` varchar(60) COLLATE utf8_bin NOT NULL,
`ChangedOn` datetime NOT NULL,
`MinOrderValueCurrencyID` varchar(3) COLLATE utf8_bin DEFAULT NULL,
`WaitingPeriod` int(11) DEFAULT NULL,
`LimitAmountForAutoGoodsReceipt` decimal(19,2) DEFAULT NULL,
`IsDiversitySupplier` char(1) COLLATE utf8_bin DEFAULT 'N',
`IsEnvPrefQualifiedSupplier` char(1) COLLATE utf8_bin DEFAULT 'N',
`IsPreferredSupplier` char(1) COLLATE utf8_bin DEFAULT 'N',
`MinOrderBehaviour` int(11) DEFAULT NULL,
`IsForUpload` char(1) COLLATE utf8_bin NOT NULL DEFAULT 'Y',
`IsDeliveryRated` char(1) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (`SupplierProvisioningSN`),
UNIQUE KEY `UK000000000000060` (`CustomerSN`,`SupplierSN`),
KEY `FK000000000000286` (`StatusSN`),
KEY `FK000000000000287` (`SupplierSN`),
KEY `FK000000000000288` (`CurrencyID`),
KEY `FK000000000000289` (`CustomerSN`),
KEY `FK000000000000327` (`MinOrderValueCurrencyID`),
CONSTRAINT `FK000000000000286` FOREIGN KEY (`StatusSN`) REFERENCES `status` (`StatusSN`),
CONSTRAINT `FK000000000000287` FOREIGN KEY (`SupplierSN`) REFERENCES `supplier` (`SupplierSN`),
CONSTRAINT `FK000000000000288` FOREIGN KEY (`CurrencyID`) REFERENCES `currency` (`CurrencyID`),
CONSTRAINT `FK000000000000289` FOREIGN KEY (`CustomerSN`) REFERENCES `customer` (`CustomerSN`),
CONSTRAINT `FK000000000000327` FOREIGN KEY (`MinOrderValueCurrencyID`) REFERENCES `currency` (`CurrencyID`)
) ENGINE=InnoDB AUTO_INCREMENT=19776 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
Table - Status
Name Engine Ver Row_format Rows Avg_row_len Data_len Max_data_len Index_len Data_free Auto_inc Create_time Upd_time Chk_time Collation Checksum Create_options
catalogprovisioning InnoDB 10 Compact 42079 137 5783552 0 10551296 4194304 46869 2015-10-01 05:30:20 NULL NULL utf8_bin NULL
catalogusage InnoDB 10 Compact 43728 108 4734976 0 6864896 4194304 45407 2015-10-01 05:30:28 NULL NULL utf8_bin NULL
contract InnoDB 10 Compact 44446 248 11026432 0 13762560 4194304 53383 2015-10-01 05:30:42 NULL NULL utf8_bin NULL
contractprovisioning InnoDB 10 Compact 45052 128 5783552 0 10551296 4194304 48976 2015-10-01 05:31:26 NULL NULL utf8_bin NULL
contractusage InnoDB 10 Compact 44066 107 4734976 0 6864896 4194304 47047 2015-10-01 05:31:31 NULL NULL utf8_bin NULL
entitlement InnoDB 10 Compact 223736 129 28901376 0 39944192 7340032 435959 2015-10-01 05:31:46 NULL NULL utf8_bin NULL
supplier InnoDB 10 Compact 19163 192 3686400 0 4243456 4194304 19096 2015-10-01 06:09:20 NULL NULL utf8_bin NULL
supplierprovisioning InnoDB 10 Compact 18253 144 2637824 0 4800512 4194304 19776 2015-10-01 06:09:26 NULL NULL utf8_bin NULL
MySQL Configuration
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
character-set-server = utf8
collation-server = utf8_bin
default-storage-engine = INNODB
event-scheduler = 1
expire_logs_days=4
general_log=1
innodb_additional_mem_pool_size=40M
innodb_buffer_pool_size = 36G
innodb_buffer_pool_instances = 2
innodb_data_file_path = ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity=2000
innodb_lock_wait_timeout = 50
innodb_log_buffer_size = 16M
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_read_io_threads=64
innodb_use_native_aio=1
innodb_file_per_table=1
innodb_write_io_threads=64
innodb_thread_concurrency=0
innodb_print_all_deadlocks=1
key_buffer_size = 256M
log_bin=/var/log/mysql/mysql-bin.log
log-output=TABLE
long_query_time = 60
lower-case-table-names = 1
max_allowed_packet = 32M
max_connections = 1600
net_buffer_length = 2K
query_cache_limit = 32M
query_cache_size = 128M
query_cache_type = 1
read_buffer_size = 256K
read_rnd_buffer_size = 256K
slow-query-log = 1
sort_buffer_size = 64K
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
table_open_cache = 175000
tmpdir=/tmp/mysqltmp/
tmp_table_size=2G
max_heap_table_size=2G
thread_cache_size = 8
thread_stack = 256k
wait_timeout=180
server-id = 1
binlog_format=mixed
I am using a 3GB tempfs for RAM Disk
Any thoughts or suggestions?
Edited 1 time(s). Last edit at 10/01/2015 10:47PM by Peter Brawley.
Subject
Views
Written By
Posted
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.