SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    My sql out of memory

    Hey guys i have this site and around 4,000 ads in sql. Below is the function for deleting expired ads. Is there a way i can have it check and delete like 250 records at a time and not try to check it all at one time? Fatal error: Out of memory (allocated 117964800) (tried to allocate 71 bytes) in /htdocs/www/wp-includes/meta.php on line 574 i , tried "posts limit 0,1000" and the error goes away but doesn't delete the expired ads. Thanks


    Code PHP:
    	function a2p_delete_expired_posts() {
    		global $wpdb, $Auction2Post;
     
    		$posts = $wpdb->get_results("SELECT ID FROM " . $wpdb->prefix . "posts");
     
    		foreach ($posts as $post) {
     
    				if (a2p_is_expired_post($post->ID)) {
     
    					// let's get rid of the "bloat" when the ad expires//
     
    					delete_post_meta($post->ID, 'a2pBestOfferEnabled');
    					delete_post_meta($post->ID, 'a2pBidCount');
    					delete_post_meta($post->ID, 'a2pBuyItNowAvailable');
    					delete_post_meta($post->ID, 'a2pGalleryURL');
    					delete_post_meta($post->ID, 'a2pQuantity');
    					delete_post_meta($post->ID, 'a2pPrimaryCategoryName');
    					delete_post_meta($post->ID, 'a2pQuantitySold');
    					delete_post_meta($post->ID, 'a2pReserveMet');
    					delete_post_meta($post->ID, 'a2pSeller');
    					delete_post_meta($post->ID, 'a2pStartTime');
    					//it's expired so we're going to delete it... but first it's attachments
     
    					$attachments = $wpdb->get_results("SELECT ID FROM " . $wpdb->prefix . "posts WHERE post_type='attachment' AND post_parent='" . $post->ID . "'");
     
    					//for each attachment - delete
    					foreach ($attachments as $attachment) {
    						wp_delete_attachment($attachment->ID);
    					}
    					wp_delete_post($post->ID);
    				}
    			}
     
    	}
    Last edited by Mittineague; Aug 13, 2013 at 18:41. Reason: reformatting bbcode tags

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,146
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    First question, did you develop this "ad framework" for wordpress or are you using a third party plugin? The reason I ask, is the way they are doing deletes is horrid. There are a lot of ways this can be improved upon but I'll need a bit more information.

    First off, what does a2p_is_expired_post do? I'm assuming it is looking at meta data and determining if the end date is before today's date (thus the ad isn't being shown anymore), is that correct? Regardless can you indicate which table that data is stored in and how it relates to the posts table?

    A quick fix to this would be only returning posts you KNOW to be expired, that will greatly reduce the number of posts you loop through (and their attachments). Can you give us an idea how often ads expire and how many are usually deleted when this process is run? Are we talking 50 ads, 250, 1000?

    Depending on how many you need to be able to delete per run, a quick fix might not be the solution, and you may need to refine the innards of your foreach loops too (but we can tackle that after we solve the initial problem of returning what seems to be TOO much data to be processed)

  3. #3
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    a2p_is_expired_post = i don't know i think its intended to run that function to delete. usually there are around 50 ads at a time that need to be deleted.

    Showing rows 0 - 29 (120021 total, Query took 0.0018 sec)
    SQL query:
    SELECT *
    FROM `wp_auction2post_auctions` this is from phpmyadmin


    Showing rows 0 - 29 (12469 total, Query took 0.0007 sec)
    SQL query:
    SELECT *
    FROM `wp_posts`
    LIMIT 0 , 30

  4. #4
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,181
    Mentioned
    191 Post(s)
    Tagged
    2 Thread(s)
    Hi miniz98, welcome to the forums,

    Do you really need to SELECT everything?
    Even if you do, you should see some improvement in db performance if you specify the field names individually.

  5. #5
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know what to do. Can we negotiate money to get this fixed?

  6. #6
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,181
    Mentioned
    191 Post(s)
    Tagged
    2 Thread(s)
    If you have the free version of the auction2post plugin the author will probably ask you to get the premium version, but ask if he's fixed the memory problem first.

    If you have the premium version, then support should be included, no? Did you try its support forum?

  7. #7
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I fixed it. Thanks for all the help. You can close the thread.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •