You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@pekko.apache.org by "ZhiXingHeYiApple (via GitHub)" <gi...@apache.org> on 2024/10/28 04:29:03 UTC
[I] Reverse order query causes performance bottleneck of cassandra cluster [pekko-persistence-cassandra]
ZhiXingHeYiApple opened a new issue, #240:
URL: https://github.com/apache/pekko-persistence-cassandra/issues/240
CassandraJournalStatements.scala
```scala
def selectHighestSequenceNr =
s"""
SELECT sequence_nr FROM $tableName WHERE
persistence_id = ? AND
partition_nr = ?
ORDER BY sequence_nr
DESC LIMIT 1
"""
```
journal messages table of cassandra below:
```sql
CREATE TABLE IF NOT EXISTS pekko.messages (
persistence_id text,
partition_nr bigint,
sequence_nr bigint,
timestamp timeuuid,
timebucket text,
writer_uuid text,
ser_id int,
ser_manifest text,
event_manifest text,
event blob,
meta_ser_id int,
meta_ser_manifest text,
meta blob,
tags set<text>,
PRIMARY KEY ((persistence_id, partition_nr), sequence_nr, timestamp)) --- sequence_nr default is asc order
WITH gc_grace_seconds =864000
AND compaction = {
'class' : 'SizeTieredCompactionStrategy',
'enabled' : true,
'tombstone_compaction_interval' : 86400,
'tombstone_threshold' : 0.2,
'unchecked_tombstone_compaction' : false,
'bucket_high' : 1.5,
'bucket_low' : 0.5,
'max_threshold' : 32,
'min_threshold' : 4,
'min_sstable_size' : 50
};
```
When business application actors are restored from cassandra disks, there will be a large number of slow queries. Is there any way to eliminate this reverse order query? Thank you！
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] Reverse order query causes performance bottleneck of cassandra cluster [pekko-persistence-cassandra]
Posted by "Roiocam (via GitHub)" <gi...@apache.org>.
Roiocam commented on issue #240:
URL: https://github.com/apache/pekko-persistence-cassandra/issues/240#issuecomment-2443248765
> Can we just use max?
>
> ```
> SELECT max(sequence_nr) FROM $tableName WHERE
> persistence_id = ? AND
> partition_nr = ?`
> ```
>
> This stackoverflow answer says that Cassandra supports it. [stackoverflow.com/questions/41717163/query-to-select-max-value-of-a-column-using-max-using-cassandra](https://stackoverflow.com/questions/41717163/query-to-select-max-value-of-a-column-using-max-using-cassandra)
It will ignore the null value, but still sorting a large set. https://stackoverflow.com/questions/34246403/performance-of-max-vs-order-by-desc-limit-1
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] Reverse order query causes performance bottleneck of cassandra cluster [pekko-persistence-cassandra]
Posted by "pjfanning (via GitHub)" <gi...@apache.org>.
pjfanning commented on issue #240:
URL: https://github.com/apache/pekko-persistence-cassandra/issues/240#issuecomment-2442587623
Can we just use max?
```
SELECT max(sequence_nr) FROM $tableName WHERE
persistence_id = ? AND
partition_nr = ?`
```
This stackoverflow answer says that Cassandra supports it.
https://stackoverflow.com/questions/41717163/query-to-select-max-value-of-a-column-using-max-using-cassandra
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] Reverse order query causes performance bottleneck of cassandra cluster [pekko-persistence-cassandra]
Posted by "Roiocam (via GitHub)" <gi...@apache.org>.
Roiocam commented on issue #240:
URL: https://github.com/apache/pekko-persistence-cassandra/issues/240#issuecomment-2440711353
I am not sure why the Cassandra plugin needs this at recovering.
but we could optimize the execution plan by cutting the result set. i.e., `WHERE sequence_nr > ?` it will reduce the ordering on RDBMS.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org
Re: [I] Reverse order query causes performance bottleneck of cassandra cluster [pekko-persistence-cassandra]
Posted by "pjfanning (via GitHub)" <gi...@apache.org>.
pjfanning commented on issue #240:
URL: https://github.com/apache/pekko-persistence-cassandra/issues/240#issuecomment-2442674444
@ZhiXingHeYiApple as a workaround until we can fix the code, is it possible for you to add an index to `pekko.messages`?
You could have one that indexes based on `(persistence_id, partition_nr), sequence_nr DESC`. I'm not a Cassandra expert. I know more about RDBMS.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@pekko.apache.org
For additional commands, e-mail: notifications-help@pekko.apache.org