mysql server has gone away

[SecondSight]

Hello ! :)

I have this "mysql server has gone away" error everyday in my cache directory... For instance I found it today, but I don' understand why, because I don't have many members online today (around 655 at the moment), and the server load is around 0.25.

My my.cnf is this one :

mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

#log-slow-queries = /var/log/mysql-slow.log

#long_query_time = 3

#log-long-format

#log-queries-not-using-indexes

skip-locking

skip-innodb

query_cache_limit=16M

query_cache_size=768M

query_cache_type=1

query_cache_min_res_unit=2K

tmp_table_size=1024M

max_heap_table_size=1024M

max_user_connections=200

max_connections=200

interactive_timeout=200

wait_timeout=300

connect_timeout=300

thread_cache_size=256

key_buffer=4096M

join_buffer=2M

max_allowed_packet=32M

table_cache=4000

record_buffer=12M

sort_buffer_size=12M

read_buffer_size=12M

read_rnd_buffer_size=12M

max_connect_errors=10

thread_concurrency=16

myisam_sort_buffer_size=1024M

low_priority_updates=1

server-id=1

If you had this problem and managed to fix it, can you tell me how you did it or give me pieces of advice ?

Thank you ! :)

[AlexJ]

I have same issue. We only have like 30-60 users online and i get same error every day.

My max connection to MySQL where around 15 and I have limit set to 75 in my.cnf config.

[joelle]

This is strange, I have the same problem the last couple days. Mysql keeps on going down.

[SecondSight]

If I have a look at the cgi files, I notice these problems always have something to do with search. For instance :

Date: Fri, 24 Sep 2010 10:48:15 +0000

Error: 2006 - MySQL server has gone away

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

mySQL query error: SELECT * FROM ibf_attachments WHERE attach_rel_module='post' AND ( attach_rel_id IN (4199317,4152382,4151867,4130655,4098163,4064055,4064028,4063947,4037449,3956821,3892175,3760745,3661389,3324831) )

Date: Sun, 26 Sep 2010 19:38:54 +0000

Error: 1317 - Query execution was interrupted

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

mySQL query error: SELECT COUNT(*) as total_results FROM ibf_members m LEFT JOIN ibf_profile_portal p ON ( p.pp_member_id=m.member_id ) WHERE m.members_l_display_name LIKE '%energie%' OR p.pp_bio_content LIKE '%energie%' OR p.signature LIKE '%energie%' OR p.pp_about_me LIKE '%energie%'

Date: Tue, 28 Sep 2010 15:54:34 +0000

Error: 2006 - MySQL server has gone away

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

mySQL query error: UPDATE ibf_sessions SET search_thread_id=0,search_thread_time=0 WHERE id='10fdaf1ff04053d8ae9b2a7b1674fe62'

Date: Tue, 28 Sep 2010 17:40:18 +0000

Error: 2006 - MySQL server has gone away

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

mySQL query error: SELECT * FROM ibf_attachments WHERE attach_rel_module='post' AND ( attach_rel_id IN (4862909,4861240,4835371,4827410,4823071,4821993,4787180,4786797,4754775,4743513,4738093,4730907,4305512,4280276,4163006,3922018,3219133,2159439) )

The query is different, but it's always a search it seems. Do you think there is a problem with the search (I'm using Sphinx) ?

Thank you for your help ! :)

[-Seven-]

even i have the issue and most times the site also crashes when this happens :(

[AlexJ]

Well in my case, this happens on random basis but each day.

 Date: Thu, 23 Sep 2010 20:35:44 +0000

 Error: 2006 - MySQL server has gone away

 IP Address: 83.163.94.252 - /forums/index.php?app=core&module=task

mySQL query error: INSERT INTO task_logs (`log_title`,`log_date`,`log_ip`,`log_desc`) VALUES('Sitemap Generator',1285274144,'83.163.94.252','Task completed successfully, pings completed.')

 Date: Wed, 29 Sep 2010 01:02:56 +0000

 Error: 2006 - MySQL server has gone away

 IP Address: 66.249.66.134 - /forum/user/3614-sypher/page__f__129__unlockUserAgent__1

 mySQL query error: UPDATE ipb_members SET fb_lastsync=1285722176 WHERE member_id=3749

 Date: Wed, 29 Sep 2010 08:55:08 +0000

 Error: 2006 - MySQL server has gone away

 IP Address: 193.254.183.244 - /forum/rss/tutorials/

 mySQL query error: SELECT item_member_id, item_last_updated, item_last_saved FROM ipb_core_item_markers_storage WHERE item_last_saved < 1285743308 ORDER BY item_last_saved ASC LIMIT 0,50

[Collin S.]

MySQL has gone away almost always means that the mysql connection died.

http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

[cthree]

From link above...

You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB.

@OP:

Your my.cnf file isn't making a great deal of sense to me.

tmp_table_size=1024M

max_heap_table_size=1024M

max_user_connections=200

max_connections=200

interactive_timeout=200

wait_timeout=300

connect_timeout=300

thread_cache_size=256

key_buffer=4096M

join_buffer=2M

max_allowed_packet=32M

table_cache=4000

record_buffer=12M

sort_buffer_size=12M

read_buffer_size=12M

read_rnd_buffer_size=12M

max_connect_errors=10

thread_concurrency=16

myisam_sort_buffer_size=1024M

I don't see net_buffer_length. It defaults to 1MB. That's the space mysqld allocates per connection x 2 (one for the request, one for the result). Each connection can grow from net_buffer_length up to max_allowed_packet, 32MB in your case. That isn't a problem until you've got 200 connections running which results in 200 X 32MB or 6000MB of RAM used from these settings alone as configured. It will probably work most of the time but has FAIL in the batter.

Each connection also a 256KB thread stack (+50MB for 200 connections)

Each request that does a table scan allocates a read_buffer_size chunk to store the rows fetched from the database. It doesn't read rows one at a time, it reads them in chunks, processes them and then reads the next chunk. You have a 12MB read_buffer_size x 200 connections resulting in a potential allocation of up to 2400MB total from 200 connection all doing a SELECT resulting in a table scan.

You've also got max_heap_table_size set to 1GB. That's also per connection! potentially requiring 200+GB of RAM to satisfy in-memory temporary table management. Are you really going to be selecting 1GB worth of non-BLOB, non-TEXT rows in a query? Temp tables with a text or blob column will not be created in-memory but will be created on disk. I can't imagine you would every use anything near that. It won't consume memory if not used but again, the config is designed to fail under pressure.

from the docs: "Most requests that perform a sort allocate a sort buffer and zero to two temporary files depending on the result set size.

You are allocating a 12MB sort_buffer and as many as two temp tables per connection for most queries which have an ORDER BY or GROUP BY clause, ie most queries! That's another 2400MB for connections.

The rule of thumb MySQL uses to calculate per connection memory requirements is:

key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections

That doesn't include in-memory tmp_tables or the thread stack or the request and result buffers. The real calc is more like:

key_buffer + (net_buffer_length * 2 + max(read_buffer_size, read_rnd_buffer_size) + sort_buffer_size + thread_stack) * max_connection ++ any in-memory temp tables

In your case that runs out to a nominal mysqld ram requirement of about 10GB, mostly allocated to connections and most of it never used.

If you've got 6 or 8GB of RAM in your mysqld server it almost certainly will die under load and by load it doesn't need to be online users. background tasks can cause a good deal of load too.

My suggestion is to rationalize your mysql config. Start with connections. Don't allow more than your server can support and do that by reducing the amount of per-connection memory you are using. This apples to both InnoDB and MyISAM.

Try the following:

key_buffer = 4G
max_allowed_packet = 8M
net_buffer_length = 1M
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 1M
max_connections = 50
max_connect_errors = 100000
concurrent_insert = 2
connect_timeout = 30
interactive_timeout = 180
wait_timeout = 180
max_heap_table_size = 64M
tmp_table_size = 64M
thread_concurrency = 8

This config will reduce your per-connection memory use from ~26MB to ~6MB and reduce the total requirement for all connections to 50x6 from 200x26.The advice that says you can adjust key_buffer to 80% of your server's physical RAM is a lie. It should be 80% - per-connection memory requirements.

check max_used_connections in mysql status to see how many connections you need at one time, then add 20% for exceptional circumstances. You may be able to reduce many of the per-connection buffers even further but I suggest starting with the values I gave and tuning each one individually.

good luck.

[SecondSight]

Hello ! :)

Thank you for your help and information you posted. This will help me a lot. :)

Someone already told me about the fact I was allocating too much ressources and I was in the process of lowering the values in my.cnf. But I'm lacking understanding of all this, and I'm doing it very slowly... Apart from reading, I can only "try and see if it works". Anyway, I've learned a lot already, and I'm much much more interested in learning then having the job being done by someone else. :)


In fact, I have 16 GB RAM and the max_used_connections value is 103.


MySQLTuner gives me this :

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.91-community-log

[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster

[--] Data in MyISAM tables: 3G (Tables: 275)

[!!] Total fragmented tables: 27

-------- Performance Metrics -------------------------------------------------

[--] Up for: 3d 16h 2m 3s (27M q [86.316 qps], 1M conn, TX: 355B, RX: 41B)

[--] Reads / Writes: 43% / 57%

[--] Total buffers: 4.0G global + 30.2M per thread (200 max threads)

[OK] Maximum possible memory usage: 9.9G (63% of installed RAM)

[OK] Slow queries: 0% (143/27M)

[OK] Highest usage of available connections: 51% (103/200)

[OK] Key buffer size / total MyISAM indexes: 3.0G/1.9G

[OK] Key buffer hit rate: 99.9% (2B cached / 2M reads)

[OK] Query cache efficiency: 47.4% (6M cached / 14M selects)

[OK] Query cache prunes per day: 0

[OK] Sorts requiring temporary tables: 0% (836 temp sorts / 204K sorts)

[OK] Temporary tables created on disk: 12% (3K on disk / 32K total)

[OK] Thread cache hit rate: 99% (103 created / 1M connections)

[OK] Table cache hit rate: 42% (901 open / 2K opened)

[OK] Open file limit used: 28% (1K/4K)

[OK] Table locks acquired immediately: 95% (27M immediate / 29M locks)

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

And this is what Tuning-Primer returns :

Uptime = 3 days 16 hrs 4 min 44 sec

Avg. qps = 86

Total Questions = 27356897

Threads Connected = 1

Server has been running for over 48hrs.

It should be safe to follow these recommendations

To find out more information on how each of these

runtime variables effects performance visit:

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Visit http://www.mysql.com/products/enterprise/advisors.html

for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES

The slow query log is enabled.

Current long_query_time = 3 sec.

You have 143 out of 27356918 that take longer than 3 sec. to complete

Your long_query_time seems to be fine

BINARY UPDATE LOG

The binary update log is NOT enabled.

You will not be able to do point in time recovery

See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

WORKER THREADS

Current thread_cache_size = 256

Current threads_cached = 102

Current threads_per_sec = 0

Historic threads_per_sec = 0

Your thread_cache_size is fine

MAX CONNECTIONS

Current max_connections = 200

Current threads_connected = 1

Historic max_used_connections = 103

The number of used connections is 51% of the configured maximum.

Your max_connections variable seems to be fine.

No InnoDB Support Enabled!

MEMORY USAGE

Max Memory Ever Allocated : 6.55 G

Configured Max Per-thread Buffers : 5.90 G

Configured Max Global Buffers : 3.50 G

Configured Max Memory Limit : 9.41 G

Physical Memory : 15.70 G

Max memory limit seem to be within acceptable norms

KEY BUFFER

Current MyISAM index space = 1.90 G

Current key_buffer_size = 3.00 G

Key cache miss rate is 1 : 974

Key buffer free ratio = 19 %

Your key_buffer_size seems to be too high.

Perhaps you can use these resources elsewhere

QUERY CACHE

Query cache is enabled

Current query_cache_size = 512 M

Current query_cache_used = 234 M

Current query_cache_limit = 16 M

Current Query cache Memory fill ratio = 45.72 %

Current query_cache_min_res_unit = 2 K

MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS

Current sort_buffer_size = 10 M

Current read_rnd_buffer_size = 10 M

Sort buffer seems to be fine

JOINS

Current join_buffer_size = 2.00 M

You have had 140 queries where a join could not use an index properly

You should enable "log-queries-not-using-indexes"

Then look for non indexed joins in the slow query log.

If you are unable to optimize your queries you may want to increase your

join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when

ANY joins not using indexes are found.

OPEN FILES LIMIT

Current open_files_limit = 4210 files

The open_files_limit should typically be set to at least 2x-3x

that of table_cache if you have heavy MyISAM usage.

Your open_files_limit value seems to be fine

TABLE CACHE

Current table_cache value = 2000 tables

You have a total of 292 tables

You have 903 open tables.

The table_cache value seems to be fine

TEMP TABLES

Current max_heap_table_size = 512 M

Current tmp_table_size = 512 M

Of 28261 temp tables, 12% were created on disk

Created disk tmp tables ratio seems fine

TABLE SCANS

Current read_buffer_size = 8 M

Current table scan ratio = 106 : 1

read_buffer_size seems to be fine

TABLE LOCKING

Current Lock Wait ratio = 1 : 22

You may benefit from selective use of InnoDB.

If you have a high concurrency of inserts on Dynamic row-length tables

consider setting 'concurrent_insert=2'.

[SecondSight]

Up to now, my my.cnf looks like this :

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-slow-queries = /var/log/mysql-slow.log

long_query_time = 3

#log-long-format

#log-queries-not-using-indexes

log-warnings=2

skip-locking

skip-innodb

query_cache_limit=16M

query_cache_size=512M

query_cache_type=1

query_cache_min_res_unit=2K

tmp_table_size=512M

max_heap_table_size=512M

max_user_connections=200

max_connections=200

interactive_timeout=400

wait_timeout=1000

connect_timeout=600

thread_cache_size=256

key_buffer=3072M

join_buffer=2M

max_allowed_packet=16M

table_cache=2000

record_buffer=12M

sort_buffer_size=10M

read_buffer_size=8M

read_rnd_buffer_size=10M

max_connect_errors=10

thread_concurrency=16

myisam_sort_buffer_size=1024M

low_priority_updates=1

server-id=1

Since I'm not working today I'm going to modify it's content and see how the server is doing. :)

Thank you, :)

[Zhana]

I have the same issue and getting tons of these errors ( in logs) on daily basis.

[Gary.]

If your getting Mysql server has gone away, But only on a tempory basis then 99.9% your packet sizes are to low:

Just add or increase the following:

max_allowed_packet=32M

If yours is already 32M then just increase there around 16M per 24 hours and moniter it, But dont forget to restart your mysql server once the change has completed.

/etc/init.d/mysql restart

And please tell me what is key_buffer ?

Correct me if I'm wrong which I highly doubt ( Not being big headed ) but the value your looking for is:

key_buffer_size

On linux you do NOT need the below:

socket=/var/lib/mysql/mysql.sock This is already default so no point adding it.

thread_concurrency = Does not exist on Linux - Not sure where he got the idea from...

Second Light:

I don't see the point in this:

max_user_connections=200
max_connections=200

why ? Well your giving your clients all the mysql resource as there both the same values so in theoy max_user is pointless.. You should atleast lower the max_user value to leave mysql some room.

max_user_connections=200

max_connections=300

query_cache_size=512M

I never seen anything so high in my entire 7 years of playing with MySql.

Look your values up on mysql.

Increasing the query_cache size over 128M may reduce performance

In your case, Your standing it up against a wall and shooting it.

You should appreciate what people say in here as there only trying to help, I'm not coming across in a bad way but you should do is head over to mysql documentation as your getting told to put incorrect values in which will not only fix anything but give incorrect reports.

[SecondSight]

Hello ,

You should appreciate what people say in here as there only trying to help

I do. :)

I also read documentation, but since I'm lacking experience, it doesn't help very much.
I'm taking the time to try the pieces of advice given to me and see if it improves things.

[u]query_cache_size=512M[/u]

I never seen anything so high in my entire 7 years of playing with MySql.

My Current query_cache_used value is 337M...
So, you mean it's better to have it set at 128 and being rebuit constantly rather than fixed at a value ajusted to Current query_cache_used. So it means the cache content becomes useless after some time ?

Thank you ! :)

[AlexJ]

Well I had query cache size set to 128M but then both phpmyadmin and tunning scripts where telling me to increase it and hence I increased it.

Do you really think forum is using packet size of 32M? I am not sure but how can I find max packet size usage? Any possible way to do it?

[Gary.]

Do you really think forum is using packet size of 32M? I am not sure but how can I find max packet size usage? Any possible way to do it?

login to ssh and run:

nano /etc/my.cnf

find:

max_allowed_packet=

If you don't have the line, Then add it:

max_allowed_packet=32M

Secondlight

query_cache_size you mean ?

Now this can be high depending on the cache limit, And yours is set to 16M, I would suggest query_cache_limit=2M 16 is extremely high.

[AlexJ]

login to ssh and run:

find:

If you don't have the line, Then add it:

May be I was not clear in my last post. Let me rephrase:

How can I know packet size? At present I have max_allowed_packet = 4M but I want to know what is the max packet size of queries. Sure I had set max allowed to 4M since last few months but I have no clue how much is being used.

Is there any specific command by which I can know packet size?

[Gary.]

The only other way is to remote assistance to the box but... I can tell you..

max_allowed_packet = 4M

would stuggle alot, Atleast 16M should be used. 32M is reasonable and 48M will do the job just fine.