我运行了一个Drupal 7.2网站，嵌入了a flash game和一些用于播放器统计的自定义PHP脚本。使用CentOS 5.6/64位，PostgreSQL 8.4.8和PHP 5.3。它是一个具有4GB RAM的Quad-Opteron。
在高峰期（大约有500名玩家在线），我的网站曾经因为太多的postmaster进程而崩溃。在pgsql-general mailing list的建议下，我安装了pgbouncer 1.3.4，并安装了以下/etc/pgbouncer.ini：

[databases]
pref = host=/tmp user=pref password=XXX dbname=pref

[pgbouncer]
logfile = /var/log/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_port = 6432
unix_socket_dir = /tmp

auth_type = md5
auth_file = /var/lib/pgsql/data/global/pg_auth

pool_mode = transaction
;pool_mode = session

server_check_delay = 10

max_client_conn = 200
default_pool_size = 16

log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

字符串
并在postgresql. conf中增加了shared_buffers = 1024 MB，减少了max_connections = 50。
这很有帮助，但我经常遇到一个问题，即无法找到准备好的PDO语句：

SQLSTATE[26000]: Invalid sql statement name: 7 ERROR: prepared statement "pdo_stmt_00000016" does not exist

型

• 可能是因为pgbouncer在prepare（）和execute（）之间切换连接。

我无法将pgbouncer切换到会话模式-我的网站将挂起。
我尝试添加PDO：：ATTR_EMULATE_PREPARES => true-我的网站也挂起了。
我已经在每个prepare（）和execute（）调用周围添加了transaction（）和commit（）-但是我经常得到以下错误：

SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block

型
下面是我的代码的一个摘录失败的错误-它是非常简单的，只是调用五个SELECT语句：

function fetch_top() {
        $table       = '';
        $top         = '';

        try {
                # throw exception on any errors
                $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
                $db = new PDO(sprintf('pgsql:host=%s port=%u; dbname=%s',
                        DBHOST, DBPORT, DBNAME), DBUSER, DBPASS, $options);

                # last week's winner
                $db->beginTransaction();
                $sth = $db->prepare("
select u.id,
        u.first_name,
        u.avatar,
        u.female,
        u.city,
        m.money,
        u.login > u.logout as online
from pref_users u, pref_money m where
        m.yw=to_char(current_timestamp - interval '1 week', 'IYYY-IW') and
        u.id=m.id
order by m.money desc
limit 1
");
                $sth->execute();
                $winner = $sth->fetch(PDO::FETCH_OBJ);
                $db->commit();

                $db->beginTransaction();
                $sth = $db->prepare('
select count(id) from (
    select id,
           row_number() over(partition by yw order by money desc) as ranking
    from pref_money
) x
where x.ranking = 1 and id=?
');
                $sth->execute(array($winner->id));
                $winner_medals = $sth->fetchColumn();
                $db->commit();

                # current week leader
                $db->beginTransaction();
                $sth = $db->prepare("
select u.id,
        u.first_name,
        u.avatar,
        u.female,
        u.city,
        m.money,
        u.login > u.logout as online
from pref_users u, pref_money m where
        m.yw=to_char(current_timestamp, 'IYYY-IW') and
        u.id=m.id
order by m.money desc
limit 1
");
                $sth->execute();
                $leader = $sth->fetch(PDO::FETCH_OBJ);
                $db->commit();

                $db->beginTransaction();
                $sth = $db->prepare('
select count(id) from (
    select id,
           row_number() over(partition by yw order by money desc) as ranking
    from pref_money
) x
where x.ranking = 1 and id=?
');
                $sth->execute(array($leader->id));
                $leader_medals = $sth->fetchColumn();
                $db->commit();

                # fetch top players
                $db->beginTransaction();
                $sth = $db->prepare("
select u.id,
        u.first_name,
        u.female,
        u.city,
        m.money,
        u.login > u.logout as online
from pref_users u, pref_money m where
        m.yw=to_char(current_timestamp, 'IYYY-IW') and
        u.id=m.id
order by m.money desc
limit 7
");
                $sth->execute();
                $i = 0;
                while ($player = $sth->fetch(PDO::FETCH_OBJ)) {
                        $top .= user_link($player) . ($i++ > 0 ? '' : '&nbsp;&raquo;') . '<br />';
                }
                $db->commit();

                # create the HTML table
                $table = sprintf('.... skipped for brevity ....');
        } catch (Exception $e) {
                exit('Database problem: ' . $e->getMessage());
        }

        return $table;
}

型
有没有人帮忙？亚历克斯