SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,104
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    queries are not fully executing

    Hi All,

    I have a problem with a high traffic (mysql) script page.
    What the script does is archive users that have expired according to time used up. The "sessionaccounting" table can have 1 to thousands of enties (24 columns) for a single user.
    What's happening is that sometimes when records are moved the script bails out and doesnt move data from all of the tables.

    I suspect that this is caused by concourant hits to the script ie.. the script is archiving a lot of records and the in the middle of archiving the script gets called again.

    Here is the code
    Code:
    $q1= "INSERT into asessionaccounting  select * FROM sessionaccounting where UserName = '" . $row['UserName'] . "' ";
    $update1 = mysql_query($q1) or die(mysql_error());
    
    // there are seven more similar inser/select queries here
    //then the delete (seven more of these)
    
    $qd1= "DELETE FROM sessionaccounting WHERE UserName = '" . $row['UserName'] . "' ";
    $updated1 = mysql_query($qd1) or die(mysql_error());
    The problem is that sometimes a complete archive does not happen.
    Does anyone have any ideas as to what may be happening and more importantly how can it be fixed.

    Many Thanks
    Loren
    What I lack in acuracy I make up for in misteaks

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If you want to prevent the tables from changing until your query finishes, then LOCK them

  3. #3
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,104
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dan,
    would it be something like,
    Code:
    $query = "LOCK TABLES sessionaccounting WRITE";
    if (!(@ mysql_query ($query, $connection)))
        or die(mysql_error());
    
    
    $q1= "INSERT into asessionaccounting  select * FROM sessionaccounting where UserName = '" . $row['UserName'] . "' ";
    $update1 = mysql_query($q1) 
    or die(mysql_error());
    
    $query = "UNLOCK TABLES";
    if (!(@ mysql_query ($query, $connection)))
    or die(mysql_error());
    I have never used LOCK before and a quick G gave me this.

    Edit:


    Also found this for locking multiple tables which I think is needed.
    LOCK TABLES sessionaccounting WRITE, sessionusers WRITE

    These are being locked from WRITE, will that also lock them from deleting?
    Thanks
    Last edited by lorenw; Jan 29, 2010 at 10:57. Reason: found more info
    What I lack in acuracy I make up for in misteaks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if "archive" means insert into a different table and then delete from the originating table, why not just flag the appropriate rows as inactive, and don't move them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,104
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Rudy, That is what I am doing.
    hmm need to think about that, it is a free radius database. This means that freeradius.so in the OS writes and reads to the database. I do have access to the queries that freeradius.so uses. The problem is that the system generates soo many reports and accounting from the radius database that I can see the entire system breaking without a huge dose of changes.

    That is a good idea though and am really considering biting the bullet.

    Meanwhile back at the ranch, I did put the LOCK on all radius tables before the while loop
    and UNLOCK TABLES after the loop. Everything works and no errors

    I hope I am doing it right.
    What I lack in acuracy I make up for in misteaks

  6. #6
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    What storage engine is the sessionaccounting table using?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  7. #7
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,104
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    It is MyISAM.
    What I lack in acuracy I make up for in misteaks

  8. #8
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    You might want to investigate using the InnoDB storage engine for the sessionaccounting table. MyISAM uses table level locking whilst InnoDB uses row level locking. If the table is getting lots of hits, the table level locking of MyISAM is more then likely causing a major bottleneck.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  9. #9
    SitePoint Wizard lorenw's Avatar
    Join Date
    Feb 2005
    Location
    was rainy Oregon now sunny Florida
    Posts
    1,104
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I did a search for MyISAM vs InnoDB, and sort of understood it. What I found did not mention locking and I will make the changes to InnoDB with the locks and see what happens in the next months. Sounds better than drawing pentagrams on the ground and throwing chiken bones at it

    Still need an answer to: do I lock all of the tables before the while loop or lock each table within the loop?

    My thought is all but waiting input. This will be a live environment.

    Thanks (really)
    What I lack in acuracy I make up for in misteaks


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
  •