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@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
