pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVery specific server situation
3+ messages / 2 participants
[nested] [flat]
* Very specific server situation
@ 2007-04-27 17:38 Mauro N. Infantino <mauroi@digbang.com>
0 siblings, 1 reply; 3+ messages in thread
From: Mauro N. Infantino @ 2007-04-27 17:38 UTC (permalink / raw)
To: pgsql-performance
Hi,
We're facing some perfomance problems with the database for a web site with
very specific needs. First of all, we're using version 8.1 in a server with
1GB of RAM. I know memory normally should be more, but as our tables are not
so big (as a matter of fact, they are small) I think the solution would not
be adding more RAM.
What we basically have is a site where each user has a box with links to
other randomly selected users. Whenever a box from a user is shown, a SPs is
executed: a credit is added to that user and a credit is substracted from
the accounts of the shown links. Accounts with no credits do not have to be
listed. So, we've lots (LOTS) of users querying and updating the same table.
Sometimes with big peaks.
Our first attempt was to split that table in two: one for the actual credits
and another one for the users. So, only the credits table gets updated on
every request, but it has a trigger that updates a flag field in the users
table saying if the user has credits. This had a good impact, but I guess
it's not enough.
For now, we only have 23.000 users, but it's going to grow. Do you have any
advice? Is this possible with postgres or do you recommend just to try with
a volatile memory approach for the credits?
We're using pgpool and the output from free shows only 350M of RAM being
used.
Some relevants parts of the .conf:
max_connections = 160
shared_buffers = 40000
work_mem = 3096
maintenance_work_mem = 131072
max_fsm_pages = 70000
fsync = false
autovacuum = on
Any help would be really appreciated.
Thanks in advance,
Mauro.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Very specific server situation
@ 2007-04-27 22:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mauro N. Infantino <mauroi@digbang.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2007-04-27 22:51 UTC (permalink / raw)
To: Mauro N. Infantino <mauroi@digbang.com>; +Cc: pgsql-performance
"Mauro N. Infantino" <mauroi@digbang.com> writes:
> What we basically have is a site where each user has a box with links to
> other randomly selected users. Whenever a box from a user is shown, a SPs is
> executed: a credit is added to that user and a credit is substracted from
> the accounts of the shown links. Accounts with no credits do not have to be
> listed. So, we've lots (LOTS) of users querying and updating the same table.
Have you checked to make sure the query plans are reasonable? Have you
checked that autovacuum is running often enough? (You might want to try
contrib/pgstattuple to see how much dead space there is in your
heavily-updated tables.) Also, on a high-update workload it is
absolutely critical to boost checkpoint_segments far enough that you are
not doing checkpoints oftener than maybe once every five minutes.
If the performance problems seem "bursty" then you may also need to look
at adjusting bgwriter and/or vacuum cost delay parameters to smooth out
the I/O load.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Very specific server situation
@ 2007-04-28 20:43 Mauro N. Infantino <mauroi@digbang.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Mauro N. Infantino @ 2007-04-28 20:43 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom,
Thank you very much for your suggestions.
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>
> Have you checked to make sure the query plans are reasonable?
I've attached the main query and its explain plan. I can't find a way to
improve it.
Does it make any difference if it's executed from a stored procedure? Is
there any difference between the SP's language (PL/pgSQL, PL/php, etc. It
needs to make some other tiny things besides the query)?
> You might want to try contrib/pgstattuple
Thanks. I'll give it a try and report the results here.
> absolutely critical to boost checkpoint_segments far enough
How do I know how ofen checkpoints are done?
I've modified the parameters:
checkpoint_segments = 36 # it was 12 before
checkpoint_timeout = 1000
checkpoint_warning = 300 # so, I'll get a warning if it's too frequent.
commit_delay = 5000
commit_siblings = 2
> adjusting bgwriter and/or vacuum cost delay parameters
I've used a moderate cost delay configuration to see how it responds
(vacuum_cost_delay = 100 & vacuum_cost_limit = 200).
Do you have any advice on how to configure the bgwriter? I have no clue
about it and couldn't find anything clear.
Also, I know an upgrade to 8.2 is always a good thing, but is there any
change that could help this specific situation?
Again, thank you very much for your answers (and, of course, everything you
do in pgsql).
Regards,
Mauro.
Attachments:
plan.txt (text/plain, 2.3K) download
SELECT Player.Title,
Player.IdPlayer
FROM Player
INNER JOIN ActivePlayer ON ActivePlayer.IdPlayer = Player.IdPlayer
INNER JOIN PlayerCategoryShow ON PlayerCategoryShow.IdCategory = 18 AND PlayerCategoryShow.IdPlayer = Player.IdPlayer
WHERE IdLanguage = 'EN'
AND Player.IdPlayer NOT IN (279)
AND Player.CreditsAvailable
ORDER BY RANDOM()
LIMIT 8;
Limit (cost=36760.49..36760.51 rows=8 width=25) (actual time=130.359..130.375 rows=8 loops=1)
-> Sort (cost=36760.49..36763.90 rows=1364 width=25) (actual time=130.355..130.360 rows=8 loops=1)
Sort Key: random()
-> Hash Join (cost=36252.38..36689.47 rows=1364 width=25) (actual time=87.092..126.733 rows=963 loops=1)
Hash Cond: ("outer".idplayer = "inner".idplayer)
-> Seq Scan on activeplayer (cost=0.00..276.45 rows=19145 width=4) (actual time=0.014..20.865 rows=19163 loops=1)
-> Hash (cost=36248.24..36248.24 rows=1654 width=29) (actual time=86.901..86.901 rows=1250 loops=1)
-> Hash Join (cost=35685.70..36248.24 rows=1654 width=29) (actual time=58.835..84.643 rows=1250 loops=1)
Hash Cond: ("outer".idplayer = "inner".idplayer)
-> Bitmap Heap Scan on playercategoryshow (cost=54.96..493.96 rows=8560 width=4) (actual time=2.445..14.130 rows=8397 loops=1)
Recheck Cond: (idcategory = 18)
-> Bitmap Index Scan on ix_playercategoryshow (cost=0.00..54.96 rows=8560 width=0) (actual time=2.328..2.328 rows=8401 loops=1)
Index Cond: (idcategory = 18)
-> Hash (cost=35619.53..35619.53 rows=4484 width=25) (actual time=56.294..56.294 rows=4114 loops=1)
-> Bitmap Heap Scan on player (cost=5296.33..35619.53 rows=4484 width=25) (actual time=12.943..49.075 rows=4114 loops=1)
Recheck Cond: ((idlanguage)::text = 'EN'::text)
Filter: ((idplayer <> 279) AND creditsavailable)
-> Bitmap Index Scan on ix_player3 (cost=0.00..5296.33 rows=15809 width=0) (actual time=9.868..9.868 rows=16245 loops=1)
Index Cond: ((idlanguage)::text = 'EN'::text
Total runtime: 130.946 ms
end of thread, other threads:[~2007-04-28 20:43 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-27 17:38 Very specific server situation Mauro N. Infantino <mauroi@digbang.com>
2007-04-27 22:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-28 20:43 ` Mauro N. Infantino <mauroi@digbang.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox