SQL Database too large

[wickedfate]

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?

[Mark.B]

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.

[jdj]

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.

[jdj]

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?

[Mark.B]

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).

[Wayne Luke]

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.

[wickedfate]

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.

[Mark.B]

You have to create your own folder, and recursively chmod it to 777.

https://www.vbulletin.com/docs/html/main/attachment_storage_db_to_fs1

[wickedfate]

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?

[Mark.B]

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).

[wickedfate]

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

[wickedfate]

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.

[wickedfate]

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.