A solution for big boards ?

[Bono]

28800 seems too big, isn't it ? I've tried 900 and it didn't change anything.

Here are some of the errors I got last friday:

Since the I have MySQL server has one away errors at different times, I think my board itself has nothing to do with it.

I alos had a look a the Sphinx logs and in fact it's doing very well...

The slow queries log file for the same day is here :

slowquerieslog.txt

Can you tell me what you think of it ?

Thank you !

That is less than default value, and just change it to that and restart mysqld and see if it helps. I do not use sphinx but I had same error until I changed that value, and with long timeout you will not degrade performance of your server.

[thompsone]

28800 is most likely your default setting anyway. I think the error message is deceiving. More than likely the queries that are experiencing this problem are failing for a more basic reason like they are too large for mysql (large packets being sent) based on your configuration of mysql. Here's something that may help but for the most part I think this is something IPS will have to address based on the way they are sending requests to mysql.

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

My logs are a bit different but I can at least confirm it's happening.

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

 Date: Sun, 31 Oct 2010 05:38:49 +0000

 Error: 1317 - Query execution was interrupted

 IP Address: 190.178.119.230 - /index.php?app=core&module=search&do=search&fromMainBar=1

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

 mySQL query error: SELECT author_id, topic_id FROM IVBposts WHERE queued=0 AND author_id=34421 AND topic_id IN(35861,59813,44199,35781,34994,41868,57824,34228,29136,55656,51485,28436,55102,53242,49891,31265,34343,47592,37391,33627,34073,40665,37888,31469,32167)

 .--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------.

 | File                                                                       | Function                                                                      | Line No.          |

 |----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------|

 | admin/sources/classes/search/controller.php                                | [search_format_forums].processResults                                         | 504               |

 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'

 | admin/applications/core/modules_public/search/search.php                   | [IPSSearch].search                                                            | 339               |

 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'

 | admin/applications/core/modules_public/search/search.php                   | [public_core_search_search].searchResults                                     | 160               |

 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'

 | admin/sources/base/ipsController.php                                       | [public_core_search_search].doExecute                                         | 296               |

 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'

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

 Date: Sun, 31 Oct 2010 18:09:07 +0000

 Error: 2006 - MySQL server has gone away

 IP Address: 190.178.75.135 - /index.php?app=core&module=search&do=search&fromMainBar=1

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

 mySQL query error: SELECT p.*,t.*,m.member_id, m.members_display_name, m.members_seo_name,cca.*,ccb.cache_content as cache_content_sig, ccb.cache_updated as cache_updated_sig FROM IVBposts p  LEFT JOIN IVBtopics t ON ( t.tid=p.topic_id ) 

 LEFT JOIN IVBmembers m ON ( m.member_id=p.author_id ) 

 LEFT JOIN IVBcontent_cache_posts cca ON ( cca.cache_content_id=p.pid ) 

 LEFT JOIN IVBcontent_cache_sigs ccb ON ( ccb.cache_content_id=p.author_id )   WHERE p.pid IN( 445086,308541,435812,429643,424523,342304,415459,409668,59777,403444,397283,262448,31828,359965,331109,359662,341167,348685,337870,329559,331346,329238,307526,312197,312195)

 .--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------.

 | File                                                                       | Function                                                                      | Line No.          |

 |----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------|

 | admin/sources/classes/search/controller.php                                | [search_format_forums].processResults                                         | 504               |

 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'

 | admin/applications/core/modules_public/search/search.php                   | [IPSSearch].search                                                            | 339               |

 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'

 | admin/applications/core/modules_public/search/search.php                   | [public_core_search_search].searchResults                                     | 160               |

 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'

 | admin/sources/base/ipsController.php                                       | [public_core_search_search].doExecute                                         | 296               |

 '----------------------------------------------------------------------------+-------------------------------------------------------------------------------+-------------------'

What I find interesting is that both our logs show the mysql "gone away" error on only search related queries.

If you're comfortable with changing your my.conf file and setting the parameter: --log-warnings=2
You should start generating error logs that might help understand why the error is reporting. I suspect though it's going to be on the IPS application side of things.

[surinp3]

/index.php?app=core&module=search&do=search&fromMainBar=1 is the error I have too.

All my errors are now just like that.

What could that be?

[cthree]

8GB is not too big, mine is over 40GB and works just fine.

The problem is definitely related to search as the script pulling in search results is failing. That could mean a number of things. First, rebuild your sphinx indexes. Basically it's failing to gather all of the posts in the search results before mysql is dropping the connection. The second failure is when IPB tried to cache those results. I would guess it's one of two things:

1) It might be a wait_timeout issue. Here is what I've got in my.cnf related to that:

max_connections = 150
max_connect_errors = 100000
concurrent_insert = 2
connect_timeout = 30
interactive_timeout = 180
wait_timeout = 180

180 means 180 seconds or 3 minutes. Any query which runs longer than that will be killed by the server. interactive_timeout is when you are using the mysql CLI tools, it doesn't apply to IPB or php scripts. a value of 20000+ is not serving any purpose except to run out-of-control queries essentially forever, blocking that connection slot until it's done.

2) the second problem may be related to the number of connections you configure. You need enough to make sure there is a health pool available but each connection eats a large chunk of mysql server RAM. How much depends on other mysql settings but it can be from 500K to several megabytes. 200 connections * 5MB per connection is 1GB of server RAM which takes away from the buffer pool. Don't forget that when you have in-memory tmp tables of up to 64MB in size, that's PER CONNECTION potentially.

I suggest you reduce your buffer pool down to something like 6GB. It will only marginally slow down your site if at all and it will ensure you have enough RAM left for all the connection overhead. With that setting changed in my.cnf you can examine the total amount of RAM the mysqld process is consuming. In your PS listing it is the RSS size value (resident set size or how much stuff is resident in memory).

If after making the change you find mysqld (at peak load) consuming a total of 12GB (or something) you will know that 12GB minus the 6GB buffer pool is 6GB of overhead divided by # of connections (say 200) you get 30MB per connection. Now take max_connections, multiply by 30MB and subtract from 80% of total server RAM to get the largest innodb buffer pool you can support.

It is possible that search creates a large in-memory tmp table to gather and sort results and that may trip a safety in mysqld on RAM pressure, dropping you connection.

Just a couple of ideas to play with, I don't guarantee it's either of these but it's a start.

Here is my complete my.cnf (CENTOS 5.4, 4 cores, 20GB, percona server, 80GB Fusion ioDrive):

-----
[mysql]
prompt='mysql [\h] {\u} (\d) > '

[mysqld_safe]
nice = -15

[client]
socket = /var/lib/mysql/mysql.sock

[mysqld]
#
# To disable the network socket and allow unix socket connections only,
# uncomment this.
#
#skip-networking

user = mysql
character_set_server = utf8
collation_server = utf8_general_ci
lower_case_table_names = 1
old_passwords = 1
default-time-zone='+0:00'

## Files
back_log = 300
open-files-limit = 8192
open-files = 1024
port = 3306
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
tmpdir = /var/lib/mysql/tmp/
datadir = /var/lib/mysql/data
skip-external-locking
skip-name-resolve

## Logging
log_bin = /var/log/mysql/bin-logs/mysql-bin
#relay_log = /var/log/mysql/bin-logs/mysql-relay
log-error = /var/log/mysql/mysql-error.log
log_warnings
log_slow_queries = /var/log/mysql/mysql-slow.log
log_queries_not_using_indexes
long_query_time = 10
max_binlog_size = 1024M
binlog_cache_size = 1M
#expire_logs_days = 10
log-slave-updates = 0

## Buffers and Connections
key_buffer = 64M
max_allowed_packet = 8M
thread_cache_size = 64
table_open_cache = 2048
sort_buffer_size = 8M
join_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M

query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 1M

max_connections = 150
max_connect_errors = 100000
concurrent_insert = 2
connect_timeout = 30
interactive_timeout = 180
wait_timeout = 180


## Default Table Settings
default_table_type = INNODB
#sql_mode = NO_AUTO_CREATE_USER

## Alt settings
max_heap_table_size = 64M
bulk_insert_buffer_size = 32M
tmp_table_size = 64M

## Number of CPU's*2 for thread_concurrency
thread_concurrency = 8

## Replication
server_id = 162
#read_only
slave_net_timeout = 600
#slave-skip-errors=
# Uncomment this to start mysql without slave io
skip-slave-start


## MyISAM Engine
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 2M
myisam_repair_threads = 4
myisam_recover = BACKUP

## InnoDB Engine

innodb_file_format = barracuda
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_file_size = 64M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1

innodb_buffer_pool_size = 12G
innodb_additional_mem_pool_size = 4M
innodb_max_dirty_pages_pct = 90
innodb_table_locks = 0
innodb_lock_wait_timeout = 50
#innodb_file_io_threads = 8
innodb_thread_concurrency = 8
#innodb_commit_concurrency = 4

innodb_status_file
#innodb_file_per_table

#InnoDB flushing method - only use O_DIRECT on non-SAN storage
innodb_flush_method = O_DIRECT

#InnoDB I/O Control - 200 default, more disks = more io_capacity.
#Setting only applicable to MySQL 5.1.38 or greater
innodb_io_capacity = 800

innodb_support_xa = 0
sync_binlog = 10

## TX Isolation
transaction-isolation = REPEATABLE-READ

[mysqldump]
quick
quote-names
max_allowed_packet = 128M

-----

top output (now) looks like:

-----
top - 09:41:54 up 118 days, 18:47, 1 user, load average: 0.22, 0.20, 0.18
Tasks: 122 total, 1 running, 121 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.3%us, 0.7%sy, 0.0%ni, 97.9%id, 0.1%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 16432000k total, 15892072k used, 539928k free, 134816k buffers
Swap: 18481144k total, 608468k used, 17872676k free, 1776488k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
15372 mysql 0 -15 13.7g 12g 4656 S 6.0 82.0 12187:24 mysqld
-----

with 1250 people logged on.

Good luck.