Re: [rt-users] Changing database connection options?
On Mon, Dec 13, 2010 at 10:57:03AM -0500, Kevin Falcone wrote: On Sat, Dec 11, 2010 at 11:40:17PM +0100, hubert depesz lubaczewski wrote: Hi, another question: is there any way to change database connections options from config? I.e. I want to change what's being passed as 4th argument to DBI-connect(). You'd need to patch DBIx::SearchBuilder to provide an option for RT to use and then patch RT to use that option. Hmm. This looks like a very complicated task. Wouldn't it be possible, to just call: $dbh-{'pg_server_prepare'} = 0; from rt? Reason - server side prepared statements are rarely useful, usually they do not speed things up, and they break some setups. Of course I can modify my own copy of rt, but patching it again and again after every upgrade seems to be suboptimal. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
Re: [rt-users] Changing database connection options?
On Tue, Dec 14, 2010 at 02:18:13PM +0100, hubert depesz lubaczewski wrote: On Mon, Dec 13, 2010 at 10:57:03AM -0500, Kevin Falcone wrote: On Sat, Dec 11, 2010 at 11:40:17PM +0100, hubert depesz lubaczewski wrote: Hi, another question: is there any way to change database connections options from config? I.e. I want to change what's being passed as 4th argument to DBI-connect(). You'd need to patch DBIx::SearchBuilder to provide an option for RT to use and then patch RT to use that option. Hmm. This looks like a very complicated task. Wouldn't it be possible, to just call: I'm delineating the right way to do it. $dbh-{'pg_server_prepare'} = 0; from rt? Reason - server side prepared statements are rarely useful, usually they do not speed things up, and they break some setups. Of course I can modify my own copy of rt, but patching it again and again after every upgrade seems to be suboptimal. I'm leery to make a change like that for RT without knowing much more about why I should be making it. -kevin pgp1VYoElflcw.pgp Description: PGP signature
Re: [rt-users] Changing database connection options?
On Tue, Dec 14, 2010 at 10:43:18AM -0500, Kevin Falcone wrote: I'm delineating the right way to do it. $dbh-{'pg_server_prepare'} = 0; from rt? Reason - server side prepared statements are rarely useful, usually they do not speed things up, and they break some setups. Of course I can modify my own copy of rt, but patching it again and again after every upgrade seems to be suboptimal. I'm leery to make a change like that for RT without knowing much more about why I should be making it. I am willing to provide all kind of information if you'd need. the biggest problem with server side prepared statements is described with examples in here: http://www.depesz.com/index.php/2008/05/10/prepared-statements-gotcha/ they (server side prepared statements) make sense if you: 1. use complicated queries with many joins 2. in the time of single db connection you run given query many times 3. if you change parameters to the query - you don't change the ones that can influence execution plan. all in all - (in my opinion) chances of this are very slim, and in rt - i haven't seen anything even resembling such queries. on the other hand - there is program called pgbouncer. which is connection pooler with great ability to reuse connection as soon as it finishes transaction. this is great from performance and resources-consumption standpoint, but breaks if you use server side prepared statements - because they are being used across many transactions. finally - when using short, simple queries - prepared statements can actually work worse than normal queries, because they need more roundtrips client-server to do the same task. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
Re: [rt-users] Changing database connection options?
finally - when using short, simple queries - prepared statements can actually work worse than normal queries, because they need more roundtrips client-server to do the same task. I suspect we'd need to see benchmarks of RT on versions of postgres from 8.1 - 9 to be able to really evaluate that. I'd hate to make someone else's performance worse. Adding a way for you to frob it to RT from the Connect code might be possible, but it'd need to be a config option. -kevin pgpk9BvA3nMA7.pgp Description: PGP signature
Re: [rt-users] Changing database connection options?
On Sat, Dec 11, 2010 at 11:40:17PM +0100, hubert depesz lubaczewski wrote: Hi, another question: is there any way to change database connections options from config? I.e. I want to change what's being passed as 4th argument to DBI-connect(). You'd need to patch DBIx::SearchBuilder to provide an option for RT to use and then patch RT to use that option. -kevin Reason for this is pretty simple: I'm using PostgreSQL, and DBD::Pg is choosing by default to use server side prepared statements. I don't like them, and would like to disable them, but I'd prefer not to modify RT source itself, so I was hoping for some way to make it happen from config. To show some example. When $dbh is created like this; $dbh = DBI-connect( 'dbi:Pg:dbname=xxx', 'yy', 'zz' ); DBD::Pg will use server side prepared statements. To disable this feature, one have to either: $dbh-{pg_server_prepare} = 0; or: $dbh = DBI-connect( 'dbi:Pg:dbname=xxx', 'yy', 'zz', { pg_server_prepare = 0 } ); In case you need to know why i want to disable server side prepared statements: 1. http://www.depesz.com/index.php/2008/05/10/prepared-statements-gotcha/ 2. it breaks connection pooling with pgbouncer (and I know I can use different pooling mode, but session pooling is pretty much useless) pgpcT8tVBTr3H.pgp Description: PGP signature