MySQL from table to table data transfer

Question

I've got some problem with my MySQL. I have the database with many many data. Well, there is only one table actually with huge ammount of data, over 1 billion rows. For some reasons I need to create duplicate of this table, but I need to make it with different indexes and make it with partitions. But field structure and data must stay the same.

So I decided to make new table like this:

CREATE TABLE `geodata` (
  `trackerId` smallint(5) NOT NULL,
  `time` int(10) unsigned NOT NULL,
  `sats` tinyint(2) unsigned NOT NULL,
  `lat` float(9,6) NOT NULL,
  `lon` float(9,6) NOT NULL,
  `course` smallint(3) NOT NULL,
  KEY `geodataTrackerID` (`trackerId`),
  KEY `geodataTime` (`time`),
  KEY `geodataLat` (`lat`),
  KEY `geodataLon` (`lon`),
  KEY `geodataCourse` (`course`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( `time`)
(PARTITION p2014m01 VALUES LESS THAN (1388523600) ENGINE = InnoDB,
 PARTITION p2014m02 VALUES LESS THAN (1391202000) ENGINE = InnoDB,
 PARTITION p2014m03 VALUES LESS THAN (1393621200) ENGINE = InnoDB,
 PARTITION p2014m04 VALUES LESS THAN (1396296000) ENGINE = InnoDB,
 PARTITION p2014m05 VALUES LESS THAN (1398888000) ENGINE = InnoDB,
 PARTITION p2014m06 VALUES LESS THAN (1401566400) ENGINE = InnoDB,
 PARTITION p2014m07 VALUES LESS THAN (1404158400) ENGINE = InnoDB,
 PARTITION p2014m08 VALUES LESS THAN (1406836800) ENGINE = InnoDB,
 PARTITION p2014m09 VALUES LESS THAN (1409515200) ENGINE = InnoDB,
 PARTITION p2014m10 VALUES LESS THAN (1412107200) ENGINE = InnoDB,
 PARTITION p2014m11 VALUES LESS THAN (1414789200) ENGINE = InnoDB,
 PARTITION p2014m12 VALUES LESS THAN (1417381200) ENGINE = InnoDB,
 PARTITION p2015m01 VALUES LESS THAN (1420059600) ENGINE = InnoDB,
 PARTITION p2015m02 VALUES LESS THAN (1422738000) ENGINE = InnoDB,
 PARTITION p2015m03 VALUES LESS THAN (1425157200) ENGINE = InnoDB,
 PARTITION p2015m04 VALUES LESS THAN (1427832000) ENGINE = InnoDB,
 PARTITION p2015m05 VALUES LESS THAN (1430424000) ENGINE = InnoDB,
 PARTITION p2015m06 VALUES LESS THAN (1433102400) ENGINE = InnoDB,
 PARTITION p2015m07 VALUES LESS THAN (1435694400) ENGINE = InnoDB,
 PARTITION p2015m08 VALUES LESS THAN (1438372800) ENGINE = InnoDB,
 PARTITION p2015m09 VALUES LESS THAN (1441051200) ENGINE = InnoDB,
 PARTITION p2015m10 VALUES LESS THAN (1443643200) ENGINE = InnoDB,
 PARTITION p2015m11 VALUES LESS THAN (1446325200) ENGINE = InnoDB,
 PARTITION p2015m12 VALUES LESS THAN (1448917200) ENGINE = InnoDB,
 PARTITION p2016m01 VALUES LESS THAN (1451595600) ENGINE = InnoDB,
 PARTITION p2016m02 VALUES LESS THAN (1454274000) ENGINE = InnoDB,
 PARTITION p2016m03 VALUES LESS THAN (1456779600) ENGINE = InnoDB,
 PARTITION p2016m04 VALUES LESS THAN (1459454400) ENGINE = InnoDB,
 PARTITION p2016m05 VALUES LESS THAN (1462046400) ENGINE = InnoDB,
 PARTITION p2016m06 VALUES LESS THAN (1464724800) ENGINE = InnoDB,
 PARTITION p2016m07 VALUES LESS THAN (1467316800) ENGINE = InnoDB,
 PARTITION p2016m08 VALUES LESS THAN (1469995200) ENGINE = InnoDB,
 PARTITION p2016m09 VALUES LESS THAN (1472673600) ENGINE = InnoDB,
 PARTITION p2016m10 VALUES LESS THAN (1475265600) ENGINE = InnoDB,
 PARTITION p2016m11 VALUES LESS THAN (1477947600) ENGINE = InnoDB,
 PARTITION p2016m12 VALUES LESS THAN (1480539600) ENGINE = InnoDB,
 PARTITION p2017m01 VALUES LESS THAN (1483218000) ENGINE = InnoDB,
 PARTITION p2017m02 VALUES LESS THAN (1485896400) ENGINE = InnoDB,
 PARTITION p2017m03plus VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

The old table is the same, but without partitions and key indexes. It only has primary key for all fields in it.

So I created new table 'geodata', then I started insert query like this:

insert into geodata (trackerId,`time`,sats,lat,lon,course) SELECT * FROM 
`trackers`.`maindata` where `maindata`.`time` >= UNIX_TIMESTAMP('2013-05-01')

The problem is that this query takes sooo much time so I started to question if it is working. I looked process list, it's in 'Sending data' status. I also looking CPU usage by mysql, looks like it working, but... It's running more than 6 days! So I really starting to worry about it.

Is there any ways to look for query progress somehow? Or maybe there is any other way to create table duplicate like this, better and quicker?

Answer 1

To answer your first question about monitoring there are several options.

1. Slow query log (included with MySQL)

2. innotop (free)

3. Jet Profiler (free and enterprise version)

As to your second question, my first thought on optimizing the import would be to create the indexes after you import the data to reduce writes

Create the geodata table

CREATE TABLE `geodata` (
  `trackerId` smallint(5) NOT NULL,
  `time` int(10) unsigned NOT NULL,
  `sats` tinyint(2) unsigned NOT NULL,
  `lat` float(9,6) NOT NULL,
  `lon` float(9,6) NOT NULL,
  `course` smallint(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Then

INSERT DATA

INSERT INTO goedata (trackerid, time, stats, lat, lon, course) values (1,.....)

Then

Create your indexes

CREATE INDEX idx_geodataTrackerID on geodata(trackerId);
CREATE INDEX idx_geodataTime on geodata(time);
CREATE INDEX idx_geodataLat on geodata(lat);
CREATE INDEX idx_geodataLon on geodata(lon);
CREATE INDEX geodataCourse on geodata(course);

It is usually faster to create indexes after data is imported.

https://stackoverflow.com/questions/3688731/is-it-better-to-create-an-index-before-filling-a-table-with-data-or-after-the-d

Craig