13 Replies Latest reply on Jan 18, 2017 1:31 PM by tadzyh

    FM Server 12 Delayed Write

    tadzyh

      I am having a problem with my filemaker server losing committed data after the server is restarted. The server is hanging on shutdown which is obviously one problem under investigation. It would seem that the filemaker server is unable to complete it's shutdown processes and my theory is that this means that any data held in cache isn't being written back to the file. However what is really throwing me is that the FM Server console is continuously reporting that the unsaved cache is at 0% and it is reporting regular disk writes. Watching the file times they are not updating in line with the console reports of disk writes. The server does not appear to be overstretched - running at cpu 85% idle and 30% unused memory. So why are the files not being written?

       

      If anyone has experienced this problem and can give me some pointers for how to investigate this and (I'm being cheeky here) any ideas on further investigation of the server shutdown hang problem that would be great too. We are running FMServer 12.0.6.600 on OSX 10.9.5 and cannot upgrade at the moment as our solution doesn't work on later versions.

        • 1. Re: FM Server 12 Delayed Write
          Johan Hedman

          First off I would update to FMS15

           

          If you have that problem of losing data, I would use less Memory on your FMS Settings. Then FMS needs to write more often. Then also, include Commit Record in all you scripts

          • 2. Re: FM Server 12 Delayed Write
            wimdecorte

            How do you determine that data is lost?

             

            FMS writes to cache to disk continuously so the chances of it losing data that way are incredibly slim and would only be data received from the clients in say the last minute or so.

             

            What are the specs on the machine?  Have you tried a disk verification and a permissions repair?

            • 3. Re: FM Server 12 Delayed Write
              tadzyh

              Thanks for the help Johan,


              We are in the progress of attempting to upgrade to FMS15 but this is a few months off as our solution needs to be redeveloped to do that. The problem has appeared this week and is an immediate issue which I need to resolve on FMS12. 

               

              The machine is a late 2013 MacPro, Processor  3.7 GHz Quad-Core Intel Xeon E5, Memory  12 GB 1866 MHz DDR3 ECC

               

              I could understand losing data if the data wasn't being committed but this problem is occurring on committed data. I have tested this by making a database change, committing it, checking it has been committed by viewing the data on another computer and then looking at the times of the database files on the server. We know the data is lost as we can trace back data changes and find when the last known update is still present. I determine whether the files are being written by FMS by looking at the update times of the filemaker files using Finder. From looking at these times I can see that the files are not being updated continuously.

               

              I've just checked using bash ls -l and it seems that Finder is reporting a different file update time for the database files than bash - why would this happen? The database files are stored on the internal SSD.

               

              FMS is reporting that it is writing regularly with our current memory settings - is this report wrong then - is it not actually writing to disk? It would be lovely to resolve that mystery. More information - the database file writes coincide the progressive backup file times so it would appear that files are only being written when the databases are paused to perform the progressive backup.

              • 4. Re: FM Server 12 Delayed Write
                Johan Hedman

                Sounds like you might have permissions problems like wimdecorte write about. Have you tried to re-install FMS?

                • 5. Re: FM Server 12 Delayed Write
                  tadzyh

                  Thank you wimdecorte (wow you are up early!) and Johan, this is helping me work things through.

                   

                  I have tried repairing disk permissions several times - we have had a reoccurrence after they were repaired. I've also done a RAM test thinking this was strange behaviour but all reported OK. I'm now wondering if the problem is more to do with spotlight or finder and whether a problem with those could cause filemaker server to stop saving to disk or somehow to revert to an older save.  FMS is running fine right now although finder is still reporting the wrong file modification times compared with terminal but as I haven't found a definitive cause I'm fearful that it could repeat and we could lose data again. 

                   

                  So question for you FMS wizards - if FMS doesn't get to shutdown a database properly when the system shuts down what state does it leave the file in and is there any chance the file could be reverted to a previous state when reopened (we have lost up to an hour of updates)?

                  • 6. Re: FM Server 12 Delayed Write
                    wimdecorte

                    tadzyh wrote:

                     

                     

                     

                    I could understand losing data if the data wasn't being committed but this problem is occurring on committed data. I have tested this by making a database change, committing it, checking it has been committed by viewing the data on another computer and then looking at the times of the database files on the server.

                     

                    The OS dates and times on the FM files are meaningless and not a good indicator as to when FMS last saved cache to the disk.

                    As I said, FMS has a very robust cache-writing mechanism that runs continuously.  The only way you can lose data is if FMS crashes and there is unsaved cache.  The cache unsaved % numbers on the FMS console don't lie.

                     

                    So you are entirely drawing the wrong conclusions from looking at the OS date/times.

                    • 7. Re: FM Server 12 Delayed Write
                      wimdecorte

                      tadzyh wrote:

                       

                      So question for you FMS wizards - if FMS doesn't get to shutdown a database properly when the system shuts down what state does it leave the file in and is there any chance the file could be reverted to a previous state when reopened (we have lost up to an hour of updates)?

                       

                      Files never get reverted to a previous state.  Forget about the date/time stamps in Finder.

                       

                      Files however can get damaged when FMS does not have time to close them properly when the system reboots.  That's why the best practice is to first make sure that FMS was able to close the files before initiating the system shutdown or reboot (all of which can be scripted through the OS).

                       

                      If the files were not properly closed through the system reboot then you should see evidence of that in the FMS event log when FMS will report that it had to do a consistency check on the files.

                      Best practice here is to revert back to a backup when that happens so that you don't start to build up latent damage in the files.

                       

                      Are you rebooting the machine on a schedule for maintenance purposes?  If so you really need to beef up that process so that you take it step by step and don't initiate the system reboot unless you have confirmation from FMS that all files are closed.

                      • 8. Re: FM Server 12 Delayed Write
                        tadzyh

                        So what I understand from this is that when FMS console says the cache unsaved % is 0 then that means that all data has definitely been saved to disk.  Interestingly on our last server forced shutdown I was actually logging the figures and it was showing 0 for the last hour based on 15s interval measurements and yet we lost about 10 minutes of committed updates. I was concerned that there might be something OS-wise that might have been misleading FMS into thinking it had saved its data when, in fact, the data hadn't actually made it to the disk - hence double checking with the file modification times.

                        • 9. Re: FM Server 12 Delayed Write
                          tadzyh

                          I will check the status of the files to ensure there are no inconsistencies/corruptions - as you say this could be storing up problems for later on.

                           

                          We don't have a policy of forcing shutdown - it is usually a managed process - just when the server hangs on shutdown it was a necessity.

                          • 10. Re: FM Server 12 Delayed Write
                            wimdecorte

                            tadzyh wrote:

                            yet we lost about 10 minutes of committed updates

                             

                            How did you determine that?  Can you describe in some detail what you did to establish that?

                             

                            How high is the cache set in FMS12?

                             

                            Used to be, FMS has a setting for 'cache flush interval' that was set to 1 minute by default.  Meaning that every second FMS would inspect 1/60th of the cache to see if it contained unsaved data and save that to disk so that every minute the whole cache gets inspected and everything flushed out.

                             

                            I don't remember with what version of FMS that setting went away and FMS now saves even more continuously.

                            • 11. Re: FM Server 12 Delayed Write
                              tadzyh

                              To establish the time period where changes had been lost after the server and FMS restarted I asked the two people who had been using the database to go back and check any changes they could remember making in the last half hour and let me know which changes had been lost. They listed the changes that had been lost and the last change they performed which was actually present including updating notes, making diary updates (e.g. updating to show that the client has arrived), creating invoices and marking invoices as payments. As all these items contain an audit trail with a timestamp I could establish roughly on this occasion that we had lost 10 minutes of data. On two previous occasions I wasn't present but the timestamps of the most recent changes before the restarts were half an hour and an hour. respectively. For the previous restarts I received reports that data had been lost for example we had issued several invoices with an autoincrement invoice number and after the restart the system was issueing invoices with the same set of invoice numbers.

                               

                              The cache is reset at 128MB now and is still showing >90% cache hit rate although I had increased it to 256MB yesterday.

                               

                              Thank you for clarifying how FMS manages it's cache flushing - I was trying to find out but haven't been able to. Even if our FMS version has the 1 minute cache flush interval that wouldn't lose the amount of data that we are experiencing.

                              • 12. Re: FM Server 12 Delayed Write
                                wimdecorte

                                tadzyh wrote:

                                 

                                For the previous restarts I received reports that data had been lost for example we had issued several invoices with an autoincrement invoice number and after the restart the system was issueing invoices with the same set of invoice numbers.

                                 

                                 

                                Are there duplicate serials in the system now?

                                 

                                If not then the only way to make happen what you describe is:

                                - that someone restored a backup without telling anyone

                                - there is another set of the files available somewhere on the network (file sharing? to the backups?)

                                • 13. Re: FM Server 12 Delayed Write
                                  tadzyh

                                  There are no duplicate serials in the system now so there is definitely an older version of the database being served after the forced restart than was being served before it.

                                   

                                  When I was told about the problem I, too thought the same as you i.e. that there are only two possible ways of reverting the database to an older version however there is actually no way on our setup that can happen due to lack of permissions for anyone other than me and I know I hadn't done anything.

                                   

                                  Then it came to me that if all the changes that were lost were only held in cache and hadn't been written back to disk then it would appear to be the same as replacing the database file with an older version.

                                   

                                  Another possibility crossed my mind - that maybe if the file failed its consistency checks following the FMS restart maybe FMS attempts to fix problems somehow - are you aware if this happens and if it does what can be the possible outcomes? Note that I could find no record in the logs to show that a consistency check was performed following restart.