A stats tracking application I’ve slowly built over the last six months or so recently started showing some potentially anomalous data for one of its views. A number that has trended upward steadily for all time suddenly dipped substantially (5%) and wallowed about at the low number for a week before beginning to trend upward again. During the low period, I looked into the matter to try to determine whether there was a problem with the stats or whether the numbers were legitimately low.

My first step was to identify any changes that had been made to the stats program immediately prior to the dip. There were a couple of changes that couldn’t possibly have related to the issue and one that seemed unlikely to have caused a problem but that merited investigation. So in a copy of the code and on a copy of the production database, I rolled back the possible culprit and re-crunched the stats. The numbers came back the same, so the possible culprit I had identified was in fact not the culprit.

Next, I wanted to determine whether or not I had made any recent changes to the database structure that could possibly have skewed my calculations (I do all sorts of calculations and caching on raw data). This wasn’t as straightforward as just viewing an svn log to determine when any relevant files were changed. But there is a way to tell when (if not how) a database was last changed. You need only look at the timestamps on the files that contain information about your database structure. This works for mysql, in any case. Here are the steps I took:

1. Figure out where your data files are stored. If you don’t know, try typing “ps -eaf |grep mysql” (or the equivalent on your linux distro) and looking for the “datadir” parameter in the command line string. Change into that directory.
2. There, you should see a directory whose name corresponds to the database you want to check for changes. Change into that directory.
3. Next, do something like “ls -la *.frm” or, if there are many tables, “ls -la <tablename>.frm”. This lists timestamps for the files that store table structure, which tells you when the tables were changed.

In my case, I have a production database and a staging database that stay pretty close to in sync. Luckily, I had synced data between the two but hadn’t synced a change I had made to a production table’s structure. So the timestamp for my production table corresponded to the date on which our stats dip appeared. I could then get a mysql prompt and type “show create table <tablename>” for both my production and my staging databases to see what the difference between them was.

It turned out to be that I had simply dropped an extraneous index in production, something that shouldn’t have affected my stats. That crunching stats in both the staging and the production environments had the same end result corroborates my conclusion that the index change didn’t matter.

As my stats had begun trending upward again and we had just come off the Thanksgiving holiday break, I felt pretty confident after doing this little diligence pronouncing the dip a product of holiday lag. In any case, now you know, if you didn’t already, how you can check to see when you last changed a mysql table’s structure.

powered by performancing firefox