Sending several commands simultaneously to PostgreSQL 8.4

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Hello,

I run a card game written in Perl on
a CentOS 6.3 / 64 bit + PostgreSQL 8.4.13
where quite a lot player statistics are written
to the d/b (score, game results, moves, etc.)

Here a player profile: http://preferans.de/DE11198

The machine has a quad intel + 32 GB RAM.

In poostgresql.conf I set:

    max_connections = 100
    shared_buffers = 4096MB
    work_mem = 32MB
    log_min_duration_statement = 10000

I also use pgbouncer (for PHP scripts),
but my Perl game daemon talks
directly to /tmp/.s.PGSQL.5432

My game daemon runs in a non-forking loop
and poll()s TCP sockets to the player machines..

Players complain about my server
freezing for few seconds sometimes
and I can see it myself in the game logs -
when data is sometimes written to d/b
(and postmaster processes take 90% CPU).

So my question is:

do I have to program a separate daemon -
which would be polled via a Unix domain
socket by my main game daemon and
which would handle sending SQL commands
(typically insert's and select's)?

Or does such a generic daemon exist already?

Or can multiple commands be sent to
the PostgreSQL simultaneously?

Here is how my Perl daemon talks to to
the d/b usually (this happens inside of the
poll() loop and thus stops everything for ms):

sub logout($) {
        my $user = shift;
        my $id = $user->{ID};

        eval {
                my $dbh = DBI->connect_cached(DSN, DBUSER, DBPASS, {
                    AutoCommit => 1,
                    PrintWarn => 1,
                    PrintError => 1,
                    RaiseError => 1,
                    FetchHashKeyName => 'NAME_lc',
                    pg_enable_utf8 => 1
                });

                if ($user->{SCORE}) {
                        my $sth_money = $dbh->prepare_cached(
                            q{select pref_update_money(?, ?)});
                        $sth_money->execute($id, $user->{SCORE});
                        $user->{SCORE} = 0;
                }

                my $sth_logout = $dbh->prepare_cached(
                    q{update pref_users set logout=now() where id=?});
                $sth_logout->execute($id);
        };
        warn $@ if $@;
}

Thanks for any ideas
Alex


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general