List:General Discussion« Previous MessageNext Message »
From:nixofortune Date:June 26 2013 11:27pm
Subject:Re: space gone after MyISAM REPAIR TABLE
View as plain text  
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
>

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