Announcement

Collapse
No announcement yet.

SQL Database too large

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

  • wickedfate
    replied
    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.

    Leave a comment:


  • wickedfate
    replied
    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.

    Leave a comment:


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

    Leave a comment:


  • Mark.B
    replied
    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).

    Leave a comment:


  • wickedfate
    replied
    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?

    Leave a comment:


  • Mark.B
    replied
    You have to create your own folder, and recursively chmod it to 777.
    https://www.vbulletin.com/docs/html/...rage_db_to_fs1

    Leave a comment:


  • wickedfate
    replied
    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.

    Leave a comment:


  • Wayne Luke
    replied
    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.

    Leave a comment:


  • Mark.B
    replied
    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).

    Leave a comment:


  • jdj
    replied
    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?

    Leave a comment:


  • jdj
    replied
    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.

    Leave a comment:


  • Mark.B
    replied
    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.

    Leave a comment:


  • wickedfate
    started a topic SQL Database too large

    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?

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