Hello everyone,

I thought I would share with all of you that which has been going on lately. As many of you know, the server we were on for such a long time failed due to a motherboard problem (a "crashed node" according to the datacenter). The drives were installed in another server at that time (October 12th).

As luck would have it, the server we were moved to also had a problem. As far as the datacenter could determine, and what I could determine without having physical access to it, there was something amiss in the server's BIOS causing it to restart itself at random times. On a couple occasions the server needed a "push" by the datacenter to turn back on as well. Eventually, the datacenter moved us to the current server, which is a newer Dell model. However, it has not been a very smooth migration.

The current server, as well as the interim server, have newer server software versions than our old server, most notably the version of MySQL (database). For those interested, we are currently running on version 5.5, the old server was running 5.0. The difference between the two isn't a small thing. We were running MySQL using the storage engine MyISAM for the tables, which on the old server was fine. However, with version 5.5 we began experiencing corrupted and/or crashed database tables which had to be repaired. One particular crash affected the Private Message System's message table and all those messages were lost when the repair was run. I used the previous night's backup to restore most of the messages so only that day's messages were permanently lost. We also had crashes in other tables, but fortunately very little data loss occurred in those.

The most recent issue I've had to deal with is the new MySQL version. To prevent the crashes we changed the storage engine from MyISAM to Innodb, which is a more stable storage engine. (MyISAM is being phased out anyway.) There are distinct differences between the two though, the main one being experienced by the members here is the speed at which MySQL functions using the InnoDB storage engine. I had many posts and PMs about that. While there were certain server configuration changes made that helped a great deal, the ultimate problem came down to this: Indexing keywords in the search engine. With indexing temporarily turned off (generally not noticed by any members here) I saw things speed up considerably. That told me I had to make some changes.

Keyword indexing is being handled differently as of last night. Before last night posts were being indexed at the time the post was made, and the InnoDB engine was causing delays not only to the member posting the message but to everyone with a subsequent database request until that post's indexing was complete. Worse yet was when two or more members were posting at about the same time, so you can imagine what I saw happening during those times. I thought about a solution for days until I came up with one I thought would work. That was this past Thursday. I worked late that night, late Friday night, and nearly every waking moment of the weekend to get the new system up and running in hopes that it would make a difference.

This new method of post indexing is a system where the post's keywords are not indexed at the time the message is posted. Instead, data from the post is copied to an indexing table while post data is saved in the database normally for viewing. Messages can be anything from one word to hundreds, so I had to come up with a way to split the data into chunks so when the indexing is run there isn't nearly as much of a delay for everyone. Plus, deciding to do it that way meant there would not be more than one indexing run at the same time causing further delays.

What happens now when a message is posted is the data is saved while an indexing script runs in the background. The script runs every twenty seconds either indexing a topic title or post, or splitting a large post into smaller chunks. So far it is functioning perfectly, or at least just as I designed it to anyway.

The result? I'm watchfully waiting to see if my efforts have paid off. It's looking pretty good so far, at least to me, but you - the Café members - are the better judges on that.

If any of you have a problem that is happening regularly, please let me know either in the Tech Help forum or by PM. I would also be interested to know your opinions on how the Café is running now versus before last night.

Thank you all.