Database size expansion 3.0.12 to 3.1.0

Get help with installation and running phpBB 3.1.x here. Please do not post bug reports, feature requests, or extension related questions here.
Anti-Spam Guide
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: phpBB 3.1.x is at its End of Life stage and support will NOT be provided after July 1st, 2018.
Locked
User avatar
P_I
Registered User
Posts: 1228
Joined: Tue Mar 01, 2011 8:35 pm
Location: Staying home - Calgary
Contact:

Database size expansion 3.0.12 to 3.1.0

Post by P_I »

From my testing it appears that upgrading a site running phpBB 3.0.12, using MySQL for the db engine, to 3.1.0 results in about a 20-25% size increase in the database. Almost all the increase seems to be in the search_wordmatch table.

Is this to be expected? What new features in 3.1 impact the search_wordmatch table and is there ACP setting that will allow the table size to reduce back towards 3.0.12 size?

This might be troublesome for sites running on shared hosting services where there are limits on database sizes.
Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
User avatar
A_Jelly_Doughnut
Former Team Member
Posts: 34457
Joined: Sat Jan 18, 2003 1:26 am
Location: Where the Rivers Run
Contact:

Re: Database size expansion 3.0.12 to 3.1.0

Post by A_Jelly_Doughnut »

The search wordmatch index is rebuilt during the upgrade, as a fix to this bug:
https://tracker.phpbb.com/browse/PHPBB3-12873

I'm not sure why that would increase the size that precipitously, though.
A Donut's Blog
"Bach's Prelude (Cello Suite No. 1) is driving Indiana country roads in Autumn" - Ann Kish
juliend
Registered User
Posts: 11
Joined: Sat Mar 19, 2011 11:23 am

Re: Database size expansion 3.0.12 to 3.1.0

Post by juliend »

I did not experience this. My 3.0.12 db size is 504 MB. After converting it to 3.1 size is 458 MB. The main difference appears to be in table phpbb_search_wordmatch which is considerably smaller in the 3.1 version of the site, for some reason....
User avatar
P_I
Registered User
Posts: 1228
Joined: Tue Mar 01, 2011 8:35 pm
Location: Staying home - Calgary
Contact:

Re: Database size expansion 3.0.12 to 3.1.0

Post by P_I »

Thanks for the data point. I've got data points from two different boards, both use MySQL and phpBB Native Fulltext.

When I updated the first one I noticed the db size increase but didn't think much of it. Only I after I updated our main forum, which has a much larger db, and saw the same size expansion did I investigate and find which table accounted for almost all the size difference.

I'm testing the update procedure in a testbed, using a mysqldump of the live databases and then loading it back into a new MySQL database for testing. As a sanity check, I've compared the live database size with the testbed database size before performing the update, just in case something strange was happening when I built the new database for testing.

I guess another testcase would be rebuilding the search index using phpBB 3.1 and see if the resultant table size changes.
Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
User avatar
P_I
Registered User
Posts: 1228
Joined: Tue Mar 01, 2011 8:35 pm
Location: Staying home - Calgary
Contact:

Re: Database size expansion 3.0.12 to 3.1.0

Post by P_I »

P_I wrote:I guess another testcase would be rebuilding the search index using phpBB 3.1 and see if the resultant table size changes.
I've now completed this testcase. I compared ACP->Maintenance->Search index before and after, the Total number of indexed words dropped by about 2%, but the Total number of word to post relations indexed increased by about 10%.

When I checked the table size in MySQL, the search_wordmatch table has grown even bigger, by another ~20%. :roll: Subsequently I had MySQL OPTIMIZE the table and it reduced it, but it still is still > 25% larger that the table on our live 3.0.12 forum.
Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
User avatar
bantu
3.0 Release Manager
3.0 Release Manager
Posts: 2523
Joined: Mon Jul 10, 2006 9:58 pm
Name: Andreas Fischer

Re: Database size expansion 3.0.12 to 3.1.0

Post by bantu »

You may have hit a bug related to https://tracker.phpbb.com/browse/PHPBB3-12873 there. On my local development board I see that on the phpbb_search_wordmatch table the index un_mtch was created, but unq_mtch was not dropped. Maybe you can confirm this, e.g. using phpMyAdmin > phpbb_search_wordmatch > Structure > Indexes. This would explain the space increase.

Thanks.

Edit: Reported this as a bug here, so we do not forget: https://tracker.phpbb.com/browse/PHPBB3-13257 Please confirm anyway.
Powered by Coffee
User avatar
P_I
Registered User
Posts: 1228
Joined: Tue Mar 01, 2011 8:35 pm
Location: Staying home - Calgary
Contact:

Re: Database size expansion 3.0.12 to 3.1.0

Post by P_I »

bantu wrote:phpMyAdmin > phpbb_search_wordmatch > Structure > Indexes.
It says "The indexes unq_mtch and un_mtch seem to be equal and one of them could possibly be removed." which confirms. Thanks.

Workaround is to manually drop unq_mtch?
Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
User avatar
bantu
3.0 Release Manager
3.0 Release Manager
Posts: 2523
Joined: Mon Jul 10, 2006 9:58 pm
Name: Andreas Fischer

Re: Database size expansion 3.0.12 to 3.1.0

Post by bantu »

P_I wrote:Workaround is to manually drop unq_mtch?
Yes, I would says so.

Thanks for the confirmation.
Powered by Coffee
User avatar
P_I
Registered User
Posts: 1228
Joined: Tue Mar 01, 2011 8:35 pm
Location: Staying home - Calgary
Contact:

Re: Database size expansion 3.0.12 to 3.1.0

Post by P_I »

Dropped the unq_mtch index and that database returned back to approximately 3.0.12 size. Time for a Image
Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
Lady_G
Registered User
Posts: 255
Joined: Fri Jun 08, 2012 12:38 pm
Location: US

Re: Database size expansion 3.0.12 to 3.1.0

Post by Lady_G »

I'd like to offer another data point, as I'm using postgreSQL and am testing on localhost.

I experienced no problems in the conversion, but did notice a significant increase in database size.

Version 3.0.12: 10062 MB
Version 3.1.0: 12 GB

I have pgAdmin, which is the postgreSQL equivalent to phpMyAdmin. I confirm the index is not dropped during the upgrade process.

In version 3.1, Tables --> Indexes --> Statistics shows:

search_wordmatch_un_mtch 1347 MB
search_wordmatch_unq_mtch 1347 MB

After manually dropping the table, my database has reduced to 11 GB.
Lady_G
Registered User
Posts: 255
Joined: Fri Jun 08, 2012 12:38 pm
Location: US

Re: Database size expansion 3.0.12 to 3.1.0

Post by Lady_G »

I repeated the update from 3.0.12 to 3.1.1, there is no change. I manually dropped the index.
User avatar
Volksdevil
Registered User
Posts: 2414
Joined: Sun Oct 03, 2010 2:03 pm
Location: Lancashire, UK
Name: Neil
Contact:

Re: Database size expansion 3.0.12 to 3.1.0

Post by Volksdevil »

P_I wrote:"The indexes unq_mtch and un_mtch seem to be equal and one of them could possibly be removed." which confirms. Thanks.

Workaround is to manually drop unq_mtch?
Guys, while doing some digging, I've come across the same message in phpmyadmin. Am I still OK to delete/drop that table?

Some of the numbers between unq_mtch and un_mtch differ as you can see.
asas.PNG
asas.PNG (54.59 KiB) Viewed 2858 times
My phpBB Extensions
Finally found great Website Hosting from Image KUALO!
Do NOT use 123-reg.co.uk - Incapable of running phpBB!
:ugeek: TekNeil - Streamer on Mixer | My Volkswagen Corrado G60
Lady_G
Registered User
Posts: 255
Joined: Fri Jun 08, 2012 12:38 pm
Location: US

Re: Database size expansion 3.0.12 to 3.1.0

Post by Lady_G »

I would think yes, but I'm using postgreSQL and see the index as search_wordmatch_unq_mtch.

The bug is still open: unq_mtch index on wordmatch table not dropped as expected (linked to another bug, has some activity)
User avatar
P_I
Registered User
Posts: 1228
Joined: Tue Mar 01, 2011 8:35 pm
Location: Staying home - Calgary
Contact:

Re: Database size expansion 3.0.12 to 3.1.2

Post by P_I »

Just tested upgrading from 3.0.12 to 3.1.2 and found PHPBB3-13257 is not fixed in 3.1.2. It is now showing to be fixed for 3.1.3-RC1.
Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
Blacktiger63
Registered User
Posts: 55
Joined: Thu Oct 17, 2013 7:43 pm

Re: Database size expansion 3.0.12 to 3.1.0

Post by Blacktiger63 »

Just a question I'm wondering about.

I have upgraded from 3.0.11 to 3.1.2 and my database size grow from 465 MiB to 520 MiB. This was due to this known bug with unq_mtch.

Now we upgraded from 3.1.2 to 3.1.3 and now we got some strange issue.
We can't find any table called unq_mtch so probably the upgrade to 3.1.3 removed it. Correct?

However... when I do a mysqldump via SSH I now get a mysql backup from 320 Mib instead of 465 or 520. So we lost 200 MiB.
But when I look in the ACP, it still says:
Database size: 522.21 MiB Our physical backup via SSH on version 3.1.2 was also 520 MiB.

But how is it possible that now on 3.1.3. the ACP still says 522.21 MiB size and physically the backup is only 320 MiB in size?
Locked

Return to “[3.1.x] Support Forum”