20 Replies Latest reply on Sep 22, 2010 9:03 AM by JR "Bob" Dobbs

    CF7 showing problem under high load

    User Since V1.5 Level 1

      We are seeing a problem in an online registration application and I am looking for any insight I can get to address it.

       

      The relevant system specs are as follows:

       

      Two Win2003 servers, each with 2GB memory and each running CF 7.0.2.142559 Standard edition. Sitting in front of them is an F5 BigIP load balancer. Once directed to a server, a user stays on that server, via 'sticky sessions' in the F5 and session management by CF. CF on each server is configured for 8 simultaneous requests and uses Java 1.4.2_09 with max memory setting of 512MB.

       

      CF on each server talks to a single SQL 2000 database on a separate box. I don't have the memory specs for that box, but I know that it's a beast. SQL is configured for unlimited connections.

       

      The problem is this: The registration application in question opens at a given time of day and is immediately hit with a huge number of attempted registrations. Only 4500 registrations can be accepted before registration must close. The last time this application was made available, more than 3000 registrations were booked in the first hour, and the entire application closed in slightly more than 2 hours.

       

      Each potential registrant must check whether registration is closed before he/she can start to register. Each does this by initially querying the database for a count of already submitted registrations. If that count is less than a predefined maximum value (4500, held in an application variable), we set a session variable to '1' for that registrant. The session variable allows the registrant to proceed to the registration form, fill it out and submit it. There is no additional checking just before the registration gets submitted -- if you're good to go when you start, you get to register even though the count might be above 4500 by the time you submit. (We usually end up only slightly over 4500.)

       

      The problem is this: display response time for the application is terrible. Almost immediately upon opening, it begins to take a great deal of time to display the registration form. What's worse, certain select lists on that form, which are built via <cfselect> using application variables (examples: country, state) sometimes do not get correctly displayed.

       

      Clearly CF is overworked, and I need to know where the bottleneck is. Are the individual session variables quickly maxing out memory? (Session-variable timeout is set at 15 min.) If so, would adding more memory per server and/or more servers, and/or increasing the JVM memory setting, help? Is CF queueing up a huge number of requests to SQL and is that what's slowing everything down? If so, will increasing the max number of simultaneous requests beyond 8 help -- and if so, how high can I go? (I've read elsewhere that setting this number is somewhat of a black art, dependent not only on total server memory but also on JVM memory and on the nature of the application.)

       

      Any suggestions will be greatly appreciated.

        • 1. Re: CF7 showing problem under high load
          JR "Bob" Dobbs Level 4

          A couple things to consider.

           

          1. Try setting your JVM heap size to 1024 to increase the amount of memory available to the Java runtime.  Note that 1024 is the maximum permitted on a 32 bit server.
          http://go.adobe.com/kb/ts_tn_19359_en-us

           


          2. Run the SQL Server profiler on your database server to identify any slow running SQL queries.  You might do this on a development server as to not increase the load on your production server.

           


          3.  Application variables

          certain select lists on that form, which are built via <cfselect> using application variables (examples: country, state) sometimes do not get correctly displayed.

          How are these lists being built?  Can you post the code?

           

           

          4. Examine what is being put into the session scope.  Avoid putting large amounts of data into this scope if possible.

           

          5. Consider wrapping complex database-only logic in stored procedures.

          There is no additional checking just before the registration gets submitted -- if you're good to go when you start, you get to register even though the count might be above 4500 by the time you submit. (We usually end up only slightly over 4500.)

           

          You could try addressing this by handling the registration in a stored procedure that checks the count inside a transaction.


          6. Update your JVM to 1.4.2_11 or newer.  I *think* that CF is certified to run on 1.4.2_15.  This may not increase your performance, but it will avoid daylight savings time related issues.
          http://go.adobe.com/kb/ts_d2ab4470_en-us

          • 2. Re: CF7 showing problem under high load
            User Since V1.5 Level 1

            This is the code that builds a select list for a 'Country' dropdown:

             

             

            The query is just

             

            SELECT CountryID, CountryAbbrev, CountryName FROM ct_Country
                    ORDER BY CountryName

             

            All variables are already in the application scope, except session.ProfileData, a struct that stores approximately 30 values relevant to the registrant's session, such as first name, last name, etc. (Also among these is the 1/0 value that tells whether registration can continue. All keys default to <empty string> if text, '0' if numeric.)

             

            'Large amounts of data' is a relative term. Every registrant or potential registrant has a copy of this struct. Is there some formula that I can use to estimate how much memory it consumes, if I know the max length of each of those 30 values?

             

            I will increase the JVM memory and investigate your other suggestions. The problem is that it's difficult if not impossible to simulate the load, and the app runs like a champ under 'normal' conditions. Re stored procedures: I will look into this as well, although I probably wouldn't check the count within the procedure (and reject the registration upon submission). Doing that only when the registrant is ready to submit strikes me as a formula for dissatisfied registrants, and as I said, we really don't mind going slightly over our limit (or can compensate by setting the limit slightly lower).

            • 3. Re: CF7 showing problem under high load
              User Since V1.5 Level 1

              Sorry, the <cfselect> code seems to have disappeared in my previous post:

               

              <cfselect size="1" name="CountryOfResidenceID" query="application.Countries" value="CountryID" display="CountryName" selected="#StructFind(session.ProfileData, "CountryOfResidenceID")#"></cfselect>

              • 4. Re: CF7 showing problem under high load
                JR "Bob" Dobbs Level 4

                Regarding the country query: 

                How often does the SQL query execute? 

                 

                Regarding " Is there some formula that I can use to estimate how much memory it consumes, if I know the max length of each of those 30 values?": 

                Not that I'm aware of.  You might invesigate the CF and Java profiling tools listed at http://www.carehart.org/cf411/#tools to examine the resources being used by your application. 

                 

                You might try re-creating a high load on a testing server either by writing a robot that creates a large number of HTTP requests or using a stress testing tool.   Microsoft has one for IIS, but I've not used it myself.

                http://msdn.microsoft.com/en-us/library/ms524518%28VS.85%29.aspx

                • 5. Re: CF7 showing problem under high load
                  User Since V1.5 Level 1

                  The country query, like all of those for application variables, executes just once unless CF gets rebooted. In application.cfm, we have this code:

                   

                  <cfif NOT #IsDefined("application.Countries")#>
                      <cflock scope="application" timeout="15" throwontimeout="Yes">
                          <cfquery name="application.Countries" datasource="#request.DataSource#">
                          SELECT CountryID, CountryAbbrev, CountryName FROM ct_Country
                          ORDER BY CountryName
                          </cfquery>

                       </cflock>

                  </cfif>

                   

                  The only query that would execute for every registrant -- and then only once, when the registrant first comes to the application -- is the one that checks the number of already submitted registrations.

                  • 6. Re: CF7 showing problem under high load
                    Jochem van Dieten Level 4

                    You are having an exclusive application scope lock there. That means that while that code is running nobody else can access any other code in an application scoped lock. Are you using cflock in more places?

                     

                    You say that the only query you run for each application is the count of previously submitted queries. But don't you store the data users enter? And do you do so in a transaction or not?

                    • 7. Re: CF7 showing problem under high load
                      Dan Bracuk Level 5

                      Regarding:

                      Each potential registrant must check whether registration is closed  before he/she can start to register. Each does this by initially  querying the database for a count of already submitted registrations. If  that count is less than a predefined maximum value (4500, held in an  application variable), we set a session variable to '1' for that  registrant. The session variable allows the registrant to proceed to the  registration form, fill it out and submit it.

                       

                      It might not make that much of a difference, but you can save some processing time here.  Try something like initializing an application variable to 0 before registration opens.  Then increment it on each registration.  That will save you many trips to the db to count records.

                      • 8. Re: CF7 showing problem under high load
                        Jochem van Dieten Level 4

                        Dan Bracuk wrote on 8/19/2010 4:18 AM:

                        It might not make that much of a difference, but you can save some processing time here.  Try something like initializing an application variable to 0 before registration opens.  Then increment it on each registration.  That will save you many trips to the db to count records.

                         

                        This won't work, there are 2 CF servers and they don't keep the

                        application variable in sync.

                         

                        I do expect this to be the other big problem (next to cflock usage) in

                        this application. Since this is MS SQL Server the database will do

                        predicate locking. That means on every count query not just all the

                        records that do match the predicate will be locked, but also all records

                        that will match the predicate in the future. Essentially there is an

                        exclusive lock on the table whenever the count runs. Now I presume the

                        table gets updated whenever a user starts the registration and again

                        when the user completes the registration. That is one giant lock

                        conflict, especially if the updates are run in some sort of transactions

                        with potentially more processing going on.

                         

                         

                        We need a better outline of what your code does to make better guesses.

                        1 person found this helpful
                        • 9. Re: CF7 showing problem under high load
                          User Since V1.5 Level 1

                          Thank you for this input, and for your previous post as well.

                           

                          Regarding the application locks: There are several similar sections, all in application.cfm -- and I see your point: Theoretically, several thousand people could simultanously enter the registration application and each could discover that application.countries, say, is not defined -- leading to several thousand simultaneous application-scope locks as each visitor tries to build application.countries.

                           

                          However, I don't think this is likely. Even when registration is closed, there is a page that potential registrants visit, advising them of that fact. This page, like all others, invokes application.cfm, and thus the if/then logic designed to initialize the various application-scope variables. Application-scope variables are spec'd for an inactivity timeout of 2 days, so the only time the scenario described above could occur would be immediately after ColdFusion gets rebooted (which is never the case). In all other cases, all application-scope variables are always 'alive', hence the locks never get invoked.

                           

                          Nevertheless, I do take your point. I will move all of the application-scope initialization/refresh code into a separate file, which I will then execute as a scheduled task on each CF server, once per day at, say 6:00 AM. That will certainly remove some if/then processing overhead for each registrant.

                           

                          Now, regarding the interaction with SQL: A simple insert query does of course run when the registrant submits the registration. Until the registration is actually submitted, all registration information exists only in the registrant's session (the session.ProfileData struct). There is no scenario like the one you describe (create record upon start of registration, then update it upon submission of registration).

                           

                          Your information regarding the count query is interesting, and I have a feeling that this is a major source of the problem. Can you suggest an alternative approach? The suggestion to keep the current registration count in an application variable will not work because there are two servers involved, as you point out.

                          • 10. Re: CF7 showing problem under high load
                            Dan Bracuk Level 5

                            Regarding:

                             

                            Now, regarding the interaction with SQL: A simple insert query does of course run when the registrant submits the registration. Until the registration is actually submitted, all registration information exists only in the registrant's session (the session.ProfileData struct). There is no scenario like the one you describe (create record upon start of registration, then update it upon submission of registration).

                             

                            That session variable is probably contributing to your problem.  Take a look at what is in there and strip out anything that is not absolutely necessary.  If the registration involves more than one form, hidden fields might be an acceptable substitute for session variables.

                             

                             

                             

                            Regarding:

                            Your information regarding the count query is interesting, and I have a feeling that this is a major source of the problem. Can you suggest an alternative approach? The suggestion to keep the current registration count in an application variable will not work because there are two servers involved, as you point out.

                             

                            You could stray from strict database normalization and store the current count in another table.  The users will still have to select it, but it might be a quicker query.

                            • 11. Re: CF7 showing problem under high load
                              User Since V1.5 Level 1

                              That session variable is probably contributing to your problem.  Take a look at what is in there and strip out anything that is not absolutely necessary.  If the registration involves more than one form, hidden fields might be an acceptable substitute for session variables.

                               

                              The registration is a single form, and as stated in the original post, the session struct that stores the registrant's information only contains about 30 keys, each key being either a single number or a short (100 chars max) character string. We maintain the registrant information in a struct because before the registrant submits, we display his information and give him a chance to change it, hence want to be able to re-display the form with the current information in place.

                               

                              Even being extremely generous, I can't see the raw number of bytes representing all of the registrant's information to be greater than, say, 5K. What I don't know, and what no one can apparently tell me, is how much memory ColdFusion requires to maintain that 5K in a struct. 10K? 100K?

                               

                              Assuming the value is 10K (a 100% overhead), does that mean that 500M of memory could support 50,000 concurrent sessions (500M/10K)? I know this is highly oversimplified -- but what it does lead me to believe is that the session is not the primary cause of my problem.

                              You could stray from strict database normalization and store the current count in another table.  The users will still have to select it, but it might be a quicker query.

                               

                              Could I not achieve the same effect by having each user execute

                               

                                   <cfquery name="get_count" datasource="#request.DataSource#">

                                        SELECT theRecordID FROM theRegistrationTable

                                   </cfquery>

                               

                              and then examining the #RecordCount# attribute that this <cfquery> returns? This assumes, of course, that this query would not trigger the kind of locking that a SELECT COUNT does, which I'm becoming increasingly convinced is the primary issue here.

                              • 12. Re: CF7 showing problem under high load
                                Dan Bracuk Level 5

                                regarding:

                                Could I not achieve the same effect by having each user execute

                                 

                                     <cfquery name="get_count" datasource="#request.DataSource#">

                                          SELECT theRecordID FROM theRegistrationTable

                                     </cfquery>

                                 

                                and then examining the #RecordCount# attribute that this <cfquery> returns? This assumes, of course, that this query would not trigger the kind of locking that a SELECT COUNT does, which I'm becoming increasingly convinced is the primary issue here.

                                 

                                That's inefficient in that you are bringing a couple thousand records into coldfusion for each user just so you can count them.

                                • 13. Re: CF7 showing problem under high load
                                  Swift Level 1

                                  I have a different solution for you. It might be considered quite drastic, but I would guarantee you that your performance problems would disappear completely. I know because I have already used this method for a somewhat similar situation.

                                   

                                  The problem is (I'm fairly certain) that the database accesses are slowing you down. Very likely because the locking is not doing what you expect, but also likely is that a single insert into a large table takes longer than you might be thinking. Especially if there are a number of indexes on that table.

                                   

                                  So, the solution is, don't access the database. Not at all. Not during the whole registration process, not even when done and you need to insert the data.

                                   

                                  Ok, I know it sounds crazy, because you will in fact access the database, but it will not be the user who does it. Instead you will add the registrants information to a memory array variable (I suggest the application scope), and use a scheduled task to take that data and insert it into the table. Two issues arise from this method, one is the fact that you have two servers, and the application scope is not shared across servers, and the second is what happens if you have a crash and lose your array? And there might be more issues to resolve also, but I cannot find any that are unresolvable.

                                   

                                  The issue of having two different application scopes should not pose you a problem. I know you've mentioned that you cannot use this method already. In fact you mentioned it more than once, but with all due respect you are wrong. One server can update the application scope of the other server, simply call a page on the other server. We use a webservice to accomplish this (call a function in the remote cfc on the other server), but you could also use a CFHTTP call if you wanted. You would not need to call this every time you add a new registrant. Perhaps set your variable to 0, and increment it up to 2250 (or just use an ArrayLen(application.yoursharedscopearray) call to determine how many registrants you already have). At that point you call a webservice (or just a cfm page) to indicate to the other server that I am full now, and then when it gets full, and knows that you are full too it can stop admitting registrants. You can either push new registrants over to the other box when one fills up, or return back from your webservice how many slots are remaining on the other box and then halve that number for each box. So, if server A fills up to 2250, and server B has 500 left, you could instruct server B that it has only 250 left now, and let server A accept 250 more. You might consider it a little hokey, but it would work for you.

                                   

                                  The second problem is what happens when you lose your array. Well, the simple answer is don't lose it. Have a scheduled task run once a minute that takes the values from the array (which are completed registrations - the whole data row and any other associated data) and inserts them into the database. If it takes 10 seconds to insert, or 30, that's OK, it's a scheduled task doing it. The user will not have to wait for locking issues. You could also CFFile it out to a file to capture the data as well, but I don't think you'd need to, and I'm not sure that's quicker than a database call anyway. If a CFFile is quicker, then you could have each user run the CFFile to put the data on the disk, and then report to them that their registration is complete, and then your scheduled task could take that file and insert the values into the database. You would never lose the data then, and it would still be quicker than a database call I think. Still though, I would prefer a shared scope array personally, with a scheduled task to insert it into the database. That what I use right now.

                                   

                                  Will you have enough memory to store them all? I think you absolutely should. You would have to experiment to see what was created. The math works out though. As someone suggested, I also recommend going up to 1024MB for the minimum and maximum java heap size in the jvm.config. If the shared scope array does in fact get too big, you could delete out the rows as you insert them into the database. That would not be a big deal at all.

                                   

                                   

                                  Anyway, when all is said and done it is a little drastic, and a departure from the method you have. But optimizing your queries, and removing indexes on the table (which I recommend by the way) will only go so far. Accessing memory variables will hands down beat a database. It's the difference between RAM and your hard drive (sans SSDs of course).

                                   

                                  Swift

                                  1 person found this helpful
                                  • 14. Re: CF7 showing problem under high load
                                    tclaremont Level 2

                                    Anyone else think that 2GB of ram on a windows server running CF is a bit low for this amount of work?

                                    • 15. Re: CF7 showing problem under high load
                                      josh.adams Level 1

                                      Swift's post of yesterday has some very interesting thoughts in it. But for something that's likely less drastic: have you tried ColdFusion 9? We've really made some great performance improvements in ColdFusion since ColdFusion MX 7. However, if the database access is the problem, you are going to have to do something drastic somewhere--and, as has been suggested by several posters, the first place I recommend you look is to the database.

                                      • 16. Re: CF7 showing problem under high load
                                        josh.adams Level 1

                                        For asynchronous processing, consider using an event gateway instead of scheduled tasks. And if you upgrade to ColdFusion 9 (and, FWIW, if you were on ColdFusion 8, this would be the same there too) you have the option of using asynchronous threads directly in your ColdFusion code.

                                        • 17. Re: CF7 showing problem under high load
                                          JaneUK

                                          Might I suggest looking into the use of the cachedAfter and cachedWithin in queries that only need to be run once? Far easier and safer than locks.


                                          • 18. Re: CF7 showing problem under high load
                                            josh.adams Level 1

                                            As for locking: I don't see where anyone pointed out that locking is only something that needs to be done in ColdFusion these days when you're concerned about race conditions.  There are no such concerns here so it's best to remove the locks (more on that in a moment).  However, at least in the code sample provided, the lock only activates when Application.Countries doesn't exist and since Application.Countries almost always exists, I'd be very surprised if you get a big performance boost from removing the locks since they're mostly not actually activating.

                                             

                                            A little more color on removing the locks:  what's going to happen here if a request hits the conditional, finds Application.Countries doesn't exist, then makes the call for the query and then a second request comes along before the query comes back?  The query is going to execute for that second request too of course whereas with a lock it wouldn't.  However, it's a "no harm, no foul" situation:  the query is going to come back with same info both times and so it's not a race condition, it's simply a waste of a trip to the database.  And that brings us to JaneUK's great point:  use query caching and you can avoid that too.

                                            • 19. Re: CF7 showing problem under high load
                                              User Since V1.5 Level 1

                                              Thanks for all responses. As I noted in response to an earlier post, I've extracted all the code that builds application-level variables into a scheduled event that runs once per day, so application-level locking via <cflock> is no longer even a potential issue.

                                               

                                              Swift's reply raises interesting possibilities, but I don't think that I can totally re-engineer the application at this time. Ditto for upgrading to CF 8 or 9.

                                               

                                              I therefore plan to focus on optimizing the database interaction. The general plan is this:

                                               

                                              1. Add a database field that stores the current number of completed registrations.When a potential registrant arrives, the first thing he does is query this field. If the value returned is less than the max number allowed (which is stored in an application variable), his session is flagged to allow him to complete his registration -- even if the max value actually gets reached before he does his final submission. (As I stated initially, we don't mind if the registration count goes slightly over the max value allowed.)

                                               

                                              2. The registrant has no further database interaction until he is ready for final submission. (All registration data is stored in a session-level struct.) Upon submission, the registrant does an insert query of his information, a second query to get the current count-field value, and an update query that increments that value. These three queries are enclosed in <cftransaction>.

                                               

                                              3. I will also investigate whether increasing the CF setting for 'max number of simultaneous requests' beyond 8 has any measurable effect, and likewise for increasing the JVM heap size from 512M to 1024M.

                                               

                                              Can anyone tell me whether an insert query that's configured to use <cfqueryparam> throughout will run more quickly than the same query without <cfqueryparam>? I know that use of <cfqueryparam> is best practice, and it's what I usually do. But I've seen some preliminary testing data that seems to show the query is actually faster without <cfqueryparam>.

                                              • 20. Re: CF7 showing problem under high load
                                                JR "Bob" Dobbs Level 4

                                                As a general rule parameterized SQL statements will execute faster than those without parameters.   This is because SQL Server will cache the execute plan for parameterized statements and re-use them.  You might look at the article linked below and use SQL profiler to see how your SQL statements are executing on your database.  The statements you are executing may not necessarily benefit from caching; although I recommend use of CFQUERYPARAM for type checking and as protection against SQL injection.

                                                 

                                                "Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005"

                                                http://msdn.microsoft.com/en-us/library/cc966425.aspx

                                                 

                                                "Getting ColdFusion SQL Statements from SQL Server Trace"

                                                http://www.petefreitag.com/item/674.cfm

                                                 

                                                You might also examine how database transactions are being used and see if they have a negative effect on performance.