Re: Problems with autovacuum

W dniu 25 maja 2009 17:32 użytkownik Scott Marlowe
<scott(dot)marlowe(at)gmail(dot)com> napisał:
>> Recent change postgresql server from Amazon EC2 small into large one.
>> That gives me x86_64 arch, two core cpu and 7.5GB ram. Atm got almost
>> ~2000 small databases at that server and autovacuum working hole time
>
>> postgresql.conf:
>> max_fsm_pages = 204800
>> max_fsm_relations = 4000
>
> So, in 2000 databases, there's only an average of 2 relations per db
> and 102 dead rows?  Cause that's all you got room for with those
> settings.
>
> Whats the last 20 or so lines of vacuum verbose as run by a superuser say?

Guess you was right

#v+
Total free space (including removable row versions) is 2408 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 2092 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_depend_depender_index" now contains 5267 row versions
in 30 pages
SZCZEGÓŁY: 4 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_depend_reference_index" now contains 5267 row
versions in 32 pages
SZCZEGÓŁY: 4 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_depend": moved 0 row versions, truncated 39 to 39 pages
SZCZEGÓŁY: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "pg_catalog.pg_depend"
INFO: "pg_depend": scanned 39 of 39 pages, containing 5267 live rows
and 0 dead rows; 3000 rows in sample, 5267 estimated total rows
INFO: free space map contains 3876 pages in 4000 relations
SZCZEGÓŁY: A total of 67824 page slots are in use (including overhead).
67824 page slots are required to track all free space.
Current limits are: 204800 page slots, 4000 relations, using 1612 kB.
NOTICE: max_fsm_relations(4000) equals the number of relations checked
PODPOWIEDŹ: You have at least 4000 relations. Consider increasing
the configuration parameter "max_fsm_relations".
VACUUM
#v-

Change:

max_fsm_pages = 6400000
max_fsm_relations = 400000

--
Łukasz Jagiełło
System Administrator
G-Forces Web Management Polska sp. z o.o. (www.gforces.pl)

Ul. Kruczkowskiego 12, 80-288 Gdańsk
Spółka wpisana do KRS pod nr 246596 decyzją Sądu Rejonowego Gdańsk-Północ