Mysql has gone away: Getting tons of these!

**Mark.B** · Thu 24 Jul '14, 1:56pm

This is a server error....you should contact your hosts.

**kehindelawal** · Thu 24 Jul '14, 3:13pm

Originally posted by Mark.B

This is a server error....you should contact your hosts.

Thanks a lot. Are the errors due to wait_timeout?

**Wayne Luke** · Thu 24 Jul '14, 4:11pm

They can be... wait_timeout should be at least 120 seconds. The default is 28800 seconds but many hosts set it lower thinking this will save resources. It doesn't.

It can also be caused by the packet being too large. MySQL versions prior to 5.6.6 defaulted the max_packet_allowed to 1 MB. This isn't large enough for dynamic applications. MySQL 5.6.6 increases this to 4 MB which is a better setting. 8 MB would be optimal though. The mysql command line client overrides the default to 16 MB.

Having a packet larger than 1 MB is the most likely cause of this error in vBulletin. Most pages have a query list that only takes half a second or less.

**kehindelawal** · Thu 24 Jul '14, 7:43pm

Originally posted by Wayne Luke

They can be... wait_timeout should be at least 120 seconds. The default is 28800 seconds but many hosts set it lower thinking this will save resources. It doesn't.

It can also be caused by the packet being too large. MySQL versions prior to 5.6.6 defaulted the max_packet_allowed to 1 MB. This isn't large enough for dynamic applications. MySQL 5.6.6 increases this to 4 MB which is a better setting. 8 MB would be optimal though. The mysql command line client overrides the default to 16 MB.

Having a packet larger than 1 MB is the most likely cause of this error in vBulletin. Most pages have a query list that only takes half a second or less.

Again thanks a lot Wayne. i spoke to my host: inmotion. They ran a db repair but the error persisted. He later said i need to upgrade from shared server to Dedicated or VPS. i just wonder that is shared hosting not suitable enough for a small forum?

**Zachery** · Thu 24 Jul '14, 11:37pm

A database repair fixes nothing if the tables are not crashed The error you got indicated that the ENTIRE server's MySQL services were down. Not just yours. If only your resources ran out then you'd get a very specific error about your MySQL user using too many connections.

I'd find a less shady webhost who is just trying to upsell you. The first thing I'd have done was move you to another machine, personally.

**kehindelawal** · Fri 25 Jul '14, 11:02am

i was able to pick those from myphpadmin on my webhost, can someone pls rate how bad their configuration is. need to decide if i should just move on

Code:

 [TABLE="class: data, width: 0"]
 	 		[TR]
 			Issue 			Recommendation 		[/TR]
 	 	 		[TR="class: linkElem noclick even"]
 			[TD]Suboptimal caching method.[/TD]
 			[TD]You are using the MySQL Query cache with a fairly high traffic database. It might be worth considering to use memcached instead of the MySQL Query cache, especially if you have multiple slaves.[/TD]
 		[/TR]
 		[TR="class: linkElem noclick odd"]
 			[TD]The query cache is considerably fragmented.[/TD]
 			[TD]Severe fragmentation is likely to (further) increase Qcache_lowmem_prunes. This might be caused by many Query cache low memory prunes due to query_cache_size being too small. For a immediate but short lived fix you can flush the query cache (might lock the query cache for a long time). Carefully adjusting query_cache_min_res_unit to a lower value might help too, e.g. you can set it to the average size of your queries in the cache using this formula: (query_cache_size - qcache_free_memory) / qcache_queries_in_cache[/TD]
 		[/TR]
 		[TR="class: linkElem noclick even"]
 			[TD]Cached queries are removed due to low query cache memory from the query cache.[/TD]
 			[TD]You might want to increase query_cache_size, however keep in mind that the overhead of maintaining the cache is likely to increase with its size, so do this in small increments and monitor the results.[/TD]
 		[/TR]
 		[TR="class: linkElem noclick odd"]
 			[TD]The query cache size is above 128 MiB. Big query caches may cause significant overhead that is required to maintain the cache.[/TD]
 			[TD]Depending on your environment, it might be performance increasing to reduce this value.[/TD]
 		[/TR]
 		[TR="class: linkElem noclick even"]
 			[TD]The max size of the result set in the query cache is the default of 1 MiB.[/TD]
 			[TD]Changing query_cache_limit (usually by increasing) may increase efficiency. This variable determines the maximum size a query result may have to be inserted into the query cache. If there are many query results above 1 MiB that are well cacheable (many reads, little writes) then increasing query_cache_limit will increase efficiency. Whereas in the case of many query results being above 1 MiB that are not very well cacheable (often invalidated due to table updates) increasing query_cache_limit might reduce efficiency.[/TD]
 		[/TR]
 		[TR="class: linkElem noclick odd"]
 			[TD]There are lots of rows being sorted.[/TD]
 			[TD]While there is nothing wrong with a high amount of row sorting, you might want to make sure that the queries which require a lot of sorting use indexed columns in the ORDER BY clause, as this will result in much faster sorting[/TD]
 		[/TR]
 		[TR="class: linkElem noclick even"]
 			[TD]There are too many joins without indexes.[/TD]
 			[TD]This means that joins are doing full table scans. Adding indexes for the columns being used in the join conditions will greatly speed up table joins[/TD]
 		[/TR]
 		[TR="class: linkElem noclick odd"]
 			[TD]The rate of reading the first index entry is high.[/TD]
 			[TD]This usually indicates frequent full index scans. Full index scans are faster than table scans but require lots of CPU cycles in big tables, if those tables that have or had high volumes of UPDATEs and DELETEs, running 'OPTIMIZE TABLE' might reduce the amount of and/or speed up full index scans. Other than that full index scans can only be reduced by rewriting queries.[/TD]
 		[/TR]
 		[TR="class: linkElem noclick even"]
 			[TD]The rate of reading data from a fixed position is high.[/TD]
 			[TD]This indicates that many queries need to sort results and/or do a full table scan, including join queries that do not use indexes. Add indexes where applicable.[/TD]
 		[/TR]
 		[TR="class: linkElem noclick odd"]
 			[TD]The rate of reading the next table row is high.[/TD]
 			[TD]This indicates that many queries are doing full table scans. Add indexes where applicable.[/TD]
 		[/TR]
 		[TR="class: linkElem noclick even"]
 			[TD]Many temporary tables are being written to disk instead of being kept in memory.[/TD]
 			[TD]Increasing max_heap_table_size and tmp_table_size might help. However some temporary tables are always being written to disk, independent of the value of these variables. To eliminate these you will have to rewrite your queries to avoid those conditions (Within a temporary table: Presence of a BLOB or TEXT column or presence of a column bigger than 512 bytes) as mentioned in the beginning of an Article by the Pythian Group[/TD]
 		[/TR]
 		[TR="class: linkElem noclick odd"]
 			[TD]MyISAM key buffer (index cache) % used is low.[/TD]
 			[TD]You may need to decrease the size of key_buffer_size, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.[/TD]
 		[/TR]
 		[TR="class: linkElem noclick even"]
 			[TD]The rate of opening tables is high.[/TD]
 			[TD]Opening tables requires disk I/O which is costly. Increasing table_open_cache might avoid this.[/TD]
 		[/TR]
 		[TR="class: linkElem noclick odd"]
 			[TD]Too many table locks were not granted immediately.[/TD]
 			[TD]Optimize queries and/or use InnoDB to reduce lock wait.[/TD]
 		[/TR]
 		[TR="class: linkElem noclick even"]
 			[TD]Too many connections are aborted.[/TD]
 			[TD]Connections are usually aborted when they cannot be authorized. This article might help you track down the source.[/TD]
 		[/TR]
 		[TR="class: linkElem noclick odd"]
 			[TD]Too many clients are aborted.[/TD]
 			[TD]Clients are usually aborted when they did not close their connection to MySQL properly. This can be due to network issues or code not closing a database handler properly. Check your network and code.[/TD]
 		[/TR]
 	 [/TABLE]

**Mark.B** · Fri 25 Jul '14, 11:48am

Just find a better host. You will be fighting endless battles you cannot win otherwise.

Mysql has gone away: Getting tons of these!

Mysql has gone away: Getting tons of these!

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Related Topics