Mapping site should be faster now

Over the past few weeks, I have been noticing that the site I created to allow users to plot multiple addresses on Google Maps would sometimes become very slow when a lot of people are using it. So, I started to investigate the cause.

(I am going to split this post into two sections, the first aimed at non-programmers, and the second at programmers, in case they are interested. I realize that most of the people that use my maps site probably don’t really care how it works under the hood, so the first part is for them. On the other hand, if I can share what I learned from this experience with other programmers, then they can read the whole post, focusing on the second half.)

Overview

The site uses a database to store the information about maps that people choose to save. I found that one of the database parameters was the most likely culprit for the slowness. Basically, it was optimized for a database that gets information read from it almost all the time, but when data is written to it, the whole thing locks, and doesn’t allow other people to read data from it while that write is happening.

So, now that I had found the likely cause of the issue, I started investigating how to fix it. I found that there were too methods, so I just picked the one that seemed easier. After a few hours of issuing the command to make this change, it appeared to still be working. Not sure if something was wrong, and the process stalled, I cancelled and re-issued the command. This was probably my biggest mistake. I later realized that the process just takes a long time, and I should have let it run. I basically did this a couple of times, trying the other method to make this change as well. Of course, this site is something I do in my free time, so I had to do other things during this process as well, such as go to work, sleep, and eat. In the end, once I realized that the process was working, and I should just let it run, it took about 10.5 hours.

Long story short, the site is back up, and hopefully it will be faster from here on out.

Technical Details

The parameter that I was trying to change was the MySQL storage engine. Originally I had it set as MyISAM, most likely because that was the default when I created the database. MyISAM is extremely fast when you are doing just reads from a table, but when you do a write, it will lock the entire table, preventing any other threads from access it for reading or writing. I suspect this is what was causing the performance issues with the site.

So, I decided to change it to the InnoDB storage engine. InnoDB is still fast for reads, but not as fast as MyISAM. One of the big advantages to InnoDB, though, is that it uses row-level locking, instead of table-level locking. So, if one request is adding a new address to the table, another request can be reading some other addresses. (The one that is currently being written won’t be able to be read, but it is very unlikely that someone would be trying to view a map that has not been fully created yet.)

First Approach

So, there are two main approaches to changing the database storage engine. The first one I tried, which seemed simpler at the time, was to issue the following command:

ALTER TABLE tablename ENGINE = InnoDB

I found this on Farhan Mashraqi’s site during my research, so I decided to try it first (after making a backup to the database, of course).

I restored the backup on my laptop, and decided to give it a test run. I tried it on some of the smaller tables, and it seemed to work. Since I wanted to get the upgrade done quickly, and get on with my life, I didn’t try upgrading the whole database on my laptop, and just started doing it on the production server.

This was really my biggest mistake. I should have done the whole upgrade on my laptop, start to finish, before trying to change anything on the real site. (I don’t really know why I did this; I never would have been so sloppy with a similar task at work.)

So, as I said, I started making the change on the real site. After a could of hours, it still wasn’t finished, and I noticed that in the MySQL Administrator GUI, on the Catalogs tab, the value in the ‘rows’ column was jumping all over the place. It would go up, down, and change in multiple different tables. This didn’t seem to make any sense, and led me to believe that something was wrong, and that perhaps I should restart the process. As it turns out, the row count is just bogus, and shouldn’t be trusted. But, I didn’t realize that at the time, so I restarted the process, and went to bed.

Second Approach

In the morning, I checked on it, and it was still chugging along. I figured that something must be wrong, since it was taking so long, and the row counts were still acting weirdly (I didn’t know they were bogus yet). So, I decided to try the other method of changing the storage engine, which is to take your backup file form mysqldump, and change the parts where it says ‘MyISAM’ to ‘InnoDB’, and then restore it. I decided to do this with the sed command, since this backup file is rather large, and I wasn’t sure if vim would choke on it. The following command did the trick:

sed s/MyISAM/InnoDB/ backupfile.sql > updatedbackupfile.sql

Then, I just ran that SQL script. It also took forever. I thought that perhaps the issue was that my site runs on a virtual server (from slicehost, and overall I am very happy with their service), this means that the disk i/o can sometimes be slow. It generally isn’t a problem, but it is one of the disadvantages to a virtualized server. Since what I was doing was very i/o intensive—reading a large file, doing a little bit of processing, and then writing the data out into the database, which resides on disk—I thought that perhaps this was the culprit. So, once again, I canceled the process.

The next (and final) try, I did the same thing, except I had the modified backup file that I was trying to restore on my laptop, and I restored it over the network. This meant that the server, with its virtualized disks, was only responsible for writing the data to disk, and not reading it from disk. It read the data from the network. I also learned that the row count in the MySQL Administrator GUI was bogus, and I shouldn’t worry myself with it.

A little more than 10 hours later, the database is back up and running, and I removed the maintenance message from the site!

Lessons Learned

  • Always do something first in a test environment before touching the production environment
  • Database actions that change entire large tables take a long time. Let them run
  • Always do something first in a test environment before touching the production environment
  • The row count in the MySQL Administrator GUI is bogus. Don’t even look at it.
  • Always do something first in a test environment before touching the production environment
This entry was posted in computers, gmaps.kaeding.name and tagged , , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

32 Comments

  1. Charles A Frean
    Posted December 21, 2010 at 12:54 pm | Permalink

    Patrick,
    Thank you for providing this really invaluable resource. I have two requests:
    1. I would very much like to be able to delete a saved list or, at least, be able to overwrite/update it. I have created a list of locations and sent the address of the list to friends. I want to be able to update the list periodically without having to create a new name each time.
    2. If two or more addresses are very close together, there appears to be only a single red flag indicating the last of the addresses, even though clicking on the “select” link will indicate each address appropriately. I would like to see some indication that a red flag represents more than a single address.
    Thank you again!
    Charles

  2. Posted December 21, 2010 at 2:18 pm | Permalink

    Hi Charles

    1. There is no way for users to edit or delete a map, because I have no way to identify a user as the creator of a map. This is by design, since I wanted a low-friction, anonymous way for people to create maps. I don’t want people to need to create an account, and keep track of yet another username/password, just to create a map. So, I would suggest coming up with a naming scheme, such as creating a map called ‘CharlesDec2010′, and then creating a new map next month, ‘CharlesJan2011′ (or some other scheme that would work for you).

    2. What you are seeing is actually two markers on top of each other. When I ask Google to translate the addresses into latitude/longitude coordinates, the results will sometimes come back with identical coordinates for two locations (for example, in some cities, multiple-unit houses will have multiple street addresses, but will actually be the same building). When this happens, the markers are placed on top of each other. You can only click on the top one, but, as you found, you can select both of them by clicking the ‘select’ link in the listing below the map. I will investigate a way to detect when two markers would be placed on top of each other, and combine them into a single marker, which would display both addresses when you click it. I don’t think that would be too difficult, from a technical standpoint.

  3. Sarah
    Posted January 8, 2011 at 5:22 pm | Permalink

    Just wanted to say thank you for this great resource! I’m a psych home care nurse, and I frequently cover for more than one nurse at a time. While it’s easy to follow the daily route of one nurse, when blending the clients of two or more into a workable run, it’s imperative to visualize the locations of all of the clients at once! We see 20-25 people before noon, and backtracking takes up valuable time!

  4. Posted January 9, 2011 at 1:33 am | Permalink

    Sarah, wow, that is a lot of traveling around. I’m glad you found my site useful!

  5. Gary Smith
    Posted January 17, 2011 at 5:17 am | Permalink

    Just found this site and love this app.
    Just one question – Would it be possible to get directions from one marker to another from the list?
    If I list a few postcodes/zipcodes then it would be nice to get directions or at least a distance between two points. I know nothing about how the API works though :-)

  6. Posted January 17, 2011 at 11:58 am | Permalink

    Hi Gary

    That is an interesting idea. It would be very difficult to find the shortest route that would allow you to visit all of your points mapped (if there was a large number of points), but it would certainly be possible to allow the user to pick two points at a time from the map, and get directions between them.

    I will add this to my list of enhancements that I hope to add when I have time to work on it.

  7. John Maclaren
    Posted March 24, 2011 at 9:53 pm | Permalink

    It seems to me that Gary needs a route planning program: as used by breweries delivering to orders to pubs to plot the shortest routes for the whole delivery round. I remember these being developed (at English Electric Computers, Kidsgrove in UK) back in 1963. That was all mainframe and batch processing and way before the advent of SatNav, but I’m sure there are modern algorithms in applications for PCs by now.
    Cheers
    John Maclaren

  8. Posted March 24, 2011 at 10:39 pm | Permalink

    Indeed, there are systems out there that can calculate routes. The trouble is that as you increase the number of locations, it becomes exponentially more complex to find the shortest route that will allow you to visit them all. In computer science, this is known as the Traveling Salesman Problem, and it is actually one of the most studied problems in the field of optimization. It is not too difficult of a problem with a small number of locations, but as the number of locations to visit grows, it becomes quite difficult. That isn’t to say there isn’t a market for this sort of computation, but I guess the market is relegated to those who can afford mainframes to be able to calculate a shortest route to a large number of pubs!

  9. k2murray
    Posted March 31, 2011 at 6:14 am | Permalink

    Is there a way to let users specify a color for a marker? Something like
    address 1, city1, XX [red]{extra notes}
    address 2, city2, XX [green]{extra notes}

    Thanks.

  10. Posted March 31, 2011 at 7:15 am | Permalink

    No, at this point, there is no way to specify different markers for different points. This is a feature I am considering adding, when I have the time, though.

    Thanks!

  11. Danny Whittaker
    Posted April 28, 2011 at 9:23 am | Permalink

    This is cool, thanks!

    I’m working on a project regarding a tour of the National Parks. I’m trying to create a map that shows the sequence of a trip (I’ve already worked out the sequence.) Looks like it isn’t possible to have the markers be numerical, but that would be cool.

    As to the traveling salesman problem. Yes, it does take a long time to run with 46 stops, ran for about 12 hours to get the solution!

  12. Posted April 28, 2011 at 9:48 am | Permalink

    I’m glad you like the site! Having numbered markers is on the top of my TODO list for enhancements to the site, so keep an eye out (or watch this blog) for when I have some free time to work on it.

  13. Posted June 29, 2011 at 4:20 am | Permalink

    Hey Patrick, That’s a nice job you did. You work really helped me. I discovered your website at a time when I needed it most. I am actually working on a project that would plot multiple points scaling up to millions. Actually I tried plotting a mere number of 2000 points using the resource on your website but I observed that on some browsers the whole process timed out (eg. chrome). But in Firefox, I was given an option to allow the continued execution of the script. It took some sometime but finally I got the points plotted. The next hitch I experienced was scaling the map, especially when points plotted are at close proximity to each other so that I can visually see how apart they are. Thanks to my CSS knowledge and the Ctrl-/Ctrl+ shortcuts I was able adjust the size of the displaying div and the scale the map before I could get the kind of picture I was expecting.
    Now I don’t know whether this project is open source but I would like to work on it and make the graph plotting scalable considering the fact that I will be plotting millions of points on the map at the same time and I am expected to get results as fast as possible without needing to adjust the browser.
    We can also collaborate using what is on ground to make plotting scalable and fast. Expecting to hear from you.
    Cheers!

  14. Posted June 29, 2011 at 7:20 am | Permalink

    Hi Kenny

    Yeah, the browser will choke if you have a really large number of points all on the same map. The most common solution is to show only one point at the farther out zoom levels to represent multiple nearby points. Then, when you zoom in, you can replace the one marker with the individual markers (but other, farther-away markers aren’t shown, since the map is zoomed in).

    My code is not open-sourced, but I will consider tweaking it so that the timeouts don’t happen. It still won’t load quickly for maps with a massive number of points, though. For that, you really need a custom solution tailored to your specific use-case.

    Good luck!

  15. Posted July 20, 2011 at 5:42 am | Permalink

    Congratulations!

    Very good thinking with the map.
    Made my job easier.

    Any change to build a reporting tool for Google Places? Need to export various stores data in excel, function which is not yet provided by Google.

  16. Posted July 20, 2011 at 7:34 am | Permalink

    HI Sergiu

    I am not really familiar with Google Places, but if you have the addresses in Excel already, you could try:

    • Arrange the columns in this order: Address, City, State, Postal Code, decsription (the description is optional, and could in fact be multiple columns.) Hide or remove all other columns.
    • Insert a column before the description.
    • Type a ‘{‘ character in the first row of the new column, and drag it down to fill in all the other rows of that column, so that each cell in that column has a ‘{‘ in it.
    • Add a column after the description, and fill it with a ‘}’ character in a similar manner.
    • Select all of these cells (the whole address, the curly-braces, and the description), and copy/paste it into the text area on my page. This will result in all of your addresses in the format that my site expects. Don’t worry if it looks funny, with lots of space in between the values; my site will ignore that space.
    • If you have a large number of locations to plot, it may take a long time for the map to generate the first time, since it needs to look up the latitude/longitude of each address, but it will load faster on subsequent times.

    Let me know how that goes!

  17. Stephan
    Posted August 18, 2011 at 10:47 am | Permalink

    Hey. Good tool to have around. I myself deal a lot with mapping and your site was quite useful in some maintenance tasks. Two things i’d like to suggest: add an option to disable address resolving of LatLong coordinates. It takes way too long. I mapped around 200 coordinates and had to wait over 20 seconds for the result.
    Secondly, enable the wheel for zooming. It’s just a simple option on the map initialization.
    BTW, I clicked on both ads, you deserve it :)

  18. Stephan
    Posted August 18, 2011 at 10:51 am | Permalink

    Oh, I just realized after posting the previous comment. All the coordinates I plotted are way off because they are resolved to addresses. I am not using the maps to plot street addresses. I need the icons to be exactly on the coordinates. Would be a nice thing if you could add that option not to resolve addresses.

  19. Posted August 18, 2011 at 2:36 pm | Permalink

    Hi Stephan

    Thanks for your feedback; I’m glad you like the site! Enabling the zoom wheel is a good idea, I will do that the next time I update the code. Detecting when a user entered coordinates directly (as opposed to an actual address) is an idea that has been suggested before, but I’ve been hesitant to do it because I’m not sure how many people would use that feature. I suppose it wouldn’t be too difficult, technically, though, so even if only a few people used it, it is worth adding.

    Thanks!

  20. CAROL
    Posted November 2, 2011 at 1:26 pm | Permalink

    Thank you for gmaps – we use it to route our trucks and love it – however lately it’s not been working — seems not to be able to find addresses therefore it won’t map all the addresses – thought at first that it was just a glitch in out makeup, but after a week of it not working, thought we would pass it along to you to see if you had any ideas.

  21. Posted November 2, 2011 at 10:56 pm | Permalink

    Hi Carol

    I am not aware of any reason why it shouldn’t be working, and it just worked for me for a test address, but I will follow up with you via email to see if we can straighten it out. There have actually been a couple of people emailing me about issues like this lately, so hopefully I can get to the bottom of it.

    -Patrick

  22. Posted November 2, 2011 at 11:05 pm | Permalink

    Hi Carol

    I am not aware of any reason why it shouldn’t be working, and it just worked for me for a test address, but I will follow up with you via email to see if we can straighten it out. There have actually been a couple of people emailing me about issues like this lately, so hopefully I can get to the bottom of it.

    -Patrick

  23. Michael
    Posted November 26, 2011 at 8:07 pm | Permalink

    You are a God.

    Saved my butt.

    I just got a new job photographing pre-foreclosures. They give me 40 a day. There is no Streets and Maps version for Mac. Which means I would have had to eyeball and hand sort these addresses.

    I remember the travelling salesman problem from CSE (Computer Science Engineering). As good as i am with the streets of Chicago, this wasnt happening

    I am able to copy paste the addresses from excell, map my 40 points, eyeball the on the map, and quickly copy paste the 40 addresses into a word file, in order of which I should visit

    Thank you. Thank you. Thank you!

    If I wasnt so broke, I would donate. Or obviously, I wouldnt be driving my old car car around to snap pictures for $3.25 ea. As soon as I am not so broke, you can def count on a donation!!!

    Only one request, can you make the map space bigger???? . . vertically? Even though all of my points are only within a 9 sq mile radius, I can only print the map in the window you have created. If i zoom in to get more resolution and street names/street arrow directions, the top and bottom get cut off

    Thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  24. Adam
    Posted March 19, 2012 at 5:36 pm | Permalink

    Yo dude. . . I must say, I have used your map multiple locations, but the last two times I try, it can’t seem to map anything. Only the adresses come up as ‘points not mapped’. I put them in exactly the same way as before and no luck :( your map site rocks, but it doesn’t seem to work anymore :(

  25. yuri
    Posted April 2, 2012 at 12:18 pm | Permalink

    For some reason I cannot map new addresses – my old saved map still works (http://gmaps.kaeding.name/saved/Nurseries%20Around%20Indio), and when I paste these old addresses or any subset of them, they work too. But for the new set of addresses I get a response

    Points not mapped. I could not interpret these addresses. You can try again below.

    It doesn’t matter which new addresses I use, I cannot map any of them, same error… pretty bizarre

    Here is my new set:

    23717 S Hawthorne Blvd Torrance CA 90505 { 588–800.00 660–889.90 }
    23133 Hawthorne Blvd Torrance CA 90505 { 456–501.60 474–521.40 525–577.50 717–788.70 }
    3711 Lomita Blvd Torrance CA 90505 { 607–940.85 }
    3812 Sepulveda Blvd Torrance CA 90505 { 553–989.87 }
    3825 Del Amo Blvd Torrance CA 90503 { 365–657.00 }
    3828 Carson St Torrance CA 90503 { 406–832.30 }
    22330 Hawthorne Blvd Torrance CA 90505 { 652–815.00 }

    And this is a subset of the old addresses:

    21425 Sky Ridge Ave Desert Hot Springs CA 92241 { SKY NURSERY (760) 251-3257 }
    72235 Painters Path Palm Desert CA 92260 { MOLLERS GARDEN CENTER (760) 346-0545 }
    45350 San Luis Rey Ave Palm Desert CA 92260 { LOTUS GARDEN CENTER (760) 404-1949 }
    40595 Carter Ln Bermuda Dunes CA 92203 { CORONEL EZEQUIEL (760) 200-9305 }
    78790 Darby Rd Bermuda Dunes CA 92203 { ALL COLOR GROWERS NURSERY (760) 200-5747 }

    The format is exactly the same. I tried saving as plain text, saving as excel tab separated values, removing comments – no difference.

    Any ideas?

    Thanks in advance, it’s a wonderful tool

  26. Carey Snyder
    Posted April 13, 2012 at 6:47 am | Permalink

    I really appreciate your site – I use it to visualize locations of polling places for pickups and deliveries of election equipment and then use findthebestroute.com to find the most efficient routes. If you guys could collaborate you would really have a marketable product…I’ve come up with some ways to extract addresses from a spreadsheet and feed them to google’s route mapping function via the URL, but your work has save me many steps. The improvements that I see that would really enhance it is a printing function, other than right-clicking and printing, where you could get a full page map, and the numbering or color coding of the markers. Thanks again for all your work!

  27. Posted April 13, 2012 at 7:42 am | Permalink

    Hi Adam

    This happens sometimes when the site is busy, and has reached its quota for looking up new addresses in Google. Old addresses that have already been looked up by the site will continue to work, but new ones will fail. I am working on a fix for this, but I’m not sure when it will be ready.

    In the meantime, you can keep trying to plot your addresses. You may have better luck in the mornings or evenings, when the site is less busy. Once an address is plotted successfully, though, it will continue to work, so if you create a saved map, you can give out the address, and people will be able to access it.

  28. Posted April 13, 2012 at 7:44 am | Permalink

    Hi Yuri

    I just tried your new addresses, and they plotted successfully. Sometimes, when the site is busy, it reaches its quota from Google for looking up new addresses, and the lookups fail. Trying again later usually works. You may have the best luck either early in the morning or later in the evening, when the site is less busy.

    Thanks!

  29. Posted April 13, 2012 at 7:46 am | Permalink

    Hi Carey

    Thanks, I’m glad you like it! Numbering the markers is the top of my to-do list for enhancements to the site. I will also think about printing options.

  30. Vincent
    Posted September 26, 2012 at 5:47 am | Permalink

    Love your site mate,
    very slick, easy and for all extremely useful!!! I travel a lot and plotting multiple locations ({with names} !!!) is a must. Thanks a lot.

    Suggestion: could you make it possible to select multiple location as well? So you can plot and see the label of multiple locations!! (great for creating printouts!)

  31. Dee Vara
    Posted January 29, 2013 at 7:25 pm | Permalink

    Hi Patrick,
    I must say that your work is amazing. You have been a great help!!

    I have a question, is it possible to send a group of address to your map as an url address?

    I have been developing an access database for a volunteer community service group and at the moment I have a web control that can find one address on google by sending it the address in the form of:
    “http://maps.google.com.au/maps?q=Address+Suburb+State&iwloc&output=embed”

    Is there a way to do something similar with a group of address to your map?

    Many thanks

  32. Posted March 8, 2013 at 8:08 pm | Permalink

    Hi Dee

    It is possible, try an address like:

    http://gmaps.kaeding.name/cgi-bin/map.cgi?addrs=FIRST+ADDRESS % 0 D % 0 A SECOND+ADDRESS (remove the spaces in the middle; I needed to add them because the blog was trying to encode them.)

    Be aware that different browsers have different limits on the length of the URL, so if you have many addresses, you won’t be able to use it like this.

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>