Erratic spikes? I found a potential fix

[Fast Lane!]

I wont dribble on. I get lots of major spikes from the topic marker tables. They are small but I discovered highly fragmented in a short amount of time. I added a cronjob to optimize them every half hour. Since then -- goodness.

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

Make a cronjob to run this every 30 minutes. It is very fast and has prevented spikes so far on my server. Food for thought! I was getting 50 or more loads for brief times and not any so far. maybe just luck but I am hoping the final fix!

[Fast Lane!]

@all

As a follow up. Charles has confirmed that this is something that is "known" about. I suggest those having issues try this above. It has worked wonders for me, where nothing else did. Charles indicated this will be a feature in 3.1 (optimize automatically). Until then I suggest the above to help reduce spikes in load that at least for me, were very bad!

[Pupette]

Thanks Fast Lane, how do I set up this up in cPanel? Any help appreciated.

[Fast Lane!]

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

[Pupette]

Thanks for your reply. I'll give that a try.

[Bono]

Nice find, after those tables are optimized forum fly's, it's like 10x faster than normal.

Time to load index page goes down from 0.9 to 0.6s.

[Fast Lane!]

Awesome news! Mine has been TONS faster since I have this cron running. I hope IPB can circulate this news. I will cross post to get some eyes on it. Hopefully many people will not have to go through the same trouble / slow downs.

[sound]

could this be done via a board task
similar to the clean up mysql process one ?

[Fast Lane!]

Sure. I prefer the regularity of the crontab but either way would work :).

[ikillbill]

we completely disable markers, load still spike