> It would be helpful to know how much data you are trying to pump across. > If you are having trouble finishing in under 30 seconds over a 100mb > connection, it must be a lot of data. > The first thing to check is to make sure you have your connections set > to full duplex. Even if there are only two machines talking you could be > getting a lot of collisions, especially if you are transferring data in > small amounts.
Ooops, my bad. The inserts are bulked up into chunks of 1000 (it was 100, but I increased the number to see if it cured the problem - doesn't, but the insertions are faster as I would have expected), and there are up to 100,000 of them. Agree about the full/half duplex issue. I believe that they're both full, but will check. > Which brings me to the next suggestion. If you are doing many individual > sql inserts you may not be using the network efficiently. You want to be > able to fill multiple network packets during your transfer, taking > advantage of what some refer to as "burst" mode. You should be using > this style insert: > INSERT INTO db (field1,field2,...) VALUES > (val1,val2,...),(val1,val2,...),(val1,val2,...),(val1,val2,...),... > > If you are still having trouble, you may want to rethink how you are > going about transferring the data. Perhaps creating an import file > locally and transferring the file over to the database machine. You then > have a program on the db machine to process files that are transferred. > In this scenario you don't have any timing issues since you are > essentially creating a queue that is being processed on the db machine. > Once a file is processed it's deleted and then the program checks for > any other files ot process. This also allows you to take the database > down for maintenance if you have to. Lots of benefits to this setup. That's true, I hadn't thought that option through completely. Using import files would help fix another problem with the current design that I've managed to produce in testing with lots of data (150,000 inserts per 30 seconds). If the child process doesn't complete in 30 seconds, children back up on the server, and eventually the HEAP table gets full. The system takes care of that automatically, but there are previously created children trying to use a full table who don't realise that there is another available. The design is a little flaky at that point. Paul Wilson Chime Communications --------------------------------------------------------------------- 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
