[wp-hackers] Running out of memory using $wpdb

[Kevinjohn Gallagher]

I'd also suggest doing this sort of thing OUTSIDE of WordPress.
Especially if it's for reporting, and not constant end user visibility.
Using $wpdb is grand, but you're also loading all of WordPress's functionality at the same time.For one of my more heavy sites with many custom tables (why CPTs didn't become CCOs with their own DB table I'll never know), I have a Cron Job (external to WP) that runs "reports" every 60secs and dumps the output into HTML.

Sometimes, we try to do to much internal to WordPress.
Kev

> From: beau at dentedreality.com.au
> Date: Wed, 21 Mar 2012 08:43:20 -0700
> To: wp-hackers at lists.automattic.com
> Subject: Re: [wp-hackers] Running out of memory using $wpdb
> 
> I think this line was supposed to be a method call:
> 
> >  $wpdb->flush;
> 
> Are you sure it's not running out of time?
> 
> set_time_limit( 0 );
> 
> Also try adding:
> 
> define( 'SAVEQUERIES', false );
> 
> If you still need more, you might also use:
> 
> 	if ( !empty( $GLOBALS['wp_object_cache'] ) && is_object(
> $GLOBALS['wp_object_cache'] ) && !is_wp_error(
> $GLOBALS['wp_object_cache'] ) ) {
> 		if ( isset( $GLOBALS['wp_object_cache']->cache ) )
> 			$GLOBALS['wp_object_cache']->cache = array();
> 		if ( isset( $GLOBALS['wp_object_cache']->group_ops ) )
> 			$GLOBALS['wp_object_cache']->group_ops = array();
> 	}
> 
> If you're doing regular expression parsing of some sort on your data
> then there are small, inherent internal caches that cannot be cleared,
> so there's no way that I know of to regain the memory that bleeds out
> through that, other than ending the request and doing another (AJAX,
> auto-redirect, something along those lines).
> 
> You can also change your main loop to:
> 
> while ( $result = $wpdb->get_results( "SELECT * FROM $table WHERE
> status='LIVE' LIMIT $start, $count" ) ) {
> 
> and then you don't need your first query (just make sure you adjust
> your logic to get $start and $count).
> 
> HTH
> 
> Beau
> 
> On Wed, Mar 21, 2012 at 8:14 AM, Tom Barrett <tcbarrett at gmail.com> wrote:
> > Hi
> >
> > I have a custom table with 25k rows (and another joined on it with 300k
> > rows, but I haven't got to that yet).
> > The data is sitting on a box with a regular ~250M memory free. I'm trying
> > to parse the data for reporting, but cannot grab more than ~6k rows without
> > running out of memory (sometimes dying silently).
> >
> > I tried grabbing the data in pieces (grabbing 1k rows at a time), but it
> > still fails after 6k rows.
> >
> > Is there a way I can squeeze the information out using $wpdb as it comes?
> >
> > This is a snippet of what I was trying to do:
> > $total = $wpdb->get_var( "SELECT COUNT(*) FROM $table WHERE status='LIVE'"
> > );
> > $count = 0;
> > while( $count < $total ) :
> >  $wpdb->flush;
> >  $start  = $count;
> >  $count += 1000;
> >  $result = $wpdb->get_results( "SELECT * FROM $table WHERE status='LIVE'
> > LIMIT $start, $count" );
> >  echo "$count / $total\n";
> > endwhile;
> > echo "Done\n";
> >
> >
> > --
> > http://www.tcbarrett.com | http://gplus.to/tcbarrett |
> > http://twitter.com/tcbarrett
> > _______________________________________________
> > wp-hackers mailing list
> > wp-hackers at lists.automattic.com
> > http://lists.automattic.com/mailman/listinfo/wp-hackers
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers