hi Jigal ;) I've spent months to tune my server, optimizing indexes, code, even i've programmed a cache system on some hot points at my site. I close mysl connections and free resources when i don't need it.
You can see at http://cluster2.genteya.com/load.png that my server keeps himself between 2 and 4 server load average over all day, except when mysql start to enqueue the locked queries and have peaks close to 20 load average for only some minutues, after these few minutes, all queries get unlocked,are processed by mysql server and all returns to normally work. This server carries over 800.000 not unique hits per day. When the mysel fall into locked state, i can see enough free RAM and swap is never used. ----- Original Message ----- From: "Jigal van Hemert" <[EMAIL PROTECTED]> To: "PaginaDeSpud" <[EMAIL PROTECTED]> Sent: Monday, November 07, 2005 9:17 AM Subject: Re: locked status problem > PaginaDeSpud wrote: > > Hi, > > i've got a problem with my server because some times per day, something occurs and server load average grows until 20 due to mysql. When it occurs, with "show processlist", I can see a lot of queued queries in "locked" state ( more than 100 queued). > > You can see the load average at http://cluster2.genteya.com/load.png ( notice these peaks, 4 o 5 peaks per day). > (...) > > set-variable=max_connections=2000 > > With such high settings for max_connections you may run out of memory > when a lot of connections are made. Your server will start to swap if it > runs out of RAM. Swapping is usually so slow that things get out of > control; the time needed to handle a request is increased dramatically, > which causes the number of requests waiting to be handled to increase > and the number of connections in use to get even higher. > > There's a lot you can do to minimize the load of your db server: > - optimize tables, indexes and queries > - do not use persistent connections in general (there might be > conditions where they may be useful, but usually making a connection is > so fast that this is better than keeping all those connections open) > - optimize your config to make optimal use of available memory > - do not keep connections to your db server open when you don't need > them (e.g. after querying your database your application initiates a > download) > - keep the number of connections in your application to a minimum (avoid > nested queries) > > Regards, Jigal. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
