0

I am trying to optimise a MySQL script that I have created. Here is a bit of background on the project.

We have 2 system which we are now merging into one system, I am currently writing the scripts to merge the databases together. I need this to run as fast as possible, while this script is running we have to shut down the systems and we don't want too much down time.

Both systems have a table called venue, zone and device_vendor which are identical they just have different data. When I merged the tables together obviously the IDs for the values in those tables have changed and they are foreign keys in a lot of other tables. The table I am currently working on has 1.7 billion records and all has the values venue_id, zone_id and device_vendor_id so I have to change these values to the new IDs.

Here is the script that I have written to change the IDs to the new values. All these values are also indexes which is why it is taking so long.

INSERT INTO `intelli_sense`.`tracking_daily_stats_zone_unique_device_uuids_per_hour` (day_epoch, day_of_week, hour, venue_id, zone_id, device_uuid, device_vendor_id, first_seen, last_seen, is_repeat)

SELECT day_epoch, day_of_week, hour, (CASE WHEN intelli_sense_venue.id!=0 THEN intelli_sense_venue.id ELSE 0 END), (CASE WHEN intelli_sense_zone.id!=0 THEN intelli_sense_zone.id ELSE 0 END), device_uuid, (CASE WHEN intelli_sense_device_vendor.id!=0 THEN intelli_sense_device_vendor.id ELSE 0 END), first_seen, last_seen, is_repeat
FROM geo_sense.daily_stats_zone_unique_device_uuids_per_hour AS tracking_daily_stats_zone_unique_device_uuids_per_hour
LEFT JOIN geo_sense.venue AS tracking_venue ON tracking_venue.id = tracking_daily_stats_zone_unique_device_uuids_per_hour.venue_id
LEFT JOIN intelli_sense.venue AS intelli_sense_venue ON intelli_sense_venue.name = tracking_venue.name
LEFT JOIN geo_sense.zone AS tracking_zone ON tracking_zone.id = tracking_daily_stats_zone_unique_device_uuids_per_hour.zone_id
LEFT JOIN intelli_sense.zone AS intelli_sense_zone ON intelli_sense_zone.name = tracking_zone.name AND intelli_sense_zone.lat = tracking_zone.lat AND intelli_sense_zone.lon = tracking_zone.lon
LEFT JOIN geo_sense.device_vendor AS tracking_device_vendor ON tracking_device_vendor.id = tracking_daily_stats_zone_unique_device_uuids_per_hour.device_vendor_id
LEFT JOIN intelli_sense.device_vendor AS intelli_sense_device_vendor ON intelli_sense_device_vendor.name = tracking_device_vendor.name AND intelli_sense_device_vendor.description = tracking_device_vendor.description;

As you can see from the script above I have used LEFT JOINS to update these values. I have basically imported both databases into one server and now I am merging them together to create a new database called intelli_sense.

In the script above this isn't merging the 2 databases together it is just updating the values in the tracking_daily_stats_zone_unique_device_uuids_per_hour table which is from the geo_sense database because after merging the venue, zone and device_vendor tables the IDs have changed. I need to update all the tables which are related to those tables.

I have ran this script and it took 3 days to insert 50mil records which is way to long so I stopped it.

I need this script to finish in less then a day because I have 3 tables with this amount of data and I don't want too much system downtime.

I can't share the data since there is so much so I hope that script explains it enough, the actual script works its just a case of speeding it up. The data is also personal data so I wouldn't be able to share it anyway.

If you don't think this is impossible let me know and I will find a new way to do it while leaving both systems on so it can take as long as it wants.

Thanks in advance.

EDIT: Here is the explain statement for the above query. Explain Statement

You may notice a lot of ALLs in the type but I believe this is due to joining on the name. I have to do that because I no longer know what the IDs are since this is the reason I am using the LEFT JOINs to change them values.

2
  • You mention "All these values are also indexes which is why it is taking so long" which means what exactly? You have a lot of indexes on the table you're inserting into? If this is the case, and you know the index maintenance is slowing you down, have you thought about dropping the indexes, doing the insert(s), and then creating the indexes? If this would be an issue ... can you provide more details on your indexing strategy?
    – markp-fuso
    Nov 16, 2018 at 13:05
  • @markp I havent thought about dropping the indexes but that might be a good thing to try and see how long it takes. I will give it a go and see how fast that runs. If you have any other suggestions just let me know. Nov 16, 2018 at 13:07

1 Answer 1

0

Several thoughts and requests. (Not yet a complete answer.)

Please provide SHOW CREATE TABLE, at least for daily_stats_zone_unique_device_uuids_per_hour. How many partitions in that table.

I assume it is practical and possible to migrate a few rows at a time, as long as we don't mess up the ids, either the old ids or the new ids.

We should walk through the main table, 1000 rows at a time, probably by the PRIMARY KEY. (I may choose to modify this advice after seeing the CREATE TABLE, especially the partition definition.)

It would involve finding all the ids and changing them for the batch. Possibly add a "sleep" between iterations.

Do the batch as a single transaction. -- The time it takes for the transaction to run becomes the length of the "downtime". Adjust the "1000" and the "sleep" time to avoid interfering too much with the rest of the system.

1.7 B rows in that table? What about the other tables?

A technique that may be useful: Adjust all the ids by N, where N is big enough to "shift" all the values to a currently-unused range of values in the ids.

I see UUIDs. How pervasive are they? How big is innodb_buffer_pool_size? How much RAM? How big (in GB) is the dataset? I ask these because UUIDs are notoriously inefficient when the index becomes bigger than can be cached; I worry that you are already there. The first thing to help with this will be to change the 1000 to, say, 100. This is because (in the 'worst' case) nearly every read will have to go to disk because of the randomness of UUIDs.

You say there are a bunch of indexes; I assume they are not UNIQUE?

In the SELECT...

Isn't (CASE WHEN id!=0 THEN id ELSE 0 END) the same as IFNULL(id, 0)?

7 tables are mentioned, we can't see which tables some of the SELECT values come from (eg first_seen). Do you really need all the LEFT JOINs?

What is the relevance of the SELECT to the merging of two tables? Isn't there an issue of shifting some ids?

More discussion of chunking: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

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.