xPDO, PHP and MySQL are slow with huge tables

First off, let me define "huge" as 10k rows, table size of 1,2Mb (optimized).

I’ve got in my contruct function a simple line of code that getCollection the table I want to, which is 1,2mb approx in size. Now, my apache_error.log was 476mb and the MODx error.log was 6mb. Not that large to cause any hickups but yet it did. Uncommenting the getCollection line fixed the problem, but still upon a couple of flood refreshes, I get the following line in my error.log. The text after it is in Dutch, but it says that the connection could not be established.

I’m using MODx 2.0.6 (I know, there is an update) with PHP 5.3.3, MySQL 5.1.36 and Apache 2.2.11 on my local WAMP installation (Windows 7 64bit).

Did I mess something up, is this a recurring problem with Windows WAMP or what?

Thanks in advance!

We’re running getCollection calls on much bigger databases than that.

What’s your query look like (php and sql code)?

Hmmm, are you sure the line fixed it?

Comment it out...do at least 10 refreshes and see if it measures a constant speed...
And then put the code back in...does it really go ’a lot slower’ 10 times?

I can’t imagine a simple getCollection being that heavy on the CPU.
What are your caching settings?

(@splittingred: I thought his getCollection’s query was empty...just a simple ’get all’ rows)

I have xpdo working with a 20+ million rows table - a tracking table which tracks which resource was rendered to which user (via whatever template) - used for ’NEW’ flag. although this is far from optimal solution - it functions well, with reading and updating fragments.

a. don’t expect to getCollection such big structures.. actually even getCollection of 10k rows would probably reach the limit of your php_memory_limit. I don’t think xpdo was meant to work with large chunks of data without limit (pagination). working with many thousands of records with getCollection(Graph) would take dozens of mb of memory, and will be slow..
add this in your code: $modx->log(MODX_ERROR_LOG,’peakMem:’.memory_get_peak_usage());

b. the xpdo objects are much larger (in memory) than the sql data.

c. on huge tables (millions of rows) - you’ll get other performance issues, for example - deleting a record which will trigger reindex, etc.. I resolve those by doing the heavy tasks via cron job - hence not to affect the user’s experience. but these should not happen in 10k table at all.

d. side note, I don’t use it, but take a quick look at this: https://github.com/netProphET/xPDO-Collection-Tools

Wait, you’re grabbing an entire 10,000 rows on one request, without a LIMIT? That’s going to make some huge performance hits, since xPDO creates an object for each. You have a few options:

1. Use PDO directly, ie, $stmt = $modx->query(’SELECT * FROM table’) and then $stmt->fetch...etc. Just remember to closeCursor when through.

2. Use $modx->getIterator(’myClass’); instead of getCollection. It will return an iterated result set, which you can loop over, and it will only grab records as you need them. You might see some huge performance improvements using this call alone.

Use $modx->getIterator(’myClass’); instead of getCollection. It will return an iterated result set, which you can loop over, and it will only grab records as you need them. You might see some huge performance improvements using this call alone.

getIterator is new or something? I never saw it earlier. I will move the heavy spots to use it right away.
is there an ’trick’ to iterate a graph? (equivalent to getCollectionGraph) ? or only to do it ’manually’ with joins, as your first proposal?

EDIT: I just saw this: "There is no getIteratorGraph yet" http://modxcms.com/forums/index.php/topic,59685.msg339805.html#msg339805

thanks

Quote from: oori at Feb 22, 2011, 02:52 PM

Use $modx->getIterator(’myClass’); instead of getCollection. It will return an iterated result set, which you can loop over, and it will only grab records as you need them. You might see some huge performance improvements using this call alone.

getIterator is new or something? I never saw it earlier. I will move the heavy spots to use it right away.

It’s new as of 2.0.7, yes.

is there an ’trick’ to iterate a graph? (equivalent to getCollectionGraph) ? or only to do it ’manually’ with joins, as your first proposal?
thanks

I dont think there’s a getIteratorGraph at the moment, although you could ask Jason specifically.

I dont think there’s a getIteratorGraph at the moment, although you could ask Jason specifically

thanks.

moving this discussion to:
http://modxcms.com/forums/index.php/topic,59685.msg339805.html

Sorry for getting back on this topic after such a long time, but the problem has subsided as far as I know. I am now however busy with a table with approx 141.000 rows. It takes a query a little over 200ms to get a result, which is acceptable.

Now, I’ve looked into the getIterator but I can’t seem to find out what changed? Does it only fetch the records and fetches aggregates and composites on-demand? As far as I know, there are 3 methods; getCollection, getCollectionGraph and getIterator. Now, for a single you’ve got getObject. Aside from the first two from both, I have NO idea wht Graph and Iterator do.

Can you give me a short example splittingred?

Thanks a bunch.

Aside from that, is there a way to count rows like getNumRows() in native PHP? I’m now using getCount but seeing that I got the upper part a bit wrong, perhaps I am missing this as well.

The getIterator() method doesn’t fetch any objects until they’re used. It’s used exactly like getCollection().



The getObjectGraph() and getCollectionGraph() methods allow you to get objects and their related objects in the same query. I don’t think getIteratorGraph() exists yet, but I could be wrong.