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

[agison]

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.

 

[Digital Doctor]

wow !
must be from bots ?

 

[Mark3121]

I cleared mine out as it was huge, everything was fine after. Do a backup though

 

[Slavik]

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.

 

[HWS]

You should be able to truncate it. We do it regularly without any issues.

 

[duderuud]

How is it possible to generate almost 15GB with ip's alone? That most be tens of millions of ip's??

 

[Gladius]

Doesn't this ever get emptied by XF?

 

[AndyB]

Doesn't this ever get emptied by XF?

Apparently not.

Might be a good idea to write a script which runs on a cron to truncate this table once a month.

 

[Slavik]

Apparently not.

Might be a good idea to write a script which runs on a cron to truncate this table once a month.

Was just about to make a suggestion of something similar.

 

[Gladius]

Please do that, this should be included in the regular cron cleanup.

 

[Adam Howard]

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).

 

[CTXMedia]

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.

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

 

[MattW]

Apparently not.

Might be a good idea to write a script which runs on a cron to truncate this table once a month.

http://xenforo.com/community/resources/truncate-ip-log-cron.1633/

 

[Marcus]

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.

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]

This still isn't part of stock XF?

Edit: guess not, 460MB here...

Is there a suggestion for this I can vote on?

 

[BassMan]

This still isn't part of stock XF?

Edit: guess not, 460MB here...

I belive this is it:

 

[Gladius]

Aah, thanks!

 

[dethfire]

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.

wow that is freakin crazy!