Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Quick Links


Categories

In this Discussion

› › Mysql InnoDB crash every unexpected shutdown,is this normal?
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

Mysql InnoDB crash every unexpected shutdown,is this normal?

ZenoZeno Member
in Help

Every time after a unexpected shutdown I used dump/import 5G data for recover the InnoDB database

It really makes me hard

Is this normal? Have you encountered such a problem?

Comments

  • SpencerSpencer Member

    Yup!

  • ZenoZeno Member

    @Spencer said:
    Yup!

    Thanks for your reply,
    How do you deal this problem?
    or like me do dump/import every time?

  • SpencerSpencer Member

    How often are you getting unexpected shutdowns?

  • AbdussamadAbdussamad Member

    @Zeno said:

    It really makes me hard

    That's nice.

  • colmcolm Member

    What kind of VPS are you using? I've had this issue with a KVM VPS, probably due to cache settings on the host node.

  • texteditortexteditor Member

    @Abdussamad said:
    That's nice.

    I love it when you talk nerdy to me

  • RuchirablogRuchirablog Member

    @Zeno said:
    It really makes me hard

  • FRCoreyFRCorey Member

    You would be better off researching and setting up active/active mysql db's or alternatively using a better storage engine other than innodb

  • ZenoZeno Member

    @FRCorey said:
    You would be better off researching and setting up active/active mysql db's or alternatively using a better storage engine other than innodb

    What kind of storage engine that better than InnoDB? Can you give me a suggestion?

    Thank you :)

  • ZenoZeno Member

    @colm said:
    What kind of VPS are you using? I've had this issue with a KVM VPS, probably due to cache settings on the host node.

    Yes,problem occurs in the KVM VPS,
    Is it true that OpenVZ or XEN do not have this problem?

  • ZenoZeno Member

    @Spencer said:
    How often are you getting unexpected shutdowns?

    About once a month or two, but it is also very annoying

  • colmcolm Member

    OpenVZ would be less likely to have this problem.

    KVM settings can be adjusted on the host node to make writes safer in case of unexpected shutdown. You can check with your provider to see if anything is possible. See http://www.ilsistemista.net/index.php/virtualization/23-kvm-storage-performance-and-cache-settings-on-red-hat-enterprise-linux-62.html?start=2

    What file system are you using and which VPS provider are you using (if you don't mind saying)?

  • ZenoZeno Member

    @colm said:
    OpenVZ would be less likely to have this problem.

    KVM settings can be adjusted on the host node to make writes safer in case of unexpected shutdown. You can check with your provider to see if anything is possible. See http://www.ilsistemista.net/index.php/virtualization/23-kvm-storage-performance-and-cache-settings-on-red-hat-enterprise-linux-62.html?start=2

    What file system are you using and which VPS provider are you using (if you don't mind saying)?

    Very thanks for your help

    I'm using buyVM KVM plan: LV Storage-250GB
    That file system:
    /dev/sda1 on / type ext4 (rw,errors=remount-ro)

    I intend to move the database to their "Offloaded SQL" to avoid this problem

    But I still wonder is there a simple way to avoid this problem(on KVM VPS), for example a better than the InnoDB storage engine?

  • MagiobiwanMagiobiwan Member

    Do you have acpid running? In the event of a node shutdown for maint, etc. that'll allow the node to do a GRACEFUL shutdown for your server, rather than a hard shutdown.

  • ZenoZeno Member

    @Magiobiwan said:
    Do you have acpid running? In the event of a node shutdown for maint, etc. that'll allow the node to do a GRACEFUL shutdown for your server, rather than a hard shutdown.

    Thanks for help

    Can "acpid" make the InnoDB safe when a power outage?

    Most "unexpected shutdown" because of power outages

  • MagiobiwanMagiobiwan Member

    Well, in the event of a "graceful" planned node shutdown it'll help. Not sure about in the event of an UNPlanned Shutdown.

  • colmcolm Member

    Acpid won't help for an unexpected shutdown of the host node such as a power outage. It will help if the host node is being rebooted for maintenance, etc., so you should definitely make sure it's running.

  • ZenoZeno Member

    @Magiobiwan said:
    Well, in the event of a "graceful" planned node shutdown it'll help. Not sure about in the event of an UNPlanned Shutdown.

    I believe that directly run "# shutdown -h now" does not make InnoDB crash

    The main problem is the host power outage, which happened many times on my KVM VPS :(

    Thanks again! :)

  • ZenoZeno Member
    edited July 2013

    @colm said:
    Acpid won't help for an unexpected shutdown of the host node such as a power outage. It will help if the host node is being rebooted for maintenance, etc., so you should definitely make sure it's running.

    Thank you ! I checked that It's running on my KVM vps, right?

    # ps aux | grep acpid

    root 16 0.0 0.0 0 0 ? S Jun27 0:00 [kacpid]

    root 32732 0.0 0.2 1884 620 pts/1 S+ 15:54 0:00 grep acpid

    (it's really won't help for an unexpected shutdown)

  • colmcolm Member
    edited July 2013

    @Zeno, I use the same plan as you but thankfully have not had the same issues. What hard disk driver are you using for your KVM settings?

    Perhaps we can ask @Francisco if he has any ideas on how to make your writes safer...

    Using offloaded SQL sounds like a good idea though.

  • ZenoZeno Member
    edited July 2013

    @colm said:
    Zeno, I use the same plan as you but thankfully have not had the same issues. What hard disk driver are you using for your KVM settings?

    Perhaps we can ask Francisco if he has any ideas on how to make your writes safer...

    Using offloaded SQL sounds like a good idea though.

    Is probably because I write data too frequent

    BuyVM is awesome and stable, I think it's not their fault

    The same problem also occurs in other vps provider of KVM even my own test dedicated server

    Power outage = InnoDB crash

    Maybe I should try OpenVZ? It's will help?

  • colmcolm Member

    OpenVZ will likely help as MySQL will have direct access to the host file system and when it tells the disk to sync it really will sync.

    There are possibly other solutions that would allow you to continue using KVM. Could you check what KVM settings you have set for the hard disk driver under the Stallion VPS panel? Maybe VirtIO would improve the safety of your writes.

  • ZenoZeno Member
    edited July 2013

    @colm said:
    OpenVZ will likely help as MySQL will have direct access to the host file system and when it tells the disk to sync it really will sync.

    There are possibly other solutions that would allow you to continue using KVM. Could you check what KVM settings you have set for the hard disk driver under the Stallion VPS panel? Maybe VirtIO would improve the safety of your writes.

    Thanks @colm , you'are so cool

    There is 3 options in Setting of disk driver under the Stallion VPS panel:

    IDE << Current selection

    SCSI

    VIRTIO (not always supported)

    I will choose VIRTIO see what changes, Thanks! :)

  • RalliasRallias Member

    @colm said:
    Acpid won't help for an unexpected shutdown of the host node such as a power outage. It will help if the host node is being rebooted for maintenance, etc., so you should definitely make sure it's running.

    It helps you if there's a UPS that gives you enough time to shutdown.

  • finlandvpsfinlandvps Member

    @zeno innodb should be crash safe, it should be able to recover automatically. I havent had crash issues really, can you check mysql setting innodb-flush-log-at-trx-commit ?

  • ZenoZeno Member
    edited July 2013

    @finlandvps said:
    zeno innodb should be crash safe, it should be able to recover automatically. I havent had crash issues really, can you check mysql setting innodb-flush-log-at-trx-commit ?

    Recover automatically ? Please tell me how to do!

    my my.cnf setting is:

    innodb_flush_log_at_trx_commit = 0

    Thanks for help :)

  • finlandvpsfinlandvps Member

    @zeno set innodb_flush_log_at_trx_commit = 1. Do you get any warnings when you startup mysql after a crash? Can you post the parts of the log to pastebin for example, so I could see what exactly has crashed and why doesn't the InnoDB crash recovery work for you? Are you sure your tables are InnoDB? show create table tablename;

  • ZenoZeno Member
    edited July 2013

    @finlandvps said:
    zeno set innodb_flush_log_at_trx_commit = 1. Do you get any warnings when you startup mysql after a crash? Can you post the parts of the log to pastebin for example, so I could see what exactly has crashed and why doesn't the InnoDB crash recovery work for you? Are you sure your tables are InnoDB? show create table tablename;

    Thanks, I'll try setting to: innodb_flush_log_at_trx_commit = 1

    The table absolutely sure that all InnoDB in Mysql5.5

    Mysql does not get start after power outage(Crash)

    And I get the error log is as follows:

    111116 15:49:36  InnoDB: Error: page 393457 log sequence number 111 561,760,232
InnoDB: is in the future! Current system log sequence number 70 3,946,969,851.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 211.
InnoDB: You may have to recover from a backup.
Sign In or Register to comment.