Re: Index update process 20+ hrs
It does not seem fine to me, mysql seems to really get slow once you break about the 3 gig myd size, or 1.5 gig myi size not sure why, I've changed every setting, but nothing seems to help Matt Clyde Lewis wrote: Matt, Looking at how the database if currently configured, do you have any suggestions or does it all seem fine. CL matt_lists wrote: Get used to it, I've got similar tables, mine have less columns, but more records, my biggest takes a week to restore a mysqldump Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index update process 20+ hrs
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 mysqlmysql18K Aug 25 17:24 parts.frm -rw-rw 1 mysqlmysql 8.9G Aug 29 12:32 parts.ibd DB MySQL: 4.1.11 OS: Solaris 9 Hardware: SUN 2900; 32GB RAM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index update process 20+ hrs
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 mysqlmysql18K Aug 25 17:24 parts.frm -rw-rw 1 mysqlmysql 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
Re: Index update process 20+ hrs
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 1 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)
Re: Index update process 20+ hrs
Get used to it, I've got similar tables, mine have less columns, but more records, my biggest takes a week to restore a mysqldump Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index update process 20+ hrs
If you are doing big updates on indexes, like during an import, you want to adjust your variables accordingly. The variables that would speed inserts and index updates are key_buffer_size bulk_insert_buffer_size if using MYISAM table types: myisam_max_sort_file_size myisam_sort_buffer_size Read this part of the online manual: http://dev.mysql.com/doc/mysql/en/insert-speed.html Tuning your variables specifically for importing can give you a dramatic performance boost. Then set them back to production values when you are done. On Aug 29, 2005, at 1:47 PM, Clyde Lewis wrote: 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 mysqlmysql18K Aug 25 17:24 parts.frm -rw-rw 1 mysqlmysql 8.9G Aug 29 12:32 parts.ibd DB MySQL: 4.1.11 OS: Solaris 9 Hardware: SUN 2900; 32GB RAM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index update process 20+ hrs
I'd try setting innodb_buffer_pool_size=8G in my.cnf -- Alexey Polyakov
Re: Index update process 20+ hrs
On Monday 29 August 2005 04:55 pm, Alexey Polyakov wrote: I'd try setting innodb_buffer_pool_size=8G in my.cnf Then it will run out of memory. You only get 2gig for bufferpools no matter how much more you have. -- === Jabber: tradergt@(smelser.org|jabber.org) Quote:Jesus loves me, this I know / For the Voices tell me so. === pgp8vTR4kVuQ5.pgp Description: PGP signature
