Tables with many columns (cpu-only)


#1

Hi there! I’m struggling to get some basic functionality work with a table of ~60k columns. I’m running a CPU-only build.

I was able to create the table as expected with each of the columns, and then performed the copy from successfully, great docs! The table looks something like this:

CREATE TABLE IF NOT EXISTS expressions (
    sample TEXT ENCODING DICT,
    ENSG00000000003_14 DECIMAL(10),
    ENSG00000000005_5 DECIMAL(10),
    ENSG00000000419_12 DECIMAL(10), ... );

I can then use copy from to load my data and confirm they have loaded properly:

> select count(*) from expressions limit 1;
EXPR$0
466

However, I have to be careful which SQL commands I execute as selecting all 60k columns, even for a single row, will lead to timeouts. select * from expressions; just hangs indefinitely.

During execution I observed the memory summary and mapd’s resource consumption via top

mapdql> \memory_summary
MapD Server Memory Usage 
CPU RAM IN BUFFER USE :    236.78 MB
GPU VRAM USAGE (in MB's)
GPU     MAX    ALLOC    IN-USE     FREE
PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND    
14468 root      20   0 10.540g 1.641g  71628 S 100.0  0.7  51:52.15 mapd_server

I built mapd from source without GPU support. The system has a lot of resources available.

ubuntu@davidcs-mapd-aggregator-0:~$ free -m
              total        used        free      shared  buff/cache   available
Mem:         246008       14871      211033          24       20103      229818
Swap:             0           0           0

Have I set up my table properly for it to be queried? Is there a way to optimize for long rows? I might try reducing the fragment size and reloading the data. Thanks!


#2

Hi,

Thanks for your observations.

I created a 60K column table with just a few (4) rows and was able to see delays in the queries but I didn’t see the timeout issue you reported.

I can select individual column or ‘reasonable’ groups of columns but if I try a * expansion style query the process takes a very long time,

The log from the * query

I0615 03:47:10.294831   621 Calcite.cpp:196] User mapd catalog mapd sql 'select * from bigcol;'
I0615 03:49:35.582175   782 BufferMgr.cpp:266] ALLOCATION slab of 4194304 pages (2147483648B) created in 10 ms GPU_MGR:0
I0615 03:53:18.996278   621 Calcite.cpp:221] Time marshalling in JNI 0 (ms), Time in Java Calcite  368701 (ms)
I0615 03:56:10.371856   621 MapDHandler.cpp:683] sql_execute-COMPLETED Total: 540077 (ms), Execution: 539855 (ms)

I see it took calcite 368 seconds to parse and generate the relation algebra (scan and project) with the 60K column projection process, before the query was passed to the execution engine to. It then took another 160 seconds to get the data back.

MapD is a column store so not really optimized for great behavior on projection of such a large number of columns. Nothing springs to mind of tweaks you could do to change these timings currently. the first place I would probably look would be to see if the Java process calcite is executing in is short of memory and maybe spending alot of time in garbage collection to bring the parse time down. Even generating a explain calcite to get the relation algenra takes 365 seconds and no data is even involved in those steps

I really need to ask if it would be a common query on your 60K column table to have to get back more than a few 10s of columns per query, as that seems to work just fine and has expected performance. What is the purpose of trying to project all 60K columns?

regards


#3

Thanks for such a quick reply! You’ve verified for me that I haven’t made any clear mistake in my modeling or configuration.

You’re correct in expecting this is an edge case and we expect most queries to span a modest number of columns.

I’d like to have the confidence that we will be able to export our database and will need to test how the timing scales with the number of rows. The provided method copy to will rely on a select statement. If it is a batch process 365 seconds is tolerable, and it was my mistake for assuming a timeout would occur, thanks for timing it out. However, if that 160 seconds goes up 40 seconds per row, we’re going to need to look to other solutions.

Perhaps there is a way to cache the calcite generation? For this use case we might just add a column that blobs together the whole vector and reconstruct on the client.

I’ll end by noting that the performance for adding a wide table was excellent and although I didn’t time it, the create table statement happened quickly too.