Announcement

Collapse
No announcement yet.

Query modification to improve performance?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query modification to improve performance?

    Our host has reviewed our server issues and advised this:

    The following query is poorly optimized.

    SELECT COUNT(*) AS count FROM post WHERE dateline >= 1140325200;

    Since there is no index on the 'dateline' column of 'posts' mysql must read through the entire +2GB table to get the count. This datestamp represents is a relatively short range of dates so an index would be quite benificial in identifing the required posts. Currently when the count is in progress the table will be unable to be modified by another query. If an update query has priority as next query following the "select count", the table will block all other selects from completing untill the update finishes. This causes a case where lots and lots of queries which need access to the 'posts' table are stuck in the processlist. Thus making the apache process wait. This then causes the number of apache processes to build up and reach MaxClients. I believe if we add an index to 'posts.dateline' then your server will perform much better and you will have fewer issues with overload.

    However the size of this table means that the process to add an index could take quite some time to complete. During this time no queries may access to table. I would like to do this modification with the forum disabled. This could be scheduled to be done at a time where it will cause the least interruption to service. Please let us know what time we could perform this maintence.
    I would like to know if:

    1. This modification would indeed improve performance and is good advice.
    2. Considering I plan to upgrade to the most current version of vB over the next few days, would the upgrade address what the modification would, doing away with the need for the modification?
    Eat Your Veggies

  • #2
    Thats not a default vbulletin query, looks like one of mine, or others get the ammount of new popsts since your last visit, and I did advise adding indecies.

    If you upgrade that query wouldn't be there by default anymore.

    Comment


    • #3
      If the upgrade will resolve it then I'll simply upgrade. Thanks Zachery.
      Eat Your Veggies

      Comment


      • #4
        Assuming you are running 2.x or 3.x, upgrading to 3.5 (or from 2.0. to 3.0) should fix the issue.

        Not sure what version you are running or where its stored. if you are running 3.5.x and upgrading to 3.5.y you might need to take other action.

        Comment


        • #5
          On second thought, I'm a bit confused. If it's not a default query, why would that query be removed by default?
          Eat Your Veggies

          Comment


          • #6
            We're running 3.0.10

            Any idea what modification that query might be for, if it is one of yours Zachery?
            Eat Your Veggies

            Comment


            • #7
              Hmm, check your phpinclude_start template and see what code is in there, I believe I released a template mod to do this in the 3.0.x days.

              Since its not default, upgrading from 3.0.x to 3.5 would remove it, regardless if its in the files (they got overwritten) or in the phpinclude_* templates as they are no longer used.

              If its in the phpinclude_* templates then its going to run if you were to upgrade to 3.0.12.

              Comment


              • #8
                I would upgrade to 3.5.3

                Thanks Zachery. I'll upgrade asap and hopefully everything will be resolved.
                Eat Your Veggies

                Comment


                • #9
                  Originally posted by Cynthia
                  I would upgrade to 3.5.3

                  Thanks Zachery. I'll upgrade asap and hopefully everything will be resolved.
                  Hi Cynthia,

                  Do you have upgraded your forum? Do you still have this problem?

                  Seems like this query is also causing me a lot of problem... (vb 3.06)
                  http://www.fnnation.com/pub/small/fnforum.gif http://www.fnnation.com/pub/small/luttemedia.gif

                  Comment


                  • #10
                    I did upgrade and optimized our server. So far so good.
                    Eat Your Veggies

                    Comment


                    • #11
                      This particular query come from this hack:
                      http://www.vbulletin.org/forum/showthread.php?t=60451
                      ([vB3 RC3] Welcome Hack v1.2)

                      Look at this:

                      Code:
                       
                      Query:
                       
                          SELECT COUNT(*) AS count 
                          FROM post 
                          WHERE dateline > '1142475009' 
                      Time before: 0.19997811317444
                      Time after: 0.20080804824829
                      Time taken: 0.00082993507385254
                      table type possible_keys key key_len ref rows Extra 
                      post  ALL          293603  Using where  
                       
                      --------------------------------------------------------------------------------
                      Query:
                       
                          SELECT COUNT(*) AS count 
                          FROM thread 
                          WHERE lastpost > '1142475009' 
                      Time before: 0.2014000415802
                      Time after: 0.20280599594116
                      Time taken: 0.0014059543609619
                      table type possible_keys key key_len ref rows Extra 
                      thread  index    forumid  10    19404  Using where; Using index
                      If I understand... MySQL must scan more tha 293 000 rows each time the index page is loaded. Is my logic right?

                      Well... if I'm right...I think that explain why MySQL is using so much CPU...

                      Last edited by RaZor Edge; Wed 15 Mar '06, 9:31pm.
                      http://www.fnnation.com/pub/small/fnforum.gif http://www.fnnation.com/pub/small/luttemedia.gif

                      Comment


                      • #12
                        I'll let the more knoweledgable folks here offer their thoughts. I'm really out of their league in such things.
                        Eat Your Veggies

                        Comment

                        Related Topics

                        Collapse

                        Working...
                        X