Discover Centmin Mod today
Register Now

MariaDB Crashes on large DB imports

Discussion in 'MariaDB & General MySQL news & discussions' started by BamaStangGuy, Mar 26, 2015.

  1. BamaStangGuy

    BamaStangGuy Active Member

    465
    136
    43
    May 25, 2014
    Ratings:
    +179
    Local Time:
    4:30 PM
    We have tried importing our 46GB database in via mysqlimport and via standard mysql but both time it crashses. Here is the log:

    Code:
    150325 19:16:33 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '10.0.17-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
    150325 20:10:08 [ERROR] mysqld got signal 11 ;
    This could be because you hit a bug. It is also possible that this binary
    or one of the libraries it was linked against is corrupt, improperly built,
    or misconfigured. This error can also be caused by malfunctioning hardware.
    
    To report this bug, see http://kb.askmonty.org/en/reporting-bugs
    
    We will try our best to scrape up some info that will hopefully help
    diagnose the problem, but since we have already crashed,
    something is definitely wrong and this may fail.
    
    Server version: 10.0.17-MariaDB
    key_buffer_size=53687091200
    read_buffer_size=65536
    max_used_connections=16
    max_threads=302
    thread_count=1
    It is possible that mysqld could use up to
    key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 52493015 K  bytes of memory
    Hope that's ok; if not, decrease some variables in the equation.
    
    Thread pointer: 0x0x7f433fe91008
    Attempting backtrace. You can use the following information to find out
    where mysqld died. If you see no messages after this, something went
    terribly wrong...
    stack_bottom = 0x7f53dfd53d30 thread_stack 0x48000
    /usr/sbin/mysqld(my_print_stacktrace+0x2b)[0xb743db]
    /usr/sbin/mysqld(handle_fatal_signal+0x398)[0x726a38]
    /lib64/libpthread.so.0[0x36e860f710]
    /lib64/libpthread.so.0(pthread_cond_signal+0x44)[0x36e860be34]
    
    Trying to get some variables.
    Some pointers may be invalid and cause the dump to abort.
    Query (0x7f42fb41d020): is an invalid pointer
    Connection ID (thread ID): 260
    Status: NOT_KILLED
    
    Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on
    
    The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
    information that should help you find out what is causing the crash.
    150325 20:10:08 mysqld_safe Number of processes running now: 0
    150325 20:10:08 mysqld_safe mysqld restarted

     
    • Like Like x 1
  2. eva2000

    eva2000 Administrator Staff Member

    29,049
    6,591
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,786
    Local Time:
    7:30 AM
    Nginx 1.13.x
    MariaDB 5.5
  3. BamaStangGuy

    BamaStangGuy Active Member

    465
    136
    43
    May 25, 2014
    Ratings:
    +179
    Local Time:
    4:30 PM
  4. eva2000

    eva2000 Administrator Staff Member

    29,049
    6,591
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,786
    Local Time:
    7:30 AM
    Nginx 1.13.x
    MariaDB 5.5
    Unfortunately, Centmin Mod is provided as is and this troubleshooting would be beyond the type of support I'd be able to provide on the forums for free. The mysql error log you posted in first post has alot of info, suggestions as to why it's segfaulting on import as well as ways to find out why. It could very well be a MariaDB bug so the official Jira bug tracker might be a source of info too System Dashboard - JIRA

    You can do to rule out the server/hardware as a culprit would be to try the import on another Centmin Mod MariaDB 10.0.17 based server and see if the same problem happens there as well as other non-MariaDB based servers.
     
  5. BamaStangGuy

    BamaStangGuy Active Member

    465
    136
    43
    May 25, 2014
    Ratings:
    +179
    Local Time:
    4:30 PM
    I understand that. Was looking for community feedback more than anything else. BTW, we replaced MariaDB with Percona and it imported with no issue. So there is a bug somewhere.
     
    • Informative Informative x 2
  6. BamaStangGuy

    BamaStangGuy Active Member

    465
    136
    43
    May 25, 2014
    Ratings:
    +179
    Local Time:
    4:30 PM
    I would be interested to know if you have any large database sites using MyISAM running MariaDB 10 though
     
  7. eva2000

    eva2000 Administrator Staff Member

    29,049
    6,591
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,786
    Local Time:
    7:30 AM
    Nginx 1.13.x
    MariaDB 5.5
    interesting so definitely a bug in MariaDB 10.0.17, if you try downgrading MariaDB 10.0.16 and see would be another option

    might want to report the bug System Dashboard - JIRA at Reporting Bugs - MariaDB Knowledge Base

    List of most important bugs at [MDEV-7057] Track most important upstream bugs which also affect MariaDB - JIRA

    large databases mainly use InnoDB now not MyISAM
     
    Last edited: Mar 26, 2015
  8. eva2000

    eva2000 Administrator Staff Member

    29,049
    6,591
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,786
    Local Time:
    7:30 AM
    Nginx 1.13.x
    MariaDB 5.5
    so @BamaStangGuy how did MariaDB 10.1.21 fair with your forum database this time round ? fixed or same issue ?
     
  9. BamaStangGuy

    BamaStangGuy Active Member

    465
    136
    43
    May 25, 2014
    Ratings:
    +179
    Local Time:
    4:30 PM
    We ran into an issue with max_seeks_for_key = 1000 which was a change from how our Percona server was setup.

    Mike spent a few days troubleshooting and finally figured out our slow query (7 seconds) was due to this.

    So if you run a large XenForo forum and are seeing slow query issues with MariaDB, this is likely the issue.

    Other than that the database imported fine and things seem to be running smoothly now.
     
    • Informative Informative x 1
  10. eva2000

    eva2000 Administrator Staff Member

    29,049
    6,591
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,786
    Local Time:
    7:30 AM
    Nginx 1.13.x
    MariaDB 5.5
    Awesome to get to the bottom of the slow query issue, looks like a potential issue which can be fixed by removing the setting form default templates :)

    default on MariaDB for max_seeks_for_keys is 4294967295

    Glad to hear the import crash bug is gone too :)
     
    Last edited: Feb 1, 2017
    • Like Like x 1
  11. pamamolf

    pamamolf Well-Known Member

    2,538
    231
    63
    May 31, 2014
    Ratings:
    +394
    Local Time:
    12:30 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    Should we remove it from our existing my.cnf file? :)
     
  12. eva2000

    eva2000 Administrator Staff Member

    29,049
    6,591
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,786
    Local Time:
    7:30 AM
    Nginx 1.13.x
    MariaDB 5.5
  13. Jimmy

    Jimmy Premium Member Premium Member

    1,026
    231
    63
    Oct 24, 2015
    East Coast USA
    Ratings:
    +555
    Local Time:
    5:30 PM
    1.13.x
    MariaDB 10.1.x
    I know for my database on my one IPB forum I need to change some settings in the my.cnf file to do a db restore. The below is from my personal Master Centmin Docs.

     
    • Informative Informative x 1
  14. eva2000

    eva2000 Administrator Staff Member

    29,049
    6,591
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,786
    Local Time:
    7:30 AM
    Nginx 1.13.x
    MariaDB 5.5
    @Jimmy shouldn't need to set that large values interesting. FYI, net_buffer_length has a fixed max settable value of 1MB any way MySQL :: MySQL 5.7 Reference Manual :: 6.1.5 Server System Variables AND max_allowed_packet max is 1GB https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet
    FYI max_seeks_for_key=1000 is in official MySQL 5.7 docs for recommendations but for large tables heh MySQL :: MySQL 5.7 Reference Manual :: 9.2.1.22 How to Avoid Full Table Scans
     
    Last edited: Feb 1, 2017
    • Informative Informative x 1
  15. Jimmy

    Jimmy Premium Member Premium Member

    1,026
    231
    63
    Oct 24, 2015
    East Coast USA
    Ratings:
    +555
    Local Time:
    5:30 PM
    1.13.x
    MariaDB 10.1.x
    My upload of a large database keep crashing... well actually it just stopped responding. I ended up researching uploading of large databases and got the link I referenced in my previous post. After I changed those setting the db uploaded without issue.

    Did the same thing the other day and didn't have any issue with a db 20+ GB. I changed back the settings when I was done.
     
    • Informative Informative x 1
  16. eva2000

    eva2000 Administrator Staff Member

    29,049
    6,591
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,786
    Local Time:
    7:30 AM
    Nginx 1.13.x
    MariaDB 5.5
    Interesting i haven't had issues with dbs up to 60GB in size with max_allowed_packet between 256-512MB
     
  17. Jimmy

    Jimmy Premium Member Premium Member

    1,026
    231
    63
    Oct 24, 2015
    East Coast USA
    Ratings:
    +555
    Local Time:
    5:30 PM
    1.13.x
    MariaDB 10.1.x
    There wasn't any logic behind those values I used. When I changed them when I was having an issue, I just wanted large numbers so that the issue would be solved. Since I was only changing them for the restore, I was going to be changing them back. So, they could probably be much lower and achieve the same results.
     
    • Informative Informative x 1
  18. eva2000

    eva2000 Administrator Staff Member

    29,049
    6,591
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,786
    Local Time:
    7:30 AM
    Nginx 1.13.x
    MariaDB 5.5
    I see :)
     
  19. pamamolf

    pamamolf Well-Known Member

    2,538
    231
    63
    May 31, 2014
    Ratings:
    +394
    Local Time:
    12:30 AM
    Nginx-1.13.x
    MariaDB 10.1.x
    Official statement is that it will help to have a value of 1000 but users report that is better to use the default much bigger value......

    Confused and not sure if it is better to disable it or not for my IPB forum...
     
  20. Jimmy

    Jimmy Premium Member Premium Member

    1,026
    231
    63
    Oct 24, 2015
    East Coast USA
    Ratings:
    +555
    Local Time:
    5:30 PM
    1.13.x
    MariaDB 10.1.x
    Official statement from IPB? Did you post a thread on the IPS forum?