Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Mar 2012
    Posts
    5

    Unanswered: DB2 on AIX Asynchronous vs Synchronous

    I'm having difficulty understanding the terms 'Asynchronous' and 'Synchronous' in terms how they are used for Reads and Writes.

    Can someone kindly provide a laments definition of 'Asynchronous' and 'Synchronous' and a real-time practical example that will explain the differences/adv's/disadv's between the 2?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Synchronous I/O means that the agent requesting the operation has to wait until I/O completes before it can continue processing. Asynchronous I/O happens when reads or writes are performed before an agent may request them (by means of prefetching or proactive page cleaning, for example), so the agent would not need to wait for I/O to complete. The latter is obviously more efficient.

  3. #3
    Join Date
    Mar 2012
    Posts
    5
    For Synchronous, what exactly do you mean by:
    'agent requesting the operation has to wait until I/O completes before it can continue processing'? What I/O are you referring to?

    For eg: If a user runs a Select (*) from a table that will retrieve 10000 rows that are stored accross 100 pages:

    What would a synchronous read look like versus an asynchronous read in the above situation?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    All data must be placed in the DB2 bufferpools before it can be processed by a SQL statement (except for LOB column data). If the page that has the needed row (DB2 processes data in pages for purposes of physical I/O) is already in the bufferpool, then no physical I/O is required. In some cases, the data may not already be in the bufferpool before the SQL statement starts, but DB2 does look-ahead I/O before the specific page needed is requested by the SQL statement (this is called prefetch and happens most often when a complete table scan is performed, since DB2 knows you will eventually need every single page for that table to be placed in the bufferpool).

    A LOB column (at least the portion not Inlined in 9.7) always requires synchronous I/O since it is not stored in bufferpools.

    When data is inserted, updated, or deleted, the changes to the data are written to the appropriate page in the bufferpool. If the page is already in the bufferpool, no synchronous I/O takes place for the table and index pages. If the data is not in the bufferpool, synchronous I/O is needed to put the page in the bufferpool so the insert, update, or delete can take place.

    When a commit is issued by the application, then there is synchronous I/O when the transaction log buffers are written to disk (to make sure DB2 can recover the transaction if the server crashes), but there is still no synchronous I/O that takes place for the data in the bufferpool (table and index pages). The table and index data will be written asynchronously to disk at a later time when DB2 thinks it is the best time to do so, in order to not adversely impact performance. DB2 decides when to update the table and indexes pages to disk, although you can indirectly affect how often that happens with various DB2 config parms (but it will always be asynch with the commit).

    In general, you cannot directly choose synchronous vs asynchronous I/O and DB2 makes the choice (except in the case of whether you use LOB columns that are not Inlined and I/O will always be synchronous). You can minimize synchronous I/O by having sufficient size bufferpools (along with the best mix of tablespace to bufferpool assignment) and setting other DB2 config parms appropriately.
    Last edited by Marcus_A; 03-11-12 at 13:23.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by DB2_BGNR View Post
    For Synchronous, what exactly do you mean by
    A simplest analogy would be pizza pickup vs. delivery.

    Mr. Asynchronous orders pizza by phone and, while it's being baked and delivered, continues to do whatever he was doing before picking up the phone. When the pizza arrives, he consumes it.

    Mr. Synchronous drives to the pizza place, makes his order, and waits, tapping his fingers on the counter, while it's being baked. He can't do anything useful until his pizza is ready for consumption.

    Quote Originally Posted by DB2_BGNR View Post
    For eg: If a user runs a Select (*) from a table that will retrieve 10000 rows that are stored accross 100 pages:

    What would a synchronous read look like versus an asynchronous read in the above situation?
    The application sends the query and starts waiting for results.
    -The agent compiles the query and requests all table blocks from the data service layer
    -and starts waiting for results.
    --The data services layer finds out that none of the pages are present in the bufferpool.
    --It sends a request to read 100 pages to the operating system kernel. The agent
    --still waits.
    ---The first of the OS read calls returns, placing the first page into the bufferpool.
    ---The agent is waiting.
    --The data service layer notifies the agent that the first data page is available.
    -The agent composes the first few rows of the result set and sends them back to the
    -application. Until now that was a synchronous read, because the agent had to wait
    -for the first page to arrive in the bufferpool.
    ---Meanwhile, the second page is placed into the bufferpool. This read was asynchronous
    ---because the agent was not waiting for it to complete - it was busy dealing with the
    ---first page.
    -By the time the agent is about to become idle, the second page is ready to be processed.
    -The agent starts processing the second page.
    ---Meanwhile, the third page is (asynchronously) placed in the bufferpool.
    And so on.

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by n_i View Post
    A simplest analogy would be pizza pickup vs. delivery.

    Mr. Asynchronous orders pizza by phone and, while it's being baked and delivered, continues to do whatever he was doing before picking up the phone. When the pizza arrives, he consumes it.

    Mr. Synchronous drives to the pizza place, makes his order, and waits, tapping his fingers on the counter, while it's being baked. He can't do anything useful until his pizza is ready for consumption.
    NICE!
    For me it became clear after reading the Bonnie Baker's article years ago, but as you can see here IDUG : Forums : SYNC I/O & ASYNC I/O it points to a dead link now.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  7. #7
    Join Date
    Mar 2012
    Posts
    5
    Quote Originally Posted by n_i View Post
    A simplest analogy would be pizza pickup vs. delivery.

    Mr. Asynchronous orders pizza by phone and, while it's being baked and delivered, continues to do whatever he was doing before picking up the phone. When the pizza arrives, he consumes it.

    Mr. Synchronous drives to the pizza place, makes his order, and waits, tapping his fingers on the counter, while it's being baked. He can't do anything useful until his pizza is ready for consumption.



    The application sends the query and starts waiting for results.
    -The agent compiles the query and requests all table blocks from the data service layer
    -and starts waiting for results.
    --The data services layer finds out that none of the pages are present in the bufferpool.
    --It sends a request to read 100 pages to the operating system kernel. The agent
    --still waits.
    ---The first of the OS read calls returns, placing the first page into the bufferpool.
    ---The agent is waiting.
    --The data service layer notifies the agent that the first data page is available.
    -The agent composes the first few rows of the result set and sends them back to the
    -application. Until now that was a synchronous read, because the agent had to wait
    -for the first page to arrive in the bufferpool.
    ---Meanwhile, the second page is placed into the bufferpool. This read was asynchronous
    ---because the agent was not waiting for it to complete - it was busy dealing with the
    ---first page.
    -By the time the agent is about to become idle, the second page is ready to be processed.
    -The agent starts processing the second page.
    ---Meanwhile, the third page is (asynchronously) placed in the bufferpool.
    And so on.

    Thank you for your reply.

    -What would cause a Synchronous read or write to happen and how to avoid it?

    -When you say that asynchronous allwos the database agent to do other tasks while it waits for the prefetcher to return the pages, what other could the database agent be doing as the agent has been assigned for one particular task per application correct?

    -I read an article (Can't find the link at the momment) where it states that synchronous reads are preferable over asynchronous reads. Why would this be the case?


    Thanks again

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by DB2_BGNR View Post
    Thank you for your reply.

    -What would cause a Synchronous read or write to happen and how to avoid it?
    Synchronous I/O happens when DB2 needs a page and it is not already in the bufferpool. This includes for both reads and writes. You can minimize it by having sufficient large bufferpools. Synchronous writes of the log buffer to log disk happens whenever a COMMIT is issued.

    Quote Originally Posted by DB2_BGNR View Post
    -When you say that asynchronous allows the database agent to do other tasks while it waits for the prefetcher to return the pages, what other could the database agent be doing as the agent has been assigned for one particular task per application correct?
    Keep in mind that prefetch only occurs when DB2 has determined that a particular SQL will need a large number of pages and that it can predict which ones it will need to put them in the bufferpool in advance so they will be there when needed without synchronous I/O needed. Not sure I understand your question though.

    Quote Originally Posted by DB2_BGNR View Post
    I read an article (Can't find the link at the momment) where it states that synchronous reads are preferable over asynchronous reads. Why would this be the case?
    No situation that I know of, unless DB2 starts prefetching pages that will not be used by the SQL.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Mar 2012
    Posts
    5
    Quote Originally Posted by Marcus_A View Post
    Synchronous I/O happens when DB2 needs a page and it is not already in the bufferpool. This includes for both reads and writes. You can minimize it by having sufficient large bufferpools. Synchronous writes of the log buffer to log disk happens whenever a COMMIT is issued.


    Keep in mind that prefetch only occurs when DB2 has determined that a particular SQL will need a large number of pages and that it can predict which ones it will need to put them in the bufferpool in advance so they will be there when needed without synchronous I/O needed. Not sure I understand your question though.


    No situation that I know of, unless DB2 starts prefetching pages that will not be used by the SQL.
    Okay, now I'm thoroughly confused

    Your first point states that a synchronous read will occur when the page requested is not in the bufferpool. The only time that either a synchronous or asynchronous reads occur IS when pages need to be retrieved from the tablespace into the bufferpool. Having said that, you're stating (unless I have misunderstood) that anytime a page(s) is read into the bufferpool it always a synchronous read. If that's the case, when is a asynchronous read performed. (Sorry for the confusion on my part)

    Am I to understand that a synchronous read will only occur if a single page will be required to be read into the bufferpool (for example when the SQL requests one record via a where clause) whereas asynchronous reads (prefetcher) will occur if multiple pages will be required into the bufferpool?

    Also, here is the article where it states that synchronous reads are preferable:

    http://www.dbisoftware.com/blog/db2_...nce.php?id=117

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by DB2_BGNR View Post
    Okay, now I'm thoroughly confused

    Your first point states that a synchronous read will occur when the page requested is not in the bufferpool. The only time that either a synchronous or asynchronous reads occur IS when pages need to be retrieved from the tablespace into the bufferpool. Having said that, you're stating (unless I have misunderstood) that anytime a page(s) is read into the bufferpool it always a synchronous read. If that's the case, when is a asynchronous read performed. (Sorry for the confusion on my part)

    Am I to understand that a synchronous read will only occur if a single page will be required to be read into the bufferpool (for example when the SQL requests one record via a where clause) whereas asynchronous reads (prefetcher) will occur if multiple pages will be required into the bufferpool?

    Also, here is the article where it states that synchronous reads are preferable:

    DBI Software :: DB2 Performance How-To :: DB2 LUW Performance: Write I/O Optimization
    1. Not all page reads into the bufferpool are synchronous. For example, DB2 may prefetch pages into the bufferpool before they are needed. This usually only occurs when DB2 knows a particular SQL statement will need a lot of pages not already in the bufferpool (less likely for OLTP applications and very likely for data warehouse applications).

    2. Keep in mind that pages stay in the bufferpool forever (while the database is activated), unless the bufferpool is full and DB2 needs to add more pages for a specific SQL request, so it may have to kick some pages out of the bufferpool to make room. If the page being kicked out is "dirty" (has been updated but not yet written to disk) it will have to be written to disk at that time when it gets kicked out. It is not unusual for some portion of the database (depending on how the DBA sets up the bufferpools) to always be in the bufferpool and there will be no synchronous I/O for the table and index pages (except when the database is first activated and the pages are loaded the first time it is requested).

    3. Regarding the article you referenced, you need to post the exact sentence where it states that.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by DB2_BGNR View Post

    Also, here is the article where it states that synchronous reads are preferable:
    With all due respect, the only thing I can agree with on this page (DBI Software :: General :: DB2 LUW Performance: Synchronous Read Percent (SRP)) is the lemonade recipe.

  12. #12
    Join Date
    Mar 2012
    Posts
    5
    Quote Originally Posted by Marcus_A View Post
    1. Not all page reads into the bufferpool are synchronous. For example, DB2 may prefetch pages into the bufferpool before they are needed. This usually only occurs when DB2 knows a particular SQL statement will need a lot of pages not already in the bufferpool (less likely for OLTP applications and very likely for data warehouse applications).

    2. Keep in mind that pages stay in the bufferpool forever (while the database is activated), unless the bufferpool is full and DB2 needs to add more pages for a specific SQL request, so it may have to kick some pages out of the bufferpool to make room. If the page being kicked out is "dirty" (has been updated but not yet written to disk) it will have to be written to disk at that time when it gets kicked out. It is not unusual for some portion of the database (depending on how the DBA sets up the bufferpools) to always be in the bufferpool and there will be no synchronous I/O for the table and index pages (except when the database is first activated and the pages are loaded the first time it is requested).

    3. Regarding the article you referenced, you need to post the exact sentence where it states that.
    Thank you for your response.

    In short, no matter how much better an asynchronous read is over a synchronous read, there is nothing we can do to influence which one is used as it based on strictly on the SQL criteria. A synchronous or asynchronous read is performed based on the type of SQL you entered. Is this correct?

    Here is the reference to the article:

    'In the blog post on Synchronous Read Percentage (SRP) , I indicated that a high percentage of Synchronous reads was preferable for OLTP databases.'

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by DB2_BGNR View Post
    A synchronous or asynchronous read is performed based on the type of SQL you entered. Is this correct?
    No. The same statement may cause sync or async I/O in different circumstances. Again, it's only about whether the agent waits for I/O to complete (syncronous) or does not (asynchronous).

  14. #14
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by DB2_BGNR View Post
    Thank you for your response.

    In short, no matter how much better an asynchronous read is over a synchronous read, there is nothing we can do to influence which one is used as it based on strictly on the SQL criteria. A synchronous or asynchronous read is performed based on the type of SQL you entered. Is this correct?

    Here is the reference to the article:

    'In the blog post on Synchronous Read Percentage (SRP) , I indicated that a high percentage of Synchronous reads was preferable for OLTP databases.'
    DB2 (not the SQL) decides whether a synchronous or asynchronous read is required, with one exception which I will discuss later.

    In looking at the article you referenced, it appears that Scott is talking about some metrics that track when sequential prefetch does, or does not, occur, as opposed to whether a page is already in the bufferpool. For most OLTP queries, sequential prefetch (decided by DB2) is not optimal, since hopefully no SQL is reading the entire table with a single SQL statement. However some SQL statements have syntax which looks like it will read the whole table, and DB2 may therefore do prefetch, but the application actually stops fetching all the rows in the cursor before the end of cursor is reached. In this case sequential prefetch is bad since DB2 is doing I/O and taking up space in the bufferpool (for pages not already in the bufferpool) that will never be used.

    In order to turn off sequential prefetch for a particular SQL statement (instead of letting DB2 always decide for you), you can use OPTIMIZE FOR 1 ROW at the end of the SQL, which tells DB2 that you will not be reading every row in the cursor. This would only be used if the WHERE clause was not very selective and it looks like a very large number of rows need to be read.
    Here is a link that explains that:
    Guidelines for restricting SELECT statements - IBM DB2 9.7 for Linux, UNIX, and Windows
    I am not sure this is a pervasive problem in most applications, but Scott's software tools are good at identifying it, and obviously is trying to explain why his tools that report these numbers are worthwhile.

    I can only assume that Scott was not talking about bufferpool hit ratio, which is the percentage of time that a page requested for a SQL statement is already in memory (bufferpool) for either the table or index. This information is available globally and by individual SQL statement with certain DB2 snapshots and is what you should be primarily focused on. Having a high bufferpool hit ratio is very desirable and extremely important for good performance.

    If you are a beginner, I would stick to the IBM DB2 documentation, which in the PDF manuals has some good general overviews of how DB2 works.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  15. #15
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by Marcus_A View Post
    Scott's software tools are good at identifying it, and obviously is trying to explain why his tools that report these numbers are worthwhile.
    What can his tools show that db2 snapshots / db2pd don't?

Posting Permissions

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