A word to the techies out there

I apologize in advance to all the computer illiterate corn burners out there. Suffer through, I need to talk technical for a bit. I will try to get to some actual corn burning talk before the post is over. I have seen some of the posts on the forum and heard from a few of you so I know there are some geeks burning corn too. I will let you know something I discovered today.

Today was sort of a wasted eight hours at my day job. I have been working on a technical issue with some ESRI mapping software being incompatible with our hardware and operating systems. I am reminded of why I will do almost anything to put off calling in tech support from a major software company. Well, I had put it off. My boss had asked me how it was coming too many times. I had no solutions myself, so, today was the day. I emailed ESRI tech support. I spent most of the day gathering information and emailing back and forth to a tech support drone. We have now successfully tried all the stuff I had already tried before. Sigh....

However, that being said, in between all that searching, documenting, and emailing, I had some free time. During that free time, I did some work on the database structure of the corn burning web site. Those changes really seemed to speed up how the site works. Much more so than what I expected them to.

Let me give you some back ground. The forum software the www.iburncorn.com site runs is called CF-forum. It is written in a computer language called Cold Fusion. Originally, my wife, who has a computer consulting business bought the CF Forum software for a client of hers. It was written to run on Microsoft Windows and use the Microsoft Access database. She rewrote the code and got it to run on the Linux operating system and talk to the open source, MySQL database. After a couple of years, her client moved on, but the software stayed with our server. Even at it's peak I think that forum only had about fifty users. Nothing like the iburncorn forum has. So, her programming and database changes were never really pressure tested.

Time passes, a couple of years go by. I bought the corn boiler on blind faith because there is no real resource out there on the web. I decided to setup my corn burning site and then setup the forum. The software suddenly has 10x the number of users it had before and more like 100x the number of messages. Things began to drag and I started getting complaints about how the software was running. I will be the first to admit, it was running S L O W.

Today in my spare minutes I had the freetime to take a look at why it was running slow from the data end. MySQL is a very powerful database engine and I assumed it should be able to handle the load just fine. Really, the computer the web site was running on should be able to handle the load as well. So, what was happening? The database tables were not very complex for the cfforum software, just 14 tables. Not even very many fields in those tables. So why did it run so slow? The tables had no indices.

Fourteen very simple tables. Each web page would fire off several queries to the database. Each query would have two or three joins to other tables based on primary keys within the database. The biggest table had less than 6000 records in it. I figured, no worries! MySQL should be able to crunch through that and do the joins, no problem. Wrong!

It made a huge difference in how the forum ran with just adding one index in each of the three largest tables. Suddenly page build time was cut by 1/4th! The index ties together the userID, messageID and topicID into one index. Wow. Amazing!

A second change, not quite so dramatic, but still made some difference. I turned on query caching in MySQL. Actually, more technically correct, I allocated RAM to the query cache. Every since MySQL V.4 query caching has been turned on in the default but since no memory is devoted to it, it does not cache. This was a very simple change, just add the line:
query-cache-size = 20M
in the /etc/my.cnf file. I would say that made some difference in how the site runs. Not as big as the indices, but noticeable.

So, some good came of the day. Now, if I could only get those stupid maps working! :-)

I never did get to any corn talk. My fingers are tired and I need to get to bed because tomorrow is my day to be in the office. I do have a new site feature in testing right now. Mostly for the pellet burners. It will allow people who are burning corn in their pellet stoves to enter some details. It will build up a database for others to see which pellet stoves can burn ratios of corn without troubles. I hope to have the feature online by tomorrow.

Have a good night!
--ja