Re: [GENERAL] prepared statement functioning range
* 高健 (luckyjack...@gmail.com) wrote: > Is there any common calculation methods for deciding the max_connections > value? max_connections is a hard limit and so you'd want to have that higher than the number of connections you actually expect to have. The general recommendation is to have the same number of connections as you have CPUs. If your system ends up being i/o bound, adding more requestors to the queue waiting for I/O isn't likely to really help, unless you have multiple I/O zones (eg; using tablespaces or similar). At that point, it really gets to be pretty specific to your environment. Note also that for batch reporting type of work can often be done using a hot-standby slave, rather than the master, eliminating that I/O load from the master system. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] prepared statement functioning range
Thanks a lot! I have understand this now. And the following: >In general you only want to have as many actual connections to PG >as you have CPU cores in your database server. This made me consider the appropriate value for max_conennections. This might be another topic I think. I am wondering how to decide the appropriate numbers of pg processes serving client. I think if there are some activities use disk heavily,there might be some different. To make it simple, I consider the following scenario: Consider there are some batch programs communicating with PG every day. Maybe I can collect the cpu run time compared with the whole time the program run for a period. If cpu runtime is closer to the whole runtime of program, I can say that cpus or cpu cores are fully used. If cpu runtime is really smaller too much than runtime of program, I can say tha cpus or cpu cores are not busy. So I can increase the value of max_connections in order to fully use cpus' ability. But on the other hand, I might need to replace disks with some more high-speed ones. Is there any common calculation methods for deciding the max_connections value? Thanks 2013/6/14 Stephen Frost > * 高健 (luckyjack...@gmail.com) wrote: > > So I can draw a conclusion: > > > > Prepared statement is only for use in the same session at which it has > > been executed. > > Prepared statements are session-local. > > > It can not be shared via multiple sessions. > > Correct. > > > That is, when in some special situations , > > > > if I have to use mulitple connections between client applicaiton and > > postgresql database, > > > > I must consider this point if I want to get benifit from prepared > > statements. > > Yes. If you're using a connection pooling system of some kind, it can > be useful to have it automatically set up all of your prepared > statements when it first connects to a new backend. If it opens new > backend connections preemptively and ensures it's always got "spares" > available, this can be done with minimal impact to the application. Or, > of course, you can simply have your application check if a given > statement has been prepared yet and, if not, prepare it before executing > it. That adds an extra round-trip to the database, of course, but you > could also cache and keep local the set of statements that you know > you've prepared for a given database connection too. > > In general, having a good connection pooler of some kind is really > critical if you're going to have a lot of application threads talking to > PG. In general you only want to have as many actual connections to PG > as you have CPU cores in your database server. > > > So I am now thinking about the reason that prepared statement can not > > cross over sessions. > > > > Maybe it is because of MVCC control? So in order to make it simple, the > > prepared statement is in one session range? > > It doesn't have anything to do with MVCC. afaik, the reason it's > implemented this way is because it was much simpler to implement as it > doesn't require any shared memory access or coordination between > backends, it's not hard to work around, and isn't a terribly often > requested feature. > > There's also a lot of backend parameters which can change what a single > 'prepare' ends up doing- search_path, constraint_exclusion, other > planner tunables, all of which need to be the same across all of the > sessions for the same plan to be the 'correct' one in all the backends, > not to mention roles and permissisons of the users involved. > > Simply put, it'd be quite a bit of work, would probably make things > slower due to the cross-backend communication required, and would really > only work for these specific "my application uses all the same prepared > statements and always connects as the same user and with all the same > parameters all the time" cases. > > Thanks, > > Stephen >
Re: [GENERAL] prepared statement functioning range
* 高健 (luckyjack...@gmail.com) wrote: > So I can draw a conclusion: > > Prepared statement is only for use in the same session at which it has > been executed. Prepared statements are session-local. > It can not be shared via multiple sessions. Correct. > That is, when in some special situations , > > if I have to use mulitple connections between client applicaiton and > postgresql database, > > I must consider this point if I want to get benifit from prepared > statements. Yes. If you're using a connection pooling system of some kind, it can be useful to have it automatically set up all of your prepared statements when it first connects to a new backend. If it opens new backend connections preemptively and ensures it's always got "spares" available, this can be done with minimal impact to the application. Or, of course, you can simply have your application check if a given statement has been prepared yet and, if not, prepare it before executing it. That adds an extra round-trip to the database, of course, but you could also cache and keep local the set of statements that you know you've prepared for a given database connection too. In general, having a good connection pooler of some kind is really critical if you're going to have a lot of application threads talking to PG. In general you only want to have as many actual connections to PG as you have CPU cores in your database server. > So I am now thinking about the reason that prepared statement can not > cross over sessions. > > Maybe it is because of MVCC control? So in order to make it simple, the > prepared statement is in one session range? It doesn't have anything to do with MVCC. afaik, the reason it's implemented this way is because it was much simpler to implement as it doesn't require any shared memory access or coordination between backends, it's not hard to work around, and isn't a terribly often requested feature. There's also a lot of backend parameters which can change what a single 'prepare' ends up doing- search_path, constraint_exclusion, other planner tunables, all of which need to be the same across all of the sessions for the same plan to be the 'correct' one in all the backends, not to mention roles and permissisons of the users involved. Simply put, it'd be quite a bit of work, would probably make things slower due to the cross-backend communication required, and would really only work for these specific "my application uses all the same prepared statements and always connects as the same user and with all the same parameters all the time" cases. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] prepared statement functioning range
高健 wrote: > Prepared statement is only for use in the same session at which it has been > executed. > It can not be shared via multiple sessions. That is correct, see http://www.postgresql.org/docs/current/static/sql-prepare.html > That is, when in some special situations , > if I have to use mulitple connections between client applicaiton and > postgresql database, > I must consider this point if I want to get benifit from prepared statements. > > So I am now thinking about the reason that prepared statement can not cross > over sessions. > Maybe it is because of MVCC control? So in order to make it simple, the > prepared statement is in one > session range? Statement plans are kept only per session, so a prepared statement across multiple transactions would not have any benefits unless the architecture would be changed to keep cached statement plans in shared memory (like Oracle has it, which is always a good source for bugs). Are there any other benefits do you expect from prepared statements? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] prepared statement functioning range
Hello everybody:
Sorry for disturbing.
I experience the prepared statement of postgresql via psql and have one
question:
In terminal A:
I prepared:
postgres=# prepare test(int) AS
postgres-# select * from customers c where c.cust_id = $*1*;
PREPARE
postgres=#
Then run:
postgres=# execute test(*3*);
cust_id | cust_name
-+---
*3* | Taylor
(*1* row)
postgres=#
In the terminal A , I can found the statement prepared via
pg_prepared_statements:
postgres=# select * from pg_prepared_statements;
name |statement|
prepare_time | parameter_types | from_sql
--+-+---+-+--
test | prepare test(int) AS +|
*2013*-*06*-*14* *15*:*58*:*22.796369*+*08* | {integer} | t
| select * from customers c where c.cust_id = $*1*; |
| |
(*1* row)
postgres=#
But in terminal B, I can't see the above statement:
postgres=# select * from pg_prepared_statements;
name | statement | prepare_time | parameter_types | from_sql
--+---+--+-+--
(*0* rows)
postgres=#
Even when I run execute in term B, It failed:
postgres=# execute test(3);
ERROR: prepared statement "test" does not exist
postgres=#
So I can draw a conclusion:
Prepared statement is only for use in the same session at which it has
been executed.
It can not be shared via multiple sessions.
That is, when in some special situations ,
if I have to use mulitple connections between client applicaiton and
postgresql database,
I must consider this point if I want to get benifit from prepared
statements.
So I am now thinking about the reason that prepared statement can not
cross over sessions.
Maybe it is because of MVCC control? So in order to make it simple, the
prepared statement is in one session range?
Thanks!
