Announcement

Collapse
No announcement yet.

SQL Database too large

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

  • SQL Database too large

    I received a message from my host:
    It has come to our attention that your site is using an excessive amount of MySQL resources on your Bluehost account. This is causing performance problems not only on your own website, but for other customers that are on this same server. When left unchecked, it can potentially cause crashes or service interruptions and lead to additional downtime.

    Our research shows that server performance degrades when the MySQL usage is over 1000 tables and/or 3 GB on a single account or 1000 tables and/or 2 GB on a single database. In order to ensure optimal performance for your account and the others in your shared hosting environment, we request that you reduce the MySQL usage on your account to under these limits by 10/06/2014.

    Your account information:
    Total MySQL Database Size: 2391.98 MB
    Total MySQL Tables: 678

    My database under 3.8.8 was only 233 MB.

    My site has definitely been sluggish since the upgrade to 5.1.3.

    What can I do to get this database down to a workable size?


  • #2
    If you are storing attachments in the database, you can move them to the file system.

    Other than that, if your host is saying your database is too large, then you need a bigger hosting package or a better host. You can't just make the database smaller.
    MARK.B | vBULLETIN SUPPORT

    TalkNewsUK - My vBulletin 5.5.6 Demo
    AdminAmmo - My Cloud Demo

    Comment


    • #3
      Originally posted by wickedfate View Post
      I received a message from my host:
      It has come to our attention that your site is using an excessive amount of MySQL resources on your Bluehost account. This is causing performance problems not only on your own website, but for other customers that are on this same server. When left unchecked, it can potentially cause crashes or service interruptions and lead to additional downtime.

      Our research shows that server performance degrades when the MySQL usage is over 1000 tables and/or 3 GB on a single account or 1000 tables and/or 2 GB on a single database. In order to ensure optimal performance for your account and the others in your shared hosting environment, we request that you reduce the MySQL usage on your account to under these limits by 10/06/2014.

      Your account information:
      Total MySQL Database Size: 2391.98 MB
      Total MySQL Tables: 678

      Even though you posted this yesterday they sent you this message in June so presumably you've now fixed the problem?

      We started out in a shared environment then we discovered that performance wasn't guaranteed; so even though we never anticipated our site would be huge we went with a dedicated server because a site like yours could affect performance on our own site. It's a bit odd that your host hasn't offered you another solution though. I.e. reduce your usage to X or move to Y package if you wish to continue doing what you are doing.

      Comment


      • #4
        Originally posted by Mark.B View Post
        If you are storing attachments in the database, you can move them to the file system.

        Other than that, if your host is saying your database is too large, then you need a bigger hosting package or a better host. You can't just make the database smaller.
        What are the pros and cons of moving attachments from the database to the file system?

        Comment


        • #5
          Originally posted by jdj View Post

          What are the pros and cons of moving attachments from the database to the file system?
          Well it's generally better on large sites, or if you have a lot of attachments. It reduces load on the database (and space, obviously).
          MARK.B | vBULLETIN SUPPORT

          TalkNewsUK - My vBulletin 5.5.6 Demo
          AdminAmmo - My Cloud Demo

          Comment


          • #6
            In addition to moving your attachments to the file system:
            • Create a database backup.
            • Then drop the post, postedithistory, postlog, postparsed, postindex, search, thread, forum and word. None of these are needed for the operation of 5.X
            • Truncate the cache and cacheevent tables.
            Your database will probably be closer to its original size after this.
            Translations provided by Google.

            Wayne Luke
            The Rabid Badger - a vBulletin Cloud demonstration site.
            vBulletin 5 API - Full / Mobile
            Vote for your favorite feature requests and the bugs you want to see fixed.

            Comment


            • #7
              I have dropped the tables you suggested, along with truncating the cache and cacheevent tables. Yes, this brought the database down in size.

              How do I know the path to move attachments to the file system? I attempted to do it, but was unsure of the path.

              As well, my web host noticed that the site was moving very slowly, and I have noticed this after the upgrade.

              Comment


              • #8
                You have to create your own folder, and recursively chmod it to 777.
                https://www.vbulletin.com/docs/html/...rage_db_to_fs1
                MARK.B | vBULLETIN SUPPORT

                TalkNewsUK - My vBulletin 5.5.6 Demo
                AdminAmmo - My Cloud Demo

                Comment


                • #9
                  Thanks. I will try that.

                  Also, I noticed this morning when I went to look at the database again, the cache was up to almost a gig, after truncating it yesterday. I did the same today, and now the database has dropped in size. Is there something I can do to stop it from getting so big?

                  Comment


                  • #10
                    Have you run both truncates?

                    TRUNCATE TABLE cache
                    TRUNCATE TABLE cacheevent

                    (Note: do not run TRUNCATE queries on any other tables, you will break your site).
                    MARK.B | vBULLETIN SUPPORT

                    TalkNewsUK - My vBulletin 5.5.6 Demo
                    AdminAmmo - My Cloud Demo

                    Comment


                    • #11
                      I have done both, a couple times now. I had to do it again today, because cache is back up to over 600mb.

                      Comment


                      • #12
                        Also, in the above reply, it said I can drop search. I don't have just search, but is searchlog the same thing? I noticed that's over 100mb as well.

                        Comment


                        • #13
                          I moved attachments to the file system, and that has cleared up some of the size issue, but I am still experiencing the cache size issues where I have to truncate it every day or 2 to get rid of hundreds of mb's of data.

                          Comment

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