Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,383
    Thanks
    264
    Thanked 32 Times in 31 Posts

    large data file process

    Hi,

    I have a file of about 200,000 records, they are profiles including pictures. I am converting it from one db structure to another.

    The conversion is not really my question though.

    My question concerns both mysql and php. I am concerned about server overload or timeout.

    During the conversion i will need to make three image sizes for each picture, small, med, large and store them on the server, and then convert the rest of the data.

    I am sure i cant just run the whole 200,000 record job at once on my shared server so whats a good number to start with 1000 at a time, 5000 at a time..

    Im not quite sure how many batches to split this up into..

    Thanks

  • #2
    New Coder
    Join Date
    Apr 2013
    Posts
    39
    Thanks
    2
    Thanked 2 Times in 2 Posts
    i think you should use maximum of 1000 or if possibly 500 at a time to avoid overload

  • #3
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,327
    Thanks
    60
    Thanked 525 Times in 512 Posts
    Blog Entries
    4
    I wouldn't even go as high as the hundreds to be honest.That would still be a massive server resource hog and on a shared system thats a really bad idea if you want to keep your account live.

    Instead I would download the database and do the conversion locally using a wamp system. That or you use a wamp system to transfer the records one at a time, convert locally and then reupload that record. For this you would need to base64 encode each field before transferring it via http back and forth. It will be slower but as far as your host is concerned it will simply look like a load of http requests rather than a single script hogging the cpu.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • Users who have thanked tangoforce for this post:

    durangod (05-09-2013)

  • #4
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,383
    Thanks
    264
    Thanked 32 Times in 31 Posts
    As luck would have it i have my own hosting company, well reseller account anyway... and my best friend is the main host so if i push the limit just a bit it will be ok..

    but i just did a site restore for a customer 65mb db and after an hour it was still processing, finally after an hour and a half i closed the window and pulled up the db and it had loaded, not sure whats up with phpMyAdmin and why it did not refresh..

    But anyway that gives me an idea of what im up against... I charged them 5 cents a record to do this... i dont know what the going rate is for conversions but for 200,000 records thats a bit of mula... and i figure it will take me prob two weeks or more to do it with as slow as the upload will be, plus having to write the conversion script..

    I dont have the bid yet we will see, at least i told them that was just a beginning bid until i can see the actual data im working with then i can give them a final one.

    Thanks for the help all

  • #5
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,327
    Thanks
    60
    Thanked 525 Times in 512 Posts
    Blog Entries
    4
    Quote Originally Posted by durangod View Post
    but i just did a site restore for a customer 65mb db and after an hour it was still processing, finally after an hour and a half i closed the window and pulled up the db and it had loaded, not sure whats up with phpMyAdmin and why it did not refresh..
    I think you've made a slight mistake there by closing the window. Have you checked every record in every table to make sure its intact?

    Be careful with phpmyadmin. It's a rogue beast when it comes to importing large databases and tables. By default it will only accept a 2Mb file uploaded by http but even if you manually import from a file on the disk (if you have that access) it can still be a pain.

    There are other options, apparently mysql can natively import files itself but I've never had access to this. I had to transfer records via http using curl and base64 encode every field before sending the array as a bunch of post fields. At the other end they would be decoded and put into the appropriate table. Sure its time consuming but I found it to be pretty reliable. It did take a lot of testing to iron out the bugs and get it working 100% but once those issues were sorted it never let me down. Again time consuming but if you write a script (or windows app) on the sending end that can run repeatedly then its not a big problem.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #6
    Senior Coder
    Join Date
    Nov 2010
    Posts
    1,383
    Thanks
    264
    Thanked 32 Times in 31 Posts
    Yeah i went thru the tables to make sure they had data and checked the db stats and it seems to be fine. I also did an export of the db and it was the same size as the one i imported so i think im ok, lucky as heck im sure... I dont like closing windows like that either.

    Yeah seems times comsuming with your process but also seems very reliable which is prob the most important thing in our business.. thanks for the info. I should know by next week if i have the proj or not..


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •