|Mark Breedlove||Sep 18, 2015 12:37 pm|
|Raffaele Palmieri||Sep 19, 2015 10:27 am|
|Mark A. Matienzo||Sep 19, 2015 12:02 pm|
|Sergio Fernández||Sep 22, 2015 12:22 am|
|Mark Breedlove||Sep 23, 2015 3:53 pm|
|Sergio Fernández||Sep 24, 2015 8:47 am|
|Mark Breedlove||Sep 25, 2015 12:30 pm|
|Mark Breedlove||Oct 2, 2015 12:46 pm|
|Sergio Fernández||Oct 8, 2015 4:04 am|
|Subject:||Re: Scaling Marmotta's LDP interface|
|From:||Mark Breedlove (mb...@dp.la)|
|Date:||Oct 2, 2015 12:46:12 pm|
I just wanted to get back to you with the status of our experimentation. I still need to gather some more information by running some more of our activities, but the activities that were bogged down last week are running now without the I/O wait that we were encountering. I'll report back when we've run the full range of our processes. (Including processes that do more inserts updates.)
On Fri, Sep 25, 2015 at 3:30 PM, Mark Breedlove <mb...@dp.la> wrote:
On Thu, Sep 24, 2015 at 10:47 AM, Sergio Fernández <wik...@apache.org> wrote:
From my experience Postgres takes as much as it can, but never risking the system. We normally also use 40% as orientative figure. But with such small server I'd try to give it more memory (6GB?) to see how it behaves.
OK. I'll experiment with that, too, including, likely, an upgrade of the instance type. It will require some scheduling, and will probably take me some number of days to have it done.
I've upgraded our EC2 instance to 60GB memory (r3.2xlarge) and increased shared_buffers to 23.9GB (40%).
This is the only thing that has produced a noticeable improvement of all of the things I said I'd try; but the difference is great.
I have been running two concurrent enrichment  activities after each tuning step that I'll mention here. I upgraded the server memory and shared_buffers as a last step, so I have observed the situation before and after each change. After upgrading the memory and shared_buffers, I/O wait dropped to less than 1% after an initial period of about 15 minutes for the operating system's filesystem cache to warm up.
Because that, another key aspect that dramatically improves the
performance on updates is I/O. Are you using SSD? Actual SSD, I mean, virtual SSDs are a completely different story.
We're using SSD-backed provisioned IOPS Amazon EBS volumes and EBS-optimized instances. [...] I'm not sure I'd want to go to bare-metal hardware yet, just for a database of 1 million ore:Aggregations; seems like we might want to revisit the choice of backend first. I could, however, resize the volumes again and boost the IOPS. I'll have to schedule a time to do that.
I've added a volume with the fastest requests / second rating that Amazon provides (20K IOPS) and have transferred the `triples` table to it. This actually made no difference, as (before the memory upgrade) we were still maxing out the volume's requests-per-second with the enrichment activities mentioned above.
By the way, in addition to the requests / second limit, EBS volumes deliver a maximum throughput of 320MiB / second, but we don't get close to it. The most I've seen us attain is 16 to 20 MiB / second. It's always the requests per second that gets us.
Could you point me in the right direction for the indices `cop`, `cp`, and
`literals`? We have `idx_triples_cspo` on `triples` from the Marmotta installation and created one that we called `idx_triples_c` . Are there optional indices that Marmotta doesn't create when it automatically creates the tables the first time you run it?
Well, internally each LDP-R uses its own context. Therefore all indexes that could improve the lookup would help. Try to create also at least cop. [...]
OK, that's on my to-do list and I'll let you know ... :-)
I've created an index, `idx_triples_cop` on triples (context, object, predicate) where deleted = false.
I made note of a query last week that is executed frequently, which now uses this index:
SELECT id,subject,predicate,object,context,deleted,inferred,creator,createdAt,deletedAt FROM triples WHERE deleted = false AND context = $1 AND subject = $2 AND predicate = $3 AND object = $4
If I `explain analyze` that query, PG says "Index Scan using idx_triples_cop on triples".
One query that's showing up frequently in our slow query log is now:
SELECT id,subject,predicate,object,context,deleted,inferred,creator,createdAt,deletedAt FROM triples WHERE deleted = false AND subject = $1 AND predicate = $2 AND context = $3
This is supposed to use a full index scan on idx_triples_spo, according to `explain analyze`. However, I think that the index is just so huge that a full index scan is what's still causing it to take about 6 seconds in a lot of cases.
We're apparently getting a 98% buffer cache hit rate on idx_triples_spo, according to a query on pg_statio_user_indexes. The idx_triples_spo index is 8GB in size. Another thing that I did was to increase work_mem to 100GB (again, before adding the memory), which didn't make any difference at the time, but which sounds like a good idea in general (and may be have been allowed to show its benefits after the filesystem cache was increased).
Since we only have about one million ore:Aggregations in our system now, out of 11 million, I'm going to have to see how big the triples table has to get before we run into more I/O wait issues as a result of the filesystem cache / shared buffers running out. But so far so good, insofar as the I/O wait has dropped. I will follow up later with you after we try some of our harvest and mapping activities .
Thanks for your help! -Mark