Search code examples
tomcat7orientdborientdb-2.1

Manage of memory on query

I have a big table (a lot of columns and some rows) in my OrientDB schema.

(columns early 35.000, rows early 100.000).

When I try to query my table with a simple COUNT, like this:

SELECT COUNT(@rid) FROM myTable WHERE filters

My process occupies early 8 GB of memory.

If I try to rewrite my query using index notation, like this:

SELECT COUNT(@rid) FROM index:myIndex WHERE key = [value1, ... valueN]

My process occupies early 8 GB of memory.

First question:

I reserved for Orient 8 GB of memory, for application server, I must reserve the same memory? Because, with DISK CACHE property of Orient, the manage of memory for its, is OK, but under application server (Tomcat) I've get Out of Memory error.

Second question:

Why a simple COUNT occupies all those memory? There is a stretegy about pagination dependent by columns number?

Solution

  • Try to start db orient without Tomcat, and verify this:

    SET INDEX

    Using the index on fields used to filter data in the query, it will lead to an improvement. In OrientDB there are various types of indices, each of them provides advantages in certain situations index. The index choice therefore depends on your situation. In my tests I used 'SB-TREE'.

    enter image description here

    VERIFY RAM OCCUPATED BY ORIENTDB PROCESS

    With the default settings (OrientDB autoconfig DISKCACHE = 5.064MB (heap = 455MB), uploading 100,000 vertices of the Person class with 3 properties (id, name, city), I have the following memory values:

    • Size db = 80 MB
    • Query = SELECT COUNT(@rid) FROM Person WHERE id >= 0
    • Time Query execution = (cold) 3.57 sec. (hot) 1.88 sec.
    • Verify query used the index = explain SELECT COUNT(@rid) FROM Person WHERE id >= 0 and check that under the column "involvedIndexes" there is the index that you created (in my case ["Person.id"])
    • OrientDB process (with studio open)= 442 MB (command: ps -ef | grep orient you get idprocess, then: top -pYOUR_ID )

    Time Query: enter image description here

    Index use: enter image description here

    RAM used by orientdb's process enter image description here

    INCREASE HEAP/DISKCACHE

    if you have an "out of memory" you may try to increase the heap:

    • open the file Server.sh (for linux) or server.bat (for windows) in the /bin folder on your orientdb's location.
    • Set MAXHEAP = -Xmx2048m

    if your query is still slow after using indexes increases cache:

    • MAXDISKCACHE = "- Dstorage.diskCache.bufferSize = 8192 FOR 8GB"

    Obviously the heap values and cache depend on how much RAM you have on your system. It takes into account that increasing it too much with Ram is already saturated for OS, and more, only gets disadvantages.

    Without Tomcat running, have you got still 'out of memory ' or the count() will return the values (fast enough) ??

    If until now everything went well, you could start Tomcat and see how it behaves the RAM with 2 processes (oriendb and tomcat) active. If it is not saturated, try to re-run the query with count (). Get 'out of memory'? With the active tomcat, you should try to re-set the memory for it to be sufficient for both Tomcat and for OrientDB. (Also considering the ram used by the OS).

    EDIT

    A correct way would be, if you already know which properties have to be present in your class, immediately creates the property and on each property creates its relative index. This way as you add vertices, indices are updated automatically after the inputs. So when you run a query you're sure that the filters in the where clause will use the indexes.