MySQL server has gone away

Author: jane     03 12th, 2010 in enart.abczj.com     edit

  • Hello,

    i´ve got 100 Mails from Vbulletin, saying:

    mysql error: MySQL server has gone away

    Then i logt in via SSH an lock at the MYSQL-Deamon:

    root:~ # /sbin/init.d/mysql status
    Checking for service MySQL: OK

    After restarting the MYSQL-Server the Error-Mails stop...

    Does someone know, where the Problem could be?

    Answers in english or german are welcome :-)


  • where can i find this... i´ve not set up the system...


  • ok,

    i found one line:

    011216 15:43:22 Error in accept: Too many open files

    and tausends of:

    011216 15:43:24 Aborted connection 196116 to db: 'vogelforen' user: 'vogelforen' host: `localhost' (Got an error reading com
    munication packets)
    011216 15:43:27 Aborted connection 196134 to db: 'vogelforen' user: 'vogelforen' host: `localhost' (Got an error reading com
    munication packets)
    011216 15:43:27 Aborted connection 196133 to db: 'usr_web93_1' user: 'web93' host: `localhost' (Got an error reading communi
    cation packets)


  • oki..

    set it to 512...

    can you have a look on this settings, from an other Server ?

    (on witch also vbulletin is running..

    set-variable = max_connections=450
    set-variable = key_buffer=16M
    set-variable = join_buffer=16M
    set-variable = record_buffer=5M
    set-variable = sort_buffer=16M
    set-variable = table_cache=2048
    set-variable = thread_cache_size=512K
    set-variable = max_allowed_packet=5M

    [myisamchk]
    set-variable = key_buffer=16M
    set-variable = sort_buffer=16M
    set-variable = read_buffer=16M
    set-variable = write_buffer=16M


  • PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME COMMAND
    9284 mysql 19 0 7500 7500 1964 R 0 94.8 1.9 2:39 mysqld

    Hi,

    can i stop mysql using so much from the CPU?

    Or ist this not a problem, if mysql use so much from the Prozessor...


  • what version of Suse Linux ??


  • +--------------------------+----------+
    Variable_name Value
    +--------------------------+----------+
    Aborted_clients 290
    Aborted_connects 5
    Bytes_received 2620300
    Bytes_sent 13773872
    Connections 1041
    Created_tmp_disk_tables 28
    Created_tmp_tables 183
    Created_tmp_files 0
    Delayed_insert_threads 0
    Delayed_writes 0
    Delayed_errors 0
    Flush_commands 1
    Handler_delete 54
    Handler_read_first 238
    Handler_read_key 16144
    Handler_read_next 19843
    Handler_read_prev 7687
    Handler_read_rnd 127367
    Handler_read_rnd_next 7304988
    Handler_update 845
    Handler_write 12789
    Key_blocks_used 777
    Key_read_requests 73137
    Key_reads 777
    Key_write_requests 153
    Key_writes 136
    Max_used_connections 5
    Not_flushed_key_blocks 0
    Not_flushed_delayed_rows 0
    Open_tables 149
    Open_files 286
    Open_streams 0
    Opened_tables 155
    Questions 47748
    Select_full_join 28
    Select_full_range_join 0
    Select_range 510
    Select_range_check 0
    Select_scan 4708
    Slave_running OFF
    Slow_launch_threads 0
    Slow_queries 0
    Sort_merge_passes 0
    Sort_range 431
    Sort_rows 127538
    Sort_scan 499
    Threads_cached 4
    Threads_connected 2
    Threads_running 2
    Uptime 2334
    +--------------------------+----------+


  • Hello,

    i changed

    set-variable = thread_cache_size=256

    to

    set-variable = thread_cache_size=1M

    This should make the mysql-server make a little bit faster.

    Or should i have more than 1M ?


  • what's your server specs as well and cpu/memory and OS?

    someone else i know using 3.23.28 is having the same problems could be a bug in 3.23.28 ?


  • Originally posted by Vogelfreund
    Hello,

    i changed

    set-variable = thread_cache_size=256

    to

    set-variable = thread_cache_size=1M

    This should make the mysql-server make a little bit faster.

    Or should i have more than 1M ? oooooooh not that high you'll make mysql keel over :)

    about 512 and restart mysql and see how it goes


  • i'd check your mysql error log for ideas on what went wrong


  • also do you have a url to your forums ? how many average and peak concurrent users do you get to the forums ?

    can you post your top stats for memory/cpu usage ?


  • You are great :-)

    cat /etc/SuSE-release
    SuSE Linux 6.4 (i386)
    VERSION = 6.4


  • Originally posted by Vogelfreund
    011216 15:43:27 Aborted connection 196133 to db: 'usr_web93_1' user: 'web93' host: `localhost' (Got an error reading communi
    cation packets)

    This Message i got nearly 150 per minute !!!


  • Hello,

    there is no automatic restart if it crashs.....


  • Mysql Version 3.23.28

    ansi_mode OFF Žndern L÷schen
    back_log 50 Žndern L÷schen
    basedir /usr/ Žndern L÷schen
    character_set latin1 Žndern L÷schen
    character_sets latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 Žndern L÷schen
    concurrent_insert ON Žndern L÷schen
    connect_timeout 5 Žndern L÷schen
    datadir /var/mysql/ Žndern L÷schen
    delay_key_write ON Žndern L÷schen
    delayed_insert_limit 100 Žndern L÷schen
    delayed_insert_timeout 300 Žndern L÷schen
    delayed_queue_size 1000 Žndern L÷schen
    flush OFF Žndern L÷schen
    flush_time 0 Žndern L÷schen
    init_file Žndern L÷schen
    interactive_timeout 28800 Žndern L÷schen
    join_buffer_size 5238784 Žndern L÷schen
    key_buffer_size 16773120 Žndern L÷schen
    language /usr/share/mysql/english/ Žndern L÷schen
    large_files_support ON Žndern L÷schen
    locked_in_memory OFF Žndern L÷schen
    log OFF Žndern L÷schen
    log_update OFF Žndern L÷schen
    log_bin OFF Žndern L÷schen
    log_slave_updates OFF Žndern L÷schen
    long_query_time 10 Žndern L÷schen
    low_priority_updates OFF Žndern L÷schen
    lower_case_table_names 0 Žndern L÷schen
    max_allowed_packet 1048576 Žndern L÷schen
    max_connections 450 Žndern L÷schen
    max_connect_errors 10 Žndern L÷schen
    max_delayed_threads 20 Žndern L÷schen
    max_heap_table_size 16777216 Žndern L÷schen
    max_join_size 4294967295 Žndern L÷schen
    max_sort_length 1024 Žndern L÷schen
    max_tmp_tables 32 Žndern L÷schen
    max_write_lock_count 4294967295 Žndern L÷schen
    myisam_recover_options OFF Žndern L÷schen
    myisam_sort_buffer_size 8388608 Žndern L÷schen
    net_buffer_length 16384 Žndern L÷schen
    net_read_timeout 30 Žndern L÷schen
    net_retry_count 10 Žndern L÷schen
    net_write_timeout 60 Žndern L÷schen
    pid_file /var/mysql/engerffm.pid Žndern L÷schen
    port 3306 Žndern L÷schen
    protocol_version 10 Žndern L÷schen
    record_buffer 5238784 Žndern L÷schen
    query_buffer_size 0 Žndern L÷schen
    server_id 0 Žndern L÷schen
    skip_locking ON Žndern L÷schen
    skip_networking OFF Žndern L÷schen
    skip_show_database OFF Žndern L÷schen
    slow_launch_time 2 Žndern L÷schen
    socket /tmp/mysql.sock Žndern L÷schen
    sort_buffer 8388600 Žndern L÷schen
    table_cache 1024 Žndern L÷schen
    table_type MYISAM Žndern L÷schen
    thread_cache_size 256 Žndern L÷schen
    thread_stack 65536 Žndern L÷schen
    timezone CET Žndern L÷schen
    tmp_table_size 1048576 Žndern L÷schen
    tmpdir /tmp/ Žndern L÷schen
    version 3.23.28-gamma Žndern L÷schen
    wait_timeout 28800 Žndern L÷schen


  • Originally posted by Vogelfreund
    +--------------------------+----------+
    Variable_name Value
    +--------------------------+----------+
    Aborted_clients 290
    Aborted_connects 5
    Bytes_received 2620300
    Bytes_sent 13773872
    Connections 1041
    +--------------------------+----------+

    After movig some Traffic to an other maschine and restarting mysql:

    Aborted_clients 2
    Aborted_connects 1
    Bytes_received 137475933
    Bytes_sent 1017293563
    Connections 88989


  • P2 800 Mhz, 512 MB RAM, Suse Linux...


  • mysql problem explanation at

    http://www.mysql.com/doc/C/o/Communication_errors.html

    http://www.mysql.com/doc/N/o/Not_enough_file_handles.html

    http://www.mysql.com/doc/T/a/Table_cache.html

    as to too many open files, it means you're running out of file descriptors..

    what os, kernel version you using ?

    mysql version, and mysql variables are set in /etc/my.cnf ?


  • Originally posted by Vogelfreund
    where can i find this... i´ve not set up the system... i'm not familiar with SUSE :(

    but a search lead me to http://sdb.suse.de/en/sdb/html/jam_release.html


    information is included in the file /etc/SuSE-release. The easiest way to view the content of this file is using the cat command. For example: cat /etc/SuSE-release produces an output as follows:

    SuSE Linux 7.1 (i386)
    Version=7.1


  • Hello,

    the server on witch i had the problems had nearly 3,5 Gb Traffic a Day....

    Now i moved some domains to an other server, so that the server who hosted the vbulletinforum comes under 3 Gb Traffic per day and it works !!!

    In the last 24 hours i had have only 2 Messages about "Aborted connection". In the Time where the Traffic on this maschine was over 3 GB a day, i had nearly 150 Messages about "Aborted connection" per Minute!!!!

    Seems that the Networkcard cant handel more then 3 GB Traffic a day... could this be? Or any other ideer??

    memory/cpu was not the Problem... mostly 100MB free memory+256MB Swap free and 60% CPU idle...

    Thanks for all your help..


  • do you have mysql set up to automatically restart when it goes down crashes ?

    it could be mysql went down and came back up

    you can check your mysql *.err log file in mysql data directory to see what caused it


  • you may want to increase your mysql variable

    thread_cache_size if you have alot of connections to mysql


  • Webserver and MSQL is on the same Maschine...


  • Originally posted by Vogelfreund
    oki..

    set it to 512...

    can you have a look on this settings, from an other Server ?

    (on witch also vbulletin is running..

    set-variable = max_connections=450
    set-variable = key_buffer=16M
    set-variable = join_buffer=16M
    set-variable = record_buffer=5M
    set-variable = sort_buffer=16M
    set-variable = table_cache=2048
    set-variable = thread_cache_size=512K
    set-variable = max_allowed_packet=5M

    [myisamchk]
    set-variable = key_buffer=16M
    set-variable = sort_buffer=16M
    set-variable = read_buffer=16M
    set-variable = write_buffer=16M i'd reduce your record and join buffer sizes and your table_cache setting you could could be your set them too high causing mysql to crash...

    try

    set-variable = max_connections=450
    set-variable = key_buffer=16M
    set-variable = join_buffer=4M
    set-variable = record_buffer=4M
    set-variable = sort_buffer=8M
    set-variable = table_cache=1280
    set-variable = thread_cache_size=512
    set-variable = max_allowed_packet=3M

    [myisamchk]
    set-variable = key_buffer=16M
    set-variable = sort_buffer=16M
    set-variable = read_buffer=16M
    set-variable = write_buffer=16M

    restart mysql







    • #If you have any other info about this subject , Please add it free.#
    Your name:
    E-mail:
    Telphone:

    Your comments:


    If you have any other info about MySQL server has gone away , Please add it free.

    Google Adsense Profits Mixed In With Affiliate Marketing - Google Adsense Kids Eating Healthy