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

Discussion in 'General XenForo Discussion and Feedback' started by Dinh Thanh, Mar 6, 2013.

  1. Dinh Thanh

    Dinh Thanh 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.
    erich37 and luutruong like this.
  2. Digital Doctor

    Digital Doctor Well-Known Member

    wow !
    must be from bots ?
  3. Mark3121

    Mark3121 Well-Known Member

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

    Slavik XenForo Moderator Staff Member

    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.
  5. HWS

    HWS Well-Known Member

    You should be able to truncate it. We do it regularly without any issues.
    luutruong likes this.
  6. duderuud

    duderuud Active Member

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

    Gladius Well-Known Member

    Doesn't this ever get emptied by XF?
  8. AndyB

    AndyB Well-Known Member

    Apparently not.

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

    Slavik XenForo Moderator Staff Member

    Was just about to make a suggestion of something similar.
    soloarquitectura and Gladius like this.
  10. Gladius

    Gladius Well-Known Member

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

    Adam Howard Well-Known Member

    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 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).
    RoldanLT and Dinh Thanh like this.
  12. CyclingTribe

    CyclingTribe Well-Known Member

    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.

    Shaun :D
  13. MattW

    MattW Well-Known Member

    Breixo and Andy.N like this.
  14. Marcus

    Marcus Well-Known Member

    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;
    maszd likes this.
  15. Gladius

    Gladius Well-Known Member

    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: Aug 20, 2014
  16. BassMan

    BassMan Well-Known Member

    I belive this is it:

    Last edited: Aug 21, 2014
    Breixo, Neal, Marcus and 3 others like this.
  17. Gladius

    Gladius Well-Known Member

    Aah, thanks!
  18. dethfire

    dethfire Well-Known Member

    wow that is freakin crazy! :D

Share This Page