Re: space gone after MyISAM REPAIR TABLE

nixofortune Wed, 26 Jun 2013 16:31:52 -0700

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 itfrom backup, loading by LOAD DATA INFILE.The problem, yes It loaded much quicker into Database 4H 16M to beprecise, but ALTER TABLE new ADD INDEX (...);Put server into meditation mode. High I/O Wait rendered box unusable. Ihad to interrupt the ALTER ADD KEY process after 5H of struggle.

Now importing with Keys in place. It takes longer, much longer but atleast the server is working and customers do not complaint.Schema design is awful, agree. I try to understand the process so willredesign 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:vegiv...@tuxera.be]
Sent: Wednesday, June 26, 2013 11:46 AM
To: li...@netrogenic.com; Jay Ess; mysql@lists.mysql.com
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 <li...@netrogenic.com> 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

Re: space gone after MyISAM REPAIR TABLE

Reply via email to