IPB 3.x speedup news

[Fast Lane!]

Recently I converted to IPB 3.0.5 from 2.3.6. Needless to say my server loads hit the roof and were unpredictably tough to manage. After tons of tweaking I found the single most important change that resulted in massive reductions of load spiking. See the below post for more details:

http://community.invisionpower.com/topic/308088-erratic-spikes-i-found-a-potential-fix/page__gopid__1938940#entry1938940

I have had feedback from IPB and several members indicating this helps a lot. Basically you want to make a cron job to execute the following commands against your database every 30 minutes or so (on an active board):

OPTIMIZE TABLE `ibf_core_item_markers`;
OPTIMIZE TABLE `ibf_core_item_markers_storage`;

If you do not, they become highly fragmented and despite their small size lead to huge table locks, slow queries and eventually major load spikes. Best wishes everyone :). I hope this helps others not go through the same thing!

[Invisiοnist]

Very interesting, I would like for IPS to confirm as well.

[Mark]

I wouldn't say every 30 minutes. I've suggested to people in the past that it might be an idea to do that once a day, though.

Those tables may also due to their nature benefit by being changed to InnoDB. You could argue that ibf_core_item_markers_storage could be MEMORY, but I wouldn't recommend it unless your only other choice is disabling topic markers completely.

[Fast Lane!]

I made innodb first. It did not help much (maybe some). The "defrag" (optimize) dropped my spikes from dreadful to 0. Seriously from peak loads of 80 to peak loads of 2. I am not sure what a good schedule is but every 30 minutes was what I tried. Maybe less frequent works but if it is working now I am not changing. Frankly the amount of time I put to figure this out (after dozens of other changes) is staggering.

As a note: I can say that every few hours does not help as I did it by hand and a few hours later the spikes were back. I automated at 30 minutes and everything has been amazing. I would bet the time between optimize runs is relative to the fragmentation amount which is in turn proportional to the forum traffic.

[Fast Lane!]

As a note. Memory tables use hash lookups (I think) and may not sort as fast as innodb/MyISAM. That means the "order by" MySQL statements that call that table all the time would run (potentially a lot) slower. I would not convert to memory. If this logic is not right I am not offended to be corrected :P.

[Invisiοnist]

Sounds good, for a noob like myself can you explain this a bit more:

Basically you want to make a cron job to execute the following commands against your database every 30 minutes or so (on an active board):

OPTIMIZE TABLE `ibf_core_item_markers`;
OPTIMIZE TABLE `ibf_core_item_markers_storage`;


How do I use cron to follow the commands every 30 mins?

[Fast Lane!]

You can create a System Scheduler "task" in the forums (to run the commands) or do as follows using the crontab (which is what i do since it is predictable and arguably safer):

(copied from my post linked above)

create a file called "db_optimize" with the following contents:

OPTIMIZE TABLE `ibf_core_item_markers_storage`;

OPTIMIZE TABLE `ibf_core_item_markers`;

then set a crontab as follows:

30 * * * * mysql -u"data base username" -p"data base password" "data base name" < db_optimize

enter the above items in " "'s without the quotes so for example if your username to your database is Tom then do ... "30 * * * * mysql -uTom ...", :).

the "< db_optimize" part of the cron points to the file. If the file is not in your users root directory you will need to point to it, ex: "/home/user/db_optimize".