Multiple database performance issues - Caused by what?

[Soenso]

Hi all,

(forgive my long story, but I figured the best policy was to give as much info as possible)

I just recently (this month) upgraded to Filemaker Server 9. My FMS9 installation is up to date, and all of my client installations are running 9.0v3. (FMS9 is running on Windows Server 2003 OS).

A database which has been in use since June 2007 has started acting up since the upgrade to 9. About a week ago, the database closed, kicking out all users. Server could not reopen the database. I opened the file locally and "Saved As Compacted". (Should have instead reverted to a backed up copy, I'm sure) Once that was complete, I replaced the existing file with the compacted file. Server was able to open the file and users were able to log back in.

Everything behaved normally for about a day, until a user discovered a portal record with no data. I found the ghost record in the table (each field replaced with the value, "?").

I decided to go into that table's settings and remove indexing on all the fields, then committed the changes. The ghost record disappeared. I then went back into the table and set indexing to "All" for each of the fields I had previously changed. Everything behaved normally for a day or so, but at some point later, Server was unable to verify the integrity of the backup. Fantastic.

Weary of possible corruption, I saved the database as a clone and opened the clone in server, running a backup on the clone. The clone's integrity was verified by Server. Considering my schema to be clean, I compacted the full database (not the clone) and then opened the file in Server and ran a backup. The backup was verified. Great. That was Friday. My backups over the weekend went without issue.

Today, users were reporting that Filemaker was hanging up (all users are running Windows XP). They were getting the "coffee cup" and when they'd click in the interface, the OS would report that the database was "not responding." In some cases, the database would complete what it was doing and allow use again. I determined that the lag was occuring when users attempted to write to the same table that'd had the ghost record and that I'd reset the indexing settings for.

Betting on a hunch that I might have had some block level corruption in that table, I backed up the file (without issue or error) and then exported all the records from that table, deleted all 60K records, and then re-imported them (and updating my auto-generated serials). I then compacted the file one last time. The resulting file was about 70K smaller than the original pre-export/delete/import file.

I opened the file, and everything looked great except for another ghost record that appeared. I once again reset the indexing, but this time, instead of re-selecting "All", I selected "update index as needed." The file is open, and I have noticed some reduced performance when writing to the problem table (that performance has improved slightly for me on my Macbook Pro, but I'm concerned my users might see the same issues again tomorrow).

Any ideas on what might be the issue? I suppose that the performance issue on that table might, at this point, go away once the indexes have been rebuilt. It also could have been a difference between setting the index to "All" verses "index as needed". Every other part of the database works without issue.

Could it be that I just haven't optimized Server 9 since installation? Any client installation configuration changes that I need to make?

Thanks for your input.

[Steven H. Blackwell]

I then compacted the file one last time.

Do you mean by this that you saved a copy as compacted? Or, that you used compact from the Maintenance utilities?

You definitely have file corruption, going back how far it is difficult to know. Do you have some good backups?

Steven

[Soenso]

I opened the database and saved a compacted copy from the File menu (then used the compacted file). I did not use the maintenance utilities.

I do have backups from before this all started, with relatively few database schema changes. There has been quite a bit of data changes since those backups, though.

[Steven H. Blackwell]

You are probably looking at a reimport intop the known good backup.

You may want to take a look at fmDataGuard from WorldSync. It can help with these type issues.

Are there any unusual circumstances about your server's setup? Hardware, drives, memory, and so forth? Any idea what might have caused this issue? Or any development practice issues such as doing schema or structure work on hosted files?

Steven

[Soenso]

Happy Thanksgiving.

Yesterday I wrote an import script to do just that. The whole import took about an hour. 100% of the records and fields imported without error.

The only unconventionality about the server is that the previous IT administrator split the entire 500GB drive space into two partitions. Unfortunately, the OS partition is only 10GB. Though annoying, this really wasn't a problem with previous versions of FIlemaker Server that didn't have to be installed on the same partition as the OS. But now, between Windows Server 2003 OS and FMS9, I don't have much space to spare (less than 500MB), so my hosted files are stored on the other partition.

I try not to make it a habit to make schema changes during database use, though I'll admit, there's been a time or two that I had to. Also, I've recently prohibited connection to the database via wireless connections. All of my client machines have battery backups and surge protection.

Hopefully my import will make the difference and address the corruption. The file has been up for a day or so, and my intraday backups have been completing without error.

[Steven H. Blackwell]

I don't have much space to spare (less than 500MB), so my hosted files are stored on the other partition.

This is the correct way to do this. A three partition--or three drive--system is even better.

Steven