Data base has grown by more than 1GB per year

[SJ77]

In 2017 my database was 1GB 

now it’s 6GB

 Normal? any thoughts? 
How can I safely reduce?

[Daniel F]

Which tables are that big? Are you truncating the logs?

[bfarber]

Yeah there's no generic answer for this. One common cause is not truncating log tables, but really you'd have to look into which tables are the largest to determine what's taking up so much space.

[SJ77]

Largest tables here

[Lucas James]

You may consider to truncate following tables (if desired) to reduce the overall DB size:

• core_members_known_ip_addresses
• core_members_known_devices

[Yamamura]

1 hour ago, SJ77 said:

Largest tables here

1. ACP → Posting → Remove IP addresses after
2. ACP → Profiles → Prune Member History
3. ACP → Notifications → Notification Settings → Remove stored notifications

[bfarber]

Actually #1 won't accomplish what you are suggesting - that option wipes out IP addresses within content but does not touch the table in question here.

We have some additional pruning options coming with 4.5 that will help clean up other tables, including the biggest ones in the screenshot.

[SJ77]

On 3/3/2020 at 8:54 AM, Takohashi said:

1.  
2. ACP → Profiles → Prune Member History

#2. Enabling this setting will prune member history entries such as display name history, commerce customer history and other items

YIKES!!... I don't want to remove commerce history. I wish it would let me git rid of  the other stuff without getting rid of commerce

[TDBF]

On 3/3/2020 at 5:53 PM, bfarber said:

Actually #1 won't accomplish what you are suggesting - that option wipes out IP addresses within content but does not touch the table in question here.

We have some additional pruning options coming with 4.5 that will help clean up other tables, including the biggest ones in the screenshot.

Do you mean like a Delete All button on most of the log pages? 👍

[bfarber]

No, we have additional task-based pruning available in 4.5 for areas such as used IP addresses and device history, among others. There is already pruning available for system logs.

[SJ77]

2 hours ago, bfarber said:

No, we have additional task-based pruning available in 4.5 for areas such as used IP addresses and device history, among others. There is already pruning available for system logs.

Are there things I can do right now to safely reduce the size of the database?

I see some suggestions but I'm not sure I understand the pros' vs con's .  For example if I had deleted member history I would have lost important purchase history.

What are the drawbacks of truncating the IP table right now? 

Maybe it's better to truncate only where the timestamp is greater than 1 year ago?

[David..]

5 minutes ago, SJ77 said:

Are there things I can do right now to safely reduce the size of the database?

I see some suggestions but I'm not sure I understand the pros' vs con's .  For example if I had deleted member history I would have lost important purchase history.

What are the drawbacks of truncating the IP table right now? 

Maybe it's better to truncate only where the timestamp is greater than 1 year ago?

I need this too! My database has grown too much in size and I don't know what to do.

[SJ77]

3 minutes ago, David.. said:

I need this too! My database has grown too much in size and I don't know what to do.

You can truncate any table you like using SQL. (back up DB first please)
The question is should you?

I am looking for answers to that question also.

[David..]

3 minutes ago, SJ77 said:

The question is should you?

I mean, it's IP addresses so I don't see why not. And apparently there is no way(?) to clear them our via ACP as bfarber said above.

[SJ77]

43 minutes ago, David.. said:

I mean, it's IP addresses so I don't see why not. And apparently there is no way(?) to clear them our via ACP as bfarber said above.

I am thinking about just running a query that truncates the table where unixtimestamp col is < 1551744000

Which is basically everything older than a year is cleaned out.

The problem is that I don't know what the trade offs are. Do I lose some of my reporting history in the ACP?

 

[Square Wheels]

Interesting topic.  My site is a little smaller than yours.  I have ~964,014 in the posts table for about 870.9 MiB.  My IP table is only 15.5 MiB but my search table is 1.1 GiB.

Similar sized sites, dramatically different table sizes.

I also have a small number of users, so maybe that's why the IP table is small?  I have another site with about 11k users, that IP table is only 1.8 MiB.

[supernal]

At some point MySQL switched the default storage engine from MyISAM to InnoDB and/or you may have done this switch yourself like I did after reading up on the differences: https://stackoverflow.com/questions/20148/myisam-versus-innodb

It has several benefits for most people in most cases but its tables are less compact. When we did the switch the DB grew considerably

Could that be what's going on here?

[SJ77]

I do use innoDB type tables but we switched to that years ago. I have just seen consistent growth in these data tables that record a lot of records.

[TDBF]

1 hour ago, SJ77 said:

I do use innoDB type tables but we switched to that years ago. I have just seen consistent growth in these data tables that record a lot of records.

This might help to explain the reason why InnoDB does this compared to MyISAM:

https://stackoverflow.com/questions/48865810/deleting-rows-causes-database-storage-to-grow-in-mysql

InnoDB is a much better storage engine than MyISAM but it has its draw backs (This issue being one of them).

You have a few options:

Optimising your tables: See here on how to do this, but that would take a good while and might not claw back as much data.

The other method, which I prefer is making a copy of your database tables, then replacing the original tables with the copied versions.

I did a little example to show size after you make a copy of the table, and highlighted the table size difference sizes after a copy. And, yes this is correct.

Basically, just create a mirror of your database and point to the mirror.

I would also suggest that you stop using phpmysqladmin for anything database related, as it is slow and awful.

As I prefer a GUI mysel, I use HeidiSQL as it allows me to do multiple databases remotely. You can import and export databases a 100 times faster (Without timing out on large sql files), copy to other databases (even on different servers), search/replace and a whole load of other tools as well.

[bfarber]

The only drawback is that you lose a little history of which IP addresses a member has visited with beyond whatever cutoff you use. There will be no other impact to how the software works.

With the device history table, you would lose the history of which devices a member has visited with beyond your cutoff. This has the potential to cause someone to be logged out if they haven't visited in a while, but I wouldn't consider that a big deal. If your last visit was a year ago, you should probably login again fresh anyways (and I think the "remember me" cookies are only good for 90 days regardless, but don't quote me on that - I don't have the file open to verify).

[SJ77]

I discovered the following

TRUNCATE is all or nothing. You can do a DELETE FROM <table> WHERE <conditions> but this loses the speed advantages of TRUNCATE

[bfarber]

Yes, TRUNCATE erases the table contents.