The Importance of Good Indices

On the Importance of Good Indices

The beginning of a new school year is always a busy time. But these last three years I have found myself holding my breath and watching Google’s real time analytics with more than a bit of nervousness. The charts below should give you a good idea why.

I'm using the newly renamed Jupyter notebooks feature to show off some data from the website over the past few years, as well as to get more practice with Pandas and some of its graphing capabilities.

In [1]:
%matplotlib inline
import pandas as pd
import seaborn as sbn

This first chart shows the number of daily sessions, starting from when we launched in June of 2012 until just this last week. Although the labels on the x axis are two close together to read you get the idea. We see stead growth each semester with lulls in the summer. The beginnings of the traffic increase fo this fall are just starting to show. It looks like we are going to see significant growth over last spring once again this fall.

In [53]:
df = pd.read_csv('Data/Analytics Audience Overview 20120601-20150911.csv')
df['day'] = pd.to_datetime(df['day'], coerce=True)
df.index = df.pop('day')
<matplotlib.axes._subplots.AxesSubplot at 0x11089b3c8>

Organic growth can lead to bad design

Because Runestone Interactive is both a research project as well as a production site for many schools around the world, we haven't always been the most careful about coordinating new features, or thinking about scalability. Many features are conceived of and implemented for small classes done in isolation. But when you combine them and serve them on a site growing like this can lead to problems.

Specifically, over the last few days I have figured out that every page that is loaded makes the api calls to updatelastpage as well as getCompletionStatus. These two separate API calls may be handled by different processes on the server. They both update and read the same two tables... This is a classic deadlock scenario, and although we haven't really had too many problems with that in the past, I started noticing deadlock messages last spring. On Thursday of this last week it got really bad, and it finally hit me why.

The following graph shows the average time for the updatelastpage API call averaged over a month starting back in February 2014 when I started to collect daily data about API calls. February 2014 corresponds to the last crisis in scalability :-)

In [55]:
uplp = pd.read_table('Data/updatelastpage',sep='\s+',header=None)
uplp.columns = ['day','useless','count','avgtime','maxtime'] = pd.to_datetime(,coerce=True)
uplp.index = uplp.pop('day')
<matplotlib.axes._subplots.AxesSubplot at 0x110f31ac8>

Similarly, we can see how badly the getCompletionStatus is performing of late.

In [56]:
cs = pd.read_table('Data/completionStatus',sep='\s+',header=None)
cs.columns = ['day','useless','count','avgtime','maxtime'] = pd.to_datetime(,coerce=True)
cs.index = cs.pop('day')
<matplotlib.axes._subplots.AxesSubplot at 0x111596940>

The reason that performance is getting worse and worse, and that deadlocks are happening more frequently is due to the lack of indices on key tables in my database. Small tables without indices perform just fine, but some of thse key tables now have over 11 million rows in them. scanning them from front to back to search for a single user begins to take time!

I've added indices to these key tables over the last 24 hours and I am alredy seeing that the performance of both updatelastpage and getCompletionStatus is returning to about the same levels as March of 2014! This is good news for everyone.

CloudFlare to distribute the load

In addition to adding the indices, this weekend, I've also subscribed to the CloudFlare Content Distribution Network. This will also have a dramatic impact on the load on my server as all of the static html, css, and javscript files can now be cached and served out of CloudFlares network. This should be particularly good news to all of the folks in India who represent the second largest country using the Runestone tools, but wh

Organizing your Runestone Course

With the latest release of the Runestone Tools for this Fall I added a feature that will allow you to edit the table of contents file for your course. This lets you do two really nice things:

  1. You can move chapters around and/or remove sections that you are not going to cover.
  2. You can provide a Syllabus and assignment notices right on the index page for your course.

To do this you need to click on one of the two links provided in the instructors dashboard. Your two options are:

  • Customize Chapter Order (thinkcspy only) If you have a course created before July 21 2015 and want this feature contact me.
  • Customize Title Page and Order (all others)

The first link is for any course based on How to Think Like a Computer Scientist. This book has its own link because the initial page of that book has the swirly turtle program on it, and the table of contents is in a different file.

The second option is for all other books which have their table of contents in the usual index file. If you are using the How to Think book this link will let you edit the page that has the swirly turtle program on it.

Clicking on either of these links will bring you to a little text editing window where you can edit the restructuredText for the appropriate page. Yeah, this is a little like coding, there is no WYSWIG version of this, just a nice simple markup language. So, to add a Syllabus or a Weekly assignments section to this page you can add something like this:

Readings and Homework

Week of Sept 3

* **Friday:**  :doc:`WWW/intro`, `exercise 1 <HTML/exercises.html#ex_html_1>`_  and 2
* **Saturday:** sleep in

Week of Sept 8

* **Monday:** Read :doc:`HTML/intro` and :doc:`HTML/basic`

If you are completely unfamiliar with markup languages like Markdown and restructuredText you may want to check out this restructuredText Primer.

There are a couple of nice things to point out. The :doc:`foo/bar` syntax allows you to create a link directly to the page you want your students to read. Its pretty friendly because all you have to do is look at the table of contents (in the file you are editing) and copy/paste what you want them to read. Note that you do need to remove the .rst suffix.

You can also link directly to any exercise in the book using the following syntax: `Some Title <Chapter/exercises.html#id_of_exercise> `

You will find id of each exercise in parenthesis in the caption of each exercise at the end of the chapter. In fact this will work for any activecode in the book not just in the exercises sections.

If you don't want the assignments to clutter up your table of contents page you can modify the above example to something like this:

.. reveal:: syllabusreveal
   :showtitle: Show Syllabus

   * Week of Sept 3

     * **Friday:**  :doc:`WWW/intro`, `exercise 1 <HTML/exercises.html#ex_html_1>`_  and 2
     * **Saturday** Sleep in

This will hide the syllabus until they click on the button.

If you want to add your own custom problems you can do that by clicking on the "Edit my Assignments" link. I'm seriously considering renaming that link to Edit my Homework Problems. The identifiers that you give your exercises in that document can be linked to just like the exercises anywhere else in the book.

Don't forget that once you have edited any of these files you need to click on the rebuild link to get a new version of the book posted on line.

Any Questions?


With an hour or so of hacking I have fixed the runestone directives so that you can now reference an active code much more simply. the above example can now be

Do :ref:`Exercise 1 <ex_html_1>`

All of the identifiers you use, and can see in the captions can now be cross referenced by providing a label, and then using the identifier of the activecode inside the < and >.

Talk Python to me

This interview with Michael Kennedy was really fun to record, and a nice overview of some of the work I have been doing over the past few years. The podcast is well worth subscribing and listening to as he talks to a lot of interesting people about good Python packages, and other interesting topics around the Python ecosystem.

Day 178

We have been away from the USA for 178 days. With the rulings of the supreme court back home this week, it is easy to feel like we are returning to a country that is better off today than it was six months ago. We still have plenty of problems, but I am very excited to return home. I can't wait to see friends and family, I may not sleep very well tonight!


Karlštejn Castle, Prague

Today we spent the majority of our day biking from the center of Prague to Karlštejn Castle 35km away. We have done a bike outing in almost every country we have visited, and today was no different. It is such a great way to see the country side and the people.


Karlštejn View

We have had an exceptional few days in Prague, mostly because of meeting so many good friends who were experiencing Luther's Bach and Bonhoeffer tour. We toured with the group dined with friends, and hosted a happy hour for the group on our amazing terrace. It was almost like being home with so many great people around.


Biking with Biko in Prague

Nevertheless tomorrow morning we board a plane in Prague that will take us to Frankfurt and then we have a direct flight from Frankfurt to Minneapolis. Local time to local time I'll be on the ground 24 hours from now, but its really 24+7 to account for the time difference.

Summary of Azamara Cruise

For future reference I like to record the itinerary and favorite stops from a cruise. So here goes.

Azamara Journey June 12 - 23, 2015

  • Venice, Italy
  • Dubrovnik, Croatia
  • At sea
  • Crete, Greece
  • Santorini -- hike Fira to Oia
  • Mykonos -- morning trip to Delos
  • Patmos
  • Kusadasi (Ephesus) Turkey
  • Lemnos (replaced Lesvos due to unrest)
  • Istanbul, Turkey

Its hard to say what my favorite stop was on this trip. I enjoyed seeing the old city of Dubrovnik and hiking around the walls. I enjoyed our hike from Fira to Oia on Santorini (despite the fact that it was hot and I complained about the heat) I enjoyed our scooter rental explorations on Patmos. I enjoyed our tour, rug bargaining, and the evening concert in Ephesus, I enjoyed our cooking class in Istanbul.

Minus the screwup on our suite, the Azamara experience was really nice and relaxing. We enjoyed the fact that drinks were included, and so we didn't have pocketsful of paper for signing for anything and everything. We enjoyed our freedom to dine in the specialty restaurants (Prime-C and Aqualina) whenever we wanted, and we had a great time at the best-of-the-best night with the other suite guests. The formal table service was a show unto itself, with all of our butlers serving us in synchrony like the servants at Downton Abbey.

Cooking in Istanbul

Since we had done all the usual tourist things during our previous visit to Istanbul we decided to try another popular thing to do this time around. A cooking class. Other than the kebabs we enjoyed during our previous visit we really didn't get to learn that much about Turkish cuisine so we decided to sign up for a cooking class with Cookistan

We took an (overpriced) cab from the docks up to the Radisson Plaza where we met our instructor for the day along with a bunch of (much younger) cooking enthusiasts. Our instructor, Ayşın, was phenomenal. She had a great sense of humor, and great timing in everything we did that day. We started out with a walking tour of her neighborhood to get a sense for the kinds of small shops that people use today in modern Istanbul. When we arrived at the cooking school this was all set up for us.


Our cooking stations, all setup for us at Cookistan

The menu for the day can be seen on the chalk board:

  • Suliman Soup -- Yes, I now officially like lentil soup.
  • Borecik -- a delicous meat mixture baked in pastry
  • Stuffed dried Aubergine -- even though aubergine sounds a lot better than eggplant, I'm still not a huge fan. Although they were not too bad. We also used the same mixture to stuff grape leaves and I really liked that.
  • Bulgur Salad -- Again, a new salad, and I'm fan.
  • Circassian Style Chicken. Kind of a cold chicken salad that you can eat with a spoon or put on bread for a sandwich.
  • Pumpkin Dessert -- Maybe the only thing all day I really didn't appreciate. -- It was too much like eating sweet squash for my palete

In a very small world moment you can see the young man in this picture with me, who works on the same team at Epic with one of my former students!


Shredding veggies for the soup

Evening Concert in Ephesus

The cat, of all things, stole the show. Earlier in the day we had learned about the cats of Ephesus. Apparently the archeological team working on the excavation of the ancient city published a book, but it was about the cats rather than about substantial archeological issues.


So, it was no surprise that when 500 of us had settled into the Odeon for a 13 piece string orchestra plus harp, that the cats decided to see what was going on. One of them decided it must have had front row seats, as it seated itself right on top of the stone wall front and center behind the orchestra. Then as the first piece started the cat started to meow. Loud enough that it could be heard alongside the harp. The orchestra got the giggles. The ship's staff was mortified. Too afraid to grab the cat and drag it out of their they tried to call it (quietly) and the head of guest services tried to shush it! Really, you do not shush a cat. Now, I know this will come as a shock to some, but I am not a cat lover. I barely tolerate their presence. But this was stereotypical cat and stereotypical human behavior at its funniest. I only wish that I had been smart enough to video the whole thing because this is America's Funniest Home Videos GOLD.

I guess the cat was mostly a fan of the harp, because it moved on after the harpist left the stage. From then on we could really enjoy the music and the small orchestra. The concert was definitely a greatest hits type of concert: Bach: Air, Boccherini: Minuet, Mozart: Eine Kliene Nacht Musik, and Brahms: Hungarian Dance No: 5. The Güçan brothers stole the show in the final number with a Violin versus Viola duel that everyone laughing and cheering.

In between all of this great music was the realization that we were sitting in the Odeon theater of Ephesus. Built in the 2nd century AD we sat on the same stones that Marc Antony and Cleopatra may have sat upon when they were here. The Odeon was the center of city government, and cultural events in early Ephesus. With the sun setting behind us it was an evening we will not forget.


Earlier in the day we did a private tour of Ephesus and it was amazing. About halfway through the tour I turned to Jane and said, "I'm feeling a little bad we did not bring the students down here when we were in Istanbul." Of all of the ancient ruins of ancient cities we have visited this would definitely be in my top three. The terraced houses, and the library (with its tunnel to the Brothel across the street) are just spectacular.

Our last stop of the day was at the temple of Artemis. There is just one column remaining but from there you can get a great view of this ancient pagan temple along with a Muslim mosque and the Basilica of St. John. Quite a combination.

Biking in Patmos

We may have missed 2 or 3 km of paved road, but other than that we pretty much covered the island of Patmos today. And what a great way to cover it we discovered. Scooters! Not our usual biking fare, but the island is very hilly and we wanted to cover more ground.


Scooter Selfie

So, we got off the ship and made the short walk to Moto-Leo. A distinctly family run scooter rental operation. Father and daughter, with no deposit or arduous paperwork to rent for the day. Which the daily rental was only 15 euro. So it was also a very cheap way to see the island.

The whole island reminded us of small town living. Although Patmos is a Christian Pilgrimage destination, and they obviously get cruise ships stopping in, the island has not succumbed to that overly touristic feeling. We loved it.

The highlight of the island is the Cave of the Apocalypse, where St. John wrote the book of Revelations. Its pretty small, and they don't let you take pictures inside, so other than saying it looked like a small cave-like chapel there is not much more to say.

Above the cave sits a monastery and this was our second stop for the day. After parking the scooters and starting the walk up the hill I noticed something that was definitely out of place on the top of Patmos. A "Hawkeye Rd" sign. Really?! An Iowa Hawkeye sign on this tiny small island in Greece. "Iowa Hawkeyes!" I exclaimed. Then I met Jimmy. "Are you from Iowa?" he asked? "yes, from Decorah" I said. "Oh, thats just north of Independence isn't it?" Clearly this dude knew his Iowa geography! "Yes" I said. "Well, people from Iowa get free coffee, come in, come in!"


Note the Hawkeye Rd sign!

We learned from Jimmy that he had lived in Iowa some 13 years until he inherited this building, which brought him back to the island. He has a fantastic view, and is obviously doing quite well with the restaurant and the gift shop.


After doing the only two touristic things on the island we took off on our scooters to explore the nooks and crannies of the island. We saw lots of beautiful little secluded beaches and coves that would be perfect for swimming or snorkeling.


One of many beautiful little coves on Patmos

Death March from Fira to Oia

There were six ships in the harbor at Santorini Celebrity, MSC, Azamara, Holland America, and Saga. Azamara was definitely the smallest with just under 700 passengers, but the Celebrity Equinox more than made up for our size. So it was probably over 6000 people descending on the city for the day. In response, the port officials had to stage all of the tender operations so as to not completely overwhelm their ability to welcome people to their city. Meaning, the gondola ride to the top and the poor donkeys carrying people not willing to wait in line for the gondola.


One of the many nice things about the Azamara line is that we stay in port longer than anyone else. Whereas most cruise lines will arrive at a port around 8AM and leave by 5PM Azamara will stay until 10 or 11PM. This has many great benefits in that you can stay ashore much longer, and even have dinner on shore if you like. Or if you have dinner on the ship you are doing it while the ship is anchored! It also goes a long way to reducing the stress around getting out and back from a shore excursion.

Nevertheless, when we arrived on shore the line to take the cable car up the hill stretched across the port. It looked like it was going to take two hours just to get into town but in the end the lined moved fairly quickly and it was only 30 minutes.

The island of Santorini is really just the rim of an ancient volcanic caldera that sticks up above the water. Its easy to see the outline of the circle in this picture:


Our goal for the day was to escape the crowds in the city by hiking from Fira to Oia a five mile walk along the rim of the caldera. Through all of the white villages with their shops and hotels. It sounded pretty easy and it didn't even look like a very long walk when we were starting out. But it felt a lot longer as there was no breeze and the temperature got up to 32 C or about 90 F. The title of this post is a bit dramatic, as it was not that bad of a walk, and although in a few places we walked through volcanic gravel that insisted on getting in our shoes we had 3 hours of amazing views.


The long walk to Oia

When we arrived in Oia we badly needed a shower, or a dip in one of the many pools we passed on the way. Instead we found a nice little shady restaurant and revived ourselves with some carbonara and a glass of wine. We had escaped the crowds of Fira, seen a lot of beautiful scenery, and worked off a few calories hiking in the heat. It was a great day. Yes, it felt like a forced march a few times along the path to Oia in the heat, but as part of life's journeys this was worth the effort.

Meanwhile, back on the ship is was Azamara's white night night. Everyone was out on deck dressed in white and the crew was serving a great dinner. They had the webber kettle going and there were some great greek kabobs and other good food. To top off the evening we watched another great sunset over the rim of the volcano.


Jane and I in our whites


Another amazing sunset

Walking the Walls of Dubrovnik


Leaving Venice

After a beautiful sailaway in venice we traveled to Croatia where our first stop was Dubrovnik. This is a beautiful small city built into the hills on the coast of the Adriatic. Our goal for seeing Dubrovnik was to walk the walls of the old city. The walls are still intact (although patched in places) and provide some pretty amazing views of the harbor as well as the town.

You will notice that all of the homes in the picture have the same red clay tile roofing. But if you look a bit more closely you will see that some of them are quite new and bright while others are old and moss covered. Sadly the new tiles vastly outnumber the old, and are an indication that the house was bombed in the war in the 90's.

After walking the wall, which is about 1.2 miles we were a bit thirsty so we found a bar, literally through a hole in the wall. It was shady and we had a wonderful glass of the local white wine and just enjoyed watching the ocean and chilling out. As you can see from the picture, the tables and chairs are scattered over the natural rock formation and so the waitstaff had to be quite agile.


Hole in the Wall Bar

Later that night we had reservations in Prime C, one of the great specialty restaurants aboard the Journey. As we were getting ready for dinner we got to enjoy a spectacular sunset from the comfort of our deck on the back of the ship.


Sunset Spectacular!

We have had great meals aboard the ship in Aqualina and in Prime C. Last night was even better as we were able to attend the Chef's table. Where we had an amazing meal with wines that had been paired with each course by the sommelier. The menu consisted of Lobster and Avocado Salad with Honig Sauvignon Blanc, Smoked Tomato Veloute with Conundrum White blend, Surf and Turf Giant Prawn with a Braised Short Rib Croquette) with Schug Pinot Noir, then New Potato Crusted Sea Bass with Lincourt Chardonnay followed by International Cheeses with Toad Hollow.