This is a test server with nothing else hitting the box. I imported a
dump of a production database and is trying to find a way to reduce the
time it takes to rebuild the index on the mentioned table
Hard Drive spec:
Clarion EMC Hard drive 100 GB internal 3.5" Fibre Channel 10000 rpm buffer
CREATE TABLE `parts` (
`prtStore` smallint(5) unsigned NOT NULL default '0',
`prtMMfg` char(3) NOT NULL default '',
`prtMfg` char(3) NOT NULL default '',
`prtPN` char(28) NOT NULL default '',
`prtStripped` char(28) NOT NULL default '',
`prtSort` char(75) NOT NULL default '',
`prtPNID` char(36) NOT NULL default '',
`prtSKU` char(14) NOT NULL default '',
`prtPic` char(20) NOT NULL default '',
`prtDesc` char(40) NOT NULL default '',
`prtDesc2` char(40) NOT NULL default '',
`prtStkQty` int(10) NOT NULL default '0',
`prtRsvQty` int(9) unsigned NOT NULL default '0',
`prtSupFlag` enum('N','Y') NOT NULL default 'N',
`prtAltMfg` char(3) NOT NULL default '',
`prtAltPN` char(28) NOT NULL default '',
`prtVD` tinyint(1) unsigned NOT NULL default '2',
`prtVQ` tinyint(1) unsigned NOT NULL default '0',
`prtMClass` char(1) NOT NULL default '',
`prtSClass1` char(1) NOT NULL default 'N',
`prtSClass2` char(1) NOT NULL default '',
`prtUClass` char(1) NOT NULL default '',
`prtProd` char(3) NOT NULL default '',
`prtGroup` char(10) NOT NULL default '',
`prtRptGroup` char(8) NOT NULL default '',
`prtPNcode` char(3) NOT NULL default '',
`prtDept` char(3) NOT NULL default '',
`prtCat` char(3) NOT NULL default '',
`prtOrderType` char(10) NOT NULL default '',
`prtDNR` enum('N','Y') NOT NULL default 'N',
`prtStockingCode` char(1) NOT NULL default '',
`prtExclude` char(3) NOT NULL default '',
`prtTax` enum('S','T','E') NOT NULL default 'S',
`prtNet` enum('N','Y') NOT NULL default 'N',
`prtMinSell` int(8) unsigned NOT NULL default '0',
`prtPerCar` smallint(5) unsigned NOT NULL default '0',
`prtMinStock` int(9) unsigned NOT NULL default '0',
`prtMaxStock` int(9) unsigned NOT NULL default '0',
`prtOrdPnt` int(9) unsigned NOT NULL default '0',
`prtLeadTime` tinyint(2) unsigned NOT NULL default '0',
`prtSMeasure` char(2) NOT NULL default 'EA',
`prtPMeasure` char(2) NOT NULL default 'EA',
`prtBMeasure` char(2) NOT NULL default 'EA',
`prtPackQty` int(7) unsigned NOT NULL default '0',
`prtPurchQty` smallint(3) unsigned NOT NULL default '1',
`prtMinBuyQty` smallint(3) unsigned NOT NULL default '1',
`prtMaxBuyQty` int(10) unsigned NOT NULL default '0',
`prtWeight` int(9) unsigned NOT NULL default '0',
`prtFreight` int(9) unsigned NOT NULL default '0',
`prtFrghtPPD` enum('Y','N') NOT NULL default 'Y',
`prtOnOrder` int(9) unsigned NOT NULL default '0',
`prtInBound` int(9) unsigned NOT NULL default '0',
`prtWHloc` smallint(5) unsigned NOT NULL default '0',
`prtWHzone` char(3) NOT NULL default '',
`prtVBO` int(9) unsigned NOT NULL default '0',
`prtCBO` int(9) unsigned NOT NULL default '0',
`prtPriVC` char(3) NOT NULL default '',
`prtPriVType` enum('M','S') NOT NULL default 'M',
`prtSecVC` char(3) NOT NULL default '',
`prtSecVType` enum('M','S') NOT NULL default 'M',
`prtStocked` enum('Y','N') NOT NULL default 'Y',
`prtReal` enum('Y','N') NOT NULL default 'Y',
`prtActive` enum('Y','N') NOT NULL default 'Y',
`prtCpnStk` enum('N','Y') NOT NULL default 'N',
`prtCpnVerify` enum('Y','N') NOT NULL default 'Y',
`prtCpnPNID` char(36) NOT NULL default '',
`prtLastSold` date default NULL,
`prtLastReturn` date default NULL,
`prtLastReceived` date default NULL,
`prtLastInventory` datetime default NULL,
`prtCreated` date default NULL,
`prtRetCore` int(10) unsigned NOT NULL default '0',
`prtTotRetCore` int(10) unsigned NOT NULL default '0',
`prtRetDef` int(10) unsigned NOT NULL default '0',
`prtTotRetDef` int(10) unsigned NOT NULL default '0',
`prtRetNew` int(10) unsigned NOT NULL default '0',
`prtTurns` int(9) unsigned NOT NULL default '0',
`prtFixedFee` int(9) unsigned NOT NULL default '0',
`prtFFFlag` enum('N','Y') NOT NULL default 'N',
`prtVOC` int(10) unsigned NOT NULL default '0',
`prtAreaPop` char(3) NOT NULL default '',
`prtMfgPop` char(3) NOT NULL default '',
`prtVehPopulation` int(5) unsigned NOT NULL default '0',
`prtAvgAge` int(6) unsigned NOT NULL default '0',
`prtForecast` int(6) unsigned NOT NULL default '0',
`prtNewPrice1` int(9) unsigned NOT NULL default '0',
`prtNewPrice2` int(9) unsigned NOT NULL default '0',
`prtNewPrice3` int(9) unsigned NOT NULL default '0',
`prtNewPrice4` int(9) unsigned NOT NULL default '0',
`prtNewPrice5` int(9) unsigned NOT NULL default '0',
`prtNewPrice6` int(9) unsigned NOT NULL default '0',
`prtNewPrice7` int(9) unsigned NOT NULL default '0',
`prtNewCurCost` int(9) unsigned NOT NULL default '0',
`prtNewAvgCost` int(9) unsigned NOT NULL default '0',
`prtNewLastCost` int(9) unsigned NOT NULL default '0',
`prtNewVCost1` int(9) unsigned NOT NULL default '0',
`prtNewVCost2` int(9) unsigned NOT NULL default '0',
`prtNewVCost3` int(9) unsigned NOT NULL default '0',
`prtNewVCost4` int(9) unsigned NOT NULL default '0',
`prtNewCoreSell` int(9) unsigned NOT NULL default '0',
`prtNewCoreCost` int(9) unsigned NOT NULL default '0',
`prtOldPrice1` int(9) unsigned NOT NULL default '0',
`prtOldPrice2` int(9) unsigned NOT NULL default '0',
`prtOldPrice3` int(9) unsigned NOT NULL default '0',
`prtOldPrice4` int(9) unsigned NOT NULL default '0',
`prtOldPrice5` int(9) unsigned NOT NULL default '0',
`prtOldPrice6` int(9) unsigned NOT NULL default '0',
`prtOldPrice7` int(9) unsigned NOT NULL default '0',
`prtOldCurCost` int(9) unsigned NOT NULL default '0',
`prtOldAvgCost` int(9) unsigned NOT NULL default '0',
`prtOldLastCost` int(9) unsigned NOT NULL default '0',
`prtOldVCost1` int(9) unsigned NOT NULL default '0',
`prtOldVCost2` int(9) unsigned NOT NULL default '0',
`prtOldVCost3` int(9) unsigned NOT NULL default '0',
`prtOldVCost4` int(9) unsigned NOT NULL default '0',
`prtOldCoreSell` int(9) unsigned NOT NULL default '0',
`prtOldCoreCost` int(9) unsigned NOT NULL default '0',
`prtPrice1` int(9) unsigned NOT NULL default '0',
`prtPrice2` int(9) unsigned NOT NULL default '0',
`prtPrice3` int(9) unsigned NOT NULL default '0',
`prtPrice4` int(9) unsigned NOT NULL default '0',
`prtPrice5` int(9) unsigned NOT NULL default '0',
`prtPrice6` int(9) unsigned NOT NULL default '0',
`prtPrice7` int(9) unsigned NOT NULL default '0',
`prtCurCost` int(9) unsigned NOT NULL default '0',
`prtAvgCost` int(9) unsigned NOT NULL default '0',
`prtLastCost` int(9) unsigned NOT NULL default '0',
`prtVCost1` int(9) unsigned NOT NULL default '0',
`prtVCost2` int(9) unsigned NOT NULL default '0',
`prtVCost3` int(9) unsigned NOT NULL default '0',
`prtVCost4` int(9) unsigned NOT NULL default '0',
`prtCoreSell` int(9) unsigned NOT NULL default '0',
`prtCoreCost` int(9) unsigned NOT NULL default '0',
`prtHstCur` int(10) NOT NULL default '0',
`prtHstYTD` int(10) NOT NULL default '0',
`prtLstYear` int(10) NOT NULL default '0',
`prtDayFill` int(10) NOT NULL default '0',
`prtLostSales` int(10) unsigned NOT NULL default '0',
`prtCurPurch` int(10) NOT NULL default '0',
`prtLifoCost1` int(9) unsigned NOT NULL default '0',
`prtLifoCost2` int(9) unsigned NOT NULL default '0',
`prtLifoCost3` int(9) unsigned NOT NULL default '0',
`prtLifoCost4` int(9) unsigned NOT NULL default '0',
`prtLifoCore1` int(9) unsigned NOT NULL default '0',
`prtLifoCore2` int(9) unsigned NOT NULL default '0',
`prtLifoCore3` int(9) unsigned NOT NULL default '0',
`prtLifoCore4` int(9) unsigned NOT NULL default '0',
`prtDisposalFee` int(9) unsigned NOT NULL default '0',
`prtStkProtect` enum('N','Y') NOT NULL default 'N',
`prtPrChg` datetime default NULL,
`prtPrApply` datetime default NULL,
`prtPrUpdate` enum('N','Y','F','M','I') NOT NULL default 'N',
`prtCommisionPer` int(6) unsigned NOT NULL default '0',
`prtUseCommision` enum('N','Y') NOT NULL default 'N',
`prtRecallMsg` int(10) unsigned NOT NULL default '0',
`prtRelatedSaleMsg` int(10) unsigned NOT NULL default '0',
`prtHazmatMsg` int(10) unsigned NOT NULL default '0',
`prtWarrantyMsg` int(10) unsigned NOT NULL default '0',
`prtWarrantyLength` smallint(5) unsigned NOT NULL default '0',
`prtWarrantyUnit`
enum('NO','MO','DA','YR','WK','HR','LT','LL','ML','TD') NOT NULL default
'NO',
`prtWarrantyMsg2` int(10) unsigned NOT NULL default '0',
`prtWarrantyLength2` smallint(5) unsigned NOT NULL default '0',
`prtWarrantyUnit2`
enum('NO','MO','DA','YR','WK','HR','LT','LL','ML','TD') NOT NULL default
'NO',
`prtInvMod` char(2) NOT NULL default 'S',
`prtBelowCostMargin` int(5) unsigned NOT NULL default '0',
`prtUseBelowCostMargin` enum('N','Y') NOT NULL default 'N',
`prtAboveCostMargin` int(5) unsigned NOT NULL default '0',
`prtUseAboveCostMargin` enum('N','Y') NOT NULL default 'N',
`prtForcedQty` enum('N','Y') NOT NULL default 'N',
`prtIsRestricted` enum('N','Y') NOT NULL default 'N',
`prtHazmatCode` char(3) NOT NULL default '',
`prtShipAnywhere` enum('N','Y') NOT NULL default 'Y',
`prtCInBound` int(11) NOT NULL default '0',
`prtPartTax` char(4) NOT NULL default '',
`prtCoreTax` char(4) NOT NULL default '',
`prtStateFee` char(4) NOT NULL default '',
`prtCIDFee` char(4) NOT NULL default '',
`prtOtherFee` char(4) NOT NULL default '',
`prtDepth` char(18) NOT NULL default '',
`prtHeight` char(9) NOT NULL default '',
`prtWidth` char(9) NOT NULL default '',
`prtMSDS` int(10) unsigned NOT NULL default '0',
`prtRetailCatType` char(3) NOT NULL default '',
`prtLogicOper` enum('N','F','B','L','1') NOT NULL default 'N',
`prtSubType` enum('NONE','TIRE','BATT','PKG','SMPT') NOT NULL default
'NONE',
`prtWrtyTypeID` int(10) unsigned NOT NULL default '0',
`prtSourceCode` char(3) NOT NULL default '',
`prtShopPart` enum('N','Y') NOT NULL default 'N',
`prtPkgGroup` char(10) NOT NULL default '',
`prtPkgSubGroup` char(10) NOT NULL default '',
`prtDisclaimID` int(10) unsigned NOT NULL default '0',
`prtQualifier` char(10) NOT NULL default '',
`prtStatus` enum('ACTIVE','VOID','CHANGEOVER') default NULL,
`prtDCPop` char(5) NOT NULL default '',
`prtCOnOrder` int(10) unsigned NOT NULL default '0',
`prtIsDisplay` enum('N','Y') NOT NULL default 'N',
`prtVolProtect` int(10) unsigned NOT NULL default '0',
`prtDNM` enum('N','Y') NOT NULL default 'N',
`prtLst2Year` int(11) NOT NULL default '0',
`prtLst3Year` int(11) NOT NULL default '0',
`prtDoNotReturn` enum('N','Y') NOT NULL default 'N',
`prtSalesHistTot` int(10) NOT NULL default '0',
`prtSalesHistPrd` smallint(5) NOT NULL default '0',
PRIMARY KEY (`prtPNID`),
UNIQUE KEY `prtUPN` (`prtPN`,`prtStore`,`prtMfg`),
KEY `prtSort` (`prtSort`),
KEY `sku1` (`prtSKU`,`prtStore`),
KEY `prtIPN` (`prtStripped`,`prtStore`,`prtMfg`),
KEY `prtDESC` (`prtDesc`,`prtStore`),
KEY `prtWrtyTypeID` (`prtWrtyTypeID`),
KEY `prtStore` (`prtStore`,`prtMfg`),
CONSTRAINT `parts_f1` FOREIGN KEY (`prtStore`) REFERENCES `cofile`
(`coStore`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Parts Table'
mysql> SHOW VARIABLES LIKE '%buff%';
+-------------------------------+----------+
| Variable_name | Value |
+-------------------------------+----------+
| bdb_log_buffer_size | 1048576 |
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 52428800 |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 67108864 |
| myisam_sort_buffer_size | 16777216 |
| net_buffer_length | 65536 |
| preload_buffer_size | 32768 |
| read_buffer_size | 1044480 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 1048568 |
+-------------------------------+----------+
13 rows in set (0.00 sec)
mysql> SHOW TABLE STATUS LIKE 'parts';
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-------------------------------------------------------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length |
Data_length | Max_data_length | Index_length | Data_free |
Auto_increment | Create_time | Update_time | Check_time |
Collation | Checksum | Create_options |
Comment
|
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-------------------------------------------------------------------------------+
| parts | InnoDB | 9 | Fixed | 3921279 | 1643 |
6445596672 | NULL | 2941255680 | 0 | NULL
| 2005-08-29 12:32:47 | NULL | NULL | latin1_swedish_ci
| NULL | | Parts Table; InnoDB free: 6144 kB;
(`prtStore`) REFER `por/cofile`(`coStore`) |
+-------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-------------------------------------------------------------------------------+
1 row in set (0.11 sec)
[EMAIL PROTECTED] wrote:
Clyde Lewis <[EMAIL PROTECTED]> wrote on 08/29/2005 01:47:11 PM:
> Guys,
>
> I have a huge table to which I'm attempting to update the foreign key
> and index. It is taking me more than 20 hrs to complete the process and
> would like to know if someone can point me in the right direction.
> Please let me know of any additional information that I should provide.
>
>
> mysql> show index from parts;
> +-------+------------+---------------+--------------+---------------
>
+-----------+-------------+----------+--------+------+------------+---------+
>
> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment |
> +-------+------------+---------------+--------------+---------------
>
+-----------+-------------+----------+--------+------+------------+---------+
>
> | parts | 0 | PRIMARY | 1 | prtPNID |
> A | 3921279 | NULL | NULL | | BTREE |
|
> | parts | 0 | prtUPN | 1 | prtPN |
> A | 326773 | NULL | NULL | | BTREE |
|
> | parts | 0 | prtUPN | 2 | prtStore |
> A | 3921279 | NULL | NULL | | BTREE |
|
> | parts | 0 | prtUPN | 3 | prtMfg |
> A | 3921279 | NULL | NULL | | BTREE |
|
> | parts | 1 | prtSort | 1 | prtSort |
> A | 3921279 | NULL | NULL | | BTREE |
|
> | parts | 1 | sku1 | 1 | prtSKU |
> A | 18 | NULL | NULL | | BTREE |
|
> | parts | 1 | sku1 | 2 | prtStore |
> A | 18 | NULL | NULL | | BTREE |
|
> | parts | 1 | prtIPN | 1 | prtStripped |
> A | 230663 | NULL | NULL | | BTREE |
|
> | parts | 1 | prtIPN | 2 | prtStore |
> A | 3921279 | NULL | NULL | | BTREE |
|
> | parts | 1 | prtIPN | 3 | prtMfg |
> A | 3921279 | NULL | NULL | | BTREE |
|
> | parts | 1 | prtDESC | 1 | prtDesc |
> A | 301636 | NULL | NULL | | BTREE |
|
> | parts | 1 | prtDESC | 2 | prtStore |
> A | 3921279 | NULL | NULL | | BTREE |
|
> | parts | 1 | prtWrtyTypeID | 1 | prtWrtyTypeID |
> A | 18 | NULL | NULL | | BTREE |
|
> | parts | 1 | prtStore | 1 | prtStore |
> A | 18 | NULL | NULL | | BTREE |
|
> | parts | 1 | prtStore | 2 | prtMfg |
> A | 46132 | NULL | NULL | | BTREE |
|
> +-------+------------+---------------+--------------+---------------
>
+-----------+-------------+----------+--------+------+------------+---------+
>
>
>
> mysql> explain select * from parts;
> +----+-------------+-------+------+---------------+------+---------
> +------+---------+-------+
>
> | id | select_type | table | type | possible_keys | key | key_len |
> ref | rows | Extra |
> +----+-------------+-------+------+---------------+------+---------
> +------+---------+-------+
>
> | 1 | SIMPLE | parts | ALL | NULL | NULL | NULL |
> NULL | 3921279 | |
> +----+-------------+-------+------+---------------+------+---------
> +------+---------+-------+
>
> 1 row in set (0.00 sec)
>
> Files from mysqldata directory
> -rw-rw---- 1 mysql mysql 18K Aug 25 17:24 parts.frm
> -rw-rw---- 1 mysql mysql 8.9G Aug 29 12:32 parts.ibd
>
>
> DB MySQL: 4.1.11
> OS: Solaris 9
> Hardware: SUN 2900; 32GB RAM
>
>
Would you be so kind as to also provide the results of
SHOW CREATE TABLE parts\G
and
SHOW VARIABLES LIKE '%buff%';
and
SHOW TABLE STATUS LIKE 'parts'\G
Based on the sizes of your fields, you may just have a lot of data to
index. The buffer settings will tell us how much space you have
allocated for sorting and key creation.
How fast are your disks and what else is using them right now? Make
sure your anti-virus scanner is not checking every write from mysql to
your data directory (that can really slow you down, especially during
an index rebuild).
Please respond to the whole list and not just to me. I have a busy
afternoon and may not be able to get back to you very soon.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
