Hi, Sergio, 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 <m...@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 [1] 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` [4]. 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 [1]. Thanks for your help! -Mark [1] https://digitalpubliclibraryofamerica.atlassian.net/wiki/display/TECH/LDP+Interactions+Overview
