Announcement

Collapse
No announcement yet.

Slow queries and maxed out CPU - vB 3.8.7

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

  • #16
    In my experience there are 2 common queries related to PMs that can get slow... One isn't a big deal because it can't be triggered by anyone other than an admin... but when it does happen, it's definitely the nastiest. When you merge users (or just change a user's username), it has to do a full table scan with a find/replace of the pmtext table to update the usernames.

    The other one is less nasty, more common and easy to fix. When a user is looking at their list of PMs, the way vBulletin sorts them, it forces all the PMs for the user to be dumped to a temporary table to do the sort. Instead of sorting by dateline, you can instead sort by pmid, and you should get the same sort order (with some exceptions like if you did an import of PMs from a different installation/system recently). You can force the more efficient sort by creating a plug-in at the private_messagelist_filter hook location with the following code:
    PHP Code:
    if ($sortfield == 'pmtext.dateline'$sortfield 'pm.pmid'
    Sphinx Search for vBulletin 4: https://marketplace.digitalpoint.com...tin-4.870/item
    Someone send me a message on Twitter when this site is usable again. https://twitter.com/digitalpoint

    Comment


    • #17
      i dont have a private_messagelist_filter hook location, just ~_messagebit ~_start and ~_period
      im still on vb3.8.7 if that matters.
      What you're saying makes sense though... it would be nice to remove that who issue from the problem and see where we stand. who knows, that could be the big one causing the rest to bog down behind it.

      Comment


      • #18
        Oh yeah... dunno about vB3... hook location is def. in vB4 though.

        Still should enable slow query log and see what's up there...
        Sphinx Search for vBulletin 4: https://marketplace.digitalpoint.com...tin-4.870/item
        Someone send me a message on Twitter when this site is usable again. https://twitter.com/digitalpoint

        Comment


        • #19
          1.
          VPS

          2.
          Quad core Xeon 2.13ghz
          4.5gig RAM
          MySql: 5.0.92-community
          PHP: 5.2.8
          Apache: Apache v1.3.41

          Code:
          turbobricks.com [/etc]# cat /proc/cpuinfo
          processor       : 0
          vendor_id       : GenuineIntel
          cpu family      : 6
          model           : 26
          model name      : Intel(R) Xeon(R) CPU           E5506  @ 2.13GHz
          stepping        : 5
          cpu MHz         : 2133.463
          cache size      : 4096 KB
          physical id     : 0
          siblings        : 4
          core id         : 0
          cpu cores       : 4
          apicid          : 0
          fpu             : yes
          fpu_exception   : yes
          cpuid level     : 11
          wp              : yes
          flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
          bogomips        : 4266.92
          clflush size    : 64
          cache_alignment : 64
          address sizes   : 40 bits physical, 48 bits virtual
          power management: [8]
          
          processor       : 1
          vendor_id       : GenuineIntel
          cpu family      : 6
          model           : 26
          model name      : Intel(R) Xeon(R) CPU           E5506  @ 2.13GHz
          stepping        : 5
          cpu MHz         : 2133.463
          cache size      : 4096 KB
          physical id     : 0
          siblings        : 4
          core id         : 1
          cpu cores       : 4
          apicid          : 2
          fpu             : yes
          fpu_exception   : yes
          cpuid level     : 11
          wp              : yes
          flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
          bogomips        : 4266.80
          clflush size    : 64
          cache_alignment : 64
          address sizes   : 40 bits physical, 48 bits virtual
          power management: [8]
          
          processor       : 2
          vendor_id       : GenuineIntel
          cpu family      : 6
          model           : 26
          model name      : Intel(R) Xeon(R) CPU           E5506  @ 2.13GHz
          stepping        : 5
          cpu MHz         : 2133.463
          cache size      : 4096 KB
          physical id     : 0
          siblings        : 4
          core id         : 2
          cpu cores       : 4
          apicid          : 4
          fpu             : yes
          fpu_exception   : yes
          cpuid level     : 11
          wp              : yes
          flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
          bogomips        : 4266.86
          clflush size    : 64
          cache_alignment : 64
          address sizes   : 40 bits physical, 48 bits virtual
          power management: [8]
          
          processor       : 3
          vendor_id       : GenuineIntel
          cpu family      : 6
          model           : 26
          model name      : Intel(R) Xeon(R) CPU           E5506  @ 2.13GHz
          stepping        : 5
          cpu MHz         : 2133.463
          cache size      : 4096 KB
          physical id     : 0
          siblings        : 4
          core id         : 3
          cpu cores       : 4
          apicid          : 6
          fpu             : yes
          fpu_exception   : yes
          cpuid level     : 11
          wp              : yes
          flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr sse4_1 sse4_2 popcnt lahf_lm
          bogomips        : 4266.92
          clflush size    : 64
          cache_alignment : 64
          address sizes   : 40 bits physical, 48 bits virtual
          power management: [8]
          3.
          vBulletin 3.8.7

          4.
          tables are MyISAM

          5.
          No idea, loaded by host. Slow queries is not enabled

          6.

          This is the load when its normal.. i will try and grab this when its acting up:
          Code:
          top - 07:11:11 up  5:44,  1 user,  load average: 2.75, 1.32, 0.91
          Tasks: 102 total,   1 running, 100 sleeping,   0 stopped,   1 zombie
          Cpu0  :  8.4% us,  1.0% sy,  0.0% ni, 33.8% id, 56.9% wa,  0.0% hi,  0.0% si
          Cpu1  :  9.1% us,  1.0% sy,  0.0% ni, 40.6% id, 49.3% wa,  0.0% hi,  0.0% si
          Cpu2  :  1.0% us,  0.3% sy,  0.0% ni,  4.3% id, 94.3% wa,  0.0% hi,  0.0% si
          Cpu3  :  4.3% us,  0.3% sy,  0.0% ni, 66.9% id, 28.4% wa,  0.0% hi,  0.0% si
          Mem:   4608000k total,   676064k used,  3931936k free,        0k buffers
          Swap:        0k total,        0k used,        0k free,        0k cached
          7.
          Code:
          turbobricks.com [/etc]# cat my.cnf
          [mysqld]
          skip-bdb
          max_connections = 400
          key_buffer = 48M
          myisam_sort_buffer_size = 32M
          join_buffer_size = 1M
          read_buffer_size = 1M
          sort_buffer_size = 2M
          table_cache = 1024
          thread_cache_size = 286
          interactive_timeout = 25
          wait_timeout = 25
          connect_timeout = 10
          max_allowed_packet = 16M
          max_connect_errors = 10
          query_cache_limit = 1M
          query_cache_size = 16M
          query_cache_type = 1
          tmp_table_size = 16M
          skip-innodb
          
          [mysqld_safe]
          open_files_limit = 8192
          
          [mysqldump]
          quick
          max_allowed_packet = 16M
          
          [myisamchk]
          key_buffer = 32M
          sort_buffer = 32M
          read_buffer = 16M
          write_buffer = 16M
          8.
          No root access

          9.
          vb is the only database, theres a website as well @ http://www.turbobricks.com

          10.
          average users around 250, normal max users i'd say 375. Cookie session timeout in the vb config is set to 900

          11.
          http://www.turbobricks.com/admin/stuff.php

          12.
          dont have access to it through terminal and my WHM is screwed up after the last outage a few hours ago - waiting on host to fix it.

          13.
          None over 2gig except a backup file of the db.

          14.
          turbobricks.com [/etc]# uname -a
          Linux hou1-web1 2.6.18-194.26.1.el5.028stab079.2 #1 SMP Fri Dec 17 19:25:15 MSK 2010 i686 i686 i386 GNU/Linux
          turbobricks.com [/etc]# ulimit -aH
          core file size (blocks, -c) 200000
          data seg size (kbytes, -d) 200000
          file size (blocks, -f) unlimited
          pending signals (-i) 106496
          max locked memory (kbytes, -l) 32
          max memory size (kbytes, -m) 200000
          open files (-n) 100
          pipe size (512 bytes, -p) 8
          POSIX message queues (bytes, -q) 819200
          stack size (kbytes, -s) 8192
          cpu time (seconds, -t) unlimited
          max user processes (-u) 20
          virtual memory (kbytes, -v) 200000
          file locks (-x) unlimited

          Comment


          • #20
            Unless you have it overridden for the MySQL "user", you are going to have a problem with the operating system limiting users to 100 open files. MySQL uses multiple files per connection, and you aren't going to be able to open enough files for your maximum number of connections (400).
            Sphinx Search for vBulletin 4: https://marketplace.digitalpoint.com...tin-4.870/item
            Someone send me a message on Twitter when this site is usable again. https://twitter.com/digitalpoint

            Comment


            • #22
              Originally posted by punchbowl View Post
              meant to say: leave out the line
              Code:
              skip-innodb
              from my.cnf as panels all seem to use innodb
              12.
              dont have access to it through terminal and my WHM is screwed up after the last outage a few hours ago - waiting on host to fix it.
              I did the same myself in that thread I linked earlier hence my warning. whm uses innodb as far as I know so skipping it is a disaster. so take out the skip innodb from my.cnf.

              I did exactly the same time back in that thread I linked. Haven't used whm since.

              Unless it's something else altogether.

              Comment


              • #23
                yeah it was a cpanel/whm license thing that got screwed up, works now.

                Comment


                • #24
                  Does anyone know the way to fix this in vb3.8.7?
                  Originally posted by digitalpoint View Post
                  The other one is less nasty, more common and easy to fix. When a user is looking at their list of PMs, the way vBulletin sorts them, it forces all the PMs for the user to be dumped to a temporary table to do the sort. Instead of sorting by dateline, you can instead sort by pmid, and you should get the same sort order (with some exceptions like if you did an import of PMs from a different installation/system recently). You can force the more efficient sort by creating a plug-in at the private_messagelist_filter hook location with the following code:
                  PHP Code:
                  if ($sortfield == 'pmtext.dateline'$sortfield 'pm.pmid'
                  There is a private_messagelist_start, private_messagelist_messagebit and private_messagelist_period
                  I buy 420 forums

                  Comment


                  • #25
                    Anders - any chance you have some pointers for me? at least something to start with for my.cnf etc. I'd be willing to hire someone (you?) to just knock out all these tweaks and changes or even just go through and upgrade the board while you're at it and i'll mess with the styles later.

                    also - im thinking this thread should be moved but up to you.
                    Last edited by tryckjävel; Mon 18th Apr '11, 11:09pm. Reason: added stuff

                    Comment


                    • #26
                      report your server stuff as a new server optimization thread in the server config forum

                      Comment


                      • #27
                        cool, doing that now.

                        Just as a side bit on the actual issue... i disabled search all together and it hasnt helped at all.

                        Comment

                        widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                        Working...
                        X