1 2 3 Previous Next 39 Replies Latest reply on Jan 26, 2017 6:50 AM by edp@oxyturbo.it

    Record deletion extremely slow with FMS 14, freezing WPE

    tcmeyers

      Summary

      Record deletion extremely slow with FMS 14, freezing WPE & clients

      Product

      FileMaker Server

      Version

      14.0v2

      Operating system version

      MacOS X 10.10.4

      Description of the issue

      Under a recently installed and freshly updated FileMaker Server 14, deleting a record or a set of records is extremely slow.

      In the case of a loop deleting a group of records individually, one at a time, the deletion takes a very long time to complete, and this it also causes sluggish response for other FMP clients, and FMGo clients connected.

      In the case of deleting a found set of records (as one step) it is also extremely slow, and during this process FMP and FMGo clients can get NO response from the server, and the WPE also goes silent, causing the web server to time out, making web users unable to access data.

      Both FMPA 13.0v9 and FMPA 14.0.1 as the clients requesting the deletion produces the same trouble.

      Steps to reproduce the problem

      Actual example:
      Deletion of a found set of 2688 records is initiated by a script running in FMPA. The deletion progress box comes up, and at 2 minutes in the progress bar has not moved. During this period the other FMP and FMGo clients and the WPE have become unresponsive. At this 2 minute point, the "Stop" button in the progress box is clicked, and the button does disappear. The deletion process is not terminated very rapidly -- it is another 9 minutes and 30 seconds before the progress box finally disappears. Only at the point that the progress box disappears (a total of 11 minute and 30 seconds) are the other client FMP, FMGo, and WPE users able to get any response from FMS. In that period of time, only 89 of the 2688 records were deleted. I don't know if the 89 were deleted in the first 2 minutes before the "Stop" button, or the entire period.

      Expected result

      Under FMS 12 deletions such as this were speedy, and the single-record-at-a-time-in-a-loop method never caused any noticeable sluggishness for other clients.

      Deleting a found set never took very long, and freezing up of the WPE or clients was never noted before.

      Actual result

      As described in the "Steps" section

      Exact text of any error message(s) that appear

      none

      Configuration information

      FMS 14 running on a Mac Mini Server (Late 2012) WITHOUT Server installed
      2.3 GHz Intel Core i7
      4 GB RAM
      Disc in use: APPLE HDD ST1000LM024 Media

      Workaround

      none, pretty disastrous to our CWP-provided web visitors, annoying to in-house users

      Screen_Shot_2015-07-22_at_11.35.21_AM.png

        • 1. Re: Record deletion extremely slow with FMS 14, freezing WPE & clients

          Troy Meyers:

           

          Thank you for the post. 

           

          “4 GB RAM”

           

          The first suggestion would be to increase the RAM to a minimum of 8 GB. 4 GB of RAM is the minimum recommended amount if not using WebDirect. 8 GB is the minimum recommended amount if using any Web Publishing and no more than 1-6 WebDirect connections.

           

          System Requirements for FileMaker Server 14

           

          A number of other users have reported similar problems running below the recommended hardware requirements. Once the system meets the minimum requirements, let me know if the issue persists. 

           

          TSFalcon

          FileMaker, Inc.

          • 2. Re: Record deletion extremely slow with FMS 14, freezing WPE & clients
            tcmeyers

            TSFalcon,

            Thank you, I will obtain and bump up the RAM. The server (FMS 12) that this new one replaced indeed did have 8 GB, and we never had such a problem. I'll increase it and report back. I have to get new parts, I can't steal the old ones from the earlier server, too slow. Fingers crossed.

            Troy

            • 3. Re: Record deletion extremely slow with FMS 14, freezing WPE & clients
              tcmeyers

              TSFalcon,

              I increased the RAM to 16 GB, and the problem remains.

              In this test I tried deleting 3345 records, and after no movement of the progress bar for 2 minutes I hit the Stop button. During this period XML for CWP went silent, as before. After clicking the Stop button, it was another 8 minutes and 25 seconds before the delete-progress box finally disappeared, with XML still being silent for the whole period. So this time, a total of 10:25.

              During either the first 2 minutes, or the whole ten minutes (depending on when it really stops deleting) only 98 of the 3345 records were actually deleted.

              Conclusion.... it's not just a lack of RAM.

              What's next? I'm not doing the deletions at this point because I can't afford to have the site down that long.

              Troy

              • 4. Re: Record deletion extremely slow with FMS 14, freezing WPE & clients
                Benjamin Fehr

                Troy:

                Not a solution but kind of a workaround would be the use of SSD-Drives which speed up processes in general to some impressive degrees.

                • 5. Re: Record deletion extremely slow with FMS 14, freezing WPE & clients
                  tcmeyers

                  Benjamin,

                  Thank you. I agree. In the FMS 12 machine that me replaced, we had swapped in an SSD and it seemed to be snappier. But as you say, I think there is something else that is at the root of this.

                  For now I'm thinking it's best to not change anything until the problem is better understood.

                  Troy

                  • 6. Re: Record deletion extremely slow with FMS 14, freezing WPE & clients
                    philmodjunk

                    My memory may be suffering form information overload from DevCon, but I thought that I saw a presentation "slide" that recommending redeploying server if you upped the RAM...

                    • 7. Re: Record deletion extremely slow with FMS 14, freezing WPE & clients
                      tcmeyers

                      PMJ,

                      Thank you, that sounds like a good thing to try. I'm hoping settings and schedules are preserved!

                      Troy

                      • 8. Re: Record deletion extremely slow with FMS 14, freezing WPE & clients
                        tcmeyers

                        Phil,

                        This made some difference seemingly, but didn't fix the problem. BTW, all settings and schedules were preserved.

                        I redeployed successfully. Did not restart (I guess I'll try that too). Starting with 3525 records to delete, I let it go for 3 minutes before hitting "Stop". I waited the extra minute because just short of 2 minutes the progress bar for the first time actually showed a little blue, and the records remaining count went to 3421 (stepped down 100). By the 3rd minute that had not changed, so I hit Stop. This time it only took 1 minute for the progress box to disappear after Stop. During the entire test period the WPE XML was affected adversely, but a very few queries (3?) actually worked rather than it being entirely silent. After the progress box disappeared I was surprised to see that the number of records deleted actually was exactly 100. This make me wonder if somehow the display of the progress is tied into the problem, since just prior to the 2 minute mark exactly 100 had been deleted according to the display, and that quantity had not changed by the end of the test.

                        Next I changed the cache from the default 1024 MB to 7168 MB, slightly less than half of the 14745 MB reported as the maximum available for the cache. I repeated the test with the 3 minutes before hitting stop to be similar to the first test of the day. Starting with 3421 records to delete, at 3 minutes the progress bar had not moved before "Stop" but this time it "only" took 30 seconds for the progress to disappear after "Stop". Interestingly again exactly 100 records had been successfully deleted in the 3 minutes and 30 seconds. A few XML queries worked, but mostly they timed-out.

                        It seems that increasing RAM, redeploying, and upping the cache improved things a little, but perhaps mostly, hopefully, clarified the problem.

                        Any other thoughts?

                        Troy

                        • 9. Re: Record deletion extremely slow with FMS 14, freezing WPE & clients
                          philmodjunk

                          For a deletion test, there are two factors to examine, if only to rule out as contributing factors:

                                 
                          • Any indexed fields in the table will need an extensive update. The larger the indexes (and the greater the number) the longer the delete will take.
                          •      
                          • Any cascading deletes due to the "delete" option being specified in a relationship somewhere in your relationships graph can greatly slow the delete process as FileMaker has to "find" and delete related records at the same time that it deletes your original found set of records. This theoretically, at least can "mushroom" your original delete to a much larger number of records--even triggering a multiple table record delete if you have a "chain" of such "delete" options.


                          I can't tell from here if either of these features might be a factor here, but you can see if turning off all indexing on the fields and checking your relationship graph (check all relationships that link to an occurrence of this specific table) for "delete" options might be the next thing to do unless you have already ruled out those factors.

                          • 10. Re: Record deletion extremely slow with FMS 14, freezing WPE & clients
                            tcmeyers

                            Phil,

                            There is a lot of data, much of it is indexed and there are a number of related tables, though only one sometimes has a records that is set to be deleted if a related record in the problem table is deleted.

                            As messy as the database design is, I don't think that's the problem (of course it contributes) because the deletes worked just fine under FMS 12.

                            I just did a FMS machine restart and left all the settings the same as the last test, that is, the one with the 7168 MB cache. The results were actually worse, but perhaps I can attribute that to the fact that one of the FMP clients was doing a complex search at the same time. That client experienced very very sluggish performance but FMS was giving it at least a little attention. WPE unresponsive. This time starting with 3324 records to delete, at 3 minutes when I hit Stop the progress bar had not moved. It was actually 9 minutes and 15 seconds before the progress box disappeared this time, a total of 12 minutes and 15 seconds, and again the magic number of exactly 100 records were deleted. I've got to think that's a clue.

                            Troy

                            • 11. Re: Record deletion extremely slow with FMS 14, freezing WPE & clients
                              tcmeyers

                              It occurred to me to see what would happen if I hit "Stop" almost right away. I started the delete of 3229 records, waited 2 seconds then hit "Stop". It took 4 minutes and 28 seconds for the progress box to disappear. WPE down the whole time.

                              Exactly 100 records deleted.

                              In 2 seconds... unless deletion continues after "Stop" is hit.

                              • 12. Re: Record deletion extremely slow with FMS 14, freezing WPE & clients
                                tcmeyers

                                Last test of the day, I'm messing up the web site too much.

                                I wrote a simple script to display the number of records deleted on a nearby FMP client, and the deletion does slowly progress after the "Stop" button is pushed, and it is not until the 100th record is deleted that the progress box in the delete-requesting client disappears, and that's exactly when it disappears. The time for each record to be deleted was not uniform, but most took over a second, some a few seconds.

                                Two observations -

                                The 100 record effect may just be that the "Stop" button is only enforced at 100-record check points. I can't say I'd like that.

                                It may be that if I did not give up and click "Stop" more than 100 records might eventually be deleted, but I can't afford having the WPE being non-responsive for that long due to it screwing up the web site.

                                I think it's clear that something is making even a single record deletion takes way too long... a thing that I haven't tested just recently, but was one of the symptoms I originally reported.

                                Troy

                                • 13. Re: Record deletion extremely slow with FMS 14, freezing WPE & clients
                                  philmodjunk

                                  I would take a back up copy of that file and host it for testing so as not to impact other users.

                                  In that copy you can test changes that would be catastrophic to the actual file to see how much they affect the time needed--such as removing all field indexes and removing all delete options in the relationships.

                                  You can also, if you have not already done so, recover a file and test the recovered copy. This would be for three reasons:

                                  1) Your forced quits might have damaged the file

                                  2) File damage can be hidden, not causing any observable issues until an upgrade of either FileMaker or the OS results in noticeable problems.

                                  3) the recovered copy will have 100% rebuilt indexes and that might affect deletion time if the current file has some corrupted indexes. (Recover never checks for damaged indexes, it just rebuilds them.)

                                  Testing a brand new "from scratch" file with the same number of fields and records is also a useful way to rule out file issues as the source of your trouble.

                                  • 14. Re: Record deletion extremely slow with FMS 14, freezing WPE & clients
                                    tcmeyers

                                    Thanks, Phil. I did a number of things like this after you suggested them. Interesting results... I have one more test to do, then I'll report.

                                    Troy

                                    1 2 3 Previous Next