Startup takes long time, depending on graph size

[laeuter]

I have started a graphd database on an iMac (Late 2012, 8 GB RAM, Fusion Drive, macos 10.14) and started to fill it with my word database. It has approximately 63 million nodes and 250 million edges (ranging from pure links to links with integer values and further link attributes). Filling the graph with gld has varying speeds, from 7000 primitives per hour up to millions per hour. Sometimes insertion appears to halt, once I dared to stopped gld and restarted the computer. Restarting graphd with the current state of the graph then took 2:09 h, with a graph of then 121 million primitives. It was not a CPU issue, graphd never used more than 2 % of a CPU. But it read gigabytes of SSD/hard disc during that time. With this kind of startup performance the graph is not ready to use, esp. considering that I have 170 million more primitives to add to the graph until it contains the same amount of data as my original copy in a standard MariaDB.

Is it clear what takes so long during startup? Is it possible to better configure the system in order to improve startup and/or insert time? Am I using graphd wrong or do I expect the impossible?

[teeler]

I'm impressed you got gld to work! ;)

121m primitives is pretty sizable - but 2 hours is indeed waaaaaaay too long. We used to run graphd with ~400m primitives and it never took that long (maybe 10, 20m to startup?). But perhaps memory is your contender...graphd operates by keeping everything in RAM, and 8GB might be on the shy side for your dataset. It might be slow because of swapping...

Whats the size of your database on disk? Also, can you share your dataset, so I can do some experiments?

[laeuter]

gld was essentially ready to be used. The usage string in its source made me use the switches "-a -t 20", and reading the processing of a line told me that I would use the normal graphd insert syntax.

Piping to graphd -y was more tedious, because there were output lines with addb error messages, but the rows were still present in the database. So I don't know what the addb error had for consequences.

If graphd holds everything in memory, then my computer is the wrong hardware for the job. I don't know what size is the interesting number (The complete db directory? All files excluding some patterns?). I have seen 2 files of 6GB+ and at least 2 more files of 5GB+ in different directories. But I have not surpassed half the number of primitives yet, so I guess, an adequate machine is beyond my reach.

I won't have access to that computer for 2 weeks from now, so I cannot give you real numbers. The MariaDB is 20GB including indices, a bz2-compressed dump should be about 10 GB.

The data comprises my research results from 1998 to 2005. Tons of semantic relations between German word forms and lemmas from 31 million sentences. The texts have some legal restrictions so I won't be able to share the sentences. I could provide links and non-sentence nodes, but that still requires (for me) serious bandwidth.

[teeler]

$ du -sh <path/to/dbdir> should suffice for DB size. Primitive size can vary wildly depending on what you put in it.

I'm glad that gld was easy for you to figure out - piping to graphd -y is probably not the right answer anyway. Can you tell me a bit more about how you're running graphd and what you're putting into each primitive?

Historically we ran graphd with very little actual data in graphd - it was mostly meant for storing metadata (we would keep other data in a type of blob store). I think we had around ~400M primitives at one point running with...128GB RAM? We had some room to spare there, though...

[laeuter]

I am simply running sudo bazel-bin/graphd/graphd -d wnt. I think I started using sudo due to the pid file. From another terminal I am piping text files to gld: cat links4.mw | bazel-bin/gld/gld -a -t 20 > links4.guid.

You can get an idea of my graph structure with some entries (comments start with //): // 00 node types write(name="NodeType") write(name="LinkType") write(name="NodeProp") write(name="LinkProp") // 04 node types (count=26) write(typeguid=40000000400000004000000040000000 name="Word form") write(typeguid=40000000400000004000000040000000 name="Lemma") write(typeguid=40000000400000004000000040000000 name="Link") write(typeguid=40000000400000004000000040000000 name="Neighbors") write(typeguid=40000000400000004000000040000000 name="FlexPattern") // 09 link types (count=40) write(typeguid=40000000400000004000000040000006 name="LemmaLink") write(typeguid=40000000400000004000000040000006 name="GrammarLink") write(typeguid=40000000400000004000000040000006 name="NeighborLink") // 0c word forms with count in corpus (count=31 million) write(typeguid=40000000400000004000000040000004 name="Faust" valuetype="integer" value="1234") write(typeguid=40000000400000004000000040000004 name="Erster" valuetype="integer" value="123456") write(typeguid=40000000400000004000000040000004 name="Teil" valuetype="integer" value="12345") // 0f lemmas with count in corpus (count=20 million) write(typeguid=40000000400000004000000040000005 name="Faust" valuetype="integer" value="4123") // 10 flexion types (count=500) write(typeguid=4000000040000000400000004000000c name="f(,,,,\"e,\"e,\"en,\"e)") // 11 grammar links (count=40 million) write(typeguid=40000000400000004000000040000009 left=4000000040000000400000004000000c right=4000000040000000400000004000000f) write(typeguid=4000000040000000400000004000000a left=4000000040000000400000004000000f right=40000000400000004000000040000010) // 13 neighbor cooccurrences with corpus count, value of link is significance (count=5 million) write(typeguid=40000000400000004000000040000007 name="[Erster, Teil]" valuetype="integer" value="354") write(typeguid=4000000040000000400000004000000b left=typeguid=4000000040000000400000004000000d right=4000000040000000400000004000000e valuetype="integer" value="897" (<- left right=40000000400000004000000040000013))

And then there are sentences (name carries up to 256 chars) with links to word forms, sentence cooccurrences (same pattern as neighbor cooccurrences), parse trees for the sentences, several lemma proximity measures and semantic relationships. They are mostly represented as links with integer values.

I suspect that all name and value fields are indexed. My values (counts, significances) suffer from Zipf's Law, i.e. there are runs of small values, but then singular large values that are rare. I don't benefit of the indexing of any values.

Hope that gives you an impression.

[teeler]

That looks good to me!

Whats the output of du -sh on your database dir?

You can specify the path for the pid file to avoid running as root, too.

I suspect that all name and value fields are indexed. My values (counts, significances) suffer from Zipf's Law, i.e. there are runs of small values, but then singular large values that are rare. I don't benefit of the indexing of any values.

They are, as are all other fields (to a degree - things that are very popular by linkage for example are indexed).

[laeuter]

I am not able to provide disk sizes until I get back to that computer on December 8th. Sorry 8-)

[teeler]

Ah ok, well so you an continue without me: my guess is that you're database is > 8GB in size and you're swapping heavily while trying to startup...on a mac I'd try using some combination of vm_stat 1 and iostat 1 while loading your database and see if that also confirms it.

I mean, if you're DB is ~ 8GB then I can tell you definitively you're going to have a bad time, but to try it yourself, do the iostat/vm_stat thing and see for yourself...

Sadly this is graphd by design - there is some improvement with using SSD's instead, the best thing to do would be to just have more RAM but if you can't, fast disks are a good runner up. I'm happy to discuss more of the history and intentions/use cases with you if you're curious (i'm on Slack, feel free to ping me there).

But yea not much else we can do I'm afraid - I'll leave this open until you can confirm database sizes.

[laeuter]

Thank you for confirming the dependence on RAM sizes. I won't be able to upgrade that iMac to RAM sizes adequate for that graph. I'm sure had already seen 4 files of more than 5 GB each, without having accumulated all the sizes.

Yes, mystery solved. I will come back with concrete db sizes. Please consider the RAM-boundedness to be an documentation item for 'Requirements'. Thanks for clearing things up!

I will have to find another storage for my graph. I will continue to consider graphd for partial graphs of that database.

[laeuter]

So here are my observations of today's startup: .../wntdb> du -h . 1,5G ./hmap/gm/large 0B ./hmap/gm/bgmap 2,5G ./hmap/gm 6,9G ./hmap 4,1M ./bmap 8,5G ./primitive 9,4M ./from/large 0B ./from/bgmap 730M ./from 0B ./scope/bgmap 0B ./scope 20M ./type/large 285M ./type/bgmap 322M ./type 172M ./to/large 0B ./to/bgmap 693M ./to 17G .

08:12:29 > sudo bazel-bin/graphd/graphd -d wntdb -i tcp://127.0.0.1:8100

08:22:45 .../wntdb> vm_stat 1 Mach Virtual Memory Statistics: (page size of 4096 bytes) free active specul inactive throttle wired prgable faults copy 0fill reactive purged file-backed anonymous cmprssed cmprssor dcomprs comprs pageins pageout swapins swapouts 3758 899968 538721 376790 0 277726 27559 4439174 361480 1731226 1012 21818 962992 852487 0 0 0 0 988907 5 0 0 3574 901218 538277 376782 0 277054 28653 303 0 639 0 2 962720 853557 0 0 0 0 450 0 0 0 3646 901403 538087 376773 0 276922 28753 323 0 117 0 0 962726 853537 0 0 0 0 509 0 0 0 3612 901553 537927 376773 0 276925 28753 276 0 80 0 0 962734 853519 0 0 0 0 518 0 0 0 3963 901715 537760 376766 0 276792 28753 361 0 161 0 1 962733 853508 0 0 0 0 520 0 0 0 3842 901957 537525 376766 0 276807 28720 424 0 196 0 0 962668 853580 0 0 0 0 469 0 0 0 4055 902150 537334 376766 0 276652 28753 257 0 58 0 1 962666 853584 0 0 0 0 523 0 0 0

08:22:53 .../wntdb> iostat 1 disk0 disk1 cpu load average KB/t tps MB/s KB/t tps MB/s us sy id 1m 5m 15m 13.22 229 2.95 22.13 117 2.54 4 4 92 1.74 1.64 1.20 4.88 59 0.28 20.51 125 2.51 2 2 96 1.74 1.64 1.20 4.34 47 0.20 16.47 120 1.93 0 2 98 1.74 1.64 1.20 5.07 56 0.28 16.29 137 2.17 1 1 98 1.74 1.64 1.20 4.73 60 0.28 14.10 115 1.59 1 1 98 1.74 1.64 1.20 6.00 16 0.09 14.45 136 1.92 1 2 97 1.76 1.65 1.20

09:52:52 end of startup

echo 'read(GUID=0401a8c0400006478000000009b13924)' | bazel-bin/gld/gld -a ok ((-> 0401a8c0400006478000000009b13924 "#0401a8c040000647800000000000001f" null null null null true true 2018-11-25T13:43:26.0001Z 0401a8c04000064780000000033fde0d 0401a8c0400006478000000009b13923))

So I have imported 162,609,445 primitives, startup was a bit faster than last time with 40 million fewer primitives. I don't know if it makes a difference: Before the 2:09 h startup I hadn't stopped the server properly with graphd -z. After the last importing session before this 1:40 h startup I had stopped the server properly.

[laeuter]

After 182458580 primitives we are at .../wntdb> du -h . 1,7G ./hmap/gm/large 0B ./hmap/gm/bgmap 2,8G ./hmap/gm 7,2G ./hmap 4,1M ./bmap 9,1G ./primitive 9,6M ./from/large 0B ./from/bgmap 902M ./from 0B ./scope/bgmap 0B ./scope 18M ./type/large 310M ./type/bgmap 345M ./type 172M ./to/large 0B ./to/bgmap 833M ./to 18G .

[laeuter]

I just declared my graph finished, at 229.8 million primitives, sparing the word-contained-in-sentence relationships. Before the last 49 million primitives being inserted, graphd had a startup time of 20 minutes, however that may have come. I have no idea about the vastly varying startup times. Insert speeds appear to vary, depending on indexes touched: I was able to insert nodes at 3 million per hour when names were different from all other material in the graph and linking to nodes that were not very well connected before.