How to know which table taking more memory

i have one omnisci mapd DB. it is taking 90% cpu. how to know which table using more memory(RAM). some of the table are backup tables, so we are not query on that table. still that will take any memory?. any query availabe to see each table wise ram use?

Hi @zachariam,

Right now, there isn’t a query or a command you can run to know which tables are taking large chunks of memory caches, but it’s on the roadmap.

Right now, you can use the \memory_cpu command of omnisql.

to warm up the caches, we will run a simple query against a small table of 120m or records

omnisql> select count(*) from flights_sk_orig where carriers_fk between 1 and 100000000 and airplanes_sk between 1 and 1000000000;
EXPR$0
72456443

the columns carriers_fk and airplanes_sk are read from the disk and placed in the cache because they are needed to filter the data

running the \memory_cpu command, we will get an output like that

omnisql> \memory_cpu
OmniSci Server Detailed CPU Memory Usage:
Maximum Bytes for one page: 512 Bytes
Maximum Bytes for node: 25606 MB
Memory allocated: 4096 MB
CPU[0] Slab Information:
SLAB     ST_PAGE NUM_PAGE  TOUCH         CHUNK_KEY
   0           0   250000      6 USED    1,17,6,0,
   0      250000   250000      4 USED    1,17,6,2,
   0      500000   250000      8 USED    1,17,6,1,
   0      750000   126201     10 USED    1,17,6,3,
   0      876201   250000      5 USED    1,17,7,2,
   0     1126201   250000      7 USED    1,17,7,0,
   0     1376201   250000      9 USED    1,17,7,1,
   0     1626201   126201     11 USED    1,17,7,3,
   0     1752402  6636206      0 FREE
---------------------------------------------------------------

the chuck key contains
number of databases: 1
number of the table: 17
number of the column: 6 and 7
the number of fragments: 0 to 3, because the table is composed of four fragments.

so we have the table 17 that’s taking 1752402 pages, and each page is 512 bytes long, so 855Mbytes

to decode, you would install sqlite3 and be very careful, connect to the internal SQLite databases that store the metadata of omnisci db, and run some queries, to know which tables are taking so much space

first check which is the name of the database 1

candido@zion-legion:/mapd_storage/data_test/mapd_catalogs$ sqlite3 omnisci_system_catalog
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> select * from mapd_databases where dbid=1;
1|omnisci|0

in this case, is omnisci, then open the database with the name of omnisci with sqlite3 and check of the table name

candido@zion-legion:/mapd_storage/data_test/mapd_catalogs$ sqlite3 omnisci
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
sqlite> select name from mapd_tables where tableid=17;
flights_sk_orig

So we know that table called flights_sk_orig is allocating 855MB of cache space.

Also temporary tables can eat some caches because temporary tables aren’t materialized on disk, so they reside in system memory whenever they are used in the queries or no.

e.g.

omnisql> create temporary table test_temporary_table as select airplanes_sk,carriers_fk from flights_sk_orig;
omnisql> \memory_cpu
OmniSci Server Detailed CPU Memory Usage:
Maximum Bytes for one page: 512 Bytes
Maximum Bytes for node: 25606 MB
Memory allocated: 4096 MB
CPU[0] Slab Information:
SLAB     ST_PAGE NUM_PAGE  TOUCH         CHUNK_KEY
   0           0   250000     30 USED    1,17,6,0,
   0      250000   250000     98 USED    1,17,6,2,
   0      500000   250000     68 USED    1,17,6,1,
   0      750000   126201    121 USED    1,17,6,3,
   0      876201   250000     99 USED    1,17,7,2,
   0     1126201   250000     31 USED    1,17,7,0,
   0     1376201   250000     69 USED    1,17,7,1,
   0     1626201   126201    122 USED    1,17,7,3,
   0     1752402    65536     65 USED    1,1073741824,4,0,
   0     1817938    32768    132 USED    1,1073741824,4,3,
   0     1850706    12288    131 FREE
   0     1862994    65536    111 USED    1,1073741824,4,2,
   0     1928530    16384    129 FREE
   0     1944914    65536     89 USED    1,1073741824,4,1,
   0     2010450     4096      0 FREE
   0     2014546   253952     64 USED    1,1073741824,1,0,
   0     2268498    20480    130 FREE
   0     2288978   253952     62 USED    1,1073741824,2,0,
   0     2542930   253952     95 USED    1,1073741824,2,1,
   0     2796882   253952    117 USED    1,1073741824,2,2,
   0     3050834    77824    137 FREE
   0     3128658   253952     94 USED    1,1073741824,1,1,
   0     3382610   253952    118 USED    1,1073741824,1,2,
   0     3636562   126976    140 USED    1,1073741824,1,3,
   0     3763538    77824    138 FREE
   0     3841362   126976    139 USED    1,1073741824,2,3,

as you can see, now we have the old caches of table 17 and new chunks of a table with id 1073741824 that eating up some space too (temporary tables id start with such a big number).
dropping that table is going to recover the space.

as you can see it’s a little tricky but it can be done.

When a more specific and simple command will be available I will reach you out here.

Best Regards,
candido