I've been playing with a beta version(0.9) of AgensGraph for the last few weeks.
Now, I am testing the product on my VM machine(with 2 cores and 2gb memory), and planning to install the product on a real server(x86_64 with 32 cores and 96gb memory).
While I am planning the installation, I am having a trouble to find proper parameters for the product.
Of course, since the product is based on PostgreSQL, I am pretty familiar with all the parameters which the product is using. However, since we are talking about graph data(not relational data), I am not sure whether I can allocate server memory and do hardware sizing like what I did previously on PostgreSQL servers.
It would be really helpful if someone can answer or provide guidelines for my question about database parameter configuration and hardware sizing.
For your information, my test scenario is written as below:
One of good things of AgensGraph is that most of operational experiences from PostgreSQL are applicable. So if you are familiar with PostgreSQL already and have some experiences of operating PostgreSQL in production, it will very helpful to configure AgensGraph as well.
But as you also pointed out, AgensGraph is a graph database, whose workloads are extensively random I/O access. So in order to optimize query performance, you allocate shared buffers as much as you can and prewarm database instances with graph objects. Of course, AgensGraph also can exploit filesystem buffers but if you can explicitly allocate enough shared buffers for graph data and the graph data is cached in the shared buffer, the performance can be the best.
You can use pg_prewarm extension to warm up the AgensGraph cache. You can refer this link (https://www.postgresql.org/docs/9.6/static/pgprewarm.html) to find out how to use this extension.
If you want warm up cache by a graph data, say 'test_graph', you can use the following query.
select pg_prewarm(c.oid)
from pg_class c
left join pg_namespace n on n.oid = c.relnamespace
where nspname = 'test_graph' and (relkind = 'i' OR relkind = 'r');
This query warms up caches with heap spaces and indexes of 'test_graph'. It is rather a long and complex query. But I think AgensGraph will provide more simple way to do this in near future.
And when the entire data can be cached in memory, it can be recommended to set random_page_cost = 1. This parameter means that the cost of random page scans is the same with the sequential scan. But because this influences the query optimizer to choose the optimal plan, you should be careful to change this.
And last thing, if you have a lot of concurrent users, you should also be careful to balance the size of shared_buffers and work_mem. I cannot assume anything before analyzing your workloads. But in general more concurrent clients means more usages of work_mem. So if the total amount of shared_buffers and work_mem exceeds the size of physical memory, page faults can occurs. You have to avoid this.