Forum: Building VoltDB Applications

Post: how to loop millions of rows

how to loop millions of rows
t0v
Mar 7, 2013
related to https://forum.voltdb.com/showthread.php?608-group-by-a-function-column i want to loop millions of rows, but keep hitting the "Output from SQL stmt overflowed output/network buffer of 50mb (-32k for message headers). Try a "limit" clause or a stronger predicate." issue.

e.g. my query returns 17M rows and i want to process them in 1M blocks.
1st query of SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT 1000000 OFFSET 0; works fine, but already 2nd SELECT .... OFFSET 1000000; fails.

i tried using smaller block size, but i hit the error always when OFFSET > ~1007700.

i have indexes on both of the columns that are used in the ORDER BY clause.

any idea, how to fix this?
t :)
pmartel
Mar 7, 2013
This sounds like a known system limitation with processing of multi-partition queries with large offset values, but I'd like to confirm this.
What would be useful in making this determination is the output of "EXPLAIN SELECT ..." when typed at the sqlcmd with desired values for limit and offset.

If you are running into this known limitation, the workaround that might apply is not to use OFFSET but instead to add a "WHERE" clause that filters out rows already seen -- those with ordered column value(s) < the maximum previously returned.
This can be done in a stored procedure that returns a fixed block of rows by adding something like "WHERE SORTKEY1 >= ? AND (SORTKEY1 > ? OR ( SORTKEY2 >= ? AND (SORTKEY2 > ? OR SORTKEY3 > ?) ) )".

Slightly more complicated output processing in the stored proc may be required to avoid skipping or repeating duplicates if the sort key combinations are not unique.

More generally, your system will perform better if you can partition your data to avoid multi-partition queries.

--paul
t0v
Mar 7, 2013
RETURN RESULTS TO STORED PROCEDURE
RETURN RESULTS TO STORED PROCEDURE
 LIMIT 10 OFFSET 1200000
  ORDER BY (SORT)
   RECEIVE FROM ALL PARTITIONS
    SEND PARTITION RESULTS TO COORDINATOR
     LIMIT 1200010
      ORDER BY (SORT)
       INDEX SCAN of "MY_TABLE" using "SYS_IDX_MYTBL_IDX_10035" (range-scan 1/2 cols)


i might be able to go with the workaround and modify the WHERE criteria for subsequent queries, but the logic will be quite complex.
i'll give it a try anyway :)

for this query, i cannot limit to a single partition, since the idea is to run a report of max 1 month worth of data (40M rows).
maybe Volt is not best suited for this type of operation, but that's one of the things, i'm trying to find out...

regards
t :)
pmartel
Mar 8, 2013
t,
Thank you for your quick reply. This does seem to be a case of the known issue with multi-partition OFFSET, so the workaround I described would seem to apply.

Please keep us posted about your success with the workaround and the general progress of your evaluation.
Let us know if there's anything we can do to help, or feel free to just share any information you'd like about your application.
We're always interested to hear how people are using VoltDB.

--paul
t0v
Mar 8, 2013
i implemented the looping procedure and it works.
the problem is that every select takes like 5 secs and processing each 1M batch takes 0.3-0.5 secs.
thus looping 17M records takes ~90 secs, which is way too slow :(

so i went back to the original problem and realized that if i just update all the records (without WHERE time_stamp BETWEEN ? AND ?), i can do that w/o hitting the temp table memory limit and it takes 'only' 22 secs.
doing the original query takes additional 3 secs and the total processing time is 25 secs, which we might be able to live with.

i still need to figure out, how this works with full data set.

thx for the support so far
t :)
t0v
Mar 15, 2013
now i've been trying to work with the full data set (up to 80M records).
1st problem is that loading them to a single node just crashes the node with no error message or anything. the DB just dies.
i suspect that the node runs out of memory and crashes. is that normal?

so i created another node (so, now i have a 2 node cluster with k=0).
now i can load the data over there, but running the query just kills the cluster leader (where my client connects).
again, there is no error message on the leader.
on the other node, i get

WARN: Host 0 failed
java.nio.channels.CancelledKeyException
	at sun.nio.ch.SelectionKeyImpl.ensureValid(SelectionKeyImpl.java:73)
	at sun.nio.ch.SelectionKeyImpl.readyOps(SelectionKeyImpl.java:87)
	at org.voltcore.network.VoltPort.lockForHandlingWork(VoltPort.java:164)
	at org.voltcore.network.VoltNetwork.callPort(VoltNetwork.java:381)
	at org.voltcore.network.VoltNetwork.access$200(VoltNetwork.java:85)
	at org.voltcore.network.VoltNetwork$3.run(VoltNetwork.java:252)
	at org.voltcore.network.VoltNetwork.run(VoltNetwork.java:301)
	at java.lang.Thread.run(Thread.java:679)
FATAL: K-Safety violation: No replicas found for partition: 0
FATAL: K-Safety violation: No replicas found for partition: 2
FATAL: K-Safety violation: No replicas found for partition: 4
FATAL: K-Safety violation: No replicas found for partition: 6
FATAL: K-Safety violation: No replicas found for partition: 8
FATAL: K-Safety violation: No replicas found for partition: 10
FATAL: K-Safety violation: No replicas found for partition: 12
FATAL: K-Safety violation: No replicas found for partition: 14
FATAL: Some partitions have no replicas.  Cluster has become unviable.
VoltDB has encountered an unrecoverable error and is exiting.
The log may contain additional information.


log file on the leader has nothing (after the query was issued) and on the other node i have just
2013-03-15 20:48:25,002   WARN  [Volt Network - 1] NETWORK: Host 0 failed
2013-03-15 20:48:25,002   INFO  [ZooKeeperServer] JOIN: Agreement, Sending fault data 0:-1 to 1:-1 survivors
2013-03-15 20:48:25,002   INFO  [ZooKeeperServer] JOIN: Agreement, Sent fault data. Expecting 1 responses.
2013-03-15 20:48:25,002   INFO  [ZooKeeperServer] JOIN: Agreement, Received failure message from 1:-1 for failed sites 0:-1 safe txn id 1377702224691462144 failed site 0:-1
2013-03-15 20:48:25,003   INFO  [ZooKeeperServer] JOIN: Agreement, handling site faults for newly failed sites 0:-1 initiatorSafeInitPoints {0:-11377702224691462144}
2013-03-15 20:48:25,003   INFO  [ZooKeeperServer] ZK-SERVER: Initiating close of session 0x131e93408b000000
2013-03-15 20:48:25,014   INFO  [ZooKeeperServer] ZK-SERVER: Processed session termination for sessionid: 0x131e93408b000000
2013-03-15 20:48:25,023   FATAL [LeaderAppointer-Babysitters] TM: K-Safety violation: No replicas found for partition: 0
2013-03-15 20:48:25,034   INFO  [Mailbox tracker] EXPORT: Attempting to boot export client due to rejoin or other cluster topology change
2013-03-15 20:48:25,040   FATAL [LeaderAppointer-Babysitters] TM: K-Safety violation: No replicas found for partition: 2
2013-03-15 20:48:25,056   FATAL [LeaderAppointer-Babysitters] TM: K-Safety violation: No replicas found for partition: 4
2013-03-15 20:48:25,072   FATAL [LeaderAppointer-Babysitters] TM: K-Safety violation: No replicas found for partition: 6
2013-03-15 20:48:25,088   FATAL [LeaderAppointer-Babysitters] TM: K-Safety violation: No replicas found for partition: 8
2013-03-15 20:48:25,104   FATAL [LeaderAppointer-Babysitters] TM: K-Safety violation: No replicas found for partition: 10
2013-03-15 20:48:25,120   FATAL [LeaderAppointer-Babysitters] TM: K-Safety violation: No replicas found for partition: 12
2013-03-15 20:48:25,136   FATAL [LeaderAppointer-Babysitters] TM: K-Safety violation: No replicas found for partition: 14
2013-03-15 20:48:25,646   FATAL [LeaderAppointer-Babysitters] HOST: Some partitions have no replicas.  Cluster has become unviable.


what am i missing here?

at the moment i have
   <cluster hostcount="2" sitesperhost="8" />

they are cloud servers with 12 'CPUs' and 40000MB of memory.
the DB has something like 50M rows (the combined snapshot size is 2105M)
the table where i'm operating has 46M rows and its snapshot size is only 971M + 963M.

the query that crashes the server is just a simple
UPDATE my_table SET scaled = time_stamp/900000000;



how can i find out, what's happening here?

regards
t :)
francis
Aug 7, 2013
Did you happen to increase your heap size? It could very well be that, I've run into some weird issues when I exceeded my java heap.