Re: index creation taking too much time

["Moon's Father" <yueliangdao0608@xxxxxxxxx>]

Change the following parameter:
myisam_sort_buffer_size=300MB
Larger than here.
myisam_max_sort_file_size=10GB
Reduce this value to 30% of your real memory.

On Tue, May 13, 2008 at 7:10 PM, Ananda Kumar <anandkl@xxxxxxxxx> wrote:

> Hi Krishna,
> how do i make my index to get more key blocks
>
> On 5/13/08, Krishna Chandra Prajapati <prajapatikc@xxxxxxxxx> wrote:
> >
> > Hi anand,
> >
> > PRIMARY KEY  (`id`),
> >  KEY `KI_IDX_0805090456` (`words`,`id`),
> >  KEY `CI_IDX_0805090456` (`lf_id`)
> >
> > Since id is a primary key. Then why again indexing on id is being created
> > (`words`,`id`). It will be a duplicate index on id.  words is a varchar
> > type. So instead of creating fulltext index restrict word(15)). Try. Key
> > buffer seems to be OK.
> >
> > No free key blocks are there. This can be the reason.
> > Key_blocks_unused                 0
> >
> >
> >
> > On Mon, May 12, 2008 at 6:48 PM, Ananda Kumar <anandkl@xxxxxxxxx> wrote:
> >
> > > Hi All,
> > > We have a table which is around 100 Million rows. Its a myisam table,
> > > but
> > > the db default is innodb.
> > > CREATE TABLE `dc_data` (
> > >  `id` decimal(22,0) NOT NULL,
> > >  `words` varchar(255) NOT NULL,
> > >  `lf_id` decimal(22,0) NOT NULL,
> > >  `occurence` bigint(20) NOT NULL,
> > >  `date_modified` timestamp NULL default CURRENT_TIMESTAMP on update
> > > CURRENT_TIMESTAMP) ENGINE=MyIsam DEFAULT CHARSET=utf8
> > >
> > >
> > > indexs are as below
> > >
> > > PRIMARY KEY  (`id`),
> > >  KEY `KI_IDX_0805090456` (`words`,`id`),
> > >  KEY `CI_IDX_0805090456` (`lf_id`)
> > >
> > > we have 8 cpu, 8 gb ram.
> > > We use set below parameters at session level
> > >
> > > myisam_sort_buffer_size=300MB
> > > myisam_max_sort_file_size=10GB
> > >
> > > Each index creation is taking 10hrs, is there any way i can speed up
> > > index
> > > creation.
> > >
> > > regards
> > > anandkl
> > >
> >
> >
> >
> > --
> > Krishna Chandra Prajapati
> >
> >
>



-- 
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn