Opush
  1. Opush
  2. OP-184

Rework the slow isServerIdSynced SQL request

    Details

    • Type: Task Task
    • Status: Reopened Reopened
    • Priority: Normal Normal
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: backlog
    • Labels:
      None
    • Rank:
      6765

      Description

      /Rework the slow isServerIdSynced SQL request of the ItemTrackingDao.
      This is a suggestion, it just make a simple to check that it should work.

      Additional informations:
      Even if Mysql is not supported since OBM 3, Opush can run against an older OBM so it must work on MySQL too.

      Current behavior:
      Change this slow query which does (I think) to many useless conditions.

      EXPLAIN ANALYZE SELECT item.addition FROM opush_sync_state
      INNER JOIN opush_sync_state AS states ON
      (states.last_sync <= opush_sync_state.last_sync
      AND states.collection_id = opush_sync_state.collection_id 
      AND states.device_id = opush_sync_state.device_id) 
      INNER JOIN opush_synced_item AS item ON (states.id = item.sync_state_id) 
      WHERE item.item_id = 602 
      AND states.id = item.sync_state_id 
      AND opush_sync_state.id = 2985788 
      ORDER BY (states.last_sync) DESC LIMIT 1;
                                                                                      QUERY PLAN                                                                                 
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       Limit  (cost=23241.47..23241.47 rows=1 width=9) (actual time=653.653..653.655 rows=1 loops=1)
         ->  Sort  (cost=23241.47..23241.47 rows=1 width=9) (actual time=653.648..653.648 rows=1 loops=1)
               Sort Key: states.last_sync
               Sort Method: quicksort  Memory: 25kB
               ->  Nested Loop  (cost=8.64..23241.46 rows=1 width=9) (actual time=352.070..653.626 rows=2 loops=1)
                     ->  Hash Join  (cost=8.64..22698.40 rows=73 width=12) (actual time=350.770..653.069 rows=35 loops=1)
                           Hash Cond: ((states.collection_id = opush_sync_state.collection_id) AND (states.device_id = opush_sync_state.device_id))
                           Join Filter: (states.last_sync <= opush_sync_state.last_sync)
                           ->  Seq Scan on opush_sync_state states  (cost=0.00..20557.44 rows=283944 width=20) (actual time=0.014..365.324 rows=293696 loops=1)
                           ->  Hash  (cost=8.63..8.63 rows=1 width=16) (actual time=0.043..0.043 rows=1 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                 ->  Index Scan using opush_sync_state_pkey on opush_sync_state  (cost=0.00..8.63 rows=1 width=16) (actual time=0.035..0.037 rows=1 loops=1)
                                       Index Cond: (id = 2985788)
                     ->  Index Scan using opush_synced_item_sync_state_id on opush_synced_item item  (cost=0.00..7.43 rows=1 width=5) (actual time=0.013..0.013 rows=0 loops=35)
                           Index Cond: (sync_state_id = states.id)
                           Filter: (item_id = 602)
       Total runtime: 653.744 ms
      (17 rows)
      

      Expected behavior:
      Into a faster and simpler one:

      obm=# EXPLAIN ANALYZE SELECT item.addition FROM opush_synced_item AS item
      INNER JOIN opush_sync_state AS state ON (state.id = item.sync_state_id)
      WHERE item.item_id = 601
      AND state.id = 2985788
      ORDER BY (state.last_sync) DESC LIMIT 1;
                                                                                      QUERY PLAN                                                                                
      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       Limit  (cost=17.85..17.86 rows=1 width=9) (actual time=0.124..0.126 rows=1 loops=1)
         ->  Sort  (cost=17.85..17.86 rows=1 width=9) (actual time=0.122..0.122 rows=1 loops=1)
               Sort Key: state.last_sync
               Sort Method: quicksort  Memory: 25kB
               ->  Nested Loop  (cost=0.00..17.84 rows=1 width=9) (actual time=0.100..0.110 rows=1 loops=1)
                     ->  Index Scan using opush_synced_item_sync_state_id on opush_synced_item item  (cost=0.00..9.21 rows=1 width=5) (actual time=0.063..0.068 rows=1 loops=1)
                           Index Cond: (sync_state_id = 2985788)
                           Filter: (item_id = 601)
                     ->  Index Scan using opush_sync_state_pkey on opush_sync_state state  (cost=0.00..8.63 rows=1 width=12) (actual time=0.032..0.034 rows=1 loops=1)
                           Index Cond: (id = 2985788)
       Total runtime: 0.201 ms
      (11 rows)
      

        Issue Links

          Activity

          Hide
          Ba Tuan NGO added a comment - - edited

          From the first look of the current request SQL:

          SELECT item.addition FROM opush_sync_state
          INNER JOIN opush_sync_state AS states ON
          (states.last_sync <= opush_sync_state.last_sync
          AND states.collection_id = opush_sync_state.collection_id
          AND states.device_id = opush_sync_state.device_id)
          INNER JOIN opush_synced_item AS item ON (states.id = item.sync_state_id)
          WHERE item.item_id = ?
          AND states.id = item.sync_state_id
          AND opush_sync_state.id = ?
          ORDER BY (states.last_sync) DESC LIMIT 1;

          red color: Have the same condition

          So the request can be reduced to:

          SELECT item.addition FROM opush_sync_state
          INNER JOIN opush_sync_state AS states ON
          (states.last_sync <= opush_sync_state.last_sync
          AND states.collection_id = opush_sync_state.collection_id 
          AND states.device_id = opush_sync_state.device_id) 
          INNER JOIN opush_synced_item AS item ON (states.id = item.sync_state_id) 
          WHERE item.item_id = ? 
          AND opush_sync_state.id = ?
          ORDER BY (states.last_sync) DESC LIMIT 1;

          Result of real test: With the new request, we probably can't find the the result when an element (mail, contact, calendar ) is modified meanwhile the original request can do it by self-join and duplicate his column id

          Show
          Ba Tuan NGO added a comment - - edited From the first look of the current request SQL: SELECT item.addition FROM opush_sync_state INNER JOIN opush_sync_state AS states ON (states.last_sync <= opush_sync_state.last_sync AND states.collection_id = opush_sync_state.collection_id AND states.device_id = opush_sync_state.device_id) INNER JOIN opush_synced_item AS item ON (states.id = item.sync_state_id) WHERE item.item_id = ? AND states.id = item.sync_state_id AND opush_sync_state.id = ? ORDER BY (states.last_sync) DESC LIMIT 1; red color: Have the same condition So the request can be reduced to: SELECT item.addition FROM opush_sync_state INNER JOIN opush_sync_state AS states ON (states.last_sync <= opush_sync_state.last_sync AND states.collection_id = opush_sync_state.collection_id AND states.device_id = opush_sync_state.device_id) INNER JOIN opush_synced_item AS item ON (states.id = item.sync_state_id) WHERE item.item_id = ? AND opush_sync_state.id = ? ORDER BY (states.last_sync) DESC LIMIT 1; Result of real test: With the new request, we probably can't find the the result when an element (mail, contact, calendar ) is modified meanwhile the original request can do it by self-join and duplicate his column id
          Hide
          Antoine DUPRAT added a comment -

          Going from:

          16:07:49.927 [INFO ] {BREAKDOWN} [t39/cSync/r1/usera@adupratvm.lyon.lan] REQUEST:699 (EMAIL:595 (CASSANDRA:77 (JSON:7, OTHER:70), OTHER:518), SQL:35, CASSANDRA:14, OTHER:55)
          16:07:50.992 [INFO ] {BREAKDOWN} [t47/cSync/r2/usera@adupratvm.lyon.lan] REQUEST:283 (EMAIL:206 (CASSANDRA:36, OTHER:170), SQL:21, CASSANDRA:11, OTHER:45)
          

          to:

          16:03:22.225 [INFO ] {BREAKDOWN} [t93/cSync/r12/usera@adupratvm.lyon.lan] REQUEST:400 (EMAIL:294 (CASSANDRA:59 (JSON:1, OTHER:58), OTHER:235), SQL:26, CASSANDRA:13, OTHER:67)
          16:03:23.132 [INFO ] {BREAKDOWN} [t94/cSync/r13/usera@adupratvm.lyon.lan] REQUEST:215 (EMAIL:161 (CASSANDRA:51, OTHER:110), CASSANDRA:19, SQL:13, OTHER:22)
          
          Show
          Antoine DUPRAT added a comment - Going from: 16:07:49.927 [INFO ] {BREAKDOWN} [t39/cSync/r1/usera@adupratvm.lyon.lan] REQUEST:699 (EMAIL:595 (CASSANDRA:77 (JSON:7, OTHER:70), OTHER:518), SQL:35, CASSANDRA:14, OTHER:55) 16:07:50.992 [INFO ] {BREAKDOWN} [t47/cSync/r2/usera@adupratvm.lyon.lan] REQUEST:283 (EMAIL:206 (CASSANDRA:36, OTHER:170), SQL:21, CASSANDRA:11, OTHER:45) to: 16:03:22.225 [INFO ] {BREAKDOWN} [t93/cSync/r12/usera@adupratvm.lyon.lan] REQUEST:400 (EMAIL:294 (CASSANDRA:59 (JSON:1, OTHER:58), OTHER:235), SQL:26, CASSANDRA:13, OTHER:67) 16:03:23.132 [INFO ] {BREAKDOWN} [t94/cSync/r13/usera@adupratvm.lyon.lan] REQUEST:215 (EMAIL:161 (CASSANDRA:51, OTHER:110), CASSANDRA:19, SQL:13, OTHER:22)
          Hide
          Antoine DUPRAT added a comment -

          Preformances comparaison

          Show
          Antoine DUPRAT added a comment - Preformances comparaison

            People

            • Assignee:
              Antoine DUPRAT
              Reporter:
              Thomas HILAIRE
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: