• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

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

dihuta

Formerly Dinh Thanh
#1
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.
 

Slavik

XenForo moderator
Staff member
#4
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.
 

Adam Howard

Well-known member
#11
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).
 

CyclingTribe

Well-known member
#12
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
 

Marcus

Well-known member
#14
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;
 

Gladius

Well-known member
#15
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: