Jump to content

Error: 2006 - MySQL server has gone away


Recommended Posts

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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? 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

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

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

 

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?

Link to comment
Share on other sites

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 CachingUp and Running
OptimizationEnabled
StartupOK
Shared memory modelmmap
Cache hits23489446
Cache misses758
Used memory66489976
Free memory67727752
Wasted memory
Interned Strings Used memory6531360
Interned Strings Free memory1857248
Cached scripts756
Cached keys886
Max keys7963
OOM restarts
Hash keys restarts
Manual restarts
Link to comment
Share on other sites

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 CachingUp and Running
OptimizationEnabled
StartupOK
Shared memory modelmmap
Cache hits23489446
Cache misses758
Used memory66489976
Free memory67727752
Wasted memory 
Interned Strings Used memory6531360
Interned Strings Free memory1857248
Cached scripts756
Cached keys886
Max keys7963
OOM restarts 
Hash keys restarts 
Manual restarts 

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

Link to comment
Share on other sites

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.

 

 

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...