On Wed, 12 Mar 2003, Stefan Sturm wrote:
> Hello,
>
> we have a performance problem with PostgreSQL 7.2.4. But first I try to
> explain our situation:
>
> We have an online game. This game is a fantasy game, where you can buy
> and sell players for your team.
> All players for the teams are stored in one table. So in this table we
> have just one primary key and to foreign keys.
> When we have around 500 Users at one time on the maschine, the game is
> slowing down(Time for one spage up to 1minute and more). And I think
> the problem is this table. There are a lot of reading and writing
> connections to this table.
>
> So now my question: What can I do to increase the performance?
Hi Stefan, the first step to increasing performance is surveying the
current situation. When the machine starts to slow down, what does the
output of top look like? Is the machine running out of any resources like
file handles or memory? Note that most flavors of unix will not show a
lot of memory free they will show it being used as cache. If your machine
shows 400 megs of system cache and postgresql is using 4 megs of shared
memory, you aren't giving enough to postgresql. It'll go slow.
If you allocate too much memory for certain things (sort_mem is a
notorious gotcha for performance tuners) then you might allocate all your
memory and start using swap. It'll go slow then too.
So it's about finding the sweet spot.
What's in your postgresql.conf file?
What's your kernel's shm and max files / inodes type stuff set to?
How much memory does your machine have?
What kinds of queries are you running?
Are you reconnecting in your script or using a single connection?
Are you pooling connections?
Are you running lots of updates in autocommit that belong together
inside one transaction? It's much faster to put em together.
