I just got done playing with this same scenario. If the task finishes locally OK, but totally dogs on the network, then you may have a network issue, NOT a MySQL issue.
1) Make sure that the switch and boxes are set to full duplex operation. This will eliminate collisions. Make sure that everything is REALLY running full duplex. Don't assume that it just is. 2) Make sure that your switch has a high enough internal bandwidth to handle the full duplex traffic. Many older switches do not have the internal backplane bandwidth between ports to support a lot of 100 Mbit traffic even though they will accept 100 Mb full duplex connections on all ports. They may have only a backplane bandwidth of 200-300 Mb and you can fill that up with e-mail, internaet, etc. and leave nothing left over for server to server traffic. 3) Put the servers on a VLAN if possible to avoid other network traffic. Symptoms of these problems are slow processing with none of your boxes loaded up. top will show them idle and the completion time for your processing will be painfully long. It will run fine locally, but, just crater over the network. Sound familiar? When I made the above changes, I was able to change network throughput from 12 Mb/sec between application server box and database server box to 160 Mb/sec of traffic. top then showed the application boxes as totally busy with no idle time. The MySQL box was never really taxed even at that throughput level. Also, you can look to optimize your SQL (i.e.: multiple records inserted per INSERT SQL command - see web for syntax) but that may not be your issue if it worked OK on the local box. I found that stringing several Athlon XP1800+ boxes together (my test environment) I was able to max out the boxes before I maxed out the network @ a true 100 Mb full duplex. Hope this helps! Ken Hylton Programmer Analyst IV LEC Systems & Programming Billing Concepts, Inc. 7411 John Smith Drive San Antonio, Texas 78229-4898 (210) 949-7261 -----Original Message----- From: [EMAIL PROTECTED] [mailto:hooker@;rave.iinet.net.au] Sent: Thursday, October 24, 2002 7:45 PM To: [EMAIL PROTECTED] Subject: Performance over a network Hi, I'm running MySQL 3.23.51 on a Debian Linux server, and I've got a performance question. I have a perl application which builds up data in memory (in a hash) and every 30 seconds forks and writes the data to a database. When both perl script and MySQL server are on the same machine, the script manages to finish writing to the database before the next child appears (*most* of the time :-) ). However, if the script and MySQL server are on different machines, separated by an uncongested 100 Mbs ethernet network, the child always fails to write to the database within the 30 second window. So, my questions are ... * Is there a measure of the expected difference between these two scenarios? * Is there any explicit tuning which can be done to speed up access over the network (short of adding gig-ethernet cards which isn't likely) ? The reason for not just leaving everything on the same machine is that the expected growth over the next 6 months will mandate more than one server to handle the incoming data, so the separation between script and database engine is going to have to happen anyway. Hopefully ... Paul Wilson Chime Communications Ltd --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
