Error: 2006 - MySQL server has gone away

David.. · February 10, 2015

Hello!

I have been getting this error for quite a while and I'm not really sure how to fix it.

The error usually comes up if someone searches something or clicks the topic tags.

What can I do to stop this error?

I'm running CentOS 6.6 with MySQL 5.1.73 & PHP 5.3.

Thank you!

Ahmad E. · February 10, 2015

Go to ACP->Stats&Logs->SQL Error logs

And post the latest error here.

RevengeFNF · February 10, 2015

Can you post here your my.cnf?

ASTRAPI · February 10, 2015

If you have many posts you can use Sphinx to avoid mysql overload.....(more than 200.000 posts)

As Revenge FNF recommend post here the my.cnf content and post here also the output of mysqltuner script.

Also you can upgrade to newest versions of Mysql and php as IPB is working great with the latest versions of both :)

David.. · February 10, 2015

Go to ACP->Stats&Logs->SQL Error logs

And post the latest error here.

Latest SQL error log

Date: Mon, 09 Feb 2015 22:43:36 +0000 Error: 2006 - MySQL server has gone away IP Address: xxx.1xx.1x5.5x - /tags/forums/General/ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- mySQL query error: UPDATE ipb_sessions SET search_thread_id=0,search_thread_time=0 WHERE id='ed62e3bdc80d686cx183dba110a0c4a5' .--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------. | File | Function | Line No. | |----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------| | admin/sources/classes/search/controller.php | [db_main_mysql].update | 700 | '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------' | admin/sources/classes/search/controller.php | [IPSSearch]._endSession | 560 | '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------' | admin/applications/core/modules_public/search/search.php | [IPSSearch].search | 671 | '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------' | admin/applications/core/modules_public/search/search.php | [public_core_search_search].searchResults | 173 | '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------' | admin/sources/base/ipsController.php | [public_core_search_search].doExecute | 306 | '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'

Can you post here your my.cnf?

If you have many posts you can use Sphinx to avoid mysql overload.....(more than 200.000 posts)

As Revenge FNF recommend post here the my.cnf content and post here also the output of mysqltuner script.

Also you can upgrade to newest versions of Mysql and php as IPB is working great with the latest versions of both

Don't have that many posts.

my.cnf is like this:

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
wait_timeout = 90
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I have added the wait_timeout = 90 myself.

RevengeFNF · February 10, 2015

First thing, you have a default my.cnf. All the value are the default from mysql 5.1.

You should really optimize your mysql.

If you post here the result of mysqltuner and/or tuning primer, we can help you with that.

Also, Mysql 5.1 is old. You should update your mysql to 5.6 or even change it to MariaDB.

David.. · February 10, 2015

First thing, you have a default my.cnf. All the value are the default from mysql 5.1.
You should really optimize your mysql.
If you post here the result of mysqltuner and/or tuning primer, we can help you with that.
Also, Mysql 5.1 is old. You should update your mysql to 5.6 or even change it to MariaDB.

I've had someone from here set the server up for me. I guess they didn't do any optimizations or even update to the latest PHP/MySQL. :/

How can I do what needs to be done with mysqltuner?

RevengeFNF · February 10, 2015

Enter in your server with ssh and do this:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

This will download the mysqltuner to the folder you are.

Then do this:

perl mysqltuner.pl

Post here the result.

If you can, do also this:

wget https://launchpadlibrarian.net/78745738/tuning-primer.sh

Then:

chmod u+x tuning-primer.sh

Then:

bash tuning-primer.sh

Post here the results.

David.. · February 10, 2015

Post here the results.

First command:

 >>  MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 244M (Tables: 234)
[--] Data in InnoDB tables: 8M (Tables: 361)
[!!] Total fragmented tables: 399

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 21h 39m 18s (5M q [31.994 qps], 397K conn, TX: 222B, RX: 1B)
[--] Reads / Writes: 81% / 19%
[--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 449.2M (5% of installed RAM)
[OK] Slow queries: 0% (14/5M)
[OK] Highest usage of available connections: 45% (68/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/87.9M
[OK] Key buffer hit rate: 99.4% (320M cached / 1M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (53 temp sorts / 238K sorts)
[!!] Joins performed without indexes: 31684
[OK] Temporary tables created on disk: 21% (32K on disk / 150K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 48K opened)
[OK] Open file limit used: 10% (103/1K)
[OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)
[!!] InnoDB  buffer pool / data size: 8.0M/8.8M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Set thread_cache_size to 4 as a starting value
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    thread_cache_size (start at 4)
    table_open_cache (> 64)
    innodb_buffer_pool_size (>= 8M)

Second command:

        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.1.73 x86_64

Uptime = 1 days 21 hrs 40 min 43 sec
Avg. qps = 31
Total Questions = 5261451
Threads Connected = 1

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.1/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 NOT enabled.
Current long_query_time = 10.000000 sec.
You have 14 out of 5261472 that take longer than 10.000000 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.1/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 0
Current threads_cached = 0
Current threads_per_sec = 4
Historic threads_per_sec = 2
Threads created per/sec are overrunning threads cached
You should raise thread_cache_size

MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 2
Historic max_used_connections = 68
The number of used connections is 45% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 9 M
Current InnoDB data space = 8 M
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 8 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 204 M
Configured Max Per-thread Buffers : 415 M
Configured Max Global Buffers : 17 M
Configured Max Memory Limit : 433 M
Physical Memory : 7.67 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 87 M
Current key_buffer_size = 7 M
Key cache miss rate is 1 : 176
Key buffer free ratio = 80 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 31716 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 = 1024 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_open_cache = 64 tables
Current table_definition_cache = 256 tables
You have a total of 618 tables
You have 64 open tables.
Current table_cache hit rate is 0%
, while 100% of your table cache is in use
You should probably increase your table_cache
You should probably increase your table_definition_cache value.

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 150978 temp tables, 17% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 481 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 1520
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

RevengeFNF · February 10, 2015

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
wait_timeout = 30
interactive_timeout = 50
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections=150

#MyISAM
key_buffer_size = 150M

#InnoDB
innodb_file_per_table
innodb_buffer_pool_size = 50M

#Thread Cache
thread_cache_size = 8

#Table cache
table_open_cache = 1000
table_definition_cache = 650
open_files_limit = 2000

#Buffers
join_buffer_size =2M
read_buffer_size=128K
sort_buffer_size=256K

#Query Cache
query_cache_size=64M
query_cache_limit =6M
query_cache_min_res_unit=1k

#Temporary Tables
tmp_table_size=256M
max_heap_table_size=256M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Try this in your my.cnf and restart your Mysql: service mysqld restart

Tomorrow +/- at this time, post here again the results of mysqltuner and tuning primer.

This is just to optimize your current mysql.

My recommendation for the future is for you to update to Mysql 5.6 or MariaDB 10, change your tables to InnoDB Engine and use Sphinx for Search.

Also, are you using any Opcode Cache, like Zend Opcache?

David.. · February 10, 2015

 
Try this in your my.cnf and restart your Mysql: service mysqld restart
Tomorrow +/- at this time, post here again the results of mysqltuner and tuning primer.

This is just to optimize your current mysql.
My suggestion for the future is for you to update to Mysql 5.6 or MariaDB 10, change your tables to InnoDB Engine and use Sphinx for Search.
Also, are you using any Opcode Cache, like Zend Opcache?

Thank you for the help!

I have just edited my.cnf file and added everything inside the code tags and replaced everything else which was stock and restarted mysqld.

I will see if I get the same errors tomorrow.

I am using memcached and gave it 512MB RAM. Is that okay?

RevengeFNF · February 10, 2015

Its ok, but ipb 3.4 wont use so many memory.

IPS 4 will use a lot more.

You did not tell if you are using an opcode cache.

David.. · February 10, 2015

Its ok, but ipb 3.4 wont use so many memory.
IPS 4 will use a lot more.
You did not tell if you are using an opcode cache.

Not sure if I am...

How can I check?

Sorry to bother you with all these questions...

RevengeFNF · February 10, 2015

Download this php file: https://raw.githubusercontent.com/amnuts/opcache-gui/master/index.php

Then run it with your browser.

David.. · February 10, 2015

Download this php file: https://raw.githubusercontent.com/amnuts/opcache-gui/master/index.php
Then run it with your browser.

I uploaded it to my httpdocs folder and opened it using my browser but it displays an empty page.

It works fine on localhost though?

RevengeFNF · February 10, 2015

Upload it to the root of your board and check if it works.

David.. · February 10, 2015

Upload it to the root of your board and check if it works.

I've tried uploading it in multiple places. Still won't read it. Just a completely blank page. Nothing in View Source also.

RevengeFNF · February 10, 2015

Thats very strange. I just tested it on my server and it worked fine.

Ok, another way. Go to the ACP of your board, then, Support -> Diagnostics -> Overview

In the "PHP Version" line, click in PHP INFO. Search for "Zend Opcache".

If you have it, it will say something like:

Zend OPcache

Opcode Caching	Up and Running
Optimization	Enabled
Startup	OK
Shared memory model	mmap
Cache hits	23489446
Cache misses	758
Used memory	66489976
Free memory	67727752
Wasted memory
Interned Strings Used memory	6531360
Interned Strings Free memory	1857248
Cached scripts	756
Cached keys	886
Max keys	7963
OOM restarts
Hash keys restarts
Manual restarts

David.. · February 11, 2015

Thats very strange. I just tested it on my server and it worked fine.
Ok, another way. Go to the ACP of your board, then, Support -> Diagnostics -> Overview
In the "PHP Version" line, click in PHP INFO. Search for "Zend Opcache".
If you have it, it will say something like:
Zend OPcache
Opcode Caching Up and Running
Optimization Enabled
Startup OK
Shared memory model mmap
Cache hits 23489446
Cache misses 758
Used memory 66489976
Free memory 67727752
Wasted memory
Interned Strings Used memory 6531360
Interned Strings Free memory 1857248
Cached scripts 756
Cached keys 886
Max keys 7963
OOM restarts
Hash keys restarts
Manual restarts

I have nothing related to Opcache... I used CTRL+F and searched manually, nothing.

RevengeFNF · February 11, 2015

You don't have the opcache then.

If you want to install and enable it, because its really that good:

Download it

wget http://pecl.php.net/get/zendopcache-7.0.4.tgz

Untar it:

tar xvf zendopcache-7.0.4.tgz

Enter in the folder:

cd zendopcache-7.0.4

Now lets run phpize:

phpize

PS: if phpize don't work, try with /usr/bin/phpize

Now configure:

./configure

Now Make:

make

Finally:

make install

Zend Opcache is installed. Now you just need to enable it and configure on php.ini.

Confirm first if you have opcache.so file inside /usr/lib64/php/modules/

So edit php.ini and after the [php ] add:

zend_extension=/usr/lib64/php/modules/opcache.so
opcache.enable=1
opcache.memory_consumption=128
opcache.interned_strings_buffer=8
opcache.max_accelerated_files=4000

Restart httpd service.

David.. · February 11, 2015

You don't have the opcache then.
If you want to install and enable it, because its really that good:

Should I install it? Will it work with memcached?

David.. · February 11, 2015

I don't even have phpize installed. Command doesn't exist.

RevengeFNF · February 11, 2015

Zend Opcache and Memcache are different things.

Memcache is a User Cache. Ok its a litle more than that, but you get it.

Zend Opcache basically compiles your php file and stores it in the Ram. This way, your server do not need to compile the php files everytime someone is using your site.

David.. · February 11, 2015

Zend Opcache and Memcache are different things.

Memcache is a User Cache. Ok its a litle more than that, but you get it.

Zend Opcache basically compiles your php file and stores it in the Ram. This way, your server do not need to compile the php files everytime someone is using your site.

Yeah, I just read about it online and there were some really good reviews and I proceeded to install it but phpize not found.

RevengeFNF · February 11, 2015

I don't even have phpize installed. Command doesn't exist.

If i remember well, if you install php-devel you will get phpize.

Run this:

yum --enablerepo=remi,remi-php53 install php-devel

PS: So you can understand the command above, the --enablerepo=remi tells the yum to search in the remi repository. The remi-php53 tell's it to search only packages compatible with php 5.3.

Invision Community 4: SEO, prepare for v5 and dormant account notifications

Invision Community 5: Beta testing and latest updates

Invision Community 4: A more professional report center

Invision Community 5: A video walkthrough creating a custom theme and homepage

Error: 2006 - MySQL server has gone away

Recommended Posts

David..

Ahmad E.

RevengeFNF

ASTRAPI

David..

RevengeFNF

David..

RevengeFNF

David..

RevengeFNF

David..

RevengeFNF

David..

RevengeFNF

David..

RevengeFNF

David..

RevengeFNF

Zend OPcache

David..

Zend OPcache

RevengeFNF

David..

David..

RevengeFNF

David..

RevengeFNF

Archived

Recently Browsing 0 members

More