[PHP] [X-POST] Fastest way to dump this huge table
I have a huge MySQL table, 2.1 million records, 200MB. Once a week I need to dump it in CSV format and zip the file. This is not on my server, and it's in production, so I don't want to risk testing different methods and possibly hanging up their server for a period of time, so I wanted to seek advice here first to find what's the best way to proceed. I can easily use PHP to query the table for the results I want and write a file line by line and then zip it, but I'm worried that might take too long and hang up the machine. The other way to go is some kind of sql dump command, which I guess would be faster, but not sure how much control I'd have over the exact format of the file. Any suggestions which way I should proceed? Not hanging up their server is my prime concern. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] [X-POST] Fastest way to dump this huge table
Brian Dunning wrote: I have a huge MySQL table, 2.1 million records, 200MB. Once a week I need to dump it in CSV format and zip the file. This is not on my server, and it's in production, so I don't want to risk testing different methods and possibly hanging up their server for a period of time, so I wanted to seek advice here first to find what's the best way to proceed. I can easily use PHP to query the table for the results I want and write a file line by line and then zip it, but I'm worried that might take too long and hang up the machine. The other way to go is some kind of sql dump command, which I guess would be faster, but not sure how much control I'd have over the exact format of the file. Any suggestions which way I should proceed? Not hanging up their server is my prime concern. If hogging their server is of prime concern then ideally you either need to (a) schedule down time for the site while the back-up happens, or (b) replicate the data to another MySQL server, and back-up that. I can't see any reason why you need to bring PHP into the equation here, MySQL has more than enough native tools to do exactly what you need - and probably a butt-load faster too. Cheers, Rich -- Zend Certified Engineer http://www.corephp.co.uk Never trust a computer you can't throw out of a window -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] [X-POST] Fastest way to dump this huge table
On 5/2/07, Richard Davey [EMAIL PROTECTED] wrote: Brian Dunning wrote: I have a huge MySQL table, 2.1 million records, 200MB. Once a week I need to dump it in CSV format and zip the file. This is not on my server, and it's in production, so I don't want to risk testing different methods and possibly hanging up their server for a period of time, so I wanted to seek advice here first to find what's the best way to proceed. I can easily use PHP to query the table for the results I want and write a file line by line and then zip it, but I'm worried that might take too long and hang up the machine. The other way to go is some kind of sql dump command, which I guess would be faster, but not sure how much control I'd have over the exact format of the file. Any suggestions which way I should proceed? Not hanging up their server is my prime concern. If hogging their server is of prime concern then ideally you either need to (a) schedule down time for the site while the back-up happens, or (b) replicate the data to another MySQL server, and back-up that. I can't see any reason why you need to bring PHP into the equation here, MySQL has more than enough native tools to do exactly what you need - and probably a butt-load faster too. Cheers, Rich Brian, As a comparison, on a *nix master/slave configuration, I'm mysqldumping nightly a database that's always growing and now is over a 1GB in size. The dump takes about 20 seconds to finish. Assuming a linear relationship, your 200MB backup should only tie up the db server for around 5 seconds. Bzipping the .sql file takes infinitiely longer, almost an hour, but a lower compression ratio should take only 1/4 to 1/3 as long. I'd say give some attention to the zip method you use. HTH, David
Re: [PHP] [X-POST] Fastest way to dump this huge table
I'd say give some attention to the zip method you use.
or better yet rsync the file, send only what has changed.
--
Regards,
Clive.
Real Time Travel Connections
{No electrons were harmed in the creation, transmission or reading of
this email. However, many were excited and some may well have enjoyed
the experience.}
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] [X-POST] Fastest way to dump this huge table
On Wed, May 2, 2007 8:54 am, Brian Dunning wrote: I have a huge MySQL table, 2.1 million records, 200MB. Once a week I need to dump it in CSV format and zip the file. You could run mysql_dump once by hand and test just how bad it is, and be ready to kill -9 it if the server gets hurt... But, really, as a long-term SAFE solution, the best bet would probably be do set up a master/slave replication in MySQL, and have the *slave* be something non-production that you mysql_dump. There are some caveats to the replication, such as making SURE you have enough hard drive space for the log files, and enough bandwidth that the log files never grow too large. But once you get that working well, you can pretty much do whatever you want to the slave and not risk the master being bogged down. This is also a VERY common way to set up a reporting server, so your marketing weenies can write the ad hoc queries they love that tend to bring down a server... :-v I can easily use PHP to query the table for the results I want and write a file line by line and then zip it, but I'm worried that might take too long and hang up the machine. The other way to go is some kind of sql dump command, which I guess would be faster, but not sure how much control I'd have over the exact format of the file. Any suggestions which way I should proceed? Not hanging up their server is my prime concern. If you can't do a replication setup, or the budget is ridiculously low, running a PHP script with a sleep call and a set_time_limit to make sure it never dies, you can slowly and surely dump out a few records at a time. The risk there is inconsistencies in your dump, for related keys between tables... It will *NOT* be faster than mysql_dump, ever, by any means, almost for sure, but you'll be able to control how much it pounds the server more easily. mysql_dump *may* have some kind of command line flag to limit the resources it consumes so you don't drag down the DB... -- Some people have a gift link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] [X-POST] Fastest way to dump this huge table
Thanks to everyone who answered, think I've got enough info now to handle it. :) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
