S L O W Server 9 -- possible serious problem?

[Peter Payne]

Hi, everyone. I was running Filemaker Server Advanced 9 version 9.0.3.326 on a G4 tower, 2.0 ghz, when the database started getting extremely slow, approximately 1/2 the speed or normal. It also started disconnecting and generally being miffy, and (horror of horrors) I'd find that the backups I religiously made weren't functioning, e.g. backups of my data from 11/1 had no records in them more recent than 10/22. That's a nightmare, let me tell you.

In the past I've had problems with "corrupted databases" that turned out to be caused by a server computer that were just plain bad (either the hard drives or the computers themselves or something), so I decided to replace this machine entirely with another one, a G5 2.5 ghz, which I wiped completely before reinstalling.

The speed of the new machine is not improved however. The server is running normally on its fresh install, but it's very slow, so that calling up a product that's sold 5000 copies is agony because the database has to calculate the number remaining in stock, which takes 30+ seconds. Not a lot of fun.

I am not one of those people who practices database purity, and when I've had problems with my databases (which have been in use since 1996), I've had to do things like recovering the data using the recover command, which some people claim is terrible to do and will lead to the end of the world. I don't *think* that my data could be so messed up that the problem is being caused by trashed data, as it functions well most of the time. Recovering the data using FM 8.5's recover feature results in no skipped records of messed up bytes.

The server machine (G5 2.5 ghz) looks fine too, and is not running any high loads or anything. It seems either that Filemaker 9 Server is a slothful beast that must be put down, unless I can find something like, I've got a really bad ethernet cable or something.

Has anyone got any advice for me, things I can try? I did install Server 9 Advanced on a Mac Pro and found the speed just as bad, so it's not a PPC/Intel thing. I'm still seeing highly unreliable backups, too, with backups seeming to not work at all, then 30-60 minutes later the files are finally updated (by checking the dates of the files) in the proper backup folder. (Yes, I've verified all permissions for the backups so Filemaker can validate the locations.)

If I have to go back to Filemaker 8 Server it will mean a) downgrading the server to 10.4 again, and : throwing away the huge investment I've made in Filemaker's products. I'd love it if someone here can help me get the server working at an acceptable rate of speed.

[Vaughan]

The problem with the recover command isn't related to data. It's job is to make sure that the data is complete as possible.

The problem is that making sure the data is complete as possible comes potentially at the expense of the database structure. If the recover command has to chuck out structure to save some data, it does. No prompting. That's its job: recover the data. There could be bits of script, layouts, value lists or who knows what else missing in the resulting file. It might just be the field order in an export script step.

Finding those little missing bits is next to impossible.

FM Server 8.0v4 is very stable. I don't know whether it runs on Leopard (it might) but it's a known quantity on 10.4.11 so put Tiger back on by all means. You need to start getting the variables down to a workable level.

Save compacted copies of all files and host these copies. Any that are still broken need to be rebuilt. Don't take shortcuts, copy and paste can re-introduce broken bits into the clean file.

[Peter Payne]

Thanks for the reply. So does it seem like

a) using recovered databases with Filemaker 9 Server can lead to heavy mucking/slowing that causes reliability to suffer greatly

and

: how would I go about knowing if this was what was going here? Obviously I would kill for a tool that stripped a database file to XML and re-imported it again, for absolutely clean-ness.

It's hard because the new server that I've set up is slow, but sometimes gets fast again. I'm going to grab screenshots of "good" "average" and "poor" performance periods and see what I can see.

[Søren Dyhr]

I am not one of those people who practices database purity, and when I've had problems with my databases (which have been in use since 1996), I've had to do things like recovering the data using the recover command, which some people claim is terrible to do and will lead to the end of the world.

You are not supposed to do other work on a recovered file, than fishing out vital data, to put it into a file - newly developed from scratch. Nothing from back then can go on unaltered, since we with fm7 got an entire different relational discourse.

Where the migration papers properly scrutinized before entering this new environment, or where the solution only migrated as is? Does the solution lend a lot from the spreadsheet metaphor, with tons interdependent of un-stored fields in very few tables?

It seems your solution suffers from lack of method, much more than the tool as such actually is buggy or faulty in behavior ...it seems as if you have believed that a sale of indulgence was possible, by keeping you wallet wide open for each opportunity?

--sd

[Steven H. Blackwell]

This sounds as if it is a file architecture and structure issue. It is also possibly a Server configuration problem.

What is the setting for the server cache?

Steven

[Peter Payne]

Thanks for the info. I can't find how to change the cache amount, can you tell me? The server (G5 2,5 ghz) was recently wiped and nothing has been changed from the initial setup at all.

[Steven H. Blackwell]

It's in the Admin Consolein the configuration area. Set to the maximum amount available.

Also, how much installed RAM does this server have?

Steven

[Peter Payne]

The machine has 2 gb of memory, so that seems like it's enough for these purposes. I'll increase it, thanks for the suggestion.

A larger issue is that my backups "seem" to work yet then don't contain the data they say they do. I back up my files often, three times a day as well as several other specific times, and now when I open my files they are lacking the data they should have. I think the invoices database is the issue, as it opens with "Mac-Server.local" in the title and doesn't have the data I expect there to be in it. Opening the invoices.fp7 file directly brings up the proper file, meaning I have link issues? I wonder if deleting the connection between invoices.fp7 and the other databases and remaking them would help -- but it sounds terrible, reconnecting everything.

I'm currently trying the clone-recover-export as merge-import again method to see if I can get things working.

One more question, my server seems to have decided recently that I (my own user) can't write files to the directory where my backups are. I set permissions with sudo chown -R fmserver.fmsysadmin /Backups (my backups folder is in the root and contains several sub folders where I put my backups). But currently it's insisting there's only read only access for fmsadmin. I'm sure my backup issue might be related to this.

ls -la returns this for my backup folder:

Server-Mac:Backups pp$ ls -la

total 32

drwxr-xr-x 34 fmserver fmsadmin 1156 Nov 4 14:14 .

drwxrwxr-t 34 root admin 1224 Nov 4 12:30 ..

-rw-r--r--@ 1 fmserver fmsadmin 15364 Nov 4 01:41 .DS_Store

drwxr-xr-x 33 fmserver fmsadmin 1122 Nov 4 14:05 10a daily

drwxr-xr-x 33 fmserver fmsadmin 1122 Nov 4 14:14 10p daily

drwxr-xr-x 33 fmserver fmsadmin 1122 Nov 4 14:05 7a daily

drwxr-xr-x 33 fmserver fmsadmin 1122 Nov 4 20:48 8p daily

[Vaughan]

How are you backing up the files? Using the FM Server schedules, or some other method that copies the live hosted databases?

All of this stuff should just work. Problems with permissions indicate that something is wrong.

Since you have multiple boxes you have the luxury of being able to set up a new machine. Format the disk and install the OS then patch it. Install FM Server and patch it, then configure it according to the server best practices white paper. Setup the backup schedules and folders etc and test them. When that's done, down the production server, copy the files across.

Get the OS and privileges thing sorted out first. That's the foundations of a stable system.

[Peter Payne]

Thanks, I'm trying to reinstall the whole thing in 10.4 this time, never did like 10.5 and Filemaker Server. We always used the normal Server schedules to backup, so that shouldn't be a problem.

[Søren Dyhr]

You didn't respond to my mentioning of purchase of indulgence? Perhaps because you find it irrelevant or irritating against your hidden agenda, but is it really?

The Beatles Sgt. Pepper was done on a 4 track recorder and George Lucas did his models for Starwars on a single Mac 8100...

It's sort of:

http://www.phrases.org.uk/meanings/59100.html

Which in this case means that spending or financial strenght might go a long way, but if your solution is attempting to get out of the realm of databases, wouldn't even the strongest powers of processing be of much use, since it's used to remedy artifacts proper normalization is used to solve.

The other proverb here is "horses for courses" ... which would require you explain your choice of tool - the purpose and context?

--sd

[IdealData]

Can you show the calculation you are using for...

The speed of the new machine is not improved however. The server is running normally on its fresh install, but it's very slow, so that calling up a product that's sold 5000 copies is agony because the database has to calculate the number remaining in stock, which takes 30+ seconds. Not a lot of fun.

[Søren Dyhr]

Yes the whole notion that it actually needs to be a calc'field at all ...is not scaling particular well. While recursive many 2 many structures for this purpose are cute and intriguing for the mind, should we be honest and say that they usually belong to mom and pop shop discourses only.

Industrial strength to a solution is obtained along the lines of this:

http://www.geistinteractive.com/system/files/Transactions.zip

--sd

[Peter Payne]

Hi, we're still trying to figure out what the slowness is being caused by. I'm going to do more work on the files tonight. I am actually quite ignorant of an alternative way I could be tracking the numbers I need to calculate -- could anyone enlighten me to alternate ways? Basically we've got the product database with, say, 100 copies of a book, and the calculation (which has been around since FM3 was the new thing on the block) just goes out and gets the total quantity sold in the lineitems.fp7 database, and tells us that if 30 have been sold then there are 70 left. I do recall seeing server-side storage of calculated values, can anyone tell me were to start looking?

[Søren Dyhr]

The problem is the unstored values over relations, if you step back and look at the principles the adapter of the arabic double sided ledger Luca di Pacioli introduced, was it done in the late 13th century where computerized aggregate functions weren't around yet, even though could the venetian merchant pretty reliable and rapid know what their stocks and thir own worth was at a given moment.

The key to it all is that only the previous records value is considered not an entire span of records, this means the last transaction is the carrier of both the transaction value as well as the sum of the previous sum and the present transaction.

This principle works with ink, and has done so for at least 1000 years. I can only suggest you study this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000333

But the scripted version I led you to in yesterdays post have other benefits....

--sd