Hi there a are a couple of things that I have found cause occasional lock ups.
running out of temp space - MySql builds temp files on bigger queries and if it runs out of temp disk space it grinds to a halt, which causes all the following queries to queue up until max_connections is exceeded. the second cause is one massive query - big tables, lots of joins etc - that takes so much processing power that there is nothing left. MySql and perhaps the server slows down to the point that it cannot process any more queries, and again the queue builds until max_connections is exceeded. if the lockup occurs overnight this might be the time the server is doing housekeeping - apache log analysis for example - so it may be the server is loaded too heavily to manage MySql queries too, so again the queue builds up. Just a few thoughts - hope it helps. Peter > -----Original Message----- > From: Fredrik Carlsson [mailto:[EMAIL PROTECTED] > Sent: 01 January 2005 10:37 > To: mysql@lists.mysql.com > Subject: Max connections being used every 10-12 day. > > > Hi list, > > I have a rather serious problem that i really dont know how to solve, > > Every 8-12 day my mysql server stops responding and i get the error code > that indicates that max_connections are full, the problem is that i have > checked all of > my code over and over again to se that every connections are closed > properly and they are. No persistent connections are being used and the > max connections error allways occures at night 02:00->03:00, my httpd > logs shows no unnormal amount of traffic at the time. > > The last time this happend i tuned the wait_timeout down to 15 seconds > to se if that helped, but no effect :( > > The server is running NetBSD 1.6.2 and mysql 4.0.21 > > I really need help on this one because i dont know what is causing > max_connections to be used all at once or how to reproduce the error, i > only know that it happens very periodicly and 'show full processlist' > hardly ever shows any connections not even the day/hours before the > error. The server has about 4-5 queries / seconds. > > According to the manual the max_connections have one connection reserved > for the superuser but i have never been able to use that extra > connection to se which user that is eating upp all the connections. > > > // Fredrik Carlsson > > ____________________________________________ > # The MySQL server > [mysqld] > port = 3306 > socket = /tmp/mysql.sock > skip-locking > key_buffer = 280M > max_allowed_packet = 32M > table_cache = 512 > sort_buffer_size = 2M > read_buffer_size = 2M > myisam_sort_buffer_size = 64M > thread_cache = 8 > query_cache_size = 64M > # Try number of CPU's*2 for thread_concurrency > thread_concurrency = 8 > max_connections = 200 > wait_timeout = 15 > connect_timeout = 5 > > [mysqldump] > quick > max_allowed_packet = 16M > > [mysql] > no-auto-rehash > # Remove the next comment character if you are not familiar with SQL > #safe-updates > > [isamchk] > key_buffer = 256M > sort_buffer_size = 256M > read_buffer = 2M > write_buffer = 2M > > [myisamchk] > key_buffer = 256M > sort_buffer_size = 256M > read_buffer = 2M > write_buffer = 2M > > [mysqlhotcopy] > interactive-timeout > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
