Jump to content

Decrease database size


Recommended Posts

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

 

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

  • 5 months later...

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. 

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

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.  

Link to comment
Share on other sites

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 by bryanharley
Link to comment
Share on other sites

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.  

1070163454_2022-08-2522_05_07.thumb.png.

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.  

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by bryanharley
Link to comment
Share on other sites

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 by bryanharley
Link to comment
Share on other sites

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. 

 

Link to comment
Share on other sites

Hi @bryanharley

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

Could contain: Menu, Text

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.

Could contain: Text, Menu

 

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.

Link to comment
Share on other sites

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...