After upgrade, my database its very big

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • gnrx
    Senior Member
    • Feb 2009
    • 241

    [Forum] After upgrade, my database its very big

    Hi.
    A few days ago, I upgrade my forum from vb 3.8.7 to 4.1.3

    With vb 3.8.7 the database size are about 110 MB.

    But now, with vb 4.1.3 the database size are 2442 MB !

    Only 6 days with vb 4.1.3.

    I review configuration of vbulletin, and I don't view any field that caused this.

    My attachs are in a folder (not in database).

    Thanks and regards!
  • Zachery
    Former vBulletin Support
    • Jul 2002
    • 59097

    #2
    There is no way your database would have grown by 2gb unless your users are that busy posting. Try a repair and optimize on your vbulletin tables.

    Comment

    • gnrx
      Senior Member
      • Feb 2009
      • 241

      #3
      Hello.
      Thanks for your reply.

      No, my users, in 6 days, not posted 2 gb

      I go to php my admin, and view the tables, this tables, are very big:

      phrase - 6.1 MB
      pmtext - 34.2 MB
      post - 153.7 MB
      postedithistory - 84.4 MB
      postparsed - 17.8
      template - 9.6 MB
      thread - 21.5 MB
      And this:
      searchcore - 71.6 MB
      searchcore_text - 219.2 MB

      The 2 last are very big size.

      I repair and optimize my vbulletin tables, but continue with the same size.

      And other times, when i go to optimize tables the table searchcore_text have a error and its fixed like table crash and I need Rebuild Search Index for fixed this table.Thanks and regards

      Comment

      • Trevor Hannant
        vBulletin Support
        • Aug 2002
        • 24325
        • 5.7.X

        #4
        searcore and searchcore_text are required tables as they contain all the information for the vBulletin search engine. There's nothing you can do with regards to these.
        Vote for:

        - Admin Settable Paid Subscription Reminder Timeframe (vB6)
        - Add Admin ability to auto-subscribe users to specific channel(s) (vB6)

        Comment

        • gnrx
          Senior Member
          • Feb 2009
          • 241

          #5
          Hello Trevor.

          Thanks for your reply

          I understand that in 4.1.3, this tables are new about a 3.8.7 and it is normal that my database has grown considerably in size (correct me if I'm wrong).

          In 3.8.7 the search I used not based in database, in 4.1.3 this is not possible?

          Thank you very much and best regards!

          Comment

          • Trevor Hannant
            vBulletin Support
            • Aug 2002
            • 24325
            • 5.7.X

            #6
            There is only one search option available at this time, you need to use this.
            Vote for:

            - Admin Settable Paid Subscription Reminder Timeframe (vB6)
            - Add Admin ability to auto-subscribe users to specific channel(s) (vB6)

            Comment

            • George L
              Former vBulletin Support
              • May 2000
              • 32996
              • 3.8.x

              #7
              Originally posted by gnrx
              Hi.
              A few days ago, I upgrade my forum from vb 3.8.7 to 4.1.3

              With vb 3.8.7 the database size are about 110 MB.

              But now, with vb 4.1.3 the database size are 2442 MB !

              Only 6 days with vb 4.1.3.

              I review configuration of vbulletin, and I don't view any field that caused this.

              My attachs are in a folder (not in database).

              Thanks and regards!
              If you want to get to the bottom of this and find out exactly where the increased size is read below

              Do you have SSH telnet access to your web server ?

              If you do, I assume you would of followed guidelines and made a mysql dump sql backup of your vB 3.x database BEFORE upgrading to vB 4.1.x ? If you can check and compare both the old vB 3.x database and vB 4.1.x database size using the following procedure.

              1. Create a new empty databasename i.e. emptydbname
              2. Import your vB 3.8.x mysql sql dump backup into emptydbname database

              !!! DO NOT USE same databasename as live forums !!!

              mysql -u mysqlusername -p emptydbname < /path/to/backup.sql

              3. Now for both emptydbname and vB 4.1.x databasenames in ssh telnet run these commands for both your vB 3.x. and vB 4.1.x databases in SSH telnet where databasename is the name of your database

              ls -lah /var/lib/mysql/databasename

              du -sh /var/lib/mysql/databasename


              4. Run these commands in SSH telnet via mysql -e just remember to change databasename to the name of your database and change mysqlusername to your mysql username. Copy and paste output here.

              Calculate Total Database Index Size

              Code:
              mysql -u mysqlusername -p  -e "SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size'
              FROM information_schema.TABLES WHERE table_schema LIKE 'databasename';"
              Calculate the Total Size of Table Data

              Code:
              mysql -u mysqlusername -p  -e "SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024), 2), ' MB') AS 'Total Data Size'
              FROM information_schema.TABLES WHERE table_schema LIKE 'databasename';"
              Per Table Sizes

              Code:
              mysql -u mysqlusername -p -e "SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows,2),' Rows') AS 'Number of Rows',ENGINE AS 'Storage Engine',CONCAT(ROUND(data_length/(1024*1024),2),'MB') AS 'Data Size',
              CONCAT(ROUND(index_length/(1024*1024),2),'MB') AS 'Index Size' ,CONCAT(ROUND((data_length+index_length)/(1024*1024),2),'MB') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'databasename';"
              Each command will prompt you for your mysqlusername's password to proceed.
              :: Always Back Up Forum Database + Attachments BEFORE upgrading !
              :: Nginx SPDY SSL - World Flags Demo [video results]
              :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

              Comment

              • gnrx
                Senior Member
                • Feb 2009
                • 241

                #8
                Originally posted by Trevor Hannant
                There is only one search option available at this time, you need to use this.
                Ok, thanks, was a question I had and was not sure, thanks!

                Originally posted by eva2000
                If you want to get to the bottom of this and find out exactly where the increased size is read below

                Do you have SSH telnet access to your web server ?

                If you do, I assume you would of followed guidelines and made a mysql dump sql backup of your vB 3.x database BEFORE upgrading to vB 4.1.x ? If you can check and compare both the old vB 3.x database and vB 4.1.x database size using the following procedure.

                1. Create a new empty databasename i.e. emptydbname
                2. Import your vB 3.8.x mysql sql dump backup into emptydbname database

                !!! DO NOT USE same databasename as live forums !!!

                mysql -u mysqlusername -p emptydbname < /path/to/backup.sql

                3. Now for both emptydbname and vB 4.1.x databasenames in ssh telnet run these commands for both your vB 3.x. and vB 4.1.x databases in SSH telnet where databasename is the name of your database

                ls -lah /var/lib/mysql/databasename

                du -sh /var/lib/mysql/databasename


                4. Run these commands in SSH telnet via mysql -e just remember to change databasename to the name of your database and change mysqlusername to your mysql username. Copy and paste output here.

                Calculate Total Database Index Size

                Code:
                mysql -u mysqlusername -p  -e "SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size'
                FROM information_schema.TABLES WHERE table_schema LIKE 'databasename';"
                Calculate the Total Size of Table Data

                Code:
                mysql -u mysqlusername -p  -e "SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024), 2), ' MB') AS 'Total Data Size'
                FROM information_schema.TABLES WHERE table_schema LIKE 'databasename';"
                Per Table Sizes

                Code:
                mysql -u mysqlusername -p -e "SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows,2),' Rows') AS 'Number of Rows',ENGINE AS 'Storage Engine',CONCAT(ROUND(data_length/(1024*1024),2),'MB') AS 'Data Size',
                CONCAT(ROUND(index_length/(1024*1024),2),'MB') AS 'Index Size' ,CONCAT(ROUND((data_length+index_length)/(1024*1024),2),'MB') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'databasename';"
                Each command will prompt you for your mysqlusername's password to proceed.
                Thank you for your indications.

                Yes, now I have a backup of database 3.8.7 (well, the 5 days before of my upgrade, for error, I deleted the last backup before upgrade...) but I made tests with this database and appears the same results, my size database of backup of 3.8.7 have 100 mb, and when upgrade, have 2.000 Mb.

                I don't have access SSH telnet access, but I ask the hosting for have access, when I have access, I made your indications and I post results here.

                Again, thank you very much for your help and attention!

                Best regards!

                Comment

                Related Topics

                Collapse

                Working...