Database Size weirdness and listed as "Not available"

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
bort70
Registered User
Posts: 35
Joined: Sun Apr 10, 2005 9:09 pm

Database Size weirdness and listed as "Not available"

Post by bort70 »

Hi -- I'm transferring to a different host (I'm using phpmyadmin and bigdump to move my DB) but have a few weird results. The total size gains about 25mb according to phpmyadmin (from 125 to 150mb). And when I log-in into the admin panel, the database size is listed as "Not available"... though the forum seems to work fine.

I think the encoding might be the root of it... the DB is in latin1_swedish, phpmyadmin dump saves it as UTF8, bigdump is set to UTF8 and the new DB is set to latin1_swedish. Is that what's happening, is it something to worry about? Can it be corrected? How can I get the database size to show up again?

Thanks

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: Database Size weirdness and listed as "Not available"

Post by espicom »

Trust phpmyadmin's size estimates vs. phpBB's. The database modules in phpBB aren't specific enough to keep track of how MySQL information requests have changed over time, so newer versions of MySQL tend to confuse it. But phpmyadmin bases things on the total file sizes (data plus index), and only the data gets transfered to the new site.

Hopefully, you've read the "moving hosts" knowledge base article, so you know what tables you do NOT have to move. The search tables alone are usually half the database!
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

bort70
Registered User
Posts: 35
Joined: Sun Apr 10, 2005 9:09 pm

Re: Database Size weirdness and listed as "Not available"

Post by bort70 »

Hi Espicom. I did read that article (several times) and am aware of the rebuild search script. So far, the file transfer has been OK (after much earlier confusion). I'll consider skipping those tables. Even if bigdump is able to process the entire SQL dump file, do you think the search tables should be skipped and rebuilt?

The discrepancy in file sizes are shown in phpmyadmin (the total sizes of 125mb vs. 150mb) also show that large tables are also a bit inflated (such as the posts table). That is why I think the characters are encoded differently, thus the greater size. The records are about the same.

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: Database Size weirdness and listed as "Not available"

Post by espicom »

Sizes are highly variable, because as posts are added and deleted, the space is not freed up until you do a repair or optimize. So I wouldn't be concerned there. In a dump-to-SQL-statements backup, the empty space does not come with, in any case.

As for not saving the search tables, that is mainly to prevent problems with import - if you are using languages with special characters, they can cause "duplicate entry" failures on import. So, why bother?
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

bort70
Registered User
Posts: 35
Joined: Sun Apr 10, 2005 9:09 pm

Re: Database Size weirdness and listed as "Not available"

Post by bort70 »

Thanks. I do run a repair then optimize prior to backup. And the new DB is the one that is the greater size. Does that make sense?

I'll skip the search tables, they definitely slow things down. Thanks again!

edit: This is a screen snap of the options I picked (I think the one in the article is a bit dated) --
Image

espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: Database Size weirdness and listed as "Not available"

Post by espicom »

Unless database problems are reported, running an optimize OR repair just before doing a backup of the type you're doing for the transfer is a non-issue. It only minutely affects the speed of transferring the data to the web server, which then sends it to you via a much slower connection, so ... why bother?
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer

bort70
Registered User
Posts: 35
Joined: Sun Apr 10, 2005 9:09 pm

Re: Database Size weirdness and listed as "Not available"

Post by bort70 »

I've done it as a precaution in preparation for transferring to a new host -- just to make sure the DB is in good shape before I make a backup. I'm testing reimporting a dump file with the previous host to see if it will inflate too. If it does, then I would at least know it's occurring during backup/transfer and is not an issue with the new host...

User avatar
ric323
Former Team Member
Posts: 22910
Joined: Tue Feb 06, 2007 12:33 am
Location: Melbourne, Australia
Name: Ric
Contact:

Re: Database Size weirdness and listed as "Not available"

Post by ric323 »

Jeff's point is, when you do a backup, empty records don't appear in the backup anyway, and the database index files aren't in the backup either, they are recreated when you do the restore, so it's a waste of time doing those two operations before a backup.
The Knowledge Base contains solutions to many common problems!
How to fix "Doesn't have a default value" and "Incorrect string value: xxx for column 'post_text' " errors.
How to do a clean re-install of the latest phpBB3 version.
Problems with permissions? Read phpBB3 Permissions

bort70
Registered User
Posts: 35
Joined: Sun Apr 10, 2005 9:09 pm

Re: Database Size weirdness and listed as "Not available"

Post by bort70 »

Gotcha.

I just finished reimporting the DB to my previous host. I'm not sure if this makes sense...

For the same amount of records (144,314) from the same dump file, the total size of an example table is:
Original DB (Host 1) = 47.1 MiB
Import DB (Host 1) = 47.5 MiB
Import DB (Host 2) = 47.7 MiB

For the larger tables, especially the search tables, the differences are more dramatic.

What's also interesting... in the admin panel, phpbb will display the Database size with Host 1 (including the test import) whereas it can't with Host 2.

Locked

Return to “2.0.x Support Forum”