xPDO, PHP and MySQL are slow with huge tables

289 Posts

ReSpawN Reply #11, 15 years, 6 months ago

So the iterator spits out an object with arrays (and obviously the entire MODx reference). When you loop over it and use the ->get() function, then and only then it fetches it from the object? If I am not mistaken this is lighter, but doesn’t that create another query that could stress the server? Or has it already been fetched all data and cached (not parsed)?

I’m starting to get into the hardcore developing on MODx so these are things I simply need to know! laugh

@MarkGHErnst

Developer at Adwise Internetmarketing, the Netherlands.

10 Posts

sofasurfer Reply #12, 15 years ago

Not sure if this is the right topic. But I'm having problems with inserting allot of data at once.

I have around 100k rates from a csv file which I need to add to the database which looks more or less like this:

// loop rates
foreach ($supplierInfo['rates'] as $rateArray ){

	// save rate to DB
	$objRate = $modx->newObject('sfVehicleRate');
	$objRate->fromArray($rateArray);
	$objRate->save();

}

<object class="sfVehicleRate" table="vehicle_rates" extends="xPDOSimpleObject">

	<field key="vehicleid" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="index" />
	<field key="periodid" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="index" />		
	<field key="price" dbtype="float" precision="10" attributes="unsigned" phptype="float" null="true" default="0" index="index" />		
	<field key="location" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />

	<aggregate alias="Vehicle" class="sfVehicle" local="vehicleid" foreign="id" cardinality="one" owner="foreign" />
	<aggregate alias="Period" class="sfRatePeriod" local="periodid" foreign="id" cardinality="one" owner="foreign" />		

</object>

This all work fine on my development machine as i have "allot of memory", unfortunately on the clients hosting environment i reach the memory limit just after a few hundert inserts.

Allowed memory size of 67108864 bytes exhausted (tried to allocate 24 bytes) in /core/xpdo/om/xpdoobject.class.php on line 1239

I could probably run a cron job with some perl/shell script but was wondering, what exactly I'm doing wrong, or is there a more efficient way to do so?

Ok 20MB is not that much but somehow it should be possible ?

Perhaps by adding them via a xpdo->query directly?

Thanks for any feedback as i really love MODX and XPDO.

Home is where the hard disk is:
http://sofasurfer.org

MODX Staff
10,725 Posts

opengeek Reply #13, 15 years ago

I don't see any reason that loop would have anything to do with the memory limit issue. It seems that just loading the CSV data might be the more likely problem. How much memory is used when you load $supplierInfo ?

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

10 Posts

sofasurfer Reply #14, 15 years ago

that's what i first thought too, but seems to be not the case.

just by removing the $objRate->save(); statement it runs quick and fast.

I attached the two output files once with the save command memory: 20185336

and then without the save command where memory: 7092280

could it be, because there are 4 keys in the $rateArray which actually don't exist in the database?

i also thought, it could be the MYSQL server but can't see and large memory grabbing there too..

Home is where the hard disk is:
http://sofasurfer.org

MODX Staff
10,725 Posts

opengeek Reply #15, 15 years ago

If you are trying to set primary keys, you need to specify that in the setPrimaryKeys parameter of fromArray(). That may be the issue.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

10 Posts

sofasurfer Reply #16, 15 years ago

not sure if i understand, the only primary key in the table is the id, and there is no id value in the array.

the array just has additional values which don't match the DB, so i thought this could be the issue.

Array ( [vehicleid] => 675 [vehiclecode] => 2BERTHST [supplierid] => 20 [starttime] => 1315155600 [endtime] => 1315674000  => D6 [locationtype] => 1 [location] => 6 [startdate] => 2011-09-05 [enddate] => 2011-09-11 [periodid] => 1 [price] => 131 )

or do i need to set vehicleid special as this is related to the vehicle table. but even when setting each value by it self $objRates->set('vehicleid',$rateArray['vehicleid']); i still get the same.. i had an other look at the SQL server and it does seem hes a bit busy... so i guess it could be also the MYSQL configuration.. i just wanna check if someone else had something similar or there's a better way to insert allot of records.. so for now i assume it's not a XPDO issue and will check on the SQL server side.. will make a short demo script which inserts the data direct to the database and see what's happening.. thanks for the tips..

Home is where the hard disk is:
http://sofasurfer.org

24,544 Posts

BobRay Reply #17, 15 years ago

I think you might want to do one of the following to see if it makes a diffrence:

Remove the extraneous fields and their values from the source file

[li]Process the input with preg_replace or something else to eliminate the array members that are not in the DB.

Add the extraneous fields to the DB table

I'm not sure what xPDO does when you try to save a nonexistent field to the DB, but it could take some time and/or memory.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

10 Posts

sofasurfer Reply #18, 15 years ago

Shouldn't that then be solved when setting the values directly and not from the array?

$objRates = $modx->newObject('cncVehiclePrice');
//$objRates->fromArray($rateArray);
$objRates->set('vehicleid',$rateArray['vehicleid']);
$objRates->set('periodid',$rateArray['periodid']);
etc..
$objRates->save();

But this gives the same result...

Home is where the hard disk is:
http://sofasurfer.org

24,544 Posts

BobRay Reply #19, 15 years ago

Yes it should. That suggests an issue with your DB server.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting