Dear list,
I am having trouble running PostgreSQL 9.3 under OpenSuSE because auto vacuum
does not seem to work.
Here are the details on my version:
# select version();
PostgreSQL 9.3.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.8.1
20130909 [gcc-4_8-branch revision 202388], 64-bit)
After starting the server with pg_ctl start, I get the following entries in the
logs:
2014-12-02 15:27:36 CET LOG: could not bind socket for statistics
collector: Cannot assign requested address
2014-12-02 15:27:36 CET LOG: disabling statistics collector for lack of
working socket
2014-12-02 15:27:36 CET WARNING: autovacuum not started because of
misconfiguration
2014-12-02 15:27:36 CET HINT: Enable the "track_counts" option.
BUT: track_counts is set to on in the postgresql.conf file (and so is
auto_vacuum).
I found some older threads using Google, and the person was given the advice to
check the listen addresses resolve to the proper IP addresses, but this is the
case for me:
> grep listen_address /var/lib/pgsql/data/postgresql.conf
listen_addresses = '127.0.0.1, 192.168.185.41' # what IP address(es) to
listen on;
> /sbin/ifconfig | grep eth0 -C 2
eth0 Link encap:Ethernet HWaddr 00:25:90:5A:B0:42
inet addr:192.168.185.41 Bcast:192.168.185.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
> ping localhost
PING localhost (127.0.0.1) 56(84) bytes of data.
64 bytes from localhost (127.0.0.1): icmp_seq=1 ttl=64 time=0.026 ms
...
Some threads claim this was only a warning, and AV would be running, but this is
not the case:
# SELECT
schemaname, relname,
last_vacuum, last_autovacuum,
vacuum_count, autovacuum_count
FROM pg_stat_user_tables;
schemaname | relname | last_vacuum | last_autovacuum |
vacuum_count | autovacuum_count
------------+------------------------------+-------------+-----------------+--------------+------------------
public | plcc_motiftype | | |
0 | 0
public | plcc_ssecontact_complexgraph | | |
0 | 0
public | plcc_nm_ssetoproteingraph | | |
0 | 0
public | plcc_ssetypes | | |
0 | 0
public | plcc_contact | | |
0 | 0
public | plcc_complexcontacttypes | | |
0 | 0
public | plcc_protein | | |
0 | 0
public | plcc_contacttypes | | |
0 | 0
public | plcc_graphtypes | | |
0 | 0
public | plcc_sse | | |
0 | 0
public | plcc_secondat | | |
0 | 0
public | plcc_nm_ssetofoldinggraph | | |
0 | 0
public | plcc_fglinnot | | |
0 | 0
public | plcc_complex_contact | | |
0 | 0
public | plcc_foldinggraph | | |
0 | 0
public | plcc_ligand | | |
0 | 0
public | plcc_nm_ligandtochain | | |
0 | 0
public | plcc_graph | | |
0 | 0
public | plcc_graphlets | | |
0 | 0
public | plcc_motif | | |
0 | 0
public | plcc_chain | | |
0 | 0
public | plcc_complexgraph | | |
0 | 0
public | plcc_nm_chaintomotif | | |
0 | 0
public | plcc_graphletsimilarity | | |
0 | 0
Atm, 64 parallel instances of a custom Java application write a lot of of data
into this database server (this is a computer cluster), so it SHOULD vaccuum.
Doing simple SELECTs takes a long time after some hours (a 'SELECT count(*)'
from a table with 5.5M columns takes 4 secs). When I run VACUUM manually, it
works (takes very long though), and afterwards, the SELECTs are fast again
(2ms). But this changes again when I re-start the processes, of course.
What should I try next? How can I get more info on why auto vaccum is not
running?
Thanks in advance,
--
Tim
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
