Exporting With 5.1 Beta Is Very Slow

[peterlaursen]

For some reason exporting with HTTP-tunnel is much slower with 5.1 BETA than 5.03 RC. Settings are identical, and there are NO BULK INSERTS.

@ritesh:

Test with FAQ-database on Webyog server. 5.03 exported database to my local in 4-5 minutes. 5.1 BETA takes almost 1 hour (tested 1:30 am this morning March 3rd). Repeated and reproduced twice. Dump is 6-7 MB. You can easily reproduce – if you are lucky to have it running for an hour without a HTTP-error occurring! 🙁

Also read: http://www.webyog.com/forums/index.php?showtopic=1966

@ritesh: assigned to you in Eventum!

[peterlaursen]

reproduced on my local with HTTP. Same data.

Export is about 3 times faster then with the remote server, but the ratio 5.03/5.1 is the same. More than 10 time slower with 5.1 Beta1. Same with max_allowed_packet from 1 to 100 MB, same on MySQL 5.0 and 5.1.

ritesh: All tests are done on my 'mini' (server+client). Did you reproduce with a more 'standard' machine or should I do?

[peterlaursen]

deep silence!

I asked if there was any need that I test this on more different computers?

If you have verified there is no need – if not I might do!

added: well I did test on both my computers. Problem is the same. And it is the same on my personal ISP too.

And I believe the new CHUNK feature with HTTP-connections is the reason. With 100.000 rows in a table you will now issue 1.000 SELECTS. Each of them must create a new connection SQLyog <> tunnelling script <> webserver <> MySQL. Establishing conncetion 1.000 times is 1.000 times slower than doing it only once! And establishing this SQLyog-PHP-webserver-MySQL connection is what takes most of the time. SQLyog client must sit idle and wait each time this is happening. Network software, hardware and webserver is in control. SQLyog is out-of-control and has to wait.

Try exporting a big table yourself with HTTP and watch HISTORY while job is running. You will see that each time a connection is established several seconds pass. That is how slow the type of connection is established. Selecting 100 rows and 100.000 rows in one SELECT are both very fast. It only depends on MySQL performance – nothing else once webserver <> MySQL connection is established. Do as few queries as possible. Do one SELECT only when it is possible – not 1.000 SELECTs. This is particularly important with HTTP-connections. Beta 5.1 multiplies the performance issue with HTTP-tunnel by iterating the 'weak link in the chain' multible times. 1.000 SELECTS and 4 seconds for connections to establish in average is more than one hour. One hour idle time for SQLyog! And not unrealistic at all!

BULKs are created at the client, and performance is not affected by the connection type at all. CHUNKs are each generated with one query to the server and takes a new connection for each. First/BULK is right. Second/CHUNK is wrong!

Changing the mysql_connect() method to mysql_pconnect() in tunnelling file does not make much difference. What I believe indicates that it is not webserver <> MySQL connection that is a problem, but establishing connection over the Internet. Also when running with a local server the difference is not that big.

[peterlaursen]

I just checked the Backup 'powertool'. Looks like this has been messed up the same way. Fortunately we have phpMyAdmin 😮

[Ritesh]

peterlaursen wrote on Mar 4 2006, 03:13 PM:

deep silence!

I asked if there was any need that I test this on more different computers?

If you have verified there is no need – if not I might do!

added: well I did test on both my computers.  Problem is the same.  And it is the same on my personal ISP too. 

And I believe the new CHUNK feature with HTTP-connections is the reason.  With 100.000 rows in a table you will now issue 1.000 SELECTS.  Each of them must create a new connection SQLyog <> tunnelling script <> webserver <> MySQL.  Establishing conncetion 1.000 times is 1.000 times slower than doing it only once! And establishing this SQLyog-PHP-webserver-MySQL connection is what takes most of the time.  SQLyog client must sit idle and wait each time this is happening.  Network software, hardware and webserver is in control.  SQLyog is out-of-control and has to wait.

Try exporting a big table yourself with HTTP and watch HISTORY while job is running.  You will see that each time a connection is established several seconds pass. That is how slow the type of connection is established. Selecting 100 rows and 100.000 rows in one SELECT are both very fast.  It only depends on MySQL performance – nothing else once webserver <> MySQL connection is established.    Do as few queries as possible.  Do one SELECT only when it is possible – not 1.000 SELECTs.  This is particularly important with HTTP-connections.  Beta 5.1 multiplies the performance issue with HTTP-tunnel by iterating the 'weak link in the chain' multible times.  1.000 SELECTS and 4 seconds for connections to establish in average is more than one hour.  One hour idle time for SQLyog!  And not unrealistic at all!

BULKs are created at the client, and performance is not affected by the connection type at all.  CHUNKs are each generated with one query to the server and takes a new connection for each.  First/BULK is right. Second/CHUNK is wrong!

Changing the mysql_connect() method to mysql_pconnect() in tunnelling file does not make much difference.  What I believe indicates that it is not webserver <> MySQL connection that is a problem, but establishing connection over the Internet.  Also when running with a local server the difference is not that big.

[post=”9012″]<{POST_SNAPBACK}>[/post]

I have not forgotten the issue. Its all in my TO-DO list.

I was working on the issue yesterday and even I think out that the new feature to break down data in chunks is the issue.

I will research on it more tomorrow and let you know the details.

[peterlaursen]

This CHUNK thing simply is against the very nature of the Internet (at least the HTTP part of it) I believe.

[peterlaursen]

Looks like there is a FAQ to write on BULKs and CHUNKs when it has all taken its final shape! 🙂

[Author]

Posts