From: Roland B. <ro...@at...> - 2014-01-03 18:38:51
|
Damien, thank you for cleaning up the database. Working in forum feels snappier now. > B) Are there any objections to dropping the 2000+ inactive users (they > never posted and possibly never even confirmed their registration) ? I clean up users from time to time. (probably not the last months) You have to be careful as there are bugs in phpBB e.g. there are such kind of accounts: users joined, "last visited" field is not set, although the user made posts http://www.mantisbt.org/forums/memberlist.php?mode=viewprofile&u=26672 http://www.mantisbt.org/forums/memberlist.php?mode=viewprofile&u=26959 ... Roland > John Reese <jo...@no...> hat am 3. Januar 2014 um 01:08 geschrieben: > > > I have never really been involved in maintenance of the forum, so I can't > answer any questions about its setup, but I will say this: > > A: Just rename the tables to start with. If nothing breaks, then I think > it's safe to blow them away. At the worst, we have backups. Alternately, > to be absolutely sure, you could always try grepping the phpbb3 codebase to > look for those table names (minus the phpbb_ prefix) to see if they ever > get used. > > B: I personally say drop inactive users, as long as their account is > actually inactive (ie, they have never logged in). Just keep read-only > users in mind; some of them like to register to customize their reading > experience without ever posting. > > C: The local backups are somewhat useful for reverting changes easily, like > when you do something like this and find out that you broke something. :) > FWIW, we do have backups for the entire Linode instance on a daily/weekly > schedule, but we can only restore entire backups, either to the existing > instance or a fresh instance, so it's not as quick/easy to just restore a > small set of files. This is where the local backups are nice to have. > Though also FWIW, we can probably safely delete any local backups older > than 14 days.... > > Lastly, awesome job investigating, and good catch. :) > > > > > John Reese > noswap.com > > > On Thu, Jan 2, 2014 at 3:46 PM, Damien Regad <dr...@ma...> wrote: > > > Hi all, > > > > I did some research into recent mantisbt.org downtimes, and noticed that > > we frequently had a spike in disk i/o before the server flatlined. > > Looking further, I realized that the /dev/xvda filesystem was 92% full, > > and nearly 9GB were being used by the forums database. > > > > I made a local copy of the whole DB and forums, and started playing with > > it, here's what I found out > > > > forums database: > > - numerous .BAK files for DB files (totaling over 6GB), probably the > > result of automatic repair when restarting MySQL after a crash > > - sessions table had over 5.5M rows (about 1.5 GB) > > - several other phpBB3 tables supposed to be small actually had hundreds > > of thousands of rows (e.g. confirm 300MB, login_attempts 60MB) > > - duplicate tables with phpbb_ prefix (possibly leftover from upgrade to > > phpbb3 ?) > > - orphaned attachments > > - over 2000 inactive users who never logged in > > > > forums code/setup: > > - impossible to execute upgrade script > > - cleaning cache did not work > > - unusual files and directories permissions > > - impossible to run phpBB upgrade (looked like CSS issue) > > - lots of old DB backups, dating back to 2008 (about 400MB) > > > > After further investigation and cleanup tests, I managed to reduce the > > footprint of the forums database by more than 99%, from over 8 GB to 66 > > MB (!) - here's the details of what I did: > > > > 1. delete .BAK files > > 2. drop phpbb_* tables > > 3. fix various permission issues on the forums code installation (e.g. > > missing write access on cache directory) - standardized on 644 for files > > and 755 for directories, except when otherwise needed by phpBB (as per > > documentation, some dirs must have write access) > > 4. purge sessions > > 5. purge cache > > 6. purge inactive users > > 7. delete orphaned attachments > > 8. optimize mysql db > > 9. upgrade to latest version of phpBB > > > > My testing showed no negative side effects on my local setup (i.e. > > forums remain fully operational as far as I can tell), so today I > > applied the above steps to mantibt.org, with the exception of #2 and #6. > > > > Good news, we are now down to 53% usage on filesystem, forums db is 82MB. > > > > Three questions to close the topic: > > > > A) Can someone (John maybe ?) please confirm if the phpbb_* tables are > > indeed a leftover of the upgrade from phpBB2 as I think they are, if so > > I would drop them as well (note, I already took a backup and saved it in > > /srv/www/forums/backup_phpbb_tbl_20140102.tar.gz) > > > > B) Are there any objections to dropping the 2000+ inactive users (they > > never posted and possibly never even confirmed their registration) ? > > > > C) Is there any use in keeping the old DB backups and upgrade files ? > > > > D > > > > PS: I'm not sure that what I did will indeed fix the server crashes, but > > for sure it can't hurt :-) > > > > > > > > > > ------------------------------------------------------------------------------ > > Rapidly troubleshoot problems before they affect your business. Most IT > > organizations don't have a clear picture of how application performance > > affects their revenue. With AppDynamics, you get 100% visibility into your > > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics > > Pro! > > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk > > _______________________________________________ > > mantisbt-dev mailing list > > man...@li... > > https://lists.sourceforge.net/lists/listinfo/mantisbt-dev > > > ------------------------------------------------------------------------------ > Rapidly troubleshoot problems before they affect your business. Most IT > organizations don't have a clear picture of how application performance > affects their revenue. With AppDynamics, you get 100% visibility into your > Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro! > http://pubads.g.doubleclick.net/gampad/clk?id=84349831&iu=/4140/ostg.clktrk_______________________________________________ > mantisbt-dev mailing list > man...@li... > https://lists.sourceforge.net/lists/listinfo/mantisbt-dev |