SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard johnn's Avatar
    Join Date
    Mar 2001
    Location
    Southern California, USA
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    time to inserts and updates

    Hi,

    I would like to know do the 'inserts' and 'updates' slow down the 'select' queries when they are executing about the same time in a database? and under what circumstances, or type of applications, etc... that I should only update or insert rows to tables in the database at certain time such as at night when no one or only few users using and access information from database?

    Thank in advance,
    John

  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)
    in MySQL with MyISAM tables (e.g. table locks), this is how it works:

    • SELECTs (read locks) can have multiple locks on one table and run at the same time
    • UPDATEs and DELETEs need exclusive write locks on a table. when an UPDATE on DELETE comes in, MySQL will write lock the table if it's free or put the write lock request in a cue until it can do the write (e.g. waiting for any reads; or writes that were put in the cue first). when a table is write locked, any SELECTs that come in have to wait (even if other SELECTs are currently running and the new query may complete before them) unless the SELECT uses HIGH_PRIORITY.

      UPDATEs, DELETEs, or INSERTs that use LOW_PRIORITY (and INSERT DELAYED) will wait until there are no SELECTs on the table (e.g. read locks). i don't like LOW_PRIORITY writes, however, because if your table has constant SELECTs running on it, the writes will be starved. it's good that the writes have higher priority than reads by default, because that way the writes get done and even if multiple SELECTs have to wait, they can all run at the same time when the write is done.
    • regular INSERTs (e.g. not LOW_PRIORITY or DELAYED) can be done simultaneously (??) and while SELECTs are running as long as 1) you're not running MySQL on Windows and 2) there is no free space in the table*


    * by no free space in the table, i mean that you haven't deleted lots of rows and then not run OPTIMIZE TABLE to reclaim this space.

    MySQL performs well with table locking if you do

    • mostly SELECTs
    • mostly INSERTs
    • both SELECTs and INSERTs


    you start to have problems with table locking if you have SELECTs that take a long time to run combined with doing a lot of UPDATEs and DELETEs that take a long time to run.

    probably more than you wanted to know.

    BTW, see these pages in the manual for this info:
    www.mysql.com/doc/en/Internal_locking.html
    www.mysql.com/doc/en/Table_locking.html
    www.mysql.com/doc/en/Locking_methods.html
    Last edited by DR_LaRRY_PEpPeR; Feb 20, 2003 at 18:56.
    - 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
    SitePoint Wizard johnn's Avatar
    Join Date
    Mar 2001
    Location
    Southern California, USA
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by DR_LaRRY_PEpPeR
    you start to have problems with table locking if you have SELECTs that take a long time to run combined with doing a lot of UPDATEs and DELETEs that take a long time to run.
    Well, I guess if a query has multiple joins with several tables in large database of about 500,000 rows, it takes a long time to run (even index properly and using integers in comparing), so I'd better run inserts, updates, and deletes at night when not many users online, so that people can as many selects as they want during the day.

    Thanks dr.


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
  •