Database size increase sudenly

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
ngaia
Registered User
Posts: 323
Joined: Sat Apr 23, 2016 4:28 am

Database size increase sudenly

Post by ngaia »

i dnt remember doing anything to the database other than search index, but i deleted it already, but phpbb_posts size doesnt change.

it was around 900mb now its around 1.4gb.

anyone can help me to reduce it, my host told me to recude it to 1gb
User avatar
warmweer
Jr. Extension Validator
Posts: 9222
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Bel ... gium
Contact:

Re: Database size increase sudenly

Post by warmweer »

ngaia wrote: Tue Nov 22, 2022 10:16 pm i dint remember doing anything to the database other than search index, but i deleted it already, but phpbb_posts size doesnt change.
Why would you expect the size of the posts_table being reduced?
ngaia wrote: Tue Nov 22, 2022 10:16 pmit was around 900mb now its around 1.4gb.

anyone can help me to reduce it, my host told me to recude it to 1gb
The search tables can easily take 1/3 of the database size, so reducing the search tables can help.
Also, there's a lot of writing/editing/deleting in the database: that can leave empty space in the database. Use the optimise option on tables to reduce wasted space. (you'll also notice that if you take a backup of a table and then restore that table, the database size will be reduced).


There's also an extension (by Dark1) which allows setting criteria to reduce the search index.
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.


Time flies like an arrow, but fruit flies like a banana.
ngaia
Registered User
Posts: 323
Joined: Sat Apr 23, 2016 4:28 am

Re: Database size increase sudenly

Post by ngaia »

Why would you expect the size of the posts_table being reduced?
So, search index doesnt effect phpbb_post?

My phpbb_posts alone is 1.3gb now, it was 788mb 3 days ago.
User avatar
janus_zonstraal
Registered User
Posts: 5862
Joined: Sat Aug 30, 2014 1:30 pm

Re: Database size increase sudenly

Post by janus_zonstraal »

So, search index doesnt effect phpbb_post?
No only the 3 search tables.

But if you are so close to 1GB it is better to search for a better host without a database limit.
Sorry! My English is bat ;) !!!
ngaia
Registered User
Posts: 323
Joined: Sat Apr 23, 2016 4:28 am

Re: Database size increase sudenly

Post by ngaia »

phpbb_posts here the one increase so much,
788mb.png
phpbb_post was 788 mb 2 days ago

1gb.png
now it's 1.3gb

When i do search index, it too long and i cancel it, 2 or 3 times, can that action cause increasing phpbbb_posts, if it's not what might cause this phpbb_post sudden increase. My host give me 48 hours, if not solve they will hold my account. Please help someone
User avatar
warmweer
Jr. Extension Validator
Posts: 9222
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Bel ... gium
Contact:

Re: Database size increase sudenly

Post by warmweer »

You changed collation of all the tables.
What was the need to do that?

Added: as janus mentioned, if 1GB is the limit your host enforces, even with changing back the collation you'll be flirting with the limits set by the host
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.


Time flies like an arrow, but fruit flies like a banana.
ngaia
Registered User
Posts: 323
Joined: Sat Apr 23, 2016 4:28 am

Re: Database size increase sudenly

Post by ngaia »

warmweer wrote: Wed Nov 23, 2022 9:52 am You changed collation of all the tables.
What was the need to do that?

Added: as janus mentioned, if 1GB is the limit your host enforces, even with changing back the collation you'll be flirting with the limits set by the host
i didnt change the collation, i think my new host did it, i moved to this host 2/3 days ago, when i asked them about this increasing database size, can this changing collation increase table size, other table seems the same size. i told me to talk to developer, that's why asked here...
Paul
Infrastructure Team Leader
Infrastructure Team Leader
Posts: 28020
Joined: Sat Dec 04, 2004 3:44 pm
Location: The netherlands.
Name: Paul Sohier
Contact:

Re: Database size increase sudenly

Post by Paul »

janus_zonstraal wrote: Wed Nov 23, 2022 9:16 am
So, search index doesnt effect phpbb_post?
No only the 3 search tables.

But if you are so close to 1GB it is better to search for a better host without a database limit.
that depends on what search index is used. If mysql fulltext is used, it won't affect the search tables but will affect the posts table.
User avatar
warmweer
Jr. Extension Validator
Posts: 9222
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Bel ... gium
Contact:

Re: Database size increase sudenly

Post by warmweer »

ngaia wrote: Wed Nov 23, 2022 3:13 pm i didnt change the collation, i think my new host did it, i moved to this host 2/3 days ago, when i asked them about this increasing database size, can this changing collation increase table size, other table seems the same size. i told me to talk to developer, that's why asked here...
So the host is the one who changed it: in that case the host should answer your question instead of sending you to "the developer".
Did the hosts do the transfer or did you create and populate the database on the new host.

Anyway: what you could do is a) put your board in read-only and b) take a database backup (in order to restore if something goes wrong) and then c) change collation of the tables (set them back to utf8_bin) then optimize the database.

Still you're going to stay close to the 1GB they use as threshold .
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.


Time flies like an arrow, but fruit flies like a banana.
ngaia
Registered User
Posts: 323
Joined: Sat Apr 23, 2016 4:28 am

Re: Database size increase sudenly

Post by ngaia »

Paul wrote: Wed Nov 23, 2022 3:26 pm
janus_zonstraal wrote: Wed Nov 23, 2022 9:16 am
No only the 3 search tables.

But if you are so close to 1GB it is better to search for a better host without a database limit.
that depends on what search index is used. If mysql fulltext is used, it won't affect the search tables but will affect the posts table.
yes, i used mysql fulltext, but i already delete it, phpbb_posts table size doesnt changed.
32ygrejr5tjk.png
Search index are empty, but post table is still 1.3gb
User avatar
warmweer
Jr. Extension Validator
Posts: 9222
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Bel ... gium
Contact:

Re: Database size increase sudenly

Post by warmweer »

The search index consist of 3 tables in the database and has no influence on the size of any other tables.
The size of the posts_table depends on the posts stored and the overhead. Overhead can be removed by optimising the table, but that's just temporary: you'll always have some overhead. In this case the posts_table overhead is (currently) negligeable.

In fact if you use native fulltext search that's probably going to be an extra 300 MB or more.
So you need to find out what your host's limits are (and perhaps change hosts as it seems you're nearing the limit for the hosting package you have).
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.


Time flies like an arrow, but fruit flies like a banana.
Post Reply

Return to “[3.2.x] Support Forum”