Page 2 of 3

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Mon Sep 21, 2009 4:19 pm
by jackpf
Mirge wrote:Do you have SSH access? Log in via SSH if you can, and run the PHP script from there.
Yeah...it depends how you want to do this really. Is it something you do yourself every now and again, or is user controlled, with an interface and stuff?

If the former, then yeah, try via SSH. If latter, see my previous post ;)

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Mon Sep 21, 2009 4:39 pm
by Benwahballz
Yes, I can login via ssh, but If I do a "php -v" the reply is "-bash: php: command not found" which says to me that I cannot use PHP CLI

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Mon Sep 21, 2009 5:37 pm
by jackpf
Just a guess, but have you cd'd to your PHP install dir?

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Mon Sep 21, 2009 5:54 pm
by Eran
Your code is failing because it is highly inefficient and does not scale. You are performing multiple SQL queries per line of text, and with tens of thousands lines, that simply brings down the MySQL server. You need to perform the same process outside of the loop, either by contanetating values or pushing them into arrays.

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Tue Sep 22, 2009 9:06 am
by Benwahballz
pytrin wrote:.......... You need to perform the same process outside of the loop, either by contanetating values or pushing them into arrays.........
I dont think I follow you on how I can perform the same process outside of the loop. And I dont feel there is any other way to check if the database already contains the information before actually adding it. I believe there will be appx 2.5million lines total that I will be reading. Each line doesnt necessarily get an insert, but there are multiple selects on each line to check if the data exists already.

Also, Time is not really an issue here, If the data that needs to be loaded needs to take hours or even a day or two that is ok. This process will be done every 6 months to 1 year. I am trying to put a pause for 3-5 seconds into the code to let the sql server catch up after 25000 lines. Maybe this wont bring the mysql server down. If the mysql server is the piece thats crapping out on this code, is there any sort of setting that I can check for to see what the max number of queries per second is allowed, or anything similar?

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Tue Sep 22, 2009 9:59 am
by Mirge
I don't think the MySQL server is crapping out, unless it's just ridiculously configured... but yes, the script WILL definitely take time to execute... which is why I suggested running it from the command-line instead. Log back into SSH & post the results of:

which php

whereis php


Those 2 specific commands.

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Tue Sep 22, 2009 10:56 am
by Benwahballz
which php -> no result, just brings another blank command line
whereis php -> displays "php:" (no quotes)

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Tue Sep 22, 2009 10:58 am
by Mirge
Who is your host? You might try sending them a quick email asking how to access php from the command-line.

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Tue Sep 22, 2009 1:33 pm
by Benwahballz
Bleh, Just contacted the host, and there is no access to PHP from the command line, so that pretty much eliminates that idea.

The only thing he suggested was a program called MySQL-Front, which is more for managing a database with a pretty front end.

I have split the files up into even smaller files now, anywhere from 14MB-19MB in size. And after reading each file, just give the user a button to press to go on to the next file. Its a little half assed but it may just work. Either the button or have javascript forward them onto the next page passing the file number as a variable in $_GET.

After running it as stated above with javascript, it still seems to timeout after the third file.
:banghead:

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Tue Sep 22, 2009 2:22 pm
by Eran
What I meant was - collect data in arrays inside the loops, but don't run queries inside. Later run specific queries with a lot of values (for checking existance, inserting, etc). Your queries can possibly be merged further to pass some of the logic to MySQL in order to reduce the number of queries. This will be the most performant way to do this (but it will require much more memory). You might find this article I wrote a while ago useful - http://www.techfounder.net/2009/05/14/m ... mysql-php/

Alternatively, you can run the process in batches - iterate lines 1 to 10,000 then pause for a couple of seconds (use sleep) or even start the next batch manually - so you can tell at what point exactly is the script overloading the server (is it 5000 iterations? 10000? maybe 50000?)
Do a couple of checks like this and you'll know much more on where the problem lies.

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Tue Sep 22, 2009 5:03 pm
by Benwahballz
Grrr, now im almost thinking it may have something else to do with the server settings/code combination.

I have it simply looping throught each line of each file and populating an array with values in the line. Sometimes it makes it completely through all the files, othertimes it quits partway through the files. :dubious:

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Tue Sep 22, 2009 5:18 pm
by Eran
show us your current code

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Tue Sep 22, 2009 5:26 pm
by Benwahballz

Code: Select all

 
<?php
$keepGoing = true;
$fileNum = $modelIndex = 0;
$Model = array();
while($keepGoing)
{
    if(file_exists("BigData$fileNum.data"))
    {
        if($file = fopen("BigData$fileNum.data","r"))
        {
            while( !feof($file) )
            {
                $line = fgets($file, 512);
                if( $line != "" )
                {
                    list($modelName,$year,$modelNum,$assyDesc,$partNum,$qtyPer,
                    $posInImage,$imageName,$partName,$refNum,$note1,$flags,
                    $note2,$note3,$note4,$note5) = explode("\t",$line);
                    $currModel = array($year, $modelName, $modelNum);
                    if( !in_array($currModel, $Model) )
                    {
                        $Model[$modelIndex] = $currModel;
                        ++$modelIndex;
                     }
                }
            }
            fclose($file);
            $fileNum++;
        }
    }
    else
    {
        $keepGoing=false;
    }
}
echo "Done";
?>
 
Simple no? :?:

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Tue Sep 22, 2009 5:30 pm
by Eran
Where is the code that queries the database?

Re: PHP + MySQL + Large Text File = Unknown result.

Posted: Tue Sep 22, 2009 5:32 pm
by Benwahballz
pytrin wrote:Where is the code that queries the database?
Ah, hah, there is the gotcha now. There doesnt need to be any queries to the database, its crapping out on this simple piece of code that reads files and populates an array.