***BOGO*** Re: running optimize/analyze command

["Nagaraj S" <nagaraj.chk@xxxxxxxxx>]

Great! Partition based on Disk will helpfull for I/O on Disk?

mdm,


On 5/26/08, chandru <pradeep.chandru@xxxxxxxxxxxx> wrote:
>
> HI nagaraj,
>   You can chose any one of the partition types:
>
>  1. Range
>  2. List
>  3. Hash
>  4. Key
>
> Lets assume that you chose a Range partition
> create table t1
> ( id int NOT NULL,
> joindate datetime
> );
> If you have to delete records that are older than 2 months, then we create
> a partition that can be designed as below:
>
> create table t1
> ( id int NOT NULL,
> joindate datetime,
> PRIMARY KEY  (` joindate`)
> ) PARTITION BY RANGE (to_days(joindate))
> (PARTITION BEFOREJAN2008 VALUES LESS THAN (to_days('2008-01-01')),
> PARTITION JAN2008 VALUES LESS THAN (to_days('2008-02-01')) ,
> PARTITION FEB2008 VALUES LESS THAN (to_days('2008-03-01')),
> .
> .
> PARTITION ERROR VALUES LESS THAN (9999999));
>
> the above statement creates partition that has partitions BEFOREJAN2008 ,
> JAN2008, FEB2008 and so on. The records gets populated in the respective
> partition depending on the value that is calculated in the to_days()
> function. So to delete records that are less than Jan2008, you can drop the
> partition BEFOREJAN2008.
>
> Alter table t1 drop partition BEFOREJAN2008;
> which reduces the time of delete operation.
>
> Regards,
> Pradeep Chandru
>
>
>
> Nagaraj S wrote:
>
>> Chandru,
>>
>> How to overcome by partition, Can you explain with details/examples
>>
>> mdm,
>>
>>
>> -----Original Message-----
>>
>> From: chandru [*mailto:pradeep.chandru@xxxxxxxxxxxx*<
>> pradeep.chandru@xxxxxxxxxxxx>]
>>
>>
>> Sent: Monday, May 26, 2008 2:40 PM
>>
>> To: Alex Arul Lurthu; Ananda Kumar
>>
>> Cc: Sebastian Mendel; MySQL General
>>
>> Subject: Re: running optimize/analyze command
>>
>> Hi anand,
>>
>> To add to alex's views, I see that you do two major operations 1. Insert
>>
>> * Use a temporary table that loads the data initially and then you
>>
>> copy the same to the original table, which can prevent load on the
>>
>> original table and shall be faster as well.
>>
>> * you can disable the indexes and then insert the records into the
>>
>> table. after the insert is over you can enable the indexes.
>>
>> * try using extended inserts and increase the bulk_insert_buffer_size.
>>
>> * In case you use load data infile use that with CONCURRENT
>>
>> * Share the I/O load by having the data written across multiple discs.
>>
>>
>>
>> 2. delete
>>
>> * Please try to use partition if you can migrate that to MySQL 5.1.x
>>
>> and partition the tables based on the delete condition.
>>
>> * Since you where mentioning that there is huge volume of data that
>>
>> needs to be deleted, check that if the max_allowed_packet value is
>>
>> set to 1GB, if the selected data volume is more than 1GB try to
>>
>> split the delete query into smaller ones
>>
>> Note: confirm that indexes are available on the where clause
>>
>> Regards,
>>
>> Pradeep Chandru
>>
>>
>>
>
>
> ********** DISCLAIMER **********
> Information contained and transmitted by this E-MAIL is proprietary to Sify
> Limited and is intended for use only by the individual or entity to which it
> is addressed, and may contain information that is privileged, confidential
> or exempt from disclosure under applicable law. If this is a forwarded
> message, the content of this E-MAIL may not have been sent with the
> authority of the Company. If you are not the intended recipient, an agent of
> the intended recipient or a  person responsible for delivering the
> information to the named recipient,  you are notified that any use,
> distribution, transmission, printing, copying or dissemination of this
> information in any way or in any manner is strictly prohibited. If you have
> received this communication in error, please delete this mail & notify us
> immediately at admin@xxxxxxxxxxxx
>