Announcement

Collapse
No announcement yet.

MySQL hammering my server after update to 5.6.3

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

  • [Bug / Issue] MySQL hammering my server after update to 5.6.3

    I updated two of my forums to 5.6.3 last night but my busier forum is causing the server to overload. The hosting support techs reported back:

    So far I have been able to narrow down the source of the load to the e-group.uk.net web site. The site is running the following queries I have included below.

    | 322 | egroup_vb | localhost | egroup_vb | Query | 4159 | Copying to tmp table | SELECT DISTINCT node.starter AS nodeid

    These queries never seem to finish and keep running constantly. When I first logged into your server there were over 30 of these running. After killing the queries the load dropped dramatically but soon the queries began to return.


    Is there an obvious fix for this? Eventually the situation reaches a load level that prevents the forum from serving pages to members.

  • #2
    PHP Version? MySQL Version? These should both be listed on the AdminCP home page when you log in.

    What table engine are the tables in your database using? You can see this in the AdminCP under Maintenance -> Diagnostics. Set the System Information diagnostic to MySQL Table Status and submit the tool. All but three of your tables should be listed as INNODB.
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API

    Comment


    • #3
      The PHP/MySQL details are:

      Click image for larger version

Name:	Screenshot 2020-09-15 000744.png
Views:	137
Size:	78.9 KB
ID:	4447372
      The Table Status lists a mixture of InnoDB and MyISAM and Memory, here is a clip of the list:
      aaggregate_temp_1314605400 MEMORY
      aaggregate_temp_1314630600 MEMORY
      aaggregate_temp_1314702600 MEMORY
      aaggregate_temp_1314724200 MEMORY
      ad InnoDB
      adcriteria InnoDB
      adminhelp MyISAM
      administrator MyISAM
      adminlog MyISAM
      adminmessage MyISAM
      adminutil MyISAM
      adv_modules MyISAM
      adv_pages MyISAM
      adv_pages_user MyISAM
      adv_setting MyISAM
      adv_settinggroup MyISAM
      announcement MyISAM
      announcementread MyISAM
      apiclient InnoDB
      apiclient_devicetoken InnoDB
      apilog InnoDB
      attach InnoDB
      attachment InnoDB
      attachmentcategory InnoDB
      attachmentcategoryuser InnoDB
      attachmentpermission MyISAM
      attachmenttype MyISAM
      attachmentviews MyISAM
      automediaembed MyISAM
      autosavetext InnoDB
      avatar MyISAM
      bbcode MyISAM
      bbcode_video InnoDB
      cache InnoDB
      cacheevent InnoDB
      calendar MyISAM
      calendarcustomfield MyISAM
      calendarmoderator MyISAM
      calendarpermission MyISAM
      channel InnoDB
      channelprefixset InnoDB
      closure InnoDB
      contentpriority InnoDB
      contenttype InnoDB
      cpsession InnoDB
      cron MyISAM
      cronlog MyISAM

      Comment


      • #4
        From a recent discussion about this same issue:

        You should use INNODB for the language, phrase, and usertextfield tables only.
        This is the advice Wayne Luke gave another user.

        You may also find Apache 2.4 functions better with vBulletin than MariaDB if you have that option.

        vBulletin does not test with MariaDB and there are a few known issues with using it.

        Comment


        • #5
          This beyond my skills - how is the choice of table engine determined?

          I will enquire about Apache 2.4.

          Edit, server status is: Server Version: Apache/2.4.46 (cPanel) OpenSSL/1.1.1g mod_bwlimited/1.4 mod_fcgid/2.3.9

          Comment


          • In Omnibus
            In Omnibus commented
            Editing a comment
            Find out if you have the ability to use MySQL 8 instead of MariaDB

          • imager
            imager commented
            Editing a comment
            In Omnibus Yes I have asked the hosting company to switch to MySQL 8. I will hopefully be done in a few hours.

          • OldGrumpyDad
            OldGrumpyDad commented
            Editing a comment
            What's the motivation for MySQL versus MariaDB? I'm on MariaDB 10.4.12 and it seems reasonable. Just upgrading MariaDB to a modern version (10.4 or 10.5) would accomplish the same goal. No need to change familiies. If you go with an open source database like MariaDB you get all the features it has. If you go with a commercial database like MySQL, you don't get features like threadpools unless you pay for a license. I'd stay off MySQL unless you really like Oracle and Oracle's approach to licensing.

        • #6
          All of the tables with aggregate in the table name and a type of Memory can be dropped. Just delete them out of the database. There are probably some named baggregate and taggregate as well. These are from an old 3.X bug.

          The basic server configuration appears to meet the minimum requirements for the software. It looks like the new vBulletin 5 tables are using INNODB, this is as it should be. However your older tables are using MyISAM. We have provided a script to change all of your vBulletin tables to the proper table type. You can find it in /do_not_upload/dbtools in your vBulletin 5.6.3 download package. The file is called "myisamfix.phar." It must be run from the command line.

          This tool will convert vBulletin 5 tables to INNODB from MyISAM. This change will generally provide a performance boost to a website. In addition it can help prevent table crashes and improve replication services. Before running this, use your database tool to check if it is needed. If your tables are already INNODB, there is no need to run this. This file will only convert default vBulletin 5 tables. Tables from previous versions or addon products will remain untouched.

          php myisamfix.phar -dofix
          From: https://forum.vbulletin.com/articles...database-tools

          When you're done, you will have tables that are still using MyISAM. Among vBulletin's default tables these will be the language, phrase, and userfield tables. However, you also have tables from addons in your database and these won't be converted.
          Translations provided by Google.

          Wayne Luke
          The Rabid Badger - a vBulletin Cloud demonstration site.
          vBulletin 5 API

          Comment


          • #7
            Originally posted by Wayne Luke View Post
            All of the tables with aggregate in the table name and a type of Memory can be dropped. Just delete them out of the database. There are probably some named baggregate and taggregate as well. These are from an old 3.X bug.

            The basic server configuration appears to meet the minimum requirements for the software. It looks like the new vBulletin 5 tables are using INNODB, this is as it should be. However your older tables are using MyISAM. We have provided a script to change all of your vBulletin tables to the proper table type. You can find it in /do_not_upload/dbtools in your vBulletin 5.6.3 download package. The file is called "myisamfix.phar." It must be run from the command line.



            From: https://forum.vbulletin.com/articles...database-tools

            When you're done, you will have tables that are still using MyISAM. Among vBulletin's default tables these will be the language, phrase, and userfield tables. However, you also have tables from addons in your database and these won't be converted.
            OK, I'm just going to check that the work to change from MariaDB isn't in progress and then I will run the script.

            Comment


            • #8
              We're still on MariaDB for now, but I have run the InnoDB conversion script and that appears to have worked as planned. I have restarted the MySQL server service and server load has dropped. Let's see if it stays low.
              Last edited by imager; Tue 15 Sep '20, 10:48am.

              Comment


              • #9
                Are you using memcached? It's a really valuable way to take load off the database server. The load on my database server is much lower because I'm using it. Every once in a while, memcached on my site crashes and when it does, the load on my database spikes up pretty high. I know exactly where to look.

                I also recommend downloading and using memcache-top to make sure it's working. I usually see results like this:

                Code:
                memcache-top v0.7 (default port: 11211, color: on, refresh: 3 seconds)
                
                INSTANCE        USAGE HIT % CONN TIME ITEMS EVICT/s READ/s WRITE/s
                localhost:11211  30.0% 0.0% 1 1.4ms 937.9K 0.0 1076 313.5K
                
                AVERAGE:         30.0% 0.0% 1 1.4ms 937.9K 0.0 1076 313.5K
                
                TOTAL:           0.2GB/ 0.8GB 1 1.4ms 937.9K 0.0 1076 313.5K

                Comment


                • imager
                  imager commented
                  Editing a comment
                  That's interesting but I will explore that later. The issue here is a sudden out of control issue with table requests.

              • #10
                Unfortunately, the server load remains too high and is creeping back towards a level that will stall vBulletin on that domain.

                The next step is for the hosting company to replace MariaDB with MySQL 8, which will probably take place overnight (UK time).

                Comment


                • #11
                  More explicit logs would be helpful in trying to figure out why it is always trying to create tmp files.
                  Translations provided by Google.

                  Wayne Luke
                  The Rabid Badger - a vBulletin Cloud demonstration site.
                  vBulletin 5 API

                  Comment


                  • #12
                    Originally posted by Wayne Luke View Post
                    More explicit logs would be helpful in trying to figure out why it is always trying to create tmp files.
                    Can you help me identify what to look for?

                    Comment


                    • #13
                      Any MySQL logs that you have particularly a slow query log. https://dev.mysql.com/doc/refman/5.7...rver-logs.html

                      They should give an idea of what is going on.
                      Translations provided by Google.

                      Wayne Luke
                      The Rabid Badger - a vBulletin Cloud demonstration site.
                      vBulletin 5 API

                      Comment


                      • #14
                        Maybe it's too difficult to switch from MariaDB to MySQL8, from my hosting company:

                        After looking into this it looks like you have existing databases on the server. Unfortunately since this is the case we can't downgrade MariaDB to MySQL 5.6 and then upgrade to MySQL 8 as this tends to break existing databases. The only way we can do this for you is it there aren't any databases on the server, in your case a new server would need to be created and then the content migrated over.

                        Please let me know how you'd like to proceed.


                        Comment


                        • OldGrumpyDad
                          OldGrumpyDad commented
                          Editing a comment
                          Changing families (MySQL to Maria or vice versa) like that is momentous. There are things that can break. I think there is value in having a more modern database server, for sure. But just upgrade from MariaDB 10.2 to MariaDB 10.4 or 10.5. That upgrade can definitely happen and it might improve the situation. But switching from one database family to the other is complicated and dangerous. You just need to modernise.

                          The mysql log that people are asking about is usually in the same directory as the MariaDB server itself. Like if your MariaDB is in /var/db/mariadb, then you'll find some file that's something like /var/db/mariadib/mysql-error.log. All of this changes from server to server. Your hosting provider can (and probably does) put this stuff wherever they feel like. So you can ask them where the MariaDB log file is and they should be able to tell you.

                      • #15
                        I should have the MySQL log shortly. Where should I send this?

                        Comment

                        Related Topics

                        Collapse

                        Working...
                        X