List:General Discussion« Previous MessageNext Message »
From:Divesh Kamra Date:June 27 2013 4:02am
Subject:Re: space gone after MyISAM REPAIR TABLE
View as plain text  
You can use "disable Key" before loading data & use "enable Key " after loading 

Command :- alter table {table. Name } disable key;

Same enabling 

DK Sent from Phone

On 27-Jun-2013, at 4:57, nixofortune <nixofortune@stripped> wrote:

> This is my table:
> CREATE TABLE `ga_monthly_keyword_visits` (
>  `site_id` int(11) DEFAULT NULL,
>  `index_date` int(11) DEFAULT NULL,
>  `index_month` int(11) NOT NULL,
>  `index_year` int(11) NOT NULL,
>  `keyword` varchar(128) DEFAULT NULL,
>  `source` varchar(30) DEFAULT NULL,
>  `visits` int(11) DEFAULT NULL,
>  `bounced_visits` int(11) DEFAULT NULL,
>  `transactions` int(11) DEFAULT NULL,
>  `revenue` float(10,2) DEFAULT NULL,
>  `value_per_click` float(10,2) DEFAULT NULL,
>  `conversions` int(11) DEFAULT NULL,
>  `goal_value` float(10,2) DEFAULT NULL,
>  KEY `idx_bounced_visits` (`site_id`,`index_date`),
>  KEY `idx_visits_month` (`site_id`,`keyword`,`index_month`,`index_year`)
> ) ENGINE=MyISAM     DEFAULT CHARSET=utf8
> 
> Was changed into:
> 
> CREATE TABLE `ga_monthly_keyword_visits` (
>  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
>  `site_id` int(11) DEFAULT NULL,
>  `index_date` int(11) DEFAULT NULL,
>  `index_month` int(11) NOT NULL,
>  `index_year` int(11) NOT NULL,
>  `keyword` varchar(128) DEFAULT NULL,
>  `source` varchar(30) DEFAULT NULL,
>  `visits` int(11) DEFAULT NULL,
>  `bounced_visits` int(11) DEFAULT NULL,
>  `transactions` int(11) DEFAULT NULL,
>  `revenue` float(10,2) DEFAULT NULL,
>  `value_per_click` float(10,2) DEFAULT NULL,
>  `conversions` int(11) DEFAULT NULL,
>  `goal_value` float(10,2) DEFAULT NULL,
>  PRIMARY KEY (`id`)
>  KEY `idx_bounced_visits` (`site_id`,`index_date`),
>  KEY `idx_visits_month` (`site_id`,`keyword`,`index_month`,`index_year`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
> 
> I tried Converting like this:
> 
>   CREATE TABLE new LIKE old;
>   ALTER TABLE new DROP (all indexes), ADD PRIMARY KEY (either autoincrement or a
> 'natural' compound PK), ENGINE=InnoDB;
>   INSERT INTO new SELECT site_id, ..., goal_value FROM old;
>   ALTER TABLE new ADD INDEX (...);
> 
> With only difference The original MyISAM table crashed and I took it from backup,
> loading by LOAD DATA INFILE.
> The problem, yes It loaded much quicker into Database 4H 16M to be precise, but ALTER
> TABLE new ADD INDEX (...);
> Put server into meditation mode. High I/O Wait rendered box unusable. I had to
> interrupt the ALTER ADD KEY process after 5H of struggle.
> 
> Now importing with Keys in place. It takes longer, much longer but at least the
> server is working and customers do not complaint.
> Schema design is awful, agree. I try to understand the process so will redesign it
> soon, but any suggestions are welcome.
> I' not a MySQL super guru so will be glad for hear your sorts, guys.
> Thanks
> 
> 
> On 27/06/13 00:04, Rick James wrote:
>>>
> (`site_id`,`index_date`,`index_month`,`index_year`,`keyword`,`source`,`visits`,
> `bounced_visits`,`transactions`,`revenue`,`value_per_click`,`conversions`,`goal_value`);
>> May we see the SHOW CREATE TABLE?  Some of this smells bad.
>> * It is almost always bad to split day/month/year into multiple fields.
>> * Often a "fact" table, which this sounds like, should not have extra indexes.
>> * Is each datatype as small as is practical?
>> * Are any of the fields VARCHAR, and could be 'normalized'?
>> 
>> I would expect this to the fastest way to convert (assuming you have the disk
> space):
>>    CREATE TABLE new LIKE old;
>>    ALTER TABLE new DROP (all indexes), ADD PRIMARY KEY (either autoincrement or a
> 'natural' compound PK), ENGINE=InnoDB;
>>    INSERT INTO new SELECT site_id, ..., goal_value FROM old;
>>    ALTER TABLE new ADD INDEX (...);
>> 
>> What version of MySQL are you running?  Newer versions do the ALTER TABLE faster
> (online??), and may require you to do one add at a time.
>> 
>> Another issue...
>> If the data in `old` is in the same order as the PRIMARY KEY of `new`, then
> INSERT..SELECT will run fast.  (No need to jump around to find where to put each row.)
>> Case 1:  You are adding an AUTO_INC -- it will be in the 'right' order.
>> Case 2:  The new PK is approximately the order of the insertions into `old` --
> probably run fast.  (However, I do not see a likely natural PK that would allow this
>> 
>> INSERT ... SELECT...ORDER BY (new PK) -- This would make the INSERT part fast,
> but the SELECT part would be slow.  ("You can't win")
>> 
>> Your task is all about disk hits.  By understanding what MySQL has to do, you can
> 'predict' whether a plan will be "slow" or "slower".
>> 
>> Back to the secondary indexes...
>> What are the SELECTs that will benefit from them?  (Sometimes discussing this can
> lead to fewer/better INDEXes.  Often it leads to suggesting Summary Table(s).)
>> 
>> 
>>> -----Original Message-----
>>> From: Johan De Meersman [mailto:vegivamp@stripped]
>>> Sent: Wednesday, June 26, 2013 11:46 AM
>>> To: lists@stripped; Jay Ess; mysql@stripped
>>> Subject: Re: space gone after MyISAM REPAIR TABLE
>>> 
>>> You can't actually move innodb tables around until 5.6 where you have
>>> transpotable tablespaces.
>>> 
>>> I suggest having a good hard look at pt-online-schema-change or
>>> whatsitcalled.
>>> 
>>> Jay Ess <lists@stripped> wrote:
>>>> On 2013-06-26 18:31, nixofortune wrote:
>>>>> What would be the best way to convert BIG MyISAM table into InnoDB?
>>>> We do not
>>>>> have SLAVE.
>>>> I would do it on another computer. Then copy the table to the server
>>>> and then add the data that has been added from the original table.
>>>> 
>>>> And/or i would experiment with TokuDB. I havent had the time to do it
>>>> myself but will probably soon. I am too looking for a lengthy 1
>>>> billion+ row conversion.
>>> --
>>> Sent from Kaiten Mail. Please excuse my brevity.
>>> 
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:    http://lists.mysql.com/mysql
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
> 
Thread
space gone after MyISAM REPAIR TABLEnixofortune24 Jun
  • Re: space gone after MyISAM REPAIR TABLEJohan De Meersman24 Jun
    • Re: space gone after MyISAM REPAIR TABLEReindl Harald24 Jun
      • Re: space gone after MyISAM REPAIR TABLEJohan De Meersman24 Jun
      • Re: space gone after MyISAM REPAIR TABLEnixofortune24 Jun
        • RE: space gone after MyISAM REPAIR TABLERick James25 Jun
          • Re: space gone after MyISAM REPAIR TABLEnixofortune26 Jun
            • Re: space gone after MyISAM REPAIR TABLEJay Ess26 Jun
              • Re: space gone after MyISAM REPAIR TABLEJohan De Meersman26 Jun
                • RE: space gone after MyISAM REPAIR TABLERick James26 Jun
                  • Re: space gone after MyISAM REPAIR TABLEnixofortune26 Jun
                    • Re: space gone after MyISAM REPAIR TABLEJay Ess27 Jun
                    • Re: space gone after MyISAM REPAIR TABLEDivesh Kamra27 Jun
            • Re: space gone after MyISAM REPAIR TABLEhsv26 Jun