Database capacity

[Riccardo]

Hello Everybody,

My forum has a very low activity in the last months/year but my database seems to grow to rapidly..

I mean, if I check it on Monday it is at 54% of his capacity, and after about 10 days it's 100% full! So I must "optimize" it and it return to 54/55%..

What's the problem?

[Marc]

If optimising it is returning to correct capacity, then it seems your database is not releasing space that isnt being used properly. I would first of all ask if you are using INNODB tables, as it sounds like you may not be, and we would advise on the use of INNODB

 

[Riccardo]

Hello Marc,

you are right! I tried to change manually the engine from MyIsam to InnoDB but for some tables I receive this error:

Errore

Query SQL:

ALTER TABLE `ibf_core_search_index`

ENGINE = INNODB;

Messaggio di MySQL:

#1709 - Index column size too large. The maximum column size is 767 bytes.

 

[Marc]

If you are having issues with converting tables, you would need to contact your hosting company on converting those. Just to note however, if you do delete an index, you can go to the support area of your admin CP which will detect its missing and give you the opportunity to readd it if needed. I would advise ensuring you take a full backup if you are looking to try that

[Riccardo]

Hello Marc,

I contacted my hosting company and I will be able to do the changes soon.

In the meanwhile I monitored  "ibf_core_output_cache" table which started from 71mb at 9:30 am to 125mb at 10:30 am!!

Is there a way to keep cache under control avoiding this "abnormal" use of DB?

Edited May 6, 2022 by Riccardo

[Marc]

That is actually normal use. The size of that table would be expected to be one of the largest, as it caches each page on your site. 

[Riccardo]

Thank you for the answer

What is the max size from cache table I must expect considering ibf_core_search_index is about 410mb,  ibf_forum_post is 380mb and ibf_core_message_post is 100mb?

What happen when the cache on DB will expire?

[Marc]

There is no way in which I would be able to give you a maximum size from just those stats, as it depends on many other factors. When the cache expires it would simply be recreated. 

[Riccardo]

This could be a problem..because I'm forced to two options:

1) expand my DB and it is a cost for me;

2) keep cache under control which means loosing funcionality of the forum...

 

Edited May 6, 2022 by Riccardo

[Marc]

Unfortunately I'm not sure what I can really suggest there. You need a database allowance that is large enough to accommodate the site you are running.

[Riccardo]

I agree with you, but now that I think about it I never had this problem before the last update, Am I wrong?

[Marc]

6 minutes ago, Riccardo said:

I agree with you, but now that I think about it I never had this problem before the last update, Am I wrong?

There has been no difference in the way we are caching since the last update, no

[Stuart Silvester]

The guest output cache will cache unique views, so if you're getting more guests looking at more unique pages, the amount cached will increase. This cache is periodically truncated by a task (use Cron to ensure it's cleaned up often)

You can disable output caching with a constant OUTPUT_CACHE_METHOD, set to 'None'. - https://invisioncommunity.com/4guides/advanced-options/configuration-options/using-constantsphp-r25/

You may see slower loading guest pages after changing this.

[Riccardo]

8 minutes ago, Stuart Silvester said:

This cache is periodically truncated by a task (use Cron to ensure it's cleaned up often)

Hello Stuart!

Thanks for the infos.

What I have to do in this case? What is "Cron"?

[Marc]

Cron is a task you set up on the server side of things. Please see the following guide on this. If you are unsure how to set up cron jobs, its something you would need to ask your hosting company to do on your behalf

 

[Riccardo]

Thanks

I see that "clearcache"  task is set every 10 minutes, but this not affect the dimension of the table on DB 🤨

[Marc]

It may not. It depends on what amount of cache is then rebuilt. Also, if that task is not running, it may not be clearing at all. While Its set to run every 10 minutes, it is currently running with traffic. So if you have not enough traffic it may not clear at all.

I do have to point out here though that the sizes you were mentioning are not large in any way. 

[Riccardo]

Thank you Marc,

I will keep u updated, in the meanwhile I will monitor the cache table on DB

Riccardo

[Riccardo]

 Hello Marc!

I checked this morning the table ibf_core_output_cache  and it's 693mb!!!

I think that the cache isn't truncated..the table seems to expand too much!!!

Never happened.

Riccardo

[Riccardo]

update:

the table is finally under INNODB engine, and it seems work correctly now.

The table increase then decrease its size regularly.

I will update u the next week during normal usage of the forum.

Riccardo

 

Edited May 7, 2022 by Riccardo

[Marc]

Glad to hear it 🙂