PHP + MySQL + Large Text File = Unknown result.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

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

Post 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 ;)
Benwahballz
Forum Commoner
Posts: 25
Joined: Mon Sep 21, 2009 12:54 pm

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

Post 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
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

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

Post by jackpf »

Just a guess, but have you cd'd to your PHP install dir?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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.
Benwahballz
Forum Commoner
Posts: 25
Joined: Mon Sep 21, 2009 12:54 pm

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

Post 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?
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

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

Post 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.
Benwahballz
Forum Commoner
Posts: 25
Joined: Mon Sep 21, 2009 12:54 pm

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

Post by Benwahballz »

which php -> no result, just brings another blank command line
whereis php -> displays "php:" (no quotes)
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

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

Post by Mirge »

Who is your host? You might try sending them a quick email asking how to access php from the command-line.
Benwahballz
Forum Commoner
Posts: 25
Joined: Mon Sep 21, 2009 12:54 pm

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

Post 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:
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post 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.
Benwahballz
Forum Commoner
Posts: 25
Joined: Mon Sep 21, 2009 12:54 pm

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

Post 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:
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post by Eran »

show us your current code
Benwahballz
Forum Commoner
Posts: 25
Joined: Mon Sep 21, 2009 12:54 pm

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

Post 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? :?:
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

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

Post by Eran »

Where is the code that queries the database?
Benwahballz
Forum Commoner
Posts: 25
Joined: Mon Sep 21, 2009 12:54 pm

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

Post 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.
Post Reply