MySQL server has gone away

[Vogelfreund]

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 :-)

[eva2000]

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

[Vogelfreund]

Hello,

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

[eva2000]

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

[Vogelfreund]

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)

[Vogelfreund]

Webserver and MSQL is on the same Maschine...

[Vogelfreund]

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 !!!

[eva2000]

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 ?

[Vogelfreund]

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

[eva2000]

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 ?

[Vogelfreund]

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

[eva2000]

what version of Suse Linux ??

[Vogelfreund]

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

[eva2000]

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

[Vogelfreund]

You are great :-)

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

[Vogelfreund]

+--------------------------+----------+
| 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 |
+--------------------------+----------+

[eva2000]

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 ?

[Vogelfreund]

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..

[Vogelfreund]

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

[eva2000]

you may want to increase your mysql variable

thread_cache_size if you have alot of connections to mysql

[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 ?

[eva2000]

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

[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

[Vogelfreund]

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...

[eva2000]

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