Kishore Jalleda wrote:
Hi All, We have a production Database, running mysql 4.0.17 , on a Poweredge 2650 with 3 GB RAM, and dual Xeon 2.4 GHZ, the server averages between 100 and 200 qps ( ,also and the CPU/MEM load is pretty low and is extremely fast except for once in a while may be 1 in 10000 queries take mote than 3 seconds to execute (slow_query_time), and the status of the query is always "writing to net"Initially the NIC was an Intel 10/100, then the slow queries were a lot more, then I started using the Broadcom Gigabit port that comes with the 2650, and recently I also started load balancing the traffic between the two Broadcomm NIC'S using the Basp Module, so that I effectively have a 200 MBPS full duplex link( the switch connected has only FE ports), with this setting the slow queries have decreased a lot, but they do come up once in a while.. There is no lock time in any of them, I have tried using both InnoDB and MyISAM, but they dont seem to go away, so I was wondering why there are still few queries which still have "writing to Net" in the status line and sometimes take 30-50 seconds to execute, note that if the queries are run in any mysql client they run in less than 0.005 seconds, so I am pretty sure its the network thats the bottleneck, and buying a Gigabit capable switch is not an option now, also sometimes the traffic on the server goes upto 35MBPS. So why is mysql not sending the results back to the client and sometimes waiting soo long, also while mysql is waiting for a long query( in the Writing to Net status) hundreds of other queries are executed extrelmely fast. I am not very familiar with how mysql handles network packets, and why its holding back .......
If the network is a bottleneck, certainly this could result in long "Writing to Net" status. One could imagine network congestion causing repeated TCP/IP retries, etc. It might just be that your server is heavily loaded and certain threads are not getting enough resources to finish processing. Things like this are usually far to complex to diagnose simply, and if you want expert help, I would recommend you engage with our professional services team with either of these packaged consulting solutions: http://www.mysql.com/consulting/packaged/performance.html http://www.mysql.com/consulting/packaged/rapidresponse.html Regards, Josh -- Josh Chamas Director, Professional Services MySQL Inc., www.mysql.com Get More with MySQL! www.mysql.com/consulting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
