3

This is my first post on here and I'm really baffled by this. I have joined a team and somewhere in the past a horrific table was created which has been bloated to a point of 600+gb of data.

Originally this data was supposed to come in for one state, but data from the entire country has been storing here for a long period of time.

I have been put in charge of running a query to isolate a LATITUDE/LONGITUDE bounding box in this momentous data set within a certain date range.

To make things even worse, the database is located on an old server which runs on a quad p3 processor with only about 4gb of ram so it's incredibly slow.

Query I would like to execute, but for obvious reasons cringe just looking at:

INSERT INTO gpsdata.vehicle_location_wa SELECT * FROM gpsdata.vehicle_location 
WHERE LOCATION_TIMESTAMP BETWEEN '2014-03-20' AND '2014-07-20' 
AND LATITUDE BETWEEN 46.86275036 AND 47.80149551
AND LONGITUDE BETWEEN -122.44599707 AND -116.94458691;

Table structure:

CREATE TABLE `vehicle_location` (  
`SEQ_ID` decimal(10 , 0 ) default NULL,  
`DEVICE_ID` varchar(100) collate utf8_unicode_ci default NULL,  
`DATA_TYPE` varchar(80) collate utf8_unicode_ci default NULL,  
`DATA_DESC` varchar(180) collate utf8_unicode_ci default NULL,  
`SPEED` decimal(6 , 3 ) default '0.000', 
`SPEED_UOM` char(3) collate utf8_unicode_ci default NULL,  
`DIRECTION` decimal(3 , 0 ) default NULL,  
`DURATION` decimal(10 , 0 ) default '0',  
`LATITUDE` decimal(16 , 13 ) default NULL,  
`LONGITUDE` decimal(16 , 13 ) default NULL,  
`GPS_STATUS` tinyint(4) default NULL,  
`LOCATION_TIMESTAMP` datetime default NULL,  
`TIMEZONE` tinyint(1) default NULL,  
`DST` tinyint(1) default NULL,  
`STATUS` tinyint(1) default NULL,  
`MILEAGE` decimal(15 , 2 ) default '0.00',  
`QUEUE_TIME` datetime default NULL,  
`OBSERVATION_TIME` datetime default NULL,  
`ID` BIGINT NOT NULL auto_increment,  
PRIMARY KEY (`ID`),  
KEY `DEVICE_ID` (`DEVICE_ID`),  
KEY `Location_Timestamp` (`LOCATION_TIMESTAMP`),  
KEY `Data_Type` (`DATA_TYPE`)  
)  ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE = utf8_unicode_ci  

Some things I thought about doing:

  • Selecting only by date range into a new table with two extra indexes: LATITUDE and LONGITUDE [Seems like best option]
  • Moving database onto a much faster machine with loads of ram (This has its own issues, the whole database is 3.5 tb of data on a raid)
  • Adding an index to table (gasp)

I'm hoping someone with a bit more experience can help guide me in the right direction or affirm some of my thinking before I start executing queries that could take days to process.

Thank you for your help!

4
  • Which version of mysql is this, 5.5 or 5.6?
    – jynus
    Commented Sep 12, 2014 at 19:58
  • Thank you for editing my post! We're running MySQL 5.5.24. Commented Sep 12, 2014 at 20:02
  • Running a 3.5TB database on a 4GB RAM server is scary. Commented Sep 12, 2014 at 20:14
  • I agree, I think everything about this situation is scary. Up until this point it has been used simply as data dump... no queries except periodic insert. Commented Sep 12, 2014 at 20:18

1 Answer 1

2

You have a big problem for one reason: BTree indexes (which are the only format available for InnoDB) are highly inefficient for filtering on more than 1 range.

There is one thing that you must understand: in general, using more than 1 index per table access is not possible/efficient (there are some cases where union_merge is faster, but that is an exception)- in general, for AND conditions you want a single index over several fields.

So, no problem then, isn't it? We just index (LOCATION_TIMESTAMP, LATITUDE, LONGITUDE) right? Sorry, no. As you are using a range for the conditions on all the columns, only the first column of the index will be used. The reason is easy to see, but difficult to explain without drawing a tree, so I hope you can trust me on this and I can later explain you why.

There is an exception to this, which is a new feature found in 5.6 called Index condition pushdown (that doesn't solve the problem completely, but makes the 3-column index more useful than a 1-column one).

So what are the options?

  1. Convert some of the conditions to the ref type, that means, convert them into equality checks. That is something that can be done with the timestamps. It may sound a bit weird, but indexing (LOCATION_TIMESTAMP, LATITUDE) and having a query like this:

    WHERE LOCATION_TIMESTAMP IN ('2014-03-20', '2014-03-21', ..., '2014-07-20') AND 
    LATITUDE BETWEEN 46.86275036 AND 47.80149551;
    

    may actually be much faster. Of course, this depends on the selectivity of the clauses, etc.

    Can we do the same for LONGITUDE. In same cases, yes, you could create an additional field called LATITUDE_INT with floor(LATITUDE), and do something like:

    WHERE LOCATION_TIMESTAMP IN ('2014-03-20', '2014-03-21', ..., '2014-07-20') AND 
    LATITUDE_INT IN (46, 47) AND
    LATITUDE BETWEEN 46.86275036 AND 47.80149551 AND 
    LONGITUDE BETWEEN -122.44599707 AND -116.94458691;
    

    The 3 columns of the indes will be used in this case. But to be frank, that starts to become a bit crazy.

  2. Use the geospatial extensions. MySQL has support for spatial types like POINT, though precisely for geographical applications, and with its own indexes (R-trees), which can index in several dimensions at te same time -so they speed up multirange searches- and have its own, optimised set of bounding box functions (MBRContains() is what you want).

    This is great, isn't it? Bad news: it is only available for MyISAM in 5.5 and 5.6, and it will only be available for InnoDB in 5.7. The change is also not very transparent (not as easy as just add an index).

So, the fact that you have an unindexed table is possible due that your options are very narrow, specially for 5.5. If you are a bit flexible about the table structure (partitioning, as you suggested) or performing other tricks, there are ways to walkaround it, but it needs some care.

Some people end up combining MySQL with external tools precisely for this reason, and we will not have a real solution until 5.7.

Finally, there is the problem of the large table. This can become a problem. InnoDB is only efficient when most of its non-leaf parts of the primary key fits into memory. As ypercube says, a 3.5TB table on a 4GB server is something scary. Inserting to that table must be slow. You will need more ram and thinking about partitioning it, for example, as you said, by state (if that is ok for your queries) of by timestamp and get advantage of the manual or automatic prunning.

3
  • Thank you for your reply. The good news is that I only have to extract the data once and then the database can be restructured and rebuilt. Like I said I think it was poor design to begin with. I will definitely take your query advice and also the rest of it when we start building the new system. Thank you. Commented Sep 12, 2014 at 20:32
  • Can you think of a few specific things I can do to pull the data out more efficiently? I have much better direction in fixing the structures later but still having a hard time the best path to extracting the data we need now. I will certainly apply equality checks to LOCATION_TIMESTAMP. Commented Sep 12, 2014 at 20:52
  • @Dmitri Zyuzin Without changing the table structure and addind additional indexes, you will have to suffer a table scan on the LOCATION_TIMESTAMP range that you want. The only thing that you can do in that case is apply the conditions in small increments by using the primary key. That won't make the query faster, only will divide it in smaller steps.
    – jynus
    Commented Sep 12, 2014 at 21:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.