Resources required to run queries
Hi,
Is there a way to find out how much memory is needed for queries? Thanks for helping, this forum has helped me navigating omnisci over the past few weeks!
I’m running these queries on a single gpu and received messages
create table yellowtaxiJan13_intersect_pickup as (
SELECT
ST_INTERSECTS(tx.omnisci_geo, ST_SetSRID(ST_Point(yt.pickup_longitude, yt.pickup_latitude), 4326)) as pickup_outcome,
yt.trip_id as pickup_tripid, tx.borough as pickup_borough
from yellowtaxiJan13 as yt, NYCtaxiZone as tx
);
create table yellowtaxiJan13_pickupsub_dist as (
SELECT
yt.trip_id, sb.Division, sb.Line, sb.StationName,
ST_DISTANCE(ST_SetSRID(ST_Point(StationLongitude, StationLatitude), 4326), ST_SetSRID(ST_Point(EntranceLongitude, EntranceLatitude), 4326)) as dist_m
from yellowtaxiJan13 as yt, NYCSubway as sb
);
First query error message
2020-12-03T13:46:51.601300 E 16 2 RelAlgExecutor.cpp:3031 Query execution failed **with** error Query couldn **'t keep the entire working set of columns in GPU memory**
2020-12-03T13:46:51.601480 I 16 2 RelAlgExecutor.cpp:3037 Query ran out **of** GPU memory, attempting punt **to** CPU
Second query error message
*Exception: Not enough host memory to execute the query*
I’m running them on a shared infrastructure. These are the settings and resources I was allocated.
2020-12-03T13:36:52.998849 I 16 0 CommandLineOptions.cpp:881 cuda block size 0
2020-12-03T13:36:53.001725 I 16 0 CommandLineOptions.cpp:882 cuda grid size 0
2020-12-03T13:36:53.001759 I 16 0 CommandLineOptions.cpp:883 Min CPU buffer pool slab size 268435456
2020-12-03T13:36:53.001767 I 16 0 CommandLineOptions.cpp:884 Max CPU buffer pool slab size 4294967296
2020-12-03T13:36:53.001770 I 16 0 CommandLineOptions.cpp:885 Min GPU buffer pool slab size 268435456
2020-12-03T13:36:53.001773 I 16 0 CommandLineOptions.cpp:886 Max GPU buffer pool slab size 4294967296
2020-12-03T13:36:53.001777 I 16 0 CommandLineOptions.cpp:887 calcite JVM max memory 1024
2020-12-03T13:36:53.001780 I 16 0 CommandLineOptions.cpp:888 OmniSci Server Port 6274
2020-12-03T13:36:53.001783 I 16 0 CommandLineOptions.cpp:889 OmniSci Calcite Port 6279
2020-12-03T13:36:53.001787 I 16 0 CommandLineOptions.cpp:890 Enable Calcite view optimize true
2020-12-03T13:36:53.001790 I 16 0 CommandLineOptions.cpp:893 Allow Local Auth Fallback: enabled
2020-12-03T13:36:53.148493 I 16 0 CommandLineOptions.cpp:702 OmniSci started with data directory at '/omnisci-storage/data'
2020-12-03T13:36:53.148540 I 16 0 CommandLineOptions.cpp:706 Watchdog is set to true
2020-12-03T13:36:53.148545 I 16 0 CommandLineOptions.cpp:707 Dynamic Watchdog is set to false
2020-12-03T13:36:53.148549 I 16 0 CommandLineOptions.cpp:711 Runtime query interrupt is set to false
2020-12-03T13:36:53.148552 I 16 0 CommandLineOptions.cpp:717 Debug Timer is set to false
2020-12-03T13:36:53.148556 I 16 0 CommandLineOptions.cpp:719 Maximum Idle session duration 60
2020-12-03T13:36:53.148559 I 16 0 CommandLineOptions.cpp:721 Maximum active session duration 43200
2020-12-03T13:36:53.151373 I 16 0 DBHandler.cpp:228 OmniSci Server 5.4.1-20200928-3d17eec6c1
2020-12-03T13:36:53.151460 I 16 0 ArrowCsvForeignStorage.cpp:719 CSV backed temporary tables has been activated. Create table `with (storage_type='CSV:path/to/file.csv');`
2020-12-03T13:36:54.576185 I 16 0 CudaMgr.cpp:369 Using 1 Gpus.
2020-12-03T13:36:54.576332 I 16 0 CudaMgr.cpp:68 Warming up the GPU JIT Compiler... (this may take several seconds)
2020-12-03T13:36:54.889210 I 16 0 CudaMgr.cpp:71 GPU JIT Compiler initialized.
2020-12-03T13:36:54.889566 I 16 0 DataMgr.cpp:185 Min CPU Slab Size is 256MB
2020-12-03T13:36:54.889599 I 16 0 DataMgr.cpp:186 Max CPU Slab Size is 4096MB
2020-12-03T13:36:54.889604 I 16 0 DataMgr.cpp:187 Max memory pool size for CPU is 154480MB
2020-12-03T13:36:54.889609 I 16 0 DataMgr.cpp:190 Reserved GPU memory is 384MB includes render buffer allocation
2020-12-03T13:36:54.889619 I 16 0 DataMgr.cpp:213 Min GPU Slab size for GPU 0 is 256MB
2020-12-03T13:36:54.889623 I 16 0 DataMgr.cpp:215 Max GPU Slab size for GPU 0 is 4096MB
2020-12-03T13:36:54.889627 I 16 0 DataMgr.cpp:217 Max memory pool size for GPU 0 is 15776.5MB
-
Hi @bycxgto,
To try, to figure how much memory is required to run a query you have to start the server with one or both those parameters turned to true
Verbose and enable-debug-timer.
Run your query and re-post the more detailed logs as an attachment. I will try to analyze.
I am sorry for the short answer but I am on a plane right now.
-
Hi @bycxgto,
I took a look at your queries, and I noticed that both of them are cartesian products, so the memory needed is going to be huge because, before the creation of the table, our engine needs to compute the entire result set, so the OOM Errors you are getting.
The records generated by the second query is around 26 Billion so the memory needed is going to be huge; also the first one is generation around 4 Billion records and is needing 124360000272 bytes on my system.
Have you tried to build the table doing a geojoin?
create table yellowtaxiJan13_intersect_pickup as ( SELECT ST_INTERSECTS(tx.omnisci_geo, ST_SetSRID(ST_Point(yt.pickup_longitude, yt.pickup_latitude), 4326)) as pickup_outcome, yt.trip_id as pickup_tripid, tx.borough as pickup_borough from yellowtaxiJan13 as yt, NYCtaxiZone as tx where ST_INTERSECTS(tx.omnisci_geo, ST_SetSRID(ST_Point(yt.pickup_longitude, yt.pickup_latitude), 4326)) = trueto improve performance I suggest switching the pickup/dropoff lat/lon to POINT datatype, turn to true the parameters enable-overlaps-hashjoin and enable-hashjoin-many-to-many rewrite the query this way
create table yellowtaxiJan13_intersect_pickup as ( SELECT ST_INTERSECTS(tx.omnisci_geo, yt.pickup) as pickup_outcome, yt.rowid as pickup_tripid, tx.borough as pickup_borough from yellowtaxiJan13_p as yt, nyctaxizones as tx where ST_contains(tx.omnisci_geo, pickup) = true ;About the first query, I suggest running several batches to populate the target table using the rowid virtual column to define the range of records for each batch. As an example
CREATE TABLE yellowtaxiJan13_pickupsub_dist as SELECT yt.trip_id, sb.Division, sb.Line, sb.StationName, ST_DISTANCE(ST_SetSRID(ST_Point(StationLongitude, StationLatitude), 4326), ST_SetSRID(ST_Point(EntranceLongitude, EntranceLatitude), 4326)) as dist_m from yellowtaxiJan13 as yt, NYCSubway as sb where rowid between 0 and 100000; INSERT INTO yellowtaxiJan13_intersect_pickupSELECT yt.trip_id, sb.Division, sb.Line, sb.StationName, ST_DISTANCE(ST_SetSRID(ST_Point(StationLongitude, StationLatitude), 4326), ST_SetSRID(ST_Point(EntranceLongitude, EntranceLatitude), 4326)) as dist_m from yellowtaxiJan13 as yt, NYCSubway as sb where rowid between 100001 and 200000; and so on..,Hopes this helps
-
Hope you had a good flight! I managed to run what you suggested, want to share some findings here
I created a
omnisci.conffile in/omnisci-storagevi omnisci.conf enable-debug-timer = 1 -v enable-overlaps-hashjoin = 1 enable-hashjoin-many-to-many = 1I restarted my docker with the new configuration but failed. After I removed the .conf file it is up and running again. I don't have access to
systemctlbut I'm guessing the .conf file was doing something to the docker that prevented it to start. Also isenable-hashjoin-many-to-manyavailable? I haven’t managed to locate this config in the website config page.I tried your first geojoin query without using the
.conffile, and voila! It completed within secondsomnisci admin 419-7B3k {"query_str","client","nonce","execution_time_ms","total_time_ms"} {"create table yellowtaxiJan13_intersect_pickup as ( SELECT ST_INTERSECTS(tx.omnisci_geo, ST_SetSRID(ST_Point(yt.pickup_longitude, yt.pickup_latitude), 4326)) as pickup_outcome, yt.trip_id as pickup_tripid, tx.borough as pickup_borough from yellowtaxiJan13 as yt, NYCtaxiZone as tx where ST_INTERSECTS(tx.omnisci_geo, ST_SetSRID(ST_Point(yt.pickup_longitude, yt.pickup_latitude), 4326)) = true );","tcp:localhost:42522","","38943","38944"}As for
ST_DISTANCEquery, will increasing GPU/CPU slab size and GPU memory pool size help? Would you still suggest running queries in batches? Thanks :slight_smile: -
Hi,
Thanks for the feedback, and don't worry, I guess the problem relies on -v rather than the hash joins parameter.
Anyway, if you want a complete list of available parameters on your release, run this command.
docker exec -it omnisciserver bash -c "/omnisci/bin/omnisci_server /omnisci-storage/data --help "The list is huge, so if you look for something more specific, you can filter using the grep command.
mapd@zion16:/var/lib/omnisci$ docker exec -it omnisciserver bash -c "/omnisci/bin/omnisci_server /omnisci-storage/data --help " | grep hashjoin --enable-overlaps-hashjoin [=arg(=1)] (=0) --enable-hashjoin-many-to-many [=arg(=1)] (=0)An omnisci.conf like that would work is this one
enable-overlaps-hashjoin=true enable-hashjoin-many-to-many=true enable-debug-timer=true verbose=trueAnyway, the most relevant parameter would be enable-overlaps-hash join. It will enable a kind of join specifically designed for geo-join with st_contains, but it's not going to work when generating a point at runtime with ST_point or similar.
so to take advantage of it, I defined the taxi's table with pickup and dropoff as a point
CREATE TABLE yellowtaxiJan13_p ( vendorid TEXT ENCODING DICT(8), tpep_pickup_datetime TIMESTAMP(0) ENCODING FIXED(32), tpep_dropoff_datetime TIMESTAMP(0) ENCODING FIXED(32), passenger_count SMALLINT, trip_distance FLOAT, pickup GEOMETRY(POINT, 4326) ENCODING COMPRESSED(32), ratecodeid TEXT ENCODING DICT(8), store_and_fwd_flag TEXT ENCODING DICT(8), dropoff GEOMETRY(POINT, 4326) ENCODING COMPRESSED(32), payment_type TEXT ENCODING DICT(8), fare_amount FLOAT, extra FLOAT, mta_tax FLOAT, tip_amount FLOAT, tolls_amount FLOAT, improvement_surcharge FLOAT, total_amount FLOAT);Loaded the table with copy command, the lat/lon are converted into points.
You are using a GPU with Volta archs to get a speedup of 3x with this feature enabled.
About the second query, the problem is that's is going to produce 24 Billion records and I don't get you to want to calculate, so I can't help you so much (calculating the distance between entrance and center of each station, without any relationship with the dropoff or pickup point of the taxi's trip). Let me know what you want to get with the query.
Regards
-
Hi @bycxgto,
about your second query, maybe you wanted to get the trips within a certain distance from subway stations?
a query would be this one
SELECT yt.rowid as trip_id, sb.Division, sb.Line, sb.StationName, ST_DISTANCE(CastToGeography(dropoff), CastToGeography(station)) as dist_m FROM yellowtaxiJan13_p as yt, NYCSubway as sb WHERE ST_DISTANCE(CastToGeography(dropoff), CastToGeography(station)) < 50;This query returns all the trip_id with station names that have ended within 50 meters of a Subway Station.
to run this query you would add this parameter to omnisci.conf trivial-loop-join-threshold=10000.
you can further elaborate the query using group by to get as an example the subway stations that has more trips within 100 meters.
SELECT sb.StationName, count(*) as the_count FROM yellowtaxiJan13_p as yt, NYCSubway as sb WHERE ST_DISTANCE(CastToGeography(dropoff), CastToGeography(station)) < 100 GROUP BY 1 ORDER BY 2 DESC LIMIT 20;Hope this helps
-
I was provided this spreadsheet calculator to determine amount of GPU ram needed by a query based on the number and type of hot columns * number or records used. For my use case of 10 hot columns (3x linestring, 3x double, 2x int, 2x dict32) and 400m records it was going roughly need 23GB of GPU ram.GPU RAM Sizing 2020 (make a copy).zip|attachment (21.5 KB)
-
Hi @candido.dessanti
Thanks for the suggestions, the geojoin has helped a lot with reducing size of query. And yes I popped the wrong data fields in second query. It should be the taxi pickup/ drop-off coordinates and distance away from subway stations.
My docker doesn’t seem to like the new
.conffile though. I addedtrivial-loop-join-threshold=10000and gotDictionary path /omnisci-storage/data/mapd_data/DB_1_DICT_48/DictPayload does not existwhen I ran queries.Then I removed the .conf file, started the docker, it was up for a few seconds then shut down again (then I restarted a few times but failed). Looks like there is some docker rebuild in order :smiley:
-
Yap,
the problem doesn't look to be the docker or the config file, but the filesystem where the database is stored is missing some files (a corruption?); never got a problem like that.
as for performance, if you want to load all the stations with relative distance to pickup/dropoff trips, I suggest to split in batches of 25000 to get the best troughtput (at least on my system looks to be the sweet spot). I'm getting 5189096 rowd writter per seond using 25000 and just 3662781 using 100000.
At the end of load the number of rows load will be of over 27 Billion so it's unlikely you will be able to query the table with GPU because of memory needed, and the query will fall back for CPU execution, so maybe it's better to narrow the selection.
Another trick if you have just lon/lat coordinates insted of geometry objects like points, you can try t use the distance_in_meter function, expecially if you are on RTX or no tesla cards
omnisql> select count(*) from NYCSubway_ll,yellowtaxiJan13 where distance_in_meters(station_longitude,station_latitude,pickup_longitude,pickup_latitude) < 100;
Please sign in to leave a comment.
Comments
8 comments