Thanks Craig, As I'm back in pg code after many years, I'm feeling much better there's one (1) or two (2) items causing the hiccup. Rereading your comments, I'm agreeing with you. I'm considering bumping up the ram to 512gb as a RAM disk just for consistent profiling (an old bad habit).
I'm reassessing my profiling plan. I'm getting ahead of myself thinking the OS is not having a significant effect. I'm using RHEL7.4, SeLinux MLS enabled using the C2S profile and some additional lock-downs applied. As a result of lock-down scripts, "tuned" has been removed and has never been executed on the distribution. Also, the processor is a Haswell with 14 cores/28 processor threads, it may have a impact on untuned thread scheduling. I need to get the OS tuned properly for the platform. I don't like profiling apps without knowing what the underlying platform is doing. After tuning and profiling the OS, I'll restart pg profiling starting at the OS interfaces working my way in. I thought this was going to take less time, I'll need to adjust my schedule.. On Sat, Feb 10, 2018 at 4:45 AM, Craig Ringer <cr...@2ndquadrant.com> wrote: > On 10 February 2018 at 14:26, Gary M <ga...@oedata.com> wrote: > >> Thanks for the replies. >> >> @Craig >> >> Realtime ? well not really. I guess it's your definition of realtime. I >> usually think of micro to nano seconds as real-time. If I were still >> designing chips, I'd be calling picosecs real-time these days. >> > > Hence "soft realtime". > > >> "How sure are you that it's not viable for SQL queries?" >> I'm topping off at 20K/s inserts due to locking and scheduling issues. >> There are peaks reaching 50K inserts, but not sustained. I also placed the >> login on a separate disk to prevent collisions. I have backing storage with >> 50us latency and 400mb/s bandwidth, so backing storage is not the issue. >> > > Sure, but none of that has anything to do with SQL vs other access methods. > > Where's your indication that you're tackling a bottleneck, not something > unrelated? > > I expect you're probably having more issues around the relation extension > lock, wal lock etc than anything else. But you'll need to do some profiling > and diagnostic work to determine that. > > Otherwise this is all just a variant on premature optimisation. > > "And, if not, what makes you think that a lower level interface will help >> you? " >> Well, I was thinking sql parsing and planning is an unnecessary step that >> can be removed from the pipeline. Instead of parsing the data stream to >> another format and creating a query, I'll just have the stream parser(s) >> generate the table structure and write directly to the tables. >> > > A variant of that is certainly practical - and not that hard. Again, see > heap_open, heap_form_tuple, etc. But beware index maintenance. > > You will NOT bypass various write bottlenecking locks this way. > > "Has profiling and tracing/timing shown that significant time/delays are >> arising from layers you can bypass in a sensible way?" >> "Sensible" is why I'm posting the question here. I'm not familiar enough >> with the code and processing pipelines to understand subtleties effecting >> high volume insert performance. There is a lot of "stuff" going on, much >> of it event driven. My first impulse is a pipeline scheduler taking >> advantage of processor affinity. Its an ugly, brute force approach, but it >> does work. >> > > Do you have any indication CPU contention is even the issue? > > Establishing CPU affinity probably does make sense, as does optimising the > number of executors for minimal contention / max throughput. But if your > performance is actually limited by lock contention, I/O, memory throughput, > cache churn, or whatever, it may not help much. > > >> The final design will likely require some type of shared block storage. >> I've always liked the Solaris ZFS LVM layer, although is not distributed. >> Fire and forget is not an option here. I was hoping to leverage existing >> postgres facilities for that heavy lifting. That's why I had >> originally looked at the wal interface. >> > > My point is that you'll want something in front that you can use to buffer > work during short DB outages / failovers / whatever. Unless you're OK with > dropping trace data when the DB is inaccessible, or having it buffered > sender-side. > > I'm also looking at approaches from a project called "Bottled-Water" >> https://www.confluent.io/blog/bottled-water- >> real-time-integration-of-postgresql-and-kafka/ >> > > Not sure how that's relevant / will help you. It's about getting a change > stream *out* of PostgreSQL to drive Kafka. > > pglogical3 will also have some features related to feeding changes to > Kafka by the way. > > >> One more fact I forgot to add.. The insert load into the database is >> about 2kb/record or about 200MB/s. >> > > So you're presumably rotating partitions or something; ageing things out. > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
