Re: [HACKERS] One process per session lack of sharing
Hi > Using TLS will slow down things noticeably though. So if we were to go > there, we'd have to make up for some constant slowdown. I can not understand why? I've read https://msdn.microsoft.com/en-us/library/windows/desktop/ms686749(v=vs.85).aspx and http://david-grs.github.io/tls_performance_overhead_cost_linux/ """ The results are quite straightforward: no overhead at all. """ 0x00404f40 <+0>: incDWORD PTR [rip+0x202382] vs 0x00404f50 <+0>: incDWORD PTR fs:0xfffc It's clear. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One process per session lack of sharing
Hi >So I think as long as process and thread have different function in OS, >use process like thread will have overhead in practice. But There are other negative things. I think parallel oriented library usually do not work with process. So Jvm integration is not exception. It is regularity. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One process per session lack of sharing
Hi > In other words, there's no theoretical reason you couldn't have adapt > a JVM to create a large shared memory segment using mmap or SysV I think even if I was the leader in OS development, I could not correctly answer your question. So just let discuss. Ok, I agree with you that there is no " theoretical reason " But in practice I think the main reason that OS(VM) developers implement this things differently. >there's no theoretical reason you couldn't Why does Os developers make threads? If there is no reason the use of thread just waste? Why do most(any) common web server or balancer use thread? May be they are bad theoretical? and so on But to be more constructive. I just don't know how to make between process things that we can easily do between threads, in most os. I don't know how to share mutable execution code. so i just cant imagine how to implement http://docs.oracle.com/javase/7/docs/technotes/guides/vm/multiple-language-support.html#invokedynamic in optimal way. I don't understand why mutex has overhead compare to critical section for windows. http://stackoverflow.com/questions/800383/what-is-the-difference-between-mutex-and-critical-section And so on. So I think as long as process and thread have different function in OS, use process like thread will have overhead in practice. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One process per session lack of sharing
Hi > This https://github.com/davecramer/plj-new is a very old project > that did work at one time which attempted to do RPC calls to the jvm to > address exactly this problem. > However "cheaply" calling jvm from sql or vice-versa is not really possible. > I do like the idea of the background worker and shared memory though. It's not opposite concepts. It's like two level cache. Somethingis best with shared memory. When "a sharing of upper layer" is best with shared process. And there is something that should not sharing at all. Any deviation is always overhead. But to be honest I really do not like "sharing". It is against human nature. And I will be really happy when there are processors with infinite performance and memory with infinite size. :))) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One process per session lack of sharing
Hi > There's https://github.com/jnr/jnr-ffi that enables to call C > functions without resorting to writing JNI wrappers. I have not said that you are wrong. It's the dark side of "like seprate process" They can cheaply call sql from jvm. And they can't cheaply call jvm from sql. Jvm in oracle appeared a long time ago. May by when java thread model had many faults.(befor jvm 1.6 for example) Nowadays it seems to have sense only for compatibility. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One process per session lack of sharing
Hi > I admit that it is risky, but I think there are things that could be > done to limit the risk. I don't believe we can indefinitely continue > to ignore the potential performance benefits of making a switch like > this. Breaking a thirty-year old code base irretrievably would be > sad, but letting it fade into irrelevance because we're not willing to > make the architecture changes that are needed to remain relevant would > be sad, too. I can add, that nowadays it seems that the paralleling processing is the only way to scale. They can't wait that CPU Clock Speeds Increased in in the coming years. I understand that use of thread has some difficulties. I can not understand why use of thread can have disadvantages. Actually I think that parallelling using threads is much easy than parallelling using processes. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One process per session lack of sharing
Hi > The issue here is an architectural mismatch between PostgreSQL and > the JVM, made worse by the user's very stored-proc-heavy code. Some > other runtime that's designed to co-operate with a multiprocessing > environment could well be fine, but the JVM isn't. At least, the > Sun/Oracle/OpenJDK JVM isn't. Actually the lack of threads make any vm quite limit in some aspects of scalability. The desire to use jvm is the result that there is no desire to reinvent the wheel. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One process per session lack of sharing
Hi > Such a host delegate process could be explicitly built with > multithread support and not 'infect' the rest of the code with its > requirements. > > Using granular RPC is nice for isolation but I am concerned that the > latencies might be high. I agree with you. Moreover I think that some decision have not sense with this "thread model" in any way. For example Embedded oracle XML DB applications: http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb23jv1.htm#i1043708 """ You can run a Java servlet. Servlets work better as the top-level entry point into Oracle Database, and require using HTTP(S) as the protocol to access Oracle Database. """ > What I know about Oracle, PL/SQL, Java - all is executed as > outprocess calls. I am sure, so PL doesn't share process with SQL engine there It's better to say that java is executed like outprocess calls. It's done for wide libraries support. But it is not separate process. """ The JDBC server-side internal driver, the Oracle JVM, the database, and the SQL engine all run within the same address space, and therefore, the issue of network round-trips is irrelevant """ http://docs.oracle.com/cd/E11882_01/java.112/e16548/overvw.htm#JJDBC28026 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One process per session lack of sharing
Hi > This is true, only when data are immutable and in memory. Elsewhere it is > false idea. For case whenthe server works 24x7 and you need ability to fix bugs(or update) on the fly in any app code. It's usual. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One process per session lack of sharing
Hi > but parallel processing doesn't requires threading support - see PostgreSQL > 9.6 features. To share dynamic execution code between threads much more easy(If sharing this code between process is possible). There is many other interaction techniques between threads which is absence between process. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One process per session lack of sharing
Hi > If amatveev (username, unsure of full name) wants to improve > PL/PgSQL performance and the ability of a JVM to share resources > between backends, then it would be more productive to focus on that than on > threading. Note, I've statred this post with https://www.postgresql.org/message-id/flat/409604420.2016071532%40bitec.ru#409604420.2016071...@bitec.ru Oracle: about 5M MSSqlServer: about 4M postgreSql: about 160М It's 11K loc of pgSql. And our code base is more than 4000k(for pgSql) lines of code. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One process per session lack of sharing
Hi > Can be nice, if we can help to all Oracle users - but it is not > possible in this world :( - there is lot of barriers - threading is > only one, second should be different design of PL/SQL - it is based > on out processed, next can be libraries, JAVA integration, and lot > of others. I believe so lot of users can be simple migrated, NTT has > statistics - 60% is migrated just with using Orafce. But still there > will be 10% where migration is not possible without significant > refactoring. The most of our customers now use oracle enterprise edition. You can know better how important this is. But I agree with you that in other cases we can use PostgreSql. We can use postgreSql with some disadvantages of pgBouncer anywhare where the scalability is not main risk.(Such customers usually don't buy Enterprise) >I don't believe so is cheaper to modify Postgres to > support threads than modify some Oracle applications. The key is Scaling. Some parallels processing just can not be divorced from data without reducing performance. It very difficult question would be it possible at all to get comparable performance at application server for such cases. If we "inject" applications server to postgreSql for that scalability and functionality we need multithreading. If customization for every project is not big. It's may be tuned. But from some point the tuning is not profitable. (The database works in 24x7 and we need the ability to fix bugs on the fly) So If for some reason we would start to use postgresql. There is always a question what to choose funcionality or scalability. And usually our customers need both. >I don't believe so is cheaper For us it's may be not cheaper. It's just imposible. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One process per session lack of sharing
Hi > I disagree - there is lot of possible targets with much higher > benefits - columns storage, effective execution - compiled > execution, implementation of temporal databases, better support for > dynamic structures, better support for XML, JSON, integration of connection > pooling, ... Off course the task is different so optimal configuration is different too. So the best balance between process per thread can change. But now he is in one extreme point. > There is only few use cases - mostly related to Oracle emulation It's few cases for one and it's most cases for others. > when multi threading is necessary - and few can be solved better - > PLpgSQL to C compilation and similar techniques. It's few cases for one and it's most cases for others. In our cases we just buy oracle and it's would be cheeper. Off course if our customers for some reason would agree to pay for that technique. We have nothing against. > The organization of work is hard, but pretty harder is doing this > work - and doing it without impact on current code base, current > users. MySQL is thread based database - is better than Postgres, or > there is more users migrated from Orace? Not. We want to decide our task by PostgreSql as easy as by Oracle. So you can say You should buy oracle and You will be right. I'm just interested if this is the position of the majority. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One process per session lack of sharing
Hi Is there any plan to implement "session per thread" or "shared sessions between thread"? > I'm personally not absolutely opposed to threading, but you'll find > it hard to convince anyone it's worth the huge work required to > ensure that everything in PostgreSQL is done thread-safely It's clear for me, I understand that organizing that work is really very hard. It's work for new segment of market in long perspective. For most open source project this is very difficult. In some case it may be not possible at all. But in the most cases there is proverb: "We make the road by walking on it" It's very important just to start. And may be the right start is to fix the Faq https://wiki.postgresql.org/wiki/FAQ#Why_does_PostgreSQL_use_so_much_memory.3F >Why does PostgreSQL use so much memory? >Despite appearances, this is absolutely normal It's not normal. It's "as is". You should use pgBouncer. See "Re: [HACKERS] One process per session lack of sharing" And it is why >there are workloads where it >fails badly - and competing database products survive a number of >scenarios where we just fall on our face > Er yeah, it really is. It's not just the mechanical changes. > It's verifying that everything's correct on all the supported > platforms. Ensuring that all the C library stuff we do is > thread-safe, all the SSL stuff, etc. Getting rid of all the > function-static variable use. Lots more. In the most cases the work can be done part by part. May be there is such parts. It's not necessary to do everything at once. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One process per session lack of sharing
Hi > It's mostly working, but there are workloads where it > fails badly - and competing database products survive a number of > scenarios where we just fall on our face. > So, I actually think it would be a good idea to think about this. Just to think. http://www.tiobe.com/tiobe_index The pl/sql has 18-th position. Where is pgSql. I've looked up the Ide for pgSql. If compare with oracle I can say there is not tools which can compete with "PlSql developer" for example. (functinality / price) https://www.allroundautomations.com/plsqldev.html?gclid=CjwKEAjw8Jy8BRCE0pOC9qzRhkMSJABC1pvJepfRpWeyMJ7CTZzlQE_PojlBO0vqGIZvVSW4jiQxShoC4PLw_wcB Why? May it because choosing another database is more profitable? I can't say for others, but for us: Offcourse We can implement some of our task in postgreSql. But when I think on full migration, it's just not real. We can contribute something but we can't work against postgreSql architecture. Our calculation shows that it is cheaper to implement "Session per thread" themselfs for example. But it's more cheaper to buy Oracle(Even if we would write from scratch). And there is just no customers which want to pay for that. Note, we don't have enough skill at postgreSql and the think that postgresql core team may do for a month, we can do for years. So in our layer we just can't attract resource for that task. At other side there is people who have infrastructure, skills and experience but they fill comfortable as is "" > there's not that much motivation to do a ton of work inside the database > to solve it there. It's clear, they work on there task. We all work on our task. But it's just a wall. It's sad. There is proverbial in russia: "It's shine and poverty of open source" May be it is this case :) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One process per session lack of sharing
Hi > On Tue, Jul 12, 2016 at 9:18 AM, Tom Lane wrote: >> amatv...@bitec.ru writes: >>> Is there any plan to implement "session per thread" or "shared >>> sessions between thread"? >>... >> so >> there's not that much motivation to do a ton of work inside the database >> to solve it there. > I agree that there's not really a plan to implement this, but I don't > ... > So, I actually think it would be a good idea to think about this. I just want to note that converting global variables to thread-specific variables. It's large work offcourse. But it's not seemed to be a ton of work. And it's the most part of refactoring for "session per thread". Offcourse that's not all. But it seemed to be the most valuable reason not to do that work. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] One process per session lack of sharing
Hi > amatv...@bitec.ru writes: >> Is there any plan to implement "session per thread" or "shared >> sessions between thread"? > No, not really. The amount of overhead that would add --- eg, the need > for locking on what used to be single-use caches --- makes the benefit > highly questionable. A two-layer cache is the best answer. > Also, most people who need this find that sticking > a connection pooler in front of the database solves their problem It has some disadvantages. Lack of temporary table for example Practical usage of that table with connection poller is highly questionable. And so on. > , so > there's not that much motivation to do a ton of work inside the database > to solve it there. It is clear. Thank you. -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] One process per session lack of sharing
Hi Is there any plan to implement "session per thread" or "shared sessions between thread"? We have analyzed the ability to contribute pgSql to jvm bytecode compiler but with current thread model this idea is far from optimal.(Vm can be different of course. But currently we use oracle and jvm is important for us) We have faced with some lack of sharing resources. So in our test memory usage per session: Oracle: about 5M MSSqlServer: about 4M postgreSql: about 160М It's discussed on pgsql-gene...@postgresql.org: http://www.mail-archive.com/pgsql-general@postgresql.org/msg206452.html >I think the "problem" that he is having is fixable only by changing how >PostgreSQL itself works. His problem is a PL/pgSQL function which is 11K >lines in length. When invoked, this function is "compiled" into a large >tokenized parse tree. This parse tree is only usable in the session which >invoked the the function. Apparently this parse tree takes a lot of memory. >And "n" concurrent users of this, highly used, function will therefore >require "n" times as much memory because the parse tree is _not_ >shareable. This is explained in: >https://www.postgresql.org/docs/9.5/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING Next interesting answer(from Karl Czajkowskiin private): > But, I search the > archives of the mailing list, and when others have previously > suggested such caching or reuse, it was immediately shot down by core > developers. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers