43 ms·

For some reason, Rails doesn't behave well with pgbouncer or any other pg connection pool. So I really have no choice but to set my max connections to 1900, and

For some reason, Rails doesn't behave well with pgbouncer or any other pg connection pool. So I really have no choice but to set my max connections to 1900, and pray that it's enough. Surprisingly, my 48 GB RAM, quad core CPU, non SSD server can handle that load, although the hardware will probably degrade after a certain time (oh well shrugs)

_hans_ 13y ago
We enabled PGBouncer transaction mode and had to set 'prepared_statements: false' in our database.yml b/c the transaction mode doesn't support that. Works fine for us.
- gkop 13y ago
  What are the implications of 'prepared_statements: false'? It sounds really bad.
  - tbarbugli 13y ago
    dont prepare statements?
  - justincormack 13y ago
    Prepared statements are not a massive performance gain in postgres so I wouldn't worry.
    - nousernamesleft 13y ago
      If done right they can be huge. The fact that they do nothing when you just do "prepare" followed immediately by "execute" in place of just running the query directly is not a problem with postgresql or prepared statements, it is a problem of doing something dumb. For simple selects the parsing and planning stage can be 90% of the time spent. The problem is that postgresql doesn't have stored procedures, so you have to make sure every connection is initialized by running all of your "prepare"s before it is made available to the pool.
      - stefantalpalaru 13y ago
        > The problem is that postgresql doesn't have stored procedures Of course it has: http://www.postgresql.org/docs/9.3/static/sql-createfunction.html http://www.postgresql.org/docs/9.3/static/sql-createfunction... And you can choose between 4 languages: http://www.postgresql.org/docs/9.3/interactive/xplang.html http://www.postgresql.org/docs/9.3/interactive/xplang.html
        nousernamesleft 13y ago
        Those are functions, not stored procedures. Every time that function is executed, it is re-parsed and re-planned.
        dbenhur 13y ago
        The default stored procedure language is PL/pgSQL http://www.postgresql.org/docs/9.3/static/plpgsql.html http://www.postgresql.org/docs/9.3/static/plpgsql.html and has been available in Postgres almost forever.
        nousernamesleft 13y ago
        Those are functions, not stored procedures.
        chanks 13y ago
        PL/pgSQL function plans are cached. Functions written in other languages may not be, I don't know, but in my experience PL/pgSQL is the most widely used. http://www.postgresql.org/docs/9.3/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING http://www.postgresql.org/docs/9.3/static/plpgsql-implementa...
        nousernamesleft 13y ago
        >PL/pgSQL function plans are cached Read your link. They can be cached. You have no way to ensure that they are. And you have no way to have the planning done up front at all, you have to wait until the function is run, and even then every code path in the function has to be exercised to get it planned. And then half the time it doesn't think it should bother to cache the plan anyways. And it is per-session, not shared. So every single connection has to start over with an empty cache every time, and there's tons of duplicate plans in the cache wasting space. Not cool, and by far the biggest thing keeping a ton of "enterprise" customers on SQL server and oracle.
    - nirvdrum 13y ago
      They do provide a nice safety net against SQL injection though. A problem that Rails has from time to time.
      - zzzeek 13y ago
        bound parameters are the safety net against SQL injection. Prepared statements are not a prerequisite for that depending on database client API. (e.g. psycopg2, doesn't use prepared statements).
        nirvdrum 13y ago
        I appreciate the comment, but since this was in regards to Rails, the two options were basically use prepared statements or don't. You don't get any safety without them and have to rely on Rails, which does an admirable job, but has faltered from time to time.
        saurik 13y ago
        If Rails stops using the two-step parse/execute PostgreSQL commands (which are supported without issue by PGbouncer, and which is how you are supposed to get bound parameters) when prepared_statements is turned off (a setting which intuitively should only turn off using standard SQL named prepared statements), then that's pathetic behavior (even for Rails) and should really be fixed.
    - deleted 13y ago
      [deleted]
  - _hans_ 13y ago
    There was no apparent problem with turning it off for us. But maybe there is for others.

_hans_ 13y ago

We enabled PGBouncer transaction mode and had to set 'prepared_statements: false' in our database.yml b/c the transaction mode doesn't support that. Works fine for us.

gkop 13y ago
What are the implications of 'prepared_statements: false'? It sounds really bad.
- tbarbugli 13y ago
  dont prepare statements?
- justincormack 13y ago
  Prepared statements are not a massive performance gain in postgres so I wouldn't worry.
  - nousernamesleft 13y ago
    If done right they can be huge. The fact that they do nothing when you just do "prepare" followed immediately by "execute" in place of just running the query directly is not a problem with postgresql or prepared statements, it is a problem of doing something dumb. For simple selects the parsing and planning stage can be 90% of the time spent. The problem is that postgresql doesn't have stored procedures, so you have to make sure every connection is initialized by running all of your "prepare"s before it is made available to the pool.
    - stefantalpalaru 13y ago
      > The problem is that postgresql doesn't have stored procedures Of course it has: http://www.postgresql.org/docs/9.3/static/sql-createfunction.html http://www.postgresql.org/docs/9.3/static/sql-createfunction... And you can choose between 4 languages: http://www.postgresql.org/docs/9.3/interactive/xplang.html http://www.postgresql.org/docs/9.3/interactive/xplang.html
      - nousernamesleft 13y ago
        Those are functions, not stored procedures. Every time that function is executed, it is re-parsed and re-planned.
        dbenhur 13y ago
        The default stored procedure language is PL/pgSQL http://www.postgresql.org/docs/9.3/static/plpgsql.html http://www.postgresql.org/docs/9.3/static/plpgsql.html and has been available in Postgres almost forever.
        nousernamesleft 13y ago
        Those are functions, not stored procedures.
        chanks 13y ago
        PL/pgSQL function plans are cached. Functions written in other languages may not be, I don't know, but in my experience PL/pgSQL is the most widely used. http://www.postgresql.org/docs/9.3/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING http://www.postgresql.org/docs/9.3/static/plpgsql-implementa...
        nousernamesleft 13y ago
        >PL/pgSQL function plans are cached Read your link. They can be cached. You have no way to ensure that they are. And you have no way to have the planning done up front at all, you have to wait until the function is run, and even then every code path in the function has to be exercised to get it planned. And then half the time it doesn't think it should bother to cache the plan anyways. And it is per-session, not shared. So every single connection has to start over with an empty cache every time, and there's tons of duplicate plans in the cache wasting space. Not cool, and by far the biggest thing keeping a ton of "enterprise" customers on SQL server and oracle.
  - nirvdrum 13y ago
    They do provide a nice safety net against SQL injection though. A problem that Rails has from time to time.
    - zzzeek 13y ago
      bound parameters are the safety net against SQL injection. Prepared statements are not a prerequisite for that depending on database client API. (e.g. psycopg2, doesn't use prepared statements).
      - nirvdrum 13y ago
        I appreciate the comment, but since this was in regards to Rails, the two options were basically use prepared statements or don't. You don't get any safety without them and have to rely on Rails, which does an admirable job, but has faltered from time to time.
        saurik 13y ago
        If Rails stops using the two-step parse/execute PostgreSQL commands (which are supported without issue by PGbouncer, and which is how you are supposed to get bound parameters) when prepared_statements is turned off (a setting which intuitively should only turn off using standard SQL named prepared statements), then that's pathetic behavior (even for Rails) and should really be fixed.
  - deleted 13y ago
    [deleted]
- _hans_ 13y ago
  There was no apparent problem with turning it off for us. But maybe there is for others.