Sending several commands simultaneously to PostgreSQL 8.4

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Sending several commands simultaneously to PostgreSQL 8.4
Date: 2013-01-21 12:57:49
Message-ID: CAADeyWjoM583u8mrtjveURpLY-iimc4Mhr8og63mHUCuXj_uqQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Ernst 2013-01-21 14:19:53 Re: pg_Restore
Previous Message Edson Richter 2013-01-21 12:38:15 What is impact of "varchar_opts"?