Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    Regular Coder sitNsmile's Avatar
    Join Date
    Dec 2009
    Location
    Charlotte, NC
    Posts
    358
    Thanks
    19
    Thanked 2 Times in 2 Posts

    innoDB or myISAM? (updates per second)

    Okay. So I have been trying to figure this out for a long time. starting to run some test now. but its so hard to figure out which is best.


    I know innoDB is good with higher concurrent connections (which I desperately need)

    So I have a table that has the information of the content, and I have another table that has so many +1s (such as stats, pageview sorta stats) it needs to be fast. and the locking of entire table with myISAM doesnt seem appetizing.

    whats the best configuration for a table that deals with a lot of updates per second?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,871
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    How many per second? "lot" isn't descriptive enough.

    I used to feed MyISAM tables *new* data at the rate of 1GB every 20 minutes, but of course INSERTs don't have the locking problem that UPDATEs do.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder sitNsmile's Avatar
    Join Date
    Dec 2009
    Location
    Charlotte, NC
    Posts
    358
    Thanks
    19
    Thanked 2 Times in 2 Posts
    For an example, lets just say anywhere from 500-1000 updates a second. Mostly due to pageview counters..etc

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,871
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Ugh...yeah, that does sound high enough to maybe choke MyISAM. I guess all you could do is try it. You could certainly simulate it easy enough. The good part is that if it works on a small table, it's almost surely going to work on a larger one. After all, if you do have page scope conflicts, you are more likely to have them if you have fewer pages.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder sitNsmile's Avatar
    Join Date
    Dec 2009
    Location
    Charlotte, NC
    Posts
    358
    Thanks
    19
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Old Pedant View Post
    Ugh...yeah, that does sound high enough to maybe choke MyISAM.
    Okay. but what I see the most is Locking. Where I may be using a lot of selects to identify what exist. and then a lot of updates to create the statistics. Running the selects which locks up some of the updates, I think could create discrepancies.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,871
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    If you can, don't do the SELECTs. Use updates that incorporate the SELECT as part of a single SQL statement. Our use stored procedures. You are likely killing performance, anyway, by doing a SELECT followed by UPDATE. You have to make *two* round trips from PHP (or whatever you are using) to the DB server instead of the one that a well-written stored procedure would require.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Regular Coder sitNsmile's Avatar
    Join Date
    Dec 2009
    Location
    Charlotte, NC
    Posts
    358
    Thanks
    19
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Old Pedant View Post
    If you can, don't do the SELECTs. Use updates that incorporate the SELECT as part of a single SQL statement. Our use stored procedures. You are likely killing performance, anyway, by doing a SELECT followed by UPDATE. You have to make *two* round trips from PHP (or whatever you are using) to the DB server instead of the one that a well-written stored procedure would require.
    I wasn't aware you could incorporate a SELECT inside a UPDATE, what would be an example of that? also my main reference was multiple users, such as other users are running queries while some are using updates, which I see the cause for the locks.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,871
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Example:
    Code:
    UPDATE pagehits, members
    SET pagehits.bypaidmembers = pagehits.bypaidmembers + IF(members.ispaid IS NULL,0,1),
        pagehits.bynonpaidmembers = pagehits.bynonpaidmembers + IF(members.ispaid IS NULL,1,0)
    WHERE members.memberid = $_SESSION["memberid"]
    Kind of a silly example, as I would assume if you were keeping track of $_SESSION["memberid"] you'd probably also have $_SESSION["paidmember"], but you get the idea.

    You don't really use a SELECT, you just do a multi-table UPDATE where you actually only update the one table.

    But of course you could also do something like this:
    Code:
    UPDATE pagehits SET firstvisits = firstvisits + 1
    WHERE pageid = $pagenumber
    AND ( SELECT COUNT(*) FROM pagesVisited
          WHERE pageid = $pagenumber
          AND memberid = $_SESSION["memberid"] ) = 0
    More than likely, though, what you really want are some stored procedures.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Regular Coder sitNsmile's Avatar
    Join Date
    Dec 2009
    Location
    Charlotte, NC
    Posts
    358
    Thanks
    19
    Thanked 2 Times in 2 Posts

    Question

    Hey, thanks for the help, but the actual answer to my question, if locking is a problem. should i go with innoDB?

    Also. if UPDATES are being locked because of high performance queries are running (SELECTS), do the UPDATES still enter in once the lock is over, or do I lose those updates?

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,871
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    ?? A SELECT should never block an UPDATE. Surely even MyISAM uses a two-phase commit? If MySQL is so poorly designed that a SELECT can block an UPDATE, then MySQL is the wrong product to be using. (Heck, even the mini-database engine that I created back in 1994-1995 used two-phase commit. Reads could never affect writes. And that was a throwaway DB, almost.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Regular Coder sitNsmile's Avatar
    Join Date
    Dec 2009
    Location
    Charlotte, NC
    Posts
    358
    Thanks
    19
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Old Pedant View Post
    ?? A SELECT should never block an UPDATE. Surely even MyISAM uses a two-phase commit? If MySQL is so poorly designed that a SELECT can block an UPDATE, then MySQL is the wrong product to be using. (Heck, even the mini-database engine that I created back in 1994-1995 used two-phase commit. Reads could never affect writes. And that was a throwaway DB, almost.)

    example: my complex query.
    Code:
    SELECT  SUM(is_packet_1.packet_imps) AS imp,  SUM(is_packet_1.packet_leads) AS leads  FROM is_campaign_pubs , is_packet_1, is_packet_2  WHERE is_packet_1.packet_id = is_packet_2.packet1_id   ..etc
    Tons of updates that are being locked when that above query runs are like
    Code:
    UPDATE is_packet_1  SET packet_imps=packet_imps+1  WHERE pcid = 277  AND mcid = 13397.. etc

    I have bots (crons) that run on the SELECT (sum) queries to update certain optimization tasks, but the users on the website create the +1 statements, which are being locked because the bots run so many heavy queries I wish I could tone down some how.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,871
    Thanks
    79
    Thanked 4,421 Times in 4,386 Posts
    Huh...so MySQL isn't as smart as I thought? That's sad. Dunno what to say.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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