Re: Need input on postgres used for phpBB

Поиск
Список
Период
Сортировка
От Jerome Macaranas
Тема Re: Need input on postgres used for phpBB
Дата
Msg-id 200505101235.28681.jerome@gmanmi.tv
обсуждение исходный текст
Ответ на Re: Need input on postgres used for phpBB  (Scott Marlowe <smarlowe@g2switchworks.com>)
Ответы Re: Need input on postgres used for phpBB  (Scott Marlowe <smarlowe@g2switchworks.com>)
Список pgsql-general
i didnt set fsm... the config i paste is all that i put into place...

is there a way to look at the query that's eating too much process
without starting the DB and redirect stdout out to a file?

process ID 32082..

32082 pts/3    S      0:08 postgres: mydbuser mydb 10.10.10.1 SELECT

if i do top..
                                    CPU
32082 postgres  17   0  203M 203M  177M R    84.1  6.7   0:05   3 postmaster
31767 postgres  15   0  168M 168M  162M R    35.6  5.5   0:38   2 postmaster
12623 root      15   0    88    4     0 S    35.0  0.0  79:44   1 rmserver
32040 postgres  15   0  162M 162M  156M S    10.1  5.3   0:07   3 postmaster
32587 postgres  15   0 39624  38M 38256 S    10.1  1.2   0:00   0 postmaster
19837 postgres  15   0  295M 295M  294M R     7.4  9.7  10:28   0 postmaster
15891 postgres  15   0  300M 299M  298M S     5.3  9.9   8:14   2 postmaster
12348 postgres  15   0  295M 294M  294M S     4.7  9.7   9:55   3 postmaster
32589 postgres  15   0 57204  55M 55972 S     4.7  1.8   0:00   1 postmaster
32661 postgres  19   0 21272  20M 19516 S     4.7  0.6   0:00   3 postmaster
21061 postgres  15   0  304M 303M  302M S     4.0 10.0   4:32   0 postmaster
32695 postgres  22   0 14624  14M 13112 S     4.0  0.4   0:00   0 postmaster
23438 postgres  15   0  304M 304M  303M S     3.3 10.0   4:18   0 postmaster
26455 postgres  15   0  307M 307M  306M S     3.3 10.1   0:26   0 postmaster
27564 postgres  15   0  307M 306M  305M S     3.3 10.1   0:18   0 postmaster
20345 postgres  15   0  303M 303M  302M S     2.6 10.0   4:25   3 postmaster
14068 postgres  15   0  306M 306M  304M S     2.6 10.1   1:19   2 postmaster
29438 postgres  15   0  310M 310M  308M S     2.6 10.2   0:06   3 postmaster
32655 postgres  18   0 47604  46M 46416 S     2.6  1.5   0:00   0 postmaster
32683 postgres  21   0  8244 8188  6912 S     2.6  0.2   0:00   2 postmaster
32707 postgres  23   0  7500 7440  6336 S     2.6  0.2   0:00   2 postmaster
19003 postgres  15   0  298M 298M  297M S     2.0  9.8   7:05   0 postmaster
19189 postgres  15   0  303M 302M  301M S     2.0 10.0   4:38   2 postmaster
 6616 postgres  15   0  312M 312M  311M S     2.0 10.3   1:09   0 postmaster
30575 postgres  15   0  189M 189M  173M S     2.0  6.2   0:07   2 postmaster
32157 postgres  15   0  174M 174M  169M S     2.0  5.7   0:01   2 postmaster
32201 postgres  15   0 53552  52M 52144 S     2.0  1.7   0:02   1 postmaster

On Monday 09 May 2005 23:28, Scott Marlowe wrote:
> On Mon, 2005-05-09 at 08:55, JM wrote:
> > Hi ALL,
> >
> >     we have a site that uses postgres as a backend for a forum.  this forum
> > does a lot of deletes, selects and inserts.  just recently for some
> > reason postgres eats a lot of processing power..
> >
> > here are some tech-details:
> >
> > tcpip_socket = true
> > max_connections = 260
> > superuser_reserved_connections = 2
> >
> > port = 5432
> > shared_buffers = 40102
> > sort_mem = 4096
> > effective_cache_size = 4000
>
> That's a LOT of shared buffers, and a very small setting for
> effective_cache_size, but I doubt those are causing your problems.  On
> most machines you'd be better off if those numbers were reversed.  how
> much RAM does your server have, by the way, and what version of
> postgresql and what os / version are you running as well?
>
> Also, what are your fsm settings?
>
> > # (initialized by initdb -- may be changed)
> > LC_MESSAGES = 'en_US.UTF-8'
> > LC_MONETARY = 'en_US.UTF-8'
> > LC_NUMERIC = 'en_US.UTF-8'
> > LC_TIME = 'en_US.UTF-8'
> >
> > ** im doing an hourly vaccum
> > 0 1-23 * * *          bin/vacuumdb --port 5432 --analyze -d myforumdb
> > 1>/dev/null 2>/tmp/vaccum_hourly.log
> >
> > --> is the hourly vaccum necessary? for some reason vaccum takes to much
> > time..
> >
> > input on how to make things work fast is highly appreciated..
>
> It is quite likely that your updates / deletes have outrun your
> vacuuming and you have table bloat.  Try issuing a vacuumdb -faz and see
> if things speed up.
>
> I'd recommend buildind, installing and running the pg_autovacuum daemon
> from now on.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

В списке pgsql-general по дате отправления:

Предыдущее
От: Neil Conway
Дата:
Сообщение: Re: [PERFORM] "Hash index" vs. "b-tree index" (PostgreSQL
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [PERFORM] "Hash index" vs. "b-tree index" (PostgreSQL