 |
 |
On Aug 30, 2011, at 12:56 PM, Ken Irwin wrote: > I have a feeling it may be time for me to learn some grown-up programming skills, and I hope someone here might be able to help. > > I have a PHP script chewing over a large MySQL query. It's creating a handful of big associative arrays in the process, and punks out after the arrays get to 32MB. > Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 242 bytes) in analysis.php on line 41 > > I'm basically building a big associative array encoding the name of the borrowing institution, the patron type (student,faculty,staff,etc), the item barcode, and the various bits of data we want for each of these items. > > // the first time we see a barcode > $stats[$inst][$patron_type][$item_barcode][first] = $date; > $stats[$inst][$patron_type][$item_barcode][min] = $renewals; > $stats[$inst][$patron_type][$item_barcode][call] = $call_no; > //subsequent instances of the barcode > $stats[$inst][$patron_type][$item_barcode][max] = $renewals; > > Once I've chewed over all 4million records (40MB) , I spit it out into a new MySQL table that has the collated data that I want. Unfortunately this system breaks down when I get to so many millions of records. > > Is there a more efficient way of doing this kind of data transformation? I *could* not keep so much in the big stats array and instead make millions of "UPDATE" calls to the MySQL table, but that doesn't sound like a winning proposition to me. I imagine that I could also increase the memory allotment, but it will eventually get to big too. Or I suppose that I could do it all in chunks - right now I'm parsing the whole raw-data SQL table at once; I could do one institution's data at a time and buy myself some wiggle-room. > > But fundamentally, it seems to me that asking PHP to hold lots of data in an array might not be the most programmerly system; it's just what I've always done. I don't know how you're getting the data in, so I don't know how much you need to keep in memory during the operation. I'm also not familiar with how memory-efficient PHP is in general, so I can't recommend changes in that regard, either. What I can advise is two things -- When you mentioned millions of mysql 'UPDATE' calls -- that's rarely efficient. Instead, write it out to some sort of text file (tab delim, CSV, etc), and then use the 'LOAD DATA ... REPLACE' command: http://dev.mysql.com/doc/refman/5.1/en/load-data.html For this to work, you need to have your primary keys set up correctly, as that's what it'll use for determining if it should do an INSERT or an UPDATE. ... If you only need a little more than 32MB to deal with (and again, I don't know how memory efficient PHP is; it's possible that 40MB in mysql might be double the space in PHP, or even worse), you can adjust your php.ini file to change the memory limit: http://www.php.net/manual/en/ini.core.php#ini.memory-limit Of course, this is only a stop-gap measure. ... Personally, I'd go with a mix of these two, plus chunking it out by institution ... you should be able to get a list with a 'SELECT DISTINCT', and then just loop through 'em. -Joe ps. See, I made it all the way through the message without commenting that 'PHP' and 'grown-up programming' don't mix ... oh. crap. Never mind.