Bono Posted October 31, 2010 Posted October 31, 2010 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 Posted November 2, 2010 Posted November 2, 2010 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 Posted November 3, 2010 Posted November 3, 2010 /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 Posted November 5, 2010 Posted November 5, 2010 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.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.