Advertisement:

Author Topic: So heres what happened the other day...  (Read 8502 times)

Offline 4LP3RUZ1

  • SMF Hero
  • ******
  • Posts: 3,441
  • Gender: Male
  • Frozen frogs are gone!
So heres what happened the other day...
« on: March 10, 2014, 07:41:00 AM »
I wasn't 100% sure, but I'm guessing its a server config topic, so I'm posting it here...

Situation:
I have a Xen HVM VPS running 3 sites, all forum scripts. One site runs SMF and is pretty big, the other two are somewhat smaller and run vB 3. OS is Centos, ram is 2GB, server setup is nginx+php-fpm+apc.

What happened:
My SMF dB has a couple InnoDB tables, one of them being smf_members. Somehow it started crashing and taking down MySQL with it. This first happened during an automated backup process (mentioned below), mysqld would restart whenever smf_members got touched, which in turn resulted in all 3 sites losing mysql connections.

What I did:
Being wise and all :P I have a daily backup routine which sends the entire gzipped sql to Dropbox, for all three sites, every morning. I dropped the members table and loaded a recent backup (previous day). Problem solved.
However because the dB backups are done in sequence, and the SMF backup is done first, the backup process of the day the crash happened was interrupted by, once again, mysql connection drops and none of the other backups completed successfully.

So questions?
1. should I be worried about some sort of attack via member data? I have a few mods, tapatalk being one which I hate because it breaks my registration system (where I handcoded some hacks a while ago).
2. In order to make sure these sort of crashes only effect one site rather than all, is the only option I have running multiple instances of mysql?
3. Should I just convert smf_members back to myisam as then I'd have checking and repairing routines? (which are done before each backup)

btw I'm not an expert on hosting or anything, these sites are just ones I like and am active in, I just have some previous linux knowledge so I offered to host them for no profit...
Frozen frogs are back :(

Online CoreISP

  • Server Admin
  • Server Team
  • SMF Super Hero
  • *
  • Posts: 18,578
  • Gender: Male
  • Liroy van Hoewijk
Re: So heres what happened the other day...
« Reply #1 on: March 10, 2014, 01:26:02 PM »
Have you looked at the mySQL logs to see why it's crashing?
How big is the members table? What is your buffer pool size? What is the max transaction size?
((U + C + I)x(10 − S)) / 20xAx1 / (1 − sin(F / 10))
President/CEO of Simple Machines - Server Manager
Please do not PM for support - anything else is usually OK.

Offline 4LP3RUZ1

  • SMF Hero
  • ******
  • Posts: 3,441
  • Gender: Male
  • Frozen frogs are gone!
Re: So heres what happened the other day...
« Reply #2 on: March 10, 2014, 04:49:36 PM »
Well mysql logs say:

Quote
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 8479.
InnoDB: You may have to recover from a backup.
140309 10:13:12  InnoDB: Page dump in ascii and hex (16384 bytes):

members table is currently 13MB with around 21 thousand members,

buffer pool size = 128MB
max transaction also 128MB, but it was 64 before the crashes.
Frozen frogs are back :(

Offline Chen Zhen

  • Sophist Member
  • *****
  • Posts: 1,317
  • Gender: Male
  • The Quest is the Quest
    • Underdog-01 on GitHub
    • WebDev.ca
Re: So heres what happened the other day...
« Reply #3 on: March 10, 2014, 05:00:42 PM »
4LP3RUZ1,

  You stated that you make frequent backups (automated or otherwise), so do you have a backup previous to the initial issues? One that can be applied now since the settings were recently increased?  I understand that due to the possible time that has passed since the initial onset of issues, some data will be lost but at least it will possibly be stable. phpmyadmin also has some features/commands to repair a database due to corruption, have you tried using it?


Regards.

My SMF Mods & Plug-Ins

WebDev

“Either you repeat the same conventional doctrines everybody is saying, or else you say something true, and it will sound like it's from Neptune.” - Noam Chomsky

Offline Chen Zhen

  • Sophist Member
  • *****
  • Posts: 1,317
  • Gender: Male
  • The Quest is the Quest
    • Underdog-01 on GitHub
    • WebDev.ca
Re: So heres what happened the other day...
« Reply #4 on: March 10, 2014, 05:05:37 PM »
Quote from: 4LP3RUZ1
1. should I be worried about some sort of attack via member data? I have a few mods, tapatalk being one which I hate because it breaks my registration system (where I handcoded some hacks a while ago).

  Btw this sounds related to your issues since registration obviously deals with the members table. Perhaps you or Taptalk somehow corrupted the members table.
 

My SMF Mods & Plug-Ins

WebDev

“Either you repeat the same conventional doctrines everybody is saying, or else you say something true, and it will sound like it's from Neptune.” - Noam Chomsky

Offline 4LP3RUZ1

  • SMF Hero
  • ******
  • Posts: 3,441
  • Gender: Male
  • Frozen frogs are gone!
Re: So heres what happened the other day...
« Reply #5 on: March 10, 2014, 05:12:08 PM »
AFAIK, InnoDB tables don't get repaired or checked like MyISAM tables. I already fixed the crash by reverting, I just don't want something similar to happen again :)

As for the registration system,

its basically using custom profile fields to get a users name and surname, then what I do is create the display name with "name+surname+(member id)" I have been using it for years like that. Tapatalk however is not compatible with custom profile fields so people registering from there get an empty display name :( :(
Frozen frogs are back :(

Online CoreISP

  • Server Admin
  • Server Team
  • SMF Super Hero
  • *
  • Posts: 18,578
  • Gender: Male
  • Liroy van Hoewijk
Re: So heres what happened the other day...
« Reply #6 on: March 10, 2014, 05:19:49 PM »
Actually, you *can* repair InnoDB. It's just a whole different process, you just move up one level at a time. (1 -> 6)
http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html

On a table that's only 21MB and those settings, I am assuming it ain't telling lies: there is corruption, somewhere.
((U + C + I)x(10 − S)) / 20xAx1 / (1 − sin(F / 10))
President/CEO of Simple Machines - Server Manager
Please do not PM for support - anything else is usually OK.

Offline 4LP3RUZ1

  • SMF Hero
  • ******
  • Posts: 3,441
  • Gender: Male
  • Frozen frogs are gone!
Re: So heres what happened the other day...
« Reply #7 on: March 13, 2014, 01:25:18 AM »
I had another crash this morning, starting once again while the upgrade script (mysqldumper) was running.

This time it crashed while trying the optimize log_boards which is another InnoDB board. :(

This in turn corrupted another table for one of my vB sites, which could be repaired. But my log was filled with "autorepair failed" lines :(
Frozen frogs are back :(

Online CoreISP

  • Server Admin
  • Server Team
  • SMF Super Hero
  • *
  • Posts: 18,578
  • Gender: Male
  • Liroy van Hoewijk
Re: So heres what happened the other day...
« Reply #8 on: March 15, 2014, 11:28:54 AM »
If it keeps crashing, it calls for further investigation on the server-side to be honest... Needless to say: it's abnormal.
((U + C + I)x(10 − S)) / 20xAx1 / (1 − sin(F / 10))
President/CEO of Simple Machines - Server Manager
Please do not PM for support - anything else is usually OK.

Offline 4LP3RUZ1

  • SMF Hero
  • ******
  • Posts: 3,441
  • Gender: Male
  • Frozen frogs are gone!
Re: So heres what happened the other day...
« Reply #9 on: March 15, 2014, 03:59:19 PM »
I don't cut access to the forum while taking a backup, bad? not so bad?

edit: I'm now putting SMF on maintenance mode 2 while taking a backup, lets see how this goes...

edit2: I'm also switching the vB boards off too now and things seem to have calmed down, marking as solved.
« Last Edit: March 25, 2014, 04:45:07 AM by 4LP3RUZ1 »
Frozen frogs are back :(

Offline 4LP3RUZ1

  • SMF Hero
  • ******
  • Posts: 3,441
  • Gender: Male
  • Frozen frogs are gone!
Re: So heres what happened the other day...
« Reply #10 on: July 10, 2014, 03:30:58 AM »
do what now? :o
Frozen frogs are back :(

Online CoreISP

  • Server Admin
  • Server Team
  • SMF Super Hero
  • *
  • Posts: 18,578
  • Gender: Male
  • Liroy van Hoewijk
Re: So heres what happened the other day...
« Reply #11 on: July 10, 2014, 09:20:30 AM »
Could you be more specific? I thought you said the issue was resolved.
((U + C + I)x(10 − S)) / 20xAx1 / (1 − sin(F / 10))
President/CEO of Simple Machines - Server Manager
Please do not PM for support - anything else is usually OK.

Offline 4LP3RUZ1

  • SMF Hero
  • ******
  • Posts: 3,441
  • Gender: Male
  • Frozen frogs are gone!
Re: So heres what happened the other day...
« Reply #12 on: September 23, 2014, 10:52:13 AM »
I'm thinking there was a post that was deleted here :)

Yes, safe moding all the forums before taking backups worked. Havent had a crash since (like 3 months now?)
Frozen frogs are back :(