SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    AdSpeed.com Son Nguyen's Avatar
    Join Date
    Aug 2000
    Location
    Silicon Valley
    Posts
    2,241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL UPDATE for busy site

    Hi,

    MySQL 3.23.36
    It's simply a counter increment, but it gets too busy, which locks the table and so, increase the connections (over 200 concurrent). So is there anything I could do about it? I can't switch to another DB system.

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what specifically is the problem? the updates to that table are getting backed up? or are you just running out of MySQL connections? the latter can be fixed by allowing more connections.

    upgrade to the latest MySQL (3.23.51 right now).

    are you using a query like

    UPDATE table SET col=col+1 WHERE unique_key=constant

    e.g. where it will only find 1 row? that will give the fastest UPDATE. you can probably do a couple hundred of those per second.

    make the columns the smallest type possible. don't use INT when SMALLINT or MEDIUMINT will do, etc.

    hopefully there isn't an index on the column you're actually UPDATEing. that would make the UPDATE slower since the index has to be updated, too.

    finally, you could try switching to the InnoDB table handler to get row-level locking. you need to have MySQL compiled with InnoDB support or be using the MySQL-Max binary for this.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you are doing something like a web hit counter it would be more efficient to use a perl or whatever program to rip through the weblogs and then aggregate that into a column in MySQL.

    Or, if you *must* use MySQL simply insert a new record in a table (which is non-locking) then at a predetermined time update your table with the COUNT( * ) from the aggregate table.


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
  •