oh oh, my xf_ip table is so big (14.2GB)

agison

Well-known member
Today I check my database and found that the second largest table on my database is xf_ip. It's 14.2GB, Crazy!

So I want to empty it but don't any issue will happen if I truncate this.
 
Just make sure to backup incase any mods you have rely on the IP data for any reason.

But otherwise you should be able to truncate the table without much issue.
 
How is it possible to generate almost 15GB with ip's alone? That most be tens of millions of ip's??
 
Today I check my database and found that the second largest table on my database is xf_ip. It's 14.2GB, Crazy!

So I want to empty it but don't any issue will happen if I truncate this.
We purge this table every so often at random timing (normally once or twice a year).

People don't always stick to the same IP address. Good IP's become bad and bad become good... (Another reason why we also purge the ban IP's as well)

So it just makes sense to speed up things and save space.... We empty it.

We DO have a few add-ons which do use IP's. You'll end up with 0.0.0.0 with everyone who does not re-log-in (no issue) and you may get 1 or 2 small errors that were only noticeable on the admin side of things (which basically told us that no IP could be found for that account).

So generally it was harmless, easy, and improved things (speed & size).
 
What about an ACP option to specify how many days the IP log should be kept? Then a daily cron to trim the table based on this setting. (y)

You could default it to 365 out-of-the-box - but at least this would give owners the opportunity to adjust the log/table size to suit their own specific environment/equipment.

Cheers,
Shaun :D
 
You can just remove the secondary index keys, they took 7% of my whole database size. The primary key does not take any innodb database space.
Code:
ALTER TABLE xf_ip DROP KEY user_id_log_date;
ALTER TABLE xf_ip DROP KEY ip_log_date;
ALTER TABLE xf_ip DROP KEY content_type_content_id;
ALTER TABLE xf_ip DROP KEY log_date;
 
This still isn't part of stock XF?

Edit: guess not, 460MB here...

Is there a suggestion for this I can vote on?
 
Last edited:
Top Bottom