ScyllaDB returning a smaller page than expected to maintain low latency, handling of tombstones

[Guy]

Originally from the User Slack

@Carlos_V: Is there a reason why a query, using gocql, will return paginated data smaller than PageSize() size, and iter.PageState() for next page will be either empty or using the result as PageState(token) won’t return anything?
Querying directly from database using cqlsh, I have a query with 207 rows, but it will only return 205, if I put limit 206 it will return 205 and 30 minutes lates it will return the 206 one.

@Marko_Ćorić: All under the same cluster key?

And after 30 minutes you see 206th record?

@Botond_Dénes: you probably have tons of tombstones

@Carlos_V: Yes, after the 30 minutes waiting the --MORE-- the 206 comes back.
This is a materializes view.
I did run a nodetool flush and repair, no change.

@Botond_Dénes: materialized views do have tombstones
repair won’t help, try nodetool compact

@Carlos_V: Let me try
still running compact, in the meantime regarding cluster key question, it a single node scylla instance and the materialized view is this:

PRIMARY KEY ((1), 2, 3, 4, 5, 6)

query:

SELECT 5, 6, ... FROM view WHERE 1 = ? AND 2 = false ORDER BY 3 DESC, 4 ASC

@Botond_Dénes: this is from the MV definition?

@Carlos_V: Yes
After 3 hours, node compact has finished, but the same issue happens, seems to be with the same 2 rows. I’m supposed to have 177 rows now. Using limit 175 is fine, with limit 176 then it wait for a long time until it comes back.
Funnily enough if I run the same query with COUNT(1) I get 177 qty.

@Botond_Dénes: looks like your tombstones could not be purged

@Carlos_V: Perhaps I don’t have enough space for that? Use% 77%

@Botond_Dénes: tombstones won’t be purged before gc_grace_seconds, this is 10 days by default

@Carlos_V: Is there a way to force?
the 10 days have passed and the data is surely returning much faster now, only a few seconds now. Count is now 187. If I do LIMIT 187 100 comes then --MORE-- and then another 85 come, and then another --MORE-- and then the 2 come after less than a second. But the go query still won’t bring these two rows.

@Botond_Dénes: Maybe there is a problem in how the go driver handles empty pages

@Carlos_V: Any change this can be looked at?

@Marko_Ćorić: can you share that part of code?

@Carlos_V: This is the way I’m fetch data

@Marko_Ćorić: Sorry for delay, I had to find our implementation since we rewrite whole production to Rust. This is code that worked for us without single problem for like 2 years

func (GameSession) Get(c *gin.Context) ([]GameSession, error) {
	nickName := c.Params.ByName("nickName")
    session, err := NewSession(nickName[:3)
	if err != nil {
		return nil, err
	}
	provider := c.Params.ByName("provider")

	// Create log
	log := logger.NewLogger(skin)

	// Create context
	ctx := context.Background()

	cql := `SELECT
			transaction_type, transaction_time, game_name, round_id, amount, round_details
		FROM game_rounds
		WHERE
			nick_name = ? AND external_type = ? AND transaction_time > ? AND transaction_time < ?`
	params := []interface{}{
		nickName, provider,
		fmt.Sprintf("%s 00:00:00.00000", c.DefaultQuery("from", time.Now().AddDate(-1, 0, 0).Format(DateTimeFormatDate))),
		fmt.Sprintf("%s 23:59:59.99999", c.DefaultQuery("to", time.Now().Format(DateTimeFormatDate))),
	}

	log.Debugf("PlayersGameSessions.Get CQL: %v", cql)
	log.Infof("PlayersGameSessions.Get PARAMS: %+v", params)

	finalResults := make([]GameSession, 0)
	var pageState []byte
	for {
		iter := session.Query(cql, params...).WithContext(ctx).PageSize(1000).PageState(pageState).Iter()
        nextPageState := iter.PageState()

		var transactionType, transactionTime, gameName, roundID, roundDetails string
		var amount int64
		for iter.Scan(&transactionType, &transactionTime, &gameName, &roundID, &amount, &roundDetails) {
			gameRound := GameSession{
				TransactionType: transactionType,
				TransactionTime: transactionTime,
				Name:            gameName,
				RoundID:         roundID,
				Amount:          amount,
			}

			// Filter freespins win (not display real/bonus) balance
			jsonResults := map[string]interface{}{}
			if gameRound.TransactionType != "freespins-win" {
				if err := json.Unmarshal([]byte(roundDetails), &jsonResults); err == nil {
					gameRound.Real = int64(jsonResults["available_real"].(float64))
					gameRound.Bonus = int64(jsonResults["available_bonus"].(float64))
				}
			}

			finalResults = append(finalResults, gameRound)
		}

		// Quit if there is no more pages
		if len(nextPageState) == 0 {
			_ = iter.Close()
			break
		}
		pageState = nextPageState
	}

	// Sort results
	sort.Slice(finalResults, func(i, j int) bool {
		return finalResults[i].TransactionTime > finalResults[j].TransactionTime
	})

	return finalResults, nil
}

@Carlos_V: Thanks @Marko_Ćorić, just managed to get back working on this now. It’s a lesson learned from scylla. So the way it was happening here is, as an example:

1. You request data (and you know there are 100 rows)
2. scylla return 90 with a next page state
3. You request data using the page stated returned
4. No rows was being returned at all but it does contain a next page state again
5. You then request data with the new next page state
6. The remaining 10 rows now have data and no next page state
   Hence why fetching is a loop is the best approach. I’m not sure if this is a bug on scylla or expected.
   Thanks for the support.

@avi: It’s expected. ScyllaDB will return a smaller page sometimes in order to maintain low latency

@Carlos_V: OK, makes sense.
The smaller make sense for me, the 0 page is more concerning I suppose.

@avi: 0 pages are sent when there is a long series of tombstones. Older versions tried to fetch at least one live row, but if you have a series of a million tombstones, a timeout is inevitable.

@Carlos_V: Thanks Avi.