Dan Langille wrote:

> Indexing?  Sorry, what do you mean?  Do you have some output of some
> command?

I was wondering that as well. I don't see anything in my Bacula logs
that refers to "indexing" or "index", nor anything in the sources that
suggests that Bacula creates an index during normal backup operations.

> What is PG doing?  Have you looked at that?

While it's running, try:

    select * from pg_stat_activity where datname = 'bacula';

(or whatever your bacula database name is).

$ psql bacula
bacula=# \x
Expanded display is on.
bacula=# select * from pg_stat_activity where datname = 'bacula';
-[ RECORD 1 ]-+--------------------------------
datid         | 16385
datname       | bacula
procpid       | 29420
usesysid      | 16384
usename       | bacula
current_query | <IDLE>
waiting       | f
xact_start    |
query_start   | 2009-03-30 12:30:40.088227+08
backend_start | 2009-03-29 23:05:00.015011+08
client_addr   |
client_port   | -1
-[ RECORD 2 ]-+--------------------------------
datid         | 16385
datname       | bacula
procpid       | 3035
usesysid      | 10
usename       | postgres
current_query | select * from pg_stat_activity;
waiting       | f
xact_start    | 2009-03-30 12:31:02.50307+08
query_start   | 2009-03-30 12:31:02.50307+08
backend_start | 2009-03-30 12:30:48.328326+08
client_addr   |
client_port   | -1

> Number of cores may not help if everything is running in serial.

And, unfortunately, PostgreSQL cannot use multiple cores for a single
query at present. This is REALLY frustrating when you're doing things
like a CREATE INDEX .

On the other hand, the majority of the time your queries are disk-bound
anyway.

>> # - Memory -
> 
>> shared_buffers = 10MB # min 128kB or max_connections*16kB

That's ... not much. However, so long as FreeBSD's OS-level file caching
is reasonably efficient it should be minimally adequate.

>> work_mem = 10MB # min 64kB
>> maintenance_work_mem = 40MB # min 1MB

Those are the critical ones if your problem really is a slow CREATE
INDEX command or the like. You can push maintenance_work_mem really
rather high fairly safely, since it's only really used for CREATE INDEX,
REINDEX, VACUUM, CLUSTER, etc.

>> max_stack_depth = 525kB # min 100kB

This won't help or make a difference.

>> # - Free Space Map -
>> max_fsm_pages = 30000 # min max_fsm_relations*16, 6 bytes each

Unless you're seeing warnings in the logs about the FSM, you don't need
to change this. PostgreSQL 8.4 will manage it automatically, by the way.

>> max_fsm_relations = 1000 # min 100, ~70 bytes each

You don't need to change this unless you have huge numbers of tables.
This parameter will also be automatically managed by PostgreSQL 8.4 .

>> I suspect the PG tweaks are not right and I am dealing with a large
>> number of files are my main problems. It could also have something to do
>> with the two previous indexing which I stopped.
> 
> Perhaps.  Have you run any vacuums on PostgreSQL?

In particular, if you have run VACUUM FULL without a corresponding
REINDEX, run REINDEX on the affected tables now. You should almost never
use VACUUM FULL; CLUSTER is more useful and more efficient, plus it
avoids index bloat.

--
Craig Ringer

------------------------------------------------------------------------------
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users