Re: many updates really slow
On Thu, May 06, 2004 at 10:12:19AM -0700, William Wang wrote: Hi everyone, Please help. I have MySQL server running on host A in US and I am using it on host B in Europe. Every query takes about 0.3 seconds. Now I want to do update db with 5000 updates. So I put all the UPDATES commands in a file cmd.sql and do: mysql -hA cmd.sql and it takes 30 MINUTES which is reasonable: 0.3 * 5000 but unbearable. Is there any better way to do this? Am I doing something stupid? Thanks a lot. Network latency is killing you. Send the cmd.sql file to host A and execute it locally. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: many updates really slow
I'm guessing here but it sounds like you have the cmd.sql file on your local machine (in Europe). If that's the case, compress the file, ftp it to the US server, uncompress it and do the load locally on the US server. If you're doing the update remotely, you're probably being killed by transmission time. Transferring the file in toto to the US server will be quicker than having the communications lag of doing sending a command from Europe to the US, having the command execute and then receiving the status back in Europe...times 5000 records. Hope this helps, Jack -Original Message- From: William Wang [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 1:12 PM To: [EMAIL PROTECTED] Subject: many updates really slow Hi everyone, Please help. I have MySQL server running on host A in US and I am using it on host B in Europe. Every query takes about 0.3 seconds. Now I want to do update db with 5000 updates. So I put all the UPDATES commands in a file cmd.sql and do: mysql -hA cmd.sql and it takes 30 MINUTES which is reasonable: 0.3 * 5000 but unbearable. Is there any better way to do this? Am I doing something stupid? Thanks a lot. __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: many updates really slow
Thanks Jack and Jeremy. But this is part of my application and I need to do this automatically. I don't want to write a server-client application to just to handle file transimission and do pass the update cmd to local server. Any idea? Thanks. William --- Jack Coxen [EMAIL PROTECTED] wrote: I'm guessing here but it sounds like you have the cmd.sql file on your local machine (in Europe). If that's the case, compress the file, ftp it to the US server, uncompress it and do the load locally on the US server. If you're doing the update remotely, you're probably being killed by transmission time. Transferring the file in toto to the US server will be quicker than having the communications lag of doing sending a command from Europe to the US, having the command execute and then receiving the status back in Europe...times 5000 records. Hope this helps, Jack -Original Message- From: William Wang [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 1:12 PM To: [EMAIL PROTECTED] Subject: many updates really slow Hi everyone, Please help. I have MySQL server running on host A in US and I am using it on host B in Europe. Every query takes about 0.3 seconds. Now I want to do update db with 5000 updates. So I put all the UPDATES commands in a file cmd.sql and do: mysql -hA cmd.sql and it takes 30 MINUTES which is reasonable: 0.3 * 5000 but unbearable. Is there any better way to do this? Am I doing something stupid? Thanks a lot. __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: many updates really slow
I'd suggest a shell script - a couple of them, actually. Have a shell script on the Europe machine that runs continually. It checks for the existence of the cmd.sql file. If it finds it, it FTPs it to the US server and then deletes or archives the file locally. If it doesn't find it, it sleeps for 5 minutes, wakes up and looks again - minimum cpu cycles wasted. The second script would be on the US server. It would check every 5 minutes for the cmd.sql file. If it finds it, it would load it into MySQL and then either delete or archive the file. If it doesn't find the file, it goes back to sleep. Jack -Original Message- From: William Wang [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 1:43 PM To: Jack Coxen; [EMAIL PROTECTED] Subject: RE: many updates really slow Thanks Jack and Jeremy. But this is part of my application and I need to do this automatically. I don't want to write a server-client application to just to handle file transimission and do pass the update cmd to local server. Any idea? Thanks. William --- Jack Coxen [EMAIL PROTECTED] wrote: I'm guessing here but it sounds like you have the cmd.sql file on your local machine (in Europe). If that's the case, compress the file, ftp it to the US server, uncompress it and do the load locally on the US server. If you're doing the update remotely, you're probably being killed by transmission time. Transferring the file in toto to the US server will be quicker than having the communications lag of doing sending a command from Europe to the US, having the command execute and then receiving the status back in Europe...times 5000 records. Hope this helps, Jack -Original Message- From: William Wang [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 1:12 PM To: [EMAIL PROTECTED] Subject: many updates really slow Hi everyone, Please help. I have MySQL server running on host A in US and I am using it on host B in Europe. Every query takes about 0.3 seconds. Now I want to do update db with 5000 updates. So I put all the UPDATES commands in a file cmd.sql and do: mysql -hA cmd.sql and it takes 30 MINUTES which is reasonable: 0.3 * 5000 but unbearable. Is there any better way to do this? Am I doing something stupid? Thanks a lot. __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover
RE: many updates really slow
Thanks Jack and Jeremy. But this is part of my application and I need to do this automatically. I don't want to write a server-client application to just to handle file transimission and do pass the update cmd to local server. Any idea? Thanks. If you have ssh installed on both machinesthis could be done with two commandsIf you set up passwdless ssh authentication, this could be automated in a script. even if you don't you'd just have to type your password. scp ./cmd.sql [EMAIL PROTECTED]:/path/to/cmd.sql ssh [EMAIL PROTECTED] '/path/to/mysql -u user -pPassword DBname /path/to/cmd.sql' I use stuff like this all the time and it works wonderfully. HTH...and i didn't miss the point. Jeff William --- Jack Coxen [EMAIL PROTECTED] wrote: I'm guessing here but it sounds like you have the cmd.sql file on your local machine (in Europe). If that's the case, compress the file, ftp it to the US server, uncompress it and do the load locally on the US server. If you're doing the update remotely, you're probably being killed by transmission time. Transferring the file in toto to the US server will be quicker than having the communications lag of doing sending a command from Europe to the US, having the command execute and then receiving the status back in Europe...times 5000 records. Hope this helps, Jack -Original Message- From: William Wang [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 1:12 PM To: [EMAIL PROTECTED] Subject: many updates really slow Hi everyone, Please help. I have MySQL server running on host A in US and I am using it on host B in Europe. Every query takes about 0.3 seconds. Now I want to do update db with 5000 updates. So I put all the UPDATES commands in a file cmd.sql and do: mysql -hA cmd.sql and it takes 30 MINUTES which is reasonable: 0.3 * 5000 but unbearable. Is there any better way to do this? Am I doing something stupid? Thanks a lot. __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- 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]
Re: many updates really slow
if it is part of app. use soap to communicate with server and adopt your application. Send all your commands as one string parameter to server, and execute it all there. Such modification to is quite simple to write using gSoap library (if your application is C++ based) or Axis (Java). mirza William Wang wrote: Thanks Jack and Jeremy. But this is part of my application and I need to do this automatically. I don't want to write a server-client application to just to handle file transimission and do pass the update cmd to local server. Any idea? Thanks. William --- Jack Coxen [EMAIL PROTECTED] wrote: I'm guessing here but it sounds like you have the cmd.sql file on your local machine (in Europe). If that's the case, compress the file, ftp it to the US server, uncompress it and do the load locally on the US server. If you're doing the update remotely, you're probably being killed by transmission time. Transferring the file in toto to the US server will be quicker than having the communications lag of doing sending a command from Europe to the US, having the command execute and then receiving the status back in Europe...times 5000 records. Hope this helps, Jack -Original Message- From: William Wang [mailto:[EMAIL PROTECTED] Sent: Thursday, May 06, 2004 1:12 PM To: [EMAIL PROTECTED] Subject: many updates really slow Hi everyone, Please help. I have MySQL server running on host A in US and I am using it on host B in Europe. Every query takes about 0.3 seconds. Now I want to do update db with 5000 updates. So I put all the UPDATES commands in a file cmd.sql and do: mysql -hA cmd.sql and it takes 30 MINUTES which is reasonable: 0.3 * 5000 but unbearable. Is there any better way to do this? Am I doing something stupid? Thanks a lot. __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
