Decrease database size

[bryanharley]

I got the following message from my web host. I need to decrease the size of my IPB database. Any suggestions for how I can get the total size to under 3GB?

 

Quote

The following databases are currently exceeding their disk allocation:

DB: nightly_ipb, Size = 7.52 GB
Table: ibf_core_search_index , Size: 2.5721 GB
Table: ibf_forums_posts , Size: 1.8121 GB
Table: ibf_core_message_posts , Size: 0.0651 GB
Table: ibf_core_output_cache , Size: 0.0591 GB
Table: ibf_core_reputation_index , Size: 0.0495 GB

 

[Jim M]

Best suggestion would be to upgrade your hosting to a plan or provider that does not limit your database size or is more inline with your growth. (Have you considered our cloud?)

The short story is that you’re growing (congrats!) and the tables listed hold core content of your community.  So the only way to get in line with your hosting would be hurting your community by sacrificing content. Once you start doing this, it’s a slippery slope, you’ll have to keep doing it to stay in line with the hosting. If you have the ability to upgrade, it’s the best idea to prevent this from happening and continue growing your community. Best of luck!

[bryanharley]

I'm not interested in changing my hosting plan or provider. 

If my largest tables are the search index at 2.5GB and posts at 1.8GB, then why is the overall database size 7.5GB?  Does that add up? 

Could I disable search and then delete the search index table?  

[Randy Calvert]

You’ll have to ask your host that question in terms of size. They’re the ones saying it’s that size without providing the supporting data. 

While you could disable search, I would not manually touch the database. It could break other things such as your ability to do upgrades etc.  (For example the upgrader expects certain tables to exist and could throw errors if it’s not in a state it expects.)

At BEST I might suggest simply emptying the table if it does not automatically happen.  But I would highly recommend you have a full backup available in case of unintended consequences.

If you manually change the database, it could leave you in a spot where IPS is unable to support you leaving you to needing to restore a backup from prior to when you manually made the change. 

[Jim M]

7 hours ago, bryanharley said:

If my largest tables are the search index at 2.5GB and posts at 1.8GB, then why is the overall database size 7.5GB?  Does that add up? 

If you have questions about your database backup overall size, you will need to ask your hosting provider all table sizes. What you have mentioned here is just a very small portion of the number tables in our software. Keep in mind that some table sizes will vary, like the core_log and cache tables, as data is automatically added/removed based on what is going on with your community.

 

[stu_m]

One thing I noticed about IPS is that the database is huge! 

I converted from phpBB and the database size there was just under 1gb but once I had converted the database was over twice the size 

 

[Randy Calvert]

16 minutes ago, stu_m said:

One thing I noticed about IPS is that the database is huge! 

I converted from phpBB and the database size there was just under 1gb but once I had converted the database was over twice the size 

 

There are a lot more capabilities in IPB than phpBB as well. That requires more space. 🙂

Also just so we’re very clear… a few gigabytes of space in reality is TINY.  Heck… free email account providers have mailbox sizes that are more than 10 times the size of the limit provided by their host. 🙂

[stu_m]

29 minutes ago, Randy Calvert said:

49 minutes ago, stu_m said:

 

There are a lot more capabilities in IPB than phpBB as well. That requires more space. 🙂

Yes I get all that but the majority aren't filled with any data so are tiny 16/32 Kib so hardly almost triple the database size 😕

I'm not worried I have plenty of space on my VPS it just seems odd that the database size almost tripled 

[Charles]

If you look in an SQL management tool, most will allow you to see each table's size and learn what is taking up space. If the search index is large, you can change the setting to reduce the amount of data indexed.

[bryanharley]

Search index was taking up the most space.  I disabled search and that reduced the size of the table, but now a few days later, it's back up to 2GB again.  But search is still disabled.

Where are these settings to reduce the search index?  I can't seem to locate them. 

[Randy Calvert]

The search index naturally will rebuild itself.  I don't think you can just keep it permanently reduced like what you're trying to accomplish.  Yes...  you freed up some space short term, but it won't stay that way as it rebuilds itself.  

I see the following options that might work for you:

1) You can delete content...  it would most likely mean deleting lots of content to really make a difference.  

2) If you can create multiple databases, create a new database and use the Archive feature to move older posts there to free up space from the main database.  

3) You can also look at using Elasticsearch (if your host provides it or if you want to purchase the service from somewhere else).  That would offload search from your database thus freeing up space.  

Since you don't want to switch hosts and your host won't increase the limits...  I don't think you're going to have very many good alternatives.  

[bryanharley]

Thanks, I hadn't considered option 2.  Am I allowed to have two installations of IPB, there won't be a license issue?  One for an archive of old posts?  

Charles seemed to indicate there was a way to reduce the search index in settings. But I can't locate them. 

Edited October 26, 2022 by bryanharley

[Randy Calvert]

12 minutes ago, bryanharley said:

Thanks, I hadn't considered option 2.  Am I allowed to have two installations of IPB, there won't be a license issue?  One for an archive of old posts?  

Charles seemed to indicate there was a way to reduce the search index in settings. But I can't locate them. 

You don't need a second installation.

ACP > Community > Forums > Settings

Click on the Archiving tab.  

You'll want to choose "A remote database".  Provide the database info for the new database (even if it's on the same physical server, just use 'localhost' as the address).  

Remember...  it may take several days to migrate content as its done in the background a little at a time.  

[bryanharley]

Thanks, I just set that up.

Is there anyway to monitor the status of the archiving process? How do I know when it's done?  My main database will shrink and the remote database will grow? 

[Marc]

There is no way in which to monitor that. It simply happens over time in the background, and is intentionally something you 'set and ignore'. Indeed one would shrink and the other grow

[The Old Man]

Just my 2 cents... Archiving content is good in theory, until you find it can't be searched. You can deactivate the archiving and go back but its best avoided I think if you value visitors being able to search older/legacy content; old but still valid pinned topics for example. If this caveat has been addressed in recent years, I apologise!

[Randy Calvert]

3 minutes ago, The Old Man said:

Just my 2 cents... Archiving content is good in theory, until you find it can't be searched. You can deactivate the archiving and go back but its best avoided I think if you value visitors being able to search older/legacy content; old but still valid pinned topics for example. If this caveat has been addressed in recent years, I apologise!

Totally agree.  But the OP was looking to disable search entirely to reduce the size of the database.  Using the archive feature would allow at least SOME content would be searchable.  It would also mean the OP does not have to delete content to get under his 3GB limit.  🙂 

[The Old Man]

Meh! The OP didn't mention disabling search entirely! Why would a community owner do that? It would be like buying a car without an engine or wheels.

[Randy Calvert]

2 minutes ago, The Old Man said:

Meh! The OP didn't mention disabling search entirely! Why would a community owner do that? It would be like buying a car without an engine or wheels.

My goal is to just not make it as painful as needing to cut off an entire arm when you could just go for a finger or two.  

(Yes....  I'm in Halloween mode this week.  haha)

[Gary]

There may be certain limitations that the original poster is experiencing with the current hosting provider. We can make assumptions, but the request was to try and decrease the database size without having to change the existing plan or hosting provider and this seems to be the best solution unless anyone else can chime in on their experiences.

[bryanharley]

On 10/26/2022 at 2:08 AM, Marc Stridgen said:

There is no way in which to monitor that. It simply happens over time in the background, and is intentionally something you 'set and ignore'. Indeed one would shrink and the other grow

I set it up on Tuesday, it's been two days and only 25 posts have been archived. I'm archiving 30,000 total.  Is there anyway to speed up this process? 

Edited October 28, 2022 by bryanharley

[Marc]

Are you running the tasks via cron? If not, please set them up to run via cron as that will speed the process

 

[bryanharley]

On 10/23/2022 at 12:21 PM, Charles said:

If you look in an SQL management tool, most will allow you to see each table's size and learn what is taking up space. If the search index is large, you can change the setting to reduce the amount of data indexed.

@Charles, what setting do I change to reduce the amount of data indexed? 

 

 

Edited October 30, 2022 by bryanharley

[Randy Calvert]

The setting to do that is what was I noted. Archiving posts don’t show up in search results, thus would not be indexed. 

If you look in the original screenshot that I posted, it specifically notes that archived posts do not show up in search results. 🙂

There is not another setting to just lower indexed data. 

Personally I would look at finding an Elasticsearch service and have search offloaded entirely. But that’s just me. 
 

[The Old Man]

Hi @bryanharley

I think what Charles was referring to was the Time Cutoff setting in the Search section of AdminCP.

What you could do is set it for a period like 365 days, then when you go the Dashboard home you will see it rebuilding.

 

Once it has finished, you could look at the size of your search index table and compare it to what it was taking up before when unlimited. Then perhaps increase it by another year or 6 months, depending upon your community needs, your patience (!) and the the resulting table sizes. Gauging the increase in multiplies won't be 100% accurate but should at least give you a guesstimate of the economies of scale. 

Obviously reducing the amount of searchable content is akin to archiving it in terms of the older data then being unsearchable, but just thinking a little out of the box and assuming we're talking publicly visible content it will still be searchable via Google. So your members, visitors and potential visitors won't lose out completely and you could add a Google Custom Search facility to your Community to supplement the built in internal search.